JDBC 详解

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

原文发布于微信公众号 - 代码拾遗(gh_8f61e8bcb1b1)

原文发表时间:2018-05-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏最高权限比特流

JDBC为什么要使用PreparedStatement而不是Statement

1522
来自专栏java达人

使用jdbc连接mysql

image.png JDBC连接MYSQL数据库: package cn.outofmemory.test;import java.sql.Connec...

2427
来自专栏java一日一条

JDBC为什么要使用PreparedStatement而不是Statement

PreparedStatement是java.sql包下面的一个接口,用来执行SQL语句查询,通过调用connection.preparedStatement(...

892
来自专栏Hongten

Hibernate逆向工程原理_java版本

之前在一篇"Hibernate 逆向工程生成POJO类和映射文件"文章中,一位朋友提出了这样的问题:

951
来自专栏Ryan Miao

sqlite - java 初学

进来准备使用一种embedded database,即嵌入式数据库,方便随项目本地存储。目前学习打算是sqlite和H2。 document:http://ww...

3309
来自专栏java一日一条

JDBC为什么要使用PreparedStatement而不是Statement

PreparedStatement是java.sql包下面的一个接口,用来执行SQL语句查询,通过调用connection.preparedStatement(...

733
来自专栏java达人

JDBC为什么要使用PreparedStatement而不是Statement

PreparedStatement是用来执行SQL查询语句的API之一,Java提供了 Statement、PreparedStatement 和 Callab...

29610
来自专栏Java架构师历程

jdbc简单增删改查代码

Class.forName(“com.mysql.jdbc.Driver”).newInstance(); 我们链接的是MYSQL数据库,所以需要一个MYSQL...

3351
来自专栏C++

Windows核心编程:第3章 内核对象

662
来自专栏乐沙弥的世界

expdp impdp中 exclude/include 的使用

      exclude和include参数能够在使用expdp或impdp是对特定的对象或对象类型进行筛选或过滤。比如因工作的需要导出特定的表或不导出特定 ...

1682

扫码关注云+社区

领取腾讯云代金券