Java 使用 JDBC 连接mysql

之前我们学习了JavaSE，编写了Java程序，数据保存在变量、数组、集合等中，无法持久化，后来学习了IO流可以将数据写入文件，但不方便管理数据以及维护数据的关系；

后来我们学习了数据库管理软件MySQL，可以方便的管理数据。

那么如何将它俩结合起来呢？即Java程序<==>MySQL，实现数据的存储和处理。

那么就可以使用JDBC技术。

JDBC：Java Database Connectivity，它是代表一组独立于任何数据库管理系统（DBMS）的API，声明在java.sql与javax.sql包中，是SUN(现在Oracle)提供的一组接口规范。由各个数据库厂商来提供实现类，这些实现类的集合构成了数据库驱动jar。

即JDBC技术包含两个部分：

1. java.sql包和javax.sql包中的API

   因为为了项目代码的可移植性，可维护性，SUN公司从最初就制定了Java程序连接各种数据库的统一接口规范。这样的话，不管是连接哪一种DBMS软件，Java代码可以保持一致性。

2. 各个数据库厂商提供的jar

   因为各个数据库厂商的DBMS软件各有不同，那么内部如何通过sql实现增、删、改、查等管理数据，只有这个数据库厂商自己更清楚，因此把接口规范的实现交给各个数据库厂商自己实现。

导入MySQL驱动

在使用代码连接MySQL数据库时，需要先导入MySQL为Java语言编写的驱动。

• MySQL驱动下载地址：MySQL :: Download MySQL Connector/J (Archived Versions)

  驱动版本与MySQL服务器以及JRE,JDK的对应关系。

  Connector/J version	MySQL Server version	JRE Required	JDK Required for Compilation	Status
  5.1	5.61, 5.71, 8.01	JRE 5 or higher1	JDK 5.0 AND JDK 8.0 or higher2, 3	General availability
  8.0	5.6, 5.7, 8.0	JRE 8 or higher	JDK 8.0 or higher2	General availability. Recommended version.

• 下载对应版本的Connector并解压，得到mysql-connector-java-8.0.33-bin.jar包。

• 在项目中新建一个libs的文件夹，将解压后的jar包复制到这个文件夹中。

• 打开IDEA，右键jar包，选择add as library，将jar包添加到library

代码实现

代码实现步骤:

1. 注册驱动

在将.jar文件标记为Library以后，需要先使用代码注册驱动。

Class.forName("com.mysql.jdbc.Driver"); // 加载 com.mysql.jdbc.Driver 这个类

Java6,也就是JDBC4.0以后，JavaSE的项目可以自动加载驱动。是因为数据库的厂商提供了MEAT-INFO-->services-->java.sql.Driver这个文件，在使用DriverManager类的getConnection方法获取数据库连接时，DriverManager将会尝试从初始化中加载的驱动程序中找到合适的驱动程序。

建议在使用前还是手动的注册一下MySQL的驱动。

2. 获取数据库连接

使用Java自带DriverManager类的getConnection(String url)或者getConncetion(String url,String user,String password)方法，可以获取到一个数据库连接，用来操作数据库。

参数解析:

• url:数据库连接路径，DriverManager尝试从一组已经注册的JDBC驱动程序中选择适当的驱动程序。

  • 格式为：?jdbc:subprotocol:subname
  • 常见数据库的url连接:
    • mysql:?jdbc:mysql://<hostname>[:<port>]/<dbname>[?连接参数]
    • oracle:?jdbc:oracle:thin:@<hostname>[:<port>]:<dbname>
    • sqlserver:jdbc:sqlserver://<hostname>[:<port>]:DatabaseName=<dbname>

• user: 连接MySQL数据库时使用的用户名。

• password:连接MySQL数据库时使用的密码。

DriverManager.getConnection("jdbc:mysql://localhost:3306/demo","root","Abcd1234");

// 调用 getConnection(String url) 方法，将用户名和密码以参数的形式传入
DriverManager.getConnection("jdbc:mysql://localhost/demo?user=root&password=Abcd1234");

3. 执行SQL语句

获取到connection的数据库连接以后，如果想要执行SQL语句，需要调用connection的的createStatement()?方法，创建一个Statement对象，用来和服务器传递SQL语句。

Statement对象执行SQL语句也分为两种情况：

• 增删改数据使用executeUpdate(String sql)方法，得到的结果是一个int类型的数字，表示SQL语句影响的行数。
• 查询数据使用executeQuery(String sql)方法，得到的结果是一个ResultSet类型的对象，配合next()和getXXX()方法获取到每个字段的值。

4. 关闭连接

resultSet.close();
statement.close();
connection.close();

public class Main {
    public static void main(String[] args) {

        try {
            // 加载 MySQL 驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 连接到 MySQL 数据库
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "12345678");
            DatabaseMetaData metaData = connection.getMetaData();

            // 在这里执行其他数据库操作
            System.out.println("数据库名称：" + metaData.getDatabaseProductName());
            System.out.println("数据库版本：" + metaData.getDatabaseProductVersion());
            System.out.println("驱动名称：" + metaData.getDriverName());
            System.out.println("驱动版本：" + metaData.getDriverVersion());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

PreparedStatement的使用

调用Connection类的prepareStatement()方法可以获取到一个PrepareStatement类的对象。PerparedStatement对象继承自Statement类，也可以调用executeUpdate和executeQuery方法来执行SQL语句。

和Statement类相比，PreparedStatement能够实现以下几个需求:

1. 避免SQL注入

SQL注入是指对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

假设user表中存储了一个name值为zhangsan，密码为123的数据。

直接使用?Statement类的executeQuery()方法，会有SQL注入的风险。

Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名:");  // 如果用户输入的是 zhangsan'#
String name = scanner.next();
System.out.print("请输入密码:");  // 这里无论输入的密码是否正确，都能获取到用户张三的信息
String pwd = scanner.next();

// 这里使用的是字符串拼接的形式得到一个 SQL 语句，有sql注入的风险
String sql = "select * from user where name='" + name + "' and password='" + pwd + "';";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);

boolean isPass = false;
while (rs.next()) {
    isPass = true;
    System.out.println("用户名密码正确!");
    System.out.println("id:" + rs.getInt(1) + "，姓名:" + rs.getString(2) + "，密码" + rs.getString(3));
}
if (!isPass) {
    System.out.println("用户名或者密码错误!");
}

rs.close();
statement.close();
connection.close();

使用PreparedStatement类可以避免SQL注入。

Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名:");
String name = scanner.next();
System.out.print("请输入密码:");
String pwd = scanner.next();

// 不再使用字符串的 + 拼接 SQL 字符串，而是使用 ? 进行占位
String sql = "select * from user where name=? and password=?;";

PreparedStatement pst = connection.prepareStatement(sql);
// 调用 PreparedStatement 对象 setXXX() 方法，给指定的 ? 传参，可以避免SQL注入
pst.setString(1, name);
pst.setString(2, pwd);

// 调用 executeQuery() 方法时，不要再传入 SQL 语句
ResultSet rs = pst.executeQuery();

2. 存储Blob类型

String sql = "insert into person values(null,?,?)";
PreparedStatement pst = connection.prepareStatement(sql);
FileInputStream fis = new FileInputStream("1.jpeg");
pst.setString(1, "张三");
pst.setObject(2, fis);  // 可以将一个文件写入到数据库中

注意两个问题：

1. my.ini关于上传的字节流文件有大小限制，可以在my.ini中配置变量

? max_allowed_packet=16M

1. 每一种blob有各自大小限制：tinyblob:255字节、blob:65k、mediumblob:16M、longblob:4G

3. 插入数据后获取自增键的值

在插入数据时，自增键的值可以直接使用 null 来表示。如果现在需要知道插入以后，这条数据的自增值是多少时，要怎样实现呢？

String sql = "insert into user values(null,?,?)";

// 调用 prepareStatement()方法时，传入 RETURN_GENERATED_KEYS 参数，可以获取到插入数据以后自增键的值
PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pst.setString(1, "jack");
pst.setString(2, "234345");
pst.executeUpdate();

// 调用 getGeneratedKeys() 可以获到自增键
ResultSet generatedKeys = pst.getGeneratedKeys();
while (generatedKeys.next()) {
    System.out.println(generatedKeys.getInt(1));
}
Copy

4. 提高批处理效率

在连接MySQL数据库的url里添加rewriteBatchedStatements=true参数，可以进行快速批处理操作，当插入多条数据时，效率会有明显的提升。

// 在连接数据库时，要添加 rewriteBatchedStatements=true 参数，可以提升批处理的效率
String url = "jdbc:mysql://localhost/demo?rewriteBatchedStatements=true";
String user = "root";
String password = "Abcd1234";
Connection connection = DriverManager.getConnection(url, user, password);

String sql = "insert into user values(null,?,?)";
PreparedStatement pst = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 2; i < 10000; i++) {
    pst.setString(1, "jack" + i);
    pst.setString(2, "34irosef0eio");
    pst.addBatch();
}
pst.executeBatch();

long end = System.currentTimeMillis();
System.out.println(end - start);

5. 事务处理

使用代码实现事务处理分为三步:

1. 关闭MySQL的事务自动提交。connection.setAutoCommit(false);
2. 调用Connection对象的commit方法提交事务，或者rollback方法回滚事务。
3. 事务处理完成以后，再调用connection.setAutoCommit(true)打开自动提交事务。避免后续从连接池获取数据库连接时可能出现的问题。

Scanner scanner = new Scanner(System.in);
System.out.print("请输入转账金额:");
float money = scanner.nextFloat();
String sql1 = "select balance from account where id=1;";


PreparedStatement pst = connection.prepareStatement(sql1);
ResultSet resultSet = pst.executeQuery();
System.out.println(resultSet.next());
float balance = resultSet.getFloat(1);

// 关闭MySQL自动提交事务
connection.setAutoCommit(false);

String sql2 = "update account set balance=balance-? where id=1;";
pst = connection.prepareStatement(sql2);
pst.setFloat(1, money);
pst.executeUpdate();

String sql3 = "update account set balance=balance+? where id=2;";
pst = connection.prepareStatement(sql3);
pst.setFloat(1, money);
pst.executeUpdate();

if (balance > money) {
    System.out.println("转账成功!");
    connection.commit();  // 成功就直接提交事务
} else {
    System.out.println("余额不足，转账失败!");
    connection.rollback(); // 失败就让事务回滚
}
resultSet.close();
pst.close();
connection.setAutoCommit(true);  // 在关闭连接之前，将自动提交修改为默认值
connection.close();