package com.just.zb.spring.jdbc;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public abstract class AbstractExceute {
private JdbcTemplate template;
private DataSource dataSource;
public JdbcTemplate getTemplate() {
return template;
}
public void setTemplate(JdbcTemplate template) {
this.template = template;
}
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public abstract void executeSql(String sql, List params) throws Exception;
public abstract void executeSqls(String []sqls, List<List> params) throws Exception;
public abstract void executeBatchSql(String sql, List<List> params) throws Exception;
public abstract int getAllCount(String sql, List params) throws Exception;
public abstract List getList(String sql,List params ,Class clazz) throws Exception;
public abstract List processStoredProcedure(final String procedure ,final Map<Integer , Object> inParams , final Map<Integer , Integer> outParams ) throws Exception ;
}
实现类:
package com.just.zb.spring.jdbc;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.CallableStatementCallback;
public class Execute extends AbstractExceute{
protected static Logger log = Logger.getLogger(Execute.class);
@Override
public void executeBatchSql(final String sql, final List<List> params)
throws Exception {
BatchPreparedStatementSetter pss = null;
pss = new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement pstmt, int ord) throws SQLException {
List param = params.get(ord);
for (int i = 0; i < (null != param ? param.size() : 0); i++) {
pstmt.setObject(i + 1, param.get(i));
}
}
public int getBatchSize() {
return 3;
}
};
getTemplate().batchUpdate(sql, pss);
}
@Override
public void executeSql(String sql, List params) throws Exception {
getTemplate().update(sql, params.toArray());
}
@Override
public void executeSqls(String []sqls, List<List> params) throws Exception {
for(int i=0;i<sqls.length;i++){
getTemplate().update(sqls[i], ((List)params.get(i)).toArray());
}
}
@Override
public int getAllCount(String sql, List params) throws Exception {
return getTemplate().queryForInt(sql,params.toArray());
}
@Override
public List getList(String sql, List params, Class clazz) throws Exception {
List list = getTemplate().queryForList(sql, params.toArray());
return list;
}
public List processStoredProcedure(final String procedure ,final Map<Integer , Object> inParams , final Map<Integer , Integer> outParams ) throws Exception {
CallableStatementCallback cb = new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException {
Set outSet = null;
if(outParams != null && outParams.size()!= 0){
outSet = outParams.entrySet();
for (Iterator iter = outSet.iterator(); iter.hasNext();) {
Entry<Integer , Integer> entry = (Entry <Integer , Integer>)iter.next();
cs.registerOutParameter(entry.getKey(),entry.getValue());
log.info("config one out put paramter , index: "+ entry.getKey() + " value :" + getTypesString(entry.getValue()));
}
}
if(inParams != null && inParams.size()!= 0){
Set inSet = inParams.entrySet();
for (Iterator iter = inSet.iterator(); iter.hasNext();) {
Entry<Integer , Object> entry = (Entry <Integer , Object>)iter.next();
cs.setObject(entry.getKey(),entry.getValue());
log.info("config one in put paramter , index: "+ entry.getKey() + " value :" + entry.getValue() );
}
}
cs.execute();
List list = new ArrayList();
if(outParams != null && outParams.size()!= 0){
for (Iterator iter = outSet.iterator(); iter.hasNext();) {
Entry<Integer , Integer> entry = (Entry <Integer , Integer>)iter.next();
Object obj = cs.getObject(entry.getKey());
list.add(obj);
log.info("get one out put value , index: "+ entry.getKey() + " value :" + obj);
}
}
return list;
}
};
return (List)getTemplate().execute(" "+procedure+" ", cb);
}
public String getTypesString(int type) {
Class clazz = Types.class ;
Field [] fields = clazz.getFields();
for(int i=0; i< fields.length ; i++){
try {
if(fields[i].getInt(clazz) == type){
return fields[i].getName();
}
} catch (IllegalArgumentException e) {
} catch (IllegalAccessException e) {
}
}
return null;
}
public static void main(String[] args) {
System.out.println(new Execute().getTypesString(1));
}
}
测试类:
package com.just.zb.spring.jdbc;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;
import org.logicalcobwebs.proxool.admin.SnapshotIF;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
public class TestSpringJDBC implements Runnable{
private static final Log LOG = LogFactory.getLog(TestSpringJDBC.class);
private static BeanFactory beans ;
public Execute getExceuteMgr() {
return ( Execute ) beans.getBean("execute");
}
public void testInsert( ) throws Exception {
Execute exeMgr = this.getExceuteMgr();
exeMgr.executeSql("insert into person values(?,?,?,?,?)", Arrays.asList("testGO",20,1,"test@163.com","ZZ"));
}
public void testUpdate( ) throws Exception {
Execute exeMgr = this.getExceuteMgr();
exeMgr.executeSql("update person set name = ? where name like ?" , Arrays.asList("testGOGO","%testGO%"));
}
public void testDelete( ) throws Exception {
Execute exeMgr = this.getExceuteMgr();
exeMgr.executeSql("DELETE FROM PERSON where name LIKE ?" , Arrays.asList("%testGO%"));
}
public void testExcSqls( ) throws Exception {
Execute exeMgr = this.getExceuteMgr();
String sqls[ ] = new String[2];
List<List> params = new ArrayList<List>();
sqls[0] = "update person set name = ? where name = ?";
params.add(Arrays.asList("testGOGO","testGO"));
sqls[1] = "insert into person values(?,?,?,?,?)" ;
params.add(Arrays.asList("test1",20,1,"test@163.com","ZZ"));
exeMgr.executeSqls(sqls, params);
}
public void testGetList() throws Exception{
Execute exeMgr = this.getExceuteMgr();
List list = exeMgr.getList("SELECT * FROM PERSON", Arrays.asList(), null);
for(int i=0 ; i<list.size() ; i++){
Map map =(Map) list.get(i);
Person p = new Person ();
RefUtil.map2obj(Person.class, map, p );
System.out.println(p);
}
}
public void testexeProNotParams() throws Exception{
Execute exeMgr = this.getExceuteMgr();
Object obj = exeMgr.processStoredProcedure("exec testPro ", new HashMap<Integer, Object>() , new HashMap<Integer, Integer>());
System.out.println(obj);
}
public void testexeProByOut() throws Exception{
Execute exeMgr = this.getExceuteMgr();
Map<Integer , Integer> out = new HashMap<Integer, Integer>();
out.put(1, Types.VARCHAR);
Object obj = exeMgr.processStoredProcedure("exec testPro1 ? ", new HashMap<Integer, Object>() , out);
System.out.println(obj);
}
public void testexeProByOutIn() throws Exception{
Execute exeMgr = this.getExceuteMgr();
Map<Integer , Object> in = new HashMap<Integer, Object>();
in.put(1, "testOUTParam");
Map<Integer , Integer> out = new HashMap<Integer, Integer>();
out.put(2, Types.VARCHAR);
List obj = exeMgr.processStoredProcedure(" exec testPro2 ? , ? ", in , out);
System.out.println(obj);
}
public static void test() {
TestSpringJDBC jdbc = new TestSpringJDBC();
jdbc.parsexml();
Execute exeMgr = jdbc.getExceuteMgr();
try {
for(int i=0 ;i< 20 ; i++){
System.out.println(i + " "+ exeMgr.getList("select * from person", Arrays.asList(), null));
Thread.sleep(100);
}
} catch (Exception e) {
e.printStackTrace();
}
/* try {
jdbc.testUpdate( );
} catch (Exception e) {
e.printStackTrace();
}
*/
/*try {
jdbc.testDelete();
} catch (Exception e) {
e.printStackTrace();
}*/
/*
try {
jdbc.testInsert();
} catch (Exception e) {
e.printStackTrace();
}
*/
/*try {
jdbc.testExcSqls();
} catch (Exception e) {
e.printStackTrace();
}*/
/* try {
jdbc.testGetList();
} catch (Exception e) {
e.printStackTrace();
}*/
/* try{
jdbc.testexeProByOutIn();
}catch(Exception e){
e.printStackTrace();
} */
}
public static void parsexml() {
Resource resource = new ClassPathResource("springbeans.xml");
beans = new XmlBeanFactory(resource);
}
/**
* 此方法可以得到连接池的信息 showSnapshotInfo
*/
private void showSnapshotInfo() {
try {
SnapshotIF snapshot = ProxoolFacade.getSnapshot("mssql", true);
int curActiveCount = snapshot.getActiveConnectionCount();// 获得活动连接数
System.out.println(curActiveCount);
int availableCount = snapshot.getAvailableConnectionCount();// 获得可得到的连接数
int maxCount = snapshot.getMaximumConnectionCount();// 获得总连接数
if (curActiveCount != activeCount)// 当活动连接数变化时输出的信息
{
System.out.println("活动连接数:" + curActiveCount
+ "(active) 可得到的连接数:" + availableCount
+ "(available) 总连接数:" + maxCount + "(max)");
activeCount = curActiveCount;
}
} catch (ProxoolException e) {
e.printStackTrace();
}
}
private static int activeCount = -1;
public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
private static Date d= new Date(Calendar.getInstance().getTimeInMillis() + 1000*3);
public void run() {
try {
//mgr.myWait();
throw new Exception ("test");
// this.testInsert();
// this.testUpdate();
} catch (Exception e) {
LOG.info(" 执行任务，时间：" + sdf.format(new Date()) , e);
LOG.error(" 执行任务，时间：" + sdf.format(new Date()) , e);
e.printStackTrace();
}
}
private static ThreadManager mgr = new ThreadManager();
public static void main(String[] args) {
parsexml();
LOG.info(" 计划执行时间：" + sdf.format(d));
new Thread(){
public void run(){
while(true){
Date c = new Date();
if(c.after(d)){
// mgr.myNotify();
break;
}
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
}
}
}
}.start();
for (int i = 0; i< 10 ; ++i ) {
TestSpringJDBC jdbc = new TestSpringJDBC();
new Thread(jdbc).start();
try {
Thread.sleep(100);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
}
applicationcontext.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> <beans> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg> <ref bean="datasource" /> </constructor-arg> </bean> <bean id="datasource_test" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" /> <property name="url" value="jdbc:sqlserver://localhost;databaseName=ICEtest" /> <property name="username" value="sa" /> <property name="password" value="access" /> </bean> <bean id="datasource" class="org.logicalcobwebs.proxool.ProxoolDataSource" destroy-method="close"> <property name="driver"> <value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value> </property> <property name="driverUrl"> <value>jdbc:sqlserver://localhost:1433;databaseName=ICEtest;user=sa;password=access</value> </property> <property name="user"> <value>sa</value> </property> <property name="password"> <value>access</value> </property> <property name="alias"> <value>mssql</value> </property> <property name="houseKeepingSleepTime"> <value>90000</value> </property> <property name="prototypeCount"> <value>50</value> </property> <property name="maximumConnectionCount"> <value>5000</value> </property> <property name="minimumConnectionCount"> <value>1000</value> </property> <property name="simultaneousBuildThrottle" > <value>2000</value> </property> <property name="trace"> <value>true</value> </property> </bean> <bean id="abstractExecute" class="com.just.zb.spring.jdbc.AbstractExceute" abstract="true"> <property name="template" ref="jdbcTemplate"></property> <property name="dataSource" ref="datasource"></property> </bean> <bean id="execute" class="com.just.zb.spring.jdbc.Execute" parent="abstractExecute" /> </beans>
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
