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);}}
}