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 rows
int n = countriesStatement.executeUpdate();
// countriesStatement belongs to the class Statement
ResultSet 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 set
ResultSet resultSet = statement.executeQuery( "select * from COUNTRIES" );
// iterating through the results rows
while( 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.必须要结合数据库才能够获取存储过程的逻辑,无法通过代码直接获取。
正如之前提到的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 ); // population
updateStmt2.setObject( 2, name ); // name
// update prepared statement using executeUpdate
numberRows = updateStmt2.executeUpdate();
System.out.println( numberRows + " rows updated..." );
updateStmt2.close();
通过Statement的addBatch()方法可以提供批处理SQL的方法。
Statement statement = null;
statement = connect.createStatement();
// adding batchs to the statement
statement.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 method
int[] 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支持事务的方法:
示例:
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();
}
// 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 );
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 executeUpdate
String 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 executeUpdate
int numberRows = deleteStmt.executeUpdate( sql );
System.out.println( numberRows + " rows deleted..." );