c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///CloudMap</property>
<property name="user">root</property>
<property name="password">root</property>
<!--连接池中保留的最大连接数。Default: 15 -->
<property name="maxPoolSize">50</property>
<!--初始化时获取三个连接,取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
<property name="initialPoolSize">10</property>
<!--c3p0是异步操作的,缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能
通过多线程实现多个操作同时被执行。Default: 3-->
<property name="numHelperThreads">10</property>
<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
<property name="acquireIncrement">10</property>
</default-config>
<named-config name="dumbTestConfig">
<property name="maxStatements">200</property>
<user-overrides user="poop">
<property name="maxStatements">300</property>
</user-overrides>
</named-config>
</c3p0-config>
DataSourceUtils.java
package com.slj.dao.utils;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class DataSourceUtils {
private static ComboPooledDataSource cpds = new ComboPooledDataSource();
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
/**
* 当DBUtils需要手动控制事务时,调用该方法获得一个连接
*
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return cpds.getConnection();
}
public static DataSource getDataSource() {
return cpds;
}
// 获取绑定到ThreadLocal中的Connection。
public static Connection getConnectionByTransaction() throws SQLException {
Connection con = tl.get();
if(con == null) {
con = cpds.getConnection();
tl.set(con);
}
return con;
}
//开启一个事务
public static void startTransaction(Connection conn) throws SQLException {
if(conn != null)
conn.setAutoCommit(false);
}
//事务回滚
public static void rollback(Connection conn) throws SQLException {
if(conn != null)
conn.rollback();
}
//关闭连接
public static void closeConnection(Connection conn) throws SQLException {
if(conn != null) {
conn.commit();
conn.close();
tl.remove();
}
}
}
dao.java
package com.slj.dao.mysql;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.junit.Test;
import com.slj.dao.utils.DataSourceUtils;
import com.slj.dao.utils.StringArray;
import com.slj.pojo.ImageInfo;
public class ImageInfoDao {
/**
* CREATE DATABASE CloudMap;
USE CloudMap;
DROP TABLE Image;
CREATE TABLE Image(
id INT PRIMARY KEY AUTO_INCREMENT,
imgName VARCHAR(20) UNIQUE NOT NULL,
meanAvg VARCHAR(100) NOT NULL,
imgVariance VARCHAR(100) NOT NULL,
location VARCHAR(100) NOT NULL
);
*添加云图信息
*/
public void insertImage(ImageInfo img) throws SQLException {
String sql = "insert Image values(null,?,?,?,?)";
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
//Connection conn = DataSourceUtils.getConnection();
//将ImageInfo中的数组属性转为字符串
double[] mean = img.getMeanAvg();
String[] meanStr = new String[4];
for(int i =0; i< mean.length; i++) {
meanStr[i] = Double.toString(mean[i]);
}
String meanString = String.format("%s,%s,%s,%s", meanStr);
double[] variance = img.getImgVariance();
String varianceStr = "";
for(int i =0; i< variance.length; i++) {
varianceStr += Double.toString(variance[i]) + ",";
}
//截掉最后的那个逗号
varianceStr = varianceStr.substring(0, varianceStr.length() - 1);
//runner.update(conn,sql,img.getImgName(),meanString,varianceStr,img.getLocation());
runner.update(sql,img.getImgName(),meanString,varianceStr,img.getLocation());
//DataSourceUtils.closeConnection(conn);
}
//获取image信息
@Test
public List<ImageInfo> getAllImage() throws SQLException {
String sql = "select * from Image";
QueryRunner runner = new QueryRunner();
Connection con = DataSourceUtils.getConnection();
List<ImageInfo> imgs = runner.query(con, sql,
new ResultSetHandler<List<ImageInfo>>() {
@Override
public List<ImageInfo> handle(ResultSet rs)
throws SQLException {
List<ImageInfo> imgs = new ArrayList<ImageInfo>();
while (rs.next()) {
ImageInfo img = new ImageInfo();
img.setImgName(rs.getString("imgName"));
String mean = rs.getString("meanAvg");
//String 转为Double型数组,并封装mean
img.setMeanAvg(StringArray.String2Array(mean));
//封装variance
String variance = rs.getString("ImgVariance");
img.setImgVariance(StringArray.String2Array(variance));
img.setLocation(rs.getString("location"));
imgs.add(img);
}
return imgs;
}
});
DbUtils.close(con);
return imgs;
}
}