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

青旅半醒 2022-06-15 05:16 181阅读 0赞

## 一、实现思路 ##

###        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";
    
    	@Override
    	public 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);
    		
    		//拼接sql
    		StringBuilder 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);
    		//执行SQL
    		ps.execute();
    		JdbcDaoHelper.release(ps, null);
    		
    		System.out.println(sql + "\n" + clazz.getSimpleName() + "添加成功!");
    	}
    
    
    	@Override
    	public 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.");
    		}
    		
    		//拼装sql
    		String sql = "delete from " + tableName + " where " + idFieldName + "=?";
    		PreparedStatement ps = JdbcDaoHelper.getConnection().prepareStatement(sql);
    		ps.setObject(1, id);
    		//执行SQL
    		ps.execute();
    		JdbcDaoHelper.release(ps,null);
    		
    		System.out.println(sql + "\n" + clazz.getSimpleName() + "删除成功!");
    	}
    
    	@Override
    	public 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("?");
    		
    		//拼接sql
    		StringBuilder 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);
    		
    		//执行SQL
    		ps.execute();
    		JdbcDaoHelper.release(ps, null);
    		
    		System.out.println(sql + "\n" + clazz.getSimpleName() + "修改成功.");
    	}
    
    	@Override
    	public 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.");
    		}
    		
    		//拼装SQL
    		Map<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;
    	}
    
    	@Override
    	public 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);
    		
    		//拼装SQL
    		String 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);
    		}
    		
    		
    		//执行SQL
    		ResultSet 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 + "%");
    				else
    					ps.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 #####

##### 2）、Id #####

##### 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;
    	}
    
    	@Override
    	public 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;
    
    	@BeforeClass
    	public static void setUpBeforeClass() throws Exception {
    		is = XmlParserTest.class.getResourceAsStream("/books.xml");
    	}
    	
    	@Test
    	public void testSave() throws Exception {
    		List<Book> books = SaxHelper.saxReader(is);
    		for (Book book : books) {
    			bookDao.save(book);
    		}
    	}
    	
    	@Test
    	public void testStudentFindAll1() throws Exception {
    		System.out.println("\n-------------更新、删除前,测试查询所有记录--------------------");
    		List<Book> books = bookDao.findAllByConditions(null, Book.class);
    		for (Book book : books) {
    			System.out.println(book);
    		}
    	} 
    	
    	@Test
    	public void testDelete() throws Exception {
    		System.out.println("\n-------------测试删除一条记录--------------------");
    		bookDao.delete("9787111349662",Book.class);
    	}
    	
    	@Test
    	public void testGet() throws Exception {
    		System.out.println("\n-------------测试查询一条记录--------------------");
    		Book book = bookDao.get("9787121025389", Book.class);
    		System.out.println(book);
    	}
    	
    	@Test
    	public 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);
    	}
    	
    	@Test
    	public 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);
    		}
    	} 
    
    }

##### 七、测试结果 #####

![0_1329544576t660.gif][]

![0_132954459640qU.gif][]

books.xml请见[http://blog.csdn.net/xyang81/article/details/7247169][http_blog.csdn.net_xyang81_article_details_7247169]

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

[0_1329544576t660.gif]: /images/20220615/9df084d0ca9b46ea98fd133b014d87da.png
[0_132954459640qU.gif]: /images/20220615/0db701fba2b34fbebd3e11f2892af945.png
[http_blog.csdn.net_xyang81_article_details_7247169]: http://blog.csdn.net/xyang81/article/details/7247169