1.增删改通用函数
/**
* 通用的增删改操作
*
* @param sql
* @param args
*/
public void update(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1.获取连接
connection = DBUtil.getConnection();
//2.预编译sql语句
preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//4.执行sql语句
preparedStatement.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5.关闭资源
DBUtil.closeResource(connection, preparedStatement);
}
}
2.针对于某个表的查找通用方法
import org.junit.Test;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.sql.Connection;
/**
* @author ym
* @create 2022-01-30 23:00
* @description 测试查询
*/
public class SelectTest {
@Test
public void testQueryForOrder() {
//使用了别名的方法来查找,此处注意,需要使对应的order bean类的属性名与别名一致
String querySql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
Order select = select(querySql, 1);
System.out.println(select.toString());
}
/**
* Order表的通用查询方法
*
* @param sql
* @param args
* @return
*/
public Order select(String sql, Object... args) {
//1.获取连接
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
//2.预编译sql语句
preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//4.执行查询,获得结果集以及结果集的元数据
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
//5.根据列名获取结果
if (resultSet.next()) {
Order order = new Order();
for (int i = 0; i < columnCount; i++) {
//1.获取列名,此时使用别名
String columnLabel = metaData.getColumnLabel(i + 1);
//2.获取数据
Object object = resultSet.getObject(i + 1);
//3.根据反射获取属性
Field declaredField = Order.class.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
//4.属性赋值
declaredField.set(order, object);
}
return order;
}
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} finally {
DBUtil.closeResource(connection, preparedStatement, resultSet);
}
return null;
}
}
3.针对各种表的查找通用方法
import org.junit.Test;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author ym
* @create 2022-01-30 23:00
* @description 测试查询
*/
public class SelectTest {
@Test
public void testQueryForOrder() {
//使用了别名的方法来查找,此处注意,需要使对应的order bean类的属性名与别名一致
String querySql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id < ?";
// Order select = select(querySql, 1);
// System.out.println(select.toString());
// Order order = selectAllTable(Order.class, querySql, 4);
// System.out.println(order.toString());
List<Order> orders = selectAllTableList(Order.class, querySql, 4);
orders.forEach(System.out::println);
}
/**
* Order表的通用查询方法
*
* @param sql
* @param args
* @return
*/
public Order select(String sql, Object... args) {
//1.获取连接
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = DBUtil.getConnection();
//2.预编译sql语句
preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//4.执行查询,获得结果集以及结果集的元数据
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
//5.根据列名获取结果
if (resultSet.next()) {
Order order = new Order();
for (int i = 0; i < columnCount; i++) {
//1.获取列名,此时使用别名
String columnLabel = metaData.getColumnLabel(i + 1);
//2.获取数据
Object object = resultSet.getObject(i + 1);
//3.根据反射获取属性
Field declaredField = Order.class.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
//4.属性赋值
declaredField.set(order, object);
}
return order;
}
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} finally {
DBUtil.closeResource(connection, preparedStatement, resultSet);
}
return null;
}
public <T> List<T> selectAllTableList(Class<T> t, String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//1.建立连接
connection = DBUtil.getConnection();
//2.预编译sql语句
preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//4.执行查询语句,返回结果集
resultSet = preparedStatement.executeQuery();
//查询结果元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//查询列数
int columnCount = metaData.getColumnCount();
List<T> ts = new ArrayList<T>();
while (resultSet.next()) {
//5.新建对象
T t1 = t.newInstance();
//使用循环给每一个对象赋予每个列的属性值
for (int i = 0; i < columnCount; i++) {
//6.获取每列数据
Object object = resultSet.getObject(i + 1);
//7.获取列名
String columnLabel = metaData.getColumnLabel(i + 1);
//8.赋值
Field declaredField = t.getDeclaredField(columnLabel);
//使用反射时,这一步千万不要忘记
declaredField.setAccessible(true);
declaredField.set(t1, object);
}
ts.add(t1);
}
return ts;
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
DBUtil.closeResource(connection, preparedStatement, resultSet);
}
return null;
}
/**
* 查询所有表的通用查询方法
*
* @param t
* @param sql
* @param args
* @param <T>
* @return
*/
public <T> T selectAllTable(Class<T> t, String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//1.建立连接
connection = DBUtil.getConnection();
//2.预编译sql语句
preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//4.执行查询语句,返回结果集
resultSet = preparedStatement.executeQuery();
//查询结果元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//查询列数
int columnCount = metaData.getColumnCount();
if (resultSet.next()) {
//5.新建对象
T t1 = t.newInstance();
//使用循环给每一个对象赋予每个列的属性值
for (int i = 0; i < columnCount; i++) {
//6.获取每列数据
Object object = resultSet.getObject(i + 1);
//7.获取列名
String columnLabel = metaData.getColumnLabel(i + 1);
//8.赋值
Field declaredField = t.getDeclaredField(columnLabel);
//使用反射时,这一步千万不要忘记
declaredField.setAccessible(true);
declaredField.set(t1, object);
}
return t1;
}
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
DBUtil.closeResource(connection, preparedStatement, resultSet);
}
return null;
}
}
4.blob类型的添加与查询
package com.ym.study;
import org.junit.Test;
import java.io.*;
import java.sql.*;
/**
* @author ym
* @create 2022-01-31 15:07
* @description 测试Blob图片的增删改查
*/
public class BlobTest {
@Test
public void testBlob() {
// String insertSql = "insert into customers(photo,name,email,birth) values(?,?,?,?)";
// String filePath = "x.jpg";
// insertBlob(insertSql,filePath,"关羽","guanyu@163.com","1998-08-15");
}
/**
* 测试插入图片类型数据的通用类
*/
@Test
public void insertBlob() throws SQLException, IOException, ClassNotFoundException {
//1.获取连接
Connection connection = DBUtil.getConnection();
//2.预编译sql
String insertSql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(insertSql);
//3.图片输入流
File blobFile = new File("x.jpg");
FileInputStream fileInputStream = new FileInputStream(blobFile);
//4.填充sql
preparedStatement.setObject(1, "李四");
preparedStatement.setObject(2, "lisi@163.com");
preparedStatement.setObject(3, "1992-09-08");
preparedStatement.setBlob(4, fileInputStream);
//5.执行
preparedStatement.execute();
DBUtil.closeResource(connection, preparedStatement);
}
/**
* blob类型的查询
* @throws SQLException
* @throws IOException
* @throws ClassNotFoundException
*/
@Test
public void queryBlob() throws SQLException, IOException, ClassNotFoundException {
Connection connection = DBUtil.getConnection();
String querySql = "select photo from customers where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(querySql);
preparedStatement.setObject(1, 22);
ResultSet resultSet = preparedStatement.executeQuery();
FileOutputStream fileOutputStream = null;
if (resultSet.next()) {
Blob blob = resultSet.getBlob(1);
InputStream binaryStream = blob.getBinaryStream();
fileOutputStream = new FileOutputStream("z.jpg");
byte[] bytes = new byte[1024];
int len = 0;
while ((len = binaryStream.read(bytes)) != -1) {
fileOutputStream.write(bytes, 0, len);
}
}
fileOutputStream.close();
// binaryStream.close();
DBUtil.closeResource(connection, preparedStatement, resultSet);
}
}
5.事务例子
package com.ym.study;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* @author ym
* @create 2022-01-31 21:33
* @description 考虑到事务的增删改查
* 对事务有影响的两个需要注意的:
* 1.增删改查操作是默认自动提交的
* 2.关闭连接时也是默认自动提交的
*/
public class Transaction {
@Test
public void testTransaction() {
//测试事务的提交
Connection connection = null;
try {
connection = DBUtil.getConnection();
//从AA账户取出100元,并将这一百元给BB
String AASql = "update user_table set balance = balance-100 where user = ?";
System.out.println(connection.getAutoCommit() + "1");
//关闭自动提交
connection.setAutoCommit(false);
System.out.println(connection.getAutoCommit() + "2");
updateOfTransaction(connection, AASql, "AA");
//模拟交易出现异常
System.out.println(10 / 0);
//将从AA账户取出的100元放入BB账户
System.out.println(connection.getAutoCommit() + "3");
String BBSql = "update user_table set balance = balance+100 where user = ?";
updateOfTransaction(connection, BBSql, "BB");
System.out.println(connection.getAutoCommit() + "4");
System.out.println("交易成功");
} catch (Exception e) {
//如果跳转到此处,应该是出现异常,此时需要进行回滚
try {
System.out.println("进行回滚");
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
//关闭连接之前,先将自动提交改回true,此处是为了数据库连接池做准备
try {
connection.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
//关闭连接
DBUtil.closeResource(connection, null);
}
}
/**
* 通用的增删改操作
*
* @param sql
* @param args
*/
public void updateOfTransaction(Connection conn, String sql, Object... args) {
PreparedStatement preparedStatement = null;
try {
//此处必须要注释,这样才能使得事务内所有sql操作的连接是同一个
// //1.获取连接
// conn = DBUtil.getConnection();
//2.预编译sql语句
preparedStatement = conn.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//4.执行sql语句
preparedStatement.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5.关闭资源 此处关闭connection资源需要在需要的时候关闭
DBUtil.closeResource(null, preparedStatement);
}
}
}
6.事务的ACID属性
1.原子性(Atimicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
2.一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3.隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能相互干扰。
4.持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。
7.数据库隔离性中常出现的问题
Read_uncommited 这个隔离级别解决不了脏读
1.脏读
脏读是指T1事务在读取数据时,读取到了T2事务修改了的但是未提交的数据。
Read_commited 这个隔离级别解决了脏读
2.不可重复读
不可重复读是指T1事务在读取数据时,读取到了T2事务修改了的并且提交了的数据
Repeated_Read 这个隔离级别可以解决脏读和不可重复读问题
3.幻读
幻读是指T1事务在读取数据时,读取到了T2事务添加的几条数据
Serializable 这个隔离基本可以解决脏读、不可重复读、幻读问题,但是并行化就很差了
oracle支持 Read_commited 和 Serializable
一般项目应用中,脏读是不可接受的,其他两个是可以接受的