JDBC增删改查示例
数据库表
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由外部传入,在关闭时候也由外部关闭,不再次方法关闭connpublic 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由外部传入,在关闭时候也由外部关闭,不再次方法关闭connpublic 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由外部传入,在关闭时候也由外部关闭,不再次方法关闭connpublic 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 xxxpublic 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(*)返回LongLong getCount(Connection conn);Date getMaxBirth(Connection conn);
}接口实现类
public class CustomerDaoImpl extends DBUtils2 implements CustomerDao{@Overridepublic 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;}@Overridepublic int deleteCustomerById(Connection conn, Integer id) {String sql="delete from customers where id=?";int count = update(conn, sql, id);return count;}@Overridepublic 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;}@Overridepublic Customers selectCustomerById(Connection conn, Integer id) {String sql="select * from customers where id=?";Customers customers = selectOne(conn, Customers.class, sql, id);return customers;}@Overridepublic List<Customers> selectAllCustomers(Connection conn) {String sql="select * from customers";List<Customers> list = selectList(conn, Customers.class, sql);return list;}@Overridepublic Long getCount(Connection conn) {String sql="select count(*) from customers";return getValue(conn, sql);}@Overridepublic 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();@Testpublic 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);}}@Testpublic 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);}};@Testpublic 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);}};@Testpublic 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);}};@Testpublic 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(*)返回Longpublic 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);}};@Testpublic 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);}};
}