package nut.jade.db;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;
import nut.jade.db.DBCPPool;
import nut.jade.db.ConfigBean;
/**
* 数据库操作类
*
* @author nut.jade
*
*/
@SuppressWarnings("unused")
public class DBUtil {
/**
* jdbc 操作类
*/
private JdbcTemplate jdbcTemplate;
/**
* 事务管理模版
*/
private TransactionTemplate transactionTemplate;
/**
* 数据源
*/
private DataSource ds;
public DBUtil() {
ds = getDataSource();
jdbcTemplate = new JdbcTemplate(ds);
// // 事务处理
PlatformTransactionManager platformTransactionManager = new DataSourceTransactionManager(
ds);
transactionTemplate = new TransactionTemplate(
platformTransactionManager);
transactionTemplate.afterPropertiesSet();
transactionTemplate
.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
}
/**
* 不带事务处理,批量更新
*
* @param sql
* @param obj
*/
public boolean batchUpdate(String sql, final List<Object[]> objs) {
try {
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i)
throws SQLException {
Object[] obj = objs.get(i);
if (obj != null) {
for (int j = 0; j < obj.length; j++) {
ps.setObject(j + 1, obj[j]);
}
}
}
public int getBatchSize() {
return objs.size();
}
});
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
return true;
}
/**
* 事务处理,批量更新List<String> String是SQL语句
*
* @return
*/
public boolean batchUpdate(final List<String> sqlList) {
try {
transactionTemplate.execute(new TransactionCallback() {
public Object doInTransaction(TransactionStatus status) {
for (String sql : sqlList) {
jdbcTemplate.update(sql);
}
return null;
}
});
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 事务处理,批量更新
* Map<String,Object[]> String是SQL语句带占位符,Object[]是占位符值
*
* @param map
* Key值 是不允许重复为SQL
* @return
*/
public boolean batchUpdate(final Map<String, Object[]> sqlMap) {
try {
transactionTemplate.execute(new TransactionCallback() {
public Object doInTransaction(TransactionStatus status) {
Set<String> keys = sqlMap.keySet();
for (String sql : keys) {
jdbcTemplate.update(sql, sqlMap.get(sql));
}
return null;
}
});
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 事务处理,批量更新
* Object[][] 允許重復 二维数组,第一列为SQL语句,第二列为Object[]占位符值
*
* @param map
* @return
*/
public boolean batchUpdate(final Object[][] sqlArr) {
try {
transactionTemplate.execute(new TransactionCallback() {
public Object doInTransaction(TransactionStatus status) {
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i][0].toString();
Object[] obj = (Object[]) sqlArr[i][1];
jdbcTemplate.update(sql, obj);
}
return null;
}
});
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 批量更新
*
* @param sqls
* @return
*/
public int[] batchUpdate(String[] sqls) {
int[] ints = jdbcTemplate.batchUpdate(sqls);
return ints;
}
/**
* 获取数据源
*
* @return
*/
private DataSource getDataSource() {
String dsType = ConfigBean.getProperty(ConfigBean.JDBC_POOL_TYPE);
if (dsType.equals(ConfigBean.DBCPPool)) {
return DBCPPool.getDataSource();
}
String jndi = ConfigBean.getProperty(ConfigBean.JDBC_JNDI);
InitialContext ctx;
DataSource ds = null;
try {
ctx = new InitialContext();
ds = (DataSource) ctx.lookup(jndi);
} catch (NamingException e) {
e.printStackTrace();
}
return ds;
}
}