依靠反射 手写DButils

闲来无事，写个dbutils玩玩，不完善，满足基本增删改查，上代码

1、Dbutils

package db;

import annotation.Table;
import java.util.*;
import java.sql.*;
import java.lang.reflect.Field;

/**
 *
 * @author xjy
 * @param <T>
 */
public class DbUtil<T> {

    private static DataBase dataBase = null;
    private static Connection con = null;
    private static Statement st = null;

    public DbUtil() {
        dataBase = new DataBase();
    }

    @SuppressWarnings("null")
    public List<T> getAll(T bean) throws Exception {

        List<T> list = new ArrayList<>();
        Class<T> clazz = (Class<T>) bean.getClass();
        Table table = clazz.getDeclaredAnnotation(Table.class);
        String name = table.name();

        String tableName;
        if (!"".equals(name)) {
            tableName = name;
        } else {
            tableName = clazz.getSimpleName().toLowerCase();
        }

        StringBuilder sql = new StringBuilder("select * from " + tableName);

        if (bean != null) {
            sql.append(" where ");
            Field[] declaredFields = clazz.getDeclaredFields();

            for (Field declaredField : declaredFields) {
                //打开私有访问
                declaredField.setAccessible(true);
                String name1 = declaredField.getName();
                Object value = declaredField.get(bean);
                if (value != null && !"id".equals(name1)) {
                    sql.append(name1).append("='").append(value).append("' and ");
                }
            }
            sql.append("1=1");
        }

        con = dataBase.getConnection();
        st = dataBase.getStatement();

        ResultSet rs = st.executeQuery(sql.toString());

        Field[] fields = clazz.getDeclaredFields();
        while (rs.next()) {

            T t = clazz.newInstance();

            for (Field field : fields) {
                String fName = field.getName();
                String str = rs.getString(fName);
                field.setAccessible(true);

                // 如果类型是Integer  
                if (field.getGenericType().toString().equals("class java.lang.Integer")) {
                    field.set(t, Integer.valueOf(str));
                } else {
                    field.set(t, str);
                }

            }

            list.add(t);
        }

        return list;
    }

    public int add(T bean) throws Exception {
        con = dataBase.getConnection();
        st = dataBase.getStatement();
        Class<T> clazz = (Class<T>) bean.getClass();
        Table table = clazz.getDeclaredAnnotation(Table.class);
        String name = table.name();

        String tableName;
        if (!"".equals(name)) {
            tableName = name;
        } else {
            tableName = clazz.getSimpleName().toLowerCase();
        }

        Field[] fields = clazz.getDeclaredFields();

        StringBuilder sql = new StringBuilder("insert into " + tableName + "(");

        for (Field field : fields) {
            String fName = field.getName();
            if (!"id".equals(fName)) {
                sql.append(fName).append(",");

            }
        }

        sql.deleteCharAt(sql.length() - 1).append(") values(");

        Field[] declaredFields = clazz.getDeclaredFields();

        for (Field declaredField : declaredFields) {

            if (!"id".equals(declaredField.getName())) {
                //打开私有访问
                declaredField.setAccessible(true);
                Object value = declaredField.get(bean);
                sql.append("'").append(value).append("',");
            }

        }

        sql.deleteCharAt(sql.length() - 1).append(")");
        int result = 0;

        try {
            result = st.executeUpdate(sql.toString());
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } finally {

        }
        return result;
    }

    @SuppressWarnings("FinallyDiscardsException")
    public List<Map<String, Object>> querySql(String sql) {
        List<Map<String, Object>> list = new ArrayList<>();

        con = dataBase.getConnection();
        st = dataBase.getStatement();
        @SuppressWarnings("UnusedAssignment")
        ResultSet rs = null;
        try {
            rs = st.executeQuery(sql);
            ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据
            int columnCount = md.getColumnCount();   //获得列数
            while (rs.next()) {
                Map<String, Object> rowData = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());

        } finally {
            dataBase.closeConnection();
            return list;
        }
    }

    @SuppressWarnings("FinallyDiscardsException")
    public int executeSql(String sql) {
        con = dataBase.getConnection();
        st = dataBase.getStatement();
        int result = 0;
        try {
            result = st.executeUpdate(sql);
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } finally {
            dataBase.closeConnection();
            return result;
        }
    }

    @SuppressWarnings("MismatchedReadAndWriteOfArray")
    public int update(T bean) throws Exception {
        con = dataBase.getConnection();
        st = dataBase.getStatement();
        Class<T> clazz = (Class<T>) bean.getClass();
        Table table = clazz.getDeclaredAnnotation(Table.class);
        String name = table.name();

        String tableName;
        if (!"".equals(name)) {
            tableName = name;
        } else {
            tableName = clazz.getSimpleName().toLowerCase();
        }

        StringBuilder sql = new StringBuilder("update " + tableName + " set ");

        Field[] declaredFields = clazz.getDeclaredFields();

        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            //打开私有访问
            String name1 = declaredField.getName();
            Object value = declaredField.get(bean);
            if (value != null && !"id".equals(name1)) {
                sql.append(name1).append("='").append(value).append("',");
            }
        }
        Field field1 = clazz.getDeclaredField("id");
        field1.setAccessible(true);

        sql.deleteCharAt(sql.length() - 1).append(" where id =").append(field1.get(bean));

        int result = 0;

        try {
            result = st.executeUpdate(sql.toString());
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } finally {
        }
        return result;
    }
}

 

 

2、注解Table

package annotation;

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

/**
 *
 * @author xjy
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Table {

    public String name() default "";

}

 

 

3、database，获取连接等，这里用的sqlserver，其他自己发挥咯

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author Administrator
 */
public class DataBase {
  
    private String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=db_car";
    private String userName = "sa";
    private String userPasswd = "123456";
    private Connection connection;
    private Statement statement;
    public DataBase(){
        try {
            Class.forName(driverName);
        }
        catch (ClassNotFoundException cnfex){
            System.err.println("装载 JDBC/ODBC 驱动程序失败。");
            cnfex.printStackTrace();
            System.exit(1);
        }
    } 
    //取得与数据库的连接
    public Connection getConnection(){
        try {
            //捕获连接数据库异常
            connection = DriverManager.getConnection(url, userName, userPasswd);
        } 
        catch (SQLException sqlex){
            System.err.println("无法连接数据库");
            sqlex.printStackTrace();
            System.exit(1);
        }
        finally {
            return connection;
        }
    }
    //取得 statement
    public Statement getStatement(){ 
        try {
            if (connection != null){
            statement = connection.createStatement();
            }
        } 
        catch (SQLException sqlex){
            System.err.println("无法取得 Statement");
            sqlex.printStackTrace();
            System.exit(1);
        }
        finally{
            return statement;
        }
    }
    public void closeConnection(){
        try {
            if (null != statement){
                statement.close(); 
            }
            if (null != connection){
                connection.close();
            }
            statement = null;
            connection = null;
            } 
            catch (Exception e){
                e.printStackTrace();
            }
        } 
    }

 

所需驱动文件：https://files.cnblogs.com/files/xujingyang/sqljdbc4.zip

顺便说下，开发工具玩的是NetBeans