jdbc之分层操作（oracle）

本Demo利用 oracle 数据库下用户 scott完成

运行环境

windows 10 + idea+oracle11g

 

一 首先配置好 oracle 数据库的配置文件  properties 在idea中的需要新建一个 resource文件夹类型方便扫描 否则容易出现扫描不到配置文件 的问题。resource文件夹可以通过项目结构进行设置

url=jdbc:oracle:thin:@//localhost:1521/orcl
className=oracle.jdbc.OracleDriver
uname=scott
upwd=123456

二 首先新建一个Dao层链接oracle的的BaseDao

public class BaseDao {
    static Properties pro=new Properties();
    static String className,url,uname,upwd;
    static{
        try {
            pro.load(BaseDao.class.getClassLoader().getResourceAsStream("mysql.properties"));
            className=pro.getProperty("className");
            url=pro.getProperty("url");
            uname=pro.getProperty("uname");
            upwd=pro.getProperty("upwd");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     *  一个获取数据连接
     */
    public Connection getConn(){
        try {
            Class.forName(className);
            return DriverManager.getConnection(url,uname,upwd);

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    /** 释放jdbc资源的
     * @param conn
     * @param stat
     * @param rs
     */
    public void closeAll(Connection conn,Statement stat,ResultSet rs){
        try{
            if(rs!=null)rs.close();
            if(stat!=null)stat.close();
            if(conn!=null)conn.close();
        }catch(Exception e){
            e.printStackTrace();
        }

    }

    /**
     * 增删改通用方法
     * @return
     */
    public int executeSql(String sql,Object[] paras){
        Connection conn=null;
        PreparedStatement stat=null;
        try{
            conn=getConn();
            conn.setAutoCommit(false);
            stat=conn.prepareStatement(sql);
            if(paras!=null){// 一一给sql的参数赋值
                for (int i = 0; i <paras.length ; i++) {
                    stat.setObject(i+1,paras[i]);
                }
            }
            int i=stat.executeUpdate();// 执行增删改方法，返回受影响行数
            if(i>0)conn.commit();else conn.rollback();
            return i;
        }catch(Exception e){
            e.printStackTrace();
            return -1;
        }finally{
           closeAll(conn,stat,null);
        }
    }
}

三 新建一个 实体类 employee

public class Employee {
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private Date hiredate;
    private double sal;
    private double comm;
    private int deptno;

    public Employee() {
    }

    public Employee(int empno, String ename, String job, int mgr,
                    Date hiredate, double sal, double comm, int deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }

    public int getEmpno() {
        return empno;
    }

    public void setEmpno(int empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public double getSal() {
        return sal;
    }

    public void setSal(double sal) {
        this.sal = sal;
    }

    public double getComm() {
        return comm;
    }

    public void setComm(double comm) {
        this.comm = comm;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }
}

四 创建一个employee的接口EmployeeDao

public interface EmpolyeeDao{

     //查找所有的员工
    List<Employee> getAllEmp();
  //按照empno查找员工
    Employee getEmpolyByEmpno(int cno);
   //添加员工
    int addemp(Employee emp);
  // 修改员工
    int updatemp(Employee emp);
//删除员工
    int delemp(int  empno);
}

五 创建EmployeeDao的实现类EmployeeDaoImpl

public class EmployeeDaoImpl extends BaseDao implements EmpolyeeDao {
   
    private Connection conn;
    private PreparedStatement stat;
    private ResultSet rs;

    @Override
    public List<Employee> getAllEmp() {
        String sql = "select * from emp";
        List<Employee> empl = null;
        try {
            conn = super.getConn();
            stat = conn.prepareStatement(sql);
            rs = stat.executeQuery();
            empl = new ArrayList<Employee>();
            while (rs.next()) {
                Employee emp = new Employee(rs.getInt(1), rs.getString(2), rs.getString(3),
                        rs.getInt(4), rs.getDate(5), rs.getDouble(6),
                        rs.getDouble(7), rs.getInt(8));
                empl.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(conn, stat, rs);
        }
        return empl;
    }

    @Override
    public Employee getEmpolyByEmpno(int empno) {
        String sql = "select * from emp where empno = ?";
        Employee emp = null;
        try {
            conn = super.getConn();
            stat = conn.prepareStatement(sql);
            stat.setInt(1, empno);
            rs = stat.executeQuery();
            while (rs.next()) emp = new Employee(rs.getInt(1), rs.getString(2),
                    rs.getString(3), rs.getInt(4), rs.getDate(5),
                    rs.getDouble(6), rs.getDouble(7), rs.getInt(8));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return emp;
    }

    @Override
    public int addemp(Employee emp) {
        String sql = "insert into emp value(?,?,?,?,to_Date(?,'YYYY-MM-DD'),?,?,?)";
        return super.executeSql(sql, new Object[]{emp.getEmpno(), emp.getEname(),
                emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno(),});
    }

    @Override
    public int updatemp(Employee emp) {
        StringBuilder sb = new StringBuilder();
        if (emp != null) {//判断该员工的是否存在  如果存在则判断输入的修改属性是否存在对其sql语句进行追加直到其结束。
            sb.append(" update class set ");
            if (emp.getEname() != null) {
                sb.append(" cname='" + emp.getEname() + "',");
            }
            if (emp.getJob() != null) {
                sb.append("job='" + emp.getJob() + "',");
            }
            if (emp.getJob() != null) {
                sb.append("mgr='" + emp.getMgr() + "',");
            }
            if (emp.getHiredate() != null) {
                sb.append("Hiredate='" + emp.getHiredate() + "',");
            }
            if (emp.getSal() != 0) {
                sb.append("sal=" + emp.getSal() + ",");
            }
            if (emp.getComm() != 0) {
                sb.append("comm=" + emp.getComm() + ",");
            }
            if (emp.getEmpno() != 0) {
                sb.append("empno=" + emp.getDeptno() + ",");
            }
            String sql = sb.toString();
            if (sql.endsWith(",")) {//sql语句中属性以，结尾表明sql语句已追加完毕
                sql = sql.substring(0, sql.length() - 1);//去除末尾的逗号
            }
            sb = new

                    StringBuilder(sql);
            sb.append(" where empno=?");
        }
        return super.executeSql(sb.toString(), new Object[]{emp.getEmpno()});
    }
@Override
public int delemp(int empno){
        String sql="delete form emp where empno = ?";
        return super.executeSql(sql.toString(),new Object[]{empno});
        }
 之所以使用 分为各个层次来进行写这个项目，因为项目分层可以更加有利于团队的分工合作，更好的完成不同的模块功能。也有利于规范代码。另外缺少oracle jdbc驱动包的可以前往https://download.csdn.net/download/jungle_yao/10569722下载使用