JDBC学习笔记——1
一、获取连接
工程文件夹下
package jdbc_1.connection;
import java.io.*;
import java.sql.*;
import java.util.Properties;
import org.junit.Test;
public class ConnectionTest {
//连接方式1
@Test
public void testConnection1() throws Exception {
//com.mysql.cj.jdbc.Driver类实现Driver接口
Driver driver = new com.mysql.cj.jdbc.Driver();
/*
* URL：
* http://localhost:8080/gmail/123.jpg
* jdbc:mysql：协议
* localhost：: IP地址
* 3306：MySQL默认端口
* test：数据库名
* ?serverTimezone=UTC：使用Mysql Connector/J 6.x以上的版本，会报时区错误，要添加改语句
*/
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
//将用户名和密码封装在Properties中
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "macheng_0213");
//使用Driver对象调用connect()方法，获取Connection对象
Connection conn = driver.connect(url, info);
}
//方式2：目的是在程序中不出现第三方API，使得程序具有较好的可移植性
@Test
public void testConnection2() throws Exception{
//1.获取Driver实现类对象，通过反射实现
Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver = (Driver)clazz.getConstructor().newInstance();
//2.提供要连接的数据库，即URL
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
//3.提供所连数据库的用户名和密码
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "macheng_0213");
//4.获取连接
Connection conn = driver.connect(url, info);
}
//方式3：使用DriverManager替换Driver
@Test
public void testConnection3() throws Exception{
//(1)提供3个获取链接的基本信息
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
String user = "root";
String password = "macheng_0213";
//(2)获取Driver实现类对象，通过反射实现
Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver = (Driver)clazz.getConstructor().newInstance();
//1.注册驱动
DriverManager.registerDriver(driver);
//2.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
}
//方式4：可以通过反射隐式加载驱动，而不用显示注册驱动了
@Test
public void testConnection4() throws Exception{
//1.提供3个获取链接的基本信息
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
String user = "root";
String password = "macheng_0213";
//2.加载Driver
//com.mysql.cj.jdbc.Driver在加载时会自动注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// Driver driver = (Driver)clazz.getConstructor().newInstance();
// //1.注册驱动
// DriverManager.registerDriver(driver);
/*
* 为什么可以省略注册驱动的步骤？
* 因为在MySQL的Driver实现类中声明了如下操作，该静态代码块会随着反射
* Class.forName("com.mysql.cj.jdbc.Driver");的加载而加载，因此可以省略
* static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
*/
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
}
//方式5：最终版 将数据库连接需要的4个基本信息声明在配置文件中，通过读取文件的方式获取连接
/*
* 好处：
* 1.实现了代码与数据的分离（解耦）
* 2.如果需要修改配置文件信息，可以避免程序重新打包，只需更换配置文件即可
*/
@Test
public void testConnection_final() throws Exception{
//1.声明配置文件，要声明在src文件下
//2.读取配置文件中的4个基本信息
//通过io流读取
// FileInputStream in = new FileInputStream(
// new File("D:\\Java\\eclipse\\workplace\\myJDBC\\src\\jdbc.properties"));
//通过类加载器读取
InputStream in = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
//创建properties对象读取数据
Properties prop = new Properties();
prop.load(in);
String user = prop.getProperty("user");
String password = prop.getProperty("password");
String url = prop.getProperty("url");
String driverClass = prop.getProperty("driverClass");
//3.加载Driver
Class.forName(driverClass);
//4.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
}
}
二、PreparedStatement
增删改
package jdbc_1.preparedStatement;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import org.junit.Test;
/**
* 使用PreparedStatement替换Statement，实现对数据表的增删改查操作
*
* 增删改查（CRUD）： 增删改不需要返回结果，查需要返回结果集
*
* @author MCC
*
*/
public class PreparedStatementInsertTest {
// 向customers表中添加记录
@Test
public void insert() {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.4个基本信息
InputStream in = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties prop = new Properties();
prop.load(in);
String user = prop.getProperty("user");
String password = prop.getProperty("password");
String url = prop.getProperty("url");
String driverClass = prop.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
conn = DriverManager.getConnection(url, user, password);
//4.预编译sql语句，获取PreparedStatement对象
/*
* ? 占位符
*/
String sql = "insert into customers (name, email, birth) values (?, ?, ?)";
ps = conn.prepareStatement(sql);
//5.填充占位符，与数据库相关的索引从1开始
ps.setString(1, "哪吒");
ps.setString(2, "nezha@gmail.com");
//保证生日的格式正确
//(1)定义日期格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//(2)传入日期
Date date = sdf.parse("1999-1-1");
//(3)传入数据
ps.setDate(3, new java.sql.Date(date.getTime()));
//6.执行sql语句
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//7.关闭资源
try {
if(ps != null) {
ps.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package jdbc_1.preparedStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import jdbc_1.myutil.*;
public class PreparedStatementUpdateTest {
@Test
//修改customer表中的记录
public void update() {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句，获取PreparedStatement对象
String sql = "update customers set name = ? where id = ?";
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setObject(1, "莫扎特");
ps.setInt(2, 18);
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
JDBCUtils.closeResource(ps, conn);
}
}
}
ResultSet接收查询结果
Bean类
Class Customer
package jdbc_1.bean;
import java.sql.Date;
/**
* 用来存储从customer表中查询到的数据
*
* ORM编程思想：Object Relational mapping对象关系映射
* 一个数据表对应一个java类，表中的一个字段对应类中的一个属性，每一条数据对应类的一个对象
*
* @author MCC
*
*/
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 + "]";
}
}
Class Order
package jdbc_1.bean;
import java.sql.Date;
/**
* 用来存储从order表中查询到的数据
* @author MCC
*
*/
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 + "]";
}
}
customer表
package jdbc_1.preparedStatement;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.junit.Test;
import jdbc_1.bean.Customer;
import jdbc_1.myutil.JDBCUtils;
/**
* 针对于customers表的查询操作
* @author MCC
*
*/
public class SelectCustomerTest {
/*
* 演示查询customer表
*/
@Test
public void testSelectCustomers() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JDBCUtils.getConnection();
//2.预编译，返回PreparedStatement实例
String sql = "select id, name, email, birth from customers where id = ?";
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setInt(1, 1);
//4.执行语句，返回结果集
rs = ps.executeQuery();
//5.处理结果集
/*
* next()：
* 判断结果集中的下一条是否有数据，如果有返回true，并将指针下移一位（初始时指针位于数据前一位）
*/
if(rs.next()) {
//获取当前这条数据的各个字段的值
int id = rs.getInt(1);
String name = rs.getString(2);
String email = rs.getString(3);
Date birth = rs.getDate(4);
//显示信息
//方式1
// System.out.println(
// "id = " + id + " name = " + name + " email = " + email + " birth " + birth);
//方式2
// Object[] data = new Object[]{id, name, email, birth};
//方式3：将信息存储到Customer对象中
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.关闭资源
JDBCUtils.closeResource(rs, ps, conn);
}
}
/*
* 针对于customers表的通用操作
*/
public Customer testGengralCustomers(String sql, Object ...obj) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句，获取PreparedStatement对象
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0; i<obj.length; i++) {
ps.setObject(i+1, obj[i]);
}
//4.执行，获取ResultSet实例
rs = ps.executeQuery();
//5.判断是否有数据，并存入Customer对象中
/*
* 要知道查出的数据有几个字段，才能定义变量接收数据，
* 获取字段数的方法封装在数据集元数据ResultSetMetaData中,
* 所以先使用ResultSet对象调用getMetaData()方法获取
*/
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取结果集的字段（列）数
int count = rsmd.getColumnCount();
if(rs.next()) {
Customer customer = new Customer();
//处理结果集中每一行数据中的每一列
for(int i=0; i<count; i++) {
//获取结果集每列的值
Object columnValue = rs.getObject(i+1);
//获取结果集每列的列名
String columnName = rsmd.getColumnName(i+1);
//给customer对象的columnName属性赋值为columnValue
//通过反射完成
//获取Customer类的名称为columnName的属性
Field field = Customer.class.getDeclaredField(columnName);
//将该属性设置为可访问
field.setAccessible(true);
//将当前customer对象的columnName属性赋值为columnValue
field.set(customer, columnValue);
}
return customer;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(rs, ps, conn);
}
return null;
}
@Test
public void testGenCustomers() {
String sql1 = "select id, name, email, birth from customers where id = ?";
String sql2 = "select name, email from customers where name = ?";
Customer customer1 = testGengralCustomers(sql1, 13);
Customer customer2 = testGengralCustomers(sql2, "周杰伦");
System.out.println(customer1);
System.out.println(customer2);
}
}
Order表
package jdbc_1.preparedStatement;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.junit.Test;
import jdbc_1.bean.Order;
import jdbc_1.myutil.JDBCUtils;
/**
* 演示对Order表的通用查询操作
* @author MCC
*
*/
public class SelectOrderTest {
@Test
public void testGenOrder() {
/*
* 当类的属性名与表的字段名不一致时，需要给表的各个字段起别名，别名为对应的属性名，
* rsmd.getColumnName()方法获取的是表的字段名（即原始名），不是别名，
* 因此此时需要更改该方法，使用rsmd.getColumnLabel()方法则可以获取字段的别名
*/
String sql =
"select "
+ "order_id orderId, "
+ "order_name orderName, "
+ "order_date orderDate "
+ "from `order` where order_id = ?";
Order order = testGengralOrder(sql, 4);
System.out.println(order);
}
public Order testGengralOrder(String sql, Object ...obj) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.建立连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0; i<obj.length; i++) {
ps.setObject(i+1, obj[i]);
}
//4.执行sql，获取结果集对象以及结果集元数据对象
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
if(rs.next()) {//如果有数据，则取出数据
Order order = new Order();
//根据数据的字段数，判断取出次数
int columnCount = rsmd.getColumnCount();
for(int i=0; i<columnCount; i++) {
//取出数值以及列名
/*
* 获取列的列名：rsmd.getColumnName()——不推荐使用
* 获取列的别名：rsmd.getColumnLabel()——没有起别名时，默认返回列的列名
*/
Object columnValue = rs.getObject(i+1);
String columnLabel = rsmd.getColumnLabel(i+1);
//通过反射，将该值赋值给order对象的对应属性
Field field = Order.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(order, columnValue);
}
return order;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(rs, ps, conn);
}
return null;
}
}
通用的增删改查操作
增删改
package jdbc_1.preparedStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import jdbc_1.myutil.JDBCUtils;
/**
* 通用的数据库增删改操作
* @author MCC
*
*/
public class CUDGeneralTest {
@Test
public void testCUD() throws Exception {
// CUD("delete from customers where id = ?", 3);
// CUD("update `order` set order_name = ? where order_id = ?", "DD", 2);
CUD("insert into `order` (order_name, order_date) values (?, ?)", "FF", "1990-2-1");
}
public void CUD(String sql, Object ...obj) {
//sql中占位符的个数应该与可变形参的长度相同
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句，获取PreparedStatement对象
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0; i<obj.length; i++) {
ps.setObject(i+1, obj[i]);//sql从1开始，obj从0开始
}
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}
//5.关闭资源
JDBCUtils.closeResource(ps, conn);
}
}
查询
package jdbc_1.preparedStatement;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import jdbc_1.bean.Customer;
import jdbc_1.bean.Order;
import jdbc_1.myutil.JDBCUtils;
/**
* 使用PreparedStatement实现针对于不同表的通用查询操作
* @author MCC
*
*/
public class selectGeneralTest {
@Test
/*
* 测试查询单个记录
*/
public void testGenOneSelect() {
String sql1 = "select id, name, email from customers where id = ?";
Customer customer = testGeneralOneSelect(Customer.class, sql1, 12);
System.out.println(customer);
/*
* 当类的属性名与表的字段名不一致时，需要给表的各个字段起别名，别名为对应的属性名，
* rsmd.getColumnName()方法获取的是表的字段名（即原始名），不是别名，
* 因此此时需要更改该方法，使用rsmd.getColumnLabel()方法则可以获取字段的别名
*/
String sql2 =
"select "
+ "order_id orderId, "
+ "order_name orderName, "
+ "order_date orderDate "
+ "from `order` where order_id = ?";
Order order = testGeneralOneSelect(Order.class, sql2, 4);
System.out.println(order);
}
/*
* 返回表中的一条记录
*/
public <T> T testGeneralOneSelect(Class<T> clazz, String sql, Object ...obj) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.建立连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0; i<obj.length; i++) {
ps.setObject(i+1, obj[i]);
}
//4.执行sql，获取结果集对象以及结果集元数据对象
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
if(rs.next()) {//如果有数据，则取出数据
T t = clazz.getConstructor().newInstance();
//根据数据的字段数，判断取出次数
int columnCount = rsmd.getColumnCount();
for(int i=0; i<columnCount; i++) {
//取出数值以及列名
/*
* 获取列的列名：rsmd.getColumnName()——不推荐使用
* 获取列的别名：rsmd.getColumnLabel()——没有起别名时，默认返回列的列名
*/
Object columnValue = rs.getObject(i+1);
String columnLabel = rsmd.getColumnLabel(i+1);
//通过反射，将该值赋值给t对象的对应属性
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(rs, ps, conn);
}
return null;
}
@Test
/*
* 测试查询多个记录
*/
public void testGenMoreSelect() {
String sql1 = "select id, name, email from customers";
List<Customer> customerList = testGeneralMoreSelect(Customer.class, sql1);
customerList.forEach(System.out::println);
/*
* 当类的属性名与表的字段名不一致时，需要给表的各个字段起别名，别名为对应的属性名，
* rsmd.getColumnName()方法获取的是表的字段名（即原始名），不是别名，
* 因此此时需要更改该方法，使用rsmd.getColumnLabel()方法则可以获取字段的别名
*/
String sql2 =
"select "
+ "order_id orderId, "
+ "order_name orderName, "
+ "order_date orderDate "
+ "from `order` where order_id < ?";
List<Order> orderList = testGeneralMoreSelect(Order.class, sql2, 4);
orderList.forEach(System.out::println);
// for(Order order : orderList) {
// System.out.println(order);
// }
}
/*
* 返回表中的多条记录
*/
public <T> List<T> testGeneralMoreSelect(Class<T> clazz, String sql, Object ...obj) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.建立连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0; i<obj.length; i++) {
ps.setObject(i+1, obj[i]);
}
//4.执行sql，获取结果集对象以及结果集元数据对象
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
//创建集合对象
List<T> list = new ArrayList<T>();
while(rs.next()) {//如果有数据，则取出数据
T t = clazz.getConstructor().newInstance();
//根据数据的字段数，判断取出次数
int columnCount = rsmd.getColumnCount();
//给t对象指定属性赋值
for(int i=0; i<columnCount; i++) {
//取出数值以及列名
/*
* 获取列的列名：rsmd.getColumnName()——不推荐使用
* 获取列的别名：rsmd.getColumnLabel()——没有起别名时，默认返回列的列名
*/
Object columnValue = rs.getObject(i+1);
String columnLabel = rsmd.getColumnLabel(i+1);
//通过反射，将该值赋值给t对象的对应属性
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
//将t对象添加到集合中
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(rs, ps, conn);
}
return null;
}
}
自定义的工具类
自定义的获取连接和关闭资源的工具类
package jdbc_1.myutil;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* 操作数据库的工具类
* @author MCC
*
*/
public class JDBCUtils {
/**
* 获取数据库连接
* @return Connection conn
* @throws Exception
*/
public static Connection getConnection() throws Exception{
InputStream in = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties prop = new Properties();
prop.load(in);
String user = prop.getProperty("user");
String password = prop.getProperty("password");
String url = prop.getProperty("url");
String driverClass = prop.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* 关闭statement和Connection
* @param conn
* @param s
*/
public static void closeResource(Statement s, Connection conn) {
try {
if(s != null) {
s.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//重载
public static void closeResource(ResultSet rs, Statement s, Connection conn) {
try {
if(rs != null) {
rs.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if(s != null) {
s.close();
}
} catch(SQLException e2) {
e2.printStackTrace();
}
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
三、Blob
package jdbc_1.blob;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
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 jdbc_1.bean.Customer;
import jdbc_1.myutil.JDBCUtils;
/**
* 使用PreparedStatement测试Blob类型的数据
* @author MCC
*
*/
public class BlobTest {
/*
* 1. 向customers表中插入Blob类型数据
*/
@Test
public void testInsetrBlob() 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, "mingren@qq.com");
ps.setObject(3, "1996-2-19");
FileInputStream fis = new FileInputStream(new File("C:\\Users\\MCC\\Desktop\\20200913142956.jpg"));
ps.setBlob(4, fis);
ps.execute();
JDBCUtils.closeResource(ps, conn);
}
/*
*2. 修改customers表中的Blob数据
*/
@Test
public void testUpdateBlob() throws Exception {
Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
FileInputStream fis = new FileInputStream(new File("C:\\Users\\MCC\\Desktop\\20200921230415.jpg"));
ps.setBlob(1, fis);
ps.setObject(2, 22);
ps.execute();
JDBCUtils.closeResource(ps, conn);
}
/*
* 3. 查询customer表中的Blob数据
*/
@Test
public void testSelectBlob() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
InputStream bs = 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()) {
//方式1：getXxx(int columnIndex)
// int id = rs.getInt(1);
// String name = rs.getString(2);
// String email = rs.getString(3);
// Date birth = rs.getDate(4);
//方式2：getXxx(String columnLabel)
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");
bs = photo.getBinaryStream();
fos = new FileOutputStream(
new File("C:\\Users\\MCC\\Desktop\\photo.jpg"));
//读取数据
byte[] by = new byte[1024];
int len = 0;
while((len=bs.read(by))!=-1) {
//写出
fos.write(by, 0, len);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(bs!=null) {
try {
bs.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(fos!=null) {
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
JDBCUtils.closeResource(rs, ps, conn);
}
}
}
四、批处理
package jdbc_1.batch;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import jdbc_1.myutil.JDBCUtils;
/**
* 利用PreparedStatement实现批量数据操作
*
* select、update、delete本身就具有批量操作效果
*
* insert需要考虑批量插入问题
*
* 问题：向goods表中插入20000条数据
*
* 方式1：使用Statement
*
* @author MCC
*
*/
public class BatchTest {
//方式2：
@Test
public void testBatchInsert_2() {
Connection conn = null;
PreparedStatement ps = null;
try {
long startTime = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods (name) values (?)";
ps = conn.prepareStatement(sql);
//填充占位符
for(int i=0; i<20000; i++) {
ps.setObject(1, "name_"+(i+1));
ps.execute();
}
long endTime = System.currentTimeMillis();
long time = endTime-startTime;
System.out.println(time);//60384ms
} catch (Exception e) {
e.printStackTrace();
}
JDBCUtils.closeResource(ps, conn);
}
/*
* 方式3：
* 改进1：addBatch(), executeBatch(), clearBatch()
* 改进2：MySQL默认是关闭批处理功能的，我们需要通过一个参数，让MySQL开启批处理功能。
* ?rewriteBatchedStatements=true 写在配置文件的url后面
*/
@Test
public void testBatchInsert_3() {
Connection conn = null;
PreparedStatement ps = null;
try {
long startTime = 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));
//1.“攒”sql
ps.addBatch();
if(i%500 == 0) {
//2.执行batch
ps.executeBatch();
//3.情况batch
ps.clearBatch();
}
}
long endTime = System.currentTimeMillis();
long time = endTime-startTime;
System.out.println(time);//15064ms
} catch (Exception e) {
e.printStackTrace();
}
JDBCUtils.closeResource(ps, conn);
}
/*
* 方式4：
* 在方式3的基础上，设置语句都执行完毕再提交，即1000000条数据都插入之后再提交数据库，
* 而不是执行500次就提交一次
* 使用Connection 的 setAutoCommit(false) / commit()
*/
@Test
public void testBatchInsert_final() {
Connection conn = null;
PreparedStatement ps = null;
try {
long startTime = 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));
//1.“攒”sql
ps.addBatch();
if(i%500 == 0) {
//2.执行batch
ps.executeBatch();
//3.情况batch
ps.clearBatch();
}
}
//都执行完毕，提交数据
conn.commit();
long endTime = System.currentTimeMillis();
long time = endTime-startTime;
System.out.println(time);//8286ms
} catch (Exception e) {
e.printStackTrace();
}
JDBCUtils.closeResource(ps, conn);
}
}
五、练习
练习1：
package jdbc_1.practice;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;
import org.junit.Test;
import jdbc_1.myutil.JDBCUtils;
/**
* 从控制台向customers表中插入一条数据，表结构为：
* id, name, email, birth, photo
*
* @author MCC
*
*/
public class Practice1Test {
@Test
//插入数据
public void testPractice1() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名：");
String name = scanner.next();
System.out.println("请输入邮箱：");
String email = scanner.next();
System.out.println("请输入生日：");
String birth = scanner.next();
String sql = "insert into customers (name, email, birth) values (?, ?, ?)";
int res = CUD(sql, name, email, birth);
if(res==0) {
System.out.println("操作失败");
} else {
System.out.println("操作成功");
}
scanner.close();
}
//通用的增删改操作
public int CUD(String sql, Object ...obj) {
//sql中占位符的个数应该与可变形参的长度相同
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句，获取PreparedStatement对象
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0; i<obj.length; i++) {
ps.setObject(i+1, obj[i]);//sql从1开始，obj从0开始
}
//4.执行
/* 方式1：
* boolean execute():
* 如果执行的是查询操作，有结果集，则返回true
* 如果执行的是增删改操作，没有结果集，则返回false
*/
// ps.execute();
/* 方式2：
* int executeUpdate():
* 返回执行增删改操作影响的行数，返回0代表影响行数为0，即增删改失败
*/
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
//5.关闭资源
JDBCUtils.closeResource(ps, conn);
return 0;
}
}
练习2：
Class ExamStudent
package jdbc_1.bean;
/**
* 用来存储从examstudent表中查询到的数据
* @author MCC
*
*/
public class ExamStudent {
private int flowID;//流水号
private int type;//考试类型
private String IDCard;//身份证号
private String examCard;//准考证号
private String name;//姓名
private String location;//所在城市
private int grade;//成绩
public ExamStudent() {
super();
}
public ExamStudent(int flowID, int type, String iDCard, String examCard, String name, String location, int grade) {
super();
this.flowID = flowID;
this.type = type;
IDCard = iDCard;
this.examCard = examCard;
this.name = name;
this.location = location;
this.grade = grade;
}
public int getflowID() {
return flowID;
}
public void setflowID(int flowID) {
this.flowID = flowID;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public String getIDCard() {
return IDCard;
}
public void setIDCard(String iDCard) {
IDCard = iDCard;
}
public String getExamCard() {
return examCard;
}
public void setExamCard(String examCard) {
this.examCard = examCard;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
@Override
public String toString() {
return "ExamStudent [flowID=" + flowID + ", type=" + type + ", IDCard=" + IDCard + ", examCard=" + examCard
+ ", name=" + name + ", location=" + location + ", grade=" + grade + "]";
}
//显示学生信息
public void showInfo() {
System.out.println("=======查询结果========");
System.out.println("流水号：" + this.flowID + "\n" +
"四级/六级：" + this.type + "\n" +
"身份证号：" + this.IDCard + "\n" +
"准考证号：" + this.examCard + "\n" +
"姓名：" + this.name + "\n" +
"成绩：" + this.grade);
}
}
package jdbc_1.practice;
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 jdbc_1.bean.ExamStudent;
import jdbc_1.myutil.JDBCUtils;
/**
* 课后练习2
* @author MCC
*
*/
public class Practice2Test {
@Test
public void testPractice2() {
System.out.println("请输入考生的详细信息");
Scanner scanner = new Scanner(System.in);
System.out.print("四级/六级（4/6）：");
int type = scanner.nextInt();
System.out.print("身份证号：");
String idCard = scanner.next();
System.out.print("准考证号：");
String examCard = scanner.next();
System.out.print("姓名：");
String studentName = scanner.next();
System.out.print("所在城市：");
String location = scanner.next();
System.out.print("成绩：");
int grade = scanner.nextInt();
String sql = "insert into examstudent "
+ "(Type, IDCard, ExamCard, StudentName, Location, Grade) "
+ "values (?, ?, ?, ?, ?, ?)";
int res = CUD(sql, type, idCard, examCard, studentName, location, grade);
if(res==0) {
System.out.println("信息录入失败！");
} else {
System.out.println("信息录入成功！");
}
scanner.close();
}
/*
* 问题1：向examstudent表中添加数据
*/
public int CUD(String sql, Object ...obj) {
//sql中占位符的个数应该与可变形参的长度相同
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句，获取PreparedStatement对象
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0; i<obj.length; i++) {
ps.setObject(i+1, obj[i]);//sql从1开始，obj从0开始
}
//4.执行
/* 方式1：
* boolean execute():
* 如果执行的是查询操作，有结果集，则返回true
* 如果执行的是增删改操作，没有结果集，则返回false
*/
// ps.execute();
/* 方式2：
* int executeUpdate():
* 返回执行增删改操作影响的行数，返回0代表影响行数为0，即增删改失败
*/
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
//5.关闭资源
JDBCUtils.closeResource(ps, conn);
return 0;
}
@Test
public void testPractice2_2() {
System.out.println("请选择您要输入的类型：\na:准考证号\nb:身份证号");
Scanner scanner = new Scanner(System.in);
String selection = scanner.next();
//避免空指针异常
if("a".equals(selection)) {
System.out.println("请输入准考证号：");
String examCard = scanner.next();
String sql = "select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name,Location location,Grade grade from examstudent where examCard = ?";
ExamStudent student = testGeneralOneSelect(ExamStudent.class, sql, examCard);
if(student != null) {
student.showInfo();
}else {
System.out.println("查无此人，请重新进入程序！");
}
} else if("b".equals(selection)) {
System.out.println("请输入身份证号：");
String IDCard = scanner.next();
String sql = "select FlowID flowID, Type type, IDCard, ExamCard examCard, StudentName name, Location location, Grade grade from examstudent where IDCard = ?";
ExamStudent student = testGeneralOneSelect(ExamStudent.class, sql, IDCard);
if(student != null) {
student.showInfo();
}else {
System.out.println("查无此人，请重新进入程序！");
}
} else {
System.out.println("您的输入有误，请重新进入程序！");
}
scanner.close();
}
/*
* 问题2：根据身份证号或准考证号查询学生的成绩信息
*/
public <T> T testGeneralOneSelect(Class<T> clazz, String sql, Object ...obj) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.建立连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0; i<obj.length; i++) {
ps.setObject(i+1, obj[i]);
}
//4.执行sql，获取结果集对象以及结果集元数据对象
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
if(rs.next()) {//如果有数据，则取出数据
T t = clazz.getConstructor().newInstance();
//根据数据的字段数，判断取出次数
int columnCount = rsmd.getColumnCount();
for(int i=0; i<columnCount; i++) {
//取出数值以及列名
/*
* 获取列的列名：rsmd.getColumnName()——不推荐使用
* 获取列的别名：rsmd.getColumnLabel()——没有起别名时，默认返回列的列名
*/
Object columnValue = rs.getObject(i+1);
String columnLabel = rsmd.getColumnLabel(i+1);
//通过反射，将该值赋值给t对象的对应属性
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(rs, ps, conn);
}
return null;
}
/*
* 问题3：删除指定考生的信息
*/
@Test
public void testPractice2_3() {
System.out.println("请输入学生的准考证号：");
Scanner scanner = new Scanner(System.in);
String examCard = scanner.next();
//先查询该考号学生是否存在
String sql1 = "select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name,Location location,Grade grade from examstudent where examCard = ?";
ExamStudent student = testGeneralOneSelect(ExamStudent.class, sql1, examCard);
if(student == null) {
System.out.println("查无此人，请重新输入！");
}else {
String sql2 = "delete from examstudent where ExamCard = ?";
int res = CUD(sql2, examCard);
if(res>0) {
System.out.println("删除成功！");
}else {
System.out.println("删除失败！");
}
}
scanner.close();
}
//改进方法
@Test
public void testPractice2_4() {
System.out.println("请输入学生的准考证号：");
Scanner scanner = new Scanner(System.in);
String examCard = scanner.next();
/*
* 不进行查询，直接删除指定准考证号的考生信息，
* 如果该生存在，则删除成功，如果不存在也不会报错，受影响行数为0
*/
String sql = "delete from examstudent where ExamCard = ?";
int res = CUD(sql, examCard);
if(res>0) {
System.out.println("删除成功！");
}else {
System.out.println("查无此人，请重新输入！");
}
scanner.close();
}
}
扫一扫
822
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
