使用注解和 实现动态Sql

一、定义注解

package util;

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

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Column {
    public boolean isId() default false;
    public String name() default "";
    public String caption() default "";
    public boolean nullable() default true;
    public int length() default 20;

}

二、定义Dao层

package dao;

import java.sql.SQLException;
import java.util.ArrayList;

public interface SqlUtilDao {
    //添加一个对象
    public Boolean add(Object object) throws IllegalAccessException, SQLException;
    
    //修改一个对象
    public Boolean update(Object object) throws SQLException, IllegalAccessException;
    
    //删除一个对象
   public Boolean delete(int id, Object object) throws IllegalAccessException, SQLException;
   
   //查询所有
    public ArrayList<Object> query(Object object) throws IllegalAccessException, SQLException;
    
    //指定某个字段查询
    public ArrayList<Object> queryByCondition(Object object, String name, Integer condition) throws IllegalAccessException, SQLException;
}

二、定义DaoImpl

package daoImpl;

import dao.SqlUtilDao;
import util.Column;
import util.DBUtil;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class SqlUtilDaoImpl implements SqlUtilDao {

    /**
     * 新增一条记录
     * @param object
     * @return
     * @throws IllegalAccessException
     * @throws SQLException
     */
    @Override
    public Boolean add(Object object) throws IllegalAccessException, SQLException {
        Class clazz = object.getClass();
        //获取实体属性
        Field[] fields = clazz.getDeclaredFields();
        //申明StringBuffer的Sql
        StringBuffer sql = new StringBuffer();

        //拼接Sql
        sql.append("insert into ");
        sql.append("`" + clazz.getSimpleName().toLowerCase() + "`");
        sql.append(" (");
        //遍历实体属性
        for(Field field:fields)
        {
            field.setAccessible(true);
            //获取属性的注解
            Column column = field.getAnnotation(Column.class);
            //没有注解或者字段为id就跳过
            if(column == null) continue;
            if(column.isId() == true)
            {
                continue;
            }
            String columnName = column.name();
            sql.append("`" + columnName + "`,");
        }
        sql.deleteCharAt(sql.length()-1);
        sql.append(") values (");

        //为字段赋值
        for(Field field:fields)
        {
            //将所有属性设置为外部可见
            field.setAccessible(true);
            Column column = field.getAnnotation(Column.class);
            //没有注解或者字段名为id就跳过
            if(column == null) continue;
            if(column.isId() == true)
            {
                continue;
            }
            //获取属性值
            Object value = field.get(object);
            String valueString ="";
            if(value instanceof String)
            {
                valueString="'"+value+"'";
            }
            else
            {
                valueString=value.toString();
            }
            //拼接Sql
            sql.append(valueString).append(",");
        }
        //删除最后的逗号
        sql.deleteCharAt(sql.length()-1);
        sql.append(")");
        String resuleSql = sql.toString();
        new DBUtil();
        Connection connection = DBUtil.open();
        Statement statement =  connection.createStatement();
        try {
            //输出SQL并执行Sql
            System.out.println(sql);
            statement.execute(resuleSql);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(connection);
        }
        return true;
    }

    /**
     * 修改一条记录
     * @param object
     * @return
     * @throws SQLException
     * @throws IllegalAccessException
     */
    @Override
    public Boolean update(Object object) throws SQLException, IllegalAccessException {
        Class clazz = object.getClass();
        //获取实体属性
        Field[] fields = clazz.getDeclaredFields();
        //申明StringBuffer的Sql
        StringBuffer sql = new StringBuffer();

        //声明id
        Integer id = 0;

        //拼接Sql
        sql.append("update ");
        sql.append("`" + clazz.getSimpleName().toLowerCase() + "`");
        sql.append(" set ");
        for(Field field:fields)
        {
            field.setAccessible(true);
            Column column = field.getAnnotation(Column.class);
            if(column == null) continue;
            if(column.isId() == true)
            {
                id = (Integer)field.get(object);
                continue;
            }
            Object value = field.get(object);
            String columnName = column.name();
            String valueString ="";
            if(value instanceof String)
            {
                valueString="'"+value+"'";
            }
            else
            {
                valueString=value.toString();
            }
            sql.append("`" + columnName + "` " + "= " + valueString).append(",");
        }
        sql.deleteCharAt(sql.length()-1);
        sql.append(" where id = " + id.toString());
        String resuleSql = sql.toString();
        new DBUtil();
        Connection connection = DBUtil.open();
        Statement statement =  connection.createStatement();
        try {
            System.out.println(sql);
            statement.execute(resuleSql);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(connection);
        }
        return true;
    }

    /**
     * 删除一条记录
     * @param id
     * @param object
     * @return
     * @throws SQLException
     */
    @Override
    public Boolean delete(int id, Object object) throws SQLException {
        Class clazz = object.getClass();
        StringBuffer sql = new StringBuffer();

        //拼接Sql
        sql.append("delete from ");
        sql.append("`" + clazz.getSimpleName().toLowerCase() + "`");
        sql.append(" where id = " + id);

        String resuleSql = sql.toString();
        new DBUtil();
        Connection connection = DBUtil.open();
        Statement statement =  connection.createStatement();
        try {
            System.out.println(sql);
            statement.execute(resuleSql);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(connection);
        }
        return true;
    }

    /**
     * 查询所有记录
     * @param object
     * @return
     * @throws SQLException
     */
    @Override
    public ArrayList<Object> query(Object object) throws  SQLException {
        Class clazz = object.getClass();
        //获取实体属性
        Field[] fields = clazz.getDeclaredFields();
        //申明StringBuffer的Sql
        StringBuffer sql = new StringBuffer();

        //获取对象属性
        Class<?> attribute = object.getClass();

        //拼接Sql
        sql.append("select ");
        for(Field field:fields)
        {
            field.setAccessible(true);
            Column column = field.getAnnotation(Column.class);

            String columnName = column.name();
            sql.append("`" + columnName + "` ").append(",");
        }
        sql.deleteCharAt(sql.length()-1);

        sql.append(" from " + "`" + clazz.getSimpleName() + "`");

        //将Sql解析为String类型
        String resuleSql = sql.toString();
        new DBUtil();
        Connection connection = DBUtil.open();
        Statement statement =  connection.createStatement();
        try {
            System.out.println(sql);
            //执行Sql语句
            ResultSet resulSet = statement.executeQuery(resuleSql);
            ArrayList<Object> list=new ArrayList();
            while(resulSet.next()) {
                Object obj = attribute.newInstance();
                for (int i = 0; i < fields.length; i++) {
                    //获取属性类型
                    String type = fields[i].getType().getSimpleName();
                    //获取属性名
                    String tName = fields[i].getName();
                    //给该属性赋值
                    if("String".equals(type)){
                        fields[i].set(obj, resulSet.getString(tName));
                    }else if("Date".equals(type)){
                        fields[i].set(obj, resulSet.getDate(tName));
                    }else if("int".equals(type)){
                        fields[i].set(obj, resulSet.getInt(tName));
                    }else if("Integer".equals(type)){
                        fields[i].set(obj, resulSet.getInt(tName));
                    }else if("double".equals(type)){
                        fields[i].set(obj, resulSet.getDouble(tName));
                    }else if("float".equals(type)){
                        fields[i].set(obj, resulSet.getFloat(tName));
                    }
                }
                list.add(obj);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            DBUtil.close(connection);
        }
    }

/**
 * 根据某个属性查询
 * @param object
 * @param name
 * @param condition
 * @return
 * @throws IllegalAccessException
 * @throws SQLException
 */
@Override
public ArrayList<Object> queryByCondition(Object object, String name, Integer condition) throws IllegalAccessException, SQLException {
    Class clazz = object.getClass();
    //获取实体属性
    Field[] fields = clazz.getDeclaredFields();
    //申明StringBuffer的Sql
    StringBuffer sql = new StringBuffer();

    //获取对象属性
    Class<?> attribute = object.getClass();

    //拼接Sql
    sql.append("select ");
    for(Field field:fields)
    {
        field.setAccessible(true);
        String fieldName = "`" + field.getName() + "`";
        sql.append(fieldName).append(",");
    }
    sql.deleteCharAt(sql.length()-1);

    sql.append(" from `" + clazz.getSimpleName() + "`");
    sql.append(" where " + name + " = " + condition);

    //将Sql解析为String类型
    String resuleSql = sql.toString();
    new DBUtil();
    Connection connection = DBUtil.open();
    Statement statement =  connection.createStatement();
    try {
        System.out.println(sql);
        //执行Sql语句
        ResultSet resulSet = statement.executeQuery(resuleSql);
        ArrayList<Object> list=new ArrayList();
        while(resulSet.next()) {
            Object obj = attribute.newInstance();
            for (int i = 0; i < fields.length; i++) {
                //获取属性类型
                String type = fields[i].getType().getSimpleName();
                //获取属性名
                String tName = fields[i].getName();
                //给该属性赋值
                if("String".equals(type)){
                    fields[i].set(obj, resulSet.getString(tName));
                }else if("Date".equals(type)){
                    fields[i].set(obj, resulSet.getDate(tName));
                }else if("int".equals(type)){
                    fields[i].set(obj, resulSet.getInt(tName));
                }else if("Integer".equals(type)){
                    fields[i].set(obj, resulSet.getInt(tName));
                }else if("double".equals(type)){
                    fields[i].set(obj, resulSet.getDouble(tName));
                }else if("float".equals(type)){
                    fields[i].set(obj, resulSet.getFloat(tName));
                }
            }
            list.add(obj);
        }
        return list;
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    } finally {
        DBUtil.close(connection);
    }
}

}