【学习笔记】JDBC连接数据库

JDBC（Java Database Connectivity）Java数据库连接。
以连接MySQL为例
有两种方式：
1.在jdk安装目录下的lib文件夹中添加mysql-connector-java的jar包
2.使用maven添加依赖（推荐）

后面操作都一样

// 更新数据
private static void updateAccount() throws ClassNotFoundException, SQLException {
        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db1";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3. 定义sql
        String sql = "update account set money = 2000 where id = 1";
        //4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();
        //5. 执行sql
        int count = stmt.executeUpdate(sql);//受影响的行数
        //6. 处理结果
        System.out.println(count);
        //7. 释放资源
        stmt.close();
        conn.close();
    }

查询数据复杂一点。

private static void queryAccount() throws ClassNotFoundException, SQLException {
        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db1";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3. 定义sql
        String sql = "select * from account";
        //4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();
        //5. 执行sql
        ResultSet resultSet = stmt.executeQuery(sql);
        //6. 处理结果，遍历resultSet中的所有数据
        while (resultSet.next()) {
            // 根据下标获取数据
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            double money = resultSet.getDouble(3);

            System.out.println("{id:["+id + "],name:[" + name + "],money:[" + money+"]}");
        }
        //7. 释放资源
        resultSet.close();
        stmt.close();
        conn.close();
    }

使用预编译SQL语句的方式

private static void preparedStatement(String queryId) throws ClassNotFoundException, SQLException {
        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3. 定义sql
        String sql = "SELECT * FROM account WHERE id = ? ";
        //4. 获取执行sql的对象 Statement
        PreparedStatement stmt = conn.prepareStatement(sql);
        // 设置?的值
        stmt.setString(1,queryId);
        //5. 执行sql
        ResultSet resultSet = stmt.executeQuery();
        //6. 处理结果，遍历resultSet中的所有数据
        while (resultSet.next()) {
            // 根据下标获取数据
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            double money = resultSet.getDouble(3);

            System.out.println("{id:["+id + "],name:[" + name + "],money:[" + money+"]}");
        }
        //7. 释放资源
        resultSet.close();
        stmt.close();
        conn.close();
    }