使用反射机制创建万能的数据库的增删查改

package com.huoguo.common;import java.lang.reflect.Field;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import com.huoguo.bean.TbReader;import com.mysql.jdbc.PreparedStatement;public class MyConn {protected Connection conn;protected PreparedStatement sql;protected ResultSet rs;public MyConn() {try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bookmanage?useUnicode=true&characterEncoding=utf-8","root", "");} catch (Exception e) {e.printStackTrace();}}/** * 查询所有记录 *  * @param ob * @return */public ArrayList getList(Object ob) {ArrayList list = new ArrayList();Class cls = ob.getClass();// 要求:数据表中的字段必须与类中的属性一一对应Field[] fi = cls.getDeclaredFields();// 获取类中的所有的属性String strsql = "select * from "+ cls.getSimpleName().replace("Tb", "").toLowerCase();// 获取表名(类名必须与数据表一致)try {sql = (PreparedStatement) conn.prepareStatement(strsql);rs = sql.executeQuery();while (rs.next()) {Object obj = cls.newInstance();// 创建指定类的实例化对象for (Field f : fi) {f.setAccessible(true);// 暴力访问f.set(obj, rs.getObject(f.getName()));// 调用类中指定属性的set方法赋值}list.add(obj);}} catch (Exception e) {e.printStackTrace();}return list;}/** * 通过主键查询 *  * @param cl * @param id * @return */public Object getObjectById(Class cl, int id) {Object obj = null;Field[] fi = cl.getDeclaredFields();// 获取类中的所有的属性String strsql = "select * from "+ cl.getSimpleName().replace("Tb", "").toLowerCase()+ " where " + fi[0].getName() + " = " + id;// fi[0].getName()获取数据表中第一列字段try {sql = (PreparedStatement) conn.prepareStatement(strsql);rs = sql.executeQuery();if (rs.next()) {obj = cl.newInstance();// 创建指定类的实例化对象for (Field f : fi) {f.setAccessible(true);// 表示可以访问类中的私有属性f.set(obj, rs.getObject(f.getName()));// 调用类中指定属性的set方法赋值}}} catch (Exception e) {e.printStackTrace();}return obj;}/** * 根据数据表中的一个字段和值查询 *  * @param cl * @param name * @param value * @return */public ArrayList getListBySome(Class cl, String name, Object value) {ArrayList list = new ArrayList();Field[] fi = cl.getDeclaredFields();// 获取类中的所有的属性String strsql = "select * from "+ cl.getSimpleName().replace("Tb", "").toLowerCase()+ " where " + name + " = '" + value + "'";try {sql = (PreparedStatement) conn.prepareStatement(strsql);rs = sql.executeQuery();while (rs.next()) {Object obj = cl.newInstance();for (Field f : fi) {f.setAccessible(true);// 暴力访问f.set(obj, rs.getObject(f.getName()));// 调用类中指定属性的set方法赋值}list.add(obj);}} catch (Exception e) {e.printStackTrace();}return list;}/** * 添加记录 *  * @param ob * @return */public int insert(Object ob) {int row = 0;// 记录数Class clz = ob.getClass();String table = clz.getSimpleName().replace("Tb", "").toLowerCase();// 数据表名Field[] fi = clz.getDeclaredFields();// 字段名StringBuilder sb = new StringBuilder();// 拼装sql语句用// 拼装sql语句sb.append("insert into ").append(table).append(" (");for (int i = 0; i < fi.length - 1; i++) {sb.append(fi[i].getName() + ",");}sb.append(fi[fi.length - 1].getName() + ") values (");// 最后一个不加逗号for (int i = 0; i < fi.length - 1; i++) {sb.append("?,");}sb.append("?)");// 最后一个不加逗号// 设置值try {sql = (PreparedStatement) conn.prepareStatement(sb.toString());for (int i = 0; i < fi.length; i++) {fi[i].setAccessible(true);// 暴力访问字段sql.setObject(i + 1, fi[i].get(ob));}row = sql.executeUpdate();} catch (Exception e) {e.printStackTrace();}return row;}/** * 更新数据 * @param obj * @return */public int update(Object obj) {int row=0;Class clz = obj.getClass();Field[] fi = clz.getDeclaredFields();// 获取类中的所有的属性StringBuilder sb = new StringBuilder();sb.append("update ");sb.append(clz.getSimpleName().replace("Tb", "").toLowerCase());sb.append(" set ");for (int i = 1; i < fi.length; i++) {sb.append(fi[i].getName());// 获取列名sb.append(" =? ");if (i != fi.length - 1)// 最后一列不用加逗号sb.append(",");}sb.append(" where ");sb.append(fi[0].getName());sb.append(" =? ");try {sql = (PreparedStatement) conn.prepareStatement(sb.toString());for (int i = 1; i < fi.length; i++) {fi[i].setAccessible(true);sql.setObject(i, fi[i].get(obj));}//设置主键fi[0].setAccessible(true);sql.setObject(fi.length, fi[0].get(obj));row = sql.executeUpdate();} catch (Exception e) {e.printStackTrace();} return row;}}