java c3p0 mysql_# Java 使用 C3P0 链接池操作 MySQL8

Java 使用 C3P0 链接池操作 MySQL8

pom 文件配置

mysql

mysql-connector-java

8.0.19

com.mchange

c3p0

0.9.5.5

C3P0-config.xml

com.mysql.cj.jdbc.Driver

jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=Hongkong&characterEncoding=utf-8&autoReconnect=true

root

root

10

10

20

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 commonQueryOne(String sql, Class 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 List commonQueryList(String sql, Class cls, Object... objects) throws Exception {

conn = C3p0Util.getConnection();

List list = new ArrayList();

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;

}

}

标签：ps,Exception,Java,rs,MySQL8,C3P0,try,objects,null

来源： https://blog.csdn.net/qq_37349830/article/details/104704800