一：Statement的弊端
使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
package com.tian.connection;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
import org.junit.Test;
public class StatementTest {
// 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
@Test
public void testLogin() {
Scanner scan = new Scanner(System.in);
System.out.print("用户名：");
String userName = scan.nextLine();
System.out.print("密 码：");
String password = scan.nextLine();
// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '
// ='1' or '1' = '1';
String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
+ "'";
User user = get(sql, User.class);
if (user != null) {
System.out.println("登陆成功!");
} else {
System.out.println("用户名或密码错误！");
}
}
// 使用Statement实现对数据表的查询操作
public <T> T get(String sql, Class<T> clazz) {
T t = null;
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 1.加载配置文件
InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
// 2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
// 3.加载驱动
Class.forName(driverClass);
// 4.获取连接
conn = DriverManager.getConnection(url, user, password);
st = conn.createStatement();
rs = st.executeQuery(sql);
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 获取结果集的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// //1. 获取列的名称
// String columnName = rsmd.getColumnName(i+1);
// 1. 获取列的别名
String columnName = rsmd.getColumnLabel(i + 1);
// 2. 根据列名获取对应数据表中的数据
Object columnVal = rs.getObject(columnName);
// 3. 将数据表中得到的数据，封装进对象
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnVal);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
}2：如何使用PreparedStatement实现对表数据进行添加操作
提前的操作：
将操作数据库的工具类封装到另一个包下
package com.tian.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import com.tian.connection.ConnectionTest;
/**
* @Description 操作数据库的工具类
* @author Tian
*
*/
public class JDBCUtils {
/**
* 获取数据库的连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
// 1.读取配置文件中的4个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
// 2.加载驱动
Class.forName(driverClass);
// 3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* @Description 关闭连接和Statement的操作
* @return ps
* @throws Exception
*/
public void closeResoource(Connection conn, Statement ps) {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
3：如何使用PreparedStatement实现对表数据进行修改操作
//修改customers表的一条记录
@Test
public void testUpdate(){
//1.获取数据库的连接
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
//2.预编译sql语句，返回PreparedStetement的实例
String sql = "update customers set name = ? where id = ?";
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setObject(1, "莫扎特");
ps.setObject(2,18);
//4.执行
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.资源的关闭
JDBCUtils.closeResoource(conn, ps);
}
}4：如何使用PreparedStatement实现对表数据进行通用操作(增删改)
由上述可以发现我们的代码实际上步骤基本一致，唯有sql语句和填充占位符的地方有所不同，所以我们在这里可以用可变个数的形参列表的个数来判断有多少个占位符需要处理，这样就可以得到一个通用的增删改操作了
//通用的增删改的操作
public void update(String sql,Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句，返回PreparedStetement的实例
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
//4.执行
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.关闭资源
JDBCUtils.closeResoource(conn, ps);
}
} @Test
public void testCommonUpdate() {
// String sql = "delete from customers where id = ?";
// update(sql,3);
String sql = "update `order` set order_name = ? where order_id = ?";
update(sql,"DD","2");
}5：使用PreparedStatement实现对表数据的查询操作
提前的操作
package com.tian.bean;
import java.sql.Date;
/*
* ORM编程思想（object relational mapping）
* 一个数据表对应一个java类
* 表中的一条记录对应java类的一个对象
* 表中的一个字段对于java类的一个属性
*
* */
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
super();
}
public Customer(int id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
}
}
package com.tian.preparedstatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.tian.bean.Customer;
import com.tian.util.JDBCUtils;
public class CustomerForQuery {
@Test
public void testQuery1(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
//执行并返回结果集
resultSet = ps.executeQuery();
//处理结果集
if(resultSet.next()) {//判断结果集的下一条是否有数据，如果有数据返回true，并指针下移；如果返回false，指针不会下移，直接结束
//获取当前这条数据的各个字段值
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
//方式一：
// System.out.println("id = " + id + ",name = " + name + "email = " + email + ",birth = " + birth);
//方式二：
// Object[] data = new Object[] {id,name,email,birth};
//方式三：
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
}
} catch(Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//关闭资源
JDBCUtils.closeResource(conn, ps,resultSet );
}
}
}
6：使用PreparedStatement实现对一张表写一个通用的查询操作
对Customers表的一个通用的查询操作
public Customer queryForCustomers(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
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
Customer cust = new Customer();
//处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
String columnName = rsmd.getColumnName(i + 1);
// 给cust对象指定的columnName属性，赋值为columValue：通过反射
Field field = Customer.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(cust, columValue);
}
return cust;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
@Test
public void testQueryForCustomers() {
String sql = "select id,name,birth,email from customers where id = ?";
Customer customer = queryForCustomers(sql, 13);
System.out.println(customer);
sql = "select name,email from customers where name = ?";
Customer customer1 = queryForCustomers(sql, "周杰伦");
System.out.println(customer1);
}对Order表的一个通用的查询操作
package com.tian.bean;
import java.sql.Date;
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order() {
super();
}
public Order(int orderId, String orderName, Date orderDate) {
super();
this.orderId = orderId;
this.orderName = orderName;
this.orderDate = orderDate;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
@Override
public String toString() {
return "Order [orderId=" + orderId + ", orderName=" + orderName + ", orderDate=" + orderDate + "]";
}
}
@Test
public void testQuery1() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select order_id,order_name,order_date from `order` where order_id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
rs = ps.executeQuery();
if (rs.next()) {
int id = (int) rs.getObject(1);
String name = (String) rs.getObject(2);
Date date = (Date) rs.getObject(3);
Order order = new Order(id,name,date);
System.out.println(order);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
}/**
* 通用的针对于order表的查询操作
*
* @return
* @throws Exception
*/
public Order orderForQuery(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 rsmd = (ResultSetMetaData) rs.getMetaData();
// 获取列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
Order order = new Order();
for (int i = 0; i < columnCount; i++) {
// 获取每个列的列值：通过ResultSet
Object columnValue = rs.getObject(i + 1);
// 通过ResultSetMetaData
// 获取列的列名：getColumnName() --不推荐使用
// 获取列的别名：getColumnLabel()
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
// 通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
Field field = Order.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(order, columnValue);
}
return order;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
/*
* 针对于表的字段名与类的属性名不相同的情况： 1. 必须声明sql时，使用类的属性名来命名字段的别名 2.
* 使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName() 获取列的别名。
* 说明：如果sql中没有给字段起别名，getColumnLabel()获取的就是列名
*/
@Test
public void testOrderForQuery() {
String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
Order order = orderForQuery(sql, 1);
System.out.println(order);
}7：使用PreparedStatement实现对一张表写一个通用的查询操作
/**
* 通用的针对于order表的查询操作
*
* @return
* @throws Exception
*/
public Order orderForQuery(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 rsmd = (ResultSetMetaData) rs.getMetaData();
// 获取列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
Order order = new Order();
for (int i = 0; i < columnCount; i++) {
// 获取每个列的列值：通过ResultSet
Object columnValue = rs.getObject(i + 1);
// 通过ResultSetMetaData
// 获取列的列名：getColumnName() --不推荐使用
// 获取列的别名：getColumnLabel()
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
// 通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
Field field = Order.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(order, columnValue);
}
return order;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}/*
* 针对于表的字段名与类的属性名不相同的情况： 1. 必须声明sql时，使用类的属性名来命名字段的别名 2.
* 使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName() 获取列的别名。
* 说明：如果sql中没有给字段起别名，getColumnLabel()获取的就是列名
*/
@Test
public void testOrderForQuery() {
String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
Order order = orderForQuery(sql, 1);
System.out.println(order);
}
8：使用PreparedStatement实现对不同表的通用查询操作
/**
* 针对不同表的通用的查询操作，返回表中的一条记录
* @param <T>
* @param clazz
* @param sql
* @param args
* @return
*/
public <T> T getInstance(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
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
T t = clazz.getDeclaredConstructor().newInstance();
//处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
// 给t对象指定的columnName属性，赋值为columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
} @Test
public void testGetInstance() {
String sql = "select id,name,email from customers where id = ?";
Customer customer = getInstance(Customer.class, sql, 12);
System.out.println(customer);
String sql1 = "select order_id orderId,order_name orderName from `order` where order_id = ?";
Order order = getInstance(Order.class, sql1, 1);
System.out.println(order);
}实现需要查询多个字段的代码
public <T> List<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
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
//创建集合对象
ArrayList<T> list = new ArrayList<T>();
while (rs.next()) {
T t = clazz.getDeclaredConstructor().newInstance();
//处理结果集一行数据中的每一个列:给t对象指定的属性赋值
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
// 给t对象指定的columnName属性，赋值为columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
} @Test
public void testGetForList() {
String sql = "select id,name,email from customers where id < ?";
List<Customer> list = getForList(Customer.class, sql,12);
list.forEach(System.out::println);
String sql1 = "select order_id orderId,order_name orderName from `order`";
List<Order> orderList = getForList(Order.class, sql1);
orderList.forEach(System.out::println);
}9：PreparedStatement解决SQL注入问题
除了解决Statement的拼串，sql注入问题之外 ,PreparedStatement还有哪些好处呢？
1.PreparedStatement操作Blob的数据，而Statement做不到。
2.PreparedStatement可以实现更高效的批量操作。
package com.tian.connection;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;
import org.junit.Test;
import com.tian.util.JDBCUtils;
/**
* 演示使用PreparedStatement替换Statement，解决SQL注入问题
* @author Administrator
*
*/
public class PreparedStatementTest {
@Test
public void testLogin() {
Scanner scan = new Scanner(System.in);
System.out.print("用户名：");
String user = scan.nextLine();
System.out.print("密 码：");
String password = scan.nextLine();
// SELECT user,password FROM user_table WHERE user = '1' or ' AND password = '='1' or '1' = '1';
String sql = "SELECT user,password FROM user_table WHERE user = ? and password = ?";
User returnUser = getInstance(User.class,sql,user,password);
if (user != null) {
System.out.println("登陆成功!");
} else {
System.out.println("用户名或密码错误！");
}
}
/**
* 针对不同表的通用的查询操作，返回表中的一条记录
* @param <T>
* @param clazz
* @param sql
* @param args
* @return
*/
public <T> T getInstance(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
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
T t = clazz.getDeclaredConstructor().newInstance();
//处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
// 给t对象指定的columnName属性，赋值为columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
}
10：练习
练习1：向costomers表中插入一条数据
// 通用的增删改的操作
public int update(String sql, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
try {
// 1.获取数据库的连接
conn = JDBCUtils.getConnection();
// 2.预编译sql语句，返回PreparedStetement的实例
ps = conn.prepareStatement(sql);
// 3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 4.执行
/*
*
* ps.execute():
* 如果执行的是查询操作，有返回结果，则此方法返回true
* 如果执行的是增删改操作，没有返回结果，则此方法返回false
* */
//方式一：
// return ps.execute();
//方式二：
return ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 5.关闭资源
JDBCUtils.closeResource(conn, ps);
}
return 0;
} @Test
public void testInsert() {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名：");
String name = scanner.next();
System.out.print("请输入邮箱：");
String email = scanner.next();
System.out.print("请输入生日：");
String birthday = scanner.next();//'1992-09-08'
String sql = "insert into customers(name,email,birth)values(?,?,?)";
int insertCount = update(sql,name,email,birthday);
if(insertCount > 0) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
}
11：使用prepareStatement操作Blob类型的数据
/**
* 测试使用prepareStatement操作Blob类型的数据
*
* @author Administrator
*
*/
public class BlobTest {
// 向数据表customers中插入Blob类型的字段
@Test
public void testInsert() 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, "he@qq.com");
ps.setObject(3, "1999-6-23");
FileInputStream is = new FileInputStream(new File("2.png"));
ps.setBlob(4, is);
ps.execute();
JDBCUtils.closeResource(conn, ps);
}
// 查询数据表customers中Blob类型的字段
@Test
public void testQuery() {
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, 22);
rs = ps.executeQuery();
if (rs.next()) {
// 方式一
// int id = rs.getInt(1);
// String name = rs.getString(2);
// String email = rs.getString(3);
// Date birth = rs.getDate(4);
// 方式二
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customer cust = new Customer(id, name, email, birth);
System.out.println(cust);
// 将Blob类型的字段下载下来，以文件的方式保存在本地
Blob photo = rs.getBlob("photo");
is = photo.getBinaryStream();
fos = new FileOutputStream("2.png");
byte[] buffer = new byte[1024];
int len;
while ((len = is.read(buffer)) != -1) {
fos.write(buffer, 0, len);
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
is.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
JDBCUtils.closeResource(conn, ps, rs);
}
}12： 使用PreparedStatement实现批量数据的操作
package com.tian.blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import com.tian.util.JDBCUtils;
/*
* 使用PreparedStatement实现批量数据的操作
*
* update，delete本身就具有批量操作的效果
* 此时的批量操作，主要指的是批量插入，使用PreparedStatement如何实现更高效的批量插入？
*
* 题目：向goods表中插入20000条数据
* CREATE TABLE goods (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(25)
);
* 方式一：使用Statement
* Connection conn = JDBCUtils.getConnection();
* Statement st = conn.createStatement();
* for(int i = 1;i < 20000;i++) {
* String sql = "insert into goods(name)values('name_" + i + "')"
* st.execute(sql);
* }
*
*/
public class InsertTest {
// 批量插入的方式二：使用PreparedStatement
@Test
public void testInsert1() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 20000; i++) {
ps.setObject(1, "name_" + i);
ps.execute();
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));// 20000：36888
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
/*
* 批量插入的方式三： 1.addBatch()，executeBatch()，clearBatch()
* 2.mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持 ?rewriteBatchedStatements=true
* 写在配置文件的url后面
*
*/
@Test
public void testInsert2() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 1000000; i++) {
ps.setObject(1, "name_" + i);
// 1."攒"sql
ps.addBatch();
if (i % 500 == 0) {
// 2.执行batch
ps.executeBatch();
// 3.清空batch
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));// 20000：36888 --> 1402；1000000：17672
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
// 批量插入的方式四：设置连接不允许自动提交数据
@Test
public void testInsert3() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
// 设置不允许自动提交数据
conn.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 1000000; i++) {
ps.setObject(1, "name_" + i);
// 1."攒"sql
ps.addBatch();
if (i % 500 == 0) {
// 2.执行batch
ps.executeBatch();
// 3.清空batch
ps.clearBatch();
}
}
// 提交数据
conn.commit();
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));// 第一次优化20000：36888 --> 1402；第二次优化1000000：17672 --> 14949
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
}
588
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
