java之jdbc学习——QueryRunner

jdbc是ORM框架的基础，但将数据库中的表映射到java对象，并进行增删改查，并不是一件简单的事情。

涉及到jdbc、注解和反射的一些基础知识。

以下内容来自网友的分享，并做了一些增减，作为笔记记录与此。

一、首先是数据表

假如数据库中有一张表app_base_log,在java中建立它对应的类。

@Table(name = "app_base_log")

public class AppBaseLog implements Serializable {

    private static final long serialVersionUID = -5206763271093824440L;

    @Column(name = "dvid")

    private String dvid;

    @Column(name = "type")

    private String type;

    public AppBaseLog() {

    }

    public AppBaseLog(String dvid, String type) {

        this.dvid = dvid;

        this.type = type;

    }

    public String getDvid() {

        return dvid;

    }

    public void setDvid(String dvid) {

        this.dvid = dvid;

    }

    public String getType() {

        return type;

    }

    public void setType(String type) {

        this.type = type;

    }

}

这里的注解都是自定义的，并非来自hibernate和mybatis。

@Target(ElementType.TYPE)

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface Table {

    String name();

}

/**

 * Created by wangbin10 on 2018/8/13.

 */

@Target(ElementType.FIELD)

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface Id {

    String name();

    String type() default "int";

    int length() default 20;

    int increment() default 1;

}

/**

 * Created by wangbin10 on 2018/8/13.

 */

@Target(ElementType.FIELD)

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface Column {

    String name();

    String type() default "string";

    int length() default 20;

}

二、核心代码

/**

 * Created by wangbin10 on 2018/8/13.

 */

public class SimpleORM<E> {

    @Autowired

    private QueryRunner queryRunner;

    /**

     * 添加一个对象

     */

    public int add(E element) throws SQLException {

        Class clazz = element.getClass();

        String tableName = getTableName(clazz);

        Field[] fields = clazz.getDeclaredFields();

        String sql = getInsertSql(tableName, fields.length);

        //通过对象和属性列表获取对应属性的值

        Object[] params = getSqlParams(element, fields);

        int result = -1;

        result = queryRunner.update(sql, params);

        return result;

    }

    /**

     * 根据对象获取sql语句的参数

     */

    private Object[] getSqlParams(E element, Field[] fields) {

        Object[] params = new Object[fields.length];

        for (int i = 0; i < fields.length; i++) {

            fields[i].setAccessible(true);

            try {

                params[i] = fields[i].get(element);

            } catch (IllegalAccessException e) {

                e.printStackTrace();

            }

        }

        return params;

    }

    /**

     * 获取插入对象的sql语句

     */

    private String getInsertSql(String tableName, int length) {

        StringBuilder sql = new StringBuilder();

        sql.append("insert into ").append(tableName).append(" values(");

        for (int i = 0; i < length; i++) {

            sql.append("?,");

        }

        sql.deleteCharAt(sql.length() - 1);

        sql.append(")");

        return sql.toString();

    }

    /**

     * 根据值对象的注解获取其对应的表名称

     */

    private String getTableName(Class<E> clazz) {

        boolean existTableAnno = clazz.isAnnotationPresent(Table.class);

        if (!existTableAnno) {

            throw new RuntimeException(clazz + " 没有Table注解.");

        }

        Table tableAnno = clazz.getAnnotation(Table.class);

        return tableAnno.name();

    }

    /**

     * 更新一个对象

     */

    public int update(E element) throws SQLException {

        Class clazz = element.getClass();

        Field[] fields = clazz.getDeclaredFields();

        Object[] params = new Object[fields.length];

        String sql = getUpdateSql(element, params);

        int result = queryRunner.update(sql, params);

        return result;

    }

    /**

     * 获取更新记录的sql语句并更新参数

     */

    private String getUpdateSql(E element, Object[] params) {

        Class clazz = element.getClass();

        String tableName = getTableName(clazz);

        Field[] fields = clazz.getDeclaredFields();

        StringBuilder updateSql = new StringBuilder();

        updateSql.append("update ").append(tableName).append(" set ");

        String idName = "";

        int index = 0; // 记录参数的位置

        for (int i = 0; i < fields.length; i ++){

            fields[i].setAccessible(true);

            // 找到id对应的列名和值

            if (fields[i].isAnnotationPresent(Id.class)){

                idName = fields[0].getAnnotation(Id.class).name();

                try {

                    params[params.length-1] = fields[i].get(element);  // id作为update sql 的最后一个参数

                    if (params[params.length-1] == null)

                        throw new RuntimeException(element + "没有Id属性!");

                } catch (IllegalAccessException e) {

                    System.out.println(e.getMessage());

                    System.out.println("获取" + element + "的属性值失败！");

                }

            }

            boolean isPresent = fields[i].isAnnotationPresent(Column.class);

            if (isPresent) {

                Column column = fields[i].getAnnotation(Column.class);

                String columnName = column.name();

                updateSql.append(" ").append(columnName).append( " = ? ,");

                try {

                    params[index++] = fields[i].get(element);  // 添加参数到数组，并更新下标

                } catch (IllegalAccessException e) {

                    System.out.println(e.getMessage());

                    System.out.println("获取" + element + "的属性值失败！");

                }

            }

        }

        updateSql.deleteCharAt(updateSql.length()-1);

        updateSql.append("where ").append(idName).append(" = ?");

        return updateSql.toString();

    }

    /**

     * 根据id删除一个对象

     */

    public int delete(String id, Class<E> clazz) throws SQLException {

        String deleteSql = getDeleteSql(clazz);

        int  result = queryRunner.update(deleteSql, id);

        return result;

    }

    private String getDeleteSql(Class<E> clazz) {

        String tableName = getTableName(clazz);

        String idName = getIdName(clazz);

        StringBuilder deleteSql = new StringBuilder();

        deleteSql.append("delete from ").append(tableName).append(" where ").append(idName).append(" = ?");

        return deleteSql.toString();

    }

    /**

     * 获取id属性对应的列名，根据值对象的字节码

     */

    private String getIdName(Class<E> clazz) {

        Field[] fields = clazz.getDeclaredFields();

        String idName = null;

        for (Field field : fields) {

            field.setAccessible(true);

            if (field.isAnnotationPresent(Id.class)) {

                idName = field.getAnnotation(Id.class).name();

            }

        }

        if (idName == null) {

            throw new RuntimeException(clazz + "没有指定@Id注解!");

        }

        return idName;

    }

    /**

     * 根据id查找一个对象

     */

    public E query(String id, Class<E> clazz) throws SQLException {

        String selectSql = getSelectSql(clazz);

        E result = queryRunner.query(selectSql, new BeanHandler<E>(clazz), id);

        return result;

    }

    private String getSelectSql(Class<E> clazz) {

        String tableName = getTableName(clazz);

        String idName = getIdName(clazz);

        StringBuilder selectSql = new StringBuilder();

        selectSql.append("select * from ").append(tableName).append(" where ").append(idName).append(" = ?");

        return selectSql.toString();

    }

    public List<E> queryToList(String sql, Class<E> clazz) throws SQLException {

        List<E>  result = queryRunner.query(sql, new BeanListHandler<E>(clazz));

        return result;

    }

    public Object[] queryToArray(String sql) throws SQLException {

        Object[]  result = queryRunner.query(sql, new ArrayHandler());

        return result;

    }

    /**

     * 查找该类对应的表中记录的数目（对应记录的数目）

     */

    public int count(Class<E> clazz) throws SQLException {

        String tableName = getTableName(clazz);

        String sql = "select count(*) from " + tableName;

        long  result = (Long) queryRunner.query(sql, new ScalarHandler());

        return (int) result;

    }

}

巴特西

java之jdbc学习——QueryRunner

最新文章

热门文章