转: JDBC详解
一、相关概念
1.什么是JDBC
JDBC(Java Data Base Connectivity, java数据库连接) 是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能够编写数据库应用程序。
2.数据库驱动
我们安装好数据库之后,我们的应用程序也是不能直接使用数据库的,必须要通过相应的数据库驱动程序,通过驱动程序去和数据库打交道。其实也是数据库厂商的JDBC接口实现,即对Connection等接口的实现类的jar文件。
二、常用接口
1. Driver接口
Driver接口由数据库厂家提供,作为java开发人员,只需要使用Driver接口就可以了。在编程中要链接数据库,必须先装载特定厂商的数据库驱动程序,不同的数据库有不同的装载方法。如:
装载MySQL驱动:Class.forName("com.mysql.jdbc.Driver");
装载Oracle驱动:Class.forName("oracle.jdbc.driver.OracleDriver");
2. Connection接口
Connection与特定数据库的连接(会话),在连接上下文中执行sql语句并返回结果。DriverManager.getConnection(url, user, password)方法建立在JDBC URL中定义的数据库Connection连接上。
连接MySql数据库:
Connection conn = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");
连接Oracle数据库:
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:database", "user", "password");
连接SqlServer数据库:
Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://host:port; DatabaseName=database", "user", "password");
常用方法:
- createStatement():创建向数据库发送sql的statement对象
- prepareStatement(sql):创建向数据库发送预编译sql的PrepareStatement对象
- prepareCall(sql):创建执行存储过程的callableStatement对象。
- setAutoCommit(boolean autoCommit):设置事务是否自动提交。
- commit():在链接上提交事务。
- rollback():在此链接上回滚事务。
3. Statement接口
用于执行静态SQL语句并返回它所生成结果的对象。
三种Statement类:
- Statement:由createStatement创建,用于发送简单的SQL语句(不带参数)。
- PreparedStatement:继承自Statement接口,由preparedStatement()创建,用于发送含有一个或多个参数的SQL语句。PreparedStatement对象比Statement对象的效率更高,并且可以防止SQL注入,所以我们一般都使用PreparedStatement。
- CallableStatement:继承自PreparedStatement接口,由方法prepareCall创建,用于调用存储过程。
常用Statement方法:
- execute(String sql):运行语句,返回是否有结果集。
- executeQuery(String sql):运行select语句,返回ResultSet结果集。
- executeUpdate(String sql):运行insert/update/delete操作,返回更新的行数。
- addBatch(String sql):把多条sql语句放到一个批处理中。
- executeBatch():向数据库发送一批sql语句执行。
4. ResultSet接口
ResultSet提供检索不同类型字段的方法,常用的有:
- getString(int index), getString(String columnName):获得在数据库里是varchar, char等类型的数据对象。
- getFloat(int index), getFloat(String columnName):获得在数据库里是Float类型的数据对象
- getDate(int index), getDate(String columnName):获得在数据库里是Date类型的数据。
- getBoolean(int index), getBoolean(String columnName):获得在数据库里是Boolean类型的数据。
- getObject(int index), getObject(String columnName):获取在数据库里任意类型的数据。
ResultSet还提供了对结果集进行滚动的方法:
- next():移动到下一行
- previous():移动到前一行
- absolute(int row):移动到指定行
- beforeFirst():移动resultSet的最前面
- afterLast():移动到resultSet的最后面
使用后依次关闭对象及连接:ResultSet -> Statement -> Connection
三、使用JDBC的步骤
加载JDBC驱动程序 -> 建立数据连接Connection -> 创建执行SQL的语句Statement -> 处理执行结果ResultSet -> 释放资源
1. 注册驱动(只做一次)
方式一:Class.forName("com.mycql.jdbc.Driver");
推荐这种方式,不会对具体的驱动类产生依赖。
方式二:DriverManager.registerDriver("com.mysql.jdbc.Driver");
会造成DriverManager中产生两个一样的驱动,并会对具体的驱动类产生依赖。
2. 建立连接
Connection conn = DriverManager.getConnection(url, user, password);
URL用于标识数据库的位置,通过URL地址告诉JDBC程序连接哪个数据库,URL的写法为:
其他参数如:useUnicode=true&characterEncoding=utf8
3. 创建执行SQL语句的statement
String id = "5";
String sql = "delete from table where id = " + id;
Statement st = conn.createStatement();
st.executeQuery(sql)
// PreparedStatement 有效的防止sql注入(SQL语句在程序运行前已经进行了预编译,当运行时动态地把参数传给PreparedStatement时,即使参数里有敏感字符如 or = '1=1'也数据库会作为一个参数一个字段的属性值来处理而不会作为一个SQL指令)
String sql = "insert into user (name, pwd) values (?, ?)";
PreparedStatement ps = conn.preparedStatement(sql);
ps.setString(1, "col_value");
ps.setString(2, "123456");
ps.executeQuery();
4.处理执行结果(ResultSet)
ResultSet rs = ps.executeQuery();
while(rs.next()){
rs.getString("col_name");
rs.getInt(1);
}
释放资源
四、事务 (ACID特点、隔离级别、提交commit、回滚rollback)
1. 批处理Batch
- 灵活指定SQL语句中的变量 -> PreparedStatement
- 对存储过程进行调用 -> CallableStatement
- 运用事务处理 -> Transaction
- 批处理
- Batch
- 对于大量的批处理,建议使用Statement,因为PreparedStatement的预编译空间有限,当数据量特别大时,会发生异常。
@Test
public void test05() {
Statement statement = null;
try {
statement = connection.createStatement();
for(int i = 0; i < 1000000; i++) {
String format = String.format("insert into eee2 set id = %d, platform='%s'", i, i);
System.out.println(format);
statement.addBatch(format);
}
int[] ints = statement.executeBatch();
System.out.println(Arrays.toString(ints));
connection.commit();
} catch (SQLException ex) {
}finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.测试事务的基本概念和用法
@Test
public void test07() {
PreparedStatement statement = null;
PreparedStatement statement2 = null;
try {
connection.setAutoCommit(false);
statement = connection.prepareStatement("INSERT INTO eee2 SET id = ?, platform = ?");
statement.setLong(1, -1);
statement.setString(2, "ni ma bi");
statement.executeUpdate();
statement2 = connection.prepareStatement("INSERT INTO eee2 values (?, ? ,?)");
statement2.setLong(1, -1);
statement2.setString(2, "ni ma bi");
statement2.setObject(3, 1);
statement2.executeUpdate();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
第二次插入,列和值不匹配,抛出异常。
java.sql.SQLException: Column count doesn't match value count at row 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:545)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826)
at com.mysql.cj.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2034)
at com.mysql.cj.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:1970)
at com.mysql.cj.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5001)
at com.mysql.cj.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1955)
at com.jianglei.mapper.JdbcTest.test07(JdbcTest.java:200)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
五、时间处理(Date和Time以及Timestamp区别,随机日期生成)
java.util.Date
- 子类:java.sql.Date
- 子类:java.sql.Time
- 子类:java.sql.Timestamp
插入时间类型
@Test
public void test08() {
PreparedStatement preparedStatement = null;
try {
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement("INSERT INTO eee3 VALUES (?, ?, ? ,?)");
preparedStatement.setLong(1, -1L);
preparedStatement.setTime(2, new Time(System.currentTimeMillis()));
preparedStatement.setDate(3, new Date(System.currentTimeMillis()));
preparedStatement.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
preparedStatement.executeUpdate();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
获取时间类型
@Test
public void test09() {
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement("SELECT * FROM test02.eee3");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
long id = resultSet.getLong(1);
Time time = resultSet.getTime(2);
Date date = resultSet.getDate(3);
Timestamp timestamp = resultSet.getTimestamp(4);
System.out.printf("id: %-10d, time: %-20s, date: %-20s, timestamp: %-20s\n", id, time, date, timestamp);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
六、CLOG文本大对象操作
CLOB (Character Lager Object)
- 用于存储大量的文本数据
- 大字段有些特殊,不同数据库处理方式不一样,大字段的操作常常是以流的方式来处理的。而非一般的字段,一次即可读出数据。
MySQL中相关类型
- TINYTEXT 最大长度为255(2^(8 - 1))字符的TEXT列。
- TEXT[M]最大长度为65535(2^(16-1))字符的TEXT列。
- MEDIUMTEXT最大长度为16777215(2^(24-1))字符的TEXT列。
- LONGTEXT最大长度为4294967295或4GB(2^(32-1))字符的TEXT列。
@Test
public void test10() {
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement("INSERT INTO eee4 SET `clob`= ?, `blob`=?");
StringReader jianglei = new StringReader("jianglei");
ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream("jianglei".getBytes());
preparedStatement.setClob(1, jianglei);
preparedStatement.setBlob(2, byteArrayInputStream);
int i = preparedStatement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
}
}
七、BLOB二进制大对象的使用
- 用于存储大量的二进制数据
- 大字段有些特殊,不同数据库处理方式不一样,大字段的操作常常是以流的方式来处理的。而非一般的字段,一次即可读出数据。
MySQL中相关类型
- TINYBLOB 最大长度为255(2^(8 - 1))字节的TEXT列。
- BLOG[M]最大长度为65535(2^(16-1))字节的TEXT列。
- MEDIUMBLOB最大长度为16777215(2^(24-1))字节的TEXT列。
- LONGBLOB最大长度为4294967295或4GB(2^(32-1))字节的TEXT列。
@Test
public void test11() {
PreparedStatement preparedStatement = null;
try{
preparedStatement = connection.prepareStatement("SELECT `clob`, `blob` FROM eee4");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Clob clob = resultSet.getClob(1);
Blob blob = resultSet.getBlob(2);
String subString = clob.getSubString(1, (int) clob.length());
byte[] bytes = blob.getBytes(1, (int) blob.length());
String s = new String(bytes);
System.out.println(subString);
System.out.println(s);
}
} catch (SQLException e) {
e.printStackTrace();
}
}