Java连接数据库-JDBC

 

 entity...

dao层:

//把数据库的属性抽取到该类中  2. static 加载驱动  3.获取链接  4、关闭资源
//dao类中公共的代码抽取到该类中。提高代码的重用性。
public class BaseDao { //父类----
    public  Connection connection;
    public  PreparedStatement ps;
    public  ResultSet resultSet;
    //加载驱动
    //静态代码块会随着类的加载而被加载 而且只会被加载一次。
    static {
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
         }catch (Exception e){
            e.printStackTrace();
        }
    }
    //获取链接对象
    public  void getConnection() throws Exception{
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/world?serverTimezone=Asia/Shanghai", "root", "root");
    }

    //关闭资源
    public  void closeAll(){
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (connection != null) {
                connection.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

 详细，但又重复，后续更新简化代码

public class UserDao extends BaseDao {
    //查询所有
    public List<User> findAll() {
        List<User> list=new ArrayList<>();
        try {
            getConnection();
            String sql = "select * from tb_user";
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()){
                User user = new User();
                //为java对象得属性赋值
                user.setUid(resultSet.getInt("uid"));
                user.setUname(resultSet.getString("uname"));
                user.setPassword(resultSet.getString("password"));
                user.setSex(resultSet.getString("sex"));
                list.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
           closeAll();
        }
        return list;
    }


    //根据id查询 1
    public User findById(Integer id){
        User user=null;//声明对象
        try {
            getConnection();
            String sql = "select * from tb_user where uid=?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, id);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                user = new User();
                //为java对象得属性赋值
                user.setUid(resultSet.getInt("uid"));
                user.setUname(resultSet.getString("uname"));
                user.setPassword(resultSet.getString("password"));
                user.setSex(resultSet.getString("sex"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
           closeAll();
        }
        return user;

    }
    //删除 --- id删除
    public int deleteById(int id){
        try {
            getConnection();
            String sql = "delete from tb_user where uid=?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, id);
            int i = ps.executeUpdate();
            return i;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return 0;
    }

    //修改
    public int update(User user){
        try {
            getConnection();
            String sql = "update tb_user set uname=?,sex=?,password=? where uid=?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, user.getUname());
            ps.setObject(2, user.getSex());
            ps.setObject(3, user.getPassword());
            ps.setObject(4, user.getUid());
            int i = ps.executeUpdate();
            return i;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
           closeAll();
        }
        return 0;
    }
    //增加
    public int insert(User user){
        try {
            getConnection();
            String sql = "insert into tb_user(uid,uname,password,sex) values(null,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, user.getUname());  //要为占位符赋值
            ps.setObject(2, user.getPassword());
            ps.setObject(3, user.getSex());
            int i = ps.executeUpdate();
            return i;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return 0;
    }
}

 测试:

public class TestJDBC {
    UserDao userDao=new UserDao();
    //单元测试方法都没有返回值 参数
    @Test
    public void testSelectAll(){
        List<User> list = userDao.findAll();
        for (User u:list) {
            System.out.println(u.getUid()+"\t"+u.getUname());
        }
    }

    @Test
    public void testSelectById(){
        User user = userDao.findById(4);
        System.out.println(user.getUid()+"\t"+user.getUname());
    }
}