JDBC 详解

代码拾遗

发布于 2018-07-24 15:56:21

5880

发布于 2018-07-24 15:56:21

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.必须要结合数据库才能够获取存储过程的逻辑，无法通过代码直接获取。

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 ); // 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支持事务的方法：

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 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..." );

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2018-05-05，如有侵权请联系 cloudcommunity@tencent.com 删除

jdbc