数据库增删改查工具类

本文介绍自己使用c3p0连接池封装的通用数据库增删改查的工具类，如有错误，请告知，谢谢！

1. 环境介绍

• JDK版本：jdk1.8.0_172
• 数据库：MySQL8.0.11
• 数据库连接池：c3p0-0.9.5
• 项目工程目录
  

2. c3p0连接池工具类

• 在pom.xml中引入依赖

<dependencies>
  	<!--mysql驱动包-->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>8.0.11</version>
      </dependency>
      <dependency>
      	<groupId>junit</groupId>
      	<artifactId>junit</artifactId>
      	<version>4.12</version>
      	<scope>compile</scope>
      </dependency>
	  <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.5</version>
      </dependency>
  </dependencies>

• c3p0-config.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xml>
<c3p0-config>
   <!--默认配置-->
    <default-config>  
        <property name="initialPoolSize">10</property>  
        <property name="maxIdleTime">30</property>  
        <property name="maxPoolSize">100</property>  
        <property name="minPoolSize">10</property>  
        <property name="maxStatements">200</property>  
    </default-config>  
  
   <!--配置连接池mysql-->
    <named-config name="mysql">  
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>  
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/exam?useSSL=false&amp;serverTimezone=Hongkong</property>  
        <property name="user">root</property>  
        <property name="password">db-password</property>  
    </named-config>  
    
</c3p0-config>

• C3P0Utils工具类java代码

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	
	private static DataSource dataSource = null;
	
	static {
		dataSource = new ComboPooledDataSource("mysql");
	}
	
	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void close(AutoCloseable... autoCloseables) {
		for(AutoCloseable ac : autoCloseables) {
			if(ac != null) {
				try {
					ac.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}

3. 增删改查工具类

• CRUDUtils工具类java代码

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
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 java.util.Properties;

/**
 1. 数据库增删改查工具类
 2. */
public class CRUDUtils {

	private static Connection conn = null;
	private static PreparedStatement ps = null;
	private static ResultSet rs = null;
	
	//insert, update, delete
	/**
	 * @Function commonUpdate
	 * @Description 插入，更新，删除
	 * @param sql 执行的SQL语句
	 * @param objects SQL语句中的字段值
	 */
	public static int commonUpdate(String sql, Object...objects) {
		conn = C3P0Utils.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			if(objects != null && objects.length > 0) {
				for(int i = 0; i < objects.length; i++) {
					ps.setObject(i+1, objects[i]);
				}
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			C3P0Utils.close(ps, conn);
		}
		return 0;
	}
	
	//selectOne
	/**
	 * @Function commonQueryOne
	 * @Description 查找单条记录
	 * @param sql 执行的SQL语句
	 * @param cls 实体类对象
	 * @param objects SQL语句中的限制条件
	 */
	public static <E> E commonQueryOne(String sql, Class<E> cls, Object...objects) {
		conn = C3P0Utils.getConnection();
		E entity = null;
		try {
			ps = conn.prepareStatement(sql);
			if(objects != null && objects.length > 0) {
				for(int i = 0; i < objects.length; i++) {
					ps.setObject(i+1, objects[i]);
				}
			}
			//获取结果集
			rs = ps.executeQuery();  
			
			/**
			 * 以下通过数据库表中字段去查找实体类中的属性名
			 */
			//获取结果集中对象的数量、列名等
			ResultSetMetaData rsmd = rs.getMetaData();  
			//获取字段数
			int columnCount = rsmd.getColumnCount();  
			while(rs.next()) {
				//ͨ通过反射获取实体类对象
				entity = cls.newInstance();  
				for(int i = 0; i < columnCount; i++) {
					//获取字段名称
					String columnName = rsmd.getColumnName(i+1); 
					//获取该字段对应的值ֵ
					Object columnValue = rs.getObject(columnName);  
					//通过字段名获取属性，try{名称不匹配}catch{到配置文件查找对应属性名}
					Field field = null;
					try{
						field = cls.getDeclaredField(columnName); 
					}catch (Exception e){
						Properties p = new Properties();
						String mappingFile = cls.getSimpleName() + "Mapping.properties";
						System.out.println(mappingFile);
						InputStream is = CRUDUtils.class.getClassLoader().getResourceAsStream(mappingFile);
						try{
							p.load(is);
							String fieldName = p.getProperty(columnName); //key=value -> user_name=username
							field = cls.getDeclaredField(fieldName);
						}catch(IOException ioe){
							ioe.printStackTrace();
						}
					}
					//将私有属性非可访问设置为可访问
					field.setAccessible(true);  
					//给实体类中的属性赋值ֵ
					field.set(entity, columnValue);  
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			C3P0Utils.close(rs, ps, conn);
		}
		return entity;
	}
	
	//selectAll
	/**
	 * @Function commonQueryList
	 * @Description 查找多条记录
	 * @param sql 执行的SQL语句
	 * @param cls 实体类对象
	 * @param objects SQL语句中的限制条件
	 */
	public static <E> List<E> commonQueryList(String sql,  Class<E> cls, Object...objects) {
		conn = C3P0Utils.getConnection();
		List<E> list = new ArrayList<E>();
		E entity = null;
		try {
			ps = conn.prepareStatement(sql);
			if(objects != null && objects.length > 0) {
				for(int i = 0; i < objects.length; i++) {
					ps.setObject(i+1, objects[i]);
				}
			}
			rs = ps.executeQuery();  
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while(rs.next()) {
				entity = cls.newInstance();
				for(int i = 0; i < columnCount; i++) {
					String columnName = rsmd.getColumnName(i+1);
					Object columnValue = rs.getObject(columnName);
					//通过字段名获取属性，try{名称不匹配}catch{到配置文件查找对应名称}
					Field field = null;
					try{
						field = cls.getDeclaredField(columnName); 
					}catch (Exception e){
						Properties p = new Properties();
						String mappingFile = cls.getSimpleName() + "Mapping.properties";
						InputStream is = CRUDUtils.class.getClassLoader().getResourceAsStream(mappingFile);
						try{
							p.load(is);
							String fieldName = p.getProperty(columnName); //key=value -> user_name=username
							field = cls.getDeclaredField(fieldName);
						}catch(IOException ioe){
							ioe.printStackTrace();
						}
					}
					field.setAccessible(true);
					field.set(entity, columnValue);
				}
				list.add(entity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			C3P0Utils.close(rs, ps, conn);
		}
		return list;
	}
}

• 说明：该工具类中查询从数据库表中查找获取结果集，通过结果集中字段名来查找*Mapping.properties（*在这里表示实体类名）文件映射到实体类属性名

4. 在dao层中使用

import java.util.List;

import com.cdc.dao.IUserDao;
import com.cdc.entity.User;
import com.cdc.utils.CRUDUtils;

public class UserDaoImpl implements IUserDao {

	@Override
	public int insert(User user) {
		String sql = "insert into t_user(name, password) values (?, ?)";
		int rs = CRUDUtils.commonUpdate(sql, user.getName(), user.getPassword());
		return rs;
	}

	@Override
	public int update(User user) {
		String sql = "update t_user set password=? where id=?";
		int rs = CRUDUtils.commonUpdate(sql, user.getPassword(), user.getId());
		return rs;
	}

	@Override
	public int delete(int id) {
		String sql = "delete from t_user where id=?";
		int rs = CRUDUtils.commonUpdate(sql, id);
		return rs;
	}

	@Override
	public User selectOne(User user) {
		String sql = "select * from t_user where id=?";
		User u = CRUDUtils.commonQueryOne(sql, User.class, user.getId());
		return u;
	}

	@Override
	public List<User> selectAll(User user) {
		String sql = "select * from t_user";
		List<User> list = CRUDUtils.commonQueryList(sql, User.class);
		return list;
	}

}