Java-JDBC操作MySQL

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