Dao底层查询工具类

1.引入几个注解，用于处理Java类名与数据表、Java类属性字段与数据库列名不同的情况

package annotion;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @Description:表名
 * @Author one world
 * @Date 2020/9/12 0012 11:45
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Table {
    /**
     * 返回注解表名值
     * @return
     */
    String value() default "";
}

package annotion;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @Description:注解于字段，该字段为主键字段
 * @Author one world
 * @Date 2020/9/12 0012 11:28
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Id {
    /**
     * 返回注解ID值
     * @return
     */
    String value() default "";
}

package annotion;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @Description:该字段说明该字段为关联关系，为外部键位的主键
 * @Author one world
 * @Date 2020/9/12 0012 12:25
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Relation {
    /**
     * 返回注解关联主键值
     * @return
     */
    String value() default "";
}

package annotion;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @Description:注解于字段，对应数据库中的列属性
 * @Author one world
 * @Date 2020/9/12 0012 11:31
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Column {
    /**
     * 返回注解列值
     * @return
     */
    String value() default "";
}

2.封装Dao底层工具类

package utils;


import annotion.Column;
import annotion.Id;
import annotion.Relation;
import annotion.Table;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: 底层查询工具类
 * @Author one world
 * @Date 2020/9/20 0020 15:08
 */
public class DaoUtil{


    private static final Logger logger = LoggerFactory.getLogger(DaoUtil.class);
    /**
     * 泛型返回单个实体
     * @param clazz  需要返回的实体类型的Class 类似于Mybatis中的resultType中你所指定的返回的实体类型
     * @param sql    传入的SQL语句
     * @param params  SQL参数
     * @param <T>     声明的T
     * @return        单个实体
     */
    public static<T> T query(Class<T> clazz , String sql, Object... params) {
        T t = null;
        Map<String,String> map = getNameAndValue(clazz);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = ConnUtil.getConnection();
            ps = conn.prepareStatement(sql);
            if(params!=null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            rs = ps.executeQuery();
            if(rs.next()){
                t= clazz.newInstance();
                t = DaoUtil.newInstance(t,rs,clazz);
            }
            logger.info("调用query方法执行的SQL语句为："+sql);
        } catch (SQLException | InstantiationException | IllegalAccessException e) {
            logger.error("DaoUtil中的query方法异常",e);
            throw new RuntimeException("DaoUtil中的query方法异常");

        }finally {
            ConnUtil.close(conn, ps, rs);
        }
        return t;
    }

    /**
     *
     * @param clazz  需要返回的实体类型Class
     * @param sql    传入的SQL语句
     * @param params  SQL参数
     * @param <T>     声明的T
     * @return         实体list
     */
    public static<T> List<T> queryList(Class<T> clazz , String sql, Object... params) {
        T t = null;
        List<T> list = new ArrayList<>();
        Map<String,String> map = getNameAndValue(clazz);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = ConnUtil.getConnection();
            ps = conn.prepareStatement(sql);
            if(params!=null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            rs = ps.executeQuery();
            while(rs.next()){
                t= clazz.newInstance();
                t = DaoUtil.newInstance(t,rs,clazz);
                list.add(t);
            }
            logger.info("调用queryList方法执行的SQL语句为："+sql);
        } catch (SQLException | InstantiationException | IllegalAccessException e) {
            logger.error("DaoUtil中的queryList方法异常",e);
            throw  new RuntimeException("DaoUtil工具类queryList方法异常");
        }finally {
            ConnUtil.close(conn, ps, rs);
        }
        return list;
    }

    /**
     * 数据库更新语句
     * @param sql
     * @param params
     * @return  true/false
     */
    public static boolean update(String sql, Object...params) {
        Connection conn = ConnUtil.getConnection();
        PreparedStatement stmt = null;
        System.out.println("SQL语句为："+sql);
        try {
            conn.setAutoCommit(false);
            stmt = conn.prepareStatement(sql);
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    //数据库的下标是从1开始的？？？OK受教了
                    stmt.setObject(i + 1, params[i]);
                }
            }
            stmt.executeUpdate();
            conn.commit();
            logger.info("update方法执行的SQL语句为："+sql);
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                logger.error("DaoUtil的update方法中的回滚操作异常",ex);
            }
            logger.error("DaoUtil的update方法异常",e);
        }finally {
            ConnUtil.close(conn, stmt, null);
        }
        return true;
    }

    /**
     * 单个查询，此处主要用于查询查询总数量，用于分页
     * @param sql
     * @param params
     * @return Object
     */
    public static Object getOne(String sql, Object... params) {
        Object obj = null;
        Connection conn = ConnUtil.getConnection();
        ResultSet rs = null;
        PreparedStatement stmt  =null;
        try {
            stmt = conn.prepareStatement(sql);

            for (int i = 0; i < params.length; i++) {
                //数据库的下标是从1开始的？？？OK受教了
                if (params != null && params[0] != null) {
                    stmt.setObject(i + 1, params[i]);
                }
            }
            conn.setAutoCommit(false);
            rs = stmt.executeQuery();
            if(rs.next()){
                obj = rs.getObject(1);
            }
            logger.info("调用getOne方法执行的SQL语句为："+sql);
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                logger.error("DaoUtil的getOne方法中的回滚操作异常",ex);
            }
            logger.error("DaoUtil类getOne方法异常",e);
        }finally {
            ConnUtil.close(conn, stmt, rs);
        }
        return obj;
    }

    /**
     * 获取列名和值===这里是用值注解的括号中的值如@Id("cid") 这里的键值对就是 （注解括号中的值，Java类属性名)
     * @param clazz
     * @return 实体类字段名：数据库字段名（注解中的字段名）
     */
    public static Map<String,String> getNameAndValue(Class clazz){
        Map<String,String> map = new HashMap<>();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field :fields){
            if (field.isAnnotationPresent(Column.class)){
                String name = field.getAnnotation(Column.class).value();
                if(name==null||name.equals("")){
                    name = field.getName();
                }
                map.put(name, field.getName());
            }else if(field.isAnnotationPresent(Relation.class)){
                String name = field.getAnnotation(Relation.class).value();
                map.put(name, field.getName());
            }else if(field.isAnnotationPresent(Id.class)){
                String id = field.getAnnotation(Id.class).value();
                if(id==null||id.equals("")){
                    id = field.getName();
                }
                map.put(id, field.getName());
            }else{
                map.put(field.getName(), field.getName());
            }
        }
        return map;
    }

    /**
     * 获取Table名，有注解则为id注解中的值，否则默认为类名
     * @param clazz
     * @return String
     */
    public static String getTable(Class clazz){
        String tableName = "";
        if(clazz.isAnnotationPresent(Table.class)){
             Table table = (Table) clazz.getAnnotation(Table.class);
            tableName = table.value();
        }else{
            tableName = clazz.getSimpleName();
        }
        return tableName;
    }

    /**
     * 获取主键注解中的值，为空时，是字段名，不为空时，为括号中的值
     * @param clazz
     * @return String
     */
    public static String getPK(Class clazz){
        String id = null;
        Field[] fields = clazz.getDeclaredFields();
        for(Field field:fields){
            field.setAccessible(true);
            if(field.isAnnotationPresent(Id.class)){
                id = field.getAnnotation(Id.class).value();
                if(id==null||id.equals("")){
                    id = field.getName();
                }
                break;
            }
        }
        return id;
    }

    /**
     * 用于获取关联注解中的值
     * @param clazz
     * @return
     */
    public static String getRelation(Class clazz){
        String relation ="";
        Field[] fields = clazz.getDeclaredFields();
        for(Field field:fields){
            field.setAccessible(true);
            if(field.isAnnotationPresent(Relation.class)){
                relation = field.getAnnotation(Relation.class).value();
                break;
            }
        }
        return relation;
    }


    /**
     * 用于创建单个实例
     * @param t
     * @param rs
     * @param clazz
     * @param <T>
     * @return t
     */
    public static <T> T newInstance(T t,ResultSet rs,Class clazz){
        Map<String,String> map = getNameAndValue(clazz);
        String rtable = null;
        try{
            for(String key:map.keySet()){
                Field field = clazz.getDeclaredField(map.get(key));
                field.setAccessible(true);
                if(field.isAnnotationPresent(Relation.class)){
                    Class rClass = field.getType();
                    if(rtable==null){
                        rtable = getTable(rClass);
                    }
                    Object object = rClass.newInstance();
                    Field[] fields = rClass.getDeclaredFields();
                    for(Field rField : fields){
                        if(rField.isAnnotationPresent(Id.class)){
                            rField.setAccessible(true);
                            rField.set(object, rs.getObject(key));
                            break;
                        }
                    }
                    field.set(t,object);

                }else{
                        field.set(t, rs.getObject(key));
                }
            }
        }catch (NoSuchFieldException | InstantiationException | SQLException | IllegalAccessException e){
            logger.error("DaoUtil类newsInstance方法异常",e);
        }

       return t;
    }

}