制作数据库框架

一  利用前端条件组装sql与查询条件的集合

public void handle() throws Exception{

Map<String,String> requestMap = new HashMap();

String fromdate = requestMap.get("fromdate");

String todate = requestMap.get("todate");

String resultcode = requestMap.get("reultcode");

SimpleDateFormat yyyy1mm1dd = new SimpleDateFormat("yyyy-MM-dd");

List values = new ArrayList();

String sql="select d.* checkpaylog where 1=1 ";

if(!StringUtils.isBlank(fromdate)){

sql+=" and d.tdate>=? ";

values.add(yyyy1mm1dd.parse(fromdate));

}

if(!StringUtils.isBlank(todate)){

sql+=" and d.tdate<=? ";

values.add(yyyy1mm1dd.parse(todate));

}

if(!"-1".equals(resultcode)){

sql+=" and d.resultcode=? ";

values.add(resultcode);

}

Map classMap = getClassMap(Checkpaylog.class);

query(sql, values, classMap, Checkpaylog.class, 0, 30 );

}

二  getClassMap方法是通过反射获取结果类的字段与set方法与方法类型

public Map getClassMap(Class entiy){

Field[] declaredFields = entiy.getClass().getDeclaredFields();

Map map = new HashMap();

for (int i = 0; i <declaredFields.length ; i++) {

//设置是否可以访问，如果不设置将报错

declaredFields[i].setAccessible(true);

String type = declaredFields[i].getType().getName();

String name = declaredFields[i].getName();

String strT = name.substring(0, 1);

String strW = name.substring(1, name.length());

String method_set = "set" + strT.toUpperCase() + strW;

System.out.println("字段名称："+name);

System.out.println("类型："+type);

map.put(name.toLowerCase(),name);

map.put(name+"type",type);

map.put(name+"method",method_set);

}

return map;

}

三  query方法是组装sql并执行后解析结果集

public List query(String sql, List pvalues,Map classMap, Class entiy, int start, int limit)throws Exception{

ResourceBundle resource = ResourceBundle.getBundle("config");

String url = resource.getString("jdbc.url");

String user = resource.getString("jdbc.username");

String pwd = resource.getString("jdbc.password");

Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection(url, user, pwd);

List list = new ArrayList();

if (start == -1 && limit == -1) {

sql=sql;

}else{

sql = sql + "limit " + start+","+limit;

}

PreparedStatement query = null;

ResultSet set = null;

try {

query = con.prepareStatement(sql);

if (pvalues != null) {

for (int i = 0; i < pvalues.size(); i++) {

if (pvalues.get(i) instanceof String) {

query.setString(i+1, (String) pvalues.get(i));

} else if (pvalues.get(i) instanceof Date) {

query.setDate(i+1, new java.sql.Date( ((Date)pvalues.get(i)).getTime()));

} else if (pvalues.get(i) instanceof Timestamp) {

query.setTimestamp(i+1, (Timestamp) pvalues.get(i));

} else if (pvalues.get(i) instanceof Long) {

query.setLong(i+1, (Long) pvalues.get(i));

} else if (pvalues.get(i) instanceof Double) {

query.setDouble(i+1, (Double) pvalues.get(i));

} else {

query.setString(i+1, (String) pvalues.get(i));

}

}

}

set = query.executeQuery();

ResultSetMetaData metadata = set.getMetaData();

int columCount = metadata.getColumnCount();

Map map = null;

while (set.next()) {

map = new HashMap<String, Object>();

Class<? extends Class> entiyClass = entiy.getClass();

for (int i = 1; i <= columCount; i++) {

String key = metadata.getColumnLabel(i).toLowerCase();

String columName = (String) classMap.get(key);

if("".equals(columName)){

break;

}

Object value = set.getObject(i);

if(value==null ){

value =null;

}else if(value instanceof TIMESTAMP){

value = (Timestamp)((TIMESTAMP) value).toJdbc();

}else if (value instanceof java.sql.Date){

if(value.toString().length()>10){

value = set.getTimestamp(i);

}else{

value = set.getDate(i);

}

}else if(value instanceof java.math.BigDecimal){

if(value.toString().indexOf(".")!=-1){

value = String.valueOf(((java.math.BigDecimal) value).doubleValue());

}

}else if (value instanceof java.lang.Long){

value = Long.valueOf(String.valueOf(value));

} else {

value = value.toString();

}

String methodName = (String) classMap.get(key + "method");

Class<?> aClass = (Class<?>) classMap.get(key + "types");

Class instance = entiyClass.getConstructor().newInstance();

Method setGuid = instance.getDeclaredMethod(methodName, aClass);

setGuid.invoke(instance,value);

}

list.add(entiy);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

if(set!=null){

try {

set.close();

} catch (SQLException e) {

}

}

if(query!=null){

try {

query.close();

} catch (SQLException e) {

}

}

}

return list;

}

四  如果结果集是左连接形成的集合,这时用map来接受结果的方法findMapsBySQL来替换query方法

public List findMapsBySQL(String sql, List pvalues, int start, int limit) throws Exception{

ResourceBundle resource = ResourceBundle.getBundle("config");

String url = resource.getString("jdbc.url");

String user = resource.getString("jdbc.username");

String pwd = resource.getString("jdbc.password");

Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection(url, user, pwd);

List list = new ArrayList();

if (start == -1 && limit == -1) {

sql=sql;

}else{

sql = sql + "limit " + start+","+limit;

}

PreparedStatement query = null;

ResultSet set = null;

try {

query = con.prepareStatement(sql);

if (pvalues != null) {

for (int i = 0; i < pvalues.size(); i++) {

if (pvalues.get(i) instanceof String) {

query.setString(i+1, (String) pvalues.get(i));

} else if (pvalues.get(i) instanceof Date) {

query.setDate(i+1, new java.sql.Date( ((Date)pvalues.get(i)).getTime()));

} else if (pvalues.get(i) instanceof Timestamp) {

query.setTimestamp(i+1, (Timestamp) pvalues.get(i));

} else if (pvalues.get(i) instanceof Long) {

query.setLong(i+1, (Long) pvalues.get(i));

} else if (pvalues.get(i) instanceof Double) {

query.setDouble(i+1, (Double) pvalues.get(i));

} else {

query.setString(i+1, (String) pvalues.get(i));

}

}

}

set = query.executeQuery();

ResultSetMetaData metadata = set.getMetaData();

int columCount = metadata.getColumnCount();

Map map = null;

while (set.next()) {

map = new HashMap<String, Object>();

for (int i = 1; i <= columCount; i++) {

Object value = set.getObject(i);

if(value==null ){

value =null;

}else if(value instanceof TIMESTAMP){

value = (Timestamp)((TIMESTAMP) value).toJdbc();

}else if (value instanceof java.sql.Date){

if(value.toString().length()>10){

value = set.getTimestamp(i);

}else{

value = set.getDate(i);

}

}else if(value instanceof java.math.BigDecimal){

if(value.toString().indexOf(".")!=-1){

value = String.valueOf(((java.math.BigDecimal) value).doubleValue());

}

}else {

value = value.toString();

}

String key = metadata.getColumnLabel(i).toLowerCase();

map.put(key, value);

}

list.add(map);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

if(set!=null){

try {

set.close();

} catch (SQLException e) {

}

}

if(query!=null){

try {

query.close();

} catch (SQLException e) {

}

}

}

return list;

}