JDBC使用和封装

谁践踏了优雅 2023-10-08 12:38 86阅读 0赞

> 本文章主要描述的是如何使用JDBC，以及对JDBC的进行封装。主要的目的是降低代码的重复性，提高代码复用性。本文章通过四个版本来对JDBC进行封装。在了解JDBC如何使用的同时，还能够体会到Java封装的魅力！

**目录**

1.前提工作

1.1 数据库表

1.2 建立一个maven工程

1.2.1 创建工程的步骤

1.3 添加jar包依赖

1.4 创建实体类

2. jdbc\_version\_1

2.1 JDBC使用步骤

2.2 JDBC使用代码

3. jdbc\_version\_2

3.1 封装jdbc连接

3.2 测试封装的JDBC工具类

4. jdbc\_version\_3

4.1 创建db.properties

4.2 加载数据库连接信息

4.3 创建jdbcTemplate

4.4 测试jdbcTemplate

5. jdbc\_version\_4

5.1 创建IRowMapper接口

5.2 实现IRowMapper接口

5.3 jdbcTemplate改动

5.4 测试

6. 结语

--------------------

## 1.前提工作 ##

### 1.1 数据库表 ###

*  teacher表

CREATE TABLE `teacher` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
      `name` varchar(50) NOT NULL DEFAULT '0' COMMENT '教师姓名',
      `age` int(11) DEFAULT NULL COMMENT '年龄',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='教师表';

*  student表

CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `age` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='学生表';

### 1.2 建立一个maven工程 ###

*PS:如果没有安装maven的小伙伴可以自行 **百度**安装一下。*

#### **1.2.1 创建工程的步骤** ####

**（1）打开idea**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw_size_16_color_FFFFFF_t_70][]

**（2）选择maven工程**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw_size_16_color_FFFFFF_t_70 1][]

**（3）填写工程名和组名**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw_size_16_color_FFFFFF_t_70 2][]

**（4）这样就创建好了一个maven工程**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw_size_16_color_FFFFFF_t_70 3][]

### 1.3 添加jar包依赖 ###

*PS：一共涉及到两个jar包 一个是 数据库连接包 另一个是 lombok包（本文章用lombok包主要是通过注解的方式，自动完成实体类的 setter和getter）*

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.hjd.jdbc</groupId>
        <artifactId>jdbc</artifactId>
        <version>1.0-SNAPSHOT</version>
        <dependencies>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.21</version>
            </dependency>
            
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.12</version>
                <scope>provided</scope>
            </dependency>
        </dependencies>
    
    </project>

### 1.4 创建实体类 ###

*  Teacher实体类

package com.hjd.jdbc.entity;
    
    import lombok.Data;
    
    /**
     * @Author: 我玩亚索我会C
     */
    @Data
    public class Teacher {
        /**
         * ID
         */
        private int id;
        /**
         * 名字
         */
        private String name;
        /**
         * 年龄
         */
        private int age;
    }

*  Student实体类

package com.hjd.jdbc.entity;
    
    import lombok.Data;
    
    /**
     * @Author: 我玩亚索我会C
     */
    @Data
    public class Student {
        /**
         * id
         */
        private int id;
        /**
         * 名字
         */
        private String name;
        /**
         * 年龄
         */
        private int age;
    }

到此万事俱备！

## 2. jdbc\_version\_1 ##

### 2.1 JDBC使用步骤 ###

（1）定义数据库连接信息

（2）加载驱动获取连接对象

（3）执行sql

（4）返回结果

（5）关闭连接资源（关闭的时候要注意倒着关闭，如：连接资源打开顺序是 Connection conn、PrepareStatement ps、ResultSet result；那么关闭的顺序就是：reuslt、ps、conn；代码中会有体现）

### 2.2 JDBC使用代码 ###

package com.hjd.jdbc.version_1;
    
    import java.sql.*;
    
    /**
     * @Author: 我玩亚索我会C
     */
    public class Jdbc {
        /**
         * jdbc驱动
         */
        public static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        /**
         * 数据库地址  url
         */
        public static final String JDBC_URL = "jdbc:mysql://192.168.31.206:3306/testdb";
        /**
         * 数据库用户名
         */
        public static final String USER_NAME = "root";
        /**
         * 数据库密码
         */
        public static final String USER_PASSWORD = "123";
    
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet result = null;
            try {
                //加载驱动
                Class.forName(JDBC_DRIVER);
                //获取连接对象
                conn = DriverManager.getConnection(JDBC_URL, USER_NAME, USER_PASSWORD);
                //预编译SQL
                //查
                //增
                //删
                //改
                ps = conn.prepareStatement("select * from teacher where name = ?");
                //设置参数
                ps.setObject(1, "张三");
                //执行SQL,并获取结果集
                result = ps.executeQuery();
                //提取结果集 如果有数据，result.next()返回true
                while (result.next()) {
                    System.out.println(result.getObject("id") + " " + result.getObject("name") + " " + result.getObject("age"));
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                //关闭连接
                try {
                    if (null != result) {
                        result.close();
                    }
                    if (null != ps) {
                        ps.close();
                    }
                    if (null != conn) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

> **思考**：上面的代码只演示了查询teacher表中name为指定参数的数据。我们可以想一下，如果想要查询该表中的所有数据SQL就会不一样，那么我们还得重写把上面的代码写一遍，如果想要实现增、删、改还需要写同样的代码。那么代码就会非常冗余，同时工作量也变得非常大。可以看到上面的代码有很多都是重复的，比如：获取连接对象、关闭资源；那么我们可以把连接数据库操作封装成一个工具类Util，谁想用直接调用即可！

## 3. jdbc\_version\_2 ##

### 3.1 封装jdbc连接 ###

package com.hjd.jdbc.version_2.utils;
    
    import java.io.IOException;
    import java.sql.*;
    
    /**
     * @Author: 我玩亚索我会C
     */
    public class JdbcUtil {
        /**
         * 加载驱动
         */
        static {
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    
    
        /**
         * 获取jdbc连接
         *
         * @param
         * @return
         */
        public static Connection getConn() throws IOException, ClassNotFoundException, SQLException {
            Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.31.206:3306/testdb", "root", "123");
            return conn;
        }
    
        /**
         * 关闭资源
         *
         * @param conn
         * @param ps
         * @param result
         */
        public static void close(Connection conn, PreparedStatement ps, ResultSet result) throws SQLException {
            if (null != result) {
                result.close();
            }
            if (null != ps) {
                ps.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }

### **3.2 测试封装的JDBC工具类** ###

package com.hjd.jdbc.version_2.test;
    import com.hjd.jdbc.entity.Teacher;
    import com.hjd.jdbc.version_2.utils.JdbcUtil;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @Author: 我玩亚索我会C
     */
    public class TestJdbc {
    
        /**
         * Connection对象
         */
        private static Connection conn;
    
        /**
         * PreparedStatement对象
         */
        private static PreparedStatement ps;
    
        /**
         * ResultSet 结果集
         */
        private static ResultSet result;
    
    
        public static void main(String[] args) {
            TestJdbc testJdbc = new TestJdbc();
            Teacher teacher = new Teacher();
            teacher.setId(8);
            teacher.setName("胡八一");
            teacher.setAge(23);
            testJdbc.insert(teacher);
    
            teacher.setName("胡建军");
            teacher.setAge(25);
            testJdbc.update(teacher);
    
            testJdbc.delete(2);
    
            Teacher selectResult = testJdbc.select(8);
            System.out.println(selectResult.toString());
    
            List<Teacher> teachers = testJdbc.queryList();
            for (int i = 0; i < teachers.size(); i++) {
                System.out.println(teachers.get(i).toString());
            }
    
        }
    
    
        /**
         * 增
         */
        public void insert(Teacher teacher) {
            try {
                conn = JdbcUtil.getConn();
                String sql = "insert into teacher(id,TeaName,age) values(?,?,?)";
                ps = conn.prepareStatement(sql);
                //设置参数
                ps.setObject(1,teacher.getId());
                ps.setObject(2,teacher.getName());
                ps.setObject(3,teacher.getAge());
                ps.executeUpdate();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtil.close(conn,ps,result);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    
        /**
         * 删
         * @param id
         */
        public void delete(int id) {
            try {
                conn = JdbcUtil.getConn();
                String sql = "delete from teacher where id = ?";
                ps = conn.prepareStatement(sql);
                //设置参数
                ps.setObject(1,id);
                ps.executeUpdate();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtil.close(conn,ps,result);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    
        /**
         * 改
         * @param teacher
         */
        public void update(Teacher teacher) {
            try {
                conn = JdbcUtil.getConn();
                String sql = "update teacher set TeaName = ?,age = ? where id = ?";
                ps = conn.prepareStatement(sql);
                //设置参数
                ps.setObject(1,teacher.getName());
                ps.setObject(2,teacher.getAge());
                ps.setObject(3,teacher.getId());
                ps.executeUpdate();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtil.close(conn,ps,result);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    
        /**
         * 根据Id查
         */
        public Teacher select(int id) {
            try {
                conn = JdbcUtil.getConn();
                String sql = "select * from teacher where id = ?";
                ps = conn.prepareStatement(sql);
                //设置参数
                ps.setObject(1,id);
                result = ps.executeQuery();
                Teacher teacher = new Teacher();
                while (result.next()) {
                    teacher.setId(result.getInt("id"));
                    teacher.setName(result.getString("TeaName"));
                    teacher.setAge(result.getInt("age"));
                }
                return teacher;
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtil.close(conn,ps,result);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return null;
        }
    
        /**
         * 查询
         * @return
         */
        public List<Teacher> queryList(){
            try {
                conn = JdbcUtil.getConn();
                String sql = "select * from teacher";
                ps = conn.prepareStatement(sql);
                result = ps.executeQuery();
                List<Teacher> teacherList = new ArrayList<Teacher>();
                while (result.next()) {
                    Teacher teacher = new Teacher();
                    teacher.setId(result.getInt("id"));
                    teacher.setName(result.getString("TeaName"));
                    teacher.setAge(result.getInt("age"));
                    teacherList.add(teacher);
                }
                return teacherList;
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtil.close(conn,ps,result);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return null;
        }
    
    }

> **思考：**在上面的代码中我们确实不需要再写那些繁琐而重复的连接数据以及关闭连接的代码了。但是这样还是不够完善，原因有两点
> 
> 第一点：在JdbcUtil中我们把连接数据的 驱动、数据库Url、用户名、密码这些信息都写死在了工具类中，比如：你连接数据库的Url发生了改变或者是你像连接Oracle数据等，这些都需要修改代码，这样属于硬编码，对后期的维护工作很不友好。可以把连接数据库的这些信息都放到一个xx.properties配置文件中。
> 
> 第二点：在TestJdbc中有很多代码都是重复的比如：设置SQL查询的参数，希望达到的效果是，在调用的时候我们只需要传递参数和SQL语句即可！那么我们需要创建一个jdbcTemplate模板类。

## 4. jdbc\_version\_3 ##

### 4.1 创建db.properties ###

在项目的resource目录下创建db.properties内容如下：

#注意属性值不要带双引号
    JDBC_DRIVER=com.mysql.jdbc.Driver
    JDBC_URL=jdbc:mysql://192.168.31.206:3306/testdb
    USER_NAME=root
    USER_PASSWORD=123

### 4.2 加载数据库连接信息 ###

package com.hjd.jdbc.version_3.utils;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.sql.*;
    import java.util.Properties;
    
    /**
     * @Author:我玩亚索我会C
     */
    public class JdbcUtil {
    
        /**
         * 获取jdbc连接
         *
         * @param
         * @return
         */
        public static Connection getConn(){
            Connection conn = null;
            try {
                //从配置文件中读取数据库连接属性。
                Properties p = new Properties();
                //注意这里需要用斜杠 / 不要用点 .
                FileInputStream is = new FileInputStream("src/main/resources/db.properties");
                p.load(is);
                Class.forName(p.getProperty("JDBC_DRIVER"));
                conn = DriverManager.getConnection(p.getProperty("JDBC_URL"), p.getProperty("USER_NAME"), p.getProperty("USER_PASSWORD"));
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }
    
        /**
         * 关闭资源
         *
         * @param conn
         * @param ps
         * @param result
         */
        public static void close(Connection conn, PreparedStatement ps, ResultSet result) throws SQLException {
            if (null != result) {
                result.close();
            }
            if (null != ps) {
                ps.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }

### 4.3 创建jdbcTemplate ###

package com.hjd.jdbc.version_3.templates;
    import com.hjd.jdbc.entity.Teacher;
    import com.hjd.jdbc.version_3.utils.JdbcUtil;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @Author: 我玩亚索我会C
     */
    public class JdbcTemplate {
        /**
         * 连接对象
         */
        private static Connection conn = null;
        /**
         * PreparedStatement 对象
         */
        private static PreparedStatement ps = null;
        /**
         * ResultSet 对象
         */
        private static ResultSet result = null;
        /**
         * 增加
         * @param sql
         * @param params 参数列表
         */
        public static void insert(String sql,Object...params){
            aboutUpdate(sql,params);
        }
    
        /**
         * 删除
         * @param sql
         * @param params
         */
        public static void delete(String sql,Object...params){
            aboutUpdate(sql,params);
        }
    
        /**
         * 修改
         * @param sql
         * @param params
         */
        public static void update(String sql,Object...params){
            aboutUpdate(sql,params);
        }
    
        /**
         * 根据id查询单个信息
         * @param sql
         * @param id
         */
        public static Teacher selectOne(String sql,int id){
            try {
                conn = JdbcUtil.getConn();
                ps = conn.prepareStatement(sql);
                ps.setInt(1,id);
                result = ps.executeQuery();
                Teacher t = new Teacher();
                while (result.next()){
                    t.setId(result.getInt("id"));
                    t.setName(result.getString("name"));
                    t.setAge(result.getInt("age"));
                }
                return t;
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtil.close(conn,ps,result);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return null;
        }
    
        /**
         * 查询多个
         * @param sql
         * @param params
         * @return
         */
        public static List<Teacher> queryList(String sql,Object...params){
            try {
                conn = JdbcUtil.getConn();
                ps = conn.prepareStatement(sql);
                for (int i = 0; i < params.length; i++) {
                    //注意设置参数是从 1 开始的
                    ps.setObject(i+1,params[i]);
                }
                result = ps.executeQuery();
                List<Teacher> teachers = new ArrayList<Teacher>();
                while (result.next()){
                    Teacher t = new Teacher();
                    t.setId(result.getInt("id"));
                    t.setName(result.getString("name"));
                    t.setAge(result.getInt("age"));
                    teachers.add(t);
                }
                return teachers;
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtil.close(conn,ps,result);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return null;
        }
    
        /**
         * 增删改方法
         * @param sql
         * @param params
         */
        private static void aboutUpdate(String sql,Object...params){
            try {
                conn = JdbcUtil.getConn();
                ps = conn.prepareStatement(sql);
                for (int i = 0; i < params.length; i++) {
                    //注意设置参数是从 1 开始的
                    ps.setObject(i+1,params[i]);
                }
                ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtil.close(conn,ps,result);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

### 4.4 测试jdbcTemplate ###

package com.hjd.jdbc.version_3.Test;
    
    import com.hjd.jdbc.entity.Teacher;
    import com.hjd.jdbc.version_3.templates.JdbcTemplate;
    
    import java.util.List;
    
    /**
     * @Author: 我玩亚索我会C
     */
    public class Test {
        public static void main(String[] args) {
            Teacher teacher = new Teacher();
            teacher.setId(14);
            teacher.setName("王凯旋");
            teacher.setAge(26);
            insert(teacher);
            update(teacher);
            delete(11);
            List<Teacher> teachers = queryList();
            teachers.forEach(t->{
                System.out.println(t.toString());
            });
        }
        public static void insert(Teacher teacher){
            String sql = "insert into teacher(id,name,age) values(?,?,?)";
            Object[] objects = {teacher.getId(), teacher.getName(), teacher.getAge()};
            JdbcTemplate.insert(sql,objects);
        }
        public static void delete(int id){
            String sql = "delete from teacher where id = ?";
            Object[] objects = {id};
            JdbcTemplate.delete(sql,objects);
        }
        public static void update(Teacher teacher){
            String sql = "update teacher set name = ? ,age = ? where id = ?";
            Object[] objects = {teacher.getName(), teacher.getAge(),teacher.getId()};
            JdbcTemplate.update(sql,objects);
        }
        public static void selectOne(int id){
            String sql = "select * from teacher where id = ?";
            JdbcTemplate.selectOne(sql,id);
        }
        public static List<Teacher> queryList(){
            String sql = "select * from teacher";
            return JdbcTemplate.queryList(sql);
        }
    
    }

上面的测试代码是不是看着简洁明了很多。

> 思考：上面的代码还是不够完善，原因是jdbcTemplate只能处理Teacher实体类有关的增、删、改、查，如果换成Student实体类就不能使用了，代码的可复用性很差。原因是Teacher和Student的字段不一样，我们在返回结果时需要知道实体类有多少字段。所以我们需要一个IRowMapper字段映射的类来去处理不同的实体类。

## 5. jdbc\_version\_4 ##

### 5.1 创建IRowMapper接口 ###

package com.hjd.jdbc.version_4.IRows;
    import java.sql.ResultSet;
    
    /**
     * @Author: 我玩亚索我会C
     */
    public interface IRowsMapper<T> {
        /**
         * 数据行映射
         * @param result
         * @return
         * @throws Exception
         */
        T rowMapper(ResultSet result) throws Exception;
    }

### 5.2 实现IRowMapper接口 ###

（1）Teacher实体类字段映射

package com.hjd.jdbc.version_4.IRows.impl;
    import com.hjd.jdbc.entity.Teacher;
    import com.hjd.jdbc.version_4.IRows.IRowsMapper;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @Author: 我玩亚索我会C
     */
    public class TeachersRowMapper implements IRowsMapper<List<Teacher>> {
        @Override
        public List<Teacher> rowMapper(ResultSet result) throws Exception {
            List<Teacher> teachers = new ArrayList<Teacher>();
            while (result.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(result.getInt("id"));
                teacher.setName(result.getString("name"));
                teacher.setAge(result.getInt("age"));
                teachers.add(teacher);
            }
            return teachers;
        }
    }

（2）Student实体类字段映射

package com.hjd.jdbc.version_4.IRows.impl;
    import com.hjd.jdbc.entity.Student;
    import com.hjd.jdbc.version_4.IRows.IRowsMapper;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @Author:我玩亚索我会C
     */
    public class StudentsRowMapper implements IRowsMapper<List<Student>> {
        @Override
        public List<Student> rowMapper(ResultSet result) throws Exception {
            List<Student> students= new ArrayList<Student>();
            while (result.next()) {
                Student student = new Student();
                student.setId(result.getInt("id"));
                student.setName(result.getString("name"));
                student.setAge(result.getInt("age"));
                students.add(student);
            }
            return students;
        }
    }

（3）通用的查询数据条数的实现

package com.hjd.jdbc.version_4.IRows.impl;
    import com.hjd.jdbc.version_4.IRows.IRowsMapper;
    import java.sql.ResultSet;
    
    /**
     * @Author: 我玩亚索我会C
     * @Date: 2020/12/4 0004 21:55
     */
    public class CountMapper implements IRowsMapper<Integer> {
        @Override
        public Integer rowMapper(ResultSet result) throws Exception {
            int count = 0;
            while (result.next()) {
                count++;
            }
            return count;
        }
    }

### 5.3 jdbcTemplate改动 ###

public static <T> T query(String sql, IRowsMapper<T> iRowsMapper,Object...params){
            try {
                conn = JdbcUtil.getConn();
                ps = conn.prepareStatement(sql);
                //設置值
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i+1,params[i]);
                }
                result = ps.executeQuery();
                T t = iRowsMapper.rowMapper(result);
                return t;
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    JdbcUtil.close(conn,ps,result);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return null;
        }

用这一个方法可以实现查询集合（List<实体类>）和数据条数（count），这就是泛型优点！

### 5.4 测试 ###

package com.hjd.jdbc.version_4.Test;
    
    import com.hjd.jdbc.entity.Student;
    import com.hjd.jdbc.entity.Teacher;
    import com.hjd.jdbc.version_4.IRows.IRowsMapper;
    import com.hjd.jdbc.version_4.IRows.impl.CountMapper;
    import com.hjd.jdbc.version_4.IRows.impl.StudentsRowMapper;
    import com.hjd.jdbc.version_4.IRows.impl.TeachersRowMapper;
    import com.hjd.jdbc.version_4.templates.JdbcTemplate;
    
    import java.util.List;
    
    /**
     * @Author: 我玩亚索我会C
     * @Date: 2020/12/3 0003 20:41
     */
    public class Test {
        public static void main(String[] args) {
            List<Teacher> teachers = queryTeacher();
            teachers.forEach(teacher -> {
                System.out.println(teacher.toString());
            });
    
            Integer count = queryTeacherCount();
            System.out.println(count);
    
            List<Student> students = queryStudent();
            students.forEach(student -> {
                System.out.println(student.toString());
            });
    
            Integer count2 = queryStudentCount();
            System.out.println(count2);
    
        }
    
        //TODO 增删改和前面逻辑一样
    
        public static List<Teacher> queryTeacher(){
            String sql = "select * from teacher where TeaName = ?";
            //创建查询teacher对象的mapper
            TeachersRowMapper teachersRowMapper = new TeachersRowMapper();
            Object[] params = {"test"};
            List<Teacher> query = JdbcTemplate.query(sql, teachersRowMapper,params);
            return query;
        }
        public static Integer queryTeacherCount(){
            String sql ="select * from teacher where TeaName like ?";
            CountMapper countMapper = new CountMapper();
            Object[] params = {"胡%"};
            Integer count = JdbcTemplate.query(sql, countMapper,params);
            return count;
        }
        public static List<Student> queryStudent(){
            String sql = "select * from student";
            //创建查询student对象的mapper
            StudentsRowMapper studentsRowMapper = new StudentsRowMapper();
            List<Student> students = JdbcTemplate.query(sql, studentsRowMapper);
            return students;
        }
        public static Integer queryStudentCount(){
            String sql = "select * from student where name like ?";
            CountMapper countMapper = new CountMapper();
            Object[] params = {"赵%"};
            Integer count = JdbcTemplate.query(sql,countMapper,params);
            return count;
        }
    
    }

> 目前就封装到这里！

## 6. 结语 ##

上面的代码还有很多不足的地方：

1.  单表的增、删、改、查也可以进一步完善，用的人只需要调用即可。
2.  只要新增一个实体类比如：Class也必须实现IRowMapper接口，如果实体类很多，这样写也是很麻烦，代码很多的。
3.  .....等等

如果有时间我会继续更新，希望我写的东西对你们有一点点帮助，如果有什么问题我们可以在评论区进行交流！

记得点个赞，支持一下~~谢谢各位看官啦~~

***不积跬步无以至千里，不积小流无以成江海***

![320451abe46411762578dc80d3d8d7c9.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201206124634866.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201206124725197.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201206124934627.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201206125051477.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzg1MjUw,size_16,color_FFFFFF,t_70
[320451abe46411762578dc80d3d8d7c9.png]: https://img-blog.csdnimg.cn/img_convert/320451abe46411762578dc80d3d8d7c9.png