Java复习日志--数据库的连接操作

数据库的连接步骤

  1.注册和加载数据库驱动（新版jdk不需要注册，默认注册）
      *采用静态块加载驱动*
  2.建立和数据库的连接通道
  3.构造sql语句
  4.执行sql语句（返回结果集）
  5.关闭资源

设置账号，密码，连接方式

final private static String username = "root";
final private static String password = "12345678";
final private static String url = "jdbc:mysql://localhost"
            + ":3306/lk?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";

静态块加载驱动

 static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

构建数据库路链接通道

 public static Connection getConn() throws Exception {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

关闭数据库资源

 public static void TryClose(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭结果集资源
     */
    public static void closeres(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        TryClose(stmt, conn);
    }

数据库的操作

  /**
     * 基于Dbutils
     * 数据的泛型转化:查询操作
     **/
    public static <T> List<T> excuteSql(String sql, Class<T> t) throws Exception {
        Connection conn = null;
        List<T> list = null;
        try {
            conn = getConn();
            QueryRunner runner = new QueryRunner();
            list = runner.query(conn, sql, new BeanListHandler<T>(t));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        TryClose(null, conn);
        return list;
    }

    /**
     * 基于Dbutils
     * 数据的泛型转化:更新操作
     **/
    public static int excuteUpdate(String sql, List<Object> list) {
        Connection conn = null;
        QueryRunner runner = new QueryRunner();
        int row = 0;
        try {
            row = runner.update(conn, sql, list);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        TryClose(null, conn);
        return row;
    }

    /**
     * 对待sql的批处理和事务处理
     * 使用preoarestatement的话
     *   * 要对数据库语句进行预编译，所以的最好操作一张表*
     */
    public static int[] excuteAll(List<String> list) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        int[] row = null;
        try {
            conn = getConn();
            stmt = conn.createStatement();
            //设置不用自动提交
            conn.setAutoCommit(false);
            for (String sql : list) {
                stmt.addBatch(sql);
            }
            row = stmt.executeBatch();
            conn.commit();
        } catch (Exception e) {
            //设置回滚
            conn.rollback();
        }
        TryClose(stmt,conn);
        return row;
    }

    /**
     *对元数据的操作
     *   * 元数据：
     *        用来描述数据的数据
     **/
    private static void OpMata() throws SQLException {
        String sql = "select *from user ";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtils.getConn();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            //获取元数据
            ResultSetMetaData metaData=rs.getMetaData();
            //获取字段的数量
            System.out.println(metaData.getColumnCount());
            //获取字段的名称,数据库的是从1开始的
            for(int i=1;i<=metaData.getColumnCount();i++){
                System.out.print(metaData.getColumnTypeName(i)+"\t"+metaData.getColumnName(i));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        jdbcUtils.closeres(rs, stmt, conn);
    }
    private static void DoAPrepare() throws SQLException {

        Connection conn = null;
        PreparedStatement ptmt = null;
        ResultSet rs = null;
        try {
            //要对语句进行预编译
            String sql = "select *from user where username=? and password=?";
            conn = jdbcUtils.getConn();
            ptmt = conn.prepareStatement(sql);
            ptmt.setString(1, "admin");
            ptmt.setString(2, "123456");
            rs = ptmt.executeQuery();
        } catch (Exception e) {
             e.printStackTrace();
        }
        while (rs.next() == true) {
            System.out.println(rs.getInt("id") + "\t" +
                    rs.getString("usercount") + "\t" +
                    rs.getString("username") + "\t" +
                    rs.getString("password"));
        }
        jdbcUtils.closeres(rs, ptmt, conn);
    }

    private static void DoGet() throws SQLException {
        String sql = "select *from user ";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtils.getConn();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        while (rs.next() == true) {
            System.out.println(rs.getInt("id") + "\t" +
                    rs.getString("usercount") + "\t" +
                    rs.getString("username") + "\t" +
                    rs.getString("password"));
        }
        jdbcUtils.closeres(rs, stmt, conn);
    }