JDBC基础

标签： Java与存储

---

JDBC(Java Database Connectivity)代表Java编程语言与数据库连接的标准API,然而JDBC只是接口,JDBC驱动才是真正的接口实现,没有驱动无法完成数据库连接. 每个数据库厂商都有自己的驱动,用来连接自己公司的数据库(如Oricle, MySQL, DB2, MS SQLServer).

下面我们以MySQL为例,JDBC编程大致步骤如下:

/**
 * @author jifang
 * @since 16/2/18 上午9:02.
 */
public class SQLClient {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        /* 加载数据库驱动 */
        Class.forName("com.mysql.jdbc.Driver");

        /* 通过 DriverManager 获取数据库连接 */
        Connection connection = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");


        /* 通过 Connection 创建 Statement */
        Statement statement = connection.createStatement();


        /* 通过 Statement 执行SQL */
        ResultSet users = statement.executeQuery("SELECT * FROM user");


        /* 操作 ResultSet 结果集 */
        int columnCount = users.getMetaData().getColumnCount();
        while (users.next()) {
            for (int i = 1; i <= columnCount; ++i) {
                System.out.printf("%s\t", users.getObject(i));
            }
            System.out.println();
        }

        /* 回收数据库资源(推荐使用Java1.7提供的 可以自动关闭资源的try) */
        users.close();
        statement.close();
        connection.close();
    }
}

• 注意: 需要在pom.xml中添加如下MySQL驱动:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.36</version>
</dependency>

注: ResultSet参数columnIndex索引从1开始,而不是0!

---

ConnectionManger

DriverManger

JDBC规定: 驱动类在被加载时,需要主动把自己注册到DriverManger中:

• com.mysql.jdbc.Driver

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    //
    // Register ourselves with the DriverManager
    //
    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

    /**
     * Construct a new driver and register it with DriverManager
     * 
     * @throws SQLException
     *             if a database error occurs.
     */
    public Driver() throws SQLException {
        // Required for Class.forName().newInstance()
    }
}

代码显示:只要去加载com.mysql.jdbc.Driver类那么就会执行static块, 从而把com.mysql.jdbc.Driver注册到DriverManager中.

java.sql.DriverManager是用于管理JDBC驱动的服务类,其主要功能是获取Connection对象:
1. static Connection getConnection(String url, Properties info)
2. static Connection getConnection(String url, String user, String password)

另: 还可以在获取Connection的URL中设置参数,如: jdbc:mysql://host:port/database?useUnicode=true&characterEncoding=UTF8
useUnicode=true&characterEncoding=UTF8指定连接数据库的过程中使用Unicode字符集/UTF-8编码;

---

Connection

java.sql.Connection代表数据库连接,每个Connection代表一个物理连接会话, 该接口提供如下创建Statement的方法, 只有获取Statement之后才可执行SQL语句:

方法	描述
Statement createStatement()	Creates a Statement object for sending SQL statements to the database.
PreparedStatement prepareStatement(String sql)	Creates a PreparedStatement object for sending parameterized SQL statements to the database.
CallableStatement prepareCall(String sql)	Creates a CallableStatement object for calling database stored procedures.

其中Connection还提供了如下控制事务/保存点的方法:

方法	描述
Savepoint setSavepoint(String name)	Creates a savepoint with the given name in the current transaction and returns the new Savepoint object that represents it.
void setTransactionIsolation(int level)	Attempts to change the transaction isolation level(事务隔离级别) for this Connection object to the one given.
void setAutoCommit(boolean autoCommit)	Sets this connection’s auto-commit mode to the given state.
void rollback()	Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.
void rollback(Savepoint savepoint)	Undoes all changes made after the given Savepoint object was set.
void commit()	Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.

以上方法还存在不同的重载形式, 详细可参考JDK文档.

---

ConnectionManger

由于获取Connection的步骤单一,每次可能只是加载的参数不同,因此我们可以将获取Connection的操作封装成一个方法,并使其从配置文件中加载配置:

• 配置文件形式

## Data Source
mysql.driver.class=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://host:port/database
mysql.user=admin
mysql.password=admin

• ConnectionManger

/**
 * @author jifang
 * @since 16/2/19 上午10:40.
 */
public class ConnectionManger {

    /*获取原生Connection*/

    public static Connection getConnection(String file) {
        Properties config = SQLUtil.loadConfig(file);
        try {
            Class.forName(config.getProperty("mysql.driver.class"));
            String url = config.getProperty("mysql.url");
            String username = config.getProperty("mysql.user");
            String password = config.getProperty("mysql.password");
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }
}

• SQLUtil

/**
 * @author jifang
 * @since 16/2/18 上午8:24.
 */
public class SQLUtil {

    /**
     * 加载.properties配置文件
     *
     * @param file
     * @return
     */
    public static Properties loadConfig(String file) {

        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemResourceAsStream(file));
            return properties;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}

---

数据库连接池

前面通过DriverManger获得Connection, 一个Connection对应一个实际的物理连接,每次操作都需要打开物理连接, 使用完后立即关闭;这样频繁的打开/关闭连接会造成不必要的数据库系统性能消耗.
数据库连接池提供的解决方案是:当应用启动时,主动建立足够的数据库连接,并将这些连接组织成连接池,每次请求连接时,无须重新打开连接,而是从池中取出已有连接,使用完后并不实际关闭连接,而是归还给池.
JDBC数据库连接池使用javax.sql.DataSource表示, DataSource只是一个接口, 其实现通常由服务器提供商(如WebLogic, WebShere)或开源组织(如DBCP,C3P0和HikariCP)提供.

• 数据库连接池的常用参数如下:
  
  1. 数据库初始连接数;
  2. 连接池最大连接数;
  3. 连接池最小连接数;
  4. 连接池每次增加的容量;

---

C3P0

Tomcat默认使用的是DBCP连接池,但相比之下,C3P0则比DBCP更胜一筹(Hibernate推荐使用C3P0),C3P0不仅可以自动清理不再使用的Connection, 还可以自动清理Statement/ResultSet, 使用C3P0需要在pom.xml中添加如下依赖:

<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
</dependency>
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>mchange-commons-java</artifactId>
    <version>0.2.11</version>
</dependency>

• ConnectionManger

public class ConnectionManger {

    /*双重检测锁保证DataSource单例*/
    private static DataSource dataSource;

    /*获取DataSource*/

    public static DataSource getDataSourceC3P0(String file) {
        if (dataSource == null) {
            synchronized (ConnectionManger.class) {
                if (dataSource == null) {
                    Properties config = SQLUtil.loadConfig(file);
                    try {
                        ComboPooledDataSource source = new ComboPooledDataSource();
                        source.setDriverClass(config.getProperty("mysql.driver.class"));
                        source.setJdbcUrl(config.getProperty("mysql.url"));
                        source.setUser(config.getProperty("mysql.user"));
                        source.setPassword(config.getProperty("mysql.password"));

                        // 设置连接池最大连接数
                        source.setMaxPoolSize(Integer.valueOf(config.getProperty("pool.max.size")));
                        // 设置连接池最小连接数
                        source.setMinPoolSize(Integer.valueOf(config.getProperty("pool.min.size")));
                        // 设置连接池初始连接数
                        source.setInitialPoolSize(Integer.valueOf(config.getProperty("pool.init.size")));
                        // 设置连接每次增量
                        source.setAcquireIncrement(Integer.valueOf(config.getProperty("pool.acquire.increment")));
                        // 设置连接池的缓存Statement的最大数
                        source.setMaxStatements(Integer.valueOf(config.getProperty("pool.max.statements")));
                        // 设置最大空闲时间
                        source.setMaxIdleTime(Integer.valueOf(config.getProperty("pool.max.idle_time")));

                        dataSource = source;
                    } catch (PropertyVetoException e) {
                        throw new RuntimeException(e);
                    }
                }
            }
        }
        return dataSource;
    }

    /*获取Connection*/

    public static Connection getConnectionC3P0(String file) {
        return getConnection(getDataSourceC3P0(file));
    }

    public static Connection getConnection(DataSource dataSource) {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // ...
}

C3P0还可以使用配置文件来初始化连接池(配置文件可以是properties/XML, 在此仅介绍XML),C3P0配置文件名必须为c3p0-config.xml,其放在类路径下:

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://host:port/database</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">user</property>
        <property name="password">password</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">3</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    <named-config name="mysql-config">
        <property name="jdbcUrl">jdbc:mysql://host:port/common</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">user</property>
        <property name="password">password</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">3</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

这样, 我们在创建ComboPooledDataSource时就默认加载配置文件中的配置, 无须手动配置:

public static DataSource getDataSourceC3P0(String file) {
    if (dataSource == null) {
        synchronized (ConnectionManger.class) {
            if (dataSource == null) {
                dataSource = new ComboPooledDataSource();
            }
        }
    }
    return dataSource;
}

C3P0配置文件可以配置多个连接信息, 并为每个配置命名, 这样可以方便的通过配置名称来切换配置信息:

public static DataSource getDataSourceC3P0(String file) {
    if (dataSource == null) {
        synchronized (ConnectionManger.class) {
            if (dataSource == null) {
                dataSource = new ComboPooledDataSource("mysql-config");
            }
        }
    }
    return dataSource;
}

其他关于C3P0的详细内容, 可参考C3P0主页.

---

HikariCP

HikariCP是另一款高性能/”零开销”/高品质的数据库连接池,据测试,其性能优于C3P0(详细信息可参考号称性能最好的JDBC连接池:HikariCP),但国内HikariCP资料不多,其项目主页为https://github.com/brettwooldridge/HikariCP,使用HikariCP需要在pom.xml中添加如下依赖:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>2.4.0</version>
</dependency>

HikariCP用方法获取Connection的方法与C3P0大同小异:

public static DataSource getDataSourceHikari(String file) {
    if (dataSource == null) {
        synchronized (ConnectionManger.class) {
            if (dataSource == null) {
                Properties properties = SQLUtil.loadConfig(file);
                HikariConfig config = new HikariConfig();
                config.setDriverClassName(properties.getProperty("mysql.driver.class"));
                config.setJdbcUrl(properties.getProperty("mysql.url"));
                config.setUsername(properties.getProperty("mysql.user"));
                config.setPassword(properties.getProperty("mysql.password"));

                // 设置连接池最大连接数
                config.setMaximumPoolSize(Integer.valueOf(properties.getProperty("pool.max.size")));
                // 设置连接池最少连接数
                config.setMinimumIdle(Integer.valueOf(properties.getProperty("pool.min.size")));
                // 设置最大空闲时间
                config.setIdleTimeout(Integer.valueOf(properties.getProperty("pool.max.idle_time")));
                // 设置连接最长寿命
                config.setMaxLifetime(Integer.valueOf(properties.getProperty("pool.max.life_time")));
                dataSource = new HikariDataSource(config);
            }
        }
    }

    return dataSource;
}

public static Connection getConnectionHikari(String file) {
    return getConnection(getDataSourceHikari(file));
}

附:
1. ConnectionManger与SQLUtil完整代码地址;
2. properties文件形式如下:

## Data Source
mysql.driver.class=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://host:port/database
mysql.user=user
mysql.password=password
pool.max.size=20
pool.min.size=3
pool.init.size=10
pool.max.statements=180
pool.max.idle_time=60
pool.max.life_time=1000

---

SQL执行

Statement

java.sql.Statement可用于执行DDL/DML/DCL语句:

方法	描述
boolean execute(String sql)	Executes the given SQL statement, which may return multiple results.
ResultSet executeQuery(String sql)	Executes the given SQL statement, which returns a single ResultSet object.
int executeUpdate(String sql)	Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
int[] executeBatch()	Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Java 1.7还新增了closeOnCompletion()方法,当所有依赖于当前Statement的ResultSet关闭时,该Statement自动关闭.

---

executeUpdate

Statement使用executeUpdate方法执行DDL/DML(不包含select)语句:执行DDL该方法返回0; 执行DML返回受影响的记录数.

• DDL

@Test
public void ddlClient() throws SQLException {
    try (
            Connection connection = ConnectionManger.getConnectionHikari("common.properties");
            Statement statement = connection.createStatement()
    ) {
        int res = statement.executeUpdate("CREATE TABLE t_ddl(" +
                "id INT auto_increment PRIMARY KEY, " +
                "username VARCHAR(64) NOT NULL, " +
                "password VARCHAR (36) NOT NULL " +
                ")");
        System.out.println(res);
    }
}

• DML

@Test
public void dmlClient() throws SQLException {
    try (
            Connection connection = ConnectionManger.getConnectionHikari("common.properties");
            Statement statement = connection.createStatement()
    ) {
        int res = statement.executeUpdate("INSERT INTO " +
                "t_ddl(username, password) " +
                "SELECT name, password FROM user");
        System.out.println(res);
    }
}

---

execute

execute方法几乎可以执行任何SQL语句,但较为繁琐(除非不清楚SQL语句类型,否则不要使用execute方法).该方法返回值为boolean,代表执行该SQL语句是否返回ResultSet,然后Statement提供了如下方法来获取SQL执行的结果:

方法	描述
ResultSet getResultSet()	Retrieves the current result as a ResultSet object.
int getUpdateCount()	Retrieves the current result as an update count; if the result is a ResultSet object or there are no more results, -1 is returned.

• SQLUtil

public class SQLUtil {

    // ...

    public static void executeSQL(Statement statement, String sql) {
        try {
            // 如果含有ResultSet
            if (statement.execute(sql)) {
                ResultSet rs = statement.getResultSet();
                ResultSetMetaData meta = rs.getMetaData();
                int columnCount = meta.getColumnCount();
                for (int i = 1; i <= columnCount; ++i) {
                    System.out.printf("%s\t", meta.getColumnName(i));
                }
                System.out.println();

                while (rs.next()) {
                    for (int i = 1; i <= columnCount; ++i) {
                        System.out.printf("%s\t", rs.getObject(i));
                    }
                    System.out.println();
                }
            } else {
                System.out.printf("该SQL语句共影响%d条记录%n", statement.getUpdateCount());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

• client

@Test
public void executeClient() throws SQLException {
    try(
            Connection connection = SQLUtil.getConnection("common.properties");
            Statement statement = connection.createStatement()
            ){
        SQLUtil.executeSQL(statement, "UPDATE t_ddl SET username = 'feiqing'");
        SQLUtil.executeSQL(statement, "SELECT * FROM t_ddl");
    }
}

---

PreparedStatement

PreparedStatement是Statement的子接口, 它可以预编译SQL语句,编译后的SQL模板被存储在PreparedStatement对象中,每次使用时首先为SQL模板设值,然后执行该语句(因此使用PreparedStatement效率更高).
创建PreparedStatement需要使用Connection的prepareStatement(String sql)方法,该方法需要传入SQL模板,可以包含占位符参数:

PreparedStatement statement = connection.prepareStatement("INSERT INTO t_ddl(username, password) VALUES (?, ?)")

PreparedStatement也提供了excute等方法来执行SQL语句, 只是这些方法无须传入参数, 因为SQL语句已经存储在PreparedStatement对象中.
由于执行SQL前需要为SQL模板传入参数值,PreparedStatement提供了一系列的setXxx(int parameterIndex, X x)方法;另外,如果不清楚SQL模板各参数的类型,可以使用setObject(int parameterIndex, Object x)方法传入参数, 由PreparedStatement来负责类型转换.

@Test
public void comparisonPrepared() throws SQLException {
    Connection connection = null;
    try {
        connection = SQLUtil.getConnection("common.properties");
        long start = System.currentTimeMillis();
        try (Statement statement = connection.createStatement()) {
            for (int i = 0; i < 1000; ++i) {
                statement.executeUpdate("INSERT INTO t_ddl(username, password) VALUES ('name" + i + "','password" + i + "')");
            }
        }
        long mid = System.currentTimeMillis();

        try (PreparedStatement statement = connection.prepareStatement("INSERT INTO t_ddl(username, password) VALUES (?, ?)")) {
            for (int i = 0; i < 1000; ++i) {
                statement.setString(1, "name" + i);
                statement.setObject(2, "password" + i);
                statement.execute();
            }
        }
        long end = System.currentTimeMillis();

        System.out.printf("Statement: %d%n", mid - start);
        System.out.printf("Prepared:  %d%n", end - mid);
    } finally {
        try {
            assert connection != null;
            connection.close();
        } catch (SQLException e) {
        }
    }
}

注意: SQL语句的占位符参数只能代替普通值, 不能代替表名/列名等数据库对象, 更不能代替INSERT/SELECT等关键字.

使用PreparedStatement还有另外一个优点:使用PreparedStatement无须拼接SQL字符串,因此可以防止SQL注入(关于SQL注入的问题可参考SQL Injection, 现代的ORM框架都解决了该问题).

注:
1. 默认使用PreparedStatement是没有开启预编译功能的,需要在URL中给出useServerPrepStmts=true参数来开启此功能;
2. 当使用不同的PreparedStatement对象来执行相同SQL语句时,还是会出现编译两次的现象,这是因为驱动没有缓存编译后的函数key,导致二次编译.如果希望缓存编译后的函数key,那么就要设置cachePrepStmts=true参数.
3. 另外, 还可以设置预编译缓存的大小:cachePrepStmts=true&prepStmtCacheSize=50&prepStmtCacheSqlLimit=300`
jdbc:mysql://host:port/database?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=50&prepStmtCacheSqlLimit=300

---

CallableStatement

在数据库中创建一个简单的存储过程add_pro:

mysql> delimiter //
mysql> CREATE PROCEDURE add_pro(a INT, b INT, OUT sum INT)
    -> BEGIN
    -> SET sum = a + b;
    -> END
    -> //
mysql> delimiter ;

delimiter //会将SQL语句的结束符改为//, 这样就可以在创建存储过程时使用;作为分隔符. MySQL默认使用;作为SQL结束符.

调用存储过程需要使用CallableStatement,可以通过Connection的prepareCall()方法来创建,创建时需要传入调用存储过程的SQL语句,形式为:

{CALL procedure_name(?, ?, ?)}

存储过程的参数既有入参,也有回参; 入参可通过setXxx(int parameterIndex/String parameterName, X x)方法传入;回参可以通过调用registerOutParameter(int parameterIndex, int sqlType)来注册, 经过上面步骤, 就可以调用execute()方法来调用该存储过程, 执行结束, 则可通过getXxx(int parameterIndex/String parameterName)方法来获取指定回参的值:

@Test
public void callableClient() throws SQLException {
    try (
            Connection connection = SQLUtil.getConnection("common.properties");
            CallableStatement statement = connection.prepareCall("{CALL add_pro(?, ?, ?)}")
    ) {
        // statement.setInt("a", 1);
        statement.setInt(1, 11);
        // statement.setInt("b", 2);
        statement.setInt(2, 22);

        // 注册CallableStatement回参
        statement.registerOutParameter(3, Types.INTEGER);
        // 执行存储过程
        statement.execute();
        // statement.getInt(3);
        System.out.printf("存储过程执行结果为: %d%n", statement.getInt("sum"));
    }
}

---

操作结果集

JDBC使用ResultSet封装查询结果,然后通过ResultSet的记录指针来读取/更新记录.并提供了ResultSetMetaDate来获得ResultSet对象的元数据信息.

---

ResultSet

java.sql.ResultSet是结果集对象,可以通过列索引/列名来读/写数据, 它提供了如下常用方法来移动记录指针:

方法	描述
boolean next()	Moves the cursor froward one row from its current position.
boolean previous()	Moves the cursor to the previous row in this ResultSet object.
boolean first()	Moves the cursor to the first row in this ResultSet object.
boolean last()	Moves the cursor to the last row in this ResultSet object.
void beforeFirst()	Moves the cursor to the front of this ResultSet object, just before the first row.
void afterLast()	Moves the cursor to the end of this ResultSet object, just after the last row.
boolean absolute(int row)	Moves the cursor to the given row number in this ResultSet object.
boolean relative(int rows)	Moves the cursor a relative number of rows, either positive or negative.

当把记录指针定位到指定行后, ResultSet可通过getXxx(int columnIndex/String columnLabel)方法来获得指定类型值.或使用<T> T getObject(int columnIndex/String columnLabel, Class<T> type)来获取任意类型值.

---

可更新/滚动的ResultSet

以默认方式打开的ResultSet是不可更新的,获得可更新的ResultSet,需要在创建Statement/PreparedStatement时传入如下两个参数:

• resultSetType: 控制ResultSet可移动方向

ResultSet.TYPE_FORWARD_ONLY	The constant indicating the type for a ResultSet object whose cursor may move only forward.
ResultSet.TYPE_SCROLL_INSENSITIVE	The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet.
ResultSet.TYPE_SCROLL_SENSITIVE	The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet.

• resultSetConcurrency: 控制ResultSet的读/写并发类型

ResultSet.CONCUR_READ_ONLY	The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.
ResultSet.CONCUR_UPDATABLE	The constant indicating the concurrency mode for a ResultSet object that may be updated.

另外可更新的结果集还必须满足如下条件:
1) 所有数据都来自一个表; 2)选出的数据集必须包含主键列;

这样, 获取的ResultSet就是可更新/可滚动的, 程序可通过调用ResultSet的updateXxx(int columnIndex/String columnLabel, X x)方法来修改记录指针所指向的值, 最后调用updateRow()来提交修改.

• SQLClient

public class SQLClient {

    private Connection connection = null;

    @Before
    public void setUp() {
        connection = ConnectionManger.getConnectionHikari("common.properties");
    }

    @Test
    public void updateResultSet() throws SQLException {
        // 创建可更新,底层数据敏感的Statement
        try (
                PreparedStatement statement = connection.prepareStatement("SELECT * FROM t_ddl where id IN(?, ?)",
                        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
        ) {
            statement.setInt(1, 19);
            statement.setInt(2, 89);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                System.out.printf("%s\t%s\t%s\t%n", rs.getInt(1), rs.getString(2), rs.getString(3));
                if (rs.getString("username").equals("name18")) {
                    rs.updateString("username", "new_name_18");
                    rs.updateRow();
                } else if (rs.getString("username").equals("name88")) {
                    rs.updateString("username", "new_name_88");
                    rs.updateRow();
                }
            }
            SQLUtil.displayResultSet(rs, 3);
        }
    }

    @After
    public void tearDown() {
        try {
            connection.close();
        } catch (SQLException ignored) {
        }
    }
}

• SQLUtil

public static void displayResultSet(ResultSet result, int column) {
    try {
        result.beforeFirst();
        while (result.next()) {
            for (int i = 1; i <= column; ++i) {
                System.out.printf("%s\t", result.getObject(i));
            }
            System.out.printf("%s%n", result.getObject(column));
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

---

ResultSetMetaData

ResultSet提供了getMetaData()方法来获取ResultSetMetaData以分析关于ResultSet的描述信息(前面我们已经使用ResultSetMetaData来获取结果集的列数以及列名):

ResultSetMetaData方法	描述
int getColumnCount()	Returns the number of columns in this ResultSet object.
String getColumnLabel(int column)	Gets the designated column’s suggested title for use in printouts and displays.
String getColumnName(int column)	Get the designated column’s name.
int getColumnType(int column)	Retrieves the designated column’s SQL type.
String getColumnTypeName(int column)	Retrieves the designated column’s database-specific type name.
boolean isAutoIncrement(int column)	Indicates whether the designated column is automatically numbered.

• analyseResult

@Test
public void analyseResult() throws SQLException {
    try (
            PreparedStatement statement = connection.prepareStatement("SELECT * FROM t_ddl")
    ) {
        ResultSetMetaData meta = statement.executeQuery().getMetaData();
        for (int i = 1; i <= meta.getColumnCount(); ++i) {
            System.out.printf("label: %s, name: %s, type: %s%n", meta.getColumnLabel(i), meta.getColumnName(i), meta.getColumnTypeName(i));
        }
    }
}

注: 因为获取ResultSetMetaData只能通过ResultSet的getMetaData()方法,所以使用ResultSetMetaData就需要一定的运行时开销;因此如果在编码过程中已经知道列数/列名/类型等信息, 就没有再用ResultSetMetaData了.

---

RowSet

javax.sql.RowSet继承自ResultSet, RowSet的子接口有CachedRowSet, FilteredRowSet, JdbcRowSet, JoinRowSet, WebRowSet, 其中只有JdbcRowSet需要保持与数据库的连接, 其他都是离线RowSet.

与ResultSet相比, RowSet默认就是可滚动/可更新/可序列化的结果集,因此可以作为JavaBean使用(比如在网络上传输,用于同步两端数据).
而对于离线RowSet, 程序在创建RowSet时已把数据从底层数据库读取到了内存,因此可以充分发挥内存的优势,降低数据库Server的负载,提高性能.

RowSet接口提供了如下常用方法:

方法	描述
void setUrl(String url)	Sets the URL this RowSet object will use when it uses the DriverManager to create a connection.
void setUsername(String name)	Sets the username property for this RowSet object to the given String.
void setPassword(String password)	Sets the database password for this RowSet object to the given String.
void setCommand(String cmd)	Sets this RowSet object’s command property to the given SQL query.
void setXxx(String parameterName/int parameterIndex, X x)
void execute()	Fills this RowSet object with data.

Java 1.7为RowSet提供了RowSetProvider与RowSetFactory工具, RowSetProvider负载创建RowSetFactory, RowSetFactory提供如下方法创建RowSet实例:

方法	描述
CachedRowSet createCachedRowSet()	Creates a new instance of a CachedRowSet.
FilteredRowSet createFilteredRowSet()	Creates a new instance of a FilteredRowSet.
JdbcRowSet createJdbcRowSet()	Creates a new instance of a JdbcRowSet.
JoinRowSet createJoinRowSet()	Creates a new instance of a JoinRowSet.
WebRowSet createWebRowSet()	Creates a new instance of a WebRowSet.

• JdbcRowSetClient

/**
 * @author jifang
 * @since 16/2/19 上午9:55.
 */
public class JdbcRowSetClient {

    private JdbcRowSet set;

    @Before
    public void setUp() throws IOException, SQLException, ClassNotFoundException {
        Properties config = SQLUtil.loadConfig("common.properties");

        Class.forName(config.getProperty("mysql.driver.class"));

        set = RowSetProvider.newFactory().createJdbcRowSet();
        set.setUrl(config.getProperty("mysql.url"));
        set.setUsername(config.getProperty("mysql.user"));
        set.setPassword(config.getProperty("mysql.password"));
    }

    @Test
    public void select() throws SQLException {
        set.setCommand("select * from t_ddl");
        set.execute();

        // 反向迭代
        set.afterLast();
        while (set.previous()) {
            System.out.printf("%d\t%s\t%s%n", set.getInt(1), set.getString(2), set.getString(3));
            if (set.getInt(1) == 187) {
                set.updateString("username", "new_188_name");
                set.updateRow();
            }
        }
    }

    @After
    public void tearDown() {
        try {
            set.close();
        } catch (SQLException e) {
        }
    }
}

可将初始化RowSet操作封装成一个方法:

• SQLUtil

public static RowSet initRowSet(RowSet set, Properties config) {
    try {
        Class.forName(config.getProperty("mysql.driver.class"));
        set.setUrl(config.getProperty("mysql.url"));
        set.setUsername(config.getProperty("mysql.user"));
        set.setPassword(config.getProperty("mysql.password"));

        return set;
    } catch (ClassNotFoundException | SQLException e) {
        throw new RuntimeException(e);
    }
}

---

离线RowSet

前面查询得到ResultSet后必须立即处理,否则一旦Connection连接关闭,再去读/写ResultSet就会引发异常.而离线RowSet会直接将数据读入内存,封装成RowSet对象,CachedRowSet是所有离线RowSet的父接口,提供了如下实用方法:

方法	描述
void populate(ResultSet data)	Populates this CachedRowSet object with data from the given ResultSet object.
void acceptChanges()	Propagates row update, insert and delete changes made to this CachedRowSet object to the underlying data source.
void acceptChanges(Connection con)	Propagates all row update, insert and delete changes to the data source backing this CachedRowSet object using the specified Connection object to establish a connection to the data source.

• CachedRowSetClient

/**
 * @author jifang
 * @since 16/2/19 上午10:32.
 */
public class CachedRowSetClient {

    private CachedRowSet query(String config, String sql) {

        /*Connection/Statement/ResultSet会自动关闭*/
        try (
                Connection connection = ConnectionManger.getConnectionHikari(config);
                Statement statement = connection.createStatement();
                ResultSet rs = statement.executeQuery(sql)
        ) {
            CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet();
            rowSet.populate(rs);
            return rowSet;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void client() throws SQLException {
        CachedRowSet set = query("common.properties", "select * from t_ddl");

        // 此时RowSet已离线
        while (set.next()) {
            System.out.printf("%s\t%s\t%s%n", set.getInt(1), set.getString(2), set.getString(3));
            if (set.getInt(1) == 3) {
                set.updateString(3, "new3_password3_3");
                set.updateRow();
            }
        }

        // 重新获得连接
        Connection connection = ConnectionManger.getConnectionHikari("common.properties");
        connection.setAutoCommit(false);
        // 把对RowSet所做的修改同步到数据库
        set.acceptChanges(connection);
    }
}

---

离线RowSet分页

由于CachedRowSet会将底层数据库数据直接装载到内存,因此如果SQL查询返回数据过大,可能会导致内存溢出.为了解决这个问题,CachedRowSet提供了分页功能: 一次只装载ResultSet的部分记录,这样可以避免CachedRowSet占用内存过大.

方法	描述
void populate(ResultSet rs, int startRow)	Populates this CachedRowSet object with data from the given ResultSet object.
void setPageSize(int size)	Sets the CachedRowSet object’s page-size.
boolean nextPage()	Increments the current page of the CachedRowSet.
boolean previousPage()	Decrements the current page of the CachedRowSet.

• CachedRowSetClient

public class CachedRowSetClient {

    @Test
    public void cachedRowSetPaging() throws SQLException {

        int page = 4;
        int size = 10;

        try (
                ResultSet rs = ConnectionManger.getConnectionHikari("common.properties")
                        .createStatement()
                        .executeQuery("SELECT * FROM t_ddl ORDER BY id")
        ) {
            CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet();
            rowSet.populate(rs, (page - 1) * size + 1);
            rowSet.setPageSize(size);

            while (rowSet.nextPage()) {
                rowSet.next();
                displayRowSet(rowSet, 3);
            }
        }
    }

    private void displayRowSet(RowSet set, int column) {
        try {
            for (int i = 1; i <= column; ++i) {
                System.out.printf("%s\t", set.getString(i));
            }
            System.out.println();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

事务

事务是由一步/几步数据库操作序列组成的逻辑执行单元, 这些操作要么全部执行, 要么全部不执行.

注: MySQL事务功能需要有InnoDB存储引擎的支持, 详见MySQL存储引擎InnoDB与Myisam的主要区别.

---

ACID特性

• 原子性(A: Atomicity): 事务是不可再分的最小逻辑执行体;
• 一致性(C: Consistency): 事务执行的结果, 必须使数据库从一个一致性状态, 变为另一个一致性状态.
• 隔离性(I: Isolation): 各个事务的执行互不干扰, 任意一个事务的内部操作对其他并发事务都是隔离的(并发执行的事务之间不能看到对方的中间状态,不能互相影响)
• 持续性(D: Durability): 持续性也称持久性(Persistence), 指事务一旦提交, 对数据所做的任何改变都要记录到永久存储器(通常指物理数据库).

---

Commit/Rollback

• 当事务所包含的全部操作都成功执行后提交事务,使操作永久生效,事务提交有两种方式:
  1). 显式提交: 使用commit;
  2). 自动提交: 执行DDL/DCL语句或程序正常退出;

• 当事务所包含的任意一个操作执行失败后应该回滚事务, 使该事务中所做的修改全部失效, 事务回滚也有两种方式:
  1). 显式回滚: 使用rollback;
  2). 自动回滚: 系统错误或强行退出.

注意: 同一事务中所有的操作,都必须使用同一个Connection.

---

JDBC支持

JDBC对事务的支持由Connection提供, Connection默认打开自动提交,即关闭事务,SQL语句一旦执行, 便会立即提交数据库,永久生效,无法对其进行回滚操作,因此需要关闭自动提交功能.

• 首先创建一张表用于测试

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `money` decimal(10,0) unsigned zerofill NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=UTF8;

• 插入两条测试数据

INSERT INTO `account` (`name`, `money`) VALUES ('feiqing', '7800');
INSERT INTO `account` (`name`, `money`) VALUES ('xiaofang', '7800');

• No Transaction

/**
 * @author jifang
 * @since 16/2/19 下午5:02.
 */
public class TransactionClient {

    private Connection connection = ConnectionManger.getConnection("common.properties");


    @Test
    public void noTransaction() throws SQLException {
        try (
                PreparedStatement minusSM = connection.prepareStatement("UPDATE `account` SET `money`=(`money` - ?) WHERE `name`=?");
                PreparedStatement addSM = connection.prepareStatement("UPDATE `account` SET `money`=(`money` + ?) WHERE `name`=?")
        ) {
            // 从feiqing账户转出
            minusSM.setBigDecimal(1, new BigDecimal(100));
            minusSM.setString(2, "feiqing");
            minusSM.execute();

            // 中途抛出异常, 会导致两账户前后不一致
            if (true){
                throw new RuntimeException("no-transaction");
            }

            // 转入xiaofang账户
            addSM.setBigDecimal(1, new BigDecimal(100));
            addSM.setString(2, "xiaofang");
            addSM.execute();
        }
    }

    @After
    public void tearDown() {
        try {
            connection.close();
        } catch (SQLException e) {
        }
    }
}

• By Transaction

@Test
public void byTransaction() throws SQLException {

    boolean autoCommitFlag = connection.getAutoCommit();
    // 关闭自动提交, 开启事务
    connection.setAutoCommit(false);

    try (
            PreparedStatement minusSM = connection.prepareStatement("UPDATE `account` SET `money`=(`money` - ?) WHERE `name`=?");
            PreparedStatement addSM = connection.prepareStatement("UPDATE `account` SET `money`=(`money` + ?) WHERE `name`=?")
    ) {
        // 从feiqing账户转出
        minusSM.setBigDecimal(1, new BigDecimal(100));
        minusSM.setString(2, "feiqing");
        minusSM.execute();

        // 中途抛出异常: rollback
        if (true) {
            throw new RuntimeException("no-transaction");
        }

        // 转入xiaofang账户
        addSM.setBigDecimal(1, new BigDecimal(100));
        addSM.setString(2, "xiaofang");
        addSM.execute();
        connection.commit();
    } catch (Throwable e) {
        connection.rollback();
        throw new RuntimeException(e);
    } finally {
        connection.setAutoCommit(autoCommitFlag);
    }
}

注意: 当Connection遇到一个未处理的SQLException时, 程序将会非正常退出,事务也会自动回滚;但如果程序捕获了该异常, 则需要在异常处理块中显式地回滚事务.

---

隔离级别

在相同数据环境下,使用相同输入,执行相同操作,根据不同的隔离级别,会导致不同的结果.不同的事务隔离级别能够解决的数据并发问题的能力是不同的, 由弱到强分为以下四级:

隔离级别	描述	释义
read uncommitted	读未提交数据	不符合原子性,称为”脏读”, 在实际业务中不用.
read commited	读已提交数据(Oracle)	事务执行中,读不到另一个事务正在进行的操作,但可以读到另一个事务结束的操作.
repeatable read	可重复读(MySQL)	在事务执行中,所有信息都来自事务开始的那一瞬间的信息,不受其他已提交事务的影响.
serializeable	串行化	所有的事务按顺序执行,也就没有了冲突的可能.隔离级别最高,但事务相互等待时间太长,性能最差,少用.

• MySQL设置事务隔离级别:
  set session transaction isolation level [read uncommitted | read committed | repeatable read |serializable]
  查看当前事务隔离级别:
  select @@tx_isolation

• JDBC设置隔离级别
  connection.setTransactionIsolation(int level)
  level可为以下值:
  1). Connection.TRANSACTION_READ_UNCOMMITTED
  2). Connection.TRANSACTION_READ_COMMITTED
  3). Connection.TRANSACTION_REPEATABLE_READ
  4). Connection.TRANSACTION_SERIALIZABLE

附: 事务并发读问题
1. 脏读(dirty read):读到另一个事务的未提交的数据,即读取到了脏数据(read commited级别可解决).
2. 不可重复读(unrepeatable read):对同一记录的两次读取不一致,因为另一事务对该记录做了修改(repeatable read级别可解决)
3. 幻读/虚读(phantom read):对同一张表的两次查询不一致,因为另一事务插入了一条记录(repeatable read级别可解决)

• 不可重复读和幻读的区别:
  
  1. 不可重复读是读取到了另一事务的更新;
  2. 幻读是读取到了另一事务的插入(MySQL中无法测试到幻读,效果与不可重复读一致);
     其他关于并发事务问题可参考<数据库事务并发带来的问题>

---

批处理

多条SQL语句被当做同一批操作同时执行.

调用Statement对象的addBatch(String sql)方法将多条SQL语句收集起来, 然后调用executeBatch()同时执行.
为了让批量操作可以正确进行, 必须把批处理视为单个事务, 如果在执行过程中失败, 则让事务回滚到批处理开始前的状态.

public class SQLClient {

    private Connection connection = null;
    private Random random = new Random();

    @Before
    public void setUp() {
        connection = ConnectionManger.getConnectionHikari("common.properties");
    }

    @Test
    public void updateBatch() throws SQLException {
        List<String> sqlList = Lists.newArrayListWithCapacity(10);
        for (int i = 0; i < 10; ++i) {
            sqlList.add("INSERT INTO user(name, password) VALUES('student" + i + "','" + encodeByMd5(random.nextInt() + "") + "')");
        }
        int[] results = update(connection, sqlList);
        for (int result : results) {
            System.out.printf("%d ", result);
        }
    }

    private int[] update(Connection connection, List<String> sqlList) {

        boolean autoCommitFlag = false;
        try {
            autoCommitFlag = connection.getAutoCommit();

            // 关闭自动提交, 打开事务
            connection.setAutoCommit(false);

            // 收集SQL语句
            Statement statement = connection.createStatement();
            for (String sql : sqlList) {
                statement.addBatch(sql);
            }

            // 批量执行 & 提交事务
            int[] result = statement.executeBatch();
            connection.commit();

            return result;
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ignored) {
            }
            throw new RuntimeException(e);
        } finally {
            try {
                connection.setAutoCommit(autoCommitFlag);
            } catch (SQLException ignored) {
            }
        }
    }

    private String encodeByMd5(String input) {
        try {
            MessageDigest md5 = MessageDigest.getInstance("MD5");
            BASE64Encoder base64Encoder = new BASE64Encoder();
            return base64Encoder.encode(md5.digest(input.getBytes("utf-8")));
        } catch (NoSuchAlgorithmException | UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
    }

    @After
    public void tearDown() {
        try {
            connection.close();
        } catch (SQLException ignored) {
        }
    }
}

注:
1). 对于批处理,也可以使用PreparedStatement,建议使用Statement,因为PreparedStatement的预编译空间有限,当数据量过大时,可能会引起内存溢出.
2). MySQL默认也没有打开批处理功能,需要在URL中设置rewriteBatchedStatements=true参数打开.

---

DbUtils

commons-dbutils是Apache Commons组件中的一员,提供了对JDBC的简单封装,以简化JDBC编程;使用dbutils需要在pom.xml中添加如下依赖:

<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.6</version>
</dependency>

dbutils的常用类/接口如下:

1. DbUtils: 提供了一系列的实用静态方法(如:close());
2. ResultSetHandler: 提供对结果集ResultSet与JavaBean等的转换;
3. QueryRunner:
   
   • update()(执行insert/update/delete)
   • query()(执行select)
   • batch()(批处理).

---

QueryRunner更新

常用的update方法签名如下:

int update(String sql, Object... params);
int update(Connection conn, String sql, Object... params);

/**
 * @author jifang
 * @since 16/2/20 上午10:25.
 */
public class QueryRunnerClient {

    @Test
    public void update() throws SQLException {
        QueryRunner runner = new QueryRunner(ConnectionManger.getDataSourceHikari("common.properties"));
        String sql = "INSERT INTO t_ddl(username, password) VALUES(?, ?)";
        runner.update(sql, "fq", "fq_password");
    }
}

第二种方式需要提供Connection, 这样多次调用update可以共用一个Connection, 因此调用该方法可以支持事务;

---

QueryRunner查询

QueryRunner常用的query方法签名如下:

<T> T query(String sql, ResultSetHandler<T> rsh, Object... params);
<T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params);

query()方法会通过sql语句和params参数查询出ResultSet，然后通过ResultSetHandler将ResultSet转换成对应的JavaBean返回.

public class QueryRunnerClient {

    // ...

    @Test
    public void select() throws SQLException {
        QueryRunner runner = new QueryRunner();
        String sql = "SELECT * FROM t_ddl WHERE id = ?";
        TDDL result = runner.query(ConnectionManger.getConnectionHikari("common.properties"), sql, rsh, 7);
        System.out.println(result);
    }

    private ResultSetHandler<TDDL> rsh = new ResultSetHandler<TDDL>() {
        @Override
        public TDDL handle(ResultSet rs) throws SQLException {
            TDDL tddl = new TDDL();
            if (rs.next()) {
                tddl.setId(rs.getInt(1));
                tddl.setUsername(rs.getString(2));
                tddl.setPassword(rs.getString(3));
            }
            return tddl;
        }
    };

    private static class TDDL {
        private Integer id;

        private String username;

        private String password;

        public Integer getId() {
            return id;
        }

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

        public String getUsername() {
            return username;
        }

        public void setUsername(String username) {
            this.username = username;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }

        @Override
        public String toString() {
            return "TDDL{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    '}';
        }
    }
}

---

ResultSetHandler

在上例中, 我们使用自定的ResultSetHandler将ResultSet转换成JavaBean, 但实际上dbutils默认已经提供了很多定义良好的Handler实现:

1. BeanHandler : 单行处理器,将ResultSet转换成JavaBean;
2. BeanListHandler : 多行处理器,将ResultSet转换成List<JavaBean>;
3. MapHandler : 单行处理器,将ResultSet转换成Map<String,Object>, 列名为键;
4. MapListHandler : 多行处理器,将ResultSet转换成List<Map<String,Object>>;
5. ScalarHandler : 单行单列处理器,将ResultSet转换成Object(如保存SELECT COUNT(*) FROM t_ddl).
6. ColumnListHandler : 多行单列处理器,将ResultSet转换成List<Object>(使用时需要指定某一列的名称/编号,如new ColumListHandler(“name”):表示把name列数据放到List中);

public class QueryRunnerClient {

    private QueryRunner runner = new QueryRunner(ConnectionManger.getDataSourceHikari("common.properties"));

    @Test
    public void clientBeanHandler() throws SQLException {
        String sql = "SELECT * FROM t_ddl WHERE id = ?";
        TDDL result = runner.query(sql, new BeanHandler<>(TDDL.class), 7);
        System.out.println(result);
    }

    @Test
    public void clientBeanListHandler() throws SQLException {
        String sql = "SELECT * FROM t_ddl";
        List<TDDL> result = runner.query(sql, new BeanListHandler<>(TDDL.class));
        System.out.println(result);
    }

    @Test
    public void clientScalarHandler() throws SQLException {
        String sql = "SELECT COUNT(*) FROM t_ddl";
        Long result = runner.query(sql, new ScalarHandler<Long>());
        System.out.println(result);
    }

    @Test
    public void clientColumnListHandler() throws SQLException {
        String sql = "SELECT * FROM t_ddl";
        List<String> query = runner.query(sql, new ColumnListHandler<String>("username"));
        for (String i : query) {
            System.out.printf("%n%s", i);
        }
    }
}

---

QueryRunner批处理

QueryRunner提供了批处理方法int[] batch(String sql, Object[][] params)(由于更新一行时需要Object[] param作为参数, 因此批处理需要指定Object[][] params,其中每个Object[]对应一条记录):

public class QueryRunnerClient {

    private QueryRunner runner = new QueryRunner(ConnectionManger.getDataSourceHikari("common.properties"));

    private Random random = new Random();

    @Test
    public void clientBeanHandler() throws SQLException {

        String sql = "INSERT INTO t_ddl(username, password) VALUES(?, ?)";
        int count = 46;
        Object[][] params = new Object[count][];
        for (int i = 0; i < count; ++i) {
            params[i] = new Object[]{"student-" + i, "password-" + random.nextInt()};
        }

        runner.batch(sql, params);
    }
}

---