【精品】JDBC模板类

1、准备工作

数据库

对应的SQL语句：

CREATETABLE`tb_user`(`id`intNOTNULLAUTO_INCREMENTCOMMENT'编号',`name`varchar(5)DEFAULTNULLCOMMENT' 姓名',`gender`bit(1)DEFAULTNULLCOMMENT'性别',`tel`char(11)DEFAULTNULLCOMMENT'电话',`credits`intDEFAULTNULLCOMMENT'总的积分',PRIMARYKEY(`id`),UNIQUEKEY`tel`(`tel`))ENGINE=InnoDBAUTO_INCREMENT=109DEFAULTCHARSET=utf8mb3 COMMENT='用户表';

实体类

@Getter@Setter@NoArgsConstructor@AllArgsConstructor@Builder@ToStringpublicclassUser{privateInteger id;privateString name;privateBoolean gender;privateString tel;privateInteger credits;publicUser(String name,Boolean gender,String tel,Integer credits){this.name = name;this.gender = gender;this.tel = tel;this.credits = credits;}}

JDBC工具类

参考博客：JDBC工具类

2、定义JDBC模板

定义Handler接口，用来规范处理结果集的接口

publicinterfaceResultSetHandler<T>{Tprocess(ResultSet rSet);}

自定义Handler实现类

查询有多少条的

publicclassScalarHandler<T>implementsResultSetHandler<T>{privateClass<T> clazz;publicBeanHandler(Class<T> clazz){this.clazz = clazz;}@OverridepublicTprocess(ResultSet rs){T res =null;try{while(rs.next()){
                res =(T) rs.getObject(1);break;}}catch(Exception e){
            e.printStackTrace();}return res;}}

将结果转换成对象的

publicclassBeanHandler<T>implementsResultSetHandler<T>{privateClass<T> clazz;publicBeanHandler(Class<T> clazz){this.clazz = clazz;}@OverridepublicTprocess(ResultSet rs){// 创建 List 用于存放装箱后的对象try{// 获取类的属性描述符BeanInfo beanInfo =Introspector.getBeanInfo(clazz,Object.class);PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();// 对结果集进行装箱操作while(rs.next()){T obj = clazz.getDeclaredConstructor().newInstance();for(PropertyDescriptor pd : pds){Object value = rs.getObject(pd.getName());
                    pd.getWriteMethod().invoke(obj, value);}return obj;}}catch(Exception e){
            e.printStackTrace();}returnnull;}}

将结果集转换成对象集合的

publicclassBeanListHandler<T>implementsResultSetHandler<List<T>>{privateClass<T> clazz;publicBeanHandler(Class<T> clazz){this.clazz = clazz;}@OverridepublicList<T>process(ResultSet rs){// 创建 List 用于存放装箱后的对象List<T> list =newArrayList<T>();try{// 获取类的属性描述符BeanInfo beanInfo =Introspector.getBeanInfo(clazz,Object.class);PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();// 对结果集进行装箱操作while(rs.next()){T obj = clazz.getDeclaredConstructor().newInstance();for(PropertyDescriptor pd : pds){Object value = rs.getObject(pd.getName());
                    pd.getWriteMethod().invoke(obj, value);}
                list.add(obj);}}catch(Exception e){
            e.printStackTrace();}return list;}}

自定义模板类

publicclassJdbcTemplate{/**
     * 具类，私有化无参构造函数
     */privateJdbcTemplate(){}/**
     * DML 操作模板方法
     * @param sql  执行操作的 SQL 语句
     * @param args SQL 语句参数
     */publicstaticintupdate(String sql,Object... args){// 获取数据库连接 connConnection conn =JdbcUtil.getConnection();PreparedStatement ps =null;try{
            ps = conn.prepareStatement(sql);// 给预编译好的 sql 语句中的占位符进行赋值if(args !=null&& args.length >0){for(int i =0; i < args.length; i++){
                    ps.setObject(i +1, args[i]);}}// 执行 SQL 语句int res = ps.executeUpdate();return res;}catch(SQLException e){
            e.printStackTrace();}finally{// 释放数据库连接JdbcUtil.release(conn, ps,null);}return-1;}/**
     * DQL 操作模板
     * @param sql     执行操作的 SQL 语句
     * @param handler 对数据库返回结果集进行装箱的操作类
     * @param args    SQL 语句参数
     * @return 返回数据库查询结果集
     */publicstatic<T>Tquery(String sql,ResultSetHandler<T> handler,Object... args){Connection conn =JdbcUtil.getConnection();PreparedStatement ps =null;ResultSet rs =null;try{
            ps = conn.prepareStatement(sql);if(args !=null&& args.length >0){for(int i =0; i < args.length; i++){
                    ps.setObject(i +1, args[i]);}}
            rs = ps.executeQuery();// 调用处理结果集类对数据库查询结果集进行装箱return handler.process(rs);}catch(SQLException e){
            e.printStackTrace();}finally{// 释放数据库连接JdbcUtil.release(conn, ps, rs);}returnnull;}}

3、测试

UserDao接口

publicinterfaceUserDao{intinsert(User user);intdeleteById(Integer id);intupdate(User user);UserselectById(Integer id);List<User>list();longselectCount();StringselectNameById(Integer id);}

UserDaoImpl实现类

publicclassUserDaoImplimplementsUserDao{@Overridepublicintinsert(User user){String sql ="INSERT INTO tb_user(name, gender, tel,credits) VALUES(?, ?, ?, ?)";Object[] args =newObject[]{user.getName(), user.getGender(), user.getTel(), user.getCredits()};returnJdbcTemplate.update(sql, args);}@OverridepublicintdeleteById(Integer id){String sql ="DELETE FROM tb_user WHERE id = ?";returnJdbcTemplate.update(sql, id);}@Overridepublicintupdate(User user){String sql ="UPDATE tb_user SET name = ?,gender= ?,tel=?,credits=? WHERE id = ?";Object[] args =newObject[]{user.getName(), user.getGender(), user.getTel(), user.getCredits(), user.getId()};returnJdbcTemplate.update(sql, args);}@OverridepublicUserselectById(Integer id){String sql ="SELECT id, name,gender, tel,credits FROM tb_user WHERE id = ?";returnJdbcTemplate.query(sql,newBeanHandler<>(User.class), id);}@OverridepublicList<User>list(){String sql ="SELECT id, name, gender, tel,credits FROM tb_user";returnJdbcTemplate.query(sql,newBeanListHandler<>(User.class));}@OverridepublicLongselectCount(){String sql ="Select count(*) from tb_user";returnJdbcTemplate.query(sql,newScalarHandler(Long.class));}@OverridepublicStringselectNameById(Integer id){String sql ="select name from tb_user where id =?";String name =JdbcTemplate.query(sql,newScalarHandler<>(String.class), id);System.out.println(name);}}

测试代码

classUserDaoImplTest{privateUserDao userDao =newUserDaoImpl();@Testvoidsave(){User user =newUser("asdf",true,"32143214312",77);
        userDao.insert(user);}@Testvoiddelete(){int res = userDao.deleteById(108);System.out.println(res);}@Testvoidupdate(){User user =newUser(3,"asdf",true,"32143214312",77);int res = userDao.update(user);System.out.println(res);}@Testvoidget(){User user = userDao.selectById(5);System.out.println(user);}@Testvoidlist(){List<User> list = userDao.list();
        list.forEach(System.out::println);}@TestvoidselectCount(){}@TestvoidselectNameById(){}}