采用c3p0数据库连接池底层是jdbc的数据库的增删改查
1.新建dbutils包，里面是JdbcUtils类：
package cn.com.xxx.xxx.dbutil; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class JdbcUtils { /* *释放数据库的连接 * */ public static void releaseConnection(Connection connection){ try { if(connection !=null){ connection.close(); } } catch (Exception e) { e.printStackTrace(); } } private static DataSource dataSource=null; static { /* * 数据源只能被创建一次 */ dataSource=new ComboPooledDataSource("stormTest"); } public static Connection getConnection() throws SQLException{ //dataSource=new ComboPooledDataSource("mvcapp");若要是放在这里就说明，获得一次就创建一下，比较耗费内存 return dataSource.getConnection(); } }
2.c3p0的配置文件:c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <named-config name="stormTest"> <property name="user">root</property> <property name="password">12345678</property> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/ies?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8</property> <property name="acquireIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">10</property> <property name="maxPoolSize">50</property> <property name="maxStatements">20</property> <property name="maxStatementsPerConnection">5</property> </named-config> </c3p0-config>
rewriteBatchedStatements=true 这个是配置mysql数据库批量处理（批量插入，删除，更新）的一个配置项
<named-config name="stormTest"> 中的stormTest,要跟JdbcUtils.java中的
dataSource=new ComboPooledDataSource("stormTest"); 构造函数的参数一致
3.写基本的Dao:(包含QueryRunner的批处理方法)
package cn.com.sgcc.gdt.dao; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.sql.Connection; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import cn.com.sgcc.gdt.dbutil.JdbcUtils; public class Dao<T> { private QueryRunner queryRunner=new QueryRunner(); private Class<T> clazz; public Dao(){ Type superClass=getClass().getGenericSuperclass(); if(superClass instanceof ParameterizedType){ ParameterizedType parameterizedType= (ParameterizedType) superClass; Type[] typeArgs=parameterizedType.getActualTypeArguments(); if(typeArgs!=null&&typeArgs.length>0){ if(typeArgs[0] instanceof Class){ clazz=(Class<T>) typeArgs[0]; } } } } /* *这个方法是只获取，表格中的某一行属性中的某一个属性值，就是返回某一个字段的值，例如返回某一条记录的customerName，或者返回数据表中有多少条记录等。 */ public <E> E getForValue(String sql,Object ... args){ Connection connection=null; try {connection=JdbcUtils.getConnection(); return (E) queryRunner.query(connection, sql, new ScalarHandler(), args); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils.releaseConnection(connection); } return null; } /* * 该方法是返回一组数据对象实体类，返回T所对应的List，其实质就是查询 */ public List<T> getForList(String sql,Object ... agrs ){ Connection connection=null; try {connection=JdbcUtils.getConnection(); return queryRunner.query(connection, sql, new BeanListHandler<T>(clazz), agrs); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils.releaseConnection(connection); } return null; } /*执行查询的语句，结果是返回数据表中的一个对象（一条记录）其实质就是查询！ */ public T get(String sql,Object ... agrs){ Connection connection=null; try {connection=JdbcUtils.getConnection(); return queryRunner.query(connection, sql, new BeanHandler<T>(clazz), agrs); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils.releaseConnection(connection); } return null; } /* * 该方法封装了INSERT、UPDATE、DELETE操作 * sql是sql语句 * args是占位符 * result 1:代表操作成功; 0:代表操作失败 */ public int update(String sql,Object ... args){ Connection connection=null; int result =0; try {connection=JdbcUtils.getConnection(); result=queryRunner.update(connection, sql, args); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils.releaseConnection(connection); } return result; } /** * 批处理 * @param sql * @param args :二维数组,高维确定执行sql语句的次数，低维是给？赋值 * @return */ public void batchUpdate(String sql,Object[][] args){ Connection connection = null; //int result = 0; try {connection=JdbcUtils.getConnection(); queryRunner.batch(connection, sql, args); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils.releaseConnection(connection); } } }
4.写要查询结果的封装对象javabean，注意：Javabean中的属性需要和查询的表的属性一致(JDBC没有rowmapper,所以对于数据别名可以在sql查询语句中 as ‘新的表头名字’)
package cn.com.sgcc.gdt.bean; import java.io.Serializable; import java.util.Date; /** * 当前值(update) * @author Joy * */ public class StatusCurrent implements Serializable{ /** * */ private static final long serialVersionUID = 1L; /**监测对象编码*/ private String MS_NO; /**指标编码*/ private String INDI_NO; /**数据类型*/ private String DATA_TYPE; /**数据刷新时间*/ private Date REFRESH_TIME; /**数据值*/ private Double RVAL; public String getMS_NO() { return MS_NO; } public void setMS_NO(String mS_NO) { MS_NO = mS_NO; } public String getINDI_NO() { return INDI_NO; } public void setINDI_NO(String iNDI_NO) { INDI_NO = iNDI_NO; } public String getDATA_TYPE() { return DATA_TYPE; } public void setDATA_TYPE(String dATA_TYPE) { DATA_TYPE = dATA_TYPE; } public Date getREFRESH_TIME() { return REFRESH_TIME; } public void setREFRESH_TIME(Date rEFRESH_TIME) { REFRESH_TIME = rEFRESH_TIME; } public Double getRVAL() { return RVAL; } public void setRVAL(Double rVAL) { RVAL = rVAL; } public StatusCurrent() { super(); } @Override public String toString() { return "StatusCurrent [MS_NO=" + MS_NO + ", INDI_NO=" + INDI_NO + ", DATA_TYPE=" + DATA_TYPE + ", REFRESH_TIME=" + REFRESH_TIME + ", RVAL=" + RVAL + "]"; } public StatusCurrent(String mS_NO, String iNDI_NO, String dATA_TYPE, Date rEFRESH_TIME, Double rVAL) { super(); MS_NO = mS_NO; INDI_NO = iNDI_NO; DATA_TYPE = dATA_TYPE; REFRESH_TIME = rEFRESH_TIME; RVAL = rVAL; } }
5.写某个表进行增删改查的dao接口:
package cn.com.sgcc.gdt.dao; import java.util.List; import cn.com.sgcc.gdt.bean.StatusCurrent; public interface StatusCurrentDao { public int update(StatusCurrent sd); public int save(StatusCurrent sd); public void batchUpdate(List<StatusCurrent> sds); public void batchSave(List<StatusCurrent> sds); }
6.5中接口的实现类（批量插入和批量删除，批量更新的代码）：
package cn.com.sgcc.gdt.daoImpl; import java.util.List; import cn.com.sgcc.gdt.bean.StatusCurrent; import cn.com.sgcc.gdt.dao.Dao; import cn.com.sgcc.gdt.dao.StatusCurrentDao; public class StatusCurrentDaoJdbcImpl extends Dao<StatusCurrent> implements StatusCurrentDao { @Override public int update(StatusCurrent sc) { String sql ="update r_status_curr set data_type=?,rval=?,refresh_time=? where ms_no=? and indi_no=?"; return update(sql,sc.getDATA_TYPE(),sc.getRVAL(),sc.getREFRESH_TIME(),sc.getMS_NO(),sc.getINDI_NO()); } @Override public int save(StatusCurrent sd) { String sql = "insert into r_status_curr(ms_no,data_type,indi_no,rval,refresh_time) values(?,?,?,?,?)"; return update(sql,sd.getMS_NO(),sd.getDATA_TYPE(),sd.getINDI_NO(),sd.getRVAL(),sd.getREFRESH_TIME()); } @Override public void batchUpdate(List<StatusCurrent> sds) { String sql ="update r_status_curr set rval=? where ms_no=? and indi_no=? "; Object[][] params = new Object[sds.size()][]; for (int i = 0; i < sds.size(); i++) { params[i] = new Object[]{sds.get(i).getRVAL(),sds.get(i).getMS_NO(),sds.get(i).getINDI_NO()}; } batchUpdate(sql, params); } @Override public void batchSave(List<StatusCurrent> sds) { String sql = "insert into r_status_curr(ms_no,data_type,indi_no,rval,refresh_time) values(?,?,?,?,?)"; Object[][] params = new Object[sds.size()][]; for (int i = 0; i < sds.size(); i++) { params[i] = new Object[]{sds.get(i).getMS_NO(),sds.get(i).getDATA_TYPE(),sds.get(i).getINDI_NO(),sds.get(i).getRVAL(),sds.get(i).getREFRESH_TIME()}; } batchUpdate(sql, params); } /*@Override public void batchUpdate(List<StatusCurrent> sds) { String sql ="replace into r_status_curr (ms_no,indi_no,data_type,rval,refresh_time) values (?,?,?,?,?)"; Object[][] params = new Object[sds.size()][]; for (int i = 0; i < sds.size(); i++) { params[i] = new Object[]{sds.get(i).getMS_NO(),sds.get(i).getINDI_NO(),sds.get(i).getDATA_TYPE(),sds.get(i).getRVAL(),sds.get(i).getREFRESH_TIME()}; } batchUpdate(sql, params); }*/ }
7.普通调用:
private static StatusCurrentDao statusCurrentDao = new StatusCurrentDaoJdbcImpl();
statusCurrentDao.方法名字()就可以使用了
