Java 使用 C3P0 链接池操作 MySQL8

Java 使用 C3P0 链接池操作 MySQL8

pom 文件配置

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.5</version>
</dependency>

C3P0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test?useSSL=false&amp;serverTimezone=Hongkong&amp;characterEncoding=utf-8&amp;autoReconnect=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">10</property>
        <property name="maxPoolSize">20</property>
    </default-config>

</c3p0-config>

C3p0Util 工具类

@Slf4j
public class C3p0Util {

	//通过标识名来创建相应连接池
	static ComboPooledDataSource dataSource = new ComboPooledDataSource();

	//从连接池中取用一个连接
	public static Connection getConnection() throws Exception {
		try {
			return dataSource.getConnection();

		} catch (Exception e) {
			log.error("Exception in C3p0Utils!", e);
			throw new Exception("Exception in C3p0Utils!", e);
		}
	}

	//释放连接回连接池
	public static void close(Connection conn, PreparedStatement pst, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				log.error("Exception in C3p0Utils!", e);
			}
		}
		if (pst != null) {
			try {
				pst.close();
			} catch (SQLException e) {
				log.error("Exception in C3p0Utils!", e);
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				log.error("Exception in C3p0Utils!", e);
			}
		}
	}
}

CURDUtil

public class CRUDUtil {
	private static Connection conn = null;
	private static PreparedStatement ps = null;
	private static ResultSet rs = null;

	//insert, update, delete

	/**
	 * @param sql     执行的SQL语句
	 * @param objects SQL语句中的字段值
	 * @Function commonUpdate
	 * @Description 插入，更新，删除
	 */
	public static int commonUpdate(String sql, Object... objects) throws Exception {
		conn = C3p0Util.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 {
			C3p0Util.close(conn, ps, null);
		}
		return 0;
	}

	//selectOne

	/**
	 * @param sql     执行的SQL语句
	 * @param cls     实体类对象
	 * @param objects SQL语句中的限制条件
	 * @Function commonQueryOne
	 * @Description 查找单条记录
	 */
	public static <E> E commonQueryOne(String sql, Class<E> cls, Object... objects) throws Exception {
		conn = C3p0Util.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 = CRUDUtil.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();
						}
					}
					//将私有属性非可访问设置为可访问
					assert field != null;
					field.setAccessible(true);
					//给实体类中的属性赋值ֵ
					field.set(entity, columnValue);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			C3p0Util.close(conn, ps, rs);
		}
		return entity;
	}

	//selectAll

	/**
	 * @param sql     执行的SQL语句
	 * @param cls     实体类对象
	 * @param objects SQL语句中的限制条件
	 * @Function commonQueryList
	 * @Description 查找多条记录
	 */
	public static <E> List<E> commonQueryList(String sql, Class<E> cls, Object... objects) throws Exception {
		conn = C3p0Util.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 = CRUDUtil.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();
						}
					}
					assert field != null;
					field.setAccessible(true);
					field.set(entity, columnValue);
				}
				list.add(entity);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			C3p0Util.close(conn, ps, rs);
		}
		return list;
	}
}

CRUDUtilTest 测试方法

测试方法使用 junit4

pom 配置如下：

<!--junit-->
<dependency>
	<groupId>junit</groupId>
	<artifactId>junit</artifactId>
	<version>4.12</version>
</dependency>

public class CRUDUtilTest {
	public int insert(User user) throws Exception {
		String sql = "insert into t_user (id, name) values (?, ?)";
		int rs = CRUDUtil.commonUpdate(sql, user.getId(), user.getName());
		return rs;
	}

	public int update(User user) throws Exception {
		String sql = "update t_user set name=? where id=?";
		int rs = CRUDUtil.commonUpdate(sql, user.getName(), user.getId());
		return rs;
	}

	public int delete(String id) throws Exception {
		String sql = "delete from t_user where id=?";
		int rs = CRUDUtil.commonUpdate(sql, id);
		return rs;
	}

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

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

	@Test
	public void commonInsert() throws Exception {
		insert(new User(UUID.randomUUID().toString(), "user1"));
	}

	@Test
	public void commonUpdate() throws Exception {
		update(new User("DPISk1wO", "user5"));
	}

	@Test
	public void commonQueryOne() {
	}

	@Test
	public void commonQueryList() {
	}
}