java jdbc 两层轻量封装

来源：互联网发布：学汉语翻译软件编辑：程序博客网时间：2024/06/05 10:52

package db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import dao.DemoDao;public class DB {    private final static String username = "root";    private final static String password = "tiger";    private final static String url = "jdbc:mysql://localhost/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";    private final static String url1 = "jdbc:mysql://localhost/yaycrawler?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";    public static Connection getConnection() throws SQLException {        Connection conn = null;        try {            Class.forName("com.mysql.cj.jdbc.Driver");// 加载jdbc驱动            // 加载完后可以通过DriverManager获取数据库连接            conn = DriverManager.getConnection(url, username, password);            conn.setAutoCommit(false);// 关闭事务自动提交        } catch (ClassNotFoundException e) {            System.out.println("找不到驱动");            e.printStackTrace();            return null;        }        return conn;    }    // 关闭连接方法    public static void closeConnection(Connection conn) throws SQLException {        if (conn != null) {            conn.close();        }    }    // 关闭结果集方法    public static void closeResultSet(ResultSet rs) throws SQLException {        if (rs != null) {            rs.close();        }    }    // 关闭查询器方法    public static void closePreparedStatement(PreparedStatement ps) throws SQLException {        if (ps != null) {            ps.close();        }    }    public static List<Map<String, Object>> toMap(ResultSet rs) throws SQLException {        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();        int maxCol;        ResultSetMetaData rsmd = rs.getMetaData();        maxCol = rsmd.getColumnCount();        // System.out.println("查询时指定列的数量,为*时则是表的列数maxCol: " + maxCol);        while (rs != null && rs.next()) {// 通过result.next()遍历结果            Map<String, Object> map = new LinkedHashMap<String, Object>();            int row = rs.getRow();            System.out.print("当前遍历的行数: " + row + " ");            // 第一列的列号为1,所以i最小为1            for (int i = 1; i <= maxCol; i++) {                System.out.print(rsmd.getColumnName(i) + " " + rs.getString(i) + " ");                // 存入列名和值                map.put(rsmd.getColumnName(i), rs.getString(i));            }            System.out.println("");            list.add(map);        }        DB.closeResultSet(rs);// 结果集用完后关闭        return list;    }    public static void main(String[] args) throws SQLException {        Connection conn = DB.getConnection();        DemoDao.insertTest(conn, "我是用户名");        conn.commit();// 更新操作要提交事务才能生效        List<Map<String, Object>> list = DemoDao.findTest(conn);        for (Map<String, Object> map : list) {            System.out.print(map.get("id") + " " + map.get("username") + " " + map.get("update_time") + " "                    + map.get("create_time"));            System.out.println();        }        DB.closeConnection(conn);    }}// 查询时指定列的数量,为*时则是表的列数maxCol: 4// 当前遍历的行数: 1 id 1 username user1 update_time 2017-05-29 10:19:39 create_time// 2017-05-29 10:19:39// 当前遍历的行数: 2 id 2 username user2 update_time 2017-05-29 10:20:15 create_time// 2017-05-29 10:20:15// 当前遍历的行数: 3 id 5 username user2 update_time 2017-05-29 10:22:12 create_time// 2017-05-29 10:22:12// 当前遍历的行数: 4 id 12 username user3 update_time 2017-05-29 10:42:10 create_time// 2017-05-29 10:42:10// 当前遍历的行数: 5 id 13 username user3 update_time 2017-05-29 10:42:12 create_time// 2017-05-29 10:42:12// 当前遍历的行数: 6 id 14 username user3 update_time 2017-05-29 10:42:17 create_time// 2017-05-29 10:42:17// 当前遍历的行数: 7 id 15 username user3 update_time 2017-05-29 10:43:34 create_time// 2017-05-29 10:43:34// 当前遍历的行数: 8 id 16 username user3 update_time 2017-05-29 10:43:42 create_time// 2017-05-29 10:43:42// 当前遍历的行数: 9 id 17 username user3 update_time 2017-05-29 10:44:33 create_time// 2017-05-29 10:44:33// 当前遍历的行数: 10 id 18 username user3 update_time 2017-05-29 10:44:42 create_time// 2017-05-29 10:44:42// 当前遍历的行数: 11 id 19 username user3 update_time 2017-05-29 10:56:58 create_time// 2017-05-29 10:56:58// 当前遍历的行数: 12 id 20 username user3 update_time 2017-05-29 11:28:13 create_time// 2017-05-29 11:28:13// 当前遍历的行数: 13 id 21 username user3 update_time 2017-05-29 11:29:02 create_time// 2017-05-29 11:29:02// 当前遍历的行数: 14 id 22 username user3 update_time 2017-06-22 13:02:19 create_time// 2017-06-22 13:02:19// 当前遍历的行数: 15 id 23 username user3 update_time 2017-06-22 19:27:53 create_time// 2017-06-22 19:27:53// 当前遍历的行数: 16 id 24 username user3 update_time 2017-06-22 19:28:01 create_time// 2017-06-22 19:28:01// 当前遍历的行数: 17 id 25 username user3 update_time 2017-06-22 19:28:03 create_time// 2017-06-22 19:28:03// 当前遍历的行数: 18 id 26 username user3 update_time 2017-06-22 19:28:05 create_time// 2017-06-22 19:28:05// 当前遍历的行数: 19 id 27 username user3 update_time 2017-06-22 19:28:12 create_time// 2017-06-22 19:28:12// 当前遍历的行数: 20 id 28 username user3 update_time 2017-06-24 00:10:06 create_time// 2017-06-24 00:10:06// 当前遍历的行数: 21 id 29 username user3 update_time 2017-06-24 00:11:50 create_time// 2017-06-24 00:11:50// 当前遍历的行数: 22 id 30 username user3 update_time 2017-06-24 00:13:24 create_time// 2017-06-24 00:13:24// 1 user1 2017-05-29 10:19:39 2017-05-29 10:19:39// 2 user2 2017-05-29 10:20:15 2017-05-29 10:20:15// 5 user2 2017-05-29 10:22:12 2017-05-29 10:22:12// 12 user3 2017-05-29 10:42:10 2017-05-29 10:42:10// 13 user3 2017-05-29 10:42:12 2017-05-29 10:42:12// 14 user3 2017-05-29 10:42:17 2017-05-29 10:42:17// 15 user3 2017-05-29 10:43:34 2017-05-29 10:43:34// 16 user3 2017-05-29 10:43:42 2017-05-29 10:43:42// 17 user3 2017-05-29 10:44:33 2017-05-29 10:44:33// 18 user3 2017-05-29 10:44:42 2017-05-29 10:44:42// 19 user3 2017-05-29 10:56:58 2017-05-29 10:56:58// 20 user3 2017-05-29 11:28:13 2017-05-29 11:28:13// 21 user3 2017-05-29 11:29:02 2017-05-29 11:29:02// 22 user3 2017-06-22 13:02:19 2017-06-22 13:02:19// 23 user3 2017-06-22 19:27:53 2017-06-22 19:27:53// 24 user3 2017-06-22 19:28:01 2017-06-22 19:28:01// 25 user3 2017-06-22 19:28:03 2017-06-22 19:28:03// 26 user3 2017-06-22 19:28:05 2017-06-22 19:28:05// 27 user3 2017-06-22 19:28:12 2017-06-22 19:28:12// 28 user3 2017-06-24 00:10:06 2017-06-24 00:10:06// 29 user3 2017-06-24 00:11:50 2017-06-24 00:11:50// 30 user3 2017-06-24 00:13:24 2017-06-24 00:13:24

package dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.Map;import db.DB;public class DemoDao {    // 插入Test表数据    public static int insertTest(Connection conn, String username) {        PreparedStatement ps = null;        String sql = "INSERT INTO test (username,create_time) VALUES (?,NOW())";        try {            ps = conn.prepareStatement(sql);            ps.setString(1, username);            return ps.executeUpdate();        } catch (SQLException e) {            try {                conn.rollback();//回滚事务            } catch (SQLException e1) {                e1.printStackTrace();            }            e.printStackTrace();            return 0;        } finally {            try {                DB.closePreparedStatement(ps);            } catch (SQLException e) {                e.printStackTrace();            }        }    }    // 查询test表数据    public static List<Map<String, Object>> findTest(Connection conn) {        PreparedStatement ps = null;        ResultSet rs = null;        String sql = "select id,username,update_time,create_time from test";        try {            ps = conn.prepareStatement(sql);            rs = ps.executeQuery();            return DB.toMap(rs);        } catch (SQLException e) {            try {                conn.rollback();//回滚事务            } catch (SQLException e1) {                e1.printStackTrace();            }            e.printStackTrace();            return null;        } finally {            try {                DB.closePreparedStatement(ps);            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

阅读全文

0 0