一、实现思路
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();
- }
- }
- }
- 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;
- }
- 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);
- }
- }
- }
- }
- 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
- 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();
- }
- 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();
- }
- 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;
- }
- 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;
- }
- }
- 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);
- }
- }
- }
- 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);
- }
- }
- }
七、测试结果