注解+反射+JDBC，实现一个简易的泛型DAO接口

一、实现思路

       1、定义3个Annotation（注解）：Entity、Id、Column，Entity作用于Type级别，用于标识JavaBean与数据库表名的映射关系。Id作用于Field级别，用于标识JavaBean中ID属性与表中ID字段的映射关系，Column作用于Field级别，用于标识JavaBean中除ID属性外的其它属性与表中字段的映射关系。

     2、在Dao实现类中，通过反射API获得JavaBean中注解和属性的信息，如：表名、字段。JavaBean属性的名称、数据类型等信息。然后将这些信息拼接成一条SQL语句，通过JDBC的方式与数据库交互。

二、示例代码

1、定义一个Dao公共类，提供获得数据库连接与释放数据库资源的接口

package dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/** * 提供获取数据库连接、释放资源的接口 */public class JdbcDaoHelper {/** * 数据库用户名 */private static final String USER = "test";/** * 数据库密码  */private static final String PASSWORD = "test";/** * 连接数据库的地址 */private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:study";private static Connection conn;/** * 获得一个数据库连接对象 * @return java.sql.Connection实例 */public static Connection getConnection() {try {if (conn == null) {Class.forName("oracle.jdbc.OracleDriver");conn = DriverManager.getConnection(URL, USER, PASSWORD);} else {return conn;}} catch (Exception e) {e.printStackTrace();}return conn;}/** * 释放数据库资源 */public static void release(PreparedStatement ps,ResultSet rs) {try {if (conn != null) {conn.close();conn = null;}if (ps != null) {ps.close();ps = null;}if (rs != null) {rs.close();rs = null;}} catch (SQLException e) {e.printStackTrace();}}}

2、定义一个泛型Dao接口GenericDao<T>

package dao;import java.util.List;import java.util.Map;public interface GenericDao<T> {public void save(T t) throws Exception;public void delete(Object id,Class<T> clazz) throws Exception;public void update(T t) throws Exception;public T get(Object id,Class<T> clazz) throws Exception;/** * 根据条件查询 * @param sqlWhereMap key：条件字段名 value：条件字段值 * @param clazz * @return * @throws Exception */public List<T> findAllByConditions(Map<String,Object> sqlWhereMap,Class<T> clazz) throws Exception;}

3、定义GenericDao<T>接口JDBC实现类JdbcGenericDaoImpl<T>

package dao;import java.beans.IntrospectionException;import java.beans.PropertyDescriptor;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Timestamp;import java.sql.Types;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.Set;import java.util.Map.Entry;import annotation.Column;import annotation.Entity;import annotation.Id;import exception.NotFoundAnnotationException;/** * 泛型DAO的JDBC实现 * @author 杨信 * @version 1.0 */public class JdbcGenericDaoImpl<T> implements GenericDao<T> {//表的别名private static final String TABLE_ALIAS = "t";@Overridepublic void save(T t) throws Exception {Class<?> clazz = t.getClass();//获得表名String tableName = getTableName(clazz);//获得字段StringBuilder fieldNames = new StringBuilder();//字段名List<Object> fieldValues = new ArrayList<Object>();//字段值StringBuilder placeholders = new StringBuilder();//占位符Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {PropertyDescriptor pd = new PropertyDescriptor(field.getName(),t.getClass());if (field.isAnnotationPresent(Id.class)) {fieldNames.append(field.getAnnotation(Id.class).value()).append(",");fieldValues.add(pd.getReadMethod().invoke(t));} else if(field.isAnnotationPresent(Column.class)) {fieldNames.append(field.getAnnotation(Column.class).value()).append(",");fieldValues.add(pd.getReadMethod().invoke(t));}placeholders.append("?").append(",");}//删除最后一个逗号fieldNames.deleteCharAt(fieldNames.length()-1);placeholders.deleteCharAt(placeholders.length()-1);//拼接sqlStringBuilder sql = new StringBuilder("");sql.append("insert into ").append(tableName)   .append(" (").append(fieldNames.toString())   .append(") values (").append(placeholders).append(")") ;PreparedStatement ps = JdbcDaoHelper.getConnection().prepareStatement(sql.toString());//设置SQL参数占位符的值setParameter(fieldValues, ps, false);//执行SQLps.execute();JdbcDaoHelper.release(ps, null);System.out.println(sql + "\n" + clazz.getSimpleName() + "添加成功!");}@Overridepublic void delete(Object id,Class<T> clazz) throws Exception {//获得表名String tableName = getTableName(clazz);//获得ID字段名和值String idFieldName = "";boolean flag = false;Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {if(field.isAnnotationPresent(Id.class)) {idFieldName = field.getAnnotation(Id.class).value();flag = true;break;}}if (!flag) {throw new NotFoundAnnotationException(clazz.getName() + " object not found id property.");}//拼装sqlString sql = "delete from " + tableName + " where " + idFieldName + "=?";PreparedStatement ps = JdbcDaoHelper.getConnection().prepareStatement(sql);ps.setObject(1, id);//执行SQLps.execute();JdbcDaoHelper.release(ps,null);System.out.println(sql + "\n" + clazz.getSimpleName() + "删除成功!");}@Overridepublic void update(T t) throws Exception {Class<?> clazz = t.getClass();//获得表名String tableName = getTableName(clazz);//获得字段List<Object> fieldNames = new ArrayList<Object>();//字段名List<Object> fieldValues = new ArrayList<Object>();//字段值List<String> placeholders = new ArrayList<String>();//占位符String idFieldName = "";Object idFieldValue = "";Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {PropertyDescriptor pd = new PropertyDescriptor(field.getName(),t.getClass());if (field.isAnnotationPresent(Id.class)) {idFieldName = field.getAnnotation(Id.class).value();idFieldValue = pd.getReadMethod().invoke(t);} else if(field.isAnnotationPresent(Column.class)) {fieldNames.add(field.getAnnotation(Column.class).value());fieldValues.add(pd.getReadMethod().invoke(t));placeholders.add("?");}}//ID作为更新条件，放在集合中的最后一个元素fieldNames.add(idFieldName);fieldValues.add(idFieldValue);placeholders.add("?");//拼接sqlStringBuilder sql = new StringBuilder("");sql.append("update ").append(tableName).append(" set ");int index = fieldNames.size() - 1;for (int i = 0; i < index; i++) {sql.append(fieldNames.get(i)).append("=").append(placeholders.get(i)).append(",");}sql.deleteCharAt(sql.length()-1).append(" where ").append(fieldNames.get(index)).append("=").append("?");//设置SQL参数占位符的值PreparedStatement ps = JdbcDaoHelper.getConnection().prepareStatement(sql.toString());setParameter(fieldValues, ps, false);//执行SQLps.execute();JdbcDaoHelper.release(ps, null);System.out.println(sql + "\n" + clazz.getSimpleName() + "修改成功.");}@Overridepublic T get(Object id,Class<T> clazz) throws Exception {String idFieldName = "";Field[] fields = clazz.getDeclaredFields();boolean flag = false;for (Field field : fields) {if (field.isAnnotationPresent(Id.class)) {idFieldName = field.getAnnotation(Id.class).value();flag = true;break;} }if (!flag) {throw new NotFoundAnnotationException(clazz.getName() + " object not found id property.");}//拼装SQLMap<String,Object> sqlWhereMap = new HashMap<String, Object>();sqlWhereMap.put(TABLE_ALIAS + "." + idFieldName, id);List<T> list = findAllByConditions(sqlWhereMap, clazz);return list.size() > 0 ? list.get(0) : null;}@Overridepublic List<T> findAllByConditions(Map<String,Object> sqlWhereMap,Class<T> clazz) throws Exception {List<T> list = new ArrayList<T>();String tableName = getTableName(clazz);String idFieldName = "";//存储所有字段的信息//通过反射获得要查询的字段StringBuffer fieldNames = new StringBuffer();Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {String propertyName = field.getName();if (field.isAnnotationPresent(Id.class)) {idFieldName = field.getAnnotation(Id.class).value();fieldNames.append(TABLE_ALIAS + "." + idFieldName)  .append(" as ").append(propertyName).append(",");} else if (field.isAnnotationPresent(Column.class)) {fieldNames.append(TABLE_ALIAS + "." + field.getAnnotation(Column.class).value())  .append(" as ").append(propertyName).append(",");}}fieldNames.deleteCharAt(fieldNames.length()-1);//拼装SQLString sql = "select " + fieldNames + " from " + tableName + " " + TABLE_ALIAS;PreparedStatement ps = null;List<Object> values = null;if (sqlWhereMap != null) {List<Object> sqlWhereWithValues = getSqlWhereWithValues(sqlWhereMap);if (sqlWhereWithValues != null) {//拼接SQL条件String sqlWhere = (String)sqlWhereWithValues.get(0);sql += sqlWhere;//得到SQL条件中占位符的值values = (List<Object>) sqlWhereWithValues.get(1);}} //设置参数占位符的值if (values != null) {ps = JdbcDaoHelper.getConnection().prepareStatement(sql);setParameter(values, ps, true);} else {ps = JdbcDaoHelper.getConnection().prepareStatement(sql);}//执行SQLResultSet rs = ps.executeQuery();while(rs.next()) {T t = clazz.newInstance();initObject(t, fields, rs);list.add(t);}//释放资源JdbcDaoHelper.release(ps, rs);System.out.println(sql);return list;}/** * 根据结果集初始化对象 */private void initObject(T t, Field[] fields, ResultSet rs)throws SQLException, IntrospectionException,IllegalAccessException, InvocationTargetException {for (Field field : fields) {String propertyName = field.getName();Object paramVal = null;Class<?> clazzField = field.getType();if (clazzField == String.class) {paramVal = rs.getString(propertyName);} else if (clazzField == short.class || clazzField == Short.class) {paramVal = rs.getShort(propertyName);} else if (clazzField == int.class || clazzField == Integer.class) {paramVal = rs.getInt(propertyName);} else if (clazzField == long.class || clazzField == Long.class) {paramVal = rs.getLong(propertyName);} else if (clazzField == float.class || clazzField == Float.class) {paramVal = rs.getFloat(propertyName);} else if (clazzField == double.class || clazzField == Double.class) {paramVal = rs.getDouble(propertyName);} else if (clazzField == boolean.class || clazzField == Boolean.class) {paramVal = rs.getBoolean(propertyName);} else if (clazzField == byte.class || clazzField == Byte.class) {paramVal = rs.getByte(propertyName);} else if (clazzField == char.class || clazzField == Character.class) {paramVal = rs.getCharacterStream(propertyName);} else if (clazzField == Date.class) {paramVal = rs.getTimestamp(propertyName);} else if (clazzField.isArray()) {paramVal = rs.getString(propertyName).split(",");//以逗号分隔的字符串} PropertyDescriptor pd = new PropertyDescriptor(propertyName,t.getClass());pd.getWriteMethod().invoke(t, paramVal);}}/** * 根据条件，返回sql条件和条件中占位符的值 * @param sqlWhereMap key：字段名 value：字段值 * @return 第一个元素为SQL条件，第二个元素为SQL条件中占位符的值 */private List<Object> getSqlWhereWithValues(Map<String,Object> sqlWhereMap) {if (sqlWhereMap.size() <1 ) return null;List<Object> list = new ArrayList<Object>();List<Object> fieldValues = new ArrayList<Object>();StringBuffer sqlWhere = new StringBuffer(" where ");Set<Entry<String, Object>> entrySets = sqlWhereMap.entrySet();for (Iterator<Entry<String, Object>> iteraotr = entrySets.iterator();iteraotr.hasNext();) {Entry<String, Object> entrySet = iteraotr.next();fieldValues.add(entrySet.getValue());Object value = entrySet.getValue();if (value.getClass() == String.class) {sqlWhere.append(entrySet.getKey()).append(" like ").append("?").append(" and ");} else {sqlWhere.append(entrySet.getKey()).append("=").append("?").append(" and ");}}sqlWhere.delete(sqlWhere.lastIndexOf("and"), sqlWhere.length());list.add(sqlWhere.toString());list.add(fieldValues);return list;}/** * 获得表名 */private String getTableName(Class<?> clazz) throws NotFoundAnnotationException {if (clazz.isAnnotationPresent(Entity.class)) {Entity entity = clazz.getAnnotation(Entity.class);return entity.value();} else {throw new NotFoundAnnotationException(clazz.getName() + " is not Entity Annotation.");}}/** * 设置SQL参数占位符的值 */private void setParameter(List<Object> values, PreparedStatement ps, boolean isSearch)throws SQLException {for (int i = 1; i <= values.size(); i++) {Object fieldValue = values.get(i-1);Class<?> clazzValue = fieldValue.getClass();if (clazzValue == String.class) {if (isSearch) ps.setString(i, "%" + (String)fieldValue + "%");elseps.setString(i,(String)fieldValue);} else if (clazzValue == boolean.class || clazzValue == Boolean.class) {ps.setBoolean(i, (Boolean)fieldValue);} else if (clazzValue == byte.class || clazzValue == Byte.class) {ps.setByte(i, (Byte)fieldValue);} else if (clazzValue == char.class || clazzValue == Character.class) {ps.setObject(i, fieldValue,Types.CHAR);} else if (clazzValue == Date.class) {ps.setTimestamp(i, new Timestamp(((Date) fieldValue).getTime()));} else if (clazzValue.isArray()) {Object[] arrayValue = (Object[]) fieldValue;StringBuffer sb = new StringBuffer();for (int j = 0; j < arrayValue.length; j++) {sb.append(arrayValue[j]).append("、");}ps.setString(i, sb.deleteCharAt(sb.length()-1).toString());} else {ps.setObject(i, fieldValue, Types.NUMERIC);}}}}

4、定义三个注解Entity、Id、Column，生命周期保存在运行期间，以便通过反射获取

1）、Entity

package annotation;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** * 数据库表的的名称 */@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.TYPE)public @interface Entity {/** * 表名 */String value();}

2）、Id

package annotation;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** * 标识数据库字段的ID */@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.FIELD)public @interface Id {/** * ID的名称 * @return */String value();}

3）、Column

package annotation;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** * 标识数据库字段的名称 * @author 杨信 * */@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.FIELD)public @interface Column {/** * 字段名称 */String value();/** * 字段的类型 * @return */Class<?> type() default String.class;/** * 字段的长度 * @return */int length() default 0;}

五、定义一个JavaBean，用于测试使用

要求：

1）、类名必须用Entity注解标识，并指定数据库中对应的表名

2）、Id属性必须用Id注解标识，并指定表中所对应的字段名

3）、其它属性必须用Column注解标识，并指定表中所对应的字段名

4）、JavaBean属性的数据类型目前只实现了8大基本数据类型、String和这些基本类型的数组类型。

5）、JavaBean属性目前没有做字段的长度与类型的判断，待以后改进。

package model;import java.util.Date;import annotation.Column;import annotation.Entity;import annotation.Id;/** * 图书 */@Entity("t_book")//表名public class Book {/** * 图书编号 */@Id("t_isbn")private String isbn;/** * 书名 */@Column("t_name")private String name;/** * 作者 */@Column("t_author")private String author;/** * 出版社 */@Column("t_publishing")private String publishing;/** * 出版时间 */@Column(value = "t_pubdate")private Date pubdate;/** * 价格 */@Column(value = "t_price")private double price;public String getIsbn() {return isbn;}public void setIsbn(String isbn) {this.isbn = isbn;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}public String getPublishing() {return publishing;}public void setPublishing(String publishing) {this.publishing = publishing;}public Date getPubdate() {return pubdate;}public void setPubdate(Date pubdate) {this.pubdate = pubdate;}public double getPrice() {return price;}public void setPrice(double price) {this.price = price;}@Overridepublic String toString() {return "书名: " + name + " 图书编号: " + isbn + " 作者: " + author+ " 出版社: " + publishing + " 出版时间: " + pubdate+ " 价格: " + price;}}

六、使用Junit4进行单元测试

package xml;import java.io.InputStream;import java.util.HashMap;import java.util.List;import java.util.Map;import model.Book;import org.junit.BeforeClass;import org.junit.Test;import util.DateUtils;import dao.GenericDao;import dao.JdbcGenericDaoImpl;/** * 测试泛型DAO的CRUD操作 */public class GenericDaoTest {private GenericDao<Book> bookDao = new JdbcGenericDaoImpl<Book>();private static InputStream is;@BeforeClasspublic static void setUpBeforeClass() throws Exception {is = XmlParserTest.class.getResourceAsStream("/books.xml");}@Testpublic void testSave() throws Exception {List<Book> books = SaxHelper.saxReader(is);for (Book book : books) {bookDao.save(book);}}@Testpublic void testStudentFindAll1() throws Exception {System.out.println("\n-------------更新、删除前,测试查询所有记录--------------------");List<Book> books = bookDao.findAllByConditions(null, Book.class);for (Book book : books) {System.out.println(book);}} @Testpublic void testDelete() throws Exception {System.out.println("\n-------------测试删除一条记录--------------------");bookDao.delete("9787111349662",Book.class);}@Testpublic void testGet() throws Exception {System.out.println("\n-------------测试查询一条记录--------------------");Book book = bookDao.get("9787121025389", Book.class);System.out.println(book);}@Testpublic void testUpdate() throws Exception {System.out.println("\n-------------测试修改一条记录--------------------");Book book = new Book();book.setIsbn("9787121025389");book.setName("JAVA面向对象编程");book.setAuthor("孙卫琴");book.setPublishing("电子工业出版社");book.setPubdate(DateUtils.string2Date("yyyy-MM-dd", "2006-07-01"));book.setPrice(50.6);bookDao.update(book);}@Testpublic void testStudentFindAll2() throws Exception {System.out.println("\n-------------更新、删除前,测试根据条件查询所有记录--------------------");Map<String,Object> sqlWhereMap = new HashMap<String, Object>();//sqlWhereMap.put("t_isbn", "9787111213826");//sqlWhereMap.put("t_name", "Java");sqlWhereMap.put("t_publishing", "机械工业出版社");//sqlWhereMap.put("t_pubdate", new Date(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2007-01-01 12:06:00").getTime()));List<Book> books = bookDao.findAllByConditions(null, Book.class);for (Book book : books) {System.out.println(book);}} }

七、测试结果

books.xml请见http://blog.csdn.net/xyang81/article/details/7247169

说明：功能比较简单，代码写得也比较笨拙，还有待优化，诚请指教！