连接池jar:c3p0
代码结构
-----------------------------------------------
配置文件 config.properties
#hibernate.dialect=sy.util.Oracle10gDialect
#driverClassName=oracle.jdbc.driver.OracleDriver
#validationQuery=SELECT 1 FROM DUAL
#jdbc_url=jdbc:oracle:thin:@localhost:1521:orcl
#jdbc_username=bjlyjoa
#jdbc_password=bjlyjoa
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
driverClass=com.mysql.jdbc.Driver
validationQuery=SELECT 1
jdbcUrl=jdbc:mysql://192.168.232.88:3306/xlzj_sh?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
user=root
password=123456
#hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect
#driverClassName=net.sourceforge.jtds.jdbc.Driver
#validationQuery=SELECT 1
#jdbc_url=jdbc:jtds:sqlserver://127.0.0.1:1433/qx
#jdbc_username=sa
#jdbc_password=123456
hibernate.hbm2ddl.auto=none
hibernate.show_sql=false
hibernate.format_sql=false
hibernate.use_sql_comments=false
----------------------------------------------------------------------------
数据库连接 文件
package light.mvc.framework.connectUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.DataSources;
/**
*
*
* 项目名称：s4h4s2s
* 类名称：DataSourceConnUtil
* 类描述： c3p0连接池管理类
* 创建人：刘军/jun liu
* 创建时间：2015-12-19 下午11:40:35
* 修改人：刘军/shall_liu
* 修改时间：2015-12-19 下午11:40:35
* 修改备注：
* @version
*
*/
public class DataSourceConnUtil {
private static final String JDBC_DRIVER = "driverClass";
private static final String JDBC_URL = "jdbcUrl";
private static DataSource ds;
/**
* 初始化连接池代码块
*/
static{
initDBSource();
}
/**
* 初始化c3p0连接池
*/
private static final void initDBSource(){
Properties c3p0Pro = new Properties();
try {
//加载配置文件
c3p0Pro.load(DataSourceConnUtil.class.getResourceAsStream("/config.properties"));
//c3p0Pro.load(new FileInputStream(PathUtil.getPath("dabase.properties")));
} catch (Exception e) {
e.printStackTrace();
}
String drverClass = c3p0Pro.getProperty(JDBC_DRIVER);
if(drverClass != null){
try {
//加载驱动类
Class.forName(drverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
Properties jdbcpropes = new Properties();
Properties c3propes = new Properties();
for(Object key:c3p0Pro.keySet()){
String skey = (String)key;
if(skey.startsWith("c3p0.")){
c3propes.put(skey, c3p0Pro.getProperty(skey));
}else{
jdbcpropes.put(skey, c3p0Pro.getProperty(skey));
}
}
try {
//建立连接池
DataSource unPooled = DataSources.unpooledDataSource(c3p0Pro.getProperty(JDBC_URL),jdbcpropes);
ds = DataSources.pooledDataSource(unPooled,c3propes);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接对象
* @return 数据连接对象
* @throws SQLException
*/
public static synchronized Connection getConnection() {
Connection conn = null;
try {
conn = ds.getConnection();
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);//取消 事务管理：事务提交机制
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
-------------------------------------------------------------------------------------------------------------
package light.mvc.framework.connectUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.DataSources;
/**
*
*
* 项目名称：s4h4s2s
* 类名称：DataSourceConnUtil
* 类描述： c3p0连接池管理类
* 创建人：刘军/jun liu
* 创建时间：2015-12-19 下午11:40:35
* 修改人：刘军/shall_liu
* 修改时间：2015-12-19 下午11:40:35
* 修改备注：
* @version
*
*/
public class DataSourceConnUtil {
private static final String JDBC_DRIVER = "driverClass";
private static final String JDBC_URL = "jdbcUrl";
private static DataSource ds;
/**
* 初始化连接池代码块
*/
static{
initDBSource();
}
/**
* 初始化c3p0连接池
*/
private static final void initDBSource(){
Properties c3p0Pro = new Properties();
try {
//加载配置文件
c3p0Pro.load(DataSourceConnUtil.class.getResourceAsStream("/config.properties"));
//c3p0Pro.load(new FileInputStream(PathUtil.getPath("dabase.properties")));
} catch (Exception e) {
e.printStackTrace();
}
String drverClass = c3p0Pro.getProperty(JDBC_DRIVER);
if(drverClass != null){
try {
//加载驱动类
Class.forName(drverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
Properties jdbcpropes = new Properties();
Properties c3propes = new Properties();
for(Object key:c3p0Pro.keySet()){
String skey = (String)key;
if(skey.startsWith("c3p0.")){
c3propes.put(skey, c3p0Pro.getProperty(skey));
}else{
jdbcpropes.put(skey, c3p0Pro.getProperty(skey));
}
}
try {
//建立连接池
DataSource unPooled = DataSources.unpooledDataSource(c3p0Pro.getProperty(JDBC_URL),jdbcpropes);
ds = DataSources.pooledDataSource(unPooled,c3propes);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接对象
* @return 数据连接对象
* @throws SQLException
*/
public static synchronized Connection getConnection() {
Connection conn = null;
try {
conn = ds.getConnection();
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);//取消 事务管理：事务提交机制
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
------------------------------------------------------------------------------------------------------------
package light.mvc.framework.connectUtil.jdbcUtl;
/**
*
* @Title: SqlParameter.java
* @Package light.mvc.framework.connectUtil.jdbcUtl
* @Description: TODO(存储过程参数类型)
* @author 刘军
* @date 2016-3-19 下午2:47:02
* @version V1.0
*/
public class SqlParameter {
/**
* 参数名称
*/
public String Name;
/**
* 参数值
*/
public Object Value;
/**
* true表示参数为输出类型
*/
public boolean OutPut;
/**
* 参数类型
*/
public int Type;
/**
* 输入类型参数的构造函数
* @param name 存储过程 输入类型 参数名称
* @param value 存储过程 输入类型 参数值
*/
public SqlParameter(String name,Object value){
this.Name = name;
this.Value= value;
}
/**
* 输出类型参数的构造函数
* @param type 存储过程 输出类型 参数类型
* @param name 存储过程 输出类型 参数名称
*/
public SqlParameter(int type,String name){
this.Name = name;
this.OutPut = true;
this.Type = type;
}
/**
* 返回类型参数的构造函数
* @param type 存储过程 返回类型
*/
public SqlParameter(int type){
this.Name = "";
this.OutPut = true;
this.Type = type;
}
}
-----------------------------------------------------------------------------------------------------
package light.mvc.framework.connectUtil.jdbcUtl;
/**
*
* @Title: SqlParameter.java
* @Package light.mvc.framework.connectUtil.jdbcUtl
* @Description: TODO(存储过程参数类型)
* @author 刘军
* @date 2016-3-19 下午2:47:02
* @version V1.0
*/
public class SqlParameter {
/**
* 参数名称
*/
public String Name;
/**
* 参数值
*/
public Object Value;
/**
* true表示参数为输出类型
*/
public boolean OutPut;
/**
* 参数类型
*/
public int Type;
/**
* 输入类型参数的构造函数
* @param name 存储过程 输入类型 参数名称
* @param value 存储过程 输入类型 参数值
*/
public SqlParameter(String name,Object value){
this.Name = name;
this.Value= value;
}
/**
* 输出类型参数的构造函数
* @param type 存储过程 输出类型 参数类型
* @param name 存储过程 输出类型 参数名称
*/
public SqlParameter(int type,String name){
this.Name = name;
this.OutPut = true;
this.Type = type;
}
/**
* 返回类型参数的构造函数
* @param type 存储过程 返回类型
*/
public SqlParameter(int type){
this.Name = "";
this.OutPut = true;
this.Type = type;
}
}
--------------------------------------------------------------------------------------------------------------------
package light.mvc.framework.connectUtil.jdbcUtl;
import java.sql.ResultSet;
/**
*
* @Title: ObjectMapper.java
* @Package light.mvc.framework.connectUtil.jdbcUtl
* @Description: TODO(用于转换数据结果集 )
* @author 刘军
* @date 2016-3-19 上午9:52:18
* @version V1.0
*/
public interface ObjectMapper {
public Object mapping(ResultSet rs);
}
-------------------------------------------------------------------------------------------------------
package light.mvc.framework.connectUtil.jdbcUtl;
import java.sql.ResultSet;
/**
*
* @Title: ObjectMapper.java
* @Package light.mvc.framework.connectUtil.jdbcUtl
* @Description: TODO(用于转换数据结果集 )
* @author 刘军
* @date 2016-3-19 上午9:52:18
* @version V1.0
*/
public interface ObjectMapper {
public Object mapping(ResultSet rs);
}
--------------------------------------------------------------------------------------------------------
package light.mvc.framework.connectUtil.jdbcUtl;
import java.sql.ResultSet;
/**
*
* @Title: ObjectMapper.java
* @Package light.mvc.framework.connectUtil.jdbcUtl
* @Description: TODO(用于转换数据结果集 )
* @author 刘军
* @date 2016-3-19 上午9:52:18
* @version V1.0
*/
public interface ObjectMapper {
public Object mapping(ResultSet rs);
}
--------------------------------------------------------------------------------------------------------------
package light.mvc.dao.impl;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import light.mvc.dao.JdbcBaseDao;
import light.mvc.dao.JdbcDao;
import light.mvc.framework.connectUtil.jdbcUtl.ObjectMapper;
import light.mvc.framework.connectUtil.jdbcUtl.PageModel;
import light.mvc.pageModel.demo.Demo;
import light.mvc.service.demo.impl.DemoMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
@Repository
public class JdbcDaoImpl extends Object implements JdbcDao{
@Autowired
private JdbcBaseDaoImpl jdbcBaseUtil =new JdbcBaseDaoImpl();
public JdbcBaseDaoImpl getJdbcBaseUtil() {
return jdbcBaseUtil;
}
public void setJdbcBaseUtil(JdbcBaseDaoImpl jdbcBaseUtil) {
this.jdbcBaseUtil = jdbcBaseUtil;
}
/**
*
* @Title: insert
* @Description: TODO(插入单挑数据)
* @param @param sql 查询sql
* @param @param obj 参数数组
* @param @param isGenerateKey (true false)
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean insert(String sql, Object[] obj){
return jdbcBaseUtil.update(sql, obj, false);
}
/**
*
* @author 刘军
* @date 2016-3-19 下午2:23:42
* @version V1.0
* @Title: insertLines
* @Description: TODO(新增)
* @param @param sql
* @param @param obj
* @param @return 设定文件
* @return int 返回类型
* @throws
*/
public int insertLines(String sql, Object[] obj){
return jdbcBaseUtil.executeUpdate(sql, obj);
}
/**
*
* @author 刘军
* @date 2016-3-19 下午2:05:19
* @version V1.0
* @Title: insert
* @Description: TODO(新增)
* @param @param sql
* @param @param obj
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean insertByList(String sql, List<Object> obj){
return jdbcBaseUtil.updateByPreparedStatement(sql, obj);
}
/**
*
* @Title: delete
* @Description: TODO(删除单挑数据)
* @param @param sql 删除sql
* @param @param obj 参数数组
* @param @param isGenerateKey (true false)
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean delete(String sql, Object[] obj){
return jdbcBaseUtil.update(sql, obj, false);
}
/**
*
* @author 刘军
* @date 2016-3-19 下午2:19:20
* @version V1.0
* @Title: delete
* @Description: TODO(删除单挑数据)
* @param @param sql
* @param @param obj
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean delete(String sql,List<Object> obj){
return jdbcBaseUtil.updateByPreparedStatement(sql, obj);
}
/**
*
* @Title: deleteALL
* @Description: TODO(批量删除数据)
* @param @param sql
* @param @param obj
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean deleteALL(String sql, Object[] obj){
StringBuffer sbStr = new StringBuffer();
for (int i = 0; i < obj.length; i++) {
sbStr.append("?,");
}
String sqls =sql+"("+ sbStr.substring(0, sbStr.length() - 1) + ")";
return jdbcBaseUtil.update(sqls, obj, false);
}
/**
*
* @author 刘军
* @date 2016-3-19 下午2:20:56
* @version V1.0
* @Title: deleteALL
* @Description: TODO(删除 批量删除 )
* @param @param sql
* @param @param obj
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean deleteALL(String sql,List<Object> obj){
StringBuffer sbStr = new StringBuffer();
for (int i = 0; i < obj.size(); i++) {
sbStr.append("?,");
}
String sqls =sql+"("+ sbStr.substring(0, sbStr.length() - 1) + ")";
return jdbcBaseUtil.updateByPreparedStatement(sqls, obj);
}
/**
*
* @Title: update
* @Description: TODO(更新单挑数据记录)
* @param @param sql
* @param @param obj
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean update(String sql, Object[] obj){
return jdbcBaseUtil.update(sql, obj, false);
}
/**
*
* @author 刘军
* @date 2016-3-19 下午2:21:45
* @version V1.0
* @Title: update
* @Description: TODO(修改 )
* @param @param sql
* @param @param obj
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean update(String sql, List<Object> obj){
return jdbcBaseUtil.updateByPreparedStatement(sql, obj);
}
/**
*
* @Title: queryFrist
* @Description: TODO(查询出第一条数据记录)
* @param @param tale
* @param @param objParams
* @param @return 设定文件
* @return Object 返回类型
* @throws
*/
public Object queryFrist(String tale,Object[] objParams){
String sql = "select * from "+tale+"";
Object[] obj = objParams;
return (Object) query(sql, obj, new DemoMapper()).get(0);
}
/**
*
* @author 刘军
* @date 2016-3-19 上午9:41:06
* @version V1.0
* @Title: find
* @Description: TODO(查询一条记录 )
* @param @param sql
* @param @param obj
* @param @param mapper
* @param @return 设定文件
* @return Object 返回类型
* @throws
*/
public Object find(String sql, Object[] obj, ObjectMapper mapper){
return jdbcBaseUtil. find( sql, obj, mapper) ;
}
/**
*
* @Title: query
* @Description: TODO( 查询所有的数据记录；并以list 集合(或者：Object 对象)返回 )
* @param @param sql
* @param @param obj
* @param @param mapper
* @param @return 设定文件
* @return List<? extends Object> 返回类型
* @throws
*/
public List<? extends Object> query(String sql, Object[] obj,
ObjectMapper mapper) {
return jdbcBaseUtil.query(sql, obj, mapper);
}
/**
*
* @Title: CountNum
* @Description: TODO( select count(*)from table 的总数据条数)
* @param @param tableName (数据库表名)
* @param @return 设定文件
* @return int 返回类型
* @throws
*/
public int CountNum (String tableName){
return jdbcBaseUtil.returnTableCount(tableName);
}
/**
*
* @Title: queryPageNumber
* @Description: TODO(分页查询)
* @param @param pageNo 第几页
* @param @param pageSize 一页显示多少条数据
* @param @param table 查询哪个表
* 全表无条件查询
* {select * from （ tablea a,tableb b where a.id=b.id）limt numStrat,numStop}
* 全表 带条件模糊查询：
*SELECT * FROM demo a ,demo b WHERE a.id=b.id AND a.id LIKE "1%" LIMIT 0,15;
* @param @return 设定文件
* @return PageModel 返回类型
* @throws
*/
public PageModel queryPageNumber(int pageNo, int pageSize,String table) {
int countTotal=CountNum(table);
//oracle 分页
//String sql="select * from (select j.*,rownum rn from (select * from "+table+") j where rownum<=?) where rn>?";
//mysql 分页
String sql="SELECT * FROM "+table+" LIMIT ?,?;";
Object[] obj = {(pageNo - 1) * pageSize, pageNo * pageSize};
List<Demo> list = (List<Demo>) query(sql, obj,new DemoMapper());
PageModel pagemodel = new PageModel();
pagemodel.setPageNo(pageNo);
pagemodel.setPageSize(pageSize);
pagemodel.setList(list);
pagemodel.setTotalRecords(countTotal);
if(pageSize!=0){
pagemodel.setCountPageNo(countTotal/pageSize);
}
return pagemodel;
}
/**
*
* @Title: findSimpleResult 获取最后一条（单条）记录
* @Description: TODO(查询单条记录)
* @param @param sql
* @param @param params
* @param @return 设定文件
* @return Map<String,Object> 返回类型
* @throws
*/
public Map<String, Object> findSimpleResult(String sql, List<Object> params){
return jdbcBaseUtil.findSimpleResult(sql, params);
}
/**
*
* @author 刘军
* @date 2016-3-19 上午12:30:02
* @version V1.0
* @Title: findModeResult 查询多条记录
* @Description: TODO(查询多条记录)
* @param @param sql
* @param @param params
* @param @return 设定文件
* @return List<Map<String,Object>> 返回类型
* @throws
*/
public List<Map<String, Object>> findModeResult(String sql, List<Object> params){
return jdbcBaseUtil.findModeResult(sql,params);
}
/**
*
* @author 刘军
* @date 2016-3-19 上午8:43:07
* @version V1.0
* @Title: executeQuerySingle
* @Description: TODO(SQL 查询将查询结果：一行一列)
* @param @param sql
* @param @param params
* @param @return 设定文件
* @return Object 返回类型
* @throws
*/
public Object executeQuerySingle(String sql, Object[] params){
return jdbcBaseUtil.executeQuerySingle(sql, params);
}
/**
*
* @author 刘军
* @date 2016-3-19 上午9:08:05
* @version V1.0
* @Title: findSimpleRefResult
* @Description: TODO(通过反射机制查询单条记录)
* @param @param sql
* @param @param params
* @param @param cls
* @param @return 设定文件
* @return T 返回类型
* @throws
*/
public <T> T findSimpleRefResult(String sql, List<Object> params,Class<T> cls) {
//return JdbcBaseUtil.findSimpleRefResult(sql, params, cls);
return null;
}
/**
*
* @author 刘军
* @date 2016-3-19 上午9:13:39
* @version V1.0
* @Title: findMoreRefResult
* @Description: TODO( 通过反射机制查询多条记录)
* @param @param sql
* @param @param params
* @param @param cls
* @param @return 设定文件
* @return List<T> 返回类型
* @throws
*/
public <T> List<T> findMoreRefResult(String sql, List<Object> params,
Class<T> cls) {
return jdbcBaseUtil.findMoreRefResult(sql, params, cls);
}
/**
*
* @author 刘军
* @date 2016-3-19 上午11:03:06
* @version V1.0
* @Title: excuteQuery
* @Description: TODO(获取结果集，并将结果放在List中)
* @param @param sql
* @param @param params
* @param @return 设定文件
* @return List<Object> 返回类型
* @throws
*/
public List<Object> excuteQuery(String sql, Object[] params){
return jdbcBaseUtil.excuteQuery( sql, params);
}
/**
*
* @author 刘军
* @date 2016-3-19 上午11:03:03
* @version V1.0
* @Title: executeQuery
* @Description: TODO(统一的select语句，为了能够访问结果集，将结果集放入ArrayList，)
* @param @param sql
* @param @param parameters
* @param @return 设定文件
* @return ArrayList 返回类型
* @throws
*/
public ArrayList executeQuery(String sql, String[] parameters){
return jdbcBaseUtil.executeQuery( sql, parameters);
}
}
------------------------------------------------------------------------------------------------
package light.mvc.dao.impl;
import java.lang.reflect.Field;
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 org.springframework.stereotype.Repository;
import light.mvc.dao.JdbcBaseDao;
import light.mvc.framework.connectUtil.ConnUtil;
import light.mvc.framework.connectUtil.DataSourceConnUtil;
import light.mvc.framework.connectUtil.jdbcUtl.ObjectMapper;
import light.mvc.framework.connectUtil.jdbcUtl.SqlParameter;
@Repository
public class JdbcBaseDaoImpl implements JdbcBaseDao {
/**
* 获取连接
*/
static {
getConnection();
}
/**
* @author 刘军
* @date 2016-3-19 上午11:14:17
* @version V1.0
* @Title: getStatement
* @Description: TODO( )
* @param @return
* @param @throws SQLException 设定文件
* @return Connection 返回类型
* @throws
*/
public Connection getStatement() throws SQLException {
Connection connection = null;
Statement statement = null;
// 仅当statement失效时才重新创建
if (statement == null || statement.isClosed() == true) {
statement = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}
return connection;
}
/**
*
* @Title: getConnection
* @Description: TODO(得到数据库连接)
* @param @return 设定文件
* @return Connection 返回类型
* @throws
*/
public static Connection getConnection() {
Connection connection = null;
try {
/**
* 从连接池获取连接
*/
connection = DataSourceConnUtil.getConnection();
if (connection == null) {
/**
* 直接连接数据库
*/
connection = ConnUtil.getConn();
}
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
/**
*
* @Title: update
* @Description: TODO(更新：update)
* @param @param sql
* @param @param obj
* @param @param isGenerateKey
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean update(String sql, Object[] obj, boolean isGenerateKey) {
Connection conn = null;
PreparedStatement pstmt = null;
boolean bFlag = false;
try {
conn = JdbcBaseDaoImpl.getConnection();
pstmt = isGenerateKey ? conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS) : conn
.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
pstmt.setObject(i + 1, obj[i]);
}
conn.setAutoCommit(false);//JDBC 事务管理
int i = pstmt.executeUpdate();
conn.commit();//JDBC 事务管理
if (i > 0)
bFlag = true;
} catch (SQLException ex) {
ex.printStackTrace();
try {
conn.rollback();//JDBC 事务管理
} catch (SQLException e) {
e.printStackTrace();
}
} finally {
try {
conn.close();
pstmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return bFlag;
}
/**
*
* @Title: updateByPreparedStatement 增加、删除、改
* @Description: TODO(增加、删除、改)
* @param @param sql
* @param @param params
* @param @return 设定文件
* @return boolean 返回类型
* @throws
*/
public boolean updateByPreparedStatement(String sql, List<Object> params) {
boolean flag = false;
Connection connection = null;
PreparedStatement pstmt = null;
int result = -1;
try {
connection = JdbcBaseDaoImpl.getConnection();
pstmt = connection.prepareStatement(sql);
int index = 1;
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
result = pstmt.executeUpdate();
connection.commit();//JDBC 事务管理
flag = result > 0 ? true : false;
} catch (SQLException e) {
try {
connection.rollback();//JDBC 事务管理
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
pstmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
/**
* insert update delete SQL语句的执行的统一方法
*
* @param sql
* SQL语句
* @param params
* 参数数组，若没有参数则为null
* @return 受影响的行数
*/
public int executeUpdate(String sql, Object[] params) {
PreparedStatement preparedStatement = null;
// 受影响的行数
int affectedLine = 0;
Connection connection = null;
try {
// 获得连接
connection = JdbcBaseDaoImpl.getConnection();
// 调用SQL
preparedStatement = connection.prepareStatement(sql);
// 参数赋值
if (params != null) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
// 执行
affectedLine = preparedStatement.executeUpdate();
connection.commit();// 提交事务
} catch (SQLException e) {
System.out.println(e.getMessage());
try {
connection.rollback();//JDBC 事务管理
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
// 释放资源
close(connection, preparedStatement, null);
}
return affectedLine;
}
/**
*
* @Title: query
* @Description: TODO(这里用一句话描述这个方法的作用)
* @param @param sql
* @param @param obj
* @param @param mapper
* @param @return 设定文件
* @return List<? extends Object> 返回类型
* @throws
*/
public List<? extends Object> query(String sql, Object[] obj,
ObjectMapper mapper) {
Object o = null;
List<Object> list = new ArrayList<Object>();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = JdbcBaseDaoImpl.getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
pstmt.setObject(i + 1, obj[i]);
}
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
o = mapper.mapping(rs);
list.add(o);
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
try {
pstmt.close();
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return list;
}
/**
*
* @Title: returnTableCount
* @Description: TODO( select count(*)from table 的总数据条数)
* @param @param table
* @param @return 设定文件
* @return int 返回类型
* @throws
*/
public int returnTableCount(String table){
String sql="select count(*) as counts from "+table+"";
Connection conn = null;
ResultSet resultSet = null;
Statement pstmt = null;
int count=0;
try {
conn = JdbcBaseDaoImpl.getConnection();
pstmt = conn.createStatement();
resultSet=pstmt.executeQuery(sql);
if(resultSet.next()){
count=resultSet.getInt("counts");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
resultSet.close();
pstmt.close();
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return count;
}
/**
*
* @Title: findSimpleResult 获取最后一条（单条）记录
* @Description: TODO(查询单条记录)
* @param @param sql
* @param @param params
* @param @return 设定文件
* @return Map<String,Object> 返回类型
* @throws
*/
public Map<String, Object> findSimpleResult(String sql, List<Object> params) {
Map<String, Object> map = new HashMap<String, Object>();
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
int index = 1;
try {
connection = JdbcBaseDaoImpl.getConnection();
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();// 返回查询结果
ResultSetMetaData metaData = resultSet.getMetaData();
int col_len = metaData.getColumnCount();
while (resultSet.next()) {
for (int i = 0; i < col_len; i++) {
String cols_name = metaData.getColumnName( i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
resultSet.close();
pstmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return map;
}
/**
* 获取结果集，并将结果放在List中
*
* @param sql
* SQL语句
* @return List 结果集
*/
public List<Object> excuteQuery(String sql, Object[] params) {
// 创建List
List<Object> list = new ArrayList<Object>();
Connection connection = null;
ResultSet rs = null;
// 创建ResultSetMetaData对象
ResultSetMetaData rsmd = null;
// 结果集列数
int columnCount = 0;
try {
// 获得连接
connection = JdbcBaseDaoImpl.getConnection();
// 执行SQL获得结果集
rs = executeQueryRS(sql, params);
// 将ResultSet的结果保存到List中
while (rs.next()) {
rsmd = rs.getMetaData();
// 获得结果集列数
columnCount = rsmd.getColumnCount();
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
map.put(rsmd.getColumnLabel(i), rs.getObject(i));
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println(e.getMessage());
} finally {
// 关闭所有资源
try {
rs.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return list;
}
/**
* SQL 查询将查询结果直接放入ResultSet中
*
* @param sql
* SQL语句
* @param params
* 参数数组，若没有参数则为null
* @return 结果集
*/
private ResultSet executeQueryRS(String sql, Object[] params) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 获得连接
connection = JdbcBaseDaoImpl.getConnection();
// 调用SQL
preparedStatement = connection.prepareStatement(sql);
// 参数赋值
if (params != null) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
// 执行
resultSet = preparedStatement.executeQuery();
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
}
return resultSet;
}
/**
*
* @Title: findModeResult 查询多条记录
* @Description: TODO(查询多条记录)
* @param @param sql
* @param @param params
* @param @return
* @param @throws SQLException 设定文件
* @return List<Map<String,Object>> 返回类型
* @throws
*/
public List<Map<String, Object>> findModeResult(String sql,
List<Object> params) {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
int index = 1;
try {
connection = JdbcBaseDaoImpl.getConnection();
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
resultSet.close();
pstmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
/**
* SQL 查询将查询结果：一行一列
*
* @param sql
* SQL语句
* @param params
* 参数数组，若没有参数则为null
* @return 结果集
*/
public Object executeQuerySingle(String sql, Object[] params) {
Object object = null;
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 获得连接
connection = JdbcBaseDaoImpl.getConnection();
// 调用SQL
preparedStatement = connection.prepareStatement(sql);
// 参数赋值
if (params != null) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
// 执行
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
object = resultSet.getObject(1);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
close(connection, preparedStatement, resultSet);
}
return object;
}
/**
*
* @Title: findSimpleRefResult 通过反射机制查询单条记录
* @Description: TODO(通过反射机制查询单条记录)
* @param @param sql
* @param @param params
* @param @param cls
* @param @return
* @param @throws Exception 设定文件
* @return T 返回类型
* @throws
*/
public <T> T findSimpleRefResult(String sql, List<Object> params,
Class<T> cls) {
T resultObject = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
int index = 1;
try {
connection = JdbcBaseDaoImpl.getConnection();
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
// 通过反射机制创建一个实例
resultObject = cls.newInstance();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true); // 打开javabean的访问权限
field.set(resultObject, cols_value);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
resultSet.close();
pstmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return resultObject;
}
/**
* 通过反射机制查询多条记录
*
* @param sql
* @param params
* @param cls
* @return
* @throws Exception
*/
public <T> List<T> findMoreRefResult(String sql, List<Object> params,
Class<T> cls) {
List<T> list = new ArrayList<T>();
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
int index = 1;
try {
connection = JdbcBaseDaoImpl.getConnection();
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
// 通过反射机制创建一个实例
T resultObject = cls.newInstance();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true); // 打开javabean的访问权限
field.set(resultObject, cols_value);
}
list.add(resultObject);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
resultSet.close();
pstmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
/**
*
* @Title: find
* @Description: TODO(这里用一句话描述这个方法的作用)
* @param @param sql
* @param @param obj
* @param @param mapper
* @param @return 设定文件
* @return Object 返回类型
* @throws
*/
public Object find(String sql, Object[] obj, ObjectMapper mapper) {
Object o = null;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = JdbcBaseDaoImpl.getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
pstmt.setObject(i + 1, obj[i]);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
o = mapper.mapping(rs);
}
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
pstmt.close();
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return o;
}
/**
*
* @Title: executeQuery 统一的select语句，为了能够访问结果集，将结果集放入ArrayList，这样可以直接关闭资源
* @Description: TODO(统一的select语句，为了能够访问结果集，将结果集放入ArrayList，这样可以直接关闭资源)
* @param @param sql
* @param @param parameters
* @param @return 设定文件
* @return ArrayList 返回类型
* @throws
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public ArrayList executeQuery(String sql, String[] parameters) {
ArrayList results = new ArrayList();
// 定义需要的变量
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 获得连接
conn = JdbcBaseDaoImpl.getConnection();
ps = conn.prepareStatement(sql);
if (parameters != null) {
for (int i = 0; i < parameters.length; i++) {
ps.setString(i + 1, parameters[i]);
}
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int column = rsmd.getColumnCount();
while (rs.next()) {
Object[] objects = new Object[column];
for (int i = 1; i <= column; i++) {
objects[i - 1] = rs.getObject(i);
}
results.add(objects);
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
try {
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return results;
}
/**
*
* @Title: executeQuery by statement
* @Description: TODO(执行 查询sql 获取结果集)
* @param @param sql
* @param @return statement resultSet
* @param @throws SQLException 设定文件
* @return ResultSet 返回类型
* @throws
*/
public ResultSet executeQuery(String sql) {
Statement statement = null;
ResultSet resultSet = null;
Connection connection = null;
try {
connection = getStatement();
if (resultSet != null && resultSet.isClosed() == false) {
resultSet.close();
}
resultSet = null;
resultSet = statement.executeQuery(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
}
return resultSet;
}
//-------------------------------------------对象化---------------
/**
* 将一条记录转成一个对象
*
* @param cls
* 泛型类型
* @param rs
* ResultSet对象
* @return 泛型类型对象
* @throws InstantiationException
* @throws IllegalAccessException
* @throws SQLException
*/
private static <T> T executeResultSet(Class<T> cls, ResultSet rs)
throws InstantiationException, IllegalAccessException, SQLException {
T obj = cls.newInstance();
ResultSetMetaData rsm = rs.getMetaData();
int columnCount = rsm.getColumnCount();
// Field[] fields = cls.getFields();
Field[] fields = cls.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
String fieldName = field.getName();
for (int j = 1; j <= columnCount; j++) {
String columnName = rsm.getColumnName(j);
if (fieldName.equalsIgnoreCase(columnName)) {
Object value = rs.getObject(j);
field.setAccessible(true);
field.set(obj, value);
break;
}
}
}
return obj;
}
//----------------------存储过程调用-------------------------------------------
/**
* 存储过程带有一个输出参数的方法
*
* @param sql
* 存储过程语句
* @param params
* 参数数组
* @param outParamPos
* 输出参数位置
* @param SqlType
* 输出参数类型
* @return 输出参数的值
*/
public Object executeQuery(String sql, Object[] params, int outParamPos,
int SqlType) {
Connection connection = null;
Statement statement = null;
Object object = null;
CallableStatement callableStatement = null;
try {
connection = JdbcBaseDaoImpl.getConnection();
// 调用存储过程
callableStatement = connection.prepareCall(sql);
// 给参数赋值
if (params != null) {
for (int i = 0; i < params.length; i++) {
callableStatement.setObject(i + 1, params[i]);
}
}
// 注册输出参数
callableStatement.registerOutParameter(outParamPos, SqlType);
// 执行
callableStatement.execute();
// 得到输出参数
object = callableStatement.getObject(outParamPos);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
// 释放资源
closeAll(connection, null, callableStatement, null);
}
return object;
}
/**
* 执行不返回结果集的存储过程
*
* @param sql
* 存储过程名称
* @param params
* 存储过程参数
* @throws ClassNotFoundException
* @throws SQLException
*/
public void executeNonQuery(String sql, SqlParameter... params) {
Connection con = null;
CallableStatement cs = null;
try {
con = getConnection();
cs = con.prepareCall(sql);
setSqlParameter(cs, params);
cs.executeUpdate();
getSqlParameter(cs, params);
} catch (Exception e) {
e.printStackTrace();
} finally {
close3(con, cs, null);
}
}
/**
* 执行Insert语句，返回Insert成功之后标识列的值
*
* @param sql
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public int executeIdentity(String sql) {
int identity = -1;
Connection con = null;
Statement ps = null;
ResultSet rs = null;
try {
con = getConnection();
ps = con.createStatement();
ps.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
rs = ps.getGeneratedKeys();
if (rs.next()) {
// identity = rs.getInt("GENERATED_KEYS");
identity = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close3(con, ps, null);
}
return identity;
}
/**
* 执行不返回结果集的存储过程
*
* @param sql
* 存储过程名称
* @param params
* 存储过程参数
* @throws ClassNotFoundException
* @throws SQLException
*/
public void executeNonQuery1(String sql, SqlParameter... params) {
Connection con = null;
CallableStatement cs = null;
try {
con = getConnection();
cs = con.prepareCall(sql);
setSqlParameter(cs, params);
cs.executeUpdate();
getSqlParameter(cs, params);
} catch (Exception e) {
e.printStackTrace();
} finally {
close3(con, cs, null);
}
}
/**
* 执行返回聚合函数的操作
*
* @param sql
* 含有聚合函数的SQL语句
* @return 聚合函数的执行结果
* @throws SQLException
* @throws ClassNotFoundException
*/
public int executeScalar(String sql) {
int result = -1;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = getConnection();
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
if (rs.next()) {
result = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close3(con, ps, rs);
}
return result;
}
/**
* 执行返回泛型集合的SQL语句
*
* @param cls
* 泛型类型
* @param sql
* 查询SQL语句
* @return 泛型集合
* @throws ClassNotFoundException
* @throws SQLException
* @throws InstantiationException
* @throws IllegalAccessException
*/
public <T> List<T> executeList(Class<T> cls, String sql) {
List<T> list = new ArrayList<T>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = getConnection();
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
T obj = executeResultSet(cls, rs);
list.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close3(con, ps, rs);
}
return list;
}
/**
* 执行返回泛型集合的存储过程
*
* @param cls
* 泛型类型
* @param sql
* 存储过程名称
* @param params
* 存储过程参数
* @return 泛型集合
* @throws ClassNotFoundException
* @throws SQLException
* @throws InstantiationException
* @throws IllegalAccessException
*/
public <T> List<T> executeList(Class<T> cls, String sql,
SqlParameter... params) {
List<T> list = new ArrayList<T>();
Connection con = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
con = getConnection();
cs = con.prepareCall(sql);
setSqlParameter(cs, params);
rs = cs.executeQuery();
while (rs.next()) {
T obj = executeResultSet(cls, rs);
list.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close3(con, cs, rs);
}
return list;
}
/**
* 执行返回泛型类型对象的SQL语句
*
* @param cls
* 泛型类型
* @param sql
* SQL语句
* @return 泛型类型对象
* @throws SQLException
* @throws ClassNotFoundException
* @throws InstantiationException
* @throws IllegalAccessException
*/
public <T> T executeEntity(Class<T> cls, String sql) {
T obj = null;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = getConnection();
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
obj = executeResultSet(cls, rs);
break;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close3(con, ps, rs);
}
return obj;
}
/**
* 执行返回泛型类型对象的存储过程
*
* @param cls
* 泛型类型
* @param sql
* SQL语句
* @param params
* 存储过程参数
* @return 泛型类型对象
* @throws SQLException
* @throws ClassNotFoundException
* @throws InstantiationException
* @throws IllegalAccessException
*/
public <T> T executeEntity(Class<T> cls, String sql,
SqlParameter... params) {
T obj = null;
Connection con = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
con = getConnection();
cs = con.prepareCall(sql);
setSqlParameter(cs, params);
rs = cs.executeQuery();
while (rs.next()) {
obj = executeResultSet(cls, rs);
break;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close3(con, cs, rs);
}
return obj;
}
/**
* 设置存储过程参数名称，参数值，参数方向
*
* @param cs
* @param params
* @throws SQLException
*/
private void setSqlParameter(CallableStatement cs,
SqlParameter... params) throws SQLException {
if (params != null) {
for (SqlParameter param : params) {
if (param.OutPut) {
String paramName = param.Name;
if (paramName == null || paramName.equals("")) {
cs.registerOutParameter(1, param.Type);// 设置返回类型参数
} else {
cs.registerOutParameter(paramName, param.Type);// 设置输出类型参数
}
} else {
cs.setObject(param.Name, param.Value);// 设置输入类型参数
}
}
}
}
/**
* 得到存储过程参数执行结果
*
* @param cs
* @param params
* @throws SQLException
*/
private void getSqlParameter(CallableStatement cs,
SqlParameter... params) throws SQLException {
for (SqlParameter param : params) {
if (param.OutPut) {
String paramName = param.Name;
if (paramName == null || paramName.equals("")) {
param.Value = cs.getObject(1);// 返回类型参数值
} else {
param.Value = cs.getObject(paramName);// 输出类型参数值
}
}
}
}
/**
*
* @Title: executeUpdate by statement
* @Description: TODO(更新结果集)
* @param @param sql
* @param @return
* @param @throws SQLException 设定文件
* @return int 返回类型
* @throws
*/
@SuppressWarnings("null")
public int executeUpdate(String sql) {
Statement statement = null;
Connection connection = null;
int result = 0;
try {
connection = getStatement();
result = statement.executeUpdate(sql);
connection.commit();//JDBC 事务管理
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();//JDBC 事务管理
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
try {
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
//-----------------------资源关闭---------------------------------------------
/**
* 关闭所有资源
*
* @param statement
*/
private void closeAll(Connection connection,
PreparedStatement preparedStatement,
CallableStatement callableStatement, ResultSet resultSet) {
// 关闭结果集对象
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
// 关闭PreparedStatement对象
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
// 关闭CallableStatement 对象
if (callableStatement != null) {
try {
callableStatement.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
// 关闭Connection 对象
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
/**
*
* @Title: close
* @Description: TODO(关闭所有的连接)
* @param @throws SQLException 设定文件
* @return void 返回类型
* @throws
*/
public void close(Connection connection, Statement statement,
ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
resultSet = null;
}
if (statement != null) {
statement.close();
statement = null;
}
if (connection != null) {
connection.close();
connection = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 关闭JDBC对象，释放资源。
*
* @param con
* 连接对象
* @param ps
* 命令对象
* @param rs
* 结果集对象
* @throws SQLException
*/
private static void close3(Connection con, Statement ps, ResultSet rs) {
try {
rs.close();
if (rs != null) {
rs = null;
}
if (ps != null) {
ps.close();
ps = null;
}
if (con != null) {
con.close();
con = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//--------------------main 方法----------------------------------------------
/**
* 测试 连接是否成功
*
* @param args
*/
public static void main(String[] args) {
getConnection();
}
}
-------------------------------------------------------------------------------------------------------------------
以下是 测试demo:
-------------------------------------------------------------------------------------------------------
package light.mvc.service.demo;
import java.util.List;
import java.util.Map;
import light.mvc.framework.connectUtil.jdbcUtl.PageModel;
import light.mvc.pageModel.demo.Demo;
public interface JdbcDemoService {
public boolean insert(Demo demo); //增
public boolean delete(String id); //单条删除
public boolean delete(String[] Ids); //批量删除
public boolean update(Demo demo); //修改
public List<Demo> query(); //全部查询
public Demo queryfrist(); //单记录查询
public PageModel query(int pageNo, int pageSize); //分页查询
public PageModel queryLike(int pageNo, int pageSize);//分页模糊查询
public Map<String, Object> findSimpleResult(String sql, List<Object> params);
public List<Map<String, Object>> findModeResult();//获取多条数据记录
public List<Map<String, Object>> findModeResult1();//获取多条数据记录
public Object executeQuerySingle();//查询将查询结果：一行一列
public Demo findSimpleRefResult();//通过反射机制查询单条记录
public List<Demo> findMoreRefResult();//通过反射机制查询多条记录
public Demo find();
}
---------------------------------------------------------------------------------------------------------------
package light.mvc.service.demo.impl;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import light.mvc.dao.JdbcBaseDao;
import light.mvc.dao.JdbcDao;
import light.mvc.dao.impl.JdbcDaoImpl;
import light.mvc.framework.connectUtil.jdbcUtl.ObjectMapper;
import light.mvc.framework.connectUtil.jdbcUtl.PageModel;
import light.mvc.pageModel.demo.Demo;
import light.mvc.service.demo.JdbcDemoService;
@Service
public class JdbcDemoServiceImpl implements JdbcDemoService{
@Autowired
private JdbcDao jdbcUtil ;
@Autowired
private JdbcBaseDao jdbcBaseUtil ;
/**
* 新增数据
*/
@Override
public boolean insert(Demo demo) {
String sql = "insert into demo(id,name,description) values(?,?,?)";
Object[] obj = {demo.getId(),demo.getName(),demo.getDescription()};
return jdbcUtil.insert(sql, obj);
}
public boolean insert() {
long start=221008;
String sql = "insert into demo(id,name,description) values(?,?,?)";
List<Object> list=new ArrayList<Object>();
list.add(start);
list.add("name"+start);
list.add("description"+start);
return jdbcUtil.insertByList(sql, list);
}
public int insertLines() {
long start=221009;
String sql = "insert into demo(id,name,description) values(?,?,?)";
Object[] obj={start,"name"+start,"description"+start};
return jdbcUtil.insertLines(sql, obj);
}
/**
* 删除数据
*/
@Override
public boolean delete(String id) {
String sql = "delete from demo where id=?";
Object[] obj = { id };
return jdbcUtil.delete(sql, obj);
}
/**
* 批量删除数据
*/
@Override
public boolean delete(String[] Ids) {
Object[] obj = Ids;
String sql = "delete from demo where id in";
return jdbcUtil.deleteALL(sql, obj);
}
/**
* 更新单条记录
*/
@Override
public boolean update(Demo demo) {
String sql = "update demo set name=?,description=? where id=?";
Object[] obj = {demo.getName(),demo.getDescription(),demo.getId()};
return jdbcUtil.update(sql, obj);
}
/**
* 查询所有的数据记录；并以list 集合返回
*/
@Override
public List<Demo> query() {
String sql = "select * from demo";
Object[] obj = {};
return (List<Demo>) jdbcUtil.query(sql, obj, new DemoMapper());
}
/**
* 查询第一条数据
*/
@Override
public Demo queryfrist() {
String tale = "demo";
Object[] obj = {};
return (Demo) jdbcUtil.queryFrist(tale, obj);
}
/**
* 分页传递参数查询
*/
@Override
public PageModel query(int pageNo, int pageSize) {
String table = " demo a ,demo b where a.id=b.id ";
return jdbcUtil.queryPageNumber(pageNo, pageSize, table);
}
/**
* 模糊分页查询
*/
@Override
public PageModel queryLike(int pageNo, int pageSize) {
int test=1;
String table = " demo a ,demo b where a.id=b.id AND a.id LIKE '1%' ";
System.out.println(table);
return jdbcUtil.queryPageNumber(pageNo, pageSize, table);
}
/**
* 获取最后一条（单条）记录
*/
@Override
public Map<String, Object> findSimpleResult(String sql, List<Object> params) {
return jdbcUtil.findSimpleResult( sql, params);
}
/**
* 查询多条记录
*/
@Override
public List<Map<String, Object>> findModeResult(){
String str="";
List<Object> list=new ArrayList<Object>();
for(int i=1;i<10000;i++){
list.add(""+i+"");
int ii=4*i;
i=ii;
}
for(int j=0;j<list.size();j++){
if(j==0){
str =str.trim()+"?";
}else if(j!=0){
str =str.trim()+",?";
}
}
String sql="select * from demo where id in("+str+")";
return jdbcUtil.findModeResult(sql,list);
}
/**
* 查询多条记录
*/
@Override
public List<Map<String, Object>> findModeResult1(){
List<Object> list=new ArrayList<Object>();
String sql="select * from demo ";
return jdbcUtil.findModeResult(sql,list);
}
@Override
//查询将查询结果：一行一列
public Object executeQuerySingle(){
String sql="select * from demo where id in(?,?,?)";
Object[] params={"1","2","3"};
return jdbcUtil.executeQuerySingle(sql, params);
}
@Override
public Demo findSimpleRefResult(){
Demo demo=new Demo();
String sql="select * from demo where id = ?";
List<Object> list=new ArrayList<Object>();
list.add("1");
demo=jdbcBaseUtil.findSimpleRefResult(sql, list, Demo.class);
return demo;
}
/**
* 通过反射机制查询多条记录
*/
@Override
public List<Demo> findMoreRefResult() {
List<Demo> demoList=new ArrayList<Demo>();
String sql="select * from demo ";
List<Object> list=new ArrayList<Object>();
demoList=jdbcUtil.findMoreRefResult(sql, list, Demo.class);
return demoList;
}
public Demo find(){
Demo demo=new Demo();
String sql="select * from demo where id = ?";
Object[] params={"1"};
return (Demo) jdbcUtil.find(sql, params, new DemoMapper());
}
public List<Object> excuteQueryForOne(){
List<Object> list=new ArrayList<Object>();
String sql="select * from demo where id = ?";
Object[] params={"1"};
list=jdbcUtil.excuteQuery(sql, params);
return list;
}
public ArrayList executeQuery(){
ArrayList list=new ArrayList();
String sql="select * from demo ";
String[] params={};
list=jdbcUtil. executeQuery(sql, params);
return list;
}
}
----------------------------------------------------------------------------------------------------------------
package light.mvc.service.demo.impl;
import java.sql.ResultSet;
import light.mvc.framework.connectUtil.jdbcUtl.ObjectMapper;
import light.mvc.pageModel.demo.Demo;
public class DemoMapper implements ObjectMapper{
public Object mapping(ResultSet rs){
Demo d=new Demo();
try{
d.setId(Long.parseLong(rs.getString("id")));
d.setName(rs.getString("name"));
d.setDescription(rs.getString("description"));
}catch(Exception ex){
ex.printStackTrace();
}
return d;
}
}
------------------------------------------------------------------------------------------
package light.mvc.service.demo.impl;
import java.sql.ResultSet;
import light.mvc.framework.connectUtil.jdbcUtl.ObjectMapper;
import light.mvc.pageModel.demo.Demo;
public class DemoMapper implements ObjectMapper{
public Object mapping(ResultSet rs){
Demo d=new Demo();
try{
d.setId(Long.parseLong(rs.getString("id")));
d.setName(rs.getString("name"));
d.setDescription(rs.getString("description"));
}catch(Exception ex){
ex.printStackTrace();
}
return d;
}
}
---------------------------------------------------------------------------------------------
测试数据库表：
DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
`id` bigint(11) NOT NULL auto_increment,
`name` varchar(64) NOT NULL,
`description` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='DEMO';
insert into demo(name,description) values("demo","这是一个演示demo");
insert into demo(name,description) values("demo","这是一个演示demo");
insert into demo(name,description) values("demo","这是一个演示demo");
insert into demo(name,description) values("demo","这是一个演示demo");
insert into demo(name,description) values("demo","这是一个演示demo");
insert into demo(name,description) values("demo","这是一个演示demo");
insert into demo(name,description) values("demo","这是一个演示demo");
insert into demo(name,description) values("demo","这是一个演示demo");
insert into demo(name,description) values("demo","这是一个演示demo");
insert into demo(name,description) values("demo","这是一个演示demo");