封装JDBC

import java.sql.*;
import java.util.*;
import java.lang.reflect.Field;
public class DBUtil {
    private static Connection conn = null;    //数据库连接对象
    private Statement stmt = null;            //数据库sql语句对象
    private ResultSet rs = null;            //数据库结果集对象

    private static final String DRIVER = "com.mysql.jdbc.Driver";//这是一个连接数据库必填的常量
    private static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false"; //数据库的URL 3308为端口  shxt是那个数据库
    private static final String USER = "root";  //数据库的账号
    private static final String PWD = "123456";    //数据库的密码
    private static DBUtil db = null;

    public static DBUtil getDB() {
        //判断是否为空，这样的方式更加节省资源
        if (db == null) {
            db = new DBUtil();//实例化对象
        }
        return db;
    }
    public DBUtil() {
    }

        //获得数据库连接，加载驱动
        public static Connection getConn() {
            //加载驱动
            try {
                Class.forName(DRIVER);
                try {
                    conn=DriverManager.getConnection(URL, USER, PWD);
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            return conn;
        }

    //获取类的属性
    private static String[] getFiledName(Class clazz) {
        Field[] fields = clazz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (int i = 0; i < fields.length; i++) {
            fieldNames[i] = fields[i].getName();
        }
        return fieldNames;
    }

    //增改sql的方法
    public int update(String sql) {
        int num = 0;
        conn = getConn();
        try {
            stmt = conn.createStatement();
            num = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        return num;
    }

    //查询字段、数据、数量的方法
    public Map query(String TableName) throws Exception {
        conn = getConn();
        String fieldName = null;
        String fieldType = null;
        String fieldValue = null;
        List valueList = new ArrayList();
        List nameList = new ArrayList();
        Map hashMap = new HashMap();
        List[] value = new List[0];
        List list = new ArrayList();
        String data = "";
        int i = 0;
        int rows = 0;
        try {
            String sql = "select * from " + TableName;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData resultMeta = rs.getMetaData();//得到结果集的结构信息，比如字段名，字段数等
            int columns = resultMeta.getColumnCount();
            int j = 0;
            while (rs.next()) {
                for (i = 1; i <= columns; i++) {
                    fieldName = new String(resultMeta.getColumnLabel(i));//得到每一列的列名
                    fieldName = fieldName.toLowerCase();
                    fieldType = new String(resultMeta.getColumnTypeName(i));//得到每一列的类型
                    fieldType = fieldType.toLowerCase();
                    fieldValue = rs.getString(fieldName);//得到相应列的值
                    ++j;
                    data += fieldValue + ",";
                    if (j <= columns) {
                        nameList.add(fieldName);
                    }
                    if (j % columns == 0) {
                        data = data.substring(0, data.length() - 1);
                        valueList.add(data);
                        data = "";
                        ++rows;
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        hashMap.put("name", nameList);
        hashMap.put("value", valueList);
        hashMap.put("count", rows);
        close();
        return hashMap;
    }

    //查询数量的方法
    public int Count(String TableName) {
        conn = getConn();
        int count = 0;
        ResultSet rs;
        try {
            stmt = conn.createStatement();
            String sql = "select count(1) from " + TableName;
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        return count;
    }

    //查询数据
    public List queryDate(String TableName, Class clazz) throws Exception {
        conn = getConn();
        List list = new ArrayList();
        try {
            String sql = "select * from " + TableName;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            //得到结果集的结构信息，比如字段名，字段数等
            ResultSetMetaData resultMeta = rs.getMetaData();
            int columns = resultMeta.getColumnCount();
            int i = 0;
            int j = 0;
            //业务对象的属性数组
            Field[] fields = clazz.getDeclaredFields();
            while (rs.next()) {
                Object obj = clazz.newInstance();
                for (i = 1; i <= columns; i++) {
                    Object value = rs.getObject(i);
                    //寻找该列对应的对象属性
                    for (j = 0; j < fields.length; j++) {
                        Field f = fields[j];
                        //如果匹配进行赋值
                        if (f.getName().equalsIgnoreCase(resultMeta.getColumnName(i))) {
                            boolean flag = f.isAccessible();
                            // 打开JavaBean的访问private权限
                            f.setAccessible(true);
                            f.set(obj, value);
                            f.setAccessible(flag);
                        }
                    }
                }
                list.add(obj);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        return list;
    }

    //增加
    public int addDate(Class clazz, String TableName, String date) throws SQLException {
        int num = 0;
        conn = getConn();
        try {
            stmt = conn.createStatement();
            String[] FiledName = getFiledName(clazz);
            String filedName = "";
            int length = FiledName.length;
            for (int i = 0; i < length; i++) {
                if (length == 1) {
                    filedName = FiledName[i];
                } else {
                    if (i == 0) {
                        filedName = FiledName[i];
                    } else {
                        filedName += "," + FiledName[i];
                    }
                }
            }
            String sql = "insert into " + TableName + " (" + filedName + ") values (" + date + ")";
            num = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        return num;
    }

    //修改
    public int updateDate(String TableName, String fileName, String date) throws SQLException {
        int num = 0;
        conn = getConn();
        try {
            stmt = conn.createStatement();
            String sql = "update " + TableName;
            num = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        return num;
    }

    //批量删除
    public int deleteDate(String TableName, String fieldName, String[] date) throws SQLException {
        int flag = 0;
        conn = getConn();
        stmt = conn.createStatement();
        if (date != null) { //判断数组是否为空，不能用length来判断，否则可能会报空指针异常。
            for (int i = 0; i < date.length; i++) {
                String sql = "delete from " + TableName + " where " + fieldName + "='" + date[i] + "'";
                flag = stmt.executeUpdate(sql);
            }
        }
        close();
        return flag;
    }

    //释放资源的方法
    private  void close() {
        try {
            if (rs != null) {
                rs.close();
            }

            if (stmt != null) {
                stmt.close();
            }

            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}