JAVA WEB数据库操作封装 – 9.22

呐，折腾了一会儿，玩了下封装，精简了下代码，只能说一定程度上解决了代码的冗余，开发经验问题，我肯定没老手做得完美，但是我还是尽力的去解决了。代码结构如下，新建一个公共类用于封装代码

接下来是3个java类的详细代码

第一个DeleteSQL.java（类的继承）

package com.lckiss.action;

import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServlet;

public class DeletSQL extends HttpServlet {

    private static final long serialVersionUID = 1L;

    @Override
    public void service(ServletRequest req, ServletResponse res) {

        try {
            // 从传输过来的链接中获取empno值
            String empno = req.getParameter("empno");
            // 设置格式，防止乱码 必须在流前定义
            res.setContentType("text/html;charset=GBK");
            PrintWriter out = res.getWriter();
            // 内部打印，方便检测是否出错
            System.out.println(empno);
            // 传入empno主键，删除其附属值
            boolean rs = UserDelete(empno);
            // 打印删除结果
            System.out.println(rs);
            if (rs) {
                out.println("<p>" + empno + " 已删除成功</p>");
                out.println("<p>编号 , 姓名 , 工作 , HIREDATA , SAL , COMM , DEPTNO.</p>");
                List list = OracleComm.QueryALL();
                for (Object c : list) {
                    String l = (String) c;
                    String no = l.split(",")[0];
                    // 因为list.add中插入这段字符串容易出错，所以单独新建一个字符串
                    String del = "http://localhost/day_1/del?empno=" + no + "";
                    out.println("<p>" + l + "-" + "<a href="" + del + "">删除</a></p>");
                }
            } else {
                out.println("失败");
            }
            out.flush();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public boolean UserDelete(String empno) throws Exception {

        String sql = "delete from emp where EMPNO=" + empno + "";
        System.out.println(sql);
        int rs = OracleComm.ExecuteUpdate(sql);
        while (rs > 0) {
            return true;
        }
        return false;
    }
}

第二个:OracleComm.java

package com.lckiss.action;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class OracleComm {

    public static Connection LinkSQL() throws Exception {
        String url = "jdbc:oracle:thin:@10.1.5.5:1521:orcl";
        String user = "user ";
        String password = "password ";
        new oracle.jdbc.driver.OracleDriver();
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    public static List QueryALL() throws Exception {

        List list = new ArrayList();
        Connection conn = LinkSQL();
        String sql = "select * from emp";
        ResultSet rs = conn.createStatement().executeQuery(sql);
        // 按行循环，读完转到下一行
        while (rs.next()) {
            int no = rs.getInt("EMPNO");
            String ename = rs.getString("ENAME");
            String job = rs.getString("JOB");
            Date hiredata = rs.getDate("HIREDATE");
            int mgr = rs.getInt("MGR");
            int comm = rs.getInt("COMM");
            int sal = rs.getInt("SAL");
            int deptno = rs.getInt("DEPTNO");
            // 以上为所以列名
            list.add(no + "," + ename + "," + job + "," + mgr + "," + hiredata + "," + sal + "," + comm + "," + deptno);
        }
        return list;
    }

    // 通用接口：验证是否连接成功
    public static boolean CheckUsers(String name, String pwd) throws Exception {

        String sql = "select * from emp where ENAME='" + name + "' and JOB ='" + pwd + "'";
        ResultSet rs = OracleComm.ExecuteQuerry(sql);
        while (rs.next()) {
            return true;
        }
        return false;
    }

    // 通用接口：用于执行查询语句并返回数据
    public static ResultSet ExecuteQuerry(String sql) throws Exception {
        Connection conn = LinkSQL();
        ResultSet rs = conn.createStatement().executeQuery(sql);

        return rs;
    }

    // 通用接口：用于执行更新语句并返回数据
    public static int ExecuteUpdate(String sql) throws Exception {

        Connection conn = LinkSQL();
        int rs = conn.createStatement().executeUpdate(sql);

        return rs;
    }
}

第三个：UserLogin.java (类的实现)

package com.lckiss.action;

import java.io.PrintWriter;
import java.util.List;
import javax.servlet.Servlet;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

public class UserLogin implements Servlet {

    @Override
    public void service(ServletRequest req, ServletResponse res) {
        
        String name = req.getParameter("name");
        String pwd = req.getParameter("pwd");
        // 内部输出一次看从网页获取的数据是否错误
        System.out.println(name);
        System.out.println(pwd);
        try {
            // 设置流格式
            res.setContentType("text/html;charset=GBK");
            PrintWriter out = res.getWriter();
            // 检查数据库登录是否成功
            boolean check = OracleComm.CheckUsers(name, pwd);
            // 成功为true，反之失败
            System.out.println(check);
            if (check) {
                // 表格样式，这里由于精力问题，不考虑div包裹
                out.println("<p>编号 , 姓名 , 工作 , HIREDATA , SAL , COMM , DEPTNO.</p>");
                List list = OracleComm.QueryALL();
                for (Object c : list) {
                    String l = (String) c;
                    String no = l.split(",")[0];
                    // 因为list.add中插入这段字符串容易出错，所以单独新建一个字符串
                    String del = "http://localhost/day_1/del?empno=" + no + "";
                    out.println("<p>" + l + "-" + "<a href="" + del + "">删除</a></p>");
                }
            } else {
                out.println("失败");
            }
            out.flush();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    @Override
    public void destroy() {
    }

    @Override
    public ServletConfig getServletConfig() {
        return null;
    }

    @Override
    public String getServletInfo() {
        return null;
    }

    @Override
    public void init(ServletConfig config) throws ServletException {
        System.out.println("已初始化");
    }
}

其他未改变，详情参照上一篇文章点我

最后上一个结果图，基本被我删得差不多了,如果你有更好的优化方案，欢迎与我探讨

本文用于博主记载自己的学习脚印，转载请注明出处。