JDBC

获取数据库连接

方式一

Test
public void testConnection1() throws SQLException {
    // 创建驱动
    Driver driver = new com.mysql.cj.jdbc.Driver();

    // 连接数据库的地址
    // 协议:ip地址:端口号:数据库名
    String url = "jdbc:mysql://localhost:3306/test";

    // 将用户名和密码封装在Properties中
    Properties info = new Properties();
    info.setProperty("user", "root");
    info.setProperty("password", "abc123");

    // 连接数据库
    Connection connection = driver.connect(url, info);

    System.out.println(connection);
}

方式二（反射）

@Test
public void testConnection2() throws Exception {
    // 使用反射
    Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
    Driver driver = (Driver) clazz.newInstance();

    String url = "jdbc:mysql://localhost:3306/test";

    Properties info = new Properties();
    info.setProperty("user", "root");
    info.setProperty("password", "abc123");

    Connection connection = driver.connect(url, info);

    System.out.println(connection);
}

方式三（DriverManager）

@Test
public void testConnection3() throws Exception {
    Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
    Driver driver = (Driver) clazz.newInstance();

    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "abc123";

    // 注册驱动
    DriverManager.registerDriver(driver);

    // 连接数据库
    Connection connection = DriverManager.getConnection(url, user, password);

    System.out.println(connection);
}

方式四（简化）

@Test
public void testConnection4() throws Exception {
    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "abc123";

    Class.forName("com.mysql.cj.jdbc.Driver");
    // 加载Driver时会自动注册

    Connection connection = DriverManager.getConnection(url, user, password);

    System.out.println(connection);
}

方式五（配置文件）

@Test
public void testConnection5() throws Exception {
    InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");

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

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

    Class.forName(driverClass);

    Connection connection = DriverManager.getConnection(url, user, password);

    System.out.println(connection);
}

properties内容：

user=root
password=abc123
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver

PreparedStatement

完整的增删改操作

@Test
public void testInsert() {
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

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

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

        Class.forName(driverClass);

        connection = DriverManager.getConnection(url, user, password);

        // 预编译sql语句，返回PreparedStatement实例
        String sql = "insert into customers(name,email,birth)values(?,?,?)";
        ps = connection.prepareStatement(sql);

        // 填充占位符
        ps.setString(1, "哪吒");
        ps.setString(2, "nezha@gmail.com");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date date = sdf.parse("1000-01-01");
        ps.setDate(3, new Date(date.getTime()));

        // 执行操作
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (ps != null) ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (connection != null) connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

封装工具类

public class JDBCUtils {

    // 获取数据库连接
    public static Connection getConnection() throws Exception {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

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

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

        Class.forName(driverClass);

        return DriverManager.getConnection(url, user, password);
    }

    // 关闭资源
    public static void closeResource(Connection connection, Statement ps) {
        try {
            if (connection != null) connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (ps != null) ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    // 关闭资源
    public static void closeResource(Connection connection, Statement ps, ResultSet rs) {
        try {
            if (connection != null) connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (ps != null) ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (rs != null) rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

通用的增删改操作

@Test
public void testCommonUpdate() {
    String sql = "update `order` set order_name = ? where order_id = ?";
    update(sql, "DD", "2");
}

// 通用的增删改操作
public void update(String sql, Object ...args) {
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        connection = JDBCUtils.getConnection();
        ps = connection.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(connection, ps);
    }
}

数据类型对应

Java类型	SQL类型
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
String	CHAR, VARCHAR, LONGVARCHAR
byte array	BINARY, VAR BINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP, DATETIME

ORM编程思想

object relational mapping

• 一个数据表对应一个类
• 一条记录对应一个对象
• 一个字段对应一个属性

public class Customer {
    
    private int id;
    private String name;
    private String email;
    private Date birth;
 
    // 提供构造方法和get、set方法
}

完整的查询操作

@Test
public void testQuery1() {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet resultSet = null;
    try {
        connection = JDBCUtils.getConnection();
        String sql = "select id,name,email,birth from customers where id = ?";
        ps = connection.prepareStatement(sql);
        ps.setObject(1, 1);

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

        // 如果有数据，返回true，指针下移；没有数据，返回false，指针不下移
        if (resultSet.next()) {
            // 获取当前数据字段的值
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);
            Date birth = resultSet.getDate(4);

            Customer customer = new Customer(id, name, email, birth);

            System.out.println(customer);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection, ps, resultSet);
    }

}

通用的查询操作

public Customer queryForCustomers(String sql, Object ...args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtils.getConnection();
        ps = connection.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()) {
            Customer cust = new Customer();
            // 处理结果集一行的每一列
            for (int i = 0; i < columnCount; i++) {
                // 获取列的值
                Object columnValue = rs.getObject(i + 1);

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

                // 给cust对象指定的columnName属性赋值为columnValue，通过反射
                Field field = Customer.class.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(cust, columnValue);
            }
            return cust;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection, ps, rs);
    }
    return null;
}

表的列名和类的属性名不同

用getColumnLabel替换getColumnName。

@Test
public void testOrderForQuery() {
    String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
    Order order = orderForQuery(sql, 1);
    System.out.println(order);
}

public Order orderForQuery(String sql, Object...args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtils.getConnection();
        ps = connection.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()) {
            Order order = new Order();
            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);
                
                Field field = Order.class.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(order, columnValue);
            }
            return order;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection, ps, rs);
    }
    return null;
}

针对不同表的通用查询

查询单个数据

public <T> T getInstance(Class<T> clazz, String sql, Object...args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtils.getConnection();
        ps = connection.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(connection, ps, rs);
    }
    return null;
}

查询多个数据

@Test
public void testGetForList() {
    String sql = "select id,name,email from customers where id < ?";
    List<Customer> list = getForList(Customer.class, sql, 12);
    list.forEach(System.out::println);
}

public <T> List<T> getForList(Class<T> clazz, String sql, Object...args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtils.getConnection();
        ps = connection.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<T> 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);
            }
            list.add(t);
        }
        return list;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection, ps, rs);
    }
    return null;
}

操作BLOB类型字段

添加

Connection connection = JDBCUtils.getConnection();
String sql = "insert into customers(name, email, birth, photo) values(?,?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);

ps.setObject(1, "张宇豪");
ps.setObject(2, "zhang@qq.com");
ps.setObject(3, "1992-09-08");
FileInputStream is = new FileInputStream("test_blob.jpg");
ps.setBlob(4, is);

ps.execute();
JDBCUtils.closeResource(connection, ps);

查询

Connection connection = JDBCUtils.getConnection();
String sql = "select id,name,email,birth,photo from customers where id = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, 20);

ResultSet rs = ps.executeQuery();
if (rs.next()) {
    // 方式一
    //            int id = rs.getInt(1);
    //            String name = rs.getString(2);
    //            String email = rs.getString(3);
    //            Date birth = rs.getDate(4);
    // 方式二
    int id = rs.getInt("id");
    String name = rs.getString("name");
    String email = rs.getString("email");
    Date birth = rs.getDate("birth");
    Customer customer = new Customer(id, name, email, birth);
    System.out.println(customer);

    // 将Blob类型的字段下载下来，以文件方式保存在本地
    Blob photo = rs.getBlob("photo");
    InputStream is = photo.getBinaryStream();
    FileOutputStream fos = new FileOutputStream("zhangyuhao.jpg");
    byte[] buffer = new byte[1024];
    int len;
    while ((len = is.read(buffer)) != -1) {
        fos.write(buffer, 0, len);
    }
    is.close();
    fos.close();
}
JDBCUtils.closeResource(connection, ps);

特殊情况

如果报错：xxx too large。在my.ini中进行配置max_allowed_packet=16M，并重启服务。

批量插入数据

update、insert本身就有批量操作的效果。这里的批量操作主要是针对插入。

注意 ：mysql服务器默认是关闭批处理的，需要通过一个参数让mysq开启批处理的支持。

在properties的url后添加?rewriteBatchedStatements=true。

Connection connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);    // 设置不自动提交
String sql = "insert into goods(good)values(?)";
PreparedStatement ps = connection.prepareStatement(sql);
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();
    }
}
connection.commit();    // 最后再统一提交
JDBCUtils.closeResource(connection, ps);

事务

具体方法中的数据库连接应该由外部传入。

public void update(Connection connection, String sql, Object ...args) {
    PreparedStatement ps = null;
    try {
        ps = connection.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 void testUpdate() {
    Connection connection = null;
    try {
        connection = JDBCUtils.getConnection();
        // 1. 取消自动提交
        connection.setAutoCommit(false);
        String sql1 = "";
        String sql2 = "";
        update(connection, sql1);
        update(connection, sql2);
        // 2. 提交数据
        connection.commit();
    } catch (Exception e) {
        e.printStackTrace();
        try {
            // 3. 回滚
            if (connection != null)
                connection.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    } finally {
        try {
            // 如果连接不关的话，应该恢复自动提交为true
            if (connection != null)
                connection.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCUtils.closeResource(connection, null);
    }
}

隔离级别：

connection.getTransactionIsolation();
connection.setTransactionIsolation(参数);

// 四种参数
TRANSACTION_READ_UNCOMMITTED
TRANSACTION_READ_COMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE

DAO

BaseDAO

public abstract class BaseDAO {
    public void update(Connection connection, String sql, Object ...args) {
        PreparedStatement ps = null;
        try {
            ps = connection.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> T getInstance(Connection connection, Class<T> clazz, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.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 <T> List<T> getForList(Connection connection, Class<T> clazz, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.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<T> 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);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }

    public <E> E getValue(Connection connection, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.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 e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }
}

BaseDAO泛型改进

public abstract class BaseDAO<T> {
    private final Class<T> clazz;

    {
        // 获取当前BaseDAO的子类继承的父类中的泛型，即这里的T
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;
        Type[] typeArguments = parameterizedType.getActualTypeArguments();  // 获取父类泛型
        clazz = (Class<T>) typeArguments[0];   // 泛型参数
    }
    
    public T getInstance(Connection connection, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.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 interface CustomerDAO {
    void insert(Connection connection, Customer cust);
    void deleteById(Connection connection, int id);
    void update(Connection connection, Customer cust);
    Customer getCustomerById(Connection connection, int id);
    List<Customer> getAll(Connection connection);
    Long getCount(Connection connection);
}

Impl

public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {
    @Override
    public void insert(Connection connection, Customer cust) {
        String sql = "insert into customers(name,email,birth)values(?,?,?)";
        update(connection, sql, cust.getName(), cust.getEmail(), cust.getBirth());
    }

    @Override
    public void deleteById(Connection connection, int id) {
        String sql = "delete from customers where id = ?";
        update(connection, sql, id);
    }

    @Override
    public void update(Connection connection, Customer cust) {
        String sql = "update customers set name = ?,email=?,birth=? where id = ?";
        update(connection, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getId());
    }

    @Override
    public Customer getCustomerById(Connection connection, int id) {
        String sql = "select id, name, email, birth from customers where id = ?";
        return getInstance(connection, Customer.class, sql, id);
    }

    @Override
    public List<Customer> getAll(Connection connection) {
        String sql = "select id, name, email, birth from customers";
        return getForList(connection, Customer.class, sql);
    }

    @Override
    public Long getCount(Connection connection) {
        String sql = "select count(*) from customers";
        return getValue(connection, sql);
    }
}

Impl泛型改进

public class CustomerDAOImpl extends BaseDAO<Customer> implements CustomerDAO {}

数据库连接池

C3P0

需要导入c3p0-0.9.1.2.jar包。

获取连接方式一

// 获取数据库连接池
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
cpds.setUser("root");
cpds.setPassword("abc123");
// 设置参数对连接池进行管理
cpds.setInitialPoolSize(10);    // 初始连接数

Connection connection = cpds.getConnection();
System.out.println(connection);

// 销毁连接池
DataSources.destroy( cpds );

获取连接方式二

ComboPooledDataSource cpds = new ComboPooledDataSource("hello_c3p0");	// 与xml中对应
Connection connection = cpds.getConnection();

配置文件c3p0-config.xml如下：

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <named-config name="hello_c3p0">
        <!-- 提供获取连接的4个基本信息 -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
        <property name="user">root</property>
        <property name="password">abc123</property>

        <!-- 管理连接池的信息 -->
        <!-- 连接池不够时，c3p0向数据库服务器申请的连接数 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化时的连接数 -->
        <property name="initialPoolSize">10</property>
        <!-- 维护的最小连接数 -->
        <property name="minPoolSize">10</property>
        <!-- 维护的最大连接数 -->
        <property name="maxPoolSize">100</property>
        <!-- 最多维护的Statement个数 -->
        <property name="maxStatements">50</property>
        <!-- 每个连接中最多使用的Statement个数 -->
        <property name="maxStatementsPerConnection">2</property>
    </named-config>
</c3p0-config>

JDBCUtils

private static final ComboPooledDataSource cpds = new ComboPooledDataSource("hello_c3p0");
public static Connection getConnection() throws Exception {
    return cpds.getConnection();
}

DBCP

需要导入commons-dbcp-1.4.jar和commons-pool-1.5.5.jar包。

获取连接方法一

// 创建数据库连接池
BasicDataSource source = new BasicDataSource();
// 设置基本信息
source.setDriverClassName("com.mysql.cj.jdbc.Driver");
source.setUrl("jdbc:mysql://localhost:3306/test");
source.setUsername("root");
source.setPassword("abc123");
// 管理的相关属性
source.setInitialSize(10);
source.setMaxActive(10);

Connection connection = source.getConnection();
System.out.println(connection);

获取连接方法二

Properties pros = new Properties();
// 方法一
// InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
// 方法二
FileInputStream is = new FileInputStream("src/dbcp.properties");
pros.load(is);
DataSource source = BasicDataSourceFactory.createDataSource(pros);

Connection connection = source.getConnection();
System.out.println(connection);

properties文件：

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=abc123

initialSize=10

JDBCUtils

public class DBCPUtils {
    private static DataSource source;
    static {
        try {
            Properties pros = new Properties();
            FileInputStream is = new FileInputStream("src/dbcp.properties");
            pros.load(is);
            source = BasicDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取数据库连接
    public static Connection getConnection() throws Exception {
        Connection connection = source.getConnection();
        return connection;
    }
}

Druid（德鲁伊）

需要导入druid-1.1.10.jar包。

获取连接

Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
DataSource source = DruidDataSourceFactory.createDataSource(pros);

Connection connection = source.getConnection();
System.out.println(connection);

properties文件内容：

url=jdbc:mysql://localhost:3306/test
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=abc123

initialSize=10
maxActive=10

JDBCUtils

public class DruidUtils {
    private static DataSource source;
    static {
        try {
            Properties pros = new Properties();
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            pros.load(is);
            source = DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取数据库连接
    public static Connection getConnection() throws Exception {
        Connection connection = source.getConnection();
        return connection;
    }
}

Apache-DBUtils实现CRUD操作

需要导入commons-dbutils-1.3.jar包。

增删改

Connection connection = null;
try {
    QueryRunner runner = new QueryRunner();
    connection = DruidUtils.getConnection();
    String sql = "insert into customers(name,email,birth)values(?,?,?)";
    int updateCount = runner.update(connection, sql, "蔡徐坤", "cxk@123.com", "2001-05-12");
    System.out.println(updateCount);
} catch (Exception e) {
    e.printStackTrace();
} finally {
    DruidUtils.closeResource(connection, null);
}

查

query方法需要传入一个ResultSetHandler的实现类对象，用来封装返回的记录。

BeanHandler

QueryRunner runner = new QueryRunner();
Connection connection = DruidUtils.getConnection();
String sql = "select id,name,email,birth from customers where id=?";
BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
Customer customer = runner.query(connection, sql, handler, 10);
System.out.println(customer);

BeanListHandler

QueryRunner runner = new QueryRunner();
Connection connection = DruidUtils.getConnection();
String sql = "select id,name,email,birth from customers";
BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
List<Customer> customers = runner.query(connection, sql, handler);
customers.forEach(System.out::println);

MapHandler

QueryRunner runner = new QueryRunner();
Connection connection = DruidUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
MapHandler handler = new MapHandler();
Map<String, Object> map = runner.query(connection, sql, handler, 10);
System.out.println(map);

MapListHandler

略。

ScalarHandler

查询特殊值。

QueryRunner runner = new QueryRunner();
Connection connection = DruidUtils.getConnection();
String sql = "select count(*) from customers";
ScalarHandler handler = new ScalarHandler();
long count = (Long) runner.query(connection, sql, handler);
System.out.println(count);

自定义

QueryRunner runner = new QueryRunner();
Connection connection = DruidUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
    @Override
    public Customer handle(ResultSet resultSet) throws SQLException {
        if (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String email = resultSet.getString("email");
            Date birth = resultSet.getDate("birth");
            return new Customer(id, name, email, birth);
        }
        return null;
    }
};
Customer customer = runner.query(connection, sql, handler, 10);
System.out.println(customer);

关闭资源

public static void closeResource(Connection connection, Statement ps, ResultSet rs) {
//    try {
/        DbUtils.close(connection);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    DbUtils.closeQuietly(connection, ps, rs);
}