JDBC
SUN公司为了简化。统一对数据库的操作,定义一套Java操作数据库的规范,称之为JDBC。
JDBC简介
JDBC(Java DataBase Connectivity)Java数据库连接,主要有接口组成。
组成JDBC的2个包:
- java.sql
- javax.sql
开发JDBC应用需要以上2个包支持外,还需要导入相应的JDBC的数据库实现,即数据库驱动。
编写JDBC程序步骤
- 搭建实验环境:
- 在mysql中创建一个库,并创建user表和插入数据;
- 新建一个Java工程,并导入数据驱动。
- 编写程序,在程序中加载数据驱动:
DriverManager.registerDriver(Driver driver); //不建议采用这种方式
Class.forName("com.mysql.jdbc.Driver"); //建议采用这种方式
- 建立连接Connection
Connection coon = DriverManager.getConnection(url, user, pwd);
- 创建用于向数据库发送的SQL的Statement/PreparedStatement对象
Statement st = conn.createStatement();
PreparedStatement pst = conN.prepareStatement(SQL);
- 从代表结果集的ResultSet中取数据
ResultSet rs = st.excuteQuery(sql)
resultset rs = pst.executequery();
断开与数据库的连接,并释放相关资源
- 编写JDBC程序示例:
// --------------MySql脚本--------------------------
create database jdbc character set utf8 collate utf8_general_ci;
use jdbc;
create table user (
id int primary key auto_increment,
name varchar(40),
password varchar(40),
email varchar(60),
birthday date
)character set utf8 collate utf8_general_ci;
insert into user(name, password, email, birthday) values ('admin', 'admin', 'admin@126.com', '1990-02-01');
insert into user(name, password, email, birthday) values ('hsx', 'hsx', 'hsx@126.com', '1990-10-11');
insert into user(name, password, email, birthday) values ('hhh', 'hhh', 'hhh@126.com', '1990-12-12');
注:在mysql中存在着各种utf8编码格式,如下表:
1)utf8_bin -- 将字符串中的每一个字符用二进制数据存储,区分大小写。
2)utf8_general_ci -- 不区分大小写,ci为case insensitive的缩写,即大小写不敏感。
3)utf8_general_cs -- 区分大小写,cs为case sensitive的缩写,即大小写敏感。
// --------------JDBC的Java程序-------------------------
package com.hsx;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBC {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1、 加载驱动
/**
* 在实际开发中不推荐使用registerDriver()方法加载驱动,
* 原因1:若采用这种方式,驱动程序会注册两次,即在内存中会有两个Driver对象;
* 原因2:若脱离了mysql的api,脱离了musql的jar包,程序将无法编译,将来程序切换底层数据库会非常麻烦。
* 推荐使用方式:Class.forName("com.mysql.jdbc.Driver");
*/
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
//2、 连接数据库
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");
//3、 创建用于向数据库发送的SQL的Statement对象
statement = connection.createStatement();
String sql = "select id, name, password, email, birthday from user";
//4、从代表结果集的ResultSet中取数据
resultSet = statement.executeQuery(sql);
// 打印
while (resultSet.next()) {
System.out.print(resultSet.getObject("id"));
System.out.print("\t" + resultSet.getObject("name"));
System.out.print("\t" + resultSet.getObject("password"));
System.out.print("\t" + resultSet.getObject("email"));
System.out.print("\t" + resultSet.getObject("birthday"));
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e2) {
e2.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (Exception e2) {
e2.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
connection = null;
}
}
}
}
编写JDBC程序的注意点
JDBC程序中的DriverManager用于加载驱动
DriverManager.registerDriver(Driver driver); //不建议采用这种方式
Class.forName("com.mysql.jdbc.Driver"); //建议采用这种方式
- 在实际开发中不推荐使用registerDriver()方法加载驱动,有两点原因:
- 原因1:若采用这种方式,驱动程序会注册两次,即在内存中会有两个Driver对象;
- 原因2:若脱离了mysql的api,脱离了musql的jar包,程序将无法编译,将来程序切换底层数据库会非常麻烦。
- 推荐使用方式:
Class.forName("com.mysql.jdbc.Driver");
原因: - 不会导致驱动程序对象在内存中重复出现,并且采用此种方式,程序仅仅只需要一个字符串,不需要依赖具体的驱动,使程序的灵活性更高。
JDBC程序中创建与数据库的连接Connection
connection = DriverManager.getConnection(url, user, password);
- 在实际开发中不建议采用具体的驱动类型指向getConnection()方法,比如:
import com.mysql.jdbc.JDBC4Connection;
,这是一个具体的类,应该采用import java.sql.Connection;
,这是一个接口,要注意面向接口编程的思想。 - JDBC程序中的Connection,它用于代表数据库的连接,客户端与数据库所有的交互都是通过Connection对象完成的,这个对象常用的方法有:
- createStatement():创建向数据库发送的sql的statement对象;
- preparStatement(sql):创建向数据库发送预编译sql的PreparedStatement对象;
- prepareCall(sql):创建执行存储过程的callableStatement对象;
- setAutoCommit(boolean autoCommit):设置事务是否自动提交;
- commit():在连接上提交事务;
- rollback():在此连接上回滚事务。
- 在实际开发中不建议采用具体的驱动类型指向getConnection()方法,比如:
数据库URL
- URL用于标识数据库的位置,程序员通过URL地址告诉JDBC程序连接哪个数据库,URL的写法为:
jdbc.mysql:[]//localhost:3306/test?参数名:参数值
- jdbc – 协议
- mysql – 子协议
- localhost – 主机
- 3306 – 端口
- test – 数据库名
- 常用的数据库URL地址写法:
- mysql:
jdbc.mysql:[]//localhost:3306/test
- sqlServer:
jdbc:sqlserver://localhost:1433; DatabaseName=test
- Oracle:
"jdbc:oracle:thin:@127.0.0.1:1521:huangorcl
- mysql的url地址的简写形式:
jdbc:mysql:///test
,这个前提是默认的本机和端口是3306 - 常用的属性:
useUnicode=true&characterEncoding=UTF-8
,当所有的字符编码都是用的是UTF-8user=monty&password=greaesqldb
,把用户名和密码当作参数使用,在调用getConnection()方法时,放一个参数就可。
- URL用于标识数据库的位置,程序员通过URL地址告诉JDBC程序连接哪个数据库,URL的写法为:
JDBC程序中Statement对象用于向数据库发送SQL语句
statement = connection.createStatement();
- Statement对象中常用的方法:
- executeQuery(String sql):用于向数据发送查询语句;
- executeUpdate(String sql):用于向数据库发送insert/update/delete语句
- execute(String sql):用于向数据库发送任意sql语句,这个不建议使用;
- addBatch(String sql):把多余sql语句放到一个批处理中;
- executeBatch():向数据库发送一批sql语句执行。
JDBC程序中ResultSet用于代表sql语句的执行结果
resultSet = statement.executeQuery(sql);
- ResultSet封装执行结果时,采用的类似于表格的方式,ResultSet对象维护了一个指向表格数据行的游标,初始的时候,游标在第一行之前,调用ResultSet.next()方法,可以使游标指向具体的数据行,进行调用方法获取该行的数据。
- ResultSet用于封装执行结果的,所以该对象提供的都是用于获取数据的get方法:
- 获取任意类型的数据:
- getObject(int index),注:从1开始,数据库一般都从1开始;
getObject(1)
- getOblect(String columnName),
getObject("id")
,建议使用这种方法。
- getObject(int index),注:从1开始,数据库一般都从1开始;
- 获取指定类型的数据,(封装数据时方便),如:
- getString(int index)
- getString(String columnName)
- getInt(int index), getDate(String columName)等
ResultSet常用的数据类型转换表
- BIT(1) bit(1) getBoolean() getBytes() Boolean byte[] - TINYINT getByte() Byte - SMALLINT getShort() Short - Int getInt() Int - BIGINT getLong() Long - CHAR VARCHAR LONGVARCHAR getString() String - TEXT(clob) BLOB getClob() getBlob() Clob Blob - DATE getDate() java.sql.Date - TIME getTime() java.sql.Time - TIMESTAMP getTimestamp() java.sql.Timestamp
ResultSet还提供了对结果集进行滚动的方法:
- next():移动到下一行
- previous():移动到前一行
- absolute():移动到指定行
- beforeFirst():移动到ResultSet的最前面,注意:打印第一行,在执行beforeFirst()这个方法后,还需要执行next()方法,因为beforeFirst()把游标放到了表头。
- afterLast():移动到ResultSet的最后面,注意:打印最后一行时,在执行了afterLast()这个方法后,还需要执行previous()方法,因为afterLast()把游标放到了表尾。
释放资源:
- JDBC程序运行完后,切记要释放程序在运行过程中创建的域数据库进行交互的对象,这些对象通常是ResultSet,Statement和Connection对象。
- 特别是Connection对象,它是非常稀有的资源,用完后必须马上释放,如果Connection不能及时。正确的关闭,极易导致系统宕机。Connection的使用原则是尽量晚创建,尽早的释放。
- 为了确保资源释放的代码能运行,资源释放代码也一定要放在finally语句中。
使用JDBC对数据库进行CRUD
JDBC中的Statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句
- Statement对象的executeUpdate()方法,用于向数据库发送增删改的sql语句,executeUpdate()方法执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
- Statemnet对象的executeQuery()方法用于向数据库发送查询语句,executeQuery()方法返回代表查询结果的ResultSet对象。
- 示例:
// ---------------数据库的配置文件db.properties------------------------
driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql:///jdbc
username = root
password = 123456
// -------获取配置文件,得到连接,关闭流操作的工具类JDBCUtil.java---------
package com.hsx.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtil {
private static String driverClassName = null;
private static String url = null;
private static String username = null;
private static String password = null;
/**
* 获取配置文件,加载数据库驱动,写在静态代码块中,表示只需要执行一次
*/
static {
try {
InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driverClassName = properties.getProperty("driverClassName");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driverClassName);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 得到连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
Connection connection = DriverManager.getConnection(url, username, password);
return connection;
}
/**
* 释放资源
* @throws Exception
*/
public static void release(ResultSet resultSet, Statement statement, Connection connection) throws Exception {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
connection = null;
}
}
}
// ---------------对数据库表的CRUD操作-----------------------------
package com.hsx;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import org.junit.Test;
import com.hsx.util.JDBCUtil;
/**
* CRUD
*
* @author hsx
*
*/
public class CRUD {
@Test
public void testAdd() throws Exception {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "insert into user values (5, 'hhh', 'hhh', 'hhh@126.com', '1990-02-25')";
int number = statement.executeUpdate(sql);
if (number > 0) {
System.out.println("添加成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, statement, connection);
}
}
@Test
public void testDelete() throws Exception {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "delete from user where id = 1";
int number = statement.executeUpdate(sql);
if (number > 0) {
System.out.println("删除成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, statement, connection);
}
}
@Test
public void testUpdate() throws Exception {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "update user set password = 'pppp' where id = 3";
int number = statement.executeUpdate(sql);
if (number > 0) {
System.out.println("修改成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, statement, connection);
}
}
@Test
public void testQuery() throws Exception {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "select * from user";
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getString("name"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, statement, connection);
}
}
}
PreparedStatement详解
PreparedStatement是Statement的子类,它的实例对象可以通过调用Connection.preparedStatement()
方法获得,相对于Statement对象而言:
- PreparedStatement可以避免SQL注入漏洞问题;
- Statement会使数据库频繁编译SQL,可能造成数据库缓冲区溢出。PreparedStatement可对SQL进行预编译,从而提高数据库的执行效率。
- PreparedStatement对于sql中的参数,允许使用占位符的形式进行替换,简化SQL语句的编写。
- 示例:PreparedStatement的使用
package com.hsx.prepared;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
public class CRUD {
@Test
public void testAdd() throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
String sql = "insert into user (name, password, email, birthday) values (?, ?, ?, ?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "ppp");
preparedStatement.setString(2, "ppp");
preparedStatement.setString(3, "ppp@126.com");
preparedStatement.setString(4, "1990-12-23");
int number = preparedStatement.executeUpdate();
if (number > 0) {
System.out.println("添加成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
public void testDelete() throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
String sql = "delete from user where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 5);
int number = preparedStatement.executeUpdate();
if (number > 0) {
System.out.println("删除成功!");
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
public void testUpdate() throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
String sql = "update user set password = ? where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "121121");
preparedStatement.setInt(2, 2);
int number = preparedStatement.executeUpdate();
if (number > 0) {
System.out.println("修改成功");
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
public void testQuery() throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
String sql = "select * from user where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "hhh");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.print(resultSet.getInt("id") + "\t" + resultSet.getString("name") + "\t" + resultSet.getString("password"));
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
}
使用JDBC处理大数据
在实际开发中,程序需要把大文本或者二进制数据保存到数据库中。
基本概念:大数据也称之为LOB(Large Objects),LOB又分为:clob和blob
- clob:用于存储大文本
- blob:用于存储二进制数据,例如图像、声音、二进制等
对于MySQL来说只有blob,而没有clob,mysql存储大文本采用的是Text,Text和blob分别又分为:
- TINYEXT、TEXT、MEDIUMTEXT、LONGTEXT
- TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JDBC处理大文本数据示例:
// --JDBCUtil.java文件用于获取Connection和释放流
// --xml.md和Tomcat.md文件是大文本
// --db.properties文件是数据库的基本配置文件
package com.hsx;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.Reader;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 大文本的存取
*
* @author hsx
*
*/
public class Text {
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
public static void main(String[] args) {
//testAdd();
//testQuery();
//testUpdate();
testDelete();
}
/**
* 向数据库中插入大文本数据
*/
private static void testAdd() {
try {
connection = JDBCUtil.getConnection();
String sql = "insert into text (content) values (?)";
preparedStatement = connection.prepareStatement(sql);
URL url = Text.class.getClassLoader().getResource("xml.md");
String pathname = url.getPath();
File file = new File(pathname);
preparedStatement.setCharacterStream(1, new FileReader(file), file.length());
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("添加成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
/**
* 查询大文本数据,并写到磁盘上
*/
private static void testQuery() {
try {
connection = JDBCUtil.getConnection();
String sql = "select content from text where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 2);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
Reader reader = resultSet.getCharacterStream("content");
FileWriter fileWriter = new FileWriter("D:/xml.md");
char[] c = new char[1024];
int len = -1;
while ((len = reader.read(c)) != -1) {
fileWriter.write(c, 0, len);
}
reader.close();
fileWriter.close();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
/**
* 修改大文本数据
*/
private static void testUpdate() {
try {
connection = JDBCUtil.getConnection();
String sql = "update text set content = ? where id = ?";
preparedStatement = connection.prepareStatement(sql);
URL url = Text.class.getClassLoader().getResource("Tomcat.md");
String pathname = url.getPath();
File file = new File(pathname);
preparedStatement.setCharacterStream(1, new FileReader(file), file.length());
preparedStatement.setInt(2, 2);
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("修改成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
/**
* 把大文本数据从数据库删除
*/
private static void testDelete() {
try {
connection = JDBCUtil.getConnection();
String sql = "delete from text where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 2);
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("删除成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
}
JDBC处理二进制数据示例:
// --JDBCUtil.java文件用于获取Connection和释放流
// --b.jbg和1.jpg文件是二进制文件
// --db.properties文件是数据库的基本配置文件
package com.hsx;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Blob {
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
public static void main(String[] args) {
//testAdd();
//testQuery();
//testUpdate();
testDelete();
}
/**
* 向数据库中添加二进制文件
*/
private static void testAdd() {
try {
connection = JDBCUtil.getConnection();
String sql = "insert into blobdemo (content) values (?)";
preparedStatement = connection.prepareStatement(sql);
URL url = Blob.class.getClassLoader().getResource("b.jpg");
String pathname = url.getPath();
File file = new File(pathname);
preparedStatement.setBinaryStream(1, new FileInputStream(file), file.length());
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("添加成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
/**
* 从数据库中查询二进制数据,并写到磁盘中
*/
private static void testQuery() {
try {
connection = JDBCUtil.getConnection();
String sql = "select content from blobdemo where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
InputStream inputStream = resultSet.getBinaryStream("content");
FileOutputStream fileOutputStream = new FileOutputStream("D:/b.jpg");
byte[] b = new byte[1024];
int len = -1;
while ((len = inputStream.read(b)) != -1) {
fileOutputStream.write(b, 0, len);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
/**
* 修改数据中二进制文本数据
*/
private static void testUpdate() {
try {
connection = JDBCUtil.getConnection();
String sql = "update blobdemo set content = ? where id = ?";
preparedStatement = connection.prepareStatement(sql);
URL url = Blob.class.getClassLoader().getResource("1.jpg");
String pathname = url.getPath();
File file = new File(pathname);
preparedStatement.setBinaryStream(1, new FileInputStream(file), file.length());
preparedStatement.setInt(2, 1);
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("修改成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
/**
* 删除数据库中二进制数据
*/
private static void testDelete() {
try {
connection = JDBCUtil.getConnection();
String sql = "delete from blobdemo where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("删除成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
}
使用JDBC进行批处理
业务场景:当需要向数据库发送一批SQL语句执行时,应避免向数据库一条条的发送执行,而应采用JDBC的批处理机制,以提升执行效率。
一般常用的方法有:
- addBatch(sql);
- executeBatch();
- clearBatch();
package com.hsx;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
public class Batch {
private static Connection connection = null;
private static Statement statement = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
public static void main(String[] args) {
//testBatchByStatement();
testBatchByPreparedStatement();
}
/**
* 使用Statement处理批处理
*/
private static void testBatchByStatement() {
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql1 = "insert into batchbdemo (name) values ('xiaoshan')";
String sql2 = "insert into batchbdemo (name) values ('hello')";
String sql3 = "insert into batchbdemo (name) values ('world')";
statement.addBatch(sql1);
statement.addBatch(sql2);
statement.addBatch(sql3);
statement.executeBatch(); //执行批处理
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, statement, connection);
}
}
/**
* 使用PreparedStatement处理批处理,包括对内存溢出的处理
*/
private static void testBatchByPreparedStatement() {
try {
connection = JDBCUtil.getConnection();
String sql = "insert into batchbdemo (name) values (?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 1; i <= 10000200; i++) {
preparedStatement.setString(1, "a" + i);
preparedStatement.addBatch();
if (i % 1000 == 0) {
preparedStatement.executeBatch();
preparedStatement.clearBatch(); //清除缓存
}
}
preparedStatement.executeBatch(); // 这句是最后对不能被1000整除的200的数据的批处理
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, statement, connection);
}
}
}
注:采用PreparedStatement.addBatch()实现批处理的优缺点即应用场合:
- 优点:发送的预编译后的SQL语句,执行效率高。
- 缺点:只能应用在SQL语句相同,但参数不同的批处理中。
- 应用场合:PreparedStatement的批处理经常用于在同一个表中批量插入数据,或批量更新表的数据。
获得数据库自动生成的主键
示例:
package com.hsx;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* 获取出入的记录的主键:insert语句时才有效,要求数据库能自动生成主键
* @author hsx
*
*/
public class GenAutoKey {
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
public static void main(String[] args) {
getGenAutoKey();
}
private static void getGenAutoKey() {
try {
connection = JDBCUtil.getConnection();
String sql = "insert into genautokey (name) values (?)";
preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); // Statement.RETURN_GENERATED_KEYS是默认的,可以不用加
preparedStatement.setString(1, "admin");
preparedStatement.executeUpdate();
resultSet = preparedStatement.getGeneratedKeys(); //这里返回的是id的记录,不能用resultSet.getObject("id");
if (resultSet.next()) {
System.out.println(resultSet.getObject(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
}
JDBC调用存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。将封装好的复杂逻辑放在数据库中执行。
示例:
// 存储过程:
/*
delimiter $$
CREATE PROCEDURE `demoSp`(IN inputParam VARCHAR(255), INOUT inOutParam varchar(255))
BEGIN
SELECT CONCAT('hello---', inputParam) into inOutParam;
END $$
*/
package com.hsx;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
/**
* 调用存储过程
* @author hsx
*
*/
public class Procddure {
private static Connection connection = null;
private static CallableStatement callableStatement = null;
private static ResultSet resultSet = null;
public static void main(String[] args) {
procedure();
}
private static void procedure() {
try {
connection = JDBCUtil.getConnection();
String sql = "{call demosp(?, ?)}";
callableStatement = connection.prepareCall(sql);
callableStatement.setString(1, "xiaosan");
callableStatement.registerOutParameter(2, Types.VARCHAR);
callableStatement.execute();
System.out.println(callableStatement.getString(2));
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, callableStatement, connection);
}
}
}
事务
事务的概念:事务指在逻辑上的一组操作,组成这组操作的各个单元,要不全部成功,要不全部不成功,比如转账的操作。
数据库开启事务命令:####
- start transaction; 开启事务
- rollback; 回滚事务
- commit; 提交事务
JDBC控制事务语句 ####
当JDBC程序向数据库获取一个Connection对象时,默认情况下这个Connection对象会自动向数据库提交在它上面的sql语句。若想关闭这种默认方式,让多条sql在一个事务中执行,connection.setAutoCommit(false)
。JDBC控制事务语句:
- connection.setAutoCommit(false); start transaction;
- connection.rollback(); rollback;
- connection.commit(); commit;
/*
create databases jdbc character set utf8 collate utf8_general_ci;
use jdbc;
create table account(
id int primary key auto_increment,
name varchar(40),
money float
)character set utf8 collate utf8_general_ci;
insert into account (name, money) values ('aaa', 1000);
insert into account (name, money) values ('bbb', 1000);
insert into account (name, money) values ('ccc', 1000);
*/
package com.hsx;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* JDBC处理事务
* @author hsx
*
*/
public class Tranaction {
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
public static void main(String[] args) {
tranaction();
}
private static void tranaction() {
try {
connection = JDBCUtil.getConnection();
connection.setAutoCommit(false); //开启事务
String sql1 = "update account set money=money+100 where name='aaa'";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
//int i = 1 / 0;
String sql2 = "update account set money=money-100 where name='bbb'";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
connection.commit();
} catch (Exception e) {
try {
connection.rollback();
connection.commit(); //关闭事务,只用commit才能关闭事务
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
}
JDBC中设置事务的回滚点: ####
- Savepoint savepoint = connection.getSavePonit()
- connection.rollback(savepoint);
- connection.commit() //回滚后必须提交
示例:
package com.hsx;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
/**
* JDBC处理事务
* @author hsx
*
*/
public class Tranaction {
public static void main(String[] args) {
tranaction();
}
private static void tranaction() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Savepoint savepoint = null;
try {
connection = JDBCUtil.getConnection();
connection.setAutoCommit(false); //开启事务
String sql1 = "update account set money=money+100 where name='aaa'";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
//设置回滚点
savepoint = connection.setSavepoint();
//int i = 1 / 0;
String sql2 = "update account set money=money-100 where name='bbb'";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
int i = 1 / 0;
String sql3 = "update account set money=money+1000 where name='ccc'";
preparedStatement = connection.prepareStatement(sql3);
preparedStatement.executeUpdate();
connection.commit();
} catch (Exception e) {
try {
connection.rollback(savepoint);
connection.commit(); //关闭事务,只用commit才能关闭事务
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
}
事务的特性(ACID) ####
- 原子性(Atomicity):事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
- 一致性(Consistency):事务必须使数据库从一个一致性状态变换到另一个一致性状态。
- 隔离性(Isolation):多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
- 持久性(Durability):一个事务一旦被提交,它对数据库中的数据的改变就是永久的,接下来即使数据发生故障也不应该对其有任何的影响。
事务的隔离性
多个线程开启各自的事务操作数据库中数据时,数据库系统要负责隔离性操作,以保证各个线程在获取数据时的准确性。如果不考虑隔离性,可能会引发问题,如:
- 脏读:指一个事务读取了另一个事务未提交的数据。
- 不可重复读:同一条记录前后不一样,在一个事务内读取表的某一行数据,多次读取结果不同。
- 虚读:同一张表前后不一样
数据库隔离性的设置语句
数据库共定义了四种隔离级别:
- Serializable:可避免脏读,不可重复读,虚读情况的发生。(串行化)
- Repeatable read:可避免脏读、不可重复读情况的发生。(可重复读)
- Read committed:可避免脏读情况发生。(读已提交)
- Read uncommitted:最低级别,以上情况均无法保证。(读未提交)
设置事务的隔离级别:set tranaction isolation level xxxxx
查询当前事务隔离级别:select @@tx_isolation
JDBC设置事务级别
Connection中的setTransactionIsolation(int level)用于设置隔离级别
level:表示Connection中的常量
先设置隔离级别,再开启事务:
- connection.setTransactionIsolaction(level);
- connection.setAutoCommit(false);
示例:
// ---------要通过开启一个MySQL的线程配合测试-------
package com.hsx;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.print.attribute.standard.MediaSize.ISO;
/**
* 实现事务隔离
* @author hsx
*
*/
public class Isolaction {
public static void main(String[] args) {
isolation();
}
private static void isolation() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); //设置事务的隔离级别
connection.setAutoCommit(false); //设置事务
String sql = "select money from account where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "aaa");
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println(resultSet.getObject(1));
}
Thread.sleep(10000);
if (resultSet.next()) {
System.out.println(resultSet.getObject(1));
}
connection.commit();
} catch (Exception e) {
try {
connection.rollback();
connection.commit();
} catch (Exception e2) {
e2.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtil.release(resultSet, preparedStatement, connection);
}
}
}
使用数据库连接池优化程序性能
应用程序直接获取连接Connection的缺点:用户每次请求都需要向数据库获得连接,而数据库创建连接通常需要消耗相对较大的资源,创建时间也较长。如果网站一天10万次的访问量,数据库服务器就需要创建10万次连接,极大的浪费数据库的资源,并且极易造成数据库服务器内存溢出、宕机。
简单的数据库连接写法示例:
package com.hsx;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.LinkedList;
public class SimpleConnectionPool {
private static LinkedList<Connection> pool = new LinkedList<Connection>();
private static String driverClassName = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/jdbc";
private static String username = "root";
private static String password = "123456";
static {
try {
//加载驱动
Class.forName(driverClassName);
//创建一定数量的连接放入连接池中 10
for (int i = 0; i < 10; i++) {
Connection connection = DriverManager.getConnection(url, username, password);
pool.add(connection);
}
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public synchronized static Connection getConnection() {
return pool.removeFirst(); //得到第一个连接,并从list中删除
}
public static void release(Connection connection) {
pool.addLast(connection); //连接池用完了再放到list中
}
}
编写数据库连接池
编写连接池需要实现java.sql.DataSource接口。DataSource接口中定义了两个重载的getConnection方法:
- Connection getConnection()
- Connection getConnection(String username, String password)
实现DataSource接口,并实现连接池功能的步骤:
- 在DataSource构造函数中批量创建与数据库的连接,并把创建的连接加入LinkedList对象中。
- 实现getConnection方法,让getConnection方法每次调用时,从LinkedList中取一个Connection返回给用户。
- 当用户用完Connection,调用Connection.close()方法时,Collection对象应保证将自己返回到LinkedList中,而不要把Connection还给数据库。
上步骤的关键点:Collection保证将自己返回到LinkedList中是此处编程的难点!具体操作如下:
对已有的类进行增强(mysql中的com.mysql.jdbc.Connection实现的close方法。增强为调用close方法时,不是关闭连接,而是把连接放回对应的LinkedList中),怎么增强:
写一个子类,覆盖父类中对应的方法(要增强的方法)
- 包装设计模式、有名:装饰设计模式。
- 定义一个类,实现与被增强对象相同的接口
- 定义一个引用变量,引用要增强的对象
- 定义构造方法,接收被增强的对象
- 对要增强的方法进行改写
- 对不需要增强的方法,调用原有对象的方法
- 动态代理
- proxy(内存中创建一个代理类,代理类与增强对象实现相同的接口)
开源数据库连接池
现在很多Web服务器(Weblogic、WebSphere、Tomcat)都提供了DataSource的实现,即连接池的实现。通常我们把DataSource的实现,按其英文含义称为数据源,数据源中都包含了数据连接池的实现。
也有一些开源组织提供了数据源的独立实现:
- DBCP数据库连接池
- C3P0数据库连接池
实际应用时不需要编写连接数据库代码,直接从数据源获得数据库的连接。我们在编程时,也应该尽量使用这些数据源的实现,以提升程序的数据库访问性。
- 使用DBCP数据库连接池(实现了DataSource接口,都带池管理)
- 拷贝jar包(commons-dbcp.jar和common-pool.jar)
- 在类路径下创建一个db.properties的配置文件
- 利用BasicDataSourceFactory创建数据源
//-----------db.properties-----------------------------
driverClassName= com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbc
username = root
password = 123456
initialSize = 10
maxActive = 50
maxIdle = 20
minIdle = 5
maxWait = 60000
connectionProperties = useUnicode=true;characterEncoding=UTF-8
defaultAutoCommit = true
#defaultReadOnly =
#defaultTransactionIsolation = READ_UNCOMMITED
// -------------利用BasicDataSourceFactory创建数据源-------------------
package com.hsx.dbcp;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class JDBCUtil_dbcp {
private static DataSource dataSource;
static {
try {
InputStream inputStream = JDBCUtil_dbcp.class.getClassLoader().getResourceAsStream("db.properties");
Properties props = new Properties();
props.load(inputStream);
BasicDataSourceFactory basicDataSourceFactory = new BasicDataSourceFactory();
dataSource = basicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws Exception {
return dataSource.getConnection();
}
public static void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
connection = null;
}
}
}
- 使用c3p0数据库连接池(实现了DataSource接口,都带池管理):
拷贝jar包(c3p0-0.9.1.2.jar和c3p0-0.9.1.2-jdk1.3.jar)另外Oracle还需用到的jar包:c3p0-oracle-thin-extras-0.9.1.2.jar;如果需要用配置文件,在类路径下创建一个名为c3p0-config.xml的配置文件。
- 不使用配置文件示例:
package com.hsx.c3p0;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCUtil_c3p0 {
private static ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
static {
try {
comboPooledDataSource.setDriverClass("com.mysql.jdbc.Driver");
comboPooledDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc");
comboPooledDataSource.setUser("root");
comboPooledDataSource.setPassword("123456");
comboPooledDataSource.setMaxPoolSize(40);
comboPooledDataSource.setMinPoolSize(10);
comboPooledDataSource.setInitialPoolSize(50);
} catch (Exception e) {
throw new ExceptionInInitializerError();
}
}
public static Connection getConnection() throws Exception {
return comboPooledDataSource.getConnection();
}
public static void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
connection = null;
}
}
}
- 使用配置文件示例:
// -----------配置文件c3p0-config.xml[注;这个配置文件必须是这个名字]-----------
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
<named-config name="jdbc">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///jdbc</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquireIncrement">10</property>
<property name="initialPoolSize">30</property>
<property name="maxPoolSize">40</property>
<property name="minPoolSize">5</property>
<property name="maxStatements">1000</property>
<property name="maxStatementsPerConnection">100</property>
</named-config>
</c3p0-config>
// -------------通过c3p0获取数据库连接池(用到配置文件)--------------------
package com.hsx.c3p0;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* 通过c3p0获取数据库连接池(用到配置文件)
* @author hsx
*
*/
public class JDBCUtil_C3p0Demo {
private static ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("jdbc");
public static Connection getConnection() throws Exception {
return comboPooledDataSource.getConnection();
}
public static void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
connection = null;
}
}
}
配置Tomcat数据源
在Web应用META-INF目录下建立一个context.xml的配置文件:
<?xml version="1.0" encoding="utf-8"?> <Context> <Resource name="jdbc/jdbc" auth="Container" type="javax.sql.DataSource" username="root" password="123456" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql:///jdbc" maxActive="8" maxIdle="4"/> </Context>
需要把数据库驱动拷贝到Tomcat安装目录/lib中(部拷贝,会出现找不到驱动的错误),
- 在需要使用数据源时,利用JNDI技术获取数据源的名称:
package com.hsx.tomcat;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class DataSourceDemo extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//JDNI:Java Naming and Directory Interface Java命名与目录服务
try {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource dataSource = (DataSource) envCtx.lookup("jdbc/jdbc");
Connection connection = dataSource.getConnection();
String sql = "select * from testdbcp";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getString("name"));
}
resultSet.close();
preparedStatement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
}
}