IDEA Connect to MySQL Database

Add MySQL database connector to project

Using mysql-connector-java-8.0.15.jar as an example, add jar files to your project



The JDBC-MySQL connector is now added successfully

Create the appropriate connection

Connect the corresponding class in the jar file

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");	//It would have been an error if jar had not been added
        } catch (Exception e) {
            e.printStackTrace();
        }

Connect to database

Java.sqlThere are two class methods in the DriverManager class in the package to make connections
Note that both methods may throw exceptions

Connection getConnection(java.lang.String,java.lang.Stiring,java.lang.String)

For example

        String url = "jdbc:mysql://localhost:3306/book?" + "useSSL = false&serverTimezone = GMT";
        try {
            connection = DriverManager.getConnection(url,"root","");   //Connect to database
        } catch (SQLException e) {
            e.printStackTrace();
        }

Connection getConnection(java.lang.String)

Query operation

Get the SQL query statement

First declare an SQL statement object using Statement, then let the created connection object con call creatStatement() to create the SQL statement object
Note that you want to use the try catch statement

            try {
                sql = connection.createStatement();
            } catch (SQLException e) {
                e.printStackTrace();
            }

Processing query results

Once you have a SQL statement object, you can manipulate it and return an object after manipulating a table in a database, which is the object declared with the ResultSet class
The SQL statement object calls the executeQuery() method, and the parentheses are filled with the SQL statement

ResultSet resultSet = sql.executeQuery("SELECT * FROM mybooklist");	//Select all columns in the table

Several methods of ResultSet object

• boolean next()
• String getString()

Example of integration

import java.sql.*;

public class Example14_1 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement sql;
        ResultSet resultSet;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
        String url = "jdbc:mysql://localhost:3306/book?" + "useSSL = false&serverTimezone = GMT";
        try {
            connection = DriverManager.getConnection(url, "root", "");   //Connect to database
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            sql = connection.createStatement();//Create SQL Statement Object
            resultSet = sql.executeQuery("SELECT * FROM mybooklist");   //Select the number of columns in a table
            while (resultSet.next()) {
                String str = resultSet.getString(1);
                String str1 = resultSet.getString(2);
                String str2 = resultSet.getString(3);
                String str3 = resultSet.getString(4);
                System.out.print(str + "/" + str1 + "/" + str2 + "/" + str3);
                System.out.println();
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Run Results

C:\Users\Administrator\.IntelliJIdea2019.3\config\jdbc-drivers\MySQL Connector\J 8\8.0.15\mysql-connector-java-8.0.15.jar" Chapter_14.Part_2.Example14_1
 7-302-01465/Advanced Mathematics/28.67/2020-12-02
 7-352-01465/University Japanese/58.5/2020-05-01
 8-345-35621/My University/33.73/2019-05-09
 8-345-35721/Sun Bay/58.43/2020-04-15

Process ended, exit code 0

Control Cursor

Result sets can be obtained based on demand

Statement stmt = con.createStatement(int type ,int concurrency)

Value of type determines scrolling mode

• ResultSet.TYPE_FORWORD_ONLY: The cursor of the result set can only scroll down.
• ResultSet.TYPESCROLL_INSENSITIVE: The cursor of a result set can be moved up and down, and the current result set remains unchanged when the data changes.
• ResultSet.TYPE_SCROLL_SENSITIVE: Returns a scrollable result set when the database changes.

Concurrent value determines whether the database can be updated with a result set

• ResultSet.CONCUR_READ_ONLY: You cannot update tables in a database with result sets.
• ResultSet.CONCUR_UPDATABLE: You can update tables in a database with result sets.

The following methods of ResultSet are often used in rolling queries.

• public boolean previous(): Moves the cursor up, which returns boolean data and false when moved before the first row of the result set.
• public void beforeFirst: Moves the cursor to the initial position of the result set, that is, before the first row.
• public void afterLast(): Moves the cursor after the last row of the result set.
• public void first(): Moves the cursor to the first row of the result set.
• public void last(): Moves the cursor to the last row of the result set.
• public boolean isAfterLast(): Determines if the cursor is after the last line.
• public boolean isBeforeFirst(): Determines whether the cursor precedes the first line
• public boolean isFirst(): Determines whether the cursor points to the first row of the result set.
• public boolean isLast(): Determines whether the cursor points to the last row of the result set.
• public int getRow(): Gets the line number that the current cursor points to, starting with 1, and returns 0 if the result set does not have any rows.
• public boolean absolute(int row): Moves the cursor to the row specified by the parameter row.A negative //row value means moving to the first line to the last, and so on

Since connecting to a database requires a lot of code to be written repeatedly, for convenience, encapsulate the code for the connection into a class and invoke it statically

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class GetDatabaseConnection {
    public static Connection connectDB(String DBname, String id, String password) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
        String url = "jdbc:mysql://localhost:3306/" + DBname + "?" + "useSSL = false&serverTimezone = GMT";
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, id, password);   //Connect to database
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

Example of randomly getting two records in a table

Classes for Getting Random Numbers

import java.util.LinkedList;
import java.util.Random;

public class GetRandomNumber {
    public static int[] getRandomNumber(int max, int amount) {
        LinkedList<Integer> linkedList = new LinkedList<Integer>(); //linked list
        for (int i = 1; i <= max; i++) {
            linkedList.add(i);  //Automatic packing
        }
        int result[] = new int[amount];
        for (int i = 0; i < amount; i++) {     //
            int index = new Random().nextInt(linkedList.size());    //Get Random Index of Chain List
            int m = linkedList.remove(index);   //Then remove
            result[i] = m;
        }
        return result;
    }
}

import java.sql.*;

public class Example14_1 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement sql;
        ResultSet resultSet;
        connection = GetDatabaseConnection.connectDB("book", "root", "");
        try {
            sql = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);//Create SQL Statement Object
            resultSet = sql.executeQuery("SELECT * FROM mybooklist");   //Select the number of columns in a table
            resultSet.last();   //Point the cursor to the last of the set
            int max = resultSet.getRow();   //Get the number of rows in the table
            System.out.println("Common in tables" + max + "Records");
            int[] a = GetRandomNumber.getRandomNumber(max, 2);
            for (int i : a) {
                resultSet.absolute(i);  //Move cursor to line i
                String str = resultSet.getString(1);
                String str1 = resultSet.getString(2);
                String str2 = resultSet.getString(3);
                String str3 = resultSet.getString(4);
                System.out.print(str + "/" + str1 + "/" + str2 + "/" + str3);
                System.out.println();
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Run Results

C:\Users\Administrator\.IntelliJIdea2019.3\config\jdbc-drivers\MySQL Connector\J 8\8.0.15\mysql-connector-java-8.0.15.jar" Chapter_14.Part_2.Example14_1
 There are 4 records in the table
 7-302-01465/Advanced Mathematics/28.67/2020-12-02
 8-345-35621/My University/33.73/2019-05-09

Process ended, exit code 0

Conditions and Sort Queries

where clause

General format:

Selectect field from table name where condition

• Fixed Value Comparison

For example, query a record whose name is higher math from mybooklist//

select * from mybooklist where name = 'Advanced mathematics'

• Field values in an interval range

For example, query a record from mybooklist with a price between 28.68 and 87.7//

select * from mybooklist where price>28.68 and price<=87.7

• Use operator like for pattern matching//Use% for 0 or more, _Represents 1

sort
Sort records with order by clause

select * from table name order by field name (column name)
select * from table name where conditional order by field name (column name)

select * from mybooklist order by price	//Sort by price

CET4P189

• primary
• outside
• variation
• interfere
• owe
• dense
• starve
• simplicity
• burden
• heap
• scatter