|
package com.qbq;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBUtil {
private static final String DTO_PACKAGE = "com.qbq.";
private static final String SET_METHOD_PREFIX = "set";
public List query(String sql, Object... args) throws SQLException, NamingException, InstantiationException, IllegalAccessException, ClassNotFoundException, IllegalArgumentException, InvocationTargetException {
Connection conn = null;
PreparedStatement stmt = null;
List result = new ArrayList();
try {
Context ctx = new InitialContext();
String jndi_name = "java:comp/env/jdbc/hsql";
DataSource ds = (DataSource) ctx.lookup(jndi_name);
conn = ds.getConnection();
stmt = conn.prepareStatement(sql);
stmt.setString(1, args[0].toString());
/*
* stmt.execute("create table test (name varchar)");
* stmt.execute("insert into test values('a')");
* stmt.execute("insert into test values('b')");
* stmt.execute("insert into test values('c')");
* stmt.execute("insert into test values('d')");
*/
ResultSet rs = stmt.executeQuery();
System.out.println(getQueryFromPreparedStatement(sql, args));
while (rs.next()) {
// System.out.println(rs.getObject(1));
Object bean = setValue(rs);
result.add(bean);
System.out.println(bean.getClass().getName() + " : " + bean.toString());
}
return result;
} catch (SQLException e) {
throw e;
} catch (NamingException e) {
throw e;
} finally {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
private String getQueryFromPreparedStatement(String sql, Object... args) {
int len = sql.length();
StringBuffer t = new StringBuffer(len * 2);
if (args != null) {
int i = 0, limit = 0, base = 0;
while ((limit = sql.indexOf('?',limit)) != -1) {
if (args[i] != null) {
t.append(sql.substring(base,limit));
t.append("'");
t.append(args[i].toString());
t.append("'");
} else {
t.append(sql.substring(base,limit));
t.append(args[i]);
}
i++;
limit++;
base = limit;
}
if (base < len) {
t.append(sql.substring(base));
}
}
return t.toString();
}
private Object setValue(ResultSet rs) throws InstantiationException, IllegalAccessException,
ClassNotFoundException, IllegalArgumentException, InvocationTargetException, SQLException {
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
String tableName = rsmd.getTableName(count);
String beanName = DTO_PACKAGE + toUpperCaseFirstOne(tableName);
Class cls = this.getClass().getClassLoader().loadClass(beanName);
Object bean = cls.newInstance();
for (int i = 1; i <= count; i++) {
String columnName = rsmd.getColumnName(i);
String type = rsmd.getColumnTypeName(i);
Object value = rs.getObject(i);
Method[] methods = cls.getMethods();
String methodName = SET_METHOD_PREFIX + toUpperCaseFirstOne(columnName);
for (Method method : methods) {
if (method.getName().equals(methodName)) {
method.invoke(bean, value);
}
}
}
return bean;
}
private String toUpperCaseFirstOne(String s) {
s = s.toLowerCase();
return (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
}
}
|
