Java-JDBC操作MySQL

一、Java-JDBC-MySQL的关系

二、创建连接

首先在MySQL官网下载一个jar包，然后导入编译器中，然后导入。

通过官方文档，在MySQL8.0以后，连接的地址为com.mysql.cj.jdbc.Driver，地址需要添加代码serverTimezone=UTC

这里创建一个DBHelper工具类，将这两个参数设为常量便于使用。

class DBHelper {final static String className = "com.mysql.cj.jdbc.Driver";final static String mysqladdress = "jdbc:mysql://localhost:3306/java?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=TRUE";}

其中localhost为地址，3306为端口，java是数据库名称

三、登录MySQL

有了指定的地址，还需要用户和密码才能登录数据库。为了安全，此处不使用root用户，采用新开一个账户来进行对数据库的操作。

create user 'java'@'localhost' identified by '123456';
grant all on java to 'java'@'localhost';

同样定义常量存储用户名和密码。

private String user, psw;

通过构造函数来对user和psw变量赋值。

DBHelper(String user, String psw) {this.user = user;this.psw = psw;}

四、操作数据库

有了地址、用户、密码，就可以登录数据库进行操作，在此之前，需要先实例化Connection类和Statement类，并在try-catch语句中创建对数据库的连接和数据传递。

Connection connection;Statement statement;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement = connection.createStatement();}catch (Exception e) {e.printStackTrace();}

连接好数据库之后，就可以使用SQL语句对数据库进行操作。

1、返回型操作

定义一个SQL语句在String中，实例化一个ResultSet，并使用statement传输SQL命令。由于select语句是有返回值的，所以使用executeQuery类型进行返回。

String sql = "select * from teacher";
ResultSet resultSet = statement.executeQuery(sql);

由于select的返回可能不止一条数据，所以使用while循环，调用resultSet中的next()函数，用于判断是否还有数据返回。然后使用resultSet中的get函数，获取字段内容。

 while (resultSet.next()) {String tid = resultSet.getString("tid");String tname = resultSet.getString("tname");String sex = resultSet.getString("sex");int age = resultSet.getInt("age");String professional_title = resultSet.getString("professional_title");int basic_salary = resultSet.getInt("basic_salary");System.out.print("tid:" + tid + "\t");System.out.print("tname:" + tname + "\t");System.out.print("sex:" + sex + "\t");System.out.print("age:" + age + "\t");System.out.print("professional_title:" + professional_title + "\t");System.out.println("basic_salary:" + basic_salary);}

最后操作完毕，关闭对象释放内存即可。

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

返回型操作完整代码：

  Connection connection;Statement statement;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement = connection.createStatement();String sql = "select * from teacher";ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()) {String tid = resultSet.getString("tid");String tname = resultSet.getString("tname");String sex = resultSet.getString("sex");int age = resultSet.getInt("age");String professional_title = resultSet.getString("professional_title");int basic_salary = resultSet.getInt("basic_salary");System.out.print("tid:" + tid + "\t");System.out.print("tname:" + tname + "\t");System.out.print("sex:" + sex + "\t");System.out.print("age:" + age + "\t");System.out.print("professional_title:" + professional_title + "\t");System.out.println("basic_salary:" + basic_salary);}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}

2、无返回型操作

与有返回操作一致，需要先对数据库建立通讯。

Connection connection;Statement statement;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement = connection.createStatement();} catch (Exception e) {e.printStackTrace();}

然后使用sql语句代码，这里以insert into语句为例。使用executeUpdate函数进行操作，由于没有返回值，直接使用statement进行操作后即可。

String sql = "insert into teacher(tid,tname,sex,age,professional_title,basic_salary) values('19G00001','靳小翠','女',38,'教授',16000)";
statement.executeUpdate(sql);

最后也是需要关闭对象。

无返回型操作完整代码：

        Connection connection;Statement statement;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement = connection.createStatement();String sql = "insert into teacher(tid,tname,sex,age,professional_title,basic_salary) values('19G00001','靳小翠','女',38,'教授',16000)";statement.executeUpdate(sql);connection.close();statement.close();} catch (Exception e) {e.printStackTrace();}

练习题目及完整代码

题目：

完整代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;class DBHelper {final static String className = "com.mysql.cj.jdbc.Driver";final static String mysqladdress = "jdbc:mysql://localhost:3306/java?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=TRUE";private String user, psw;DBHelper(String user, String psw) {this.user = user;this.psw = psw;}public void show_table() {Connection connection;Statement statement;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement = connection.createStatement();String sql = "select * from book";ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()) {String bid = resultSet.getString("bid");String bname = resultSet.getString("bname");String author = resultSet.getString("author");double price = resultSet.getDouble("price");String press = resultSet.getString("press");String type = resultSet.getString("type");System.out.print("bid:" + bid + "\t");System.out.print("bname:" + bname + "\t");System.out.print("author:" + author + "\t");System.out.print("price:" + price + "\t");System.out.print("press:" + press + "\t");System.out.println("type:" + type);}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}}public void change_data() {Connection connection;Statement statement;Statement statement1;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement1 = connection.createStatement();statement = connection.createStatement();String sql = "select press from book";String sql_professior = "update book set price=price*1.1";ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()) {String bookname = resultSet.getString("press");if (bookname.equals("清华大学出版社")) {statement1.executeUpdate(sql_professior);}}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();} finally {show_table();}}public void sort() {Connection connection;Statement statement;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement = connection.createStatement();String sql = "select * from book order by price DESC";ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()) {String bid = resultSet.getString("bid");String bname = resultSet.getString("bname");String author = resultSet.getString("author");double price = resultSet.getDouble("price");String press = resultSet.getString("press");String type = resultSet.getString("type");System.out.print("bid:" + bid + "\t");System.out.print("bname:" + bname + "\t");System.out.print("author:" + author + "\t");System.out.print("price:" + price + "\t");System.out.print("press:" + press + "\t");System.out.println("type:" + type);}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}}public void del() {Connection connection;Statement statement;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement = connection.createStatement();String sql = "delete from book where price<50 ";statement.executeUpdate(sql);connection.close();statement.close();} catch (Exception e) {e.printStackTrace();}show_table();}public void add() {Connection connection;Statement statement;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement = connection.createStatement();String sql = "insert into book(bid,bname,author,price,press,type) values('b00006','数据结构','严蔚敏',48,'清华大学出版社','科学类')";statement.executeUpdate(sql);connection.close();statement.close();} catch (Exception e) {e.printStackTrace();}show_table();}public void statistics() {Connection connection;Statement statement;try {Class.forName(className);connection = DriverManager.getConnection(mysqladdress, user, psw);statement = connection.createStatement();String sql = "select * from book where press<(select avg(press) from book);";ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()) {String bid = resultSet.getString("bid");String bname = resultSet.getString("bname");String author = resultSet.getString("author");double price = resultSet.getDouble("price");String press = resultSet.getString("press");String type = resultSet.getString("type");System.out.print("bid:" + bid + "\t");System.out.print("bname:" + bname + "\t");System.out.print("author:" + author + "\t");System.out.print("price:" + price + "\t");System.out.print("press:" + press + "\t");System.out.println("type:" + type);}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}}
}
public class sql {static final String name = "java";static final String psw = "123456";public static void main(String[] args) {DBHelper dbHelper = new DBHelper(name, psw);Scanner scanner = new Scanner(System.in);while (true) {System.out.println("1、打印数据\n2、增加工资\n3、排序\n4、删除数据\n5、添加数据\n6、筛选数据");int choose = scanner.nextInt();switch (choose) {case 1:dbHelper.show_table();break;case 2:dbHelper.change_data();break;case 3:dbHelper.sort();break;case 4:dbHelper.del();break;case 5:dbHelper.add();break;case 6:dbHelper.statistics();break;}}}
}