本文介绍了如何使用Apache Commons DBCP实现MySQL连接池的配置，并提供了完整的代码示例，包括连接池的基本设置、获取连接及操作数据库的方法。
package com.qimh.mysql.datasource;
import java.sql.*;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
public class ConnectionPool{
private static BasicDataSource dbs = null;
public static DataSource setupDataSource(){
// bds = new BasicDataSource();
// //设置驱动程序
// bds.sestDriverClassName("com.mysql.jdbc.Driver");
// //设置连接用户名
// bds.setUsername("root");
// //设置连接密码
// bds.setPassword("root");
// //设置连接地址
// bds.setUrl("jdbc:mysql://localhost:3306/databasename");
// //设置初始化连接总数
// bds.setInitialSize(50);
// //设置同时应用的连接总数
// bds.setMaxActive(-1);
// //设置在缓冲池的最大连接数
// bds.setMaxIdle(-1);
// //设置在缓冲池的最小连接数
// bds.setMinIdle(0);
// //设置最长的等待时间
// bds.setMaxWait(-1);
// return (DataSource)bds;
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/ktv_demo7.5");
ds.setUsername("root");
ds.setPassword("123456");
java.sql.Connection conn = null;
/**
* 最大活动连接数
* 同时进行的数据库连接数不超过这个数目
* */
ds.setMaxActive(20);
/**
* 最大空闲连接数
* 当释放数据库连接后,空闲连接数超过这个数目时关闭一些空闲连接
*/
ds.setMaxIdle(7);
/**
* 是否预编译SQL语句
* */
ds.setPoolPreparedStatements(true);
try {
conn = ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("最大活动连接数:"+ds.getMaxActive());
System.out.println("最大空闲连接数:"+ds.getMaxIdle());
return ds;
}
// //显示连接池的连接个数的方法
// public static void printDataSourceStats(DataSource ds) throws SQLException{
// bds = (BasicDataSource)ds;
// System.out.println();
// System.out.println();
// }
// //关闭连接池的方法
// public static void shutdownDataSource(DataSource ds) throws SQLException{
// bds = (BasicDataSource)ds;
// bds.close();
// }
public static void main(String[] args) {
ConnectionPool.setupDataSource();
}
}
参考链接：http://www.2cto.com/kf/201108/100523.html
jar 包链接：https://pan.baidu.com/s/1geBLrVl
其他参考代码：
package mysqlJDBC;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.dbcp.BasicDataSource;
import com.housekeeper.myListener.services.Const;
import shopping.ConnectionEcStore.WebUtils;
import shopping.util.MyProperties;
public class MysqlJDBC {
private static String dbip = MyProperties.getPropertyValue(Const.UPLOAD_CONFIG, "ecstore.dbip");
private static String dbname = MyProperties.getPropertyValue(Const.UPLOAD_CONFIG, "ecstore.dbname");
private static Connection con = null; // 定义一个MYSQL链接对象
private static BasicDataSource bds = null;
public static void getMysqlConnection()
{
try {
if(bds == null)
{
String uname = MyProperties.getPropertyValue(Const.UPLOAD_CONFIG, "ecstore.username");
String upass = MyProperties.getPropertyValue(Const.UPLOAD_CONFIG, "ecstore.password");
bds = new BasicDataSource();
bds.setUsername(uname);
bds.setPassword(upass);
bds.setDriverClassName("com.mysql.jdbc.Driver");
bds.setUrl("jdbc:mysql://"+dbip+"/"+dbname+"?autoReconnect=true;characterEncoding=UTF-8");
bds.setInitialSize(4);// 初始化连接数
bds.setMinIdle(5);// 最小空闲数，防止当洪峰到来时，再次申请连接引起的 性能开销；
bds.setMaxIdle(7);// 最大空闲数，放洪峰过后，连接池中的连接过多，
bds.setMaxActive(10);// 最大连接数
bds.setMaxWait(5000);// 最大等待时间5秒 ,设为-1表示无限期；
}
con = bds.getConnection(); // 获取连接
System.out.println("yes");
} catch (Exception e) {
System.out.print("MYSQL ERROR:" + e.getMessage());
}
}
public static List<Map<String, Object>> findDataList(String sql) {
Statement stmt = null;
ResultSet rs = null;
List<Map<String, Object>> datalist = new ArrayList<Map<String, Object>>();
List<String> selectFieldList = new ArrayList<String>();
try {
getMysqlConnection();
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
ResultSetMetaData rsmt = rs.getMetaData();
for (int i = 1; i <= rsmt.getColumnCount(); i++) {
selectFieldList.add(rsmt.getColumnLabel(i));
}
while (rs.next()) {
Map<String, Object> valueMap = new HashMap<String, Object>();
for (String columnLabel : selectFieldList) {
valueMap.put(columnLabel, rs.getObject(columnLabel));
}
datalist.add(valueMap);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (con != null) con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return datalist;
}
public static Map<String, Object> findDataObject (String sql) {
List<Map<String, Object>> rs = findDataList(sql);
if (rs.size() > 0) {
return rs.get(0);
}
return new HashMap<String, Object>();
}
/**
* 查询方法
* @param sql 查询语句
* @param itemlist 列名list对象
* @return
*/
public static List<Map<String,Object>> selectDataList(String sql,List<String> itemlist)
{
Statement stmt = null; //创建声明
List<Map<String,Object>> datalist = new ArrayList<Map<String,Object>>();
try{
getMysqlConnection();
stmt = con.createStatement();
ResultSet res = stmt.executeQuery(sql);
while (res.next()) {
Map<String,Object> map = new HashMap<String,Object>();
for(int i=0;i<itemlist.size();i++)
{
String item = itemlist.get(i);
Object value = res.getObject(item);
map.put(item, value);
}
datalist.add(map);
}
res.close();
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{
if(stmt != null)
stmt.close();
if(con != null)
con.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
return datalist;
}
/**
* 数据修改
* @param sql
* @return
*/
public static boolean insertUpdateDelData(String sql)
{
Statement stmt = null; //创建声明
boolean b = false;
try{
getMysqlConnection();
stmt = con.createStatement();
//新增一条数据
int size = stmt.executeUpdate(sql);
if(size > 0)
b = true;
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{
if(stmt != null)
stmt.cancel();
if(con != null)
con.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
return b;
}
/**
* 添加数据
* @param sql
* @return
*/
public static boolean insertData(String tableName,Map<String,Object> map)
{
boolean b = false;
try{
String sql = "INSERT INTO "+tableName+" ";
String itemstr = "";
String valuestr = "";
int index = 0;
for(Entry<String, Object> entry: map.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
if(value instanceof String)
{
if(index == 0)
{
itemstr = key;
valuestr = "'"+value+"'";
}
else
{
itemstr = itemstr + "," +key;
valuestr = valuestr + ",'"+value+"'";
}
}
else if(value instanceof Integer || value instanceof Float || value instanceof Double || value instanceof Long)
{
if(index == 0)
{
itemstr = key;
valuestr = ""+value+"";
}
else
{
itemstr = itemstr + "," +key;
valuestr = valuestr + ",'"+value+"'";
}
}
else if(value instanceof Boolean)
{
if(index == 0)
{
itemstr = key;
valuestr = "'"+value+"'";
}
else
{
itemstr = itemstr + "," +key;
valuestr = valuestr + ",'"+value+"'";
}
} else {
if(index == 0) {
itemstr = key;
valuestr = "'"+value+"'";
} else {
itemstr = itemstr + "," +key;
valuestr = valuestr + ",'"+value+"'";
}
}
index++;
}
sql = sql + "("+itemstr+") VALUES ("+valuestr+")";
b = insertUpdateDelData(sql);
}catch(Exception ex){
ex.printStackTrace();
}
return b;
}
/**
* 更新数据
* @param tableName 要更新的表名
* @param map 需要更新的字段值
* @param wheremap 更新需要的条件
* @return
*/
public static boolean updateData(String tableName,Map<String,Object> map,Map<String,Object> wheremap)
{
boolean b = false;
try{
String sql = "UPDATE "+tableName+" SET ";
String itemstr = "";
String wherestr = "";
int index = 0;
for(Entry<String, Object> entry: map.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
if(value instanceof String)
{
if(index == 0)
{
itemstr = key + " = '"+value+"'";
}
else
{
itemstr = itemstr + "," +key + " = '"+value+"'";
}
}
else if(value instanceof Integer || value instanceof Float || value instanceof Double)
{
if(index == 0)
{
itemstr = key + " = "+value+"";
}
else
{
itemstr = itemstr + "," +key + " = "+value+"";
}
}
else if(value instanceof Boolean)
{
if(index == 0)
{
itemstr = key + " = '"+value+"'";
}
else
{
itemstr = itemstr + "," +key + " = '"+value+"'";
}
}
index++;
}
index = 0;
for(Entry<String, Object> entry: wheremap.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
if(value instanceof String)
{
if(index == 0)
{
wherestr = key + " = '"+value+"'";
}
else
{
wherestr = wherestr + " and " +key + " = '"+value+"'";
}
}
else if(value instanceof Integer || value instanceof Float || value instanceof Double)
{
if(index == 0)
{
wherestr = key + " = "+value+"";
}
else
{
wherestr = wherestr + " and " +key + " = "+value+"";
}
}
else if(value instanceof Boolean)
{
if(index == 0)
{
wherestr = key + " = '"+value+"'";
}
else
{
wherestr = wherestr + " and " +key + " = '"+value+"'";
}
}
index++;
}
sql = sql + itemstr + " where " + wherestr;
b = insertUpdateDelData(sql);
}catch(Exception ex){
ex.printStackTrace();
}
return b;
}
/**
* 删除数据
* @param tableName 要删除的表名
* @param wheremap 需要删除的条件
* @return
*/
public static boolean deleteData(String tableName,Map<String,Object> wheremap)
{
boolean b = false;
try{
String sql = "DELETE FROM "+tableName+" ";
String wherestr = "";
int index = 0;
for(Entry<String, Object> entry: wheremap.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
if(value instanceof String)
{
if(index == 0)
{
wherestr = key + " = '"+value+"'";
}
else
{
wherestr = wherestr + " and " +key + " = '"+value+"'";
}
}
else if(value instanceof Integer || value instanceof Float || value instanceof Double)
{
if(index == 0)
{
wherestr = key + " = "+value+"";
}
else
{
wherestr = wherestr + " and " +key + " = "+value+"";
}
}
else if(value instanceof Boolean)
{
if(index == 0)
{
wherestr = key + " = '"+value+"'";
}
else
{
wherestr = wherestr + " and " +key + " = '"+value+"'";
}
}
index++;
}
sql = sql + " where " + wherestr;
b = insertUpdateDelData(sql);
}catch(Exception ex){
ex.printStackTrace();
}
return b;
}
public static void main(String arg[]) {
String sql = "select max(member_id) as mid from sdb_pam_members";
List<String> itemlist = new ArrayList<String>();
itemlist.add("mid");
// itemlist.add("shipping");
// itemlist.add("ship_name");
// itemlist.add("ship_time");
// itemlist.add("total_amount");
List<Map<String,Object>> dlist = selectDataList(sql,itemlist);
// for(int i=0;i<dlist.size();i++)
// {
Map<String,Object> map3 = dlist.get(0);
System.out.println(map3.toString());
long mid = ((Long)map3.get("mid")+1);
// }
Date date = new Date();
String time = String.valueOf(date.getTime());
time = time.substring(0,time.length()-3);
Map<String,Object> map = new HashMap<String,Object>();
map.put("member_id", mid);
map.put("member_lv_id", 1);
map.put("sex", "2");
map.put("email", "yinliang@htomail.com");
map.put("reg_ip", "15.12.11.1");
map.put("regtime", time);
map.put("cur", "CNY");
map.put("member_refer", "local");
map.put("source", "api");
boolean b = insertData("sdb_b2c_members",map);
System.out.println(b);
String s = WebUtils.extends_md5("222222","yinl88",time);
Map<String,Object> map2 = new HashMap<String,Object>();
map2.put("member_id", mid);
map2.put("login_password", s);
map2.put("login_account", "yinl88");
map2.put("login_type", "local");
map2.put("password_account", "yinl88");
map2.put("disabled", "false");
map2.put("createtime", time);
boolean b2 = insertData("sdb_pam_members",map2);
System.out.println(b2);
// Map<String,Object> map2 = new HashMap<String,Object>();
// map2.put("shipping", "test1111");
// map2.put("ship_name", "test21111");
// map2.put("ship_time", "test211111");
// map2.put("total_amount", 60.00);
//
// Map<String,Object> wheremap = new HashMap<String,Object>();
// wheremap.put("order_id", "1200333");
//
// boolean b2 = updateData("sdb_b2c_orders",map2,wheremap);
// System.out.println(b2);
// Map<String,Object> wheremap = new HashMap<String,Object>();
// wheremap.put("order_id", "1200303222");
//
// boolean b2 = deleteData("sdb_b2c_orders",wheremap);
// System.out.println(b2);
}
}
读取properties 属性文件的工具类
package shopping.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
public class MyProperties {
public static String getPropertyValue (String path, String key) {
Properties props = new Properties();
try {
InputStream in = new FileInputStream(new File(path).getCanonicalPath());
try {
props.load(in);
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return props.getProperty(key);
}
public static void main(String[] args) {
String val = getPropertyValue("hot-deploy/ktv_demo/webapp/config/customize.properties", "mail.server.host");
System.out.println(val);
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
