JDBC基础

JDBC（Java Data Base Connectivity [ˌkɒnekˈtɪvəti]）是一种用于执行 SQL 语句的 Java API，可以为多种关系数据库提供统一访问，它由一组用 Java 语言编写的类和接口组成。JDBC 提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

JDBC 为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题。 

JDBC 的目标是使 Java 程序员使用 JDBC 可以连接任何提供了 JDBC 驱动程序的数据库系统，这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程。 

持久化（persistence [pəˈsɪstəns]）：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。 持久化的主要应用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。  

驱动（driver [ˈdraɪvə(r)]）：我们安装好数据库之后，我们的应用程序也是不能直接使用数据库的，必须要通过相应的数据库驱动程序，通过驱动程序去和数据库打交道。其实也就是数据库厂商的 JDBC 接口实现，即对 Connection [kəˈnekʃn]等接口的实现类的 jar 文件。

2.基本操作

前期准备
1、在 mysql 中创建一个库，并创建相应测试表和插入一些测试数据。
2、新建一个 Java 工程，并导入 MySQL 数据库驱动包，将 mysql 驱动拷入项目根目录并右键添加到项目依赖（Add As Library）。
JDBC 操作数据步骤一般分为如下五个步骤：
一、加载数据库驱动类。
Class.forName(driver)
二、建立连接(Connection)
Connection connection = DriverManager.getConnection(url,user,password);
三、创建用于向数据库发送 SQL 的 Statement 对象，并发送 sql
Statement statement = connection.createStatement();
四、从结果集的 ResultSet 中取出返回数据。
ResultSet resultSet = statement.excuteQuery(sql);
五、关闭相关资源，并释放内存
*.close();
案例：

import java.sql.*;

public class Jdbc01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from student");
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
        }
        resultSet.close();
        statement.close();
        connection.close();
    }
}

常见驱动类（driver）的全路径一般如下，导入相应的驱动 jar 包后即可加载相应的驱动类：

mysql 驱动 com.mysql.jdbc.Driver
oracle 驱动 oracle.jdbc.driver.OracleDriver
sqlserver 驱动 com.microsoft.jdbc.sqlserver.SQLServerDriver

URL：
用于标识数据库的位置，一般由协议、主机、端口和数据库名构成，程序员通过 URL 地址指定 JDBC 程序连接的数据库信息，常见 URL 的写法如下：

Oracle— jdbc:oracle:thin:@localhost:1521:实例ID
SqlServer— jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=databaseName
MySql— jdbc:mysql://localhost:3306/databaseName

MySQL 连接的 url 格式如下，常用参数一般有 characterEncoding=utf-8&useSSL=false


注意：如果是连接本地，主机一般填写 127.0.0.1 或 localhost 和省略主机和端口。如果是连接服务器或虚拟机上的数据库服务则填写对应设备的 ip 地址和端口即可。

3.数据库常用对象

在使用 jdbc 过程中经常会使用到如下对象。

1.Connection [kəˈnekʃn]：

Jdbc 程序中的 Connection，它用于代表数据库的连接，Connection 是数据库编程中最重要的一个对象，客户端与数据库所有交互都是通过 Connection 对象完成的，这个对象的常用方法：

•  createStatement()：创建向数据库发送 sql 的 Statement 对象。
•  prepareStatement(sql) ：创建向数据库发送预编译 sql 的 PrepareSatement 对象。
•  prepareCall(sql)：创建执行存储过程的 callableStatement 对象。
•  setAutoCommit(boolean autoCommit)：设置事务是否自动提交。
•  commit() ：在链接上提交事务。
•  rollback() ：在此链接上回滚事务。 

2.Statement [ˈsteɪtmənt]：

Jdbc 程序中的 Statement 对象用于向数据库发送 SQL 语句， Statement 对象常用方法： 

• executeQuery(String sql) ：用于向数据发送查询语句。 
• executeUpdate(String sql)：用于向数据库发送 insert、update 或 delete 语句 
• execute(String sql)：用于向数据库发送任意 sql 语句
• addBatch(String sql) ：把多条 sql 语句放到一个批处理中。 
• executeBatch()：向数据库发送一批 sql 语句执行。  

3.ResultSet [rɪˈzʌlt set]：

Jdbc 程序中的 ResultSet 用于代表 Sql 语句的执行结果。Resultset 封装执行结果时，采用的类似于表格的方式。ResultSet 对象维护了一个指向表格数据行的游标，初始的时候，游标在第一行之前，调用 resultSet.next() 方法，可以使游标指向具体的数据行，进行调用方法获取该行的数据。

ResultSet 提供检索不同类型字段的方法，常用的有： 

• getString(int index)、getString(String columnName)：通过索引或者字段名获得在数据库里是 varchar、char 等类型的数据对象。 
• getFloat(int index)、getFloat(String columnName)：通过索引或者字段名获得在数据库里是 Float 类型的数据对象。 
• getDate(int index)、getDate(String columnName)：通过索引或者字段名获得在数据库里是 Date 类型的数据。 
• getBoolean(int index)、getBoolean(String columnName)：通过索引或者字段名获得在数据库里是 Boolean 类型的数据。 
• getObject(int index)、getObject(String columnName)：通过索引或者字段名获取在数据库里任意类型的数据。

注意：ResultSet 的 index 是从 1 开始的，这与我们平时所写代码注意区分。

ResultSet 还提供了对结果集进行滚动的方法： 

• next()：移动到下一行
• previous()：移动到前一行
• absolute(int row)：移动到指定行
• beforeFirst()：移动 resultSet 的最前面。 
• afterLast() ：移动到 resultSet 的最后面。

注意：释放资源，Jdbc 程序运行完后，切记要释放程序在运行过程中，创建的那些与数据库进行交互的对象，这些对象通常有 ResultSet，Statement 和 Connection 对象。  

释放资源按照“后开先关”原则，顺序如下：ResultSet → Statement → Connection

为确保资源释放代码能正确运行，资源释放代码在使用 try-catch 时一定要放在 finally 语句中。 

4.SQL 注入与 PreparedStatement

是用户利用某些系统没有对输入数据进行充分的检查，从而进行恶意破坏的行为。 statement 存在 sql 注入攻击问题，例如登陆用户名采用 ' or 1=1 # or 时无论密码是否正确都将登录成功。

import java.sql.*;

public class Jdbc01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        //String name = "Jack";
        String name = "'or 1=1 #";
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from student where studentname='" + name + "' and studentage='20'");
        //最终SQL变成下面这种
        //SELECT * FROM student WHERE studentname=''or 1=1 #' and studentage='20'
        while (resultSet.next()) {
            System.out.println(resultSet.getString("studentname"));
        }
        resultSet.close();
        statement.close();
        connection.close();
    }
}

SQL 注入的根本原因是 SQL 字符串的原始拼接。它可能会导致数据库被恶意破坏，这是任何开发都需要注意的问题。随着 jdbc 的发展，为防范 SQL 注入，可以采用 PreparedStatement [prɪˈpeəd][ˈsteɪtmənt] 取代 Statement。 PreperedStatement 是 Statement 的子类，它的实例对象可以通过调用 connection.preparedStatement() 方法获得，相对于 Statement 对象而言： PreperedStatement 可以避免 SQL 注入的问题。 Statement 会使数据库频繁编译 SQL，可能造成数据库缓冲区溢出。PreparedStatement 可对 SQL 进行预编译，从而提高数据库的执行效率。 并且 PreperedStatement 对于 SQL 中的参数，允许使用占位符的形式进行替换，简化 SQL 语句的编写。

Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql:///my_test?useSSL=false", "root", "123456");
PreparedStatement statement = connection.prepareStatement("select * from `jdbc_test` where `name` = ? and `pwd` = ?");
statement.setObject(1,"admin");
statement.setObject(2,"123");
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
    System.out.println(resultSet.getString(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(3));
}
resultSet.close();
statement.close();
connection.close();

注意：？不能作为字段名和表名的占位符，PreparedStatement 会为占位符 ? 的两边自动加上单引号，这样会使得 SQL 语句不可执行或找不到对应的表和字段，比如使用将表名设置为占位符，数据库执行 SQL 语句时，表名会用单引号引起来，这样会使得 SQL 语句执行出错或者查询不出数据。但这句代码在 SQL 中执行不会报错，但是查询没有结果。

5.数据库的 CRUD

CRUD 是增删改查的简写，分别对应：增加（Create [kriˈeɪt]）、查询（Retrieve [rɪˈtriːv]）、修改（Update [ˈʌpdeɪt]）和删除（Delete [dɪˈliːt])，使用 jdbc 通常也就是为了完成数据库的增删改查。增删改三种操作对 jdbc 都是相同的语法执行 statement.executeUpdate() 方法，该方法返回数据库的影响行数。如果数据库的影响行数为 0 这说明修改失败。

增加：

private static boolean add(User user) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("insert into user (name,password,age) values (?,?,?)");
    statement.setObject(1, user.getName());
    statement.setObject(2, user.getPassword());
    statement.setObject(3, user.getAge());
    int i = statement.executeUpdate();//返回数据库影响行数
    statement.close();
    connection.close();
    return i != 0;
}

查询：

private static List<User> query() throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("select * from user");
    ResultSet resultSet = statement.executeQuery();
    List<User> list = new ArrayList<>();
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        int age = resultSet.getInt("age");
        String name = resultSet.getString("name");
        String password = resultSet.getString("password");
        list.add(new User(id, name, password, age));
    }
    resultSet.close();
    statement.close();
    connection.close();
    return list;
}

6.数据库分页 

在 MySQL 中分页使用 limit 关键字实现： 

Select * from table limit [M，]N ;

• M：记录开始索引位置（从 0 开始）
• N：取多少条记录

一般来说 M 和我们的 page 的关系为 M =(page-1)*size 换句话说，我们分页查询第 page 页，有 size 条数据时使用的 SQL 如下：

select * from user limit (page-1)*size,size

注意：limit 后面不能进行数值运算，所以我们需要在 java 代码中运算后填入，代码如下：

int page = 2;
int size = 10;
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
PreparedStatement statement = connection.prepareStatement("select * from student limit ?,?");
statement.setObject(1, (page - 1) * size);
statement.setObject(2, size);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
    System.out.println(resultSet.getObject(2));
}
resultSet.close();
statement.close();
connection.close();

Oracle 中没有 limit 关键字，分页需要依赖行号（rownum）和子查询完成分页，SQL 如下： 

select * from ( select rownum r_, row_.* from ( select * from student order by id ) row_ where rownum <=Y ) where r_>=X ;

• X：起始索引位置。 
• Y：结束索引位置。 

7.获取数据库自增主键

在实际开发中，我们经常需要获取插入数据的自增 id 针对这样的需求通常我们有两种解决办法。

1.插入完成后查询最大的 id 值：适用低并发的情况，如果数据库有多个线程同时插入时获取到的 id 将是不准确的。

2.使用 jdbc 中相应的 API 获取自增主键：适用全部情况。

获取数据库自增主键只能针对插入 SQL，用法如下：

String sql = "insert into user(name,password,email,birthday) values('abc','123','abc@sina.com','1978-08-08')";
PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
st.executeUpdate();
ResultSet rs = st.getGeneratedKeys();  //得到插入行的主键
if(rs.next())
    System.out.println(rs.getObject(1));
}

8.调用存储过程

创建表和存储过程

create table p_user(  
	id int primary key auto_increment,  
	name varchar(10),
	sex char(2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
insert into p_user(name,sex) values('A',"男");  
insert into p_user(name,sex) values('B',"女");  
insert into p_user(name,sex) values('C',"男"); 

创建存储过程（查询得到男性或女性的数量，如果传入的是0就女性否则是男性）

CREATE PROCEDURE ges_user_count(IN sex_id INT, OUT user_count INT)
BEGIN  
IF sex_id=0 THEN
SELECT COUNT(*) FROM p_user WHERE p_user.sex='女' INTO user_count;
ELSE
SELECT COUNT(*) FROM p_user WHERE p_user.sex='男' INTO user_count;
END IF;
END 

测试调用存储过程

SET @user_count = 0;
CALL ges_user_count(1, @user_count);
SELECT @user_count;

查询得到男性或女性的数量, 如果传入的是 0 就女性否则是男性
jdbc 调用存储过程

private static void t2() throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
    CallableStatement prepareCall = connection.prepareCall("{call ges_user_count(?, ?)}");
    prepareCall.setInt(1, 1);
    prepareCall.registerOutParameter(2, Types.INTEGER);
    prepareCall.execute();
    System.out.println(prepareCall.getString(2));
    connection.close();
}