paging.zip

package cn.kgc.dao; import java.sql.*; import java.util.*; /** * 数据库连接工具类 */ public class BaseDao { //mysql 8.0 以下版本的配置方式 // private static final String JDBC_DRIVER_8="com.mysql.jdbc.Driver"; // private static final String DB_URL_8 = "jdbc:mysql://localhost:3306/test"; //mysql 8.0 以上版本配置方式 // private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver"; // private static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC"; // private static final String USERNAME = "root"; // private static final String PASSWORD = "123456"; private static String JDBC_DRIVER; private static String DB_URL; private static String USERNAME; private static String PASSWORD; protected static Connection connection; protected static PreparedStatement preparedStatement;//这个是做insert update delete protected static ResultSet resultSet;//做select操作 //配置文件只需要加载一次，提供静态代码，当前类被加载到内存执行 static { //Resource.getBundle（） 可以去查找对应的 properties 文件 ResourceBundle bundle = ResourceBundle.getBundle("jdbc"); //bundle.getObject() 来获取配置文件对应的值 通过key来取值 JDBC_DRIVER = bundle.getString("jdbc.driver"); DB_URL = bundle.getString("jdbc.url"); USERNAME = bundle.getString("jdbc.username"); PASSWORD = bundle.getString("jdbc.password"); } /** * 1.配置驱动 */ static { try { Class.forName(JDBC_DRIVER); System.out.println("---------------------- 配置驱动..."); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 2.获取数据库连接 */ public static Connection getConnection() { try { connection = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD); System.out.println("---------------------- 获取数据库连接....."); } catch (SQLException throwables) { throwables.printStackTrace(); } return connection; } /** * 查询通用方法 * * @param sql 查询所需的sql语句，SQL中可以带问号，例如 “select * from table_name where id=?" * @param params 传入参数内容数组 * @return */ public static List<Map<String,Object>> commQuery(String sql, Object... params) { connection = getConnection(); List<Map<String,Object>> list=new ArrayList<>(); try { preparedStatement = connection.prepareStatement(sql); if (params != null && params.length > 0) { setValues(preparedStatement, params); //执行查询语句，返回查询结果 resultSet = preparedStatement.executeQuery(); } else { resultSet = preparedStatement.executeQuery(); } list= getList(resultSet); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { closeAll(connection,preparedStatement,resultSet); } return list; } /** * 增删修通用方法 * * @param sql * @param params * @return */ public static int commUpdate(String sql, Object... params) { //记录受影响行数 int result = 0; connection = getConnection(); try { preparedStatement = connection.prepareStatement(sql); if (params != null && params.length > 0) { setValues(preparedStatement, params); } result = preparedStatement.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { closeAll(connection, preparedStatement, resultSet); } return result; } /** * 给sql中的参数赋值 * * @param pst * @param params */ private static void setValues(PreparedStatement pst, Object... params) { for (int i = 0; i < params.length; i++) { try { pst.setObject(i + 1, params[i]); } catch (SQLException throwables) { throwables.printStackTrace(); } } } /** * 关闭资源 Connection、PreparedStatement、ResultSet */ public static void closeAll(Connection conn, PreparedStatement pst, ResultSet rs) { try { if (rs != null) { rs.close(); } if (pst != null) { pst.close(); } if (conn != null) { conn.close(); } } catch (SQLException throwables) { throwables.printStackTrace(); } } /** * 封装list * @param rs * @return */ public static List<Map<String, Object>> getList(ResultSet rs) { //声明一个集合，存放查询之后的数据 // 可以想一下二维数组 List<Map<String, Object>> list = new ArrayList<>(); try { //对结果集遍历 while (rs.next()) { //创建一个map集合，存放每一行的数据 Map<String, Object> map = new HashMap<>(); //查询列名 ResultSetMetaData metaData = rs.getMetaData(); //获取列的数量。从1开始。 查询的结果一共有多少列 int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { //查列名 String columnName = metaData.getColumnName(i); map.put(columnName, rs.getObject(i)); } list.add(map); } } catch (SQLException throwables) { throwables.printStackTrace(); } return list; } }