基础总结目录2
- 3. 使用PreparedStatement实现CRUD操作
- 3.1 PreparedStatement介绍
- 3.2 PreparedStatement vs Statement
- 3.3 Java与SQL对应数据类型转换表
- 3.4 使用PreparedStatement实现增、删、改、查操作
- 4.操作BLOB类型字段
- 4.1 MySQL BLOB类型
- 4.2 向数据表中插入大数据类型
- 4.3 从数据表中读取大数据类型
- 5. 批量插入
- 5.1 批量执行SQL语句
- 5.2 高效的批量插入
说明:本篇文章承接上一篇文章---->JDBC基础篇总结,链接地址为:
JDBC基础篇总结
3. 使用PreparedStatement实现CRUD操作
3.1 PreparedStatement介绍
①可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取PreparedStatement 对象
②PreparedStatement 接口是 Statement 的 子接口,它表示一条 预编译过的 SQL 语句
③PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示,调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数,第一个参数是要设置的 SQL 语句中的参数的索引(从1开始),第二个是设置的 SQL 语句中的参数的值
3.2 PreparedStatement vs Statement
代码的可读性和可维护性。
PreparedStatement 能最大可能提高性能:
①DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用,所以语句在被DBServer的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中就会得到执行。
②在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
③(语法检查,语义检查,翻译成二进制命令,缓存)
3.3 Java与SQL对应数据类型转换表
Java类型 | SQL类型 |
---|---|
boolean | BIT |
int | INTEGER |
byte | TINYINT |
short | SMALLINT |
long | BIGINT |
String | CHAR,VARCHAR,LONGVARCHAR |
byte array | BINARY , VAR BINARY |
java.sql.Date | DATE |
java.sql.Time | TIME |
java.sql.Timestamp | TIMESTAMP |
3.4 使用PreparedStatement实现增、删、改、查操作
(1)实现增删改通用的操作
实现的步骤主要如下:
1.获取数据库的连接
2.预编译sql语句,获取PreparedStatement实例
3.填充占位符
4.执行操作
5.资源关闭
public void update(String sql,Object ...args) throws Exception {
Connection connection = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
connection = JDBCUtils.getConnection();
//2.预编译sql语句,获取PreparedStatement实例
ps = connection.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1,args[i]);
}
//4.执行操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.资源的关闭
JDBCUtils.closeResource(connection,ps);
}
}
在此之前用到的配置文件以及Java文件(根据自己的实际情况需要做出修改)
jdbc.properties
user=root
password=111111
url=jdbc:mysql://localhost:3306/testcharacterEncoding=utf8&rewriteBatchedStatements=true
driverClass=com.mysql.jdbc.Driver
数据库的连接
//获取数据库的连接
public static Connection getConnection() throws Exception {
//1.读取四个基本配置信息
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
关闭数据库的连接
public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs){
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
(2)实现查询操作
提示:在进行查询前的准备工作:数据的连接,配置信息等在上面已经展示
查询操作步骤:
①获取数据库的连接
②预编译sql语句,获取PreparedStatement实例
③填充占位符
④获取结果集
⑤获取元数据
⑥获取字段数目
⑦创建一个ArrayList集合
⑧创建一个T (根据每个不同的类创建不同类型的对象)对象
⑨获取列值和字段名
⑩通过反射给属性赋值------将对象添加到集合-------资源关闭
使用集合来记录每一条数据
public <T> ArrayList<T> getForList(Class<T> clazz,String sql,Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//获取连接
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1,args[i]);
}
//获取结果集
rs = ps.executeQuery();
//获取元数据
ResultSetMetaData ramd = rs.getMetaData();
int columnCount = ramd.getColumnCount();
ArrayList<T> arrayList = new ArrayList<T>();
while (rs.next()){
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columnValue = rs.getObject(i + 1);
String columnLabel = ramd.getColumnLabel(i + 1);
//通过反射给属性赋值
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
arrayList.add(t);
}
return arrayList;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps,rs);
}
return null;
}
4.操作BLOB类型字段
4.1 MySQL BLOB类型
①MySQL中,BLOB是一个二进制大型对象,是一个可以存储大量数据的容器,它能容纳不同大小的数据。
②插入BLOB类型的数据必须使用PreparedStatement,因为BLOB类型的数据无法使用字符串拼接写的。
③MySQL的四种BLOB类型(除了在存储的最大信息量上不同外,他们是等同的)
类型 | 大小(单位:字节) |
---|---|
TinyBlob | 255 |
Blob | 64K |
MediumBlob | 16M |
LongBlob | 4G |
④如果在指定了相关的Blob类型以后,还报错:xxx too large,那么在mysql的安装目录下,找my.ini文件加上如下的配置参数:max_allowed_packet=16M。同时注意:修改了my.ini文件之后,需要重新启动mysql服务
4.2 向数据表中插入大数据类型
插入图片,注意:需要将插入的图片放到通一个工程下:
实现步骤:
①获取数据库的连接
②预编译sql语句,获取PreparedStatement实例
③填充占位符
④执行操作
⑥关闭资源
@Test
public void InsertCustomer() throws Exception {
Connection conn = JDBCUtils.getConnection();
String sql = "insert into customers(name,email,birth,photo) values (?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1,"张杰");
ps.setObject(2,"zhangjie@126.com");
ps.setObject(3,"1999-06-05");
FileInputStream fis = new FileInputStream(new File("张杰.png"));
ps.setBlob(4,fis);
ps.execute();
JDBCUtils.closeResource(conn,ps);
}
4.3 从数据表中读取大数据类型
实现步骤:
①获取数据库的连接
②预编译sql语句,获取PreparedStatement实例
③填充占位符
④获取结果集
⑤执行查询操作
⑤获取字段值
⑥创建类的对象,将字段值填充进去
⑦获取图片的二进制对象Blob以及获取字节输入流,创建字节输出流
⑧将输入从内存读到硬盘
⑨资源关闭
@Test
public void testQuary() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
InputStream is = null;
FileOutputStream fos = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,email,birth,photo from customers where id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1,21);
rs = ps.executeQuery();
is = null;
fos = null;
if (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customer customer = new Customer(id,name,email,birth);
Blob photo = rs.getBlob("photo");
//获取字节输入流
is = photo.getBinaryStream();
fos = new FileOutputStream(new File("Jason.jpg"));
byte[] buffer = new byte[20];
int len;
while ((len = is.read(buffer)) != -1){
fos.write(buffer,0,len);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps,rs);
if (is != null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fos != null){
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
5. 批量插入
5.1 批量执行SQL语句
当需要成批插入或者更新记录时,可以采用Java的批量更新机制,这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率
JDBC的批量处理语句包括下面三个方法:
①addBatch(String):添加需要批量处理的SQL语句或是参数;
②executeBatch():执行批量处理语句;
③clearBatch():清空缓存的数据
通常我们会遇到两种批量执行SQL语句的情况:
(1)多条SQL语句的批量处理;
(2)一个SQL语句的批量传参;
5.2 高效的批量插入
第一种方式
每一次的插入单独执行,插入一次执行提交一次
@Test
public void testInsert1() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name) values (?)";
ps = conn.prepareStatement(sql);
for (int i = 0; i < 200; i++) {
ps.setObject(1,"name_"+i);
ps.execute();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps);
}
}
第二种方式
做出以下修改:
1.addBatch(), executeBatch()、cleatBatch()
2.mysql服务器默认是关闭批处理的,我们需要通过一个参数,让mysql开启批处理的支持。 ?rewriteBatchedStatements=true写在配置文件的url后
获得多个SQL语句之后执行一次
@Test
public void testInsert2() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name) values (?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 200000; i++) {
ps.setObject(1,"name_"+i);
//添加多条数据之后再进行提交
ps.addBatch();
if (i % 50 == 0){
ps.executeBatch();
ps.clearBatch();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps);
}
}
添加方式三:
最优方案:
设置为不允许自动提交,在所有的数据都加载完毕之后,再提交数据
@Test
public void testInsert3() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name) values (?)";
ps = conn.prepareStatement(sql);
conn.setAutoCommit(false);
for (int i = 1; i <= 200000; i++) {
ps.setObject(1,"name_"+i);
//添加多条数据之后再进行提交
//‘攒’sql
ps.addBatch();
if (i % 50 == 0){
//执行executeBatch()
ps.executeBatch();
//清空executeBatch()
ps.clearBatch();
}
}
conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps);
}
}