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

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

public class SQLClient {    public static void main(String[] args) throws ClassNotFoundException, SQLException {                Class.forName("com.mysql.jdbc.Driver");                Connection connection = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");                Statement statement = connection.createStatement();                ResultSet users = statement.executeQuery("SELECT * FROM user");                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();        }                users.close();        statement.close();        connection.close();    }}

    mysql    mysql-connector-java    5.1.36

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

ConnectionManger

DriverManger

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

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!");        }    }        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语句:

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

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

ConnectionManger

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

## Data Sourcemysql.driver.class=com.mysql.jdbc.Drivermysql.url=jdbc:mysql://host:port/databasemysql.user=adminmysql.password=admin

public class ConnectionManger {        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);        }    }}

public class SQLUtil {        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)提供.

C3P0

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

    com.mchange    c3p0    0.9.5.2    com.mchange    mchange-commons-java    0.2.11

public class ConnectionManger {        private static 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;    }        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,其放在类路径下:

            jdbc:mysql://host:port/database        com.mysql.jdbc.Driver        user        password        5        10        3        20        nfig name="mysql-config">        jdbc:mysql://host:port/common        com.mysql.jdbc.Driver        user        password        5        10        3        20    

这样, 我们在创建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/brettwooldridge/HikariCP,使用HikariCP需要在pom.xml中添加如下依赖:

    com.zaxxer    HikariCP    2.4.0

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 Sourcemysql.driver.class=com.mysql.jdbc.Drivermysql.url=jdbc:mysql://host:port/databasemysql.user=usermysql.password=passwordpool.max.size=20pool.min.size=3pool.init.size=10pool.max.statements=180pool.max.idle_time=60pool.max.life_time=1000

SQL执行

Statement

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

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

executeUpdate

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

@Testpublic 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);    }}

@Testpublic 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执行的结果:

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);        }    }}

@Testpublic 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来负责类型转换.

@Testpublic 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)方法来获取指定回参的值:

@Testpublic 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是结果集对象,可以通过列索引/列名来读/写数据, 它提供了如下常用方法来移动记录指针:

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

可更新/滚动的ResultSet

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

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

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

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) {        }    }}

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来获取结果集的列数以及列名):

@Testpublic 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接口提供了如下常用方法:

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

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操作封装成一个方法:

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的父接口,提供了如下实用方法:

public class CachedRowSetClient {    private CachedRowSet query(String config, String sql) {                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占用内存过大.

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特性

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');

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) {        }    }}

@Testpublic 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();        connectionmit();    } catch (Throwable e) {        connection.rollback();        throw new RuntimeException(e);    } finally {        connection.setAutoCommit(autoCommitFlag);    }}

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

隔离级别

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

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级别可解决)

不可重复读和幻读的区别:
不可重复读是读取到了另一事务的更新; 幻读是读取到了另一事务的插入(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 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 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();            connectionmit();            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中添加如下依赖:

    commons-dbutils    commons-dbutils    1.6

dbutils的常用类/接口如下:

QueryRunner更新

常用的update方法签名如下:

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

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 query(String sql, ResultSetHandler rsh, Object... params); T query(Connection conn, String sql, ResultSetHandler 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 rsh = new ResultSetHandler() {        @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实现: