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(); } }}
