java 自己写的一个自动分析对象的mysql数据库操作工具

功能：利用反射机制，自动将传进来的class进行解析，对象名为表名，属性为字段，提供基本的CRUD，从而建立由对象->到数据库的操作，比传统的DAO模式效率提高了无数倍。

代码：

package com.bool.utils;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.struts2.ServletActionContext;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import com.bool.model.CorePage_Model;
import com.bool.model.DBInfo_Model;
/**
 * 功能：自动对类进行解析，类名为表名，属性为表的字段，提供CRUD操作
 * @author root
 *
 * @param <T>
 */
public class AutoAnalysisDB<T> {
	private String name;	//类名 -> 表名
	private Field[] fields;	//类属性 -> 表的字段
	private Class<T> c;
	private static DBInfo_Model DBINFO = null;
	private static String URL = null;
	static{
		DBINFO = readDBInfo();
		URL = "jdbc:mysql://"+DBINFO.getDbAddress()+":"+DBINFO.getDbPort()+"/"
				+DBINFO.getDbName()+"?useSSL=true&characterEncoding=utf8";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	
	public AutoAnalysisDB(Class<T> c){
		fields = c.getDeclaredFields();
		name = c.getName().toLowerCase();
		name = name.substring(name.lastIndexOf('.')+1, name.length());
		this.c=c;
	}
	
	public void add(T t) throws SQLException, IllegalArgumentException, IllegalAccessException{
		StringBuffer sb1 = new StringBuffer("insert into "+name+"(");
		StringBuffer sb2 = new StringBuffer(" values(");
		for(int i=0;i<fields.length;i++){
			if(!fields[i].getName().equals("id")){//跳过id字段
				if(i==(fields.length-1)){//如果是最后一个参数则不加,
					sb1.append(fields[i].getName());
					sb2.append("?");
				}else{
					sb1.append(fields[i].getName()+",");
					sb2.append("?,");
				}
			}
		}
		sb1.append(")");
		sb2.append(")");
		sb1.append(sb2);//拼接成完整的sql语句
		Connection conn = getConn();
		PreparedStatement state = conn.prepareStatement(sb1.toString());
		int j=1;
		for(int i=0;i<fields.length;i++){
			if(!fields[i].getName().equals("id")){//跳过id字段
				Field field = fields[i];
				field.setAccessible(true);
				state.setObject(j, field.get(t));
				field.setAccessible(false);
				j++;
			}
		}
		state.execute();//执行增加操作
		close(conn, state);
	}
	/**
	 * 
	 * @param obj 对象的任意一个参数删除记录
	 * @param limit 前多少个参数是符合条件的
	 * @throws SQLException 
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	public void remove(T t,int limit) throws SQLException, IllegalArgumentException, IllegalAccessException{
		StringBuffer sb = new StringBuffer("delete from "+name+" where");
		for(int i=0;i<limit;i++){
			if(i==(limit-1)){
				sb.append(" "+fields[i].getName()+"=?");
			}else{
				sb.append(" "+fields[i].getName()+" or");
			}
		}
		Connection conn = getConn();
		PreparedStatement state = conn.prepareStatement(sb.toString());
		for(int i=0;i<limit;i++){
			Field field = fields[i];
			field.setAccessible(true);
			state.setObject(i+1, field.get(t));
			field.setAccessible(false);
		}
		state.execute();
		close(conn, state);
	}
	
	/**
	 * 通过id修改指定表数据
	 * @param t
	 * @throws SQLException 
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	public void update(T t) throws SQLException, IllegalArgumentException, IllegalAccessException{
		StringBuffer sb = new StringBuffer("update "+name+" set ");
		for(int i=0;i<fields.length;i++){
			if(!fields[i].getName().equals("id")){//跳过id
				if(i==(fields.length-1)){
					sb.append(fields[i].getName()+"=? where id=?");
				}else{
					sb.append(fields[i].getName()+"=?,");
				}
			}
			
		}
		
		Connection conn = getConn();
		PreparedStatement state = conn.prepareStatement(sb.toString());
		int j=1;
		for(int i=0;i<fields.length;i++){
			if(!fields[i].getName().equals("id")){//跳过id
				Field field = fields[i];
				field.setAccessible(true);
				state.setObject(j, field.get(t));
				field.setAccessible(false);
				j++;
			}
		}
		Field field = fields[0];
		field.setAccessible(true);
		state.setObject(fields.length, field.get(t)); //获取指定对象的id
		field.setAccessible(false);
		state.execute();
		close(conn, state);
	}
	
	/**
	 * 
	 * @param obj	对象的任意一个参数删除记录
	 * @param limit 前多少个参数是符合条件的
	 * @return
	 * @throws SQLException 
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 * @throws InstantiationException 
	 */
	public T find(T t,int limit) throws SQLException, IllegalArgumentException, IllegalAccessException, InstantiationException{
		StringBuffer sb = new StringBuffer("select * from "+name+" where ");
		for(int i=0;i<limit;i++){
			if(i==limit-1){
				sb.append(fields[i].getName()+"=?");
			}else{
				sb.append(fields[i].getName()+"=? or");
			}
		}
		Connection conn = getConn();
		PreparedStatement state = conn.prepareStatement(sb.toString());
		for(int i=0;i<limit;i++){
			Field field = fields[i];
			field.setAccessible(true);
			state.setObject(i+1, field.get(t));
			field.setAccessible(false);
		}
		ResultSet rs = state.executeQuery();
		T t1 = null;
		if(rs.next()){
			t1 = c.newInstance();
			for(int i=0;i<fields.length;i++){
				Field field = fields[i];
				field.setAccessible(true);
				field.set(t1, rs.getObject(i+1));
				field.setAccessible(false);
			}
		}
		rs.close();
		close(conn, state);
		return t1;
	}
	
	public List<T> findAll() throws SQLException, InstantiationException, IllegalAccessException{
		StringBuffer sb = new StringBuffer("select * from "+name);
	
		Connection conn = getConn();
		PreparedStatement state = conn.prepareStatement(sb.toString());
		ResultSet rs = state.executeQuery();
		List<T> list = new ArrayList<T>();
		while(rs.next()){
			T t1 = c.newInstance();
			for(int i=0;i<fields.length;i++){
				Field field = fields[i];
				field.setAccessible(true);
				field.set(t1, rs.getObject(i+1));
				field.setAccessible(false);
			}
			
			list.add(t1);
		}
		rs.close();
		close(conn, state);
		return list;
	}
	
	public List<T> findByCorePage(CorePage_Model cpm) throws SQLException, InstantiationException, IllegalAccessException{
		StringBuffer sb = new StringBuffer("select * from "+name+" limit "+cpm.getThisPage()+","+cpm.getStep());
		
		Connection conn = getConn();
		PreparedStatement state = conn.prepareStatement(sb.toString());
		ResultSet rs = state.executeQuery();
		List<T> list = new ArrayList<T>();
		while(rs.next()){
			T t1 = c.newInstance();
			for(int i=0;i<fields.length;i++){
				Field field = fields[i];
				field.setAccessible(true);
				field.set(t1, rs.getObject(i+1));
				field.setAccessible(false);
			}
			list.add(t1);
		}
		rs.close();
		close(conn, state);
		return list;
		
	}
	
	public int getCount() throws SQLException{
		String sql = "select count(*) from "+name;
		Connection conn = getConn();
		PreparedStatement state = conn.prepareStatement(sql);
		ResultSet rs = state.executeQuery();
		int count = -1;
		if(rs.next()){
			count = rs.getInt(1);
		}
		rs.close();
		close(conn, state);
		return count;
		
	}
	
	public static DBInfo_Model readDBInfo(){
		DBInfo_Model dbInfo = null;
		DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		try {
			DocumentBuilder builder = factory.newDocumentBuilder();
//			File file = new File(ServletActionContext.getServletContext().getRealPath("/WEB-INF/")+"/config/dbconfig.xml");
			File file = new File("./config/dbconfig.xml");
			Document document = builder.parse(file);
			NodeList nlDbName = document.getElementsByTagName("db-name");
			Node ndbName = nlDbName.item(0);
			String strName = ndbName.getTextContent();
			
			NodeList nldbAddr = document.getElementsByTagName("db-address");
			Node ndbAddr = nldbAddr.item(0);
			String strAddr = ndbAddr.getTextContent();
			
			NodeList nldbPort = document.getElementsByTagName("db-port");
			Node ndbPort = nldbPort.item(0);
			String strPort = ndbPort.getTextContent();
			
			NodeList nldbUsername = document.getElementsByTagName("db-username");
			Node nuserName = nldbUsername.item(0);
			String strUsername = nuserName.getTextContent();
			
			NodeList nlPassword = document.getElementsByTagName("db-password");
			Node nPassword = nlPassword.item(0);
			String strPassword = nPassword.getTextContent();
			dbInfo = new DBInfo_Model();
			dbInfo.setDbName(strName);
			dbInfo.setDbPort(strPort);
			dbInfo.setDbAddress(strAddr);
			dbInfo.setUsername(strUsername);
			dbInfo.setPassword(strPassword);
		} catch (ParserConfigurationException e) {
			e.printStackTrace();
		} catch (SAXException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return dbInfo;
	}
	
	public static Connection getConn(){
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(URL, DBINFO.getUsername(), DBINFO.getPassword());
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return conn;
		
	}
	
	public static void close(Connection conn,Statement state){
		if(state != null){
			try {
				state.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}