JDBC和Statement的使用

JDBC的使用

加载数据库驱动程序 → 建立数据库连接 Connection → 创建执行 SQL 的语句
Statement → 处理执行结果 ResultSet → 释放资源

1. 下载驱动

1.1 MySQL驱动

MySQL驱动下载地址: https://dev.mysql.com/downloads/connector/j/

1.2 Oracle驱动

数据库安装目录\oracle\product\11.2.0\dbhome_1\jdbc\lib

2. 往项目中添加驱动并配置路径

往idea添加mysql的jdbc

新建一个目录, 起名为lib

将库文件拷贝到lib目录下

右击添加过来的jdbc, 作为库文件添加. 这个时候就将jdbc添加到项目了

3. 执行sql语句代码实现

新建一个类JdbcDemo.java. 向departments表添加一条数据. 当然, 更新也是一样的操作. 只是sql语句不同

package pers.jssd.jdbc.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDome {
    public static void main(String[] args) {
        Connection con = null;
        Statement statement = null;
        try {
            // 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 取得数据库连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myemployees?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC", "root", "root");
            statement = con.createStatement();
            String sql = "insert into departments values (default, 'Tes', 200, 1700)";
            int i = statement.executeUpdate(sql);
            System.out.println("i = " + i);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

4. 封装数据库操作的工具类

package pers.jssd.jdbc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;

/**
 * 数据库连接对象
 *
 * @author jssd
 */
public class DBUtil {

    private static String URL;
    private static String USER;
    private static String PWD;

    static {
        ResourceBundle db = ResourceBundle.getBundle("db");
        String DRIVER = db.getString("driver");
        URL = db.getString("url");
        USER = db.getString("user");
        PWD = db.getString("pwd");
        // 注册驱动
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 取得连接对象
     *
     * @return 返回连接对象
     */
    public static Connection getConnection() {
        Connection con = null;
        // 取得数据库连接
        try {
            con = DriverManager.getConnection(URL, USER, PWD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    /**
     * 关闭资源
     *
     * @param closeable 实现了AutoCloseable的可关闭变量
     */
    public static void close(AutoCloseable... closeable) {
        for (AutoCloseable autoCloseable : closeable) {
            try {
                if (autoCloseable != null) {
                    autoCloseable.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

资源文件如下

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/myemployees?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
user = root
pwd = 1624022009.a

5. 使用Statment和ResultSet查询数据

package pers.jssd.jdbc.test;

import pers.jssd.jdbc.util.DBUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDome3 {
    public static void main(String[] args) {
        selectDepartmentsById(272);
    }

    private static void selectDepartmentsById(int department_id) {
        String sql = "select * from departments where department_id = " + department_id;
        Connection connection = DBUtil.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String department_name = resultSet.getString("department_name");
                System.out.println("department_name = " + department_name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet, statement, connection);
        }
    }
}

6. ResultSet详解

注意 ResultSet 中封装的并不是我们查询到的所有的结果集， 而是返回了查询到的结果
集的数据库游标。 通过 ResultSet 中的 next()方法操作游标的位置获取结果集

7. 通过使用ResultSet实现逻辑分页

物理分页: 使用数据库查询实现查询分页

逻辑分页: 查询到的结果集不变, 只是通过游标取得某些数据

package pers.jssd.jdbc.test;

import pers.jssd.jdbc.util.DBUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 使用ResultSet查询数据, 并分页
 *
 * @author jssdjing@gmail.com
 * @create 2019-08-07 22:02:32
 *
 */
public class JdbcDome4 {
    public static void main(String[] args) {
        selectDepartmentsById(2, 5);
    }

    private static void selectDepartmentsById(int currentPage, int pageCount) {
        String sql = "select * from departments";
        Connection connection = DBUtil.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            int start = (currentPage - 1) * pageCount;
            int end = start + pageCount;
            int cur = 0;
            while (resultSet.next()) {
                if (cur >= start && cur < end) {
                    int anInt = resultSet.getInt(1);
                    System.out.println("anInt = " + anInt);
                    String string = resultSet.getString(2);
                    System.out.println("string = " + string);
                    if (cur == end - 1) {
                        break;
                    }
                }
                cur ++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet, statement, connection);
        }
    }
}