Java300集课程笔记整理3.0（JDBC）

文章目录

一、获取数据库连接的方式
二、实现对数据库的增删改
三、对数据表的查询操作
四、批量插入
五、创建baseDAO
六、使用Druid数据库连接池
七、使用DBUtils实现CRUD操作

学习视频（Java300集）：https://www.bilibili.com/video/BV1ct411n7oG

一、获取数据库连接的方式

基本步骤：

获取Driver实现类对象
提供要连接的数据库
提供连接需要的用户名和密码
获取连接

测试代码中使用@Test的方式，不用写main函数就可以运行单个类（class）

测试代码

方式一

package com.ljh.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.junit.Test;

public class ConnectionTest {

    //方式一
    @Test
    public void testConnection1() throws SQLException {

        //获取Driver的实现类对象
        Driver driver = new com.mysql.jdbc.Driver();//com.mysql.jdbc.Driver为第三方api

        
        String url = "jdbc:mysql://localhost:3306/test";
        Properties info = new Properties();
        //将用户名和密码封装在Properties中
        info.setProperty("user","root");
        info.setProperty("password","Myroot_ljh");

        //用Connection的接口connect进行连接
        Connection conn = driver.connect(url,info);

        System.out.println(conn);
    }

方式二：对方式一的迭代,程序中不存在第三方的api，使程序更具有可移植性

    //方式二：对方式一的迭代,程序中不存在第三方的api，使程序更具有可移植性。
    @Test
    public void testConnection2() throws Exception {

        //1，获取Driver实现类对象，使用反射（相当于方式一的Driver driver = new com.mysql.jdbc.Driver();）
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        //2，提供要连接的数据库
        String url = "jdbc:mysql://localhost:3306/test";

        //3，提供连接需要的用户名和密码
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","Myroot_ljh");

        //4，获取连接
        Connection conn = driver.connect(url,info);
        System.out.println(conn);
    }

方式三：对方式二的更改，用DriverManager替换Driver

    //方式三：对方式二的更改，用DriverManager替换Driver
    @Test
    public void testConnection3() throws Exception {

        //1，提供另外三个连接的基本信息
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "Myroot_ljh";

        //2，获取Driver实现类的对象
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();
        //注册驱动
        DriverManager.deregisterDriver(driver);

        //3，获取连接
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }

方式四：对方式三的优化，对第二步优化

    //方式四：对方式三的优化，对第二步优化
    @Test
    public void testConnection4() throws Exception {

        //1，提供另外三个连接的基本信息
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "Myroot_ljh";

        //2，加载Driver
        Class.forName("com.mysql.jdbc.Driver");//在MySQL中也可以省略，但不建议，因为不具有通用性
        

        //3，获取连接
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }

方式五：最终版，将数据库所需要连接的4个基本信息声明在配置文件中，通过读取文件的方式获取连接

    //方式五：将数据库所需要连接的4个基本信息声明在配置文件中，通过读取文件的方式获取连接
    
    @Test
    public void testConnection5() throws Exception {

        //1，读取配置文件中的4个基本信息
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros =new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //2，加载驱动Driver
        Class.forName(driverClass);

        //3，获取连接
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }

方式五中的配置文件：
测试结果：

二、实现对数据库的增删改

基本步骤

获取数据库的连接
预编译sql语句，获取PreparedStatement的实例
填充占位符（？）
执行
释放资源

测试代码

package com.ljh.jdbc;

import com.ljh.util.JDBCUtils;
import org.junit.Test;

import java.sql.*;
import java.text.ParseException;


public class PreparedStatementUpdateTest {

    //测试通用的操作方法
    @Test
    public void testCommonUpdate() throws ParseException {

        

        

        //3，修改一条记录
        String sql3 = "update students set name = ? where id = ?";
        update(sql3,"王菲",5);/
public class JDBCUtils {

    //获取数据库的连接
    public static Connection getConnection() throws Exception {

        //1，读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros =new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //2，加载驱动Driver
        Class.forName(driverClass);

        //3，获取连接
        Connection conn = DriverManager.getConnection(url,user,password);

        return conn;
    }

    //关闭连接和Statement的操作（释放资源）
    public static void closeResource(Connection conn, Statement ps) {

        //7，释放资源
        if(ps != null) {
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试结果：

三、对数据表的查询操作

Employee类：

package com.ljh.bean;

import java.sql.Date;


public class Employee {

    private long id;//java.lang里面对应的是long型，故要改成long才行
    private String name;
    private String email;
    private Date birth;

    //创建一个空方法
    public Employee(){

        super();
    }

    public Employee(int id, String name, String email, Date birth) {

        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public long getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", email='" + email + ''' +
                ", birth=" + birth +
                '}';
    }
}

针对某一个表的查询，返回一条记录

测试代码1.0

package com.ljh.jdbc;

import com.ljh.bean.Employee;
import com.ljh.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;


public class EmployeeForQuery {

    //测试通用方法
    @Test
    public void testQueryForEmployee() {

        String sql = "select id,name,email,birth from employee where id = ?";
        Employee employee = queryForEmployee(sql,1);
        System.out.println(employee);
    }

    //针对employee的通用查询操作方法
    public Employee queryForEmployee(String sql, Object ...args) {

        

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);

            for (int i = 0;i < args.length;i++)
                ps.setObject(i+1,args[i]);

            rs = ps.executeQuery();

            //获取结果集的元数据
            ResultSetmetaData rsmd = rs.getmetaData();

            //通过元数据获取结果集中的列数
            int columnCount = rsmd.getColumnCount();

            if(rs.next()){

                //先创建一个当前要处理的数据对应的对象
                Employee emp = new Employee();

                //处理结果集第一行数据中的每一列
                for (int i = 0;i < columnCount;i++){

                    //获取每一列的值
                    Object columnValue = rs.getObject(i+1);

                    //获取每一列的列名
                    
                    //String columnName = rsmd.getColumnName(i+1);
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //给emp对象指定的columnName属性赋值为columnValue，通过反射实现
                    Field field = Employee.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(emp,columnValue);//将每一列的值赋值给对象emp对应的属性
                }

                return emp;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,ps,rs);
        }

        return null;
    }

针对表的字段名与类的属性名不同的情况

必须声明sql时使用类的属性名来命名字段的别名
使用ResultSetmetaData时，用getColumnLabel()替换getColumnName()来获取字段的别名

说明：使用sql中没有用到别名，则用getColumnLabel()获取就是列名

测试代码2.0

  @Test
    public void testQuery() {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        try {
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from employee where id = ?";
            ps = conn.prepareStatement(sql);

            ps.setObject(1,2);//填充占位符

            //执行，并返回一个结果集
            resultSet = ps.executeQuery();

            //处理结果集
            if(resultSet.next()){//next():判断结果集的下一条有无数据，如果有数据返回则返回true并指针下移，无则返回false

                //获取当前数据的各个字段值
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);

                //打印数据，将数据封装成一个对象，再打印
                Employee employee = new Employee(id,name,email,birth);
                System.out.println(employee);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            //释放资源
            JDBCUtils.closeResource(conn,ps,resultSet);
        }
    }
}

针对不同表的查询，返回一条记录

package com.ljh.jdbc;

import com.ljh.bean.Employee;
import com.ljh.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetmetaData;


public class PreparedStatementQueryTest {

    //测试针对不同表的查询
    @Test
    public void testGetInstance(){

        String sql = "select id,name,email from employee where id = ?";
        Employee employee = getInstance(Employee.class,sql,2);
        System.out.println(employee);
    }

    //通用查询方法（泛型方法）
    public  T getInstance(Class clazz, String sql, Object ...args) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);

            for (int i = 0;i < args.length;i++)
                ps.setObject(i+1,args[i]);

            rs = ps.executeQuery();

            //获取结果集的元数据
            ResultSetmetaData rsmd = rs.getmetaData();
            //获取列数
            int columnCount = rsmd.getColumnCount();

            if(rs.next()){

                //创建一个类
                T t = clazz.newInstance();

                for (int i = 0;i < columnCount;i++){

                    //获取每一列的值
                    Object columnValue = rs.getObject(i+1);

                    //获取每一列的列名（用别名）
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //与类的对象属性对应上
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }

                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,ps,rs);
        }

        return null;
    }
}

针对不同表的查询，返回多条记录

//测试getForList
    @Test
    public void testGetForList() {

        String sql = "select id,name,email from employee where id < ?";//注意这里是小于，不是等于，因为要查询多条记录
        List list = getForList(Employee.class,sql,4);
        list.forEach(System.out::println);//打印方法第一次见。。
    }

    //不同表的查询，返回多条记录（使用集合，泛型）
    public  List getForList(Class clazz, String sql, Object... args) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++)
                ps.setObject(i + 1, args[i]);

            rs = ps.executeQuery();

            //获取结果集的元数据
            ResultSetmetaData rsmd = rs.getmetaData();
            //获取列数
            int columnCount = rsmd.getColumnCount();

            //要在循环前创建一个集合对象
            ArrayList list = new ArrayList();

            while (rs.next()) {//原来的if变while

                //创建一个类
                T t = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {

                    //获取每一列的值
                    Object columnValue = rs.getObject(i + 1);

                    //获取每一列的列名（用别名）
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //与类的对象属性对应上
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                //在集合中添加元素t
                list.add(t);
            }

            return list;//返回一个集合，如果没有查询到数据，则返回的集合长度为0

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
    }

测试结果：

四、批量插入

方式一，用Statement，不推荐，会有SQL注入的风险
方式二，用PreparedStatement

//方式二，用PreparedStatement
    @Test
    public void testInsert2() {

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCUtils.getConnection();

            String sql = "insert into goods(name)values(?)";

            ps = conn.prepareStatement(sql);

            //插入2w个数据
            for(int i = 1;i <= 20000;i++){

                ps.setObject(1,"name_"+i);

                ps.execute();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,ps);
        }
    }

方式三，对方式二的优化

//方式三，对方式二的优化
    @Test
    public void testInsert3() {

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCUtils.getConnection();

            String sql = "insert into goods(name)values(?)";

            ps = conn.prepareStatement(sql);

            //插入2w个数据
            for(int i = 1;i <= 20000;i++){

                ps.setObject(1,"name_"+i);

                //1.“攒”sql
                ps.addBatch();

                //每500个数据执行一次
                if(i % 500 == 0){
                    //2.执行batch
                    ps.executeBatch();

                    //3.清空batch
                    ps.clearBatch();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,ps);
        }
    }

方式四，对方式三的优化，设置连接时不允许自动提交数据

//方式四，对方式三的优化，设置连接时不允许自动提交数据
    @Test
    public void testInsert4() {

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCUtils.getConnection();

            //获取连接时设置不允许自动提交数据
            conn.setAutoCommit(false);

            String sql = "insert into goods(name)values(?)";

            ps = conn.prepareStatement(sql);

            //插入2w个数据
            for(int i = 1;i <= 20000;i++){

                ps.setObject(1,"name_"+i);

                //1.攒sql
                ps.addBatch();

                if(i % 500 == 0){

                    //2.执行batch
                    ps.executeBatch();

                    //3.清空batch
                    ps.clearBatch();
                }
            }

            //全部执行完后再提交数据
            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,ps);
        }
    }

五、创建baseDAO

DAO：Data Access Object，访问数据类和接口

EmployeeDAO接口

package com.ljh.dao1;

import com.ljh.bean.Employee;

import java.sql.Connection;
import java.util.Date;
import java.util.List;


public interface EmployeeDAO {

    
    void insert(Connection conn, Employee emp);

    
    void deleteById(Connection conn, int id);

    
    void update(Connection conn, Employee emp);

    
    Employee getEmployee(Connection conn, int id);

    
    List getAll(Connection conn);

    
    Long getCount(Connection conn);

    
    Date getMaxDate(Connection conn);
}

baseDAO类1.0

package com.ljh.dao1;

import com.ljh.util.JDBCUtils;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public abstract class baseDAO {//封装类

    //增删改操作（考虑上事务）
    public void update(Connection conn, String sql, Object ...args) {//sql中的占位符个数与可变参数args的长度一致

        //先初始化
        PreparedStatement ps = null;

        try {

            //预编译，获取PreparedStatement的实例
            ps = conn.prepareStatement(sql);

            //填充占位符（用循环实现）
            for (int i = 0;i < args.length;i++)
                ps.setObject(i+1,args[i]);//注意参数的声明！

            //执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            //释放资源
            JDBCUtils.closeResource(null,ps);
        }
    }

    //查询操作，返回数据表中一条记录（考虑到事务）
    public  T getInstance(Connection conn, Class clazz, String sql, Object ...args) {

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            ps = conn.prepareStatement(sql);

            for (int i = 0;i < args.length;i++)
                ps.setObject(i+1,args[i]);

            rs = ps.executeQuery();

            //获取结果集的元数据
            ResultSetmetaData rsmd = rs.getmetaData();
            //获取列数
            int columnCount = rsmd.getColumnCount();

            if(rs.next()){

                //创建一个类
                T t = clazz.newInstance();

                for (int i = 0;i < columnCount;i++){

                    //获取每一列的值
                    Object columnValue = rs.getObject(i+1);

                    //获取每一列的列名（用别名）
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //与类的对象属性对应上
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }

                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            JDBCUtils.closeResource(null,ps,rs);
        }

        return null;
    }

    //查询操作，返回数据表中多条记录（考虑到事务）
    public  List getForList(Connection conn, Class clazz, String sql, Object... args) {

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            ps = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++)
                ps.setObject(i + 1, args[i]);

            rs = ps.executeQuery();

            //获取结果集的元数据
            ResultSetmetaData rsmd = rs.getmetaData();
            //获取列数
            int columnCount = rsmd.getColumnCount();

            //创建一个集合对象
            ArrayList list = new ArrayList();

            while (rs.next()) {

                //创建一个类
                T t = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {

                    //获取每一列的值
                    Object columnValue = rs.getObject(i + 1);

                    //获取每一列的列名（用别名）
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //与类的对象属性对应上
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                //在集合中添加元素t
                list.add(t);
            }

            return list;//返回一个集合，如果没有查询到数据，则返回的集合长度为0

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JDBCUtils.closeResource(null, ps, rs);
        }

        return null;
    }

    //用于查询一些特殊值的通用方法（比如数据的条目数）
    public  E getValue(Connection conn, String sql, Object ...args) {

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(sql);

            for (int i = 0;i < args.length;i++)
                ps.setObject(i+1,args[i]);

            rs = ps.executeQuery();

            if(rs.next())
                return (E) rs.getObject(1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            JDBCUtils.closeResource(null,ps,rs);
        }

        return null;
    }
}

EmployeeDao实现类1.0

package com.ljh.dao1;

import com.ljh.bean.Employee;

import java.sql.Connection;
import java.util.Date;
import java.util.List;


public class EmployeeDAOImpl extends baseDAO implements EmployeeDAO {
    @Override
    public void insert(Connection conn, Employee emp) {

        String sql = "insert into employee(id,name,email,birth)values(?,?,?,?)";
        update(conn,sql,emp.getId(),emp.getName(),emp.getEmail(),emp.getBirth());
    }

    @Override
    public void deleteById(Connection conn, int id) {

        String sql = "delete from employee where id = ?";
        update(conn,sql,id);
    }

    @Override
    public void update(Connection conn, Employee emp) {

        String sql = "update employee set name = ?,email = ?,birth = ? where id = ?";
        update(conn,sql,emp.getName(),emp.getEmail(),emp.getBirth(),emp.getId());
    }

    @Override
    public Employee getEmployee(Connection conn, int id) {

        String sql = "select id,name,email,birth from employee where id = ?";
        return getInstance(conn,Employee.class,sql,id);
    }

    @Override
    public List getAll(Connection conn) {

        String sql = "select id,name,email,birth from employee";
        return getForList(conn,Employee.class,sql);
    }

    @Override
    public Long getCount(Connection conn) {

        String sql = "select count(*) from employee";
        return getValue(conn,sql);
    }

    @Override
    public Date getMaxDate(Connection conn) {

        String sql = "select max(birth) from employee";
        return getValue(conn,sql);
    }
}

baseDao2.0

PS：与baseDao1.0的区别在于多了获取当前baseDAO的子类继承的父类的泛型的操作，实现接口时可以不用再传入Employee对象

package com.ljh.dao2;

import com.ljh.util.JDBCUtils;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public abstract class baseDAO {//封装类

    private Class clazz = null;

    {
        //获取当前baseDAO的子类继承的父类的泛型
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType paramType = (ParameterizedType)genericSuperclass;
        Type[] typeArguments = paramType.getActualTypeArguments();//获取父类的泛型
        clazz = (Class) typeArguments[0];//泛型的第一个参数，就是T
    }

    //增删改操作（考虑上事务）
    public void update(Connection conn, String sql, Object ...args) {//sql中的占位符个数与可变参数args的长度一致

        //先初始化
        PreparedStatement ps = null;

        try {

            //预编译，获取PreparedStatement的实例
            ps = conn.prepareStatement(sql);

            //填充占位符（用循环实现）
            for (int i = 0;i < args.length;i++)
                ps.setObject(i+1,args[i]);//注意参数的声明！

            //执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            //释放资源
            JDBCUtils.closeResource(null,ps);
        }
    }

    //查询操作，返回数据表中一条记录（考虑到事务）
    public T getInstance(Connection conn, String sql, Object ...args) {

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            ps = conn.prepareStatement(sql);

            for (int i = 0;i < args.length;i++)
                ps.setObject(i+1,args[i]);

            rs = ps.executeQuery();

            //获取结果集的元数据
            ResultSetmetaData rsmd = rs.getmetaData();
            //获取列数
            int columnCount = rsmd.getColumnCount();

            if(rs.next()){

                //创建一个类
                T t = clazz.newInstance();

                for (int i = 0;i < columnCount;i++){

                    //获取每一列的值
                    Object columnValue = rs.getObject(i+1);

                    //获取每一列的列名（用别名）
                    String columnLabel = rsmd.getColumnLabel(i+1);

                    //与类的对象属性对应上
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }

                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            JDBCUtils.closeResource(null,ps,rs);
        }

        return null;
    }

    //查询操作，返回数据表中多条记录（考虑到事务）
    public List getForList(Connection conn, String sql, Object... args) {

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            ps = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++)
                ps.setObject(i + 1, args[i]);

            rs = ps.executeQuery();

            //获取结果集的元数据
            ResultSetmetaData rsmd = rs.getmetaData();
            //获取列数
            int columnCount = rsmd.getColumnCount();

            //创建一个集合对象
            ArrayList list = new ArrayList();

            while (rs.next()) {

                //创建一个类
                T t = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {

                    //获取每一列的值
                    Object columnValue = rs.getObject(i + 1);

                    //获取每一列的列名（用别名）
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //与类的对象属性对应上
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                //在集合中添加元素t
                list.add(t);
            }

            return list;//返回一个集合，如果没有查询到数据，则返回的集合长度为0

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JDBCUtils.closeResource(null, ps, rs);
        }

        return null;
    }

    //用于查询一些特殊值的通用方法（比如数据的条目数）
    public  E getValue(Connection conn, String sql, Object ...args) {

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(sql);

            for (int i = 0;i < args.length;i++)
                ps.setObject(i+1,args[i]);

            rs = ps.executeQuery();

            if(rs.next())
                return (E) rs.getObject(1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            JDBCUtils.closeResource(null,ps,rs);
        }

        return null;
    }
}

Employee实现类2.0

PS：与1的区别是getEmployee和getAll方法实现中减少了Employee.class，不用再传入对象，因为只是针对Employee一个类，只需获取父类的泛型（Employee）即可。

package com.ljh.dao2;

import com.ljh.bean.Employee;

import java.sql.Connection;
import java.util.Date;
import java.util.List;


public class EmployeeDAOImpl extends baseDAO implements EmployeeDAO {
    @Override
    public void insert(Connection conn, Employee emp) {

        String sql = "insert into employee(id,name,email,birth)values(?,?,?,?)";
        update(conn,sql,emp.getId(),emp.getName(),emp.getEmail(),emp.getBirth());
    }

    @Override
    public void deleteById(Connection conn, int id) {

        String sql = "delete from employee where id = ?";
        update(conn,sql,id);
    }

    @Override
    public void update(Connection conn, Employee emp) {

        String sql = "update employee set name = ?,email = ?,birth = ? where id = ?";
        update(conn,sql,emp.getName(),emp.getEmail(),emp.getBirth(),emp.getId());
    }

    @Override
    public Employee getEmployee(Connection conn, int id) {

        String sql = "select id,name,email,birth from employee where id = ?";
        return getInstance(conn,sql,id);
    }

    @Override
    public List getAll(Connection conn) {

        String sql = "select id,name,email,birth from employee";
        return getForList(conn,sql);
    }

    @Override
    public Long getCount(Connection conn) {

        String sql = "select count(*) from employee";
        return getValue(conn,sql);
    }

    @Override
    public Date getMaxDate(Connection conn) {

        String sql = "select max(birth) from employee";
        return getValue(conn,sql);
    }
}

六、使用Druid数据库连接池

package com.ljh.connection;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;


public class DruidTest {

    //用配置文件的方法
    @Test
    public void getConnection() throws Exception {

        Properties pros = new Properties();

        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");

        pros.load(is);

		//关键代码
        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = source.getConnection();

        System.out.println(conn);
    }
}

配置文件（druid.properties）

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=Myroot_ljh
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=10

七、使用DBUtils实现CRUD操作

package com.ljh.dbutils;

import com.ljh.bean.Employee;
import com.ljh.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;


public class QueryRunnerTest {

    
    @Test
    public void testInsert() {

        Connection conn = null;

        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection1();

            String sql = "insert into employee(name,email,birth)values(?,?,?)";

            int insertCount = runner.update(conn,sql,"谢霆锋","xietingfeng@126.com","1890-02-12");

            System.out.println("添加了"+insertCount+"条记录");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,null);
        }
    }

    
    @Test
    public void testQuery1() {

        Connection conn = null;

        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection1();

            String sql = "select id,name,email,birth from employee where id = ?";

            BeanHandler handler = new BeanHandler<>(Employee.class);
            Employee emp = runner.query(conn,sql,handler,6);

            System.out.println(emp);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,null);
        }
    }

    
    @Test
    public void testQuery2() {

        Connection conn = null;

        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection1();

            String sql = "select id,name,email,birth from employee where id < ?";

            BeanListHandler handler = new BeanListHandler<>(Employee.class);
            List list = runner.query(conn,sql,handler,6);

            //list的打印
            list.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,null);
        }
    }

    
    @Test
    public void testQuery3() {

        Connection conn = null;

        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection1();

            String sql = "select id,name,email,birth from employee where id = ?";

            MapHandler handler = new MapHandler();
            Map map = runner.query(conn,sql,handler,6);

            System.out.println(map);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,null);
        }
    }

    
    @Test
    public void testQuery4() {

        Connection conn = null;

        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection1();

            String sql = "select id,name,email,birth from employee where id < ?";

            MapListHandler handler = new MapListHandler();
            List> list = runner.query(conn,sql,handler,6);

            //list的打印
            list.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,null);
        }
    }

    
    @Test
    public void testQuery5() {

        Connection conn = null;

        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection1();

            String sql = "select count(*) from employee";

            ScalarHandler handler = new ScalarHandler();

            Long count = (Long) runner.query(conn,sql,handler);
            System.out.println(count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            JDBCUtils.closeResource(conn,null);
        }
    }
}

Java300集课程笔记整理3.0（JDBC）

Java相关栏目本月热门文章