目录
1.技术体系
XML负责后台配置。
servlet：1、获取用户请求，2、处理用户请求 3、响应
2.jdbc的五种连接方式
（掌握第五种）
package com.oneluckyguy.connection;
import java.beans.Expression;
import java.io.InputStream;
import java.rmi.server.ExportException;
import java.sql.*;
import java.util.*;
import org.junit.Test;
import org.junit.experimental.theories.suppliers.TestedOn;
import javax.management.MalformedObjectNameException;
/**
* @author Liu Qingfeng
* @create 2020-12-12----23:52
*/
public class ConnectionTest {
//方式一
@Test
public void testConnection1() throws SQLException {
//1.获取Driver实现类对象
Driver driver = new com.mysql.jdbc.Driver(); //第三方的
//jdbc:mysql 协议
//localhost: ip地址
//3306 端口
//test 数据库名
String url = "jdbc:mysql://localhost:3306/test";
//将用户名密码封装在Properties里面
Properties info = new Properties();
info.setProperty("user","root");
info.setProperty("password","");
Connection connection = driver.connect(url,info);
System.out.println(connection);
}
//方式二：对方式一的迭代
//不出现第三方api使程序有更好的移植性
@Test
public void testConnection2() throws Exception {
//1.获取Driver实现类对象
Class driverClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) driverClass.newInstance();
//2.提供要连接的数据库
String url = "jdbc:mysql://localhost:3306/test";
//3.提供连接需要的用户名和密码
Properties info = new Properties();
info.setProperty("user","root");
info.setProperty("password","");
//4.获取连接
Connection connection = driver.connect(url,info);
System.out.println(connection);
}
//使用驱动管理器DriverManage代替Driver
@Test
public void testConnection3() throws Exception {
//1.获取Driver实现类的对象
Class class1 = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) class1.newInstance();
//2.提供另外三个获取连接的基本信息
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "";
//注册驱动
DriverManager.registerDriver(driver);
//3.获取连接
Connection connection = DriverManager.getConnection(url,user,password);
System.out.println(connection);
}
//在三的基础上优化
//优化下面的序号二
@Test
public void ConnectionTest4() throws Exception {
//1.提供另外三个获取连接的基本信息
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "";
//2.获取Driver实现类的对象
//下面这句话可以在mysql中省略但是在Oracle中不能省略，所以不建议省略
//在mysql能省略是因为在META-INF下的services 中有java.sql.Driver文件里面的路径就是下面的内容
Class class1 = Class.forName("com.mysql.jdbc.Driver");
//在mysql的Driver实现类中，自动创建了，所以说下面的语句能省略
// Driver driver = (Driver) class1.newInstance();
// //注册驱动
// DriverManager.registerDriver(driver);
//3.获取连接
Connection connection = DriverManager.getConnection(url,user,password);
System.out.println(connection);
}
//方式五：将数据库连接需要的四个基本信息生命在配置文件中，通过读取配置文件的方式获取连接。
@Test
public void ConnectionTest5() throws Exception{
//1.读取配置文件中的四个基本信息
InputStream inputStream = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(inputStream);
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 connection = DriverManager.getConnection(url,user,password);
System.out.println(connection);
}
}
方式五要在src文件夹下新建 jdbc.properties 文件（properties文件内等号前后不要有空格，可能会误认为空格是后边变量的一部分）
user=root
password=abc123
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
driverClass=com.mysql.jdbc.Driver3.基于PreparedStatement的增删改查操作
对表的单条数据查询
package com.oneluckyguy.connection;
import com.oneluckyguy.bean.Customer;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
/**
* @author Liu Qingfeng
* @create 2021-01-03----0:06
*
* 对数据库增删改操作
* 对数据库的查操作
*/
public class PreparedStatementTest1 {
@Test
public void test1() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1.读取配置文件中的四个信息
Properties properties = new Properties();
InputStream inputStream = PreparedStatementTest1.class.getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(inputStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driverClass = properties.getProperty("driverClass");
String url = properties.getProperty("url");
//2.加载驱动
Class.forName(driverClass);
//3.连接
connection = DriverManager.getConnection(url,user,password);
//4.预编译sql 语句，返回PreparedStatement实例
String sql = "insert into customers(name,email,birth)values(?,?,?)";
preparedStatement = connection.prepareStatement(sql);
//5.填充占位符 (与数据库相关，从1开始不是0)
preparedStatement.setString(1,"HelloKetty");
preparedStatement.setString(2,"HelloKetty@gmail.com");
//生日的具体转换。
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date parse = simpleDateFormat.parse("1998-01-28");
preparedStatement.setDate(3,new java.sql.Date(parse.getTime()));
//6.执行sql
preparedStatement.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} finally {
//7.资源的关闭
if (preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
//使用自定义Utils工具类来开启连接和关闭资源
//删除操作
@Test
public void Delete() throws SQLException, IOException, ClassNotFoundException {
//1.获取数据库的连接
Connection connection = JDBCUtils.getConnection();
//2.预编译sql语句，返回PreparedStatement的实例
String sql = "delete from customers where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
preparedStatement.setInt(1,1);
//4.执行sql语句
preparedStatement.execute();
//5.资源的关闭
JDBCUtils.closeResource(connection,preparedStatement);
}
//使用自定义Utils工具类来开启连接和关闭资源
//修改操作
@Test
public void Modify() throws SQLException, IOException, ClassNotFoundException {
Connection connection = JDBCUtils.getConnection();
String sql = "update customer set name = ? where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"d茶");
preparedStatement.setInt(2,4);
preparedStatement.execute();
JDBCUtils.closeResource(connection,preparedStatement);
}
//整合增删改到一个函数
public void Update(String sql,Object ...args) throws SQLException, IOException, ClassNotFoundException {
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
preparedStatement.execute();
JDBCUtils.closeResource(connection,preparedStatement);
}
//测试整合函数
@Test
public void UpdateTest() throws SQLException, IOException, ClassNotFoundException {
String sql = "delete from customers where id = ?";
Integer args = 4;
Update(sql,args);
}
//一般查询操作
@Test
public void Query1() throws SQLException, IOException, ClassNotFoundException {
Connection connection = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,5);
//执行并返回结果集
ResultSet resultSet = preparedStatement.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};
// for (Object obj: data){
// System.out.println(obj);
// }
//方式三：封装到类的对象中（新建一个bean类）
Customer customer = new Customer(id,name,email,birth);
System.out.println(customer);
//关闭资源
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
}
//通用的查询语句：
//返回值是Customer类的，只能针对Customers表进行查询
public Customer Query2(String sql,Object ...args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
//获取结果集的元数据
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
//通过ResultSetMetaData实例获取元数据的列数
int columnCount = resultSetMetaData.getColumnCount();
if (resultSet.next()){
Customer customer = new Customer();
//处理结果集中一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
//获取列值 值是在resultSet中的，
Object columnValue = resultSet.getObject(i + 1);
//获取每个列的列名
String columnName = resultSetMetaData.getColumnName(i + 1);
//给customer中columnName赋值为value(反射)
Field declaredField = Customer.class.getDeclaredField(columnName);
declaredField.setAccessible(true);
declaredField.set(customer,columnValue);
}
return customer;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
//测试上面的查询操作
@Test
public void QueryTest(){
String sql = "select name, id, email from customers where id = ?";
Customer customer = Query2(sql, 5);
System.out.println(customer.toString());
String sql1 = "select name, id, email from customers where email = ?";
Customer customer1 = Query2(sql1, "linzl@gmail.com");
System.out.println(customer1.toString());
}
}
查询的customers表的类结构
package com.oneluckyguy.bean;
import java.util.Date;
/**
* @author Liu Qingfeng
* @create 2021-01-03----2:30
*/
public class Customer {
private int id;
private String name;
private String email;
public int getId() {
return id;
}
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", birth=" + birth +
'}';
}
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;
}
public Customer(int id, String name, String email, Date birth) {
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
public Customer() {
}
private Date birth;
}
需要用到的自定义Util类
package com.oneluckyguy.connection;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author Liu Qingfeng
* @create 2021-01-03----0:37
*/
public class JDBCUtils {
//这里声明为static是为了直接用类来调用，少写语句。不用再新建对象来调用了。
public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException {
//1.读取配置文件中的四个信息
Properties properties = new Properties();
InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
properties.load(inputStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driverClass = properties.getProperty("driverClass");
String url = properties.getProperty("url");
//2.加载驱动
Class.forName(driverClass);
//3.连接
Connection connection = DriverManager.getConnection(url,user,password);
return connection;
}
//关闭连接
public static void closeResource(Connection connection, Statement preparedStatement){
//7.资源的关闭
if (preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//关闭连接
public static void closeResource(Connection connection, Statement preparedStatement, ResultSet resultSet){
//7.资源的关闭
if (preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
4.对Order类的查询操作
order类
package com.oneluckyguy.bean;
import java.sql.Date;
/**
* @author Liu Qingfeng
* @create 2021-01-03----16:34
*/
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order() {
}
public Order(int orderId, String orderName, Date orderDate) {
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 +
'}';
}
}
查询操作：
package com.oneluckyguy.connection;
import com.oneluckyguy.bean.Order;
import org.junit.Test;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
/**
* @author Liu Qingfeng
* @create 2021-01-03----16:37
*/
public class OrderQueryTest {
//基本查询语句
@Test
public void test1() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select order_id,order_name,order_date from order1 where order_id = ? ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,1);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
int id = (int)resultSet.getObject(1);
String name = (String)resultSet.getObject(2);
Date date = (Date)resultSet.getObject(3);
Order order = new Order(id,name,date);
System.out.println(order);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
}
/*
* 针对于表的字段名和类的属性名不相同的情况：
* 1.必须声明SQL语句是，使用类的属性名来命名字段的别名
* 2.使用ResultSetMetaData时，需要使用getColumnLabel()来代替getColumnName()来获取别名
* 如果没有别名，getColumnLabel()获取的就是列的原来的名字。
*
* */
//用于order表的查询语句
public Order QueryForOrder(String sql,Object ...args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//建立连接
connection = JDBCUtils.getConnection();
//预编译
preparedStatement = connection.prepareStatement(sql);
//给sql中占位符赋值
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
//执行
resultSet = preparedStatement.executeQuery();
//用于获取表中列的别名。
ResultSetMetaData metaData = resultSet.getMetaData();
//对表中要显示的列计数
int columnCount = metaData.getColumnCount();
//对结果集遍历（几条（行）数据）：这是结果集只有一条数据的操作语句
//如果结果集有多行数据，应当用while，新建的order对象也应当是一个list集合
if (resultSet.next()){
Order order = new Order();
//对每一行中的列的值与列的名对应赋值
for (int i = 0; i < columnCount; i++) {
Object value = resultSet.getObject(i + 1);
//获取表中列的列名
//String columnName = metaData.getColumnName(i + 1);
//获取表中列的别名
String columnLabel = metaData.getColumnLabel(i + 1);
//反射
Field declaredField = Order.class.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(order,value);
}
return order;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
@Test
public void testQueryForOrder(){
String sql = "select order_id as orderId, order_name as orderName from order1 where order_id = ?";
Order order = QueryForOrder(sql, 1);
System.out.println(order.toString());
}
}
5.使用集合泛型实现查询多条结果和实现不同表的查询
package com.oneluckyguy.connection;
import com.oneluckyguy.bean.Customer;
import com.oneluckyguy.bean.Order;
import org.junit.Test;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.RecursiveTask;
/**
* @author Liu Qingfeng
* @create 2021-01-03----20:31
* 可以实现查询多条结果
* 可以实现不同表的查询
*/
public class PreparedStatementTest2 {
public <T> List<T> Query(Class<T> tClass,String sql, Object ...args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = preparedStatement.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList list = new ArrayList();
while (resultSet.next()){
T t = tClass.newInstance();
for (int i = 0; i < columnCount; i++) {
Object object = resultSet.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i + 1);
Field declaredField = tClass.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,object);
}
list.add(t);
}
ArrayList var = list;
return var;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
@Test
public void test(){
String sql = "select id,name,email from customers where id < ?";
List<Customer> query = Query(Customer.class, sql, 12);
query.forEach(System.out::println);
System.out.println();
String sql1 = "select order_id orderId,order_name orderName from order1 where order_id < ?";
List<Order> query1 = Query(Order.class, sql1, 4);
query1.forEach(System.out::println);
}
}
6.向数据库存储图片，视频等资源
package com.oneluckyguy.connection;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
/**
* @author Liu Qingfeng
* @create 2021-01-03----21:55
*/
public class BlobTest {
//向数据表customers中插入Blob类型的字段
@Test
public void testInsert() throws Exception {
//1.连接数据库
Connection connection = JDBCUtils.getConnection();
//2.预编译
String sql = "insert into customers (name,email,birth,photo)values(?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//3.占位符赋值
preparedStatement.setString(1,"Underwood");
preparedStatement.setString(2,"U@gmail.com");
preparedStatement.setObject(3,"2020-1-2");
//Blob类型数据要用流来操作
//也可以用下面方法来存储流
//用类加载器是资源初始路径是src目录
//用输入流初始路径是这个工程
// InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("school.png");
FileInputStream fileInputStream = new FileInputStream("src//school.png");
preparedStatement.setBlob(4,fileInputStream);
//4.执行
preparedStatement.execute();
//5.关闭资源
JDBCUtils.closeResource(connection,preparedStatement);
}
}
7.采用事务，解决转账出现异常的问题
package top.oneluckyguy.transaction;
import com.oneluckyguy.connection.JDBCUtils;
import org.junit.Test;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author Liu Qingfeng
* @create 2021-01-04----0:46
*/
public class TransactionTest {
//V1.0
//整合增删改到一个函数
public int Update(String sql,Object ...args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1.获取链接
connection = JDBCUtils.getConnection();
//2.预编译
preparedStatement = connection.prepareStatement(sql);
//3.占位符赋值
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
//4.执行
return preparedStatement.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
//5.关闭
JDBCUtils.closeResource(connection,preparedStatement);
}
return 0;
}
/*
* 未考虑数据库事务的情况下的转账操作
*
* 针对于数据表user_table 来说
* AA用户给BB用户转账100
*
* update user_table set balance = balance - 100 where user = 'AA'
* update user_table set balance = balance + 100 where user = 'BB'
*
* */
@Test
public void UpdateTest() throws SQLException, IOException, ClassNotFoundException {
String sql = "update user_table set balance = balance - 100 where user = ?";
Update(sql,"AA");
//用来模拟网络异常 会出现AA的钱减少了，BB的钱没增加
//System.out.println(10 / 0);
String sql1 = "update user_table set balance = balance + 100 where user = ?";
Update(sql1,"BB");
System.out.println("转账成功");
}
//V2.0考虑事务的情况下的转账操作
public int Update2( Connection connection,String sql,Object ...args) {
PreparedStatement preparedStatement = null;
try {
//连接通过传参获取。
//2.预编译
preparedStatement = connection.prepareStatement(sql);
//3.占位符赋值
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
//4.执行
return preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//5.关闭
JDBCUtils.closeResource(connection,preparedStatement);
}
return 0;
}
public void test() {
Connection connection = null;
try {
//获取连接
connection = top.oneluckyguy.transaction.JDBCUtils.getConnection();
//取消自动提交
connection.setAutoCommit(false);
//转账操作
String sql = "update user_table set balance = balance - 100 where user = ?";
Update2(connection,sql,"AA");
//模拟网络异常
// System.out.println(10 / 0);
String sql1 = "update user_table set balance = balance + 100 where user = ?";
Update2(connection,sql1,"BB");
System.out.println("转账成功");
//提交
connection.commit();
} catch (Exception e) {
e.printStackTrace();
try {
//如果出现异常，回滚数据
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
//重新打开自动提交
try {
connection.setAutoCommit(true);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//连接的关闭
top.oneluckyguy.transaction.JDBCUtils.closeResource(connection,null);
}
}
//查询V2.0（带有事务的查询）
public <T> List<T> Query(Connection connection, Class<T> tClass, String sql, Object ...args) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = preparedStatement.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList list = new ArrayList();
while (resultSet.next()){
T t = tClass.newInstance();
for (int i = 0; i < columnCount; i++) {
Object object = resultSet.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i + 1);
Field declaredField = tClass.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,object);
}
list.add(t);
}
ArrayList var = list;
return var;
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
try {
connection.setAutoCommit(true);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
//测试带事务的查询，模拟多线程
@Test
public void testTransactionSelect() throws SQLException, IOException, ClassNotFoundException {
Connection connection = top.oneluckyguy.transaction.JDBCUtils.getConnection();
String sql = "select user,password,balance from user_table where user = ?";
User user = (User) Query(connection,User.class,sql,"CC");
System.out.println(user.toString());
}
//测试带事务的更新，模拟多线程
@Test
public void testTransactionUpdate() throws Exception {
Connection connection = top.oneluckyguy.transaction.JDBCUtils.getConnection();
String sql = "update user_table set balance = ? where user = ?";
Update2(connection,sql,3000,"CC");
Thread.sleep(10000);
System.out.println("修改结束");
}
}
用到的utils类
package top.oneluckyguy.transaction;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author Liu Qingfeng
* @create 2021-01-03----0:37
*/
public class JDBCUtils {
//这里声明为static是为了直接用类来调用，少写语句。不用再新建对象来调用了。
public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException {
//1.读取配置文件中的四个信息
Properties properties = new Properties();
InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
properties.load(inputStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driverClass = properties.getProperty("driverClass");
String url = properties.getProperty("url");
//2.加载驱动
Class.forName(driverClass);
//3.连接
Connection connection = DriverManager.getConnection(url,user,password);
return connection;
}
//关闭连接
public static void closeResource(Connection connection, Statement preparedStatement){
//7.资源的关闭
if (preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//关闭连接
public static void closeResource(Connection connection, Statement preparedStatement, ResultSet resultSet){
//7.资源的关闭
if (preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
操作的User表的类结构
package top.oneluckyguy.transaction;
/**
* @author Liu Qingfeng
* @create 2021-01-05----14:53
*/
public class User {
private String user;
private String password;
private int balance;
public User() {
super();
}
public User(String user, String password, int balance) {
this.user = user;
this.password = password;
this.balance = balance;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getBalance() {
return balance;
}
public void setBalance(int balance) {
this.balance = balance;
}
@Override
public String toString() {
return "User{" +
"user='" + user + '\'' +
", password='" + password + '\'' +
", balance=" + balance +
'}';
}
}
8.数据库连接池测试
进行数据库连接池操作时先要导入jar包
比着相应的操作文档index.html写
1.C3P0
package top.oneluckyguy.pool;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;
import org.junit.Test;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @author Liu Qingfeng
* @create 2021-01-05----16:18
*/
public class C3P0Test {
@Test
public void testGetConnection() throws Exception {
//获取c3p0数据库连接池
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
comboPooledDataSource.setDriverClass("com.mysql.jdbc.Driver");
comboPooledDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
comboPooledDataSource.setUser("root");
comboPooledDataSource.setPassword("");
//通过设置相关参数对数据库连接池进行管理
//设置初始时数据库连接池的连接数
comboPooledDataSource.setInitialPoolSize(10);
Connection connection = comboPooledDataSource.getConnection();
System.out.println(connection);
//销毁c3p0数据库连接池
// DataSources.destroy(comboPooledDataSource);
}
//方式二： 使用配置文件
@Test
public void testGetConnection1() throws SQLException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("helloc3p0");
Connection connection = comboPooledDataSource.getConnection();
System.out.println(connection);
}
}
2.DBCP
package top.oneluckyguy.pool;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author Liu Qingfeng
* @create 2021-01-05----17:02
*/
public class DBCPTest {
@Test
public void testGetConnection() throws SQLException {
//创建DBCP数据库连接池
BasicDataSource dataSource = new BasicDataSource();
//设置基本信息
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///test");
dataSource.setUsername("root");
dataSource.setPassword("");
//还可以设置其他设计数据库连接池管理的相关属性
dataSource.setInitialSize(10);
dataSource.setMaxActive(10);
//连接操作
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
//方式二 采用配置文件
@Test
public void testGetConnection1() throws Exception {
Properties properties = new Properties();
//方式一:
InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
//方式二：
//FileInputStream fileInputStream = new FileInputStream("src/dbcp.properties");
properties.load(resourceAsStream);
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
}
3.Druid
package top.oneluckyguy.pool;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
/**
* @author Liu Qingfeng
* @create 2021-01-05----18:19
*/
public class DruidTest {
@Test
public void getConection() throws Exception {
Properties properties = new Properties();
InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
properties.load(resourceAsStream);
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
}
4.系统提供的增删改查获取链接方法
package top.oneluckyguy.dbutils;
import com.oneluckyguy.bean.Customer;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import top.oneluckyguy.util.JDBCUtils;
import java.sql.Connection;
import java.util.List;
import java.util.Map;
/**
* @author Liu Qingfeng
* @create 2021-01-05----18:43
*/
public class QueryRunnerTest {
//测试插入
@Test
public void testInsert() {
Connection connection3 = null;
try {
QueryRunner queryRunner = new QueryRunner();
connection3 = JDBCUtils.getConnection3();
String sql = "insert into customers(name,email,birth)values(?,?,?)";
int tom = queryRunner.update(connection3, sql, "Tom", "tom@gmail.com", "1997-05-21");
System.out.println(tom);
} catch (Exception e) {
e.printStackTrace();
} finally {
top.oneluckyguy.transaction.JDBCUtils.closeResource(connection3,null);
}
}
//测试查询
/*
* BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
* */
@Test
public void testQuery1() {
Connection connection3 = null;
try {
QueryRunner queryRunner = new QueryRunner();
connection3 = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id = ?";
BeanHandler<Customer> customerBeanHandler = new BeanHandler<>(Customer.class);
Customer query = queryRunner.query(connection3, sql, customerBeanHandler, 16);
System.out.println(query);
} catch (Exception e) {
e.printStackTrace();
} finally {
top.oneluckyguy.transaction.JDBCUtils.closeResource(connection3,null);
}
}
//返回多条记录
@Test
public void testQuery2() {
Connection connection3 = null;
try {
QueryRunner queryRunner = new QueryRunner();
connection3 = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id < ?";
BeanListHandler<Customer> customerBeanHandler = new BeanListHandler<>(Customer.class);
List<Customer> query = queryRunner.query(connection3, sql, customerBeanHandler, 16);
query.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
top.oneluckyguy.transaction.JDBCUtils.closeResource(connection3,null);
}
}
//MapHander:封装一条记录。。对比于testQuery1()；
//将字段及相应字段的值作为map中的key和value
@Test
public void testQuery3() {
Connection connection3 = null;
try {
QueryRunner queryRunner = new QueryRunner();
connection3 = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id = ?";
MapHandler mapHandler = new MapHandler();
Map<String, Object> query = queryRunner.query(connection3, sql, mapHandler, 16);
System.out.println(query);
} catch (Exception e) {
e.printStackTrace();
} finally {
top.oneluckyguy.transaction.JDBCUtils.closeResource(connection3,null);
}
}
//MapListHander:封装多条记录。。对比于testQuery2()；
//将字段及相应字段的值作为map中的key和value,将这些map添加到List
@Test
public void testQuery4() {
Connection connection3 = null;
try {
QueryRunner queryRunner = new QueryRunner();
connection3 = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id < ?";
MapListHandler mapListHandler = new MapListHandler();
List<Map<String, Object>> query = queryRunner.query(connection3, sql, mapListHandler, 16);
query.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
top.oneluckyguy.transaction.JDBCUtils.closeResource(connection3,null);
}
}
//ScalarHandler
//用于查询特殊值：
@Test
public void testQuery5() {
Connection connection3 = null;
try {
QueryRunner queryRunner = new QueryRunner();
connection3 = JDBCUtils.getConnection3();
String sql = "select count(*) from customers;";
ScalarHandler scalarHandler = new ScalarHandler();
Object query = queryRunner.query(connection3, sql, scalarHandler);
System.out.println(query);
} catch (Exception e) {
e.printStackTrace();
} finally {
top.oneluckyguy.transaction.JDBCUtils.closeResource(connection3,null);
}
}
@Test
public void testQuery6() {
Connection connection3 = null;
try {
QueryRunner queryRunner = new QueryRunner();
connection3 = JDBCUtils.getConnection3();
String sql = "select max(birth) from customers;";
ScalarHandler scalarHandler = new ScalarHandler();
Object query = queryRunner.query(connection3, sql, scalarHandler);
System.out.println(query);
} catch (Exception e) {
e.printStackTrace();
} finally {
top.oneluckyguy.transaction.JDBCUtils.closeResource(connection3,null);
}
}
}
1057
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
