目录
简易版的c3p0连接。
1.项目整体结构
这里的两个c3p0jar包必须有。
可以去http://mvnrepository.com/搜索jar包名下载,别浪费积分去csdn里面下载了...小编以前被坑过很多次。
2.新建c3p0-config.xml文件
命名必须为c3p0-config.xml,
必须放在src目录下,c3p0包会默认加载src目录下的c3p0-config.xml文件。
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 这是默认配置信息 -->
<default-config>
<!-- 连接四大参数配置 -->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbctest</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 池参数配置 -->
<property name="acquireIncrement">3</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">2</property>
<property name="maxPoolSize">10</property>
</default-config>
<!-- 专门为oracle提供的配置信息 -->
<named-config name="oracle-config">
<property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb1</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">123</property>
<property name="acquireIncrement">3</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">2</property>
<property name="maxPoolSize">10</property>
</named-config>
</c3p0-config>
3.新建JDBCUtil工具类
package com.test.uitls;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCUtil {
private static DataSource ds = new ComboPooledDataSource();
// 获取连接池
public static DataSource getDataSource() {
return ds;
}
// 获取连接
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* 关闭连接
* @param resultSet
* @param pst
* @param connection
*/
public static void close(ResultSet resultSet, PreparedStatement pst, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4.数据库操作
package com.test.dao;
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.HashMap;
import java.util.List;
import java.util.Map;
import com.test.uitls.JDBCUtil;
public class JDBCDao {
/**
* * 增加,删除,修改
*/
public static void insertOrDeleteOrUpdate(String sql) {
try {
Connection connection = JDBCUtil.getConnection();
PreparedStatement pst = connection.prepareStatement(sql);
int execute = pst.executeUpdate();
System.out.println("执行语句:" + sql + "," + execute + "行数据受影响");
JDBCUtil.close(null, pst, connection);
} catch (SQLException e) {
System.out.println("异常提醒:" + e);
}
}
/**
* * 查询,返回结果集
*/
public static List<Map<String, Object>> select(String sql) {
List<Map<String, Object>> returnResultToList = null;
try {
Connection connection = JDBCUtil.getConnection();
PreparedStatement pst = connection.prepareStatement(sql);
ResultSet resultSet = pst.executeQuery();
// returnResultToList = returnResultToList(resultSet);
JDBCUtil.close(resultSet, pst, connection);
} catch (SQLException e) {
System.out.println("异常提醒:" + e);
}
return returnResultToList;
}
/**
* * 数据返回集合 * @param resultSet * @return * @throws SQLException
*/
public static List<Map<String, Object>> returnResultToList(ResultSet resultSet) {
List<Map<String, Object>> values = null;
try {
// 键: 存放列的别名, 值: 存放列的值.
values = new ArrayList<>();
// 存放字段名
List<String> columnName = new ArrayList<>();
ResultSetMetaData rsmd = resultSet.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
// 字段名
columnName.add(rsmd.getColumnLabel(i + 1));
}
System.out.println("表字段为:");
System.out.println(columnName);
System.out.println("表数据为:");
Map<String, Object> map = null;
// 处理 ResultSet, 使用 while 循环
while (resultSet.next()) {
map = new HashMap<>();
for (String column : columnName) {
Object value = resultSet.getObject(column);
map.put(column, value);
System.out.print(value + "\t");
}
// 把一条记录的 Map 对象放入准备的 List 中
values.add(map);
System.out.println();
}
} catch (SQLException e) {
System.out.println("异常提醒:" + e);
}
return values;
}
}