JDBC实现CRUD

操作和访问数据库

数据库连接被用于向数据库服务器发送命令和SQL语句，并接受数据库服务器返回的结果，数据库连接是一个Socket连接；
java.sql包有3个接口定义了对数据库调用的不同方式：

• Statement：执行静态SQL语句并返回生成的结果的对象；
• PrepatedStatement：SQL语句被预编译并且存储在此对象中，可以使用此对象多次高效地执行该语句；
• CallableStatement：执行SQL存储过程；

使用Statement操作数据表地弊端：

• Statement：不能避免SQL注入；
• PrepatedStatement：可以避免SQL注入；

CRUD

实体类

public class emp {
    private int empno;
    private String ename;
    private String job;
    private Date hiredate;
    private float sal;
}

以上代码省略了get、set、toString、无参构造、全参构造。

CRUD接口

public interface empCRUD {
    int addEmp(emp e) throws SQLException, ClassNotFoundException;
    int deleteByEmpno(int empno) throws SQLException, ClassNotFoundException;
    int updateEnameByEmpno(int empno,String name) throws SQLException, ClassNotFoundException;
    List<emp> empList() throws SQLException, ClassNotFoundException;
}

CRUD实现类

public class empCRUDImpl implements empCRUD{
    private static Connection conn;
    //得到连接
    public static Connection getConn() throws ClassNotFoundException, SQLException {}
    //增
    @Override
    public int addEmp(emp e) throws SQLException, ClassNotFoundException {}
    //删
    @Override
    public int deleteByEmpno(int empno) throws SQLException, ClassNotFoundException {}
    //改
    @Override
    public int updateEnameByEmpno(int empno,String name) throws SQLException, ClassNotFoundException {}
    //查
    @Override
    public List<emp> empList() throws SQLException, ClassNotFoundException {}
}

连接

private static Connection conn;
    public static Connection getConn() throws ClassNotFoundException, SQLException {
            if(conn!=null) return conn;
            Properties properties = new Properties();
            String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT";
            properties.setProperty("user","root");
            properties.setProperty("password","123456");
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn= DriverManager.getConnection(url,properties);
            return conn;
    }

增

@Override
    public int addEmp(emp e) throws SQLException, ClassNotFoundException {
        Connection conn=getConn();
        String sql="insert into emp(empno,ename,job,sal,hiredate) values (?,?,?,?,?)";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setInt(1,e.getEmpno());
        ps.setString(2,e.getEname());
        ps.setString(3,e.getJob());
        ps.setFloat(4,e.getSal());
        ps.setDate(5,new Date(e.getHiredate().getTime()));
        int result=ps.executeUpdate();
        ps.close();
        conn.close();
        return result;
    }

删

@Override
    public int deleteByEmpno(int empno) throws SQLException, ClassNotFoundException {
        Connection conn = getConn();
        String sql="delete from emp where empno=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setInt(1,empno);
        int n=ps.executeUpdate();
        ps.close();
        conn.close();
        return n;
    }

改

@Override
    public int updateEnameByEmpno(int empno,String name) throws SQLException, ClassNotFoundException {
        Connection conn=getConn();
        String sql="update emp set ename=? where empno=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,name);
        ps.setInt(2,empno);
        int result=ps.executeUpdate();
        ps.close();
        conn.close();
        return result;
    }

查

 @Override
    public List<emp> empList() throws SQLException, ClassNotFoundException {
        Connection conn=getConn();
        String sql="select * from emp";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<emp> list=new ArrayList<>();
        while (rs.next()){
            emp emp = new emp();
            emp.setEmpno(rs.getInt("empno"));
            emp.setEname(rs.getString("ename"));
            emp.setHiredate(rs.getDate("hiredate"));
            emp.setJob(rs.getString("job"));
            emp.setSal(rs.getFloat("sal"));
            list.add(emp);
        }
        rs.close();
        ps.close();
        conn.close();
        return list;
    }