jdbc简易泛型dao

最新推荐文章于 2024-08-04 11:07:52 发布

she_lover

最新推荐文章于 2024-08-04 11:07:52 发布

阅读量433

点赞数

分类专栏： jdbc 文章标签： dao java

jdbc 专栏收录该内容

0 篇文章 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

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接口JDBC实现类JdbcGenericDaoImpl

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();

}

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;

}

5、定义一个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;
    }
}

6、使用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);
        }
    } 

}

7、测试结果
这里写图片描述
说明：该代码是早期的版本，功能比较简单，代码写得也比较笨拙，还有待优化，但是思路是非常好的，日后采用Spring进行改造，另外注解可以直接采用javax.persistence.*里的，不用自己再写了，否则容易产生歧义

转自：http://blog.csdn.net/wangpeng047/article/details/8615843

she_lover

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
jdbc简易泛型dao

一、实现思路 1、定义3个Annotation（注解）：Entity、Id、Column，Entity作用于Type级别，用于标识JavaBean与数据库表名的映射关系。Id作用于Field级别，用于标识JavaBean中ID属性与表中ID字段的映射关系，Column作用于Field级别，用于标识JavaBean中除ID属性外的其它属性与表中字段的映射关系。 2、在Dao实现类中，通过反射API
复制链接

扫一扫

专栏目录