JDBC
一、软件的架构方式
-
B/S架构:Browser Server 浏览器-服务器
-
C/S架构:Client Server 客户端-服务器
弊端:需要经常更新客户端来访问到最新修改后的信息
优势:有更好的用户体验
二、获取数据库连接
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author TheMutents
* @creat 2021-08-27-16:40
*/
public class JDBC_connection {
//连接方式1
@Test
public void connectiontest1() throws SQLException {
//1.提供java.sql.Driver接口实现类的对象
Driver driver = new com.mysql.jdbc.Driver();
//2.提供url,指明具体操作的数据库
// url:http://localhost:8080/gmall/keyboard.jpg
// jdbc:mysql : 协议:子协议
// localhost : ip地址
// 3306 : 默认mysql的端口号
// test : test数据库
String url = "jdbc:mysql://localhost:3306/test";
//3.提供Properties的对象,指明用户名和密码
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password","000422");
//4.调用driver的connect(),获取连接
Connection connection = driver.connect(url,info);
System.out.println(connection);
}
//连接方式2:使用反射获取Driver对象,不会出现第三方的api,有更好的移植性
@Test
public void connectionTest2() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
//1.使用java反射机制来提供java.sql.Driver接口实现类的对象
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//2.提供url,指明具体操作的数据库
String url = "jdbc:mysql://localhost:3306/test";
//3.提供Properties的对象,指明用户名和密码
Properties info = new Properties();
info.setProperty("user","root");
info.setProperty("password","000422");
//4.调用driver的connect(),获取连接
Connection connect = driver.connect(url, info);
System.out.println(connect);
}
//连接方式3:使用DriverManager代替Driver(常用方式)
@Test
public void connectionTest3() throws Exception {
//1.使用java反射机制来提供java.sql.Driver接口实现类的对象
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//2.获取连接信息:url user pasaword
String url="jdbc:mysql://localhost:3306/test";
String user="root";
String password="000422";
//3.注册驱动
DriverManager.registerDriver(driver);
//4.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
//连接方式4:只加载驱动,不用显示的注册驱动
@Test
public void connectionTest4() throws Exception{
//1.获取连接信息:url user password
String url="jdbc:mysql://localhost:3306/test";
String user="root";
String password="000422";
//2.加载Driver类,会执行类中的静态代码块,即注册驱动
/*
* 在mysql 的 Driver实现类中,声明了如下操作
* static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
* */
Class.forName("com.mysql.jdbc.Driver");//在mysql中该局可省略,因为引入的包包含了该操作,但是不建议省略,原因是连接其他数据库时不能省略
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
//连接方式5(最终版):将数据库连接需要的4个基本信息声明在配置文件中,通过读取配置文件的方式获取连接
/*
* 该方式的好处:
* 1.实现了数据与代码的分离,实现了解耦
* 2.如果需要修改配置文件信息,可以避免程序重新打包
* */
@Test
public void ConnectionTest5() throws IOException, ClassNotFoundException, SQLException {
//1.读取配置文件中的4个基本信息
InputStream resource = JDBC_connection.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(resource);
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String driverClass = pros.getProperty("driverClass");
//2.加载Driver类
Class.forName(driverClass);
//3.获取连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
}
三、使用PreparedStatement实现CRUD(增删改查)操作
import com.JDBC.lhk.Connection.JDBC_connection;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* 获取数据库连接和关闭资源
* @author TheMutents
* @creat 2021-09-20-13:38
*/
public class jdbcUtils {
/**
* 获取数据库连接
* @return Connection
* @throws Exception
*/
public static Connection getConnection() throws Exception {
//1.读取配置文件中的4个基本信息
InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(resource);
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String driverClass = pros.getProperty("driverClass");
//2.加载Driver类
Class.forName(driverClass);
//3.获取连接
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
/**
* 关闭Connection和PreparedStatement
* @param connection
* @param ps
*/
public static void closeResource(Connection connection, Statement ps){
//资源关闭
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void closeResource(Connection connection, Statement ps, ResultSet rs){
//资源关闭
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (rs!=null)
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//通用的增删改方法
public static int update(String sql, Object ...args) {
Connection coon = null;
PreparedStatement ps = null;
try {
//1.获取数据库连接
coon = jdbcUtils.getConnection();
//2.预编译sql语句
ps = coon.prepareStatement(sql);
//3.填充占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行
/**
* *ps.execute():
* *如果执行的是查询操作,有返回结果,则此方法返回true;
* *如果执行的是增、删、改操作,没有返回结果,则此方法返回false.
*/
// ps.execute();
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
jdbcUtils.closeResource(coon,ps);
}
return 0;
}
}
import com.JDBC.lhk.JDBCutil.jdbcUtils;
import org.junit.Test;
import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Properties;
/**
* @author TheMutents
* @creat 2021-09-19-15:54
*
* 使用PrepareStatement代替Statement,实现对数据库的增删改操作
* prepareStatement可以解决拼串和sql注入问题
* prepareStatement还可以操作Blob数据
* 可以实现更高效的批量操作
*/
public class PrepareStatementUpdateTest {
/**
* 向account表插入数据
*/
@Test
public void insertTest() {
Connection connection = null;
PreparedStatement ps = null;
try {
//1.读取配置文件中的4个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pos = new Properties();
pos.load(is);
String url = pos.getProperty("url");
String user = pos.getProperty("user");
String password = pos.getProperty("password");
String driverClass = pos.getProperty("driverClass");
//2.加载Driver类
Class<?> aClass = Class.forName(driverClass);
//3.获取连接
connection = DriverManager.getConnection(url, user, password);
//4.预编译sql语句,返回PrepareStatement实例
String sql="insert into account(username,balance,birth) values(?,?,?)";//?:占位符
ps = connection.prepareStatement(sql);
//5.填充占位符
ps.setString(1,"lhk");
ps.setDouble(2,15800);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yy-MM-dd");
java.util.Date date = simpleDateFormat.parse("2000-04-22");
ps.setDate(3,new Date(date.getTime()));
//6.执行sql
ps.execute();//execute:执行
} catch (Exception e) {
e.printStackTrace();
} finally {
//7.资源关闭
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//修改account表的一条记录
@Test
public void modifyTest() {
Connection coon = null;
PreparedStatement ps = null;
try {
//1.获取数据库连接
coon = jdbcUtils.getConnection();
//2.预编译sql语句,返回PrepareStatement实例
String sql="update account set username=?,balance=? where id=3";
ps = coon.prepareStatement(sql);
//3.填充占位符
ps.setString(1,"LHK");
ps.setDouble(2,16800);
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//5.关闭资源
jdbcUtils.closeResource(coon,ps);
}
}
//通用的增删改方法
public void update(String sql, Object ...args) {
Connection coon = null;
PreparedStatement ps = null;
try {
//1.获取数据库连接
coon = jdbcUtils.getConnection();
//2.预编译sql语句
ps = coon.prepareStatement(sql);
//3.填充占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
jdbcUtils.closeResource(coon,ps);
}
}
//测试通用的增删改方法
@Test
public void testCommonUpdate(){
// //删除
// String sql="delete from account where id = ?";
// update(sql,3);
// //修改
// String sql="update stuinfo set sex=? where id=?";
// update(sql,"f",1);
//增加
String sql="insert into stuinfo(id,name,sex) values(?,?,?)";
update(sql,2,"Themutents","m");
}
}
import com.JDBC.lhk.JDBCutil.jdbcUtils;
import com.JDBC.lhk.bean.Dept2;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
/**
* 针对dept2表进行查询操作
* @author TheMutents
* @creat 2021-09-21-15:42
*/
public class dept2ForQuery {
@Test
public void test(){
String sql="select department_id departmentId,department_name departmentName,manager_id managerId,location_id locationId from dept2 where department_id=?";
Dept2 dept2 = dept2ForQuery(sql, 20);
System.out.println(dept2);
sql="select department_id departmentId,department_name departmentName from dept2 where department_id=?";
Dept2 dept2_1 = dept2ForQuery(sql, 60);
System.out.println(dept2_1);
}
/**
* 针对dept2表的通用的查询方法
*
* 针对于表的字段名与类的属性名不相同的情况:
* 1.必须声明sql时,使用类的属性名来命名字段的别名
* 2.使用ResultSetMetaData时,需要使用getColumnLabel()来替换getColumnName( ),
* 获取列的别名。
* 说明:如果sql中没有给字段其别名,getColumnLabel()获取的就是列名
*/
public Dept2 dept2ForQuery(String sql,Object ...args) {
Connection coon = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.获取连接
coon = jdbcUtils.getConnection();
//2.获取PrepareStatement实例
ps = coon.prepareStatement(sql);
//3.填充在占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行并返回结果集
rs = ps.executeQuery();
//5.处理结果集
//5.1获取结果集的列数
ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据:即解释结果集的相关数据
int columnCount = rsmd.getColumnCount();//获取结果集的列数
//5.2处理结果集每一行数据的各个列
if (rs.next()){
Dept2 dept2 = new Dept2();
for (int i=0;i<columnCount;i++){
//获取每一列的列值
Object columnValue = rs.getObject(i + 1);
//获取结果集每一列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射机制给dept2对象指定的columnName属性,赋值为columnValue
//使用getColumnLabel()替换getColumnName(),来获取列的别名
Field field = Dept2.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(dept2,columnValue);
}
return dept2;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.关闭资源
jdbcUtils.closeResource(coon,ps,rs);
}
return null;
}
@Test
public void testQuery() {
Connection coon = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
//1.获取连接
coon = jdbcUtils.getConnection();
//2.预编译sql语句,并获取PrepareStatement实例
String sql="select * from dept2 where department_id=?";
ps = coon.prepareStatement(sql);
//3.填充占位符
ps.setObject(1,80);
//4.执行并返回结果集
resultSet = ps.executeQuery();
//5.处理结果集
if (resultSet.next()) {//next():判断结果集的下一条是否有数据,有则返回true,且指针下移,否则返回false
int dept_id = resultSet.getInt(1);
String dept_name = resultSet.getString(2);
int manager_id = resultSet.getInt(3);
int location_id = resultSet.getInt(4);
Dept2 dept2 = new Dept2(dept_id, dept_name, manager_id, location_id);
System.out.println(dept2);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.关闭资源
jdbcUtils.closeResource(coon,ps,resultSet);
}
}
}
/**
* 使用preparedStatement实现针对不同类的通用查询方法
* @author TheMutents
* @creat 2021-09-26-11:51
*/
public class PreparedSatementQuery {
@Test
public void test(){
String sql="select department_id departmentId,department_name departmentName from dept2 where department_id=?";
Dept2 d = query(Dept2.class, sql, 50);
System.out.println(d);
sql="select * from account where id=?";
Account a = query(Account.class, sql, 1);
System.out.println(a);
System.out.println("----------------------------");
sql="select * from account";
List<Account> list1 = getQuery(Account.class, sql);
list1.forEach(System.out::println);
sql="select department_id departmentId,department_name departmentName from dept2 where department_id<?";
List<Dept2> list2 = getQuery(Dept2.class, sql, 100);
list2.forEach(System.out::println);
}
/**
* 查询一条记录的通用方法
* @param clazz
* @param sql
* @param args
* @param <T>
* @return
*/
public <T>T query(Class<T> clazz,String sql,Object ...args){
Connection coon = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.获取连接
coon = jdbcUtils.getConnection();
//2.获取PrepareStatement实例
ps = coon.prepareStatement(sql);
//3.填充在占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行并返回结果集
rs = ps.executeQuery();
//5.处理结果集
//5.1获取结果集的列数
ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据:即解释结果集的相关数据
int columnCount = rsmd.getColumnCount();//获取结果集的列数
//5.2处理结果集每一行数据的各个列
if (rs.next()){
T t = clazz.newInstance();
for (int i=0;i<columnCount;i++){
//获取每一列的列值
Object columnValue = rs.getObject(i + 1);
//获取结果集每一列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射机制给dept2对象指定的columnName属性,赋值为columnValue
//使用getColumnLabel()替换getColumnName(),来获取列的别名
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.关闭资源
jdbcUtils.closeResource(coon,ps,rs);
}
return null;
}
/**
* 查询多条记录的方法
* @param clazz
* @param sql
* @param args
* @param <T>
* @return
*/
public <T> List<T> getQuery(Class<T> clazz, String sql, Object ...args) {
Connection coon = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.获取连接
coon = jdbcUtils.getConnection();
//2.获取PrepareStatement实例
ps = coon.prepareStatement(sql);
//3.填充在占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行并返回结果集
rs = ps.executeQuery();
//5.处理结果集
//5.1获取结果集的列数
ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据:即解释结果集的相关数据
int columnCount = rsmd.getColumnCount();//获取结果集的列数
//创建集合对象
ArrayList<T> list = new ArrayList<>();
//5.2处理结果集每一行数据的各个列,给指定的每个t对象赋值
while (rs.next()){
T t = clazz.newInstance();
for (int i=0;i<columnCount;i++){
//获取每一列的列值
Object columnValue = rs.getObject(i + 1);
//获取结果集每一列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射机制给dept2对象指定的columnName属性,赋值为columnValue
//使用getColumnLabel()替换getColumnName(),来获取列的别名
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.关闭资源
jdbcUtils.closeResource(coon,ps,rs);
}
return null;
}
}
四、操作Blob类型数据
如果在指定了相关的Blob类型以后,还报错:xxx too large,那么在mysql的安装目录下,找my.ini文件加上如下的配置参数: max_allowed_packet=16M。同时注意:修改了my.ini文件之后,需要重新启动mysql服务。
import com.JDBC.lhk.JDBCutil.jdbcUtils;
import org.junit.Test;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 使用PrepareStatement操作Blob数据
* @author TheMutents
* @creat 2021-10-01-11:20
*/
public class BlobTest {
/**
* 向beauty表中插入blob数据
* 修改mysql中的全局变量:SET GLOBAL max_allowed_packet=16777216;
* @throws Exception
*/
@Test
public void test() {
Connection connection = null;
PreparedStatement ps = null;
FileInputStream is=null;
try {
connection = jdbcUtils.getConnection();
String sql="insert into beauty(name,sex,borndate,phone,photo,boyfriend_id) values(?,?,?,?,?,?) ";
ps = connection.prepareStatement(sql);
ps.setObject(1,"MM");
ps.setObject(2,"f");
ps.setObject(3,"1998-05-06");
ps.setObject(4,"15677775555");
is=new FileInputStream(new File("xc.png"));
ps.setBlob(5,is);
ps.setInt(6,3);
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (is!=null){
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
jdbcUtils.closeResource(connection,ps);
}
}
/**
* 查询beauty表的blob字段
*/
@Test
public void test1() {
Connection connection = null;
PreparedStatement ps = null;
InputStream is=null;
FileOutputStream fos=null;
try {
connection = jdbcUtils.getConnection();
String sql="select photo from beauty where id=?";
ps = connection.prepareStatement(sql);
ps.setInt(1,27);
ResultSet rs = ps.executeQuery();
if (rs.next()){
is = rs.getAsciiStream("photo");
fos = new FileOutputStream(new File("xiaochou.png"));
int i=0;
byte[] buff=new byte[1024];
while((i= is.read(buff))!=-1){
fos.write(buff,0, i);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (is!=null){
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (fos!=null){
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
jdbcUtils.closeResource(connection,ps);
}
}
五、批量插入
import com.JDBC.lhk.JDBCutil.jdbcUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
/**
* 实现批量插入操作,使用PreparedStatement实现更高效的批量插入
* update、 delete本身就具有批量操作的效果。
* 此时的批量操作,主要指的是批量插入。
* 向goods表中插入20000条数据
* @author TheMutents
* @creat 2021-10-01-20:57
*/
public class InsertTest {
//方式1:使用Statement实现批量插入操作
@Test
public void test1() throws Exception {
long start = System.currentTimeMillis();
Connection connection = jdbcUtils.getConnection();
Statement sm = connection.createStatement();
for (int i=1;i<=20000;i++){
String sql="insert into goods(name) values('name_+"+i+"')";
sm.execute(sql);
}
long end = System.currentTimeMillis();
System.out.println("花费时间:"+(end-start)+"ms");//花费时间:19844ms
jdbcUtils.closeResource(connection,sm);
}
//方式2:使用PrepareStatement实现批量插入操作
@Test
public void test2() throws Exception {
long start = System.currentTimeMillis();
Connection connection = jdbcUtils.getConnection();
String sql="insert into goods(name) values(?)";
PreparedStatement ps = connection.prepareStatement(sql);
for (int i=1;i<=20000;i++){
ps.setString(1,"name_"+i);
ps.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("花费时间:"+(end-start)+"ms");//花费时间:19802ms
jdbcUtils.closeResource(connection,ps);
}
//方式3
/*
* 修改1: 使用 addBatch() / executeBatch() / clearBatch()
* 修改2:mysql服务器默认是关闭批处理的,我们需要通过一个参数,让mysql开启批处理的支持。
* ?rewriteBatchedStatements=true 写在配置文件jdbc.properties的url后面
* 修改3:使用更新的mysql 驱动:mysql-connector-java-5.1.37-bin.jar
*
*/
@Test
public void test3() {
Connection connection = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
connection = jdbcUtils.getConnection();
String sql="insert into goods(name) values(?)";
ps = connection.prepareStatement(sql);
for (int i=1;i<=20000;i++){
ps.setString(1,"name_"+i);
//1.攒sql
ps.addBatch();
if(i%500==0){//攒500条sql
//2.执行
ps.executeBatch();
//3.清空Batch
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("花费时间:"+(end-start)+"ms");//花费时间:682ms
} catch (Exception e) {
e.printStackTrace();
}
jdbcUtils.closeResource(connection,ps);
}
//方式4
/*
* 在方式3的基础上操作
* 使用Connection 的 setAutoCommit(false) / commit()
*/
@Test
public void test4() throws Exception {
long start = System.currentTimeMillis();
Connection connection = jdbcUtils.getConnection();
//取消自动提交
connection.setAutoCommit(false);
String sql="insert into goods(name) values(?)";
PreparedStatement ps = connection.prepareStatement(sql);
for (int i=1;i<=20000;i++){
ps.setString(1,"name_"+i);
//1.攒sql
ps.addBatch();
if (i%500==0){
//2.执行
ps.executeBatch();
//3.清空
ps.clearBatch();
}
}
//提交数据
connection.commit();
long end = System.currentTimeMillis();
System.out.println("花费时间:"+(end-start)+"ms");//花费时间:623ms
jdbcUtils.closeResource(connection,ps);
}
}
六、数据库事务
import com.jdbc.lhk.Bean.Account;
import com.jdbc.lhk.utils.JdbcUtils;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.*;
/**
*事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
*
* 事务处理(事务操作):保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方式。
* 当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),那么这些修改就永久地保存下来;要么数据
* 库管理系统将放弃所作的所有修改,整个事务回滚(rollback)到最初状态
*
* 数据一旦提交,就不可回滚。
*
*4.哪些操作会导致数据的自动提交?
* >DDL操作一旦执行,都会自动提交。
* >set autocommit = false对DDL操作失效
* >DML默认情况下,—旦执行,就会自动提交。
* >我们可以通过set autocommit = false的方式取消DML操作的自动提交。
* >默认在关闭连接时,会自动的提交数据
*
* @author TheMutents
* @creat 2021-10-02-22:28
*/
public class TransactionTest {
/**
* 不考虑事务的转账操作
* 针对account表:
* 张无忌给赵敏转账100
*
*/
@Test
public void test(){
String sql="update account set balance=balance-100 where username='?'";
update(sql,"张无忌");
//遇到异常(网络异常等等)
System.out.println(10/0);
String sql2="update account set balance=balance+100 where username='?'";
update(sql2,"赵敏");
System.out.println("转账成功");
}
//通用的增删改方法-----1.0
public int update(String sql, Object ...args) {
Connection coon = null;
PreparedStatement ps = null;
try {
//1.获取数据库连接
coon = JdbcUtils.getConnection();
//2.预编译sql语句
ps = coon.prepareStatement(sql);
//3.填充占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
JdbcUtils.closeResource(coon,ps);
}
return 0;
}
/**
* 考虑事务后的转账操作
* 针对account表:
* 张无忌给赵敏转账100
*/
@Test
public void test1() {
Connection coon = null;
try {
coon = JdbcUtils.getConnection();
//1.取消自动提交
coon.setAutoCommit(false);
String sql="update account set balance=balance-100 where username=?";
update(coon,sql,"张无忌");
//遇到异常(网络异常等等)
System.out.println(10/0);
String sql2="update account set balance=balance+100 where username=?";
update(coon,sql2,"赵敏");
System.out.println("转账成功");
//2.提交数据
coon.commit();
} catch (Exception e) {
e.printStackTrace();
//3.遇到异常回滚数据
try {
coon.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
//恢复连接自动提交
try {
coon.setAutoCommit(true);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
JdbcUtils.closeResource(coon,null);
}
}
//通用的增删改方法-----2.0(考虑事务)
public int update(Connection coon,String sql, Object ...args) {
PreparedStatement ps = null;
try {
//1.预编译sql语句
ps = coon.prepareStatement(sql);
//2.填充占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//3.执行
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//4.关闭资源,这里不关闭连接
JdbcUtils.closeResource(null,ps);
}
return 0;
}
//***********************************************************************
/**
* 设置数据库的隔离级别
* */
/*查询记录*/
@Test
public void test3() throws Exception {
Connection coon= JdbcUtils.getConnection();
//获取当前的连接的隔离级别(全局的隔离级别)
System.out.println(coon.getTransactionIsolation());
//设置当前数据库的隔离级别
//coon.setTransactionIsolation();
//取消自动提交
coon.setAutoCommit(false);
String sql="select id,username,balance from account where id=?";
Account a1 = query(coon, Account.class, sql, 1);
System.out.println(a1);
}
/*修改记录*/
@Test
public void test4() throws Exception {
Connection coon = JdbcUtils.getConnection();
//取消自动提交
coon.setAutoCommit(false);
String sql="update account set balance=? where id= ?";
update(coon, sql, 1500,1);
Thread.sleep(15000);
System.out.println("修改结束");
}
/**
* 查询一条记录的通用方法---2.0 考虑了事务
* @param clazz
* @param sql
* @param args
* @param <T>
* @return
*/
public <T>T query(Connection coon,Class<T> clazz,String sql,Object ...args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.获取PrepareStatement实例
ps = coon.prepareStatement(sql);
//2.填充在占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//3.执行并返回结果集
rs = ps.executeQuery();
//4.处理结果集
//4.1获取结果集的列数
ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据:即解释结果集的相关数据
int columnCount = rsmd.getColumnCount();//获取结果集的列数
//4.2处理结果集每一行数据的各个列
if (rs.next()){
T t = clazz.newInstance();
for (int i=0;i<columnCount;i++){
//获取每一列的列值
Object columnValue = rs.getObject(i + 1);
//获取结果集每一列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射机制给dept2对象指定的columnName属性,赋值为columnValue
//使用getColumnLabel()替换getColumnName(),来获取列的别名
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
JdbcUtils.closeResource(null,ps,rs);
}
return null;
}
}
七、DAO及其相关实现类
1.BaseDao
import com.jdbc.lhk.utils.JdbcUtils;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* DAO:
* 封装了针对数据表的通用操作
* @author TheMutents
* @creat on 2021-10-08-15:21
*/
public abstract class BaseDao {
//通用的增删改方法-----2.0(考虑事务)
public int update(Connection coon, String sql, Object ...args) {
PreparedStatement ps = null;
try {
//1.预编译sql语句
ps = coon.prepareStatement(sql);
//2.填充占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//3.执行
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//4.关闭资源,这里不关闭连接
JdbcUtils.closeResource(null,ps);
}
return 0;
}
/**
* 查询一条记录的通用方法---2.0 考虑了事务
* @param clazz
* @param sql
* @param args
* @param <T>
* @return
*/
public <T>T query(Connection coon,Class<T> clazz,String sql,Object ...args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.获取PrepareStatement实例
ps = coon.prepareStatement(sql);
//2.填充在占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//3.执行并返回结果集
rs = ps.executeQuery();
//4.处理结果集
//4.1获取结果集的列数
ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据:即解释结果集的相关数据
int columnCount = rsmd.getColumnCount();//获取结果集的列数
//4.2处理结果集每一行数据的各个列
if (rs.next()){
T t = clazz.newInstance();
for (int i=0;i<columnCount;i++){
//获取每一列的列值
Object columnValue = rs.getObject(i + 1);
//获取结果集每一列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射机制给dept2对象指定的columnName属性,赋值为columnValue
//使用getColumnLabel()替换getColumnName(),来获取列的别名
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
JdbcUtils.closeResource(null,ps,rs);
}
return null;
}
/**
* 查询多条记录的方法----2.0(考虑上事务)
* @param clazz
* @param sql
* @param args
* @param <T>
* @return
*/
public <T> List<T> getQuery(Connection coon,Class<T> clazz, String sql, Object ...args) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
//2.获取PrepareStatement实例
ps = coon.prepareStatement(sql);
//3.填充在占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行并返回结果集
rs = ps.executeQuery();
//5.处理结果集
//5.1获取结果集的列数
ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据:即解释结果集的相关数据
int columnCount = rsmd.getColumnCount();//获取结果集的列数
//创建集合对象
ArrayList<T> list = new ArrayList<>();
//5.2处理结果集每一行数据的各个列,给指定的每个t对象赋值
while (rs.next()){
T t = clazz.newInstance();
for (int i=0;i<columnCount;i++){
//获取每一列的列值
Object columnValue = rs.getObject(i + 1);
//获取结果集每一列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射机制给dept2对象指定的columnName属性,赋值为columnValue
//使用getColumnLabel()替换getColumnName(),来获取列的别名
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.关闭资源
JdbcUtils.closeResource(null,ps,rs);
}
return null;
}
//用于查询特殊值的通用方法
public <E> E getValue(Connection coon,String sql,Object ...args) {
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps = coon.prepareStatement(sql);
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if(rs.next()){
return (E) rs.getObject(1);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(null,ps,rs);
}
return null;
}
}
2.实体类
/**
* @author TheMutents
* @creat on 2021-10-16-22:03
*/
public class Account {
private int id ;
private String username;
private double balance;
public Account() {
}
public Account(int id, String username, double balance) {
this.id = id;
this.username = username;
this.balance = balance;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", username='" + username + '\'' +
", balance=" + balance +
'}';
}
}
3.工具类
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* 获取数据库连接和关闭资源
* @author TheMutents
* @creat 2021-09-20-13:38
*/
public class JdbcUtils {
/**
* 获取数据库连接
* @return Connection
* @throws Exception
*/
public static Connection getConnection() throws Exception {
//1.读取配置文件中的4个基本信息
InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(resource);
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String driverClass = pros.getProperty("driverClass");
//2.加载Driver类
Class.forName(driverClass);
//3.获取连接
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
/**
* 关闭Connection和PreparedStatement
* @param connection
* @param ps
*/
public static void closeResource(Connection connection, Statement ps){
//资源关闭
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void closeResource(Connection connection, Statement ps, ResultSet rs){
//资源关闭
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (rs!=null)
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//通用的增删改方法
public static int update(String sql, Object ...args) {
Connection coon = null;
PreparedStatement ps = null;
try {
//1.获取数据库连接
coon = JdbcUtils.getConnection();
//2.预编译sql语句
ps = coon.prepareStatement(sql);
//3.填充占位符
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行
/**
* *ps.execute():
* *如果执行的是查询操作,有返回结果,则此方法返回true;
* *如果执行的是增、删、改操作,没有返回结果,则此方法返回false.
*/
// ps.execute();
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
JdbcUtils.closeResource(coon,ps);
}
return 0;
}
}
4.针对于account表的常用操作的接口
import com.jdbc.lhk.Bean.Account;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
/**
* 此接口用于规范针对于account表的常常用操作
* @author TheMutents
* @creat on 2021-10-16-22:52
*/
public interface AccountDao {
/**
* 将account对象添加到数据表中
* @param coon
* @param account
*/
void insert(Connection coon, Account account);
/**
* 根据id删除表中的一条记录
* @param coon
* @param id
*/
void deleteById(Connection coon,int id);
/**
* 根据内存中的account的对象修改表中的指定的记录
* @param coon
* @param account
*/
void updateById(Connection coon,Account account);
/**
* 根据指定的id从查询得到对应的Account对象
* @param coon
* @param id
*/
Account getAccountById(Connection coon,int id);
/**
* 查询表中的所有记录构成的集合
* @param coon
* @return
*/
List<Account> getAll(Connection coon);
/**
* 返回表中的记录条数
* @param coon
* @return
*/
Long getCount(Connection coon);
}
5.接口实现类
import com.jdbc.lhk.Bean.Account;
import java.sql.Connection;
import java.util.List;
/**
* @author TheMutents
* @creat on 2021-10-16-23:07
*/
public class AccountDaoImpl extends BaseDao implements AccountDao{
@Override
public void insert(Connection coon, Account account) {
String sql ="insert into account(username,balance) values(?,?)";
update(coon,sql,account.getUsername(),account.getBalance());
}
@Override
public void deleteById(Connection coon, int id) {
String sql="delete from account where id=?";
update(coon,sql,id);
}
@Override
public void updateById(Connection coon, Account account) {
String sql="update account set username=?,balance=? where id=?";
update(coon,sql,account.getUsername(),account.getBalance(),account.getId());
}
@Override
public Account getAccountById(Connection coon, int id) {
String sql="select id,username,balance from account where id=?";
Account query = query(coon, Account.class, sql, id);
return query;
}
@Override
public List<Account> getAll(Connection coon) {
String sql="select id,username,balance from account";
List<Account> query = getQuery(coon, Account.class, sql);
return query;
}
@Override
public Long getCount(Connection coon) {
String sql="select count(*) from account";
return getValue(coon, sql);
}
}
6.测试类
import com.jdbc.lhk.Bean.Account;
import com.jdbc.lhk.DAO.AccountDaoImpl;
import com.jdbc.lhk.utils.JdbcUtils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.util.List;
/**
* @author TheMutents
* @creat on 2021-10-19-17:49
*/
class AccountDaoImplTest {
AccountDaoImpl accountDaoImpl=new AccountDaoImpl();
@Test
void insert(){
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
Account account = new Account(3,"lhk",15000);
accountDaoImpl.insert(connection,account);
System.out.println("添加成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void deleteById() {
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
accountDaoImpl.deleteById(connection,3);
System.out.println("删除成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void updateById() {
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
Account account = new Account(1,"无忌",15000);
accountDaoImpl.updateById(connection,account);
System.out.println("修改成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void getAccountById() {
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
Account account = accountDaoImpl.getAccountById(connection, 1);
System.out.println(account);
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void getAll() {
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
List<Account> all = accountDaoImpl.getAll(connection);
all.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void getCount() {
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
Long count = accountDaoImpl.getCount(connection);
System.out.println("account表中共有"+count+"条记录");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
}
八、数据库连接池
1.C3P0数据库连接池技术
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<named-config name="MyC3P0">
<!-- 提供获取连接的4个基本信息 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">000422</property>
<!-- 进行数据库管理的基本信息 -->
<!-- acquireIncrement:当数据库连接池连接数不够时,c3p0一次性向数据库服务器申请的连接数 -->
<property name="acquireIncrement">5</property>
<!-- initialPoolSize:c3p0数据库连接池初始化时的连接数 -->
<property name="initialPoolSize">10</property>
<!-- minPoolSize:c3p0数据库连接池维护的最少的连接数 -->
<property name="minPoolSize">10</property>
<!-- maxPoolSize:c3p0数据库连接池维护的最多的连接数 -->
<property name="maxPoolSize">100</property>
<!-- maxStatements:c3p0数据库连接池维护的最多的Statement的个数 -->
<property name="maxStatements">50</property>
<!-- maxStatementsPerConnection:每个连接中可以最多使用的Statement的个数 -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>
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 TheMutents
* @creat on 2021-10-20-20:47
*/
public class C3P0 {
//方式一:
@Test
public void testGetConnection() throws PropertyVetoException, SQLException {
//获取C3P0数据库连接池
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
cpds.setUser("root");
cpds.setPassword("000422");
//通过设置相关参数,对数据库连接池进行管理
//设置初始的数据库连接池中的连接数
cpds.setInitialPoolSize(10);
Connection coon = cpds.getConnection();
System.out.println(coon);
//销毁c3p0连接池
DataSources.destroy( cpds );
}
//方式二:使用配置文件获取数据库连接池
@Test
public void testGetConnection1() throws SQLException {
ComboPooledDataSource cpds = new ComboPooledDataSource("MyC3P0");
Connection conn = cpds.getConnection();
System.out.println(conn);
DataSources.destroy( cpds );
}
}
- DBCP数据库连接池技术
#基本配置信息
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=000422
#设置其他管理数据库连接池的相关属性
initialSize=10
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author TheMutents
* @creat on 2021-10-21-12:31
*/
public class DBCP {
/**
* 获取DBCP连接池方式一(不推荐)
* @throws SQLException
*/
@Test
public void testGetConnection() throws SQLException {
//创建DBCP的数据库连接池
BasicDataSource source=new BasicDataSource();
//基本配置信息
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUrl("jdbc:mysql:///test");
source.setUsername("root");
source.setPassword("000422");
//设置其他管理数据库连接池的相关属性
source.setInitialSize(10);
source.setMaxActive(10);
Connection coon = source.getConnection();
System.out.println(coon);
}
/**
*获取DBCP连接池方式二
*/
private static Properties properties=null;
static{
try {
properties = new Properties();//只创建一个Properties对象
//读取dbcp.properties文件
//方式一
// InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
//方式二
FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
//将流对象加载到Properties对象中
properties.load(is);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void testGetConnection1() throws Exception {
//创建DBCP的数据库连接池
DataSource source = BasicDataSourceFactory.createDataSource(properties);
Connection coon = source.getConnection();
System.out.println(coon);
}
}
- Druid数据库连接池技术
#基本配置信息
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=000422
#设置其他管理数据库连接池的相关属性
initialSize=10
maxActive=10
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author TheMutents
* @creat on 2021-10-21-13:42
*/
public class Druid {
@Test
public void getConnection() throws Exception {
Properties properties=new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
DataSource source = DruidDataSourceFactory.createDataSource(properties);
Connection conn = source.getConnection();
System.out.println(conn);
}
}
4.测试类
import com.jdbc.lhk.Bean.Account;
import com.jdbc.lhk.DAO.AccountDaoImpl;
import com.jdbc.lhk2.utils.JdbcUtils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.util.List;
/**
* @author TheMutents
* @creat on 2021-10-19-17:49
*/
class AccountDaoImplTest {
AccountDaoImpl accountDaoImpl=new AccountDaoImpl();
@Test
void insert(){
Connection connection = null;
try {
connection = JdbcUtils.getConnection1();
Account account = new Account(3,"lhk",15000);
accountDaoImpl.insert(connection,account);
System.out.println("添加成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void deleteById() {
Connection connection = null;
try {
connection = JdbcUtils.getConnection1();
accountDaoImpl.deleteById(connection,3);
System.out.println("删除成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void updateById() {
Connection connection = null;
try {
connection = JdbcUtils.getConnection1();
Account account = new Account(1,"无忌",15000);
accountDaoImpl.updateById(connection,account);
System.out.println("修改成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void getAccountById() {
Connection connection = null;
try {
connection = com.jdbc.lhk2.utils.JdbcUtils.getConnection3();
Account account = accountDaoImpl.getAccountById(connection, 1);
System.out.println(account);
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void getAll() {
Connection connection = null;
try {
connection = JdbcUtils.getConnection2();
List<Account> all = accountDaoImpl.getAll(connection);
all.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
@Test
void getCount() {
Connection connection = null;
try {
connection = JdbcUtils.getConnection1();
Long count = accountDaoImpl.getCount(connection);
System.out.println("account表中共有"+count+"条记录");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(connection,null);
}
}
}
5.工具类
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author TheMutents
* @creat on 2021-10-20-21:48
*/
public class JdbcUtils {
/**
* 使用C3PO的数据库连接技术
* @return
* @throws SQLException
*/
//数据库连接池只需提供一个即可。|
private static ComboPooledDataSource cpds = new ComboPooledDataSource("MyC3P0");
public static Connection getConnection1() throws SQLException {
Connection conn = cpds.getConnection();
return conn;
}
/**
* 使用DBCP的数据库连接技术
* @return
* @throws Exception
*/
//创建一个DBCP连接池即可
private static Properties properties=null;
private static DataSource source=null;
static {
try {
properties = new Properties();
FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
//将流对象加载到Properties对象中
properties.load(is);
source = BasicDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection2() throws Exception {
Connection coon = source.getConnection();
return coon;
}
/**
* 使用Druid的数据库连接技术
* @return
*/
private static DataSource source1;
static {
try {
Properties properties=new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
source1 = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection3() throws SQLException {
Connection conn = source1.getConnection();
return conn;
}
/**
* 关闭Connection和PreparedStatement
* @param connection
* @param ps
*/
public static void closeResource(Connection connection, Statement ps){
//资源关闭
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void closeResource(Connection connection, Statement ps, ResultSet rs){
//资源关闭
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (rs!=null)
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
九、Apache-DBUtils实现CRUD操作
1.工具类
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;
import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author TheMutents
* @creat on 2021-10-20-21:48
*/
public class JdbcUtils {
/**
* 使用C3PO的数据库连接技术
* @return
* @throws SQLException
*/
//数据库连接池只需提供一个即可。|
private static ComboPooledDataSource cpds = new ComboPooledDataSource("MyC3P0");
public static Connection getConnection1() throws SQLException {
Connection conn = cpds.getConnection();
return conn;
}
/**
* 使用DBCP的数据库连接技术
* @return
* @throws Exception
*/
//创建一个DBCP连接池即可
private static Properties properties=null;
private static DataSource source=null;
static {
try {
properties = new Properties();
FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
//将流对象加载到Properties对象中
properties.load(is);
source = BasicDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection2() throws Exception {
Connection coon = source.getConnection();
return coon;
}
/**
* 使用Druid的数据库连接技术
* @return
*/
private static DataSource source1;
static {
try {
Properties properties=new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
source1 = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection3() throws SQLException {
Connection conn = source1.getConnection();
return conn;
}
/**
* 关闭Connection和PreparedStatement
* @param connection
* @param ps
*/
public static void closeResource(Connection connection, Statement ps){
//资源关闭
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void closeResource(Connection connection, Statement ps, ResultSet rs){
//资源关闭
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (rs!=null)
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
/**
* 使用dbutils.jar中的DbUtils工具类,实现资源的关闭
* @param connection
* @param ps
* @param rs
*/
public static void closeResource1(Connection connection, Statement ps, ResultSet rs){
// try {
// DbUtils.close(connection);
// DbUtils.close(rs);
// DbUtils.close(ps);
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(ps);
DbUtils.closeQuietly(connection);
}
}
2.测试类
import com.jdbc.lhk.Bean.Account;
import com.jdbc.lhk2.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它封装了针对数据库的增删改查操作,
* 学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。
* @author TheMutents
* @creat on 2021-10-21-14:28
*/
public class QueryRunnerTest {
//使用dbutils测试插入操作
@Test
public void testInsert() {
Connection conn = null;
try {
QueryRunner queryRunner = new QueryRunner();
conn = JdbcUtils.getConnection3();
String sql="insert into account(username,balance) values(?,?)";
int insertCount = queryRunner.update(conn, sql, "lhk", 12000);
System.out.println("成功添加了"+insertCount+"条数据");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}
/**
* BeanHandler:是ResultSetHandler接口的实现类,用于封装表中的一条记录
*/
@Test
public void query1() {
Connection conn=null;
try {
QueryRunner queryRunner=new QueryRunner();
conn = JdbcUtils.getConnection3();
String sql="select id,username,balance from account where id=?";
BeanHandler<Account> handler = new BeanHandler<>(Account.class);
Account account = queryRunner.query(conn, sql, handler, 4);
System.out.println(account);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}
/**
* BeanListHandler:是ResultSetHandler接口的实现类,用于封装表中的多条记录构成的集合。
*/
@Test
public void query2() {
Connection conn = null;
try {
QueryRunner queryRunner=new QueryRunner();
conn = JdbcUtils.getConnection3();
String sql="select id,username,balance from account where id<?";
BeanListHandler<Account> handler = new BeanListHandler<>(Account.class);
List<Account> accounts = queryRunner.query(conn, sql, handler, 3);
accounts.forEach(System.out::println);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}
/**
* MapHandler:是ResultSetHandler接口的实现类,对应表中的一条记录
* 将字段及相应字段的值作为map中的key和value
*/
@Test
public void query3() {
Connection conn = null;
try {
QueryRunner queryRunner=new QueryRunner();
conn = JdbcUtils.getConnection3();
String sql="select id,username,balance from account where id=?";
MapHandler handler = new MapHandler();
Map<String, Object> map = queryRunner.query(conn, sql, handler, 4);
System.out.println(map);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}
/**
* MapHandler:是ResultSetHandler接口的实现类,对应表中的多条记录,封装为集合
* 将字段及相应字段的值作为map中的key和value
*/
@Test
public void query4() {
Connection conn = null;
try {
QueryRunner queryRunner=new QueryRunner();
conn = JdbcUtils.getConnection3();
String sql="select id,username,balance from account where id<?";
MapListHandler handler = new MapListHandler();
List<Map<String, Object>> list = queryRunner.query(conn, sql, handler, 3);
list.forEach(System.out::println);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}
/**
* ScalarHandler:用于查询特殊值
*/
@Test
public void query5() {
Connection conn = null;
try {
QueryRunner queryRunner=new QueryRunner();
conn = JdbcUtils.getConnection3();
String sql="select count(*) from account";
ScalarHandler handler = new ScalarHandler();
var count = queryRunner.query(conn, sql, handler);
System.out.println(count);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}
/**
* 自定义ResultSetHandler的实现类
*/
@Test
public void query6() {
Connection conn = null;
try {
QueryRunner queryRunner=new QueryRunner();
conn = JdbcUtils.getConnection3();
String sql="select id,username,balance from account where id=?";
//匿名类实现ResultSetHandler,查询一条记录
ResultSetHandler<Account> handler = new ResultSetHandler<>() {
@Override
public Account handle(ResultSet resultSet) throws SQLException {
if (resultSet.next()){
int id=resultSet.getInt("id");
String username=resultSet.getString("username");
Double balance=resultSet.getDouble("balance");
Account account = new Account(id, username, balance);
return account;
}
return null;
}
};
Account account = queryRunner.query(conn, sql, handler, 4);
System.out.println(account);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}
}