import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry; import org.slf4j.Logger; public abstract class DataBaseDaoAbstract<T> { private static Logger logger = CocoLoggerFactory.getLogger(DataBaseDaoAbstract.class); protected T execQueryObject(String sql, Object ...params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
if(result.next()) {
T t = this.parse(result);
return t;
}
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
return null;
} protected T execQueryObject(String sql, Collection<Object> params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
if(result.next()) {
T t = this.parse(result);
return t;
}
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
return null;
} protected <K> K execQueryObject(String sql, Class<K> clazz, Object ...params) throws Exception {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
if(result.next()) {
if(this.isSimpleClass(clazz)) {
return (K) this.getValue(result, clazz, 1);
}
K instance = clazz.newInstance();
Map<Integer, Method> columnMethodMap = this.generateMethodMap(clazz, result.getMetaData());
for(Entry<Integer, Method> entry : columnMethodMap.entrySet()) {
Object param = this.getValue(result, entry.getValue().getParameterTypes()[0], entry.getKey());
entry.getValue().invoke(instance, param);
}
return instance;
}
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
return null;
}
protected <K> K execQueryObject(String sql, Class<K> clazz, Collection<Object> params) throws Exception {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
if(result.next()) {
if(this.isSimpleClass(clazz)) {
return (K) this.getValue(result, clazz, 1);
}
K instance = clazz.newInstance();
Map<Integer, Method> columnMethodMap = this.generateMethodMap(clazz, result.getMetaData());
for(Entry<Integer, Method> entry : columnMethodMap.entrySet()) {
Object param = this.getValue(result, entry.getValue().getParameterTypes()[0], entry.getKey());
entry.getValue().invoke(instance, param);
}
return instance;
}
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
return null;
} protected <K> List<K> execQueryObjects(String sql, Class<K> clazz, Object ...params) throws Exception {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
List<K> returnList = Lists.newArrayList();
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
if(this.isSimpleClass(clazz)) {
while(result.next()) {
returnList.add((K) this.getValue(result, clazz, 1));
}
}
Map<Integer, Method> columnMethodMap = this.generateMethodMap(clazz, result.getMetaData());
while(result.next()) {
K instance = clazz.newInstance();
for(Entry<Integer, Method> entry : columnMethodMap.entrySet()) {
Object param = this.getValue(result, entry.getValue().getParameterTypes()[0], entry.getKey());
entry.getValue().invoke(instance, param);
}
returnList.add(instance);
}
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
return returnList;
} protected <K> List<K> execQueryObjects(String sql, Class<K> clazz, Collection<Object> params) throws Exception {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
List<K> returnList = Lists.newArrayList();
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
if(this.isSimpleClass(clazz)) {
while(result.next()) {
returnList.add((K) this.getValue(result, clazz, 1));
}
}
Map<Integer, Method> columnMethodMap = this.generateMethodMap(clazz, result.getMetaData());
while(result.next()) {
K instance = clazz.newInstance();
for(Entry<Integer, Method> entry : columnMethodMap.entrySet()) {
Object param = this.getValue(result, entry.getValue().getParameterTypes()[0], entry.getKey());
entry.getValue().invoke(instance, param);
}
returnList.add(instance);
}
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
return returnList;
} private boolean isSimpleClass(Class<?> clazz) {
if(clazz.isPrimitive()) {
return true;
}
if(clazz.equals(Integer.class) || clazz.equals(Long.class)
|| clazz.equals(Short.class) || clazz.equals(String.class)
|| clazz.equals(Float.class) || clazz.equals(Double.class)
|| clazz.equals(BigDecimal.class) || clazz.equals(Byte.class)
|| clazz.equals(Boolean.class) || Date.class.isAssignableFrom(clazz)) {
return true;
}
return false; } private Map<Integer, Method> generateMethodMap(Class<?> clazz, ResultSetMetaData metaData) throws SQLException {
Map<Integer, Method> columnMethodMap = Maps.newHashMap();
Map<String, Method> methodMap = Maps.newHashMap();
Method[] methods = clazz.getMethods();
for(int i = 0; i < methods.length; i++) {
String methodName = methods[i].getName();
if(methodName.startsWith("set") && methods[i].getParameterTypes().length == 1) {
methodName = methodName.substring(3, 4).toLowerCase() + methodName.substring(4);
methodMap.put(methodName, methods[i]);
}
}
for(int i = 1; i <= metaData.getColumnCount(); i++) {
Method method = methodMap.get(metaData.getColumnLabel(i));
if(method == null) {
continue;
}
columnMethodMap.put(i, method);
}
return columnMethodMap;
} private Object getValue(ResultSet result, Class<?> paramterType, int columnIndex) throws SQLException {
if(result.getObject(columnIndex) == null && !paramterType.isPrimitive()) {
return null;
}
if(paramterType.equals(int.class) || paramterType.equals(Integer.class)) {
return result.getInt(columnIndex);
}
if(paramterType.equals(long.class) || paramterType.equals(Long.class)) {
return result.getLong(columnIndex);
}
if(paramterType.equals(Byte.class) || paramterType.equals(byte.class)) {
return result.getByte(columnIndex);
}
if(paramterType.equals(Short.class) || paramterType.equals(short.class)) {
return result.getShort(columnIndex);
}
if(paramterType.equals(Boolean.class) || paramterType.equals(boolean.class)) {
return result.getBoolean(columnIndex);
}
if(paramterType.equals(Float.class) || paramterType.equals(float.class)) {
return result.getFloat(columnIndex);
}
if(paramterType.equals(Double.class) || paramterType.equals(double.class)) {
return result.getDouble(columnIndex);
}
if(paramterType.equals(BigDecimal.class)) {
return result.getBigDecimal(columnIndex);
}
return result.getObject(columnIndex);
} private void addParamters(PreparedStatement stmm, Object ...params) throws SQLException {
if(params != null && params.length != 0) {
int i = 1;
for(Object param : params) {
if(param instanceof Integer) {
stmm.setInt(i, (Integer)param);
} else if (param instanceof Long) {
stmm.setLong(i, (Long)param);
} else if (param instanceof Byte) {
stmm.setByte(i, (Byte)param);
} else if (param instanceof Boolean) {
stmm.setBoolean(i, (Boolean)param);
} else if (param instanceof String) {
stmm.setString(i, (String) param);
} else if(param instanceof Short) {
stmm.setShort(i, (Short) param);
} else {
stmm.setObject(i, param);
}
i ++;
}
}
}
private void addParamters(PreparedStatement stmm, Collection<Object> params) throws SQLException {
if(params != null && params.size() != 0) {
int i = 1;
for(Object param : params) {
if(param instanceof Integer) {
stmm.setInt(i, (Integer)param);
} else if (param instanceof Long) {
stmm.setLong(i, (Long)param);
} else if (param instanceof Byte) {
stmm.setByte(i, (Byte)param);
} else if (param instanceof Boolean) {
stmm.setBoolean(i, (Boolean)param);
} else if (param instanceof String) {
stmm.setString(i, (String) param);
} else if(param instanceof Short) {
stmm.setShort(i, (Short) param);
} else {
stmm.setObject(i, param);
}
i ++;
}
}
} protected List<T> execQueryObjects(String sql, Object ...params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
List<T> returnList = Lists.newArrayList();
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
while(result.next()) {
T t = this.parse(result);
returnList.add(t);
}
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
return returnList;
} protected List<T> execQueryObjects(String sql, Collection<Object> params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
List<T> returnList = Lists.newArrayList();
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
while(result.next()) {
T t = this.parse(result);
returnList.add(t);
}
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
return returnList;
} protected int execInsert(String sql, Object ...params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
return stmm.executeUpdate();
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} protected int execInsert(String sql, Collection<Object> params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
return stmm.executeUpdate();
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} protected int execInsertReturnId(String sql, String name, Object object, Object ...params ) throws Exception {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
int effectRows = stmm.executeUpdate();
String methodName = "set" + name.substring(0, 1).toUpperCase() + name.substring(1);
Method[] methods = object.getClass().getMethods();
Method methodInvoke = null;
for(Method method : methods) {
if(method.getName().equals(methodName)) {
methodInvoke = method;
}
}
if(methodInvoke != null) {
Object id = this.getConnectionId(connection, methodInvoke.getParameterTypes()[0]);
methodInvoke.invoke(object, id);
}
return effectRows;
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} protected int execInsertReturnId(String sql, String name, Object object, Collection<Object> params ) throws Exception {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
int effectRows = stmm.executeUpdate();
String methodName = "set" + name.substring(0, 1).toUpperCase() + name.substring(1);
Method[] methods = object.getClass().getMethods();
Method methodInvoke = null;
for(Method method : methods) {
if(method.getName().equals(methodName)) {
methodInvoke = method;
}
}
if(methodInvoke != null) {
Object id = this.getConnectionId(connection, methodInvoke.getParameterTypes()[0]);
methodInvoke.invoke(object, id);
}
return effectRows;
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} protected int execUpdate(String sql, Object ...params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
return stmm.executeUpdate();
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} protected int execUpdate(String sql, Collection<Object> params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
return stmm.executeUpdate();
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} protected int execDelete(String sql, Object ...params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
return stmm.executeUpdate();
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} protected int execDelete(String sql, Collection<Object> params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
return stmm.executeUpdate();
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} protected int selectCount(String sql, Object ...params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
result.next();
return result.getInt(1);
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} protected int selectCount(String sql, Collection<Object> params) throws SQLException {
PooledDatabaseConnection connection = this.getConnection();
PreparedStatement stmm = null;
try {
stmm = connection.prepareStatement(sql);
this.addParamters(stmm, params);
ResultSet result = stmm.executeQuery();
result.next();
return result.getInt(1);
} finally {
DBUtil.close(stmm);
if(TransactionContainer2.needAutoCommit()) {
connection.pushConnection();
}
}
} private PooledDatabaseConnection getConnection() {
PooledDatabaseConnection connection = TransactionContainer2.getConnection();
if(connection != null) {
return connection;
} DataSourceConnection dataSource = this.getClass().getAnnotation(DataSourceConnection.class);
if(dataSource == null) {
throw new RuntimeException(this.getClass().getSimpleName() + " do not has Connection Conf");
}
Class<? extends IConnectionConfiguration> connectionConf = dataSource.connection();
connection = DatabaseConnectionPool.getPooledConnection(connectionConf);
if(connection == null) {
throw new RuntimeException("can not get db connection");
} if(!TransactionContainer2.needAutoCommit()) {
try {
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
TransactionContainer2.setConnection(connection);
}
return connection;
} private Object getConnectionId(PooledDatabaseConnection conn, Class<?> type) throws SQLException {
String idQuery = "select @@Identity";
PreparedStatement idQuerySttmt = conn.prepareStatement(idQuery);
try {
ResultSet idQueryRs = idQuerySttmt.executeQuery();
idQueryRs.next();
return getValue(idQueryRs, type, 1);
} finally {
DBUtil.close(idQuerySttmt);
} } protected abstract T parse(ResultSet result) throws SQLException;
}