通用Dao方法

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

public class RDao {

	
	/**
	 * 通过表明获取数据库中表的列明
	 * @param tableName
	 * @return
	 */
	public ArrayList<String> getAllColumn(String tableName){
		ArrayList<String> ar = new ArrayList<String>();
		Connection  conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		//1=2不成立只能查出来列明
		String sql = "select * from "+tableName+" where 1=2";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			ResultSetMetaData metedata = rs.getMetaData();
			int colum = metedata.getColumnCount();
			for (int i=1;i<=colum;i++) {
				String columnName = metedata.getColumnName(i);
				ar.add(columnName);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps, rs);
		}
		
		return ar;
	}
	/**
	 * 通过元数据获得数据列明，数据类型等
	 * 
	 * 两者的区别R可以获得SQL语句查询出来的所有的列明
	 * D只可以获取单表列明以及列的所有的属性
	 * 
	 * @param tableName
	 * @return
	 */
	public ArrayList<String> getAllColumns(String tableName){
		ArrayList<String> ar = new ArrayList<String>();
		Connection  conn = BaseConnection.getConnection();
		ResultSet rs = null;
		try {
			//数据库元数据对象，就是数据库数据对象
			DatabaseMetaData dbm = conn.getMetaData();
		    rs = dbm.getColumns(null, "%", tableName, "%");
			while (rs.next()) {
				String columnName = rs.getString("COLUMN_NAME");
				String columnType = rs.getString("TYPE_NAME");
				String datasize = rs.getString("COLUMN_SIZE");
				String digits = rs.getString("DECIMAL_DIGITS");
				String nullable = rs.getString("NULLABLE");
				ar.add(columnName);
			}
			
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, rs);
		}
		
		return ar;
		
	}
	
	public boolean insert(Object ob){
		boolean b = false;
		Connection  conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		
		
		Class cl = ob.getClass();
		
		Field[] fi = cl.getDeclaredFields();
		
		ArrayList<String >col = getAllColumn(cl.getSimpleName());
		
		StringBuffer sb = new StringBuffer();
		StringBuffer sb1 = new StringBuffer();
		
		sb.append("insert into ");
		sb.append(cl.getSimpleName());
		sb.append("(");
		for (int i=1;i<col.size();i++) {
			sb.append(col.get(i));
			sb1.append("?");
			if (i != col.size()-1) {
				sb.append(",");
				sb1.append(",");
			}
		}
		sb.append(")values(");
		sb.append(sb1);
		sb.append(")");
		
		try {
			ps = conn.prepareStatement(sb.toString());
			for (int i=1;i<col.size();i++) {
				for (Field ff:fi) {
					if (ff.getName().equals(col.get(i))) {
						ff.setAccessible(true);
						ps.setObject(i, ff.get(ob));
						break;
						
					}
				}
			}
			
			int a = ps.executeUpdate();
			if (a > 0) {
				b = true;
			}
			
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps);
		}
		
		return b;
	}
	
	//需求是现在有一个表，这个表是一个订单表（订单号，订单时间，订单人）
	//                             33  20150101 zhang
	//                       从表订单详细表     编号     订单号  商品名 数量
	//                                   1    33   ku 2
	//                                   2    33   qun 3
	//依次订单有好多数据，我们插入数据的时候就是先插入订单表，然后再插入订单详细表，再插入订单详细表的时候你是需要订单表中的订单号的。
	
	public int insertGetKey(Object ob) {
		
		int id = 0;
		Connection  conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		Class cl = ob.getClass();
		
		Field[] fi = cl.getDeclaredFields();
		
		ArrayList<String >col = getAllColumn(cl.getSimpleName());
		
		StringBuffer sb = new StringBuffer();
		StringBuffer sb1 = new StringBuffer();
		
		sb.append("insert into ");
		sb.append(cl.getSimpleName());
		sb.append("(");
		for (int i=1;i<col.size();i++) {
			sb.append(col.get(i));
			sb1.append("?");
			if (i != col.size()-1) {
				sb.append(",");
				sb1.append(",");
			}
		}
		sb.append(")values(");
		sb.append(sb1);
		sb.append(")");
		
		try {
			ps = conn.prepareStatement(sb.toString());
			for (int i=1;i<col.size();i++) {
				for (Field ff:fi) {
					if (ff.getName().equals(col.get(i))) {
						ff.setAccessible(true);
						ps.setObject(i, ff.get(ob));
						break;
						
					}
				}
			}
			
			int a = ps.executeUpdate();
			if (a > 0) {
				////获得主键,主键必须是递增，还有必须是添加的方法才能获得主键
				rs = ps.getGeneratedKeys();
				if (rs.next()) {
					id = rs.getInt(1);
				}
			}
			
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps);
		}
		
		return id;
		
		
	}
	
	//删除自己写根据id删除
	
	public boolean deleteById(Class cl,int id) {
		
		boolean b = false;
		
		Object ob = null;
		Connection  conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		
		
		Field[] fi = cl.getDeclaredFields();
		//获得表中的所有列
		ArrayList<String> col = getAllColumns(cl.getSimpleName());
		
		StringBuffer sb = new StringBuffer();
		sb.append("delete ");
		
		sb.append("from ");
		sb.append(cl.getSimpleName());
		sb.append("where");
		sb.append(col.get(0));
		sb.append("?");
		
		try {
			ps = conn.prepareStatement(sb.toString());
			ps.setInt(1, id);
			int a = ps.executeUpdate();
			
			if (a>0) {
				b = true;
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps);
		}
		
		return b;
		
	}
	
	
	
	
	
	
	/**
	 * 这个方法是不需要的，没有意义，直接忘记，还获取不到值
	 * @param ob
	 * @return
	 */
	public int updateGetKey(Object ob){
		int id = 0;
		Connection  conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Class cl = ob.getClass();
		
		Field[] fi = cl.getDeclaredFields();
		
		ArrayList<String >col = getAllColumn(cl.getSimpleName());
		
		StringBuffer sb = new StringBuffer();
		
		sb.append("update ");
		sb.append(cl.getSimpleName());
		sb.append(" set");
		
		for (int i=1;i<col.size();i++) {
			sb.append(col.get(i));
			sb.append("?");
			if (i != col.size()-1) {
				sb.append(",");
				
			}
		}
		
		sb.append(" where");
		sb.append(col.get(0));
		sb.append("=?");
		
		
		try {
			ps = conn.prepareStatement(sb.toString());
			for (int i=1;i<col.size();i++) {
				for (Field ff:fi) {
					if (ff.getName().equals(col.get(i))) {
						ff.setAccessible(true);
						ps.setObject(i, ff.get(ob));
						break;
						
					}
				}
			}
			
			fi[0].setAccessible(true);
			ps.setObject(col.size(), fi[0].get(ob));
			int a = ps.executeUpdate();
			if (a > 0) {
				//获得主键,主键必须是递增，还有必须是添加的方法才能获得主键
				rs = ps.getGeneratedKeys();
				if (rs.next()) {
					id = rs.getInt(1);
				} 
			}
			
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps);
		}
		
		
		return id;
	}
	
	public boolean update(Object ob){
		boolean b = false;
		Connection  conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		
		Class cl = ob.getClass();
		
		Field[] fi = cl.getDeclaredFields();
		
		ArrayList<String >col = getAllColumn(cl.getSimpleName());
		
		StringBuffer sb = new StringBuffer();
		
		sb.append("update ");
		sb.append(cl.getSimpleName());
		sb.append(" set");
		
		for (int i=1;i<col.size();i++) {
			sb.append(col.get(i));
			sb.append("?");
			if (i != col.size()-1) {
				sb.append(",");
				
			}
		}
		
		sb.append(" where");
		sb.append(col.get(0));
		sb.append("=?");
		
		
		try {
			ps = conn.prepareStatement(sb.toString());
			for (int i=1;i<col.size();i++) {
				for (Field ff:fi) {
					if (ff.getName().equals(col.get(i))) {
						ff.setAccessible(true);
						ps.setObject(i, ff.get(ob));
						break;
						
					}
				}
			}
			
			fi[0].setAccessible(true);
			ps.setObject(col.size(), fi[0].get(ob));
			int a = ps.executeUpdate();
			if (a > 0) {
				b = true;
			}
			
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps);
		}
		
		
		return b;
	}
	
	/**
	 * 
	 * @param cl
	 * @param name 数据库中的列明
	 * @param value
	 * @return
	 */
	public ArrayList getListByLike(Class cl,String name,Object value) {
		
		ArrayList ar = new ArrayList();
		Connection conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Field[] fi = cl.getDeclaredFields();
		ArrayList<String> col = getAllColumns(cl.getName());
		StringBuffer sb = new StringBuffer();
		sb.append("select ");
		for (int i=0;i<col.size();i++) {
			sb.append(col.get(i));
			if (i != col.size()-1) {
				sb.append(",");
			}
		}
		
		sb.append(" from");
		sb.append(cl.getSimpleName());
		sb.append(" where ");
		sb.append(name);
		sb.append(" like '%");
		sb.append(value);
		sb.append("%'");
		//like 语句不支持占位符
		
		try {
			ps = conn.prepareStatement(sb.toString());
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Object obj = cl.newInstance();
				for (String str:col) {
					for (Field ff:fi) {
						if (str.equals(ff.getName())) {
							ff.setAccessible(true);
							ff.set(obj, rs.getObject(ff.getName()));
							break;
						}
					}
				}
				
				ar.add(obj);
				
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps, rs);
		}
		
		
		return ar;
		
		
		
		
	}
	
	/**
	 * 该方法用于所有的查询
	 * 注意：尽量保证链接表中数据 除了主外键关联的外，尽量不要使用重复的表列名
	 * @param 查询出来的数据放到那个类中 实体类中
	 * @param sql 查询的sql语句
	 * @param obs 所有的条件
	 * @return
	 */
	public ArrayList getListBySqlX(Class cl,String sql,Object[]obs){
		Connection conn = BaseConnection.getConnection();
		ArrayList ar = new ArrayList();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Field[] fi = cl.getDeclaredFields();
		
		ArrayList<String> col = new ArrayList<String>();
		try {
			ps = conn.prepareStatement(sql);
			
			
			//获得数据表中的列
			ResultSetMetaData metaData = rs.getMetaData();
			int column = metaData.getColumnCount();
			for (int i=1;i<=column;i++) {
				String columnName = metaData.getColumnName(i);
				col.add(columnName);
			}
			for (int i=0;i<obs.length;i++) {
				ps.setObject(i+1, obs[i]);
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				Object ob = cl.newInstance();
				for (String str:col) {
					for (Field ff:fi) {
						if (str.equals(ff.getName())) {
							ff.setAccessible(true);
							ff.set(ob, rs.getObject(ff.getName()));
							break;
						}
					}
				}
				ar.add(ob);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			BaseConnection.close(conn, ps, rs);
		}
		return ar;
	}
	
	
	
	
	
	
	
	
	public ArrayList getListBySome(Class cl,String name,Object value) {
		
		ArrayList ar = new ArrayList();
		
		
		Connection  conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		Field[] fi = cl.getDeclaredFields();
		//获得表中的所有列
		ArrayList<String> col = getAllColumns(cl.getSimpleName());
		
		StringBuffer sb = new StringBuffer();
		sb.append("select ");
		for (int i=0;i<col.size();i++) {
			sb.append(col.get(i));
			if (i != col.size()-1) {
				sb.append(",");
			}
		}
		sb.append("from ");
		sb.append(cl.getSimpleName());
		sb.append(" where ");
		sb.append(name);
		sb.append(" =? ");
		
		try {
			ps = conn.prepareStatement(sb.toString());
			ps.setObject(1, value);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Object obj = cl.newInstance();
				for (String str:col) {
					for (Field ff:fi) {
						if (str.equals(ff.getName())) {
							ff.setAccessible(true);
							ff.set(obj, rs.getObject(ff.getName()));
							break;
						}
					}
				}
				
				ar.add(obj);
				
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps, rs);
		}
		
		
		return ar;
		
		
	}
	
	public Object getObById(Class cl,int id){
		
		Object ob = null;
		Connection  conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		Field[] fi = cl.getDeclaredFields();
		//获得表中的所有列
		ArrayList<String> col = getAllColumns(cl.getSimpleName());
		
		StringBuffer sb = new StringBuffer();
		sb.append("select ");
		for (int i=0;i<col.size();i++) {
			sb.append(col.get(i));
			if (i != col.size()-1) {
				sb.append(",");
			}
		}
		sb.append("from ");
		sb.append(cl.getSimpleName());
		sb.append("where");
		sb.append(col.get(0));
		sb.append("?");
		
		try {
			ps = conn.prepareStatement(sb.toString());
			ps.setInt(1, id);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Object obj = cl.newInstance();
				for (String str:col) {
					for (Field ff:fi) {
						if (str.equals(ff.getName())) {
							ff.setAccessible(true);
							ff.set(obj, rs.getObject(ff.getName()));
							break;
						}
					}
				}
				
				
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps, rs);
		}
		
		return ob;
		
	}
	
	
	public ArrayList getList(Class cl){
		ArrayList ar = new ArrayList();
		Connection  conn = BaseConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		Field[] fi = cl.getDeclaredFields();
		//获得表中的所有列
		ArrayList<String> col = getAllColumns(cl.getSimpleName());
		
		StringBuffer sb = new StringBuffer();
		
		sb.append("select");
		for (int i=0;i<col.size();i++) {
			sb.append(col.get(i));
			if (i != col.size()-1) {
				sb.append(",");
			}
		}
		
		sb.append("from");
		
		sb.append(cl.getSimpleName());
		
		try {
			ps = conn.prepareStatement(sb.toString());
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Object obj = cl.newInstance();
				for (String str:col) {
					for (Field ff:fi) {
						if (str.equals(ff.getName())) {
							ff.setAccessible(true);
							ff.set(obj, rs.getObject(ff.getName()));
							break;
						}
					}
				}
				ar.add(obj);
				
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			BaseConnection.close(conn, ps, rs);
		}
	
		return ar;
	}
	
	public static void main(String[] args) {
		
	}
	
	
}