原文链接：
JDBC(Java Database Connectivety),主要是用来连接数和操作数据库的API，本片文章基于JDBC4.2。
组件
Java主要通过JDBC和数据库进行交互，它支持执行不同的sql，处理不同数据源返回的结果。 在本节中主要是简单介绍一下一下JDBC中最重要的一些组件，这些组件稍后都会详细描述。 首先Java应用需要知道同哪个数据建立连接，通过java.sql.DriverManager类确定，或者直接通过JDBC的数据源，例如javax.sql.DataSource。 然后需要和数据库建立连接，通过java.sql.Connection 建立连接 最后就需要执行各种SQL，这个通过java.sql.Statement或者java.sql.PreparedStatement,java.sql.CallableStatement执行。 例如：
PreparedStatement countriesStatement = connection.prepareStatement("UPDATE COUNTRIES SET NAME = ? WHERE ID = ?");countriesStatement.setString(1, "Spain");countriesStatement.setInt(2, 123456789); // countriesStatement belongs to the class Statement, returning number of updated rowsint n = countriesStatement.executeUpdate(); // countriesStatement belongs to the class StatementResultSet rs = countriesStatement.executeQuery("SELECT NAME, POPULATION FROM COUNTRIES");//rs contains the results in rows plus some metadata 连接
使用java.sql.Connection获取和数据库的连接，通过DriverManager的getConnection()方法获取。
Class.forName("com.mysql.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/countries?user=root&password=root"); Connection 可以用来创建Statement，PreparedStatement，CallableStatement。
PreparedStatement updateStmt = connection.prepareStatement(sql);
提供提交和回滚事务功能。connnection.setAutoCommit(false)。默认是true，需要设置为false，防止自动提交事务。
数据类型
| SQL类型 | java类型 |
|---|---|
| VARCHAR | String |
| CHAR | String |
| LONGVARCHAR | String |
| BIT | boolean |
| NUMBERIC | BigDecimal |
| TINYINT | byte |
| SMALLINT | short |
| INTEGER | int |
| BIGINT | long |
| REAL | float |
| FLOAT | float |
| DOUBLE | double |
| VARBINARY | byte[] |
| BINARY | byte[] |
| DATE | java.sql.Date |
| TIME | java.sql.Time |
| TIMESTAMP | java.sql.Timestamp |
| CLOB | java.sql.Clob |
| BLOG | java.sql.Blog |
| ARRAY | java.sql.Array |
| REF | java.sql.Ref |
| STRUCT | java.sql.Struct |
Null 值SQL和Java的处理方式各不相同，处理null值的时候最好避免使用原生类型，因为原生类型不能为null，对于int可以转为0，boolean转成false等。 或者使用原生类型的包装类来处理。ResultSet类提供给了方法wasNull()来处理这类情况
Statement stmt = connection.createStatement();String sql = "SELECT NAME, POPULATION FROM COUNTRIES";ResultSet rs = stmt.executeQuery(sql);int id = rs.getInt(1);if(rs.wasNull()){ id = 0;} 数据库驱动
JDBC的驱动管理器java.sql.DriverManager是JDBC中最重要的组件。它提供了处理不同数据库的服务。 DriverManager最长用的方法是getConnection() 例如：
Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:mydb","SA","pw"); 可以通过DriverManager.registerDriver()注册驱动：
DriverManager.registerDriver(new org.hsqldb.jdbc.JDBCDriver());
或者通过Class.forName()来加载驱动
Class.forName("org.hsqldb.jdbc.JDBCDriver"); 这两种方式的主要不同是，registerDriver()需要在编译时就确定驱动在classpath中，而forName()的方式是运行时加载的。
数据库
JDBC支持多种数据库。通过使用不同的驱动程序，抽象了数据库的处理方式，使得和不同数据库交换可以使用相同的方法。这里以MySQL和HSQLDB为例： MySQL
public static void main( String[] args ) throws ClassNotFoundException, SQLException { // connection to JDBC using mysql driver Class.forName( "com.mysql.jdbc.Driver" ); Connection connect = DriverManager.getConnection("jdbc:mysql://localhost/countries?" + "user=root&password=root" ); selectAll( connect ); // close resources, in case of exception resources are not properly cleared... } /** * select statement and print out results in a JDBC result set * * @param conn * @throws SQLException */ private static void selectAll( java.sql.Connection conn ) throws SQLException { Statement statement = conn.createStatement(); ResultSet resultSet = statement.executeQuery( "select * from COUNTRIES" ); while( resultSet.next() ) { String name = resultSet.getString( "NAME" ); String population = resultSet.getString( "POPULATION" ); System.out.println( "NAME: " + name ); System.out.println( "POPULATION: " + population ); } } HSQLDB
public static void main( String[] args ) throws ClassNotFoundException, SQLException { // Loading the HSQLDB JDBC driver Class.forName( "org.hsqldb.jdbc.JDBCDriver" ); // Create the connection with the default credentials java.sql.Connection conn = DriverManager.getConnection( "jdbc:hsqldb:mem:mydb", "SA", "" ); // Create a table in memory String countriesTableSQL = "create memory table COUNTRIES (NAME varchar(256) not null primary key, POPULATION varchar(256) not null);"; // execute the statement using JDBC normal Statements Statement st = conn.createStatement(); st.execute( countriesTableSQL ); // nothing is in the database because it is just in memory, non persistent selectAll( conn ); // after some insertions, the select shows something different, in the next execution these // entries will not be there insertRows( conn ); selectAll( conn ); }... /** * select statement and print out results in a JDBC result set * * @param conn * @throws SQLException */ private static void selectAll( java.sql.Connection conn ) throws SQLException { Statement statement = conn.createStatement(); ResultSet resultSet = statement.executeQuery( "select * from COUNTRIES" ); while( resultSet.next() ) { String name = resultSet.getString( "NAME" ); String population = resultSet.getString( "POPULATION" ); System.out.println( "NAME: " + name ); System.out.println( "POPULATION: " + population ); } } 对于这两者，除了加载驱动时不同外，其他都完全相同。
返回结果集
java.sql.ResultSet表示数据库中的表的数。可以通过列名和列的索引（从1开始）获取返回值。 例如
ResultSet resultSet = statement.executeQuery("select * from COUNTRIES");while(resultSet.next()){ String name = resultSet.getString("NAME"); int population = resultSet.getInt("POPULATION"); System.out.println( "NAME: " + name ); System.out.println( "POPULATION: " + population );}resultSet.close(); 或者
// creating the result setResultSet resultSet = statement.executeQuery( "select * from COUNTRIES" );// iterating through the results rowswhile( resultSet.next() ){ // accessing column values by index or name String name = resultSet.getString( 1 ); int population = resultSet.getInt( 2 ); System.out.println( "NAME: " + name ); System.out.println( "POPULATION: " + population );}resultSet.close(); 创建ResultSet的时候也可以设置默认值，改变其行为
/*** indicating result sets properties that will be created from this statement: type,* concunrrency and holdability*/Statement statement = conn.createStatement( ResultSet. TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT );
存储过程
存储过程就是将一组SQL保存为一个逻辑执行单元，执行一个特定的任务。形式如下：
delimiter //CREATE PROCEDURE spanish (OUT population_out INT) BEGIN SELECT COUNT(*) INTO population_out FROM countries; END// delimiter ; CALL simpleproc(@a);
为了调用这个存储过程，我们需要使用CallableStatement。
String spanishProcedure = "{call spanish(?)}";CallableStatement callableStatement = connect.prepareCall(spanishProcedure);callableStatement.registerOutParameter(1, java.sql.Types.VARCHAR);callableStatement.executeUpdate();String total = callableStatement.getString(1);System.out.println( "amount of spanish countries " + total ); 不过在实际应用中并不是很推荐使用存储过程，因为存储过程时保存在数据库的， 1.更改逻辑都需要去数据库更改， 2.存储过程的代码并不如Java代码易读， 3.必须要结合数据库才能够获取存储过程的逻辑，无法通过代码直接获取。
Statement
正如之前提到的java.sql.Statement用来执行select，insert，update，delete.同时也可以执行DDL（Alter，Create，Drop）操作，基本的方法比如：executeQuery(String),executeUpdate(String)。 为了防止SQL注入，获取更好的性能可以使用PreparedStatement。 例如：
System.out.println( "Updating rows for " + name + "..." );String sql = "UPDATE COUNTRIES SET POPULATION=? WHERE NAME=?";PreparedStatement updateStmt = conn.prepareStatement(sql);updateStmt.setInt(1,10000000);updateStmt.setString(2,name);int numberRows = updateStmt.executeUpdate();System.out.println( numberRows + " rows updated..." );
如果对于类型不确定，可以使用setObject()方法
PreparedStatement updateStmt2 = conn.prepareStatement( sql );// Bind values into the parameters using setObject, can be used for any kind and type of// parameter.updateStmt2.setObject( 1, 10000000 ); // populationupdateStmt2.setObject( 2, name ); // name// update prepared statement using executeUpdatenumberRows = updateStmt2.executeUpdate();System.out.println( numberRows + " rows updated..." );updateStmt2.close();
批处理
通过Statement的addBatch()方法可以提供批处理SQL的方法。
Statement statement = null;statement = connect.createStatement();// adding batchs to the statementstatement.addBatch( "update COUNTRIES set POPULATION=9000000 where NAME='USA'" );statement.addBatch( "update COUNTRIES set POPULATION=9000000 where NAME='GERMANY'" );statement.addBatch( "update COUNTRIES set POPULATION=9000000 where NAME='ARGENTINA'" );// usage of the executeBatch methodint[] recordsUpdated = statement.executeBatch();int total = 0;for( int recordUpdated : recordsUpdated ){ total += recordUpdated;}System.out.println( "total records updated by batch " + total ); 使用PreparedStatement
String sql = "update COUNTRIES set POPULATION=? where NAME=?";PreparedStatement preparedStatement = null;preparedStatement = connect.preparedStatement(sql);preparedStatement.setObject(1,10000);preparedStatement.setObject(2,"spain");prepardStatement.addBatch();preparedStatement.setObject( 1, 1000000 ); preparedStatement.setObject( 2, "USA" ); // adding batches preparedStatement.addBatch(); // executing all batchs int[] updatedRecords = preparedStatement.executeBatch(); int total = 0; for( int recordUpdated : updatedRecords ) { total += recordUpdated; } System.out.println( "total records updated by batch " + total ); 事务
JDBC支持事务的方法：
- java.sql.Connection.setAutoCommit(boolean) 默认为true，所有的SQL执行完之后自动提交事务
- java.sql.Connection.commit() 手动提交事务
-
java.sql.Connection.rollback() 回滚事务
示例：
Class.forName( "com.mysql.jdbc.Driver" );Connection connect = null;try{ // connection to JDBC using mysql driver connect = DriverManager.getConnection( "jdbc:mysql://localhost/countries?" + "user=root&password=root" ); connect.setAutoCommit( false ); System.out.println( "Inserting row for Japan..." ); String sql = "INSERT INTO COUNTRIES (NAME,POPULATION) VALUES ('JAPAN', '45000000')"; PreparedStatement insertStmt = connect.prepareStatement( sql ); // insert statement using executeUpdate insertStmt.executeUpdate( sql ); connect.rollback(); System.out.println( "Updating row for Japan..." ); // update statement using executeUpdate -> will cause an error, update will not be // executed becaues the row does not exist sql = "UPDATE COUNTRIES SET POPULATION='1000000' WHERE NAME='JAPAN'"; PreparedStatement updateStmt = connect.prepareStatement( sql ); updateStmt.executeUpdate( sql ); connect.commit();}catch( SQLException ex ){ ex.printStackTrace(); //undoes all changes in current transaction connect.rollback();}finally{ connect.close();} CRUD示例
// Create a table in memoryString countriesTableSQL = "create memory table COUNTRIES (NAME varchar(256) not null primary key, POPULATION varchar(256) not null);";// execute the statement using JDBC normal StatementsStatement st = conn.createStatement();st.execute( countriesTableSQL );Statement insertStmt = conn.createStatement();String sql = "INSERT INTO COUNTRIES (NAME,POPULATION) VALUES ('SPAIN', '45Mill')";insertStmt.executeUpdate( sql );sql = "INSERT INTO COUNTRIES (NAME,POPULATION) VALUES ('USA', '200Mill')";insertStmt.executeUpdate( sql );sql = "INSERT INTO COUNTRIES (NAME,POPULATION) VALUES ('GERMANY', '90Mill')";insertStmt.executeUpdate( sql );System.out.println( "Updating rows for " + name + "..." );Statement updateStmt = conn.createStatement();// update statement using executeUpdateString sql = "UPDATE COUNTRIES SET POPULATION='10000000' WHERE NAME='" + name + "'";int numberRows = updateStmt.executeUpdate( sql );System.out.println( numberRows + " rows updated..." );Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery( "select * from COUNTRIES" );while( resultSet.next() ){ String name = resultSet.getString( "NAME" ); String population = resultSet.getString( "POPULATION" ); System.out.println( "NAME: " + name ); System.out.println( "POPULATION: " + population );}System.out.println( "Deleting rows for JAPAN..." );String sql = "DELETE FROM COUNTRIES WHERE NAME='JAPAN'";PreparedStatement deleteStmt = connect.prepareStatement( sql );// delete statement using executeUpdateint numberRows = deleteStmt.executeUpdate( sql );System.out.println( numberRows + " rows deleted..." );