flame

路漫漫其修远兮 吾将上下而求索

0%

mysql jdbc

发表于 更新于 分类于 ，

前言

​ 关于Mysql-JDBC的使用

JDBC

JDBC(Java Database Connectivity)是java应用统一访问数据库层的api。

JDK JDBC Version JSR Specification
jdk8 JDBC 4.2 JSR 221
jdk7 JDBC 4.1 JSR 221
jdk6 JDBC 4.0 JSR 221

Mysql JDBC

Mysql-JDBC是用于mysql的JDBC驱动

URL

Mysql Connection URL 格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
jdbc:mysql://[host][:port]/[database][?property1][=value1]...

  host - The host name where MySQL server is running.
         Default is 127.0.0.1 - the IP address of localhost.

  port - The port number where MySQL is listening for connection.
         Default is 3306.

  Database - The name of an existing database on MySQL server.
         If not specified, the connection starts no current database.

  Property - The name of a supported connection properties.
         "user" and "password" are 2 most important properties.
    
  Value - The value for the specified connection property.

样例

1
2
3
4
5
6
7
8
jdbc:mysql://localhost:3306/testDb?user=root&password=123456
jdbc:mysql://:3306/testDb?user=root&password=123456
jdbc:mysql://localhost/testDb?user=root&password=123456
jdbc:mysql://localhost:3306/?user=root&password=123456
jdbc:mysql://localhost/?user=root&password=123456
jdbc:mysql://:3306/?user=root&password=123456
jdbc:mysql:///HerongDB?user=root&password=123456
jdbc:mysql:///?user=root&password=123456

加载

  • Class.forName

    1
    2
    //Load the MySQL JDBC driver，前提是mysql driver jar存在
    Class.forName("com.mysql.jdbc.Driver");

  • system prperties

    1
    -Djdbc.drivers="com.mysql.jdbc.Driver"

  • 自动(驱动自定义: META-INF/services/jdbc.sql.Driver)

    1
    //JDK8会自动加载类路径下存在的Mysql Jdbc Driver。

连接

建立connection的两种方式：

  • by DriverManager

    1
    2
    3
    4
    5
    Connection con = DriverManager.getConnection(String mysqlConnectionUrl);

    Connection con = DriverManager.getConnection(String mysqlConnectionUrl, Properties info)

    Connection con = DriverManager.getConnection(String mysqlConnectionUrl,  String user, String password)

  • by DataSource

    1
    dataSource.getConnection

Experiments

JDBC experiments

类 功能
JdbcCase1 list jdbc dirvers
JdbcCase2 build connection by DriverManager
build connection by DataSource
JdbcCase3 java.sql.Statement api usage
JdbcCase java.sql.PreparedStatement api usage
JdbcCase5 java.sql.PreparedStatement Insert Performance

Statement

使用Statement处理SQL.

1
2
3
4
5
Statement statement = connection.createStatement();

statement.executeUpdate...

statement.executeQuery...

PreparedStatement

If you have a SQL statement that needs to be executed multiple times, it is more efficient to use a JDBC PreparedStatement object to run it.

JDBC PreparedStatement features:

  • SQL statements PreparedStatement objects are pre-compiled on the database server side.
  • IN parameters are supported in SQL statements in PreparedStatement objects.
  • Batch execution mode is supported to run the run SQL statement multiple times in a single transaction.

基本语法

To make a PreparedStatement object more flexible, you can add parameters to the embedded SQL statement with question marks (?). 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//by?符号
String embeddedSQL = "SELECT * FROM Profile WHERE ID = ?";

//get prepareStatement
PreparedStatement ps = connection.prepareStatement(embeddedSQL);

// Sets value to the n-th parameter.
ps.setXXX(1, value);
ps.setXXX(2, value);
...
ps.setXXX(n, value);

//execute
ps.execute...

批量处理

1
2
3
4
5
6
7
8
9
10
11
12
ps.setXXX(...);  // Set parameters for the first copy
...
ps.addBatch();   // Create the first copy of the SQL statement
ps.setXXX(...);  // Set parameters for the second copy
...
ps.addBatch();   // Create the second copy of the SQL statement
ps.setXXX(...);  // Set parameters for the third copy
...
ps.addBatch();   // Create the third copy of the SQL statement


ps.executeBatch(); // Execute all copies together as a batch

性能对比

结论: If you have a SQL statement that needs to be executed multiple times, it is more efficient to use a JDBC PreparedStatement object to run it.

实验代码JdbcCase5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
  551 ms used when insert   100 records by ResultSet.
  202 ms used when insert   100 records by Statement.
  188 ms used when insert   100 records by PreparedStatement.
  183 ms used when insert   100 records by PreparedStatement-BatchInsert.
=====================
  610 ms used when insert   200 records by ResultSet.
  416 ms used when insert   200 records by Statement.
  337 ms used when insert   200 records by PreparedStatement.
  386 ms used when insert   200 records by PreparedStatement-BatchInsert.
=====================
 2309 ms used when insert   500 records by ResultSet.
  970 ms used when insert   500 records by Statement.
  920 ms used when insert   500 records by PreparedStatement.
  820 ms used when insert   500 records by PreparedStatement-BatchInsert.
=====================
 3502 ms used when insert  1000 records by ResultSet.
 2000 ms used when insert  1000 records by Statement.
 2019 ms used when insert  1000 records by PreparedStatement.
 2020 ms used when insert  1000 records by PreparedStatement-BatchInsert.
=====================
 7362 ms used when insert  2000 records by ResultSet.
 4972 ms used when insert  2000 records by Statement.
 4218 ms used when insert  2000 records by PreparedStatement.
 6301 ms used when insert  2000 records by PreparedStatement-BatchInsert.
=====================

CallableStatement

CallableStatement : 支持调用存储过程,提供了对输出和输入/输出参数(INOUT)的支持;

  • A JDBC CallableStatement object allows you to call stored procedures in the database server.
  • IN and OUT parameters can be passed to stored procedures from JDBC CallableStatement objects.

Reference

Java Jdbc Guides

JDBC Basics Tutorial

Java Sql Package Summary

JDBC Tutorials Examples

JDBC介绍