目录
3.3PreparedStatement实现表数据的修改操作
3.4PreparedStatement实现通用的增删改操作
3.7PreparedStatement针对不同表的通用查询操作1(查询单行数据)
3.8PreparedStatement针对不同表的通用查询操作2(查询多行数据)
4.4CustomerDAO及CustomerDAOImpl的实现
6.2使用QueryRunner查询表中一条或多条记录的操作
6.4自定义ResultSetHandler的实现类完成查询操作
1、JDBC概述
1.1JDBC程序编写步骤
2、获取数据库连接
2.1要素一:Driver接口实现类
2.2要素二:URL
2.3要素三:用户名和密码
2.4数据库连接方式举例
2.4.1连接方式一
package connection;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
import com.mysql.cj.jdbc.Driver;
public class ConnectionTest {
@Test
public void testConnection1() throws SQLException {
Driver driver=new com.mysql.cj.jdbc.Driver();
//jdbc:mysql:协议
//localhost:ip地址
//3306:默认mysql的端口
//test:test数据库
String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Wuhan";
/*将用户名和密码封装在Propertise中*/
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","root");
Connection conn=driver.connect(url, info);
System.out.println(conn);
}
}
输出结果:
2.4.2连接方式二
package connection;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
import com.mysql.cj.jdbc.Driver;
public class ConnectionTest {
//方式二,对方式一的迭代:在如下的程序中不出现第三方的api,使得程序具有更好的可移植性
@Test
public void testConnection2() throws ClassNotFoundException, InstantiationException, IllegalAccessException {
//获取Driver实现类对象,使用反射
Class clazz=Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver=(Driver)clazz.newInstance();
//2.提供要连接的数据库
String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Wuhan";
//3连接需要的用户名和密码
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","root");
//4获取连接
Connection conn=driver.connect(url, info);
System.out.println(conn);
}
}
2.4.3连接方式三
package connection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
import com.mysql.cj.jdbc.Driver;
public class ConnectionTest {
//方式三:使用DriverManager替换Driver
@Test
public void testConnection() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
//1.获取Driver实现类对象
Class clazz=Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver=(Driver)clazz.newInstance();
//2.提供另外三个连接的基本信息
String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Wuhan";
String user="root";
String password="root";
//注册驱动
DriverManager.registerDriver(driver);
//获取连接
Connection conn=DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
}
2.4.4连接方式四
//方式四:不用显示的注册驱动
@Test
public void testConnection() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
//1.获取Driver实现类对象
Class.forName("com.mysql.cj.jdbc.Driver");
//相比方法三省略了创建Driver实例和注册驱动操作,因为在Driver的实现类中已经帮我们注册好了驱动
//Driver driver=(Driver)Clazz.newInstance();
//2.提供另外三个连接的基本信息
String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Wuhan";
String user="root";
String password="root";
//注册驱动
//DriverManager.registerDriver(driver);
//获取连接
Connection conn=DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
2.4.5连接方式五(最终版)
package connection;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
import com.mysql.cj.jdbc.Driver;
public class ConnectionTest {
//方式五:将数据库连接需要的4个基本信息声明在配置文件中,通过读取配置文件的方式,获取连接
@Test
public void getConnection() throws IOException, ClassNotFoundException, SQLException {
//1.读取配置文件中的4个基本信息
InputStream is=ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(is);
String user=pros.getProperty("user");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driver=pros.getProperty("driver");
//2.加载驱动
Class.forName(driver);
//3.获取连接
Connection conn=DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
}
//其中,配置文件jdbc.properties:此配置文件声明在工程的src下
使用方式五的好处:
1、实现了数据与代码的分类,实现了解耦
2、如果需要修改配置文件信息,可以避免程序重新打包
3.使用PreparedStatement实现CRUD操作
3.1PreparedStatement实现表数据插入操作
package connection;
/*
* 使用PreparedStatement来替换Statement,实现对数据表的增删改查操作
*/
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;
import org.junit.Test;
import com.mysql.cj.jdbc.Driver;
public class PreparedStatementUpdate {
@Test
public void getConnection() {
//3.获取连接
Connection conn = null;
PreparedStatement ps = null;
try {
//1.读取配置文件中的4个基本信息
InputStream is=PreparedStatementUpdate.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(is);
String user=pros.getProperty("user");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driver=pros.getProperty("driver");
//2.加载驱动
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
//System.out.println(conn);
//4预编译sql语句,返回PreparedStatement的实例
String sql="insert into customers(name,email,birth)values(?,?,?)";
ps = conn.prepareStatement(sql);
//5填充占位符
ps.setString(1, "小舞");
ps.setString(2, "xiao@gmail.com");
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date=sdf.parse("1000-01-01");
ps.setDate(3, new Date(date.getTime()));
//6执行操作
ps.execute();
System.out.println("插入成功");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//7资源关闭
try {if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//关闭连接
try {if(ps!=null)
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//关闭PreparedStatement
}
}
}
3.2封装数据库连接
package connection.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import com.mysql.cj.xdevapi.Collection;
import connection.ConnectionTest;
/*
* 操作数据库的工具类
*/
public class JDBCUtils {
//获取数据库的连接
public static Connection getConnection() throws SQLException, ClassNotFoundException, IOException {
//1.读取配置文件中的4个基本信息
InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros=new Properties();
pros.load(is);
String user=pros.getProperty("user");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driver=pros.getProperty("driver");
//2.加载驱动
Class.forName(driver);
//3.获取连接
Connection conn=DriverManager.getConnection(url, user, password);
return conn;
}
//关闭连接和Statement的操作
public static void closeResource(Connection conn,Statement ps) {
//7资源关闭
try {if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//关闭连接
try {if(ps!=null)
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//关闭PreparedStatement
}
}
3.3PreparedStatement实现表数据的修改操作
package connection;
/*
*statement的弊端:1.存在拼串操作,繁琐2.存在sql注入问题3.无法操作Blob类型变量4.实现批量插入时效率低
* 使用PreparedStatement来替换Statement,实现对数据表的增删改查操作
*/
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;
import org.junit.Test;
import com.mysql.cj.jdbc.Driver;
import connection.util.JDBCUtils;
public class PreparedStatementUpdate {
private Connection conn;
private Statement ps;
//修改Customers表的一条记录
@Test
public void testupdate() {
try {
//1获取数据库的连接
Connection conn=JDBCUtils.getConnection();
//2预编译sql语句,返回PreparedStatement的实例
String sql="update customers set name=? where id=?";
PreparedStatement ps=conn.prepareStatement(sql);
//3填充占位符
ps.setString(1 ,"唐三");
ps.setObject(2, 18);
//4执行
ps.execute();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5资源的关闭
JDBCUtils.closeResource(conn, ps);
System.out.println("修改成功");
}
}
}
3.4PreparedStatement实现通用的增删改操作
package connection;
/*
* 使用PreparedStatement来替换Statement,实现对数据表的增删改查操作
*/
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;
import org.junit.Test;
import com.mysql.cj.jdbc.Driver;
import com.mysql.cj.x.protobuf.MysqlxDatatypes.Object;
import connection.util.JDBCUtils;
public class PreparedStatementUpdate {
@Test
public void testConUpdate() {
String sql="delete from costomers where id=? and name=?";
update(sql,20,"小舞");
//String sql="update `order` set order_name=? where order_id=?";
//update(sql,"DD","2");
}
//通用的增删改操作
public void update(String sql,Object...args) {//SQL中占位符的个数与可变参数的长度相同
Connection conn=null;
PreparedStatement ps=null;
try{//1获取数据库的连接
conn=JDBCUtils.getConnection();
//2预编译sql语句,返回PreparedStatement的实例
ps=conn.prepareStatement(sql);
//3填充占位符
for(int i=0;i<args.length;i++){
ps.setObject(i+1, args[i]);
}
//4执行
ps.execute();
}catch(Exception e) {
e.printStackTrace();
}finally{
//5关闭资源
JDBCUtils.closeResource(conn, ps);
}
}
}
3.5对Customers表的查询操作
package connection;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import connection.util.JDBCUtils;
import org.junit.Test;
/*
* 针对customers表的查询操作
*/
public class CustomerForQuery {
@Test
public void testquery() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultset = null;
try {
//1创建连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句
String sql="select id,name,email,birth from customers where id=?";
ps = conn.prepareStatement(sql);
//3填充占位符
ps.setObject(1,1);
//4执行并返回结果集
resultset = ps.executeQuery();
//5处理结果集
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};
//方式三:将数据封装成一个对象
Customers customer=new Customers(id,name,email,birth);
System.out.println(customer);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//关闭资源
JDBCUtils.closeResource(conn, ps, resultset);
}
}
}
Customers类:
package connection;
import java.sql.Date;
/*
* ORM编程思想(object relational mapping)
* 一个数据表对应一个java类
* 表中的一条记录对应java类的一个对象
* 表中的一个字段对应java类的一个属性
*/
public class Customers {
private int id;
private String name;
private String email;
private Date birth;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public Customers() {
super();
// TODO Auto-generated constructor stub
}
public Customers(int id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
@Override
public String toString() {
return "Customers [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
}
}
封装的关闭资源操作:
/*关闭资源操作*/
public static void closeResource(Connection conn,Statement ps,ResultSet rs) {
try {if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//关闭连接
try {if(ps!=null)
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//关闭PreparedrsStatement
try {
if(rs!=null) {
rs.close();
}
}catch(SQLException e){
e.printStackTrace();
}
3.6对Customers表的通用查询操作
package connection;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import connection.util.JDBCUtils;
import org.junit.Test;
import com.mysql.cj.result.Field;
/*
* 针对customers表的查询操作
*/
public class CustomerForQuery {
@Test
public void testQueryCustomers() {
String sql="select id,name,email,birth from costomers where id=?";
Customers customer=queryForCustomers(sql,13);
System.out.println(customer);
}
/*
* 针对Customers表的通用操作
*/
public Customers queryForCustomers(String sql,Object...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
//2.预编译sql语句
sql="select id,name,email,birth from customers where id=?";
ps = conn.prepareStatement(sql);
//3填充占位符
for(int i=0;i<args.length;i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的源数据:ResultSetMetaData
ResultSetMetaData rsmd=rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount=rsmd.getColumnCount();
if(rs.next()) {
Customers cust=new Customers();
//处理结果第一行数据中的每一列
for(int i=0;i<columnCount;i++) {
//获取列值
Object columValue=rs.getObject(i+1);
//getColumnName():获取每个列的列名
//getColumnLabel():获取表字段的别名,如果字段没有取别名则获取的就是列名
//当数据库中表的字段名(列名)和类中定义的变量名不同时,需要在sql语句中为字段名取一个和类中变
//量名相同的别名,然后通过getColumnLabel()方法获取到设置的别名才能使用反射的方式为类中指定变量名的变量设值
String columName=rsmd.getColumnName(i+1);
//给cust对象指定的columName属性赋值为columValue,通过反射实现
java.lang.reflect.Field field=Customers.class.getDeclaredField(columName);
field.setAccessible(true);
field.set(cust, columValue);
}
return cust;
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
}
3.7PreparedStatement针对不同表的通用查询操作1(查询单行数据)
package connection;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.junit.Test;
import connection.util.JDBCUtils;
/*
* 使用PreparedStatement实现针对不同表的通用的查询操作,返回表中的一条记录
*/
public class PreparedStatementQueryTest {
@Test
public void testGetInstance() throws InstantiationException {
String sql="select id,name,email from customers where id=?";
Customers customer=getInstance(Customers.class,sql,12);
System.out.println(customer);
}
public <T>T getInstance(Class<T> clazz,String sql,Object...args) throws InstantiationException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
//2.预编译sql语句
ps = conn.prepareStatement(sql);
//3填充占位符
for(int i=0;i<args.length;i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的源数据:ResultSetMetaData
ResultSetMetaData rsmd=rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount=rsmd.getColumnCount();
if(rs.next()) {
T t=clazz.newInstance();
//处理结果第一行数据中的每一列
for(int i=0;i<columnCount;i++) {
//获取列值
Object columValue=rs.getObject(i+1);
//获取每个列的列名
String columName=rsmd.getColumnName(i+1);
//给cust对象指定的columName属性赋值为columValue,通过反射实现
java.lang.reflect.Field field=clazz.getDeclaredField(columName);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
}
3.8PreparedStatement针对不同表的通用查询操作2(查询多行数据)
package connection;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import connection.util.JDBCUtils;
/*
* 使用PreparedStatement实现针对不同表的通用的查询操作,查询多行数据
*/
public class PreparedStatementQueryTest {
@Test
public void testGetForList() throws InstantiationException {
String sql="select id,name,email from customers where id<?";
List<Customers> list=getForList(Customers.class,sql,12);
list.forEach(System.out::println);
}
public <T> List<T> getForList(Class<T> clazz,String sql,Object...args) throws InstantiationException{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
//2.预编译sql语句
ps = conn.prepareStatement(sql);
//3填充占位符
for(int i=0;i<args.length;i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的源数据:ResultSetMetaData
ResultSetMetaData rsmd=rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount=rsmd.getColumnCount();
//创建集合对象
ArrayList<T> list=new ArrayList<T>();
while(rs.next()) {
T t=clazz.newInstance();
//处理结果第一行数据中的每一列:给t对象指定的属性赋值
for(int i=0;i<columnCount;i++) {
//获取列值
Object columValue=rs.getObject(i+1);
//获取每个列的列名
String columName=rsmd.getColumnName(i+1);
//给cust对象指定的columName属性赋值为columValue,通过反射实现
java.lang.reflect.Field field=clazz.getDeclaredField(columName);
field.setAccessible(true);
field.set(t, columValue);
}
list.add(t);
}
return list;
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
}
ps.execute():当执行的是增删改操作时反回值0,当时查询操作时查询到了值反回1否则反回0
ps.executeUpdate():对增删改操作,有几条数据被操作了反回几,没有就反回0
3.9向数据库表中插入Blob(二进制)类型数据
MediumBlob:一般用于存储图片
LongBlob:一般用于存储视频
package connection;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import connection.util.JDBCUtils;
/*
* 使用PreparedStatement操作Blob类型的数据
*/
public class BlobTest {
//向数据表customers中插入Blob类型的字段
@Test
public void testInsert() throws Exception{
//获取连接
Connection conn=JDBCUtils.getConnection();
//预编译sql语句
String sql="insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
//填充占位符
ps.setObject(1,"唐旧");
ps.setObject(2, "tangsan@qq.com");
ps.setObject(3, "1992-09-10");
FileInputStream is=new FileInputStream(new File("tup.jpg"));
ps.setBlob(4, is);
//执行
ps.execute();
//关闭资源
JDBCUtils.closeResource(conn, ps);
}
}
3.10从数据表中读取Blob类型数据
-
package connection; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; import com.mysql.cj.jdbc.Blob; import connection.util.JDBCUtils; /* * 使用PreparedStatement操作Blob类型的数据 */ public class BlobTest { //查询数据表customers中Blob类型的字段 @Test public void testQuery() { InputStream is = null; FileOutputStream fos=null; Connection conn = null; PreparedStatement ps=null; ResultSet rs=null; try { is = null; fos = null; conn = JDBCUtils.getConnection(); //预编译sql语句 String sql="select id,name,email,birth,photo from customers where id=?"; ps = conn.prepareStatement(sql); //填充占位符 ps.setObject(1, 16); rs = ps.executeQuery(); if(rs.next()) { //方式一: //int id=rs.getInt(1); //String name=rs.getString(2); //String email=rs.getString(3); //Date birth=rs.getDate(4); //方式二: int id=rs.getInt("id"); String name=rs.getString("name"); String email=rs.getString("email"); Date birth=rs.getDate("birth"); Customers cust=new Customers(id,name,email,birth); System.out.println(cust); java.sql.Blob photo=rs.getBlob("photo"); is=photo.getBinaryStream(); fos=new FileOutputStream("picture.jpg"); byte[] buffer=new byte[1024]; int len; while((len=is.read(buffer))!=-1) { fos.write(buffer,0,len); } } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { //关闭资源 try { is.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { fos.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } JDBCUtils.closeResource(conn, ps, rs); } } }
3.11插入Blob字段特殊情况说明
在存储图片时一般使用MediumBlob虽然允许存储16M以内的信息,但Packet限制要在1M以内,所以需要在MYSQL的my.ini文件中加上配置参数:max_allowed_packet=16M,同时注意:修改了my.ini文件之后,需要重新启动mysql服务
- Mysql8.0中my.ini文件的位置在:C:\ProgramData\MySQL\MYSQL Server 8.0
除了解决Statement的拼串,sql问题之外,PreparedStatement还有哪些好处呢?
1、PreparedStatement可以操作Blob类型的数据,而Statement做不到
2、PreparedStatement可以实现更高效的批量操作
3.12批量插入数据的操作
package connection;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import connection.util.JDBCUtils;
/*
* 使用PreparedStatement实现批量数据的操作
* update delete本身就具有批量操作的效果
* 此时的批量操作,主要指的是批量插入,使用PreparedStatement如何实现更高效的批量插入?
*
* 题目:向goods表中插入20000条数据
*
* 方式一:使用Statement
* Connection conn=JDBCUtils.getConnection();
* Statement st=conn.creatStatement();
* for(int i=1;i<20000;i++){
* String sql="insert into goods(name) values("name_"+i+"");
* st.execute(sql);
*/
public class InsertTest {
//批量插入的方式二
/*@Test
public void testInsert1() {
//获取连接
Connection conn = null;
PreparedStatement ps = null;
try {
long start=System.currentTimeMillis();
conn = JDBCUtils.getConnection();
//预编译sql语句
String sql="insert into goods(name) values(?)";
ps = conn.prepareStatement(sql);
//填充占位符
for(int i=1;i<=200;i++) {
ps.setObject(1,"name_"+i);
//执行
ps.execute();
}
long end=System.currentTimeMillis();
System.out.println("发费的时间为"+(end-start));//200条数据:17346毫秒
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
//关闭资源
JDBCUtils.closeResource(conn, ps);
}
}*/
//批量插入的方式三:
/*
* 1.addBatch()、executeBatch()、clearBatch()
* 2.mysql服务默认是关闭处理的,我们需要通过一个参数,让mysql开启批处理的支持:在配置文件的url中添加rewriteBatchedStatements=true
* ,成为:
//user=root
//password=root
//url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
//driver=com.mysql.cj.jdbc.Driver
* 3.
*/
/*@Test
public void testInsert1() {
//获取连接
Connection conn = null;
PreparedStatement ps = null;
try {
long start=System.currentTimeMillis();
conn = JDBCUtils.getConnection();
//预编译sql语句
String sql="insert into goods(name) values(?)";
ps = conn.prepareStatement(sql);
//填充占位符
for(int i=1;i<=200;i++) {
ps.setObject(1,"name_"+i);
//1.攒sql
ps.addBatch();
if(i%50==0) {
ps.executeBatch();//2.攒够50条数据再执行
//3.清空Batch
ps.clearBatch();
}
}
long end=System.currentTimeMillis();
System.out.println("发费的时间为"+(end-start));//200条数据:16478毫秒
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
//关闭资源
JDBCUtils.closeResource(conn, ps);
}
}*/
//批量插入的方式四:(最终版)设置连接不允许自动提交数据
/*
*
*/
@Test
public void testInsert1() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start=System.currentTimeMillis();
//获取连接
conn = JDBCUtils.getConnection();
//设置不允许自动提交数据
conn.setAutoCommit(false);
//预编译sql语句
String sql="insert into goods(name) values(?)";
ps = conn.prepareStatement(sql);
//填充占位符
for(int i=1;i<=200;i++) {
ps.setObject(1,"name_"+i);
//1.攒sql
ps.addBatch();
if(i%50==0) {
ps.executeBatch();//2.攒够50条数据再执行
//3.清空Batch
ps.clearBatch();
}
}
//提交数据
conn.commit();
long end=System.currentTimeMillis();
System.out.println("发费的时间为"+(end-start));//200条数据:1390毫秒
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
//关闭资源
JDBCUtils.closeResource(conn, ps);
}
}
}
4.事务
4.1考虑事务以后的代码实现
package com.atguigul.transaction;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import com.atguigul.util.JDBCUtils;
/*
* 1.什么叫数据库事务?
* 事务:一组逻辑操作单元,使数据从一种状态变换到例外一种状态
* 一组逻辑操作单元:一个或多个DML操作
*
* 2.事务处理的原则:保证所有事物都“被提交(commit)",那么这些修改就永久保存下来,
* 要么数据库管理系统将放弃所有的修改,整个事务“回滚(rollback)到最初状态”
*
* 3.数据一旦提交就不可回滚
*
* 4.哪些操作会导致数据的自动提交?
* DDL操作一旦执行,都会自动提交,set autocommit=false对DDL操作失效
* DML默认情况下,一旦执行,就会自动提交,我们可以通过set autocommit=false的方式取消DML操作的自动提交
* 默认在关闭连接时,会自动的提交数据
*/
public class TransactionTest {
/*
* 针对于数据表user_table来说
* AA用户给BB用户转账
* update user_table set balance=balance-100 where user="AA";
* update user_table set balance=balance+100 where user="BB";
*/
/*@Test
public void testUpdate() {
String sql1="update user_table set balance=balance-100 where user=?";
update(sql1,"AA");
//模拟网络异常
System.out.println(10/0);//A用户的balance减少100而B的balance未变
String sql2="update user_table set balance=balance+100 where user=?";
update(sql2,"BB");
System.out.println("转账成功");
}
//通用的增删改操作----version1.0
public int update(String sql,Object...args) {//SQL中占位符的个数与可变参数的长度相同
Connection conn=null;
PreparedStatement ps=null;
try{//1获取数据库的连接
conn=JDBCUtils.getConnection();
//2预编译sql语句,返回PreparedStatement的实例
ps=conn.prepareStatement(sql);
//3填充占位符
for(int i=0;i<args.length;i++){
ps.setObject(i+1, args[i]);
}
//4执行
return ps.executeUpdate();
}catch(Exception e) {
e.printStackTrace();
}finally{
//5关闭资源
JDBCUtils.closeResource(conn, ps);
}
return 0;
}*/
//-----------考虑数据库事务后的转账操作--------------
@Test
public void testUpdateWithTx() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
//1取消数据的自动提交功能
conn.setAutoCommit(false);
String sql1="update user_table set balance=balance-100 where user=?";
update(conn,sql1,"AA");
//模拟网络异常
System.out.println(10/0);//A用户和B用户的balance都未变
String sql2="update user_table set balance=balance+100 where user=?";
update(conn,sql2,"BB");
System.out.println("转账成功");
//2提交数据
conn.commit();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
//3回滚数据
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
//3回滚数据
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
//3回滚数据
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally {
JDBCUtils.closeResource(conn, null);
}
}
//通用的增删改操作----version2.0(考虑上事务)
public int update(Connection conn,String sql,Object...args) {//SQL中占位符的个数与可变参数的长度相同
PreparedStatement ps=null;
try{
//1预编译sql语句,返回PreparedStatement的实例
ps=conn.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;
}
}
4.2JAVA代码演示并设置数据库的隔离级别
package com.atguigul.transaction;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.atguigul.util.JDBCUtils;
/*
* 1.什么叫数据库事务?
* 事务:一组逻辑操作单元,使数据从一种状态变换到例外一种状态
* 一组逻辑操作单元:一个或多个DML操作
*
* 2.事务处理的原则:保证所有事物都“被提交(commit)",那么这些修改就永久保存下来,
* 要么数据库管理系统将放弃所有的修改,整个事务“回滚(rollback)到最初状态”
*
* 3.数据一旦提交就不可回滚
*
* 4.哪些操作会导致数据的自动提交?
* DDL操作一旦执行,都会自动提交,set autocommit=false对DDL操作失效
* DML默认情况下,一旦执行,就会自动提交,我们可以通过set autocommit=false的方式取消DML操作的自动提交
* 默认在关闭连接时,会自动的提交数据
*/
public class TransactionTest {
//-------------------------------------------------
@Test
public void testTransactionSelect()throws Exception {
Connection conn=JDBCUtils.getConnection();
//获取当前连接的隔离级别
System.out.println(conn.getTransactionIsolation());
//设置数据库的隔离级别
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
//取消自动提交数据
conn.setAutoCommit(false);
String sql="select user,password,balance from user_table where user=?";
User user=getInstance(conn,User.class,sql,"CC");
System.out.println(user);
}
@Test
public void testTransactionUpdate()throws Exception {
Connection conn=JDBCUtils.getConnection();
//取消自动提交数据
conn.setAutoCommit(false);
String sql="update user_table set balance=? where user=?";
update(conn,sql,5000,"CC");
Thread.sleep(15000);
System.out.println("修改结束");
}
//通用的查询操作,用于反回数据表中的一条记录(version2.0,考虑上事务)
public <T>T getInstance(Connection conn,Class<T> clazz,String sql,Object...args) throws InstantiationException {
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
//2.预编译sql语句
ps = conn.prepareStatement(sql);
//3填充占位符
for(int i=0;i<args.length;i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的源数据:ResultSetMetaData
ResultSetMetaData rsmd=rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount=rsmd.getColumnCount();
if(rs.next()) {
T t=clazz.newInstance();
//处理结果第一行数据中的每一列
for(int i=0;i<columnCount;i++) {
//获取列值
Object columValue=rs.getObject(i+1);
//获取每个列的列名
String columName=rsmd.getColumnName(i+1);
//给cust对象指定的columName属性赋值为columValue,通过反射实现
java.lang.reflect.Field field=clazz.getDeclaredField(columName);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
//通用的增删改操作(考虑上事务)
public void update(Connection conn,String sql,Object...args) {//SQL中占位符的个数与可变参数的长度相同
PreparedStatement ps=null;
try{
//1预编译sql语句,返回PreparedStatement的实例
ps=conn.prepareStatement(sql);
//2填充占位符
for(int i=0;i<args.length;i++){
ps.setObject(i+1, args[i]);
}
//3执行
ps.execute();
}catch(Exception e) {
e.printStackTrace();
}finally{
//4关闭资源
JDBCUtils.closeResource(null, ps);
}
return 0;
}
}
4.3提供操作数据表的BaseDAO
package com.atguigu2.dao;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.atguigul.util.JDBCUtils;
//import com.mysql.cj.x.protobuf.MysqlxDatatypes.Object;
/*
* 封装了针对于数据表的通用操作
*/
public abstract class BaseDao {//设置为抽象类,表示不能创建它的对象
//通用的增删改操作---version2.0(考虑上事务)
public int update(Connection conn,String sql,Object...args) {//SQL中占位符的个数与可变参数的长度相同
PreparedStatement ps=null;
try{//1获取数据库的连接
conn=JDBCUtils.getConnection();
//2预编译sql语句,返回PreparedStatement的实例
ps=conn.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(null, ps);
}
return 0;
}
//通用的查询操作,用于反回数据表中的一条记录(version2.0,考虑上事务)
public <T>T getInstance(Connection conn,Class<T> clazz,String sql,Object...args) throws InstantiationException {
PreparedStatement ps = null;
ResultSet rs = null;
try {
//conn = JDBCUtils.getConnection();
//2.预编译sql语句
ps = conn.prepareStatement(sql);
//3填充占位符
for(int i=0;i<args.length;i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的源数据:ResultSetMetaData
ResultSetMetaData rsmd=rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount=rsmd.getColumnCount();
if(rs.next()) {
T t=clazz.newInstance();
//处理结果第一行数据中的每一列
for(int i=0;i<columnCount;i++) {
//获取列值
Object columValue=rs.getObject(i+1);
//获取每个列的列名
String columName=rsmd.getColumnName(i+1);
//给cust对象指定的columName属性赋值为columValue,通过反射实现
java.lang.reflect.Field field=clazz.getDeclaredField(columName);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
//通用的查询操作,用于反回数据表中的多条记录构成的集合(version2.0,考虑上事务)
public <T> List<T> getForList(Connection conn,Class<T> clazz,String sql,Object...args) throws InstantiationException{
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.预编译sql语句
ps = conn.prepareStatement(sql);
//2填充占位符
for(int i=0;i<args.length;i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//3获取结果集的源数据:ResultSetMetaData
ResultSetMetaData rsmd=rs.getMetaData();
//通过ResultSetMetaData获取结果集中的列数
int columnCount=rsmd.getColumnCount();
//创建集合对象
ArrayList<T> list=new ArrayList<T>();
while(rs.next()) {
T t=clazz.newInstance();
//处理结果第一行数据中的每一列:给t对象指定的属性赋值
for(int i=0;i<columnCount;i++) {
//获取列值
Object columValue=rs.getObject(i+1);
//获取每个列的列名
String columName=rsmd.getColumnName(i+1);
//给cust对象指定的columName属性赋值为columValue,通过反射实现
java.lang.reflect.Field field=clazz.getDeclaredField(columName);
field.setAccessible(true);
field.set(t, columValue);
}
list.add(t);
}
return list;
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
//用于查询特殊值的通用方法
public <E>E getValue(Connection conn,String sql,Object...args) throws Exception{
PreparedStatement ps=null;
ResultSet rs=null;
try{
ps=conn.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 e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null, ps,rs);
}
return null;
}
}
4.4CustomerDAO及CustomerDAOImpl的实现
Customers类:
package com.atguigu2.bean;
import java.sql.Date;
/*
* ORM编程思想(object relational mapping)
* 一个数据表对应一个java类
* 表中的一条记录对应java类的一个对象
* 表中的一个字段对应java类的一个属性
*/
public class Customers {
private int id;
private String name;
private String email;
private Date birth;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public Customers() {
super();
// TODO Auto-generated constructor stub
}
public Customers(int id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
@Override
public String toString() {
return "Customers [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
}
}
CustomerDAO:
package com.atguigu2.dao;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
import com.atguigu2.bean.Customers;
/*
* 此接口用于规范针对于Customers表的常用操作
*/
public interface CustomerDAO {
//将cust对象添加到数据库中
void insert(Connection conn,Customers cust);
//根据指定的id,删除表中的记录
void deleteById(Connection conn,int id);
//针对内存中的cust对象,去修改数据表中指定的记录
void updateById(Connection conn,Customers cust);
//针对指定的id查询得到对应的customers对象
Customers getCustomersById(Connection conn,int id);
//查询表中所有记录构成的集合
List<Customers> getAll(Connection conn);
//反回数据表中数据的条目数
Long getCount(Connection conn);
//反回数据表中最大的生日
Date getMaxBirth(Connection conn);
}
CustomersDAOImpl:
package com.atguigu2.dao;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
import com.atguigu2.bean.Customers;
public class CustomersDAOImpl extends BaseDao implements CustomerDAO{
@Override
public void insert(Connection conn, Customers cust) {
String sql="insert into customers(name,email,birth)values(?,?,?)";
update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql="delete from customers where id=?";
update(conn,sql,id);
}
@Override
public void updateById(Connection conn, Customers cust) {
String sql="update customers set name=?,email=?,birth=? where id=?";
update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
}
@Override
public Customers getCustomersById(Connection conn, int id){
Customers customer=null;
String sql="select id,name,email,birth from customers where id=?";
try {
customer=getInstance(conn,Customers.class,sql,id);
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return customer;
}
@Override
public List<Customers> getAll(Connection conn) {
String sql="select id,name,email,birth from customers";
List<Customers> list = null;
try {
list = getForList(conn,Customers.class,sql);
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@Override
public Long getCount(Connection conn) {
String sql="select count(*) from customers";
long value = 0;
try {
value = getValue(conn,sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return value;
}
@Override
public Date getMaxBirth(Connection conn) {
String sql="select max(birth) from costomers";
Date birth = null;
try {
birth = getValue(conn,sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return birth;
}
}
4.5CustomerDAOImpl的单元测试
package com.atguigu2.dao.junit;
import static org.junit.Assert.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
import org.junit.Test;
import com.atguigu2.bean.Customers;
import com.atguigu2.dao.CustomersDAOImpl;
import com.atguigul.util.JDBCUtils;
public class CustomerDAOImplTest {
private CustomersDAOImpl dao=new CustomersDAOImpl();
@Test
public void testInsert() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Customers cust=new Customers(1,"于小飞","xiaofei@126.com",new Date(34555));
dao.insert(conn, cust);
JDBCUtils.closeResource(conn, null);
}
@Test
public void testDeleteById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
dao.deleteById(conn,20);
System.out.println("删除成功");
}catch(Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testUpdateById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customers cust=new Customers(19,"贝多芬","beiduofen@126.con",new Date(55886));
dao.updateById(conn,cust);
System.out.println("修改成功");
}catch(Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetCustomersById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customers cust=dao.getCustomersById(conn, 13);
System.out.println(cust);
}catch(Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetAll() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
List<Customers> list=dao.getAll(conn);
list.forEach(System.out::println);
}catch(Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetCount() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Long count=dao.getCount(conn);
System.out.println("表中的记录数为"+count);
}catch(Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetMaxBirth() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Date maxBirth=dao.getMaxBirth(conn);
System.out.println("最大的生日为"+maxBirth);
}catch(Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
}
4.6升级以后的DAO及相关类的测试
5数据库连接池
5.1C3P0数据库连接池的两种实现方式
记得先导入c3p0-0.9.1.2.jar
C3P0 是一个开源组织提供的一个数据库连接池, 速度相对较慢,稳定性还可以。 hibernate 官方推荐使用
package com.atguigu4.connection;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;
public class C3P0Test {
//方式一:
/*@Test
public void testGetConnection()throws Exception {
//获取C3P0数据库连接池
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true" );
cpds.setUser("root");
cpds.setPassword("root");
//通过设置相关的参数,对连接池进行管理
//设置初始时数据库连接池中的连接数
cpds.setInitialPoolSize(10);
Connection conn=cpds.getConnection();
System.out.println(conn);
//销毁C3P0数据库连接池,一般不会做这个操作
//DataSources.destroy( cpds );
}*/
//方式二:使用配置文件
@Test
public void testGetConnection()throws SQLException {
//获取配置文件名为hellc3p0文件中的信息
ComboPooledDataSource cpds = new ComboPooledDataSource("hellc3p0");
Connection conn=cpds.getConnection();
System.out.println(conn);
}
}
配置文件c3p0-config.xml:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="hellc3p0">
<!-- 提供获取连接的四个基本信息 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 进行数据库连接池管理的基本信息 -->
<!-- 当数据库连接池中的连接数不够时,c3p0一次性向数据库服务器申请的连接数 -->
<property name="acquireIncrement">5</property>
<!-- c3p0数据库连接池中初始化时的连接数 -->
<property name="initialPoolSize">10</property>
<!-- c3p0数据库连接池维护的最少连接数 -->
<property name="minPoolSize">10</property>
<!-- c3p0数据库连接池维护的最多连接数 -->
<property name="maxPoolSize">100</property>
<!-- c3p0数据库连接池最多维护的statement的个数 -->
<property name="maxStatements">50</property>
<!-- 每个连接中可以最多使用的statement的个数 -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>
5.2JDBCUtils中使用c3p0数据库连接池获取连接
package com.atguigu4.util;
import java.sql.Connection;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCUtils {
/*
* 使用c3p0的数据库连接池技术
*/
//cpds放在方法外面,防止每次调用方法获取连接都造了一个池子,池子只用造一个就行
private static ComboPooledDataSource cpds = new ComboPooledDataSource("hellc3p0");
public static Connection getConnection1() throws SQLException{
Connection conn=cpds.getConnection();
return conn;
}
}
5.3DBCP数据库连接池的两种实现方式
DBCP 是 Apache 提供的数据库连接池。 tomcat 服务器自带 dbcp 数据库连接池。 速度相对 c3p0 较快 ,但因自身存在 BUG , Hibernate3 已不再提供支持。
两种实现方式:记得先导入commons-dbcp-1.4.jar和commons-pool-1.5.5.jar
package com.atguigu4.connection;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
public class DBCPTest {
/*
* 测试DBCP的数据库连接池技术
*/
//方式一:不推荐
/*@Test
public void testGetConnection() throws Exception{
//创建了DBCP数据库连接池
BasicDataSource source=new BasicDataSource();
//设置基本信息
source.setDriverClassName("com.mysql.cj.jdbc.Driver");
source.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true");
source.setUsername("root");
source.setPassword("root");
//还可以设置其它涉及数据库连接池管理的相关属性
source.setInitialSize(10);
source.setMaxActive(10);
Connection conn=source.getConnection();
System.out.println(conn);
}*/
//方式二:使用配置文件
@Test
public void testGetConnection1() throws Exception{
Properties pros=new Properties();
//方式一:
//InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
//方式二:
FileInputStream is=new FileInputStream(new File("src/dbcp.properties"));
pros.load(is);
DataSource source=BasicDataSourceFactory.createDataSource(pros);
Connection conn=source.getConnection();
System.out.println(conn);
}
}
dbcp.properties配置文件:
user=root
password=root
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
driver=com.mysql.cj.jdbc.Driver
JDBCUtils中使用dbcp数据库连接池获取连接:
package com.atguigu4.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCUtils {
private static DataSource source;
static {//静态代码块随着类的加载而加载,因此只会加载一次,创建一个连接池
try {
Properties pros=new Properties();
//方式一:
//InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
//方式二:
FileInputStream is=new FileInputStream(new File("src/dbcp.properties"));
pros.load(is);
//创建一个dbcp数据库连接池
DataSource source=BasicDataSourceFactory.createDataSource(pros);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection2()throws Exception {//通过调用静态方法获取连接
Connection conn=source.getConnection();
return conn;
}
}
5.4Druid数据库连接池技术的实现(★)
Druid 是阿里提供的数据库连接池,据说是集 DBCP 、 C3P0 、 Proxool 优点于一身的数据库连接池,但是速度不确定是否有 BoneCP 快
实现方式:记得要先导入druid-1.1.10.jar
package com.atguigu4.connection;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;
import org.junit.Test;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidTest{
@Test
public void getConnection() throws Exception{
Properties pros=new Properties();
InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
DataSource source=DruidDataSourceFactory.createDataSource(pros);
Connection conn=source.getConnection();
System.out.println(conn);
}
}
druid.properties配置文件:
username=root
password=root
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
driverClassName=com.mysql.cj.jdbc.Driver
initialSize=10
maxActive=10
JDBCUtils中使用druid数据库连接池获取连接:
package com.atguigu4.util;
import java.io.File;
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;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCUtils {
/*
* 使用druid数据库连接池技术
*/
private static DataSource source;
static {try {
//静态代码块随着类的加载而加载,因此只会加载一次,创建一个连接池
Properties pros=new Properties();
//方式一:
InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
//方式二:
//FileInputStream is=new FileInputStream(new File("dbcp.properties"));
pros.load(is);
//创建一个druid数据库连接池
DataSource source=DruidDataSourceFactory.createDataSource(pros);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection2()throws Exception {
Connection conn=source.getConnection();//获取连接
return conn;
}
}
6.Apache-DBUtils实现CRUD操作
6.1QueryRunner测试添加数据的操作
先导入commons-dbutils-1.3jar才能够使用
package com.atguigu5.dbutils;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import com.atguigul.util.JDBCUtils;
/*
* commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,封装了针对于数据库的增删改查操作
*/
public class QueryRunnerTest {
@Test
public void testInsert() {
Connection conn = null;
try {
QueryRunner runner=new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="insert into customers(name,email,birth)values(?,?,?)";
int insertCount=runner.update(conn, sql, "蔡徐坤","caixukun@129.com","1997-09-08");
System.out.println("添加了"+insertCount+"条记录");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
}
6.2使用QueryRunner查询表中一条或多条记录的操作
package com.atguigu5.dbutils;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;
import com.atguigu2.bean.Customers;
import com.atguigul.util.JDBCUtils;
/*
* commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,封装了针对于数据库的增删改查操作
*/
public class QueryRunnerTest {
//测试插入
/*@Test
public void testInsert() {
Connection conn = null;
try {
QueryRunner runner=new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="insert into customers(name,email,birth)values(?,?,?)";
int insertCount=runner.update(conn, sql, "蔡徐坤","caixukun@129.com","1997-09-08");
System.out.println("添加了"+insertCount+"条记录");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}*/
//测试查询
/*
* BeanHandler:是ResultSetHandler接口的实现类,用于封装表中的一条记录
*/
/*@Test
public void testQuery1() {
Connection conn = null;
try {
QueryRunner runner=new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth from customers where id=?";
BeanHandler<Customers> handler=new BeanHandler<>(Customers.class);
Customers customer=runner.query(conn, sql, handler, 22);
System.out.println(customer);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}*/
/*
* BeanListHandler:是ResultSetHandler接口的实现类,用于封装表中的多条记录构成的集合
*/
@Test
public void testQuery2() {
Connection conn = null;
try {
QueryRunner runner=new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth from customers where id<?";
BeanListHandler<Customers> handler=new BeanListHandler<>(Customers.class);
List<Customers> list=runner.query(conn, sql, handler, 22);
list.forEach(System.out::println);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
/*
* MapHandler:是ResultSetHandler接口的实现类,对应表中的一条记录
* 将字段及相应字段的值作为map中key和value
*/
@Test
public void testQuery3() {
Connection conn = null;
try {
QueryRunner runner=new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth from customers where id=?";
MapHandler handler=new MapHandler();
Map<String,Object> map=runner.query(conn, sql, handler, 22);
System.out.println(map);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
/*
* MapListHandler:是ResultSetHandler接口的实现类,对应表中的多条记录
* 将字段及相应字段的值作为map中key和value,将这些map添加到list中
*/
@Test
public void testQuery4() {
Connection conn = null;
try {
QueryRunner runner=new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth from customers where id<?";
MapListHandler handler=new MapListHandler();
List<Map<String,Object>> list=runner.query(conn, sql, handler, 22);
list.forEach(System.out::println);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
}
6.3QueryRunner查询表中特殊值的操作
package com.atguigu5.dbutils;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import com.atguigu2.bean.Customers;
import com.atguigul.util.JDBCUtils;
/*
* commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,封装了针对于数据库的增删改查操作
*/
public class QueryRunnerTest {
/*
* ScalarHandler:用于查询特殊值
*/
@Test
public void testQuery5() {
Connection conn = null;
try {
QueryRunner runner=new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select count(*) from customers";
ScalarHandler handler=new ScalarHandler();
Long count=(Long)runner.query(conn, sql, handler);
System.out.println(count);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
}
6.4自定义ResultSetHandler的实现类完成查询操作
package com.atguigu5.dbutils;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import com.atguigu2.bean.Customers;
import com.atguigul.util.JDBCUtils;
public class QueryRunnerTest {
/*
* ResultSetHandler的实现类
*/
@Test
public void testQuery6() {
Connection conn = null;
try {
QueryRunner runner=new QueryRunner();
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth from customers where id=?";
ResultSetHandler<Customers> handler=new ResultSetHandler<Customers>(){
@Override
public Customers handle(ResultSet rs) throws SQLException {
//用于实现自己想要的操作
return null;
}
};
Customers customer=runner.query(conn, sql, handler,23);
System.out.println(customer);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, null);
}
}
}
6.5DbUtils类关闭资源的操作
/*
* 使用dbutils.jar中提供的dbutils工具类,实现资源的关闭
*/
public static void closeResource1(Connection conn,Statement ps,ResultSet rs) {
try {
DbUtils.close(conn);
DbUtils.close(rs);
DbUtils.close(ps);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}