数据库表
CREATE TABLE `customers` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(15) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`birth` date DEFAULT NULL,
`photo` mediumblob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=gb2312;
引入的依赖 下图是为了快速将inputStream转byte[]引入的一个依赖
没有使用连接池
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.30</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13.2</version> <scope>test</scope> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.11.0</version> <!-- 使用最新版本 --> </dependency>
封装的工具类,这个工具类如果说想使用泛型 需要把除了closed和getConnection的其他几个方法的静态去掉
package com.utils;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
/**
* @author hrui
* @date 2023/10/16 8:45
*/
//public class DBUtils2<T> {
public class DBUtils2 {
private static ResourceBundle bundle=ResourceBundle.getBundle("jdbc");
private static String driver=bundle.getString("jdbc.driver");
private static String url=bundle.getString("jdbc.url");
private static String username=bundle.getString("jdbc.username");
private static String password=bundle.getString("jdbc.password");
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// private Class<T> clazz;
//
// {
// //获取这个类带泛型的父类DBUtils2<某个类>
// Type genericSuperclass = this.getClass().getGenericSuperclass();
// ParameterizedType parameterizedType=(ParameterizedType)genericSuperclass;
// //获取父类泛型
// Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
// clazz=(Class<T>)actualTypeArguments[0];
// }
//通用查询多个 添加事务后conn由外部传入,在关闭时候也由外部关闭,不再次方法关闭conn
public static <T> List<T> selectList(Connection conn,Class<T> clazz, String sql, Object...args){
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();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
List<T> list=new ArrayList<>();
while(rs.next()){
T t = clazz.newInstance();
for(int i=0;i<columnCount;i++){
Object object = rs.getObject(i + 1);
//String columnName = metaData.getColumnName(i + 1); 这个方法返回实际列名
String columnLabel = metaData.getColumnLabel(i + 1);//该方法返回别名,没有别名就返回列名
columnLabel = getString(columnLabel);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,object);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils.closed(null,ps,rs);
}
return null;
}
private static String getString(String columnLabel) {
if (columnLabel.contains("_")) {
StringBuilder result = new StringBuilder();
boolean convertNextCharToUpperCase = false;
for (char c : columnLabel.toCharArray()) {
if (c == '_') {
convertNextCharToUpperCase = true;
} else {
if (convertNextCharToUpperCase) {
result.append(Character.toUpperCase(c));
convertNextCharToUpperCase = false;
} else {
result.append(c);
}
}
}
}
return columnLabel;
}
//通用查询单个 添加事务后conn由外部传入,在关闭时候也由外部关闭,不再次方法关闭conn
public static <T> T selectOne(Connection conn,Class<T> clazz,String sql,Object...args){
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();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
if(rs.next()){
T t = clazz.newInstance();
for(int i=0;i<columnCount;i++){
Object object = rs.getObject(i + 1);
//System.out.println(object.getClass());
String columnLabel = metaData.getColumnLabel(i + 1);
columnLabel = getString(columnLabel);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,object);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils.closed(null,ps,rs);
}
return null;
}
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url, username, password);
return connection;
}
//通用增删改方法 添加事务后conn由外部传入,在关闭时候也由外部关闭,不再次方法关闭conn
public static int update(Connection conn,String sql,Object...args){
PreparedStatement ps=null;
int count=0;
try {
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
ps.setObject(i+1, args[i]);
}
count = ps.executeUpdate();
//ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closed(null,ps,null);
}
return count;
}
//一些特殊查询封装的方法,例如select count(*) from xxx
public static <E> E getValue(Connection conn,String sql,Object...args){
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 {
DBUtils2.closed(null,ps,rs );
}
return null;
}
public static void closed(Connection conn, Statement st, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
接口
public interface CustomerDao {
int insertCustomer(Connection conn, Customers cust);
int deleteCustomerById(Connection conn,Integer id);
int updateCustomer(Connection conn,Customers cust);
Customers selectCustomerById(Connection conn,Integer id);
List<Customers> selectAllCustomers(Connection conn);
//查询表中由多少条数据 count(*)返回Long
Long getCount(Connection conn);
Date getMaxBirth(Connection conn);
}
接口实现类
public class CustomerDaoImpl extends DBUtils2 implements CustomerDao{
@Override
public int insertCustomer(Connection conn, Customers cust) {
String sql="insert into customers(name,email,birth,photo)value(?,?,?,?)";
int count = update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getPhoto());
return count;
}
@Override
public int deleteCustomerById(Connection conn, Integer id) {
String sql="delete from customers where id=?";
int count = update(conn, sql, id);
return count;
}
@Override
public int updateCustomer(Connection conn, Customers cust) {
String sql="update customers set name=?,email=?,birth=?,photo=? where id=?";
int count = update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getPhoto(),cust.getId());
return count;
}
@Override
public Customers selectCustomerById(Connection conn, Integer id) {
String sql="select * from customers where id=?";
Customers customers = selectOne(conn, Customers.class, sql, id);
return customers;
}
@Override
public List<Customers> selectAllCustomers(Connection conn) {
String sql="select * from customers";
List<Customers> list = selectList(conn, Customers.class, sql);
return list;
}
@Override
public Long getCount(Connection conn) {
String sql="select count(*) from customers";
return getValue(conn, sql);
}
@Override
public Date getMaxBirth(Connection conn) {
String sql="select max(birth) from customers";
return getValue(conn, sql);
}
}
测试类
package com.utils;
import org.apache.commons.io.IOUtils;
import org.junit.jupiter.api.Test;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
/**
* @author hrui
* @date 2023/10/16 16:00
*/
public class CustomterDaoImplTest {
private CustomerDao customerDao=new CustomerDaoImpl();
@Test
public void insertCustomerTest(){
Connection conn=null;
try {
conn = DBUtils2.getConnection();
InputStream inputStream = CustomterDaoImplTest.class.getClassLoader().getResourceAsStream("123.jpg");
byte[] imageBytes = IOUtils.toByteArray(inputStream);//这个需要引入commons-io
// 关闭输入流
inputStream.close();
Customers cust=new Customers(null,"小白","xiaobai@163.com",new Date(),imageBytes);
int i = customerDao.insertCustomer(conn, cust);
System.out.println(i==1?"新增成功":"新增失败");
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils2.closed(conn,null,null);
}
}
@Test
public void deleteCustomerByIdTest(){
Connection conn=null;
try {
conn = DBUtils2.getConnection();
int i = customerDao.deleteCustomerById(conn, 1);
System.out.println(i==1?"删除成功":"删除失败");
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils2.closed(conn,null,null);
}
};
@Test
public void updateCustomer(){
Connection conn=null;
try {
conn = DBUtils2.getConnection();
InputStream inputStream = CustomterDaoImplTest.class.getClassLoader().getResourceAsStream("123.jpg");
byte[] imageBytes = IOUtils.toByteArray(inputStream);//这个需要引入commons-io
// 关闭输入流
inputStream.close();
Customers cust=new Customers(2,"王菲菲","feifei@666.com",new Date(),imageBytes);
int i = customerDao.updateCustomer(conn,cust);
System.out.println(i==1?"更新成功":"更新失败");
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils2.closed(conn,null,null);
}
};
@Test
public void selectCustomerById(){
Connection conn=null;
try {
conn = DBUtils2.getConnection();
Customers customers = customerDao.selectCustomerById(conn, 2);
System.out.println(customers);
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils2.closed(conn,null,null);
}
};
@Test
public void selectAllCustomersTest(){
Connection conn=null;
try {
conn = DBUtils2.getConnection();
List<Customers> customers = customerDao.selectAllCustomers(conn);
System.out.println(customers);
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils2.closed(conn,null,null);
}
};
@Test
//查询表中由多少条数据 count(*)返回Long
public void getCount(){
Connection conn=null;
try {
conn = DBUtils2.getConnection();
Long count = customerDao.getCount(conn);
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils2.closed(conn,null,null);
}
};
@Test
public void getMaxBirth(){
Connection conn=null;
try {
conn = DBUtils2.getConnection();
Date maxBirth = customerDao.getMaxBirth(conn);
System.out.println(maxBirth);
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils2.closed(conn,null,null);
}
};
}