jdbc实现增删改查

JDBC增删改查

代码展示：

实体类：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class SelectALL {
    // 编号
    private int id;
    // 姓名
    private String name;
    // 性别
    private int gender;
    // 入职时间
    private Date createTime;
    // 工资
    private Double salary;
    // 修改时间
    private Date updateTime;
    // 部门
    private  String departName;

}

1、查找

public List<SelectALL> select() throws SQLException {
        // 创建连接
        conn = JbConnection.getConn();
        // sql语句
        String sql = "select * from employee, department where dept_id = department.id order by employee.id; ";
        // 获取预编译对象
        ps = conn.prepareStatement(sql);
        // 执行sql，获取结果集
        rs = ps.executeQuery();
        List<SelectALL> list = new ArrayList<>();
        // 处理结果集
        while(rs.next()){
            SelectALL e = new SelectALL();
            // 编号
            e.setId(rs.getInt(1));
            // 用户名
            e.setName(rs.getString(2));
            // 性别
            e.setGender(rs.getInt(3));
            // 入职时间
            e.setCreateTime(rs.getDate(5));
            // 修改时间
            e.setUpdateTime(rs.getDate(6));
            // 工资
            e.setSalary(rs.getDouble(7));
            // 部门
            e.setDepartName(rs.getString(9));
            list.add(e);
        }
        conn.close();
        return list;
    }

2、分页查找

public List<SelectALL> pageBean(int begin, int pageSize) throws SQLException {
        // 创建连接
        conn = JbConnection.getConn();
        // sql语句
        String sql = "select * from employee, department where dept_id = department.id order by employee.id limit ?, ? ";
        // 获取预编译对象
        ps = conn.prepareStatement(sql);
        ps.setInt(1, begin);
        ps.setInt(2, pageSize);
        // 执行sql，获取结果集
        rs = ps.executeQuery();
        List<SelectALL> list = new ArrayList<>();
        while(rs.next()){
            SelectALL e = new SelectALL();
            // 编号
            e.setId(rs.getInt(1));
            // 用户名
            e.setName(rs.getString(2));
            // 性别
            e.setGender(rs.getInt(3));
            // 入职时间
            e.setCreateTime(rs.getDate(5));
            // 修改时间
            e.setUpdateTime(rs.getDate(6));
            // 工资
            e.setSalary(rs.getDouble(7));
            // 部门
            e.setDepartName(rs.getString(9));
            list.add(e);

        }
        return list;
    }

3、添加员工

 public boolean add(Employee employee) throws SQLException {
        // 创建连接
        conn = JbConnection.getConn();
        // sql语句
        String sql = "insert into employee(name, gender, dept_id, create_time, update_time, salary) values (?, ?, ?, ?, ?, ?)";
        // 获取预编译对象
        ps = conn.prepareStatement(sql);
        // 添加查询属性
        ps.setString(1, employee.getName());
        ps.setInt(2, employee.getGender());
        ps.setInt(3, employee.getDeptId());
        ps.setDate(4, new Date(new java.util.Date().getTime()));
        ps.setDate(5, new Date(new java.util.Date().getTime()));
        ps.setDouble(6, employee.getSalary());
        int p = ps.executeUpdate();
        return p != 0;
    }

4、修改员工信息

public boolean update(Employee employee, int id) throws SQLException {
        // 创建连接
        conn = JbConnection.getConn();
        // sql语句
        String sql = "update employee set name = ?, dept_id = ?,create_time = ?, update_time = ?, salary = ? where id = ?";
        // 获取预编译对象
        ps = conn.prepareStatement(sql);
        // 添加修改元素
        ps.setString(1, employee.getName());
        ps.setInt(2, employee.getDeptId());
        ps.setDate(3, (Date) employee.getCreateTime());
        ps.setDate(4, (Date) employee.getUpdateTime());
        ps.setDouble(5, employee.getSalary());
        ps.setInt(6, id);
        int p = ps.executeUpdate();
        return p != 0;
    }

5、删除员工信息

 public boolean delete(int id) throws SQLException {
        // 建立连接
        conn = JbConnection.getConn();
        // sql语句
        String sql = "delete from employee where id = ?";
        // 添加删除属性
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        int p = ps.executeUpdate();
        return p != 0;
    }

6、模糊查询

public List<SelectALL> findName(String name) throws SQLException {
        // 建立连接
        conn = JbConnection.getConn();
        // sql语句
        String sql = "select * from employee, department " +
                "where dept_id = department.id and " +
                "(employee.name like concat('%', ?, '%'))";
        // 预编译
        ps = conn.prepareStatement(sql);
        ps.setString(1, name);
        // 获取结果集
        rs = ps.executeQuery();
        List<SelectALL> list = new ArrayList<>();
        while(rs.next()) {
            SelectALL e = new SelectALL();
            // 编号
            e.setId(rs.getInt(1));
            // 用户名
            e.setName(rs.getString(2));
            // 性别
            e.setGender(rs.getInt(3));
            // 入职时间
            e.setCreateTime(rs.getDate(5));
            // 修改时间
            e.setUpdateTime(rs.getDate(6));
            // 工资
            e.setSalary(rs.getDouble(7));
            // 部门
            e.setDepartName(rs.getString(9));
            list.add(e);
        }
        return list;
    }