笔记：JDBC 数据库
- 数据库 URL
在连接数据库时，我们必须使用各种与数据库类型相关的参数，例如主机名、端口号和数据库名称等，JDBC使用了一种与普通URL相类似的语法来描述数据库，JDBC URL 一般语法为：
jdbc:subprotocol:other stuff
其中 subprotocol 用于选择连接到数据库的具体驱动程序
other stuff 参数的格式随所使用的 subprotocol 不同而不同
示例：
MySQL 数据库：jdbc:mysql://localhost:port/dbname
- 注册驱动器类
某些JDBC 的JAR文件将自动注册驱动器类，在这种情况下可以跳过手动注册步骤，在驱动程序JAR文件中的 META-INF/services/java.sql.Driver 存在这个文件就表示自动注册。
如果驱动程序JAR文件不支持自动注册，那就需要找出数据库提供商使用的JDBC驱动器类名字，通过使用 DriverManager 可以在Java程序中加载驱动器类，例如：
Class.forName("驱动器类");
这条语句将使驱动器类被加载，由此执行可以注册驱动器的静态初始化器。
- 连接到数据库
使用 java.sql 包下的 DriverManager 驱动管理器类的 getConnection 方法获取数据库连接对象，在获取数据库连接对象需要传入数据库URL和用户名等验证信息，MySQL示例代码：
package org.drsoft;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl="jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName="dev";
String password="liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName,password);
conn.close();
}
}
- 执行SQL语句
在执行SQL 语句之前，首先需要创建一个Statement 对象，要创建 Statement 对象，需要使用调用 DriverManager.getConnection 方法获取获取的 Connection 对象，使用 Connection 对象调用 createStatement 方法来创建，代码示例如下：
- 执行插入/更新/删除SQL语句
package org.drsoft;
import java.sql.*;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl="jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName="dev";
String password="liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName,password);
Statement statement= conn.createStatement();
String sql="Insert Into Transit_Record(Total_Count,Total_Trade_Amt) Values(101,1023)";
int updateRowCount = statement.executeUpdate(sql);
System.out.println("executeUpdate row "+updateRowCount);
conn.close();
}
}
- 执行插入并获取自增长主键值
package org.drsoft;
import java.sql.*;
import java.text.SimpleDateFormat;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName = "dev";
String password = "liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
String insertDataTimeSql =
"Insert Into Transit_Record(Total_Count,Total_Trade_Amt,Create_Time) Values(101,1023,?)";
PreparedStatement ps = conn.prepareStatement(insertDataTimeSql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "2016-01-01 23:44:33");
int rowCount = ps.executeUpdate();
ResultSet keyResultSet = ps.getGeneratedKeys();
if (keyResultSet.next()) {
System.out.println("insertDateTime rowCount=" + rowCount + "\tKey=" + keyResultSet.getInt(1));
}
conn.close();
}
}
- 执行查询SQL语句
package org.drsoft;
import java.sql.*;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName = "dev";
String password = "liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
Statement statement = conn.createStatement();
String querySql = "Select * from Transit_Record Order by Record_ID DESC Limit 0,10";
ResultSet resultSet = statement.executeQuery(querySql);
while (resultSet.next()) {
System.out.println(
"RecordID=" + resultSet.getInt("Record_ID") + "\tTotalCount=" +
resultSet.getInt("Total_Count"));
}
conn.close();
}
}
- 执行参数SQL语句
package org.drsoft;
import java.sql.*;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName = "dev";
String password = "liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
String querySingleSql = "Select * from Transit_Record Where Record_ID = ?";
PreparedStatement preparedStatement = conn.prepareStatement(querySingleSql);
preparedStatement.setInt(1, 6624);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(
"RecordID=" + resultSet.getInt("Record_ID") + "\tTotalCount=" +
resultSet.getInt("Total_Count"));
}
conn.close();
}
}
- 读取 LOB
在SQL中，二进制大对象称为 BLOB，字符型大对象称为CLOB，要读取LOB，需要执行SELECT语句，然后在ResultSet 对象上调用 getBlob 或 getClob方法，这样就可以获取Blob或Clob类型对象，然后分别通过调用 getBytes 或 getBinaryStream 获取二进制数据；通过调用 getSubString 或 getCharacterStream 获取字符串数据，代码示例：
package org.drsoft;
import java.sql.*;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName = "dev";
String password = "liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
String querySingleSql = "Select Cover from Transit_Record Where Record_ID = ?";
PreparedStatement preparedStatement = conn.prepareStatement(querySingleSql);
preparedStatement.setInt(1, 6624);
ResultSet resultSet = preparedStatement.executeQuery();
if(resultSet.next()) {
Blob blob = resultSet.getBlob(1);
Image coverImage ImageIo.read(blob.getBinaryStream();
}
conn.close();
}
}
- 写入LOB
要将LOB保存到数据库，需要在 Connection 对象上调用 createBlob 或 createClob，然后获取一个用于该LOB的输出流，写入数据，并将该对象存储到数据库中，示例代码：
package org.drsoft;
import java.sql.*;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName = "dev";
String password = "liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
String querySingleSql = "Insert Into Transit_Record(Cover) Values(?)";
byte[] buff = new byte[1024];
Blob newBlob = conn.createBlob();
newBlob.setBytes(0,newBlob);
PreparedStatement preparedStatement = conn.prepareStatement(querySingleSql);
preparedStatement.setInt(1, newBlob);
int updateRowCount = preparedStatement.executeUpdate();
conn.close();
}
- 调用存储过程
- 查询存储过程
package org.drsoft;
import java.sql.*;
import java.text.SimpleDateFormat;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName = "dev";
String password = "liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
String procCallSql = "call procGet_Trade_Report(?,?)";
PreparedStatement procPreparedtatement = conn.prepareStatement(procCallSql);
procPreparedtatement.setInt(1, 1000);
procPreparedtatement.setInt(2, 4000);
ResultSet procResultSet = procPreparedtatement.executeQuery();
if (procResultSet.next()) {
System.out.println("procGet_Trade_Report SumTotalAmt=" + procResultSet.getLong(1)
+ "\tSumTotalCount=" + procResultSet.getLong(2));
}
conn.close();
}
}
- 输入和输出参数储存过程
package org.drsoft;
import java.sql.*;
import java.text.SimpleDateFormat;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName = "dev";
String password = "liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
String procReturnCallSql = "{call procTransit_Record_Create(?,?,?)}";
CallableStatement callPreParedStatement = conn.prepareCall(procReturnCallSql);
// 第一个参数是输出参数
callPreParedStatement.registerOutParameter(1, Types.INTEGER);
callPreParedStatement.setInt(2, 1000);
callPreParedStatement.setLong(3, new Long(4000));
// 调用结果返回 true 则表示返回的是结果集，返回 false 表示结果是更新计数或没有结果
callPreParedStatement.execute();
// 获取输出参数值
System.out.println("procTransit_Record_Create Record_ID=" + callPreParedStatement.getInt(1));
conn.close();
}
}
- SQL转义
"转义"语法支持各种数据库普遍支持的特性，当是数据库使用的是与数据库相关的语法变体，因此，将转义语法转译为特定的数据库语法是JDBC驱动程序的任务之一，在LIKE子句中的转义字符，"_"和"%"字符在LIKE子句中有特殊含义，用来匹配一个字符或一个字符序列，如果需要匹配所有包含"_"的字符串，就必须使用"!"转义符
- 数据库事务
默认情况下，数据库连接处于自动提交模式，每个SQL语句一旦被提交给数据库，一旦命名被提交，就无法对他进行回滚操作，在使用事务时，需要关闭这个默认值。
package org.drsoft;
import java.sql.*;
import java.text.SimpleDateFormat;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName = "dev";
String password = "liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
// 设置自动提交为 false
conn.setAutoCommit(false);
String procReturnCallSql = "{call procTransit_Record_Create(?,?,?)}";
CallableStatement callPreParedStatement = conn.prepareCall(procReturnCallSql);
callPreParedStatement.registerOutParameter(1, Types.INTEGER);
callPreParedStatement.setInt(2, 1000);
callPreParedStatement.setLong(3, new Long(4000));
// 调用结果返回 true 则表示返回的是结果集，返回 false 表示结果是更新计数或没有结果
callPreParedStatement.execute();
System.out.println("Record_ID=" + callPreParedStatement.getInt(1)+"\tCommit");
// 提交事务
conn.commit();
callPreParedStatement.execute();
System.out.println("Record_ID=" + callPreParedStatement.getInt(1)+"\tRollback");
callPreParedStatement.execute();
System.out.println("Record_ID=" + callPreParedStatement.getInt(1)+"\tRollback");
// 回滚事务
conn.rollback();
conn.close();
}
}
- 批量更新
如果有一个程序需要执行许多INSERT语句，以便将数据填入数据库表中，此时可以使用批量更新的方法来提高程序性能，在使用批量更新时，可以支持INSERT、UPDATE、DELETE和数据库定义语句相关。
package org.drsoft;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Locale;
public class JDBCManager {
public static void main(String[] args) throws SQLException {
String jdbcUrl = "jdbc:mysql://192.168.94.128:3306/hibernatedb";
String userName = "dev";
String password = "liyong";
Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
String sql = "Insert Into Transit_Record(Total_Count,Total_Trade_Amt,Create_Time) Values(101,1023,?)";
PreparedStatement batchStatement = conn.prepareStatement(sql);
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Calendar calendar = Calendar.getInstance(Locale.CHINA);
for (int i = 0; i < 10; i++) {
calendar.set(Calendar.SECOND, calendar.get(Calendar.SECOND) + 1);
batchStatement.setString(1, df.format(calendar.getTime()));
// 增加一条语句和相关参数
batchStatement.addBatch();
}
// 批量执行
int[] executeResult = batchStatement.executeBatch();
for (int i = 0; i < executeResult.length; i++) {
System.out.println("Execute Result " + executeResult[i]);
}
conn.close();
}
}
