81,092
社区成员
发帖
与我相关
我的任务
分享
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
<tx:annotation-driven transaction-manager="txManager" />
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${c3p0.driverClass}"></property>
<property name="jdbcUrl" value="${c3p0.url}"></property>
<property name="user" value="${c3p0.user}"></property>
<property name="password" value="${c3p0.password}"></property>
<property name="acquireIncrement" value="${c3p0.acquireIncrement}"></property>
<property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property>
<property name="maxIdleTime" value="${c3p0.maxIdleTime}"></property>
<property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property>
<property name="minPoolSize" value="${c3p0.minPoolSize}"></property>
<property name="acquireRetryDelay" value="1000"></property>
<property name="acquireRetryAttempts" value="60"></property>
<property name="breakAfterAcquireFailure" value="false"></property>
</bean>
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath*:c3p0.properties</value>
</list>
</property>
</bean>
<bean id="txManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
package cn.com.silence310.dao;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.PreDestroy;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* 数据库基类
*
* @author XMX 建议:
*
*/
@Component
public class BaseDao {
@Autowired
public ComboPooledDataSource ds = null;
private Connection connection = null;
private Statement statement = null;
// 关闭相关链接和rs
/**
* 没用完之前不要关。。。 建议外部使用
*
* @param rs
* @throws SQLException
*/
public void free(ResultSet rs) throws SQLException {
close(rs, this.statement, this.connection);
}
/**
* 执行操作前,初始化当前statement和链接
*
* @throws SQLException
*/
private void init() throws SQLException {
if (this.connection == null || this.connection.isClosed()) {
this.connection = this.ds.getConnection();
this.statement = this.connection.createStatement();
} else {
this.statement = this.connection.createStatement();
}
}
public void close(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
System.out.println("未成功关闭ResultSet");
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
System.out.println("未成功关闭Statement");
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
System.out.println("未成功关闭Connection");
e.printStackTrace();
}
}
}
}
public void close(Statement st, Connection conn) {
try {
if (st != null)
st.close();
} catch (SQLException e) {
System.out.println("未成功关闭Statement");
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 直接把链接返回连接池,并不是关闭链接
public void close(Connection conn) {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
System.out.println("未成功关闭Connection");
e.printStackTrace();
}
}
// Dao被销毁前执行操作,关闭statment,并返回链接到连接池
@PreDestroy
void destory() {
close(this.statement, this.connection);
System.err.println("链接返回连接池");
}
public ResultSet executeQuery(String sql) throws SQLException {
init();
ResultSet rs = null;
try {
rs = this.statement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public int executeUpdate(String sql) throws SQLException {
init();
int count = 0;
try {
count = this.statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
public JSONArray queryJSON(String sql) throws SQLException {
init();
ResultSet rs = null;
JSONArray rsJsonArray = new JSONArray();
try {
rs = this.statement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
ResultSetMetaData metaData = rs.getMetaData();
int cloumnCount = metaData.getColumnCount();
while (rs.next()) {
JSONObject cloumn = new JSONObject();
for (int i = 0; i < cloumnCount; i++) {
String columnName = metaData.getColumnLabel(i);
String value = rs.getString(columnName);
try {
cloumn.put(columnName, value);
rsJsonArray.put(cloumn);
} catch (JSONException e) {
e.printStackTrace();
}
}
}
return rsJsonArray;
}
public void free() {
close(this.statement, this.connection);
}
/**
* rs转List<Map>
*
* @param rs
* @return
* @throws SQLException
*/
private List ResultToListMap(ResultSet rs) throws SQLException {
List list = new ArrayList();
while (rs.next()) {
ResultSetMetaData md = rs.getMetaData();
Map map = new HashMap();
for (int i = 1; i < md.getColumnCount(); i++) {
map.put(md.getColumnLabel(i), rs.getObject(i));
}
list.add(map);
}
return list;
}
/**
* @author XMX
* @param sql
* @return
* @throws SQLException
*/
public List query(String sql) throws SQLException {
init();
ResultSet rs = null;
PreparedStatement preparedStatement = null;
try {
preparedStatement = this.connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
preparedStatement.close();
}
}
/**
* 用于带参数的查询,返回结果集
* @author XMX
* @param sql
* @param paramters 参数集合
* @return 结果集
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
public List query(String sql, Object... paramters)
throws SQLException {
init();
ResultSet rs = null;
PreparedStatement preparedStatement = null;
try {
preparedStatement = this.connection.prepareStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
rs = preparedStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
preparedStatement.close();
}
}
/**
* 返回单个结果的值,如count\min\max等等
* @author XMX
* @param sql语句
*
* @return 结果集
* @throws SQLException
*/
public Object getSingle(String sql) throws SQLException {
init();
Object result = null;
ResultSet rs = null;
PreparedStatement preparedStatement = null;
try {
preparedStatement = this.connection.prepareStatement(sql);
rs = preparedStatement.executeQuery();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
preparedStatement.close();
}
}
/**
* @author XMX
* @param sql
* @param paramters
* @return
* @throws SQLException
*/
public Object getSingle(String sql, Object... paramters)
throws SQLException {
init();
Object result = null;
ResultSet rs = null;
PreparedStatement preparedStatement = null;
try {
preparedStatement = this.connection.prepareStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
rs = preparedStatement.executeQuery();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
preparedStatement.close();
}
}
/**
* 用于增删改
*
* @param sql
* sql语句
* @return 影响行数
* @throws SQLException
*/
public int update(String sql) throws SQLException {
init();
PreparedStatement preparedStatement = null;
try {
preparedStatement = this.connection.prepareStatement(sql);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
preparedStatement.close();
}
}
/**
* 用于增删改(带参数)
*
* @param sql
* sql语句
* @param paramters
* sql语句
* @return 影响行数
* @throws SQLException
*/
public int update(String sql, Object... paramters)
throws SQLException {
init();
PreparedStatement preparedStatement = null;
try {
preparedStatement = this.connection.prepareStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
preparedStatement.close();
}
}