Load MYSQL database driver
The development tool used by the author is IntelliJ IDEA, and other integrated environments have similar principles.
Download MYSQL database driver
JDBC is java data base connectivity. In order to access the database on the MySQL database server, the application must ensure that the corresponding JDBC-MYSQL driver is installed on the computer where the application resides. You can go to the official website: www.mysql.com Com download, you can also use the MySQL driver I downloaded.
Login to the official website of mysql.1 COM, select Products on the navigation bar of the page
2. Select MYSQL Connectors under MySQL Features in the right area of the page
3. Select JDBC Driver for MySQL(Connector/J) under Developed by MySQL in the right area of the page
4. Or you can use the driver I downloaded mysql-connector-java-5.1.40-bin jar
Link: https://pan.baidu.com/s/1XEwVXKilL9Hc7Tjhl5xXGA
Extraction code: 5zrb
Import MYSQL database driver
1. Click file - > project structure
2. Click Modulus, Dependencies, the + sign, JARs or directories, and then select the database driver just downloaded
3. After importing successfully, you can see this driver in External Libraries
Load MYSQL database driver
The application program is responsible for loading the JDBC-MYSQL database driver. The code is as follows. The try catch statement is used because there may be exceptions and the loading is unsuccessful
try{
Class.forName("com.mysql.jdbc.Driver");
}
catch(Exception e){
}
Connect to MYSQL database
There are two ways to connect to MYSQL database, among which getconnection () is an overloaded method.
First connection mode
The database Connection object can only be declared with Connection and cannot be obtained with new. To get the database Connection object, you must use the API provided by JDBC: the driver manager calls its method getConnection (url) to get it. As follows:
Connection con = DriverManager.getConnection(String url);
The format of url is:
String url = "jdbc:mysql://localhost:3306/xsxk？user=root&password=root&userSSL=true&characterEncoding=utf-8";
1. Localhost: This is the IP address of the host where the database you want to connect to is located. If it is local, use localhost
2. 3306: This is the port number used by MySQL database. Since 3306 is the default port number of MySQL database server, applications are allowed to omit the default port number when connecting to the database
3. Xsxk: This is the name of the database you want to connect to. What I connect here is a database named xsxk on this machine.
4. user=root: This is the user name who has access to the database. The user name I use is root
5. password=root: This is the password corresponding to the user name. The password I use is root
6,useSSL=true: MYSQL5. The version above 5.6 can be written but not written, and the version after 5.6 must be written; Usessl is: use Secure Socket Link, which uses secure socket connection
7. characterEncoding=utf-8: if there are Chinese characters recorded in the table of the database, an additional parameter characterEncoding must be passed when establishing the connection, and the value is gb2312 or utf-8
Second connection mode
The second connection method is more brief, that is to peel the account and password from the URL and pass them to the getConnection(String url, String user, String passeord) function as two parameters, as follows:
Connextion con;
String url = "jdbc:mysql://localhost:3306/xsxk?userSSL=true";
String user = "root";
String password = "root";
try{
con = DriverManager.getConnection(url,user,password);
}catch(SQLException e){}
Query operation
Send SQL statement
1. Get SQL statement object
SQL Statement objects can only be declared with Statement, not generated with new. You need to use the database connection object obtained above to call its method createStatement(), as follows:
try{
Statement sql = con.createStatement();
}catch(SQLException e){}
2. Send sql statement
The SQL statement object calls the method executeQuery("String sql") to send the SQL query statement.
And return a result set Resultset. The following is a query request sent to a table named students:
ResultSet rs = sql.executeQuery("SELECT * FROM students");
Processing query results
The ResultSet object can only see one data row at a time. Use the next() method to move to the next row. After obtaining one row of data, the ResultSet object can use the getXxx () method to obtain the field value. The function parameter can be either the field name or the column represented by Arabic numerals, such as getInt("name") or getInt(1). It is worth noting that the initial position of the ResultSet object is before the first row of the result set
Some methods of ResultSet
Return type Method name boolean next() byte getByte(int columnIndex)//Column number representation Date getDate(int columnIndex) double getDouble(int columnIndex) float getFloat(int columnIndex) int getInt(int columnIndex) long getLong(int columnIndex) String getString(int columnIndex) byte getByte(String columnName)//Field name Date getDate(String columnName) double getDouble(String columnName) float getFloat(String columnName) int getInt(String columnName) long getLong(String columnName) String getString(String columnName)
Close connection
JDBC uses mapping technology instead of replication. The results obtained from the query are mapped into the ResultSet, just like looking in the mirror instead of replication. This means that the result object is tightly bound to the database connection object. Once the connection object is closed, the data in the ResultSet disappears immediately. Therefore, when we use the data in the ResultSet, we must always maintain the connection with the database until we use the data in the ResultSet, so call con. After using the data close().
Sequential query example
The so-called sequential query means that the resultSet object can only see one data row at a time and use the next method to move to the next data row. The following example is to query the value of the name field of xsb (student table) in xsxk (student course selection) database
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Example {
public static void main(String[] args) {
Connection con = null;//Database connection object
Statement sql;//SQL statement object
ResultSet re;//Result set object
try{
Class.forName("com.mysql.jdbc.Driver");//Load JDBC-MYSQL driver
}
catch (Exception e){
System.out.println("Loading failed");//exception handling
}
String uri = "jdbc:mysql://localhost:3306/xsxk?useSSL=true "; / / URL of the database to be accessed
String user = "root";//User name authorized to access the database
String password = "root";//Password corresponding to user name
try{
con = DriverManager.getConnection(uri,user,password);//Get connection
}
catch (Exception e){
System.out.println("Connection acquisition failed");//exception handling
}
try{
sql = con.createStatement();//Get SQL statement object
re = sql.executeQuery("SELECT * FROM xsb");//Execute query operation
while(re.next()){
String xuehao = re.getString(1);//View the value of the first column (student number)
String number = re.getString("xm");//View xm field value (name)
System.out.print(xuehao);//Output the queried student number
System.out.println(" "+number);//Output the queried name
}
con.close();//After using the data, close the connection
}
catch (Exception e){
System.out.println(e);
}
}
}
The query result is consistent with the data in the database, as shown in the following figure:
Cursor query example
The cursor position of the result set is above the first row. Using the next() method to view the data becomes a sequential query. If you want to directly obtain the data of a row in the result set, you need to get a scrollable result set. For this purpose, use con When using the createstatement () method, we need to make corresponding changes, as shown below:
Statement sql = con.createStatement(int type,int concurrency);
1. The value of type determines the rolling mode. The values are as follows:
1)ResultSet.TYPE.FORWORD_ONLY: the cursor of the result set can only move down
2)ResultSet. TYPE. SCROLL_ Independent: the cursor of the result set can be moved up and down. When the database changes, the current result set remains unchanged
3)ResultSet.TYPE.SCROLL_SENSITIVE: the cursor of the result set can move up and down. When the data changes, the current result set changes synchronously
2. The value of concurrent determines whether the database can be updated with the result set. The values are as follows:
ResultSet.CONCUR.READ_ONLY: cannot update a table in the database with a result set
ResultSet.CONCUR.UPDATEBLE: tables in the database can be updated with the result set
3. When using cursor query, the following methods of ResultSet are often used:
public boolean previous(); //Move the cursor up and return false when moving before the first line public void beforeFirst(); //Move 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 whether the cursor is after the last line public boolead isBeforeFirst(); //Determines whether the cursor is before the first line public boolean isFirst(); //Determine whether the cursor is on the first line public boolean isLast(); //Determines whether the cursor is on the last line public int getRow(); //Get the line number pointed by the current cursor. The line number is encoded from 1. If there is no line in the result set, 0 is returned public boolean absolute(int row); //Move the cursor to the row specified by the parameter row. If row is a negative number, it represents the reciprocal row. Absolute means moving to the last row, and absolute (- 2) means moving to the penultimate row. When moving to the front of the first row or after the last row, the method returns false.
The following example first moves the cursor to the last row to get the total number of records in the student table, and then moves the cursor to row 15 to get the name of the student in row 15
package Example11;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Example {
public static void main(String[] args) {
Connection con = null;
Statement sql;
ResultSet re;
try{
Class.forName("com.mysql.jdbc.Driver");
}
catch (Exception e){
System.out.println("load JDBC fail");
}
String uri = "jdbc:mysql://localhost:3306/xsxk?useSSL=true";
String user = "root";
String password = "root";
try{
con = DriverManager.getConnection(uri,user,password);
}
catch (Exception e){
System.out.println("connection failed");
}
try{
sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
re = sql.executeQuery("SELECT * FROM xsb");
re.last();//Moves the cursor to the last line
int max = re.getRow();
System.out.println("There are two student lists"+max+"record");
re.absolute(15);
String name = re.getString("xm");
System.out.println("The name of the student on line 15 is:"+name);
con.close();
}
catch (Exception e){
System.out.println(e);
}
}
}
The running result corresponds to the data of the table in the database
Update, add, and delete operations
The Statement object calls the following methods to update, add and delete the database through the parameter sql:
public int executeUpdate(String sql);
The following example implements
Insert a record named "Zhang San"
Update a record named "Zhang" to "Chen"
A record named "Li Si" was deleted
package Example11;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Example11_4 {
public static void main(String[] args) {
Connection con = null;
Statement sql;
String url="jdbc:mysql://localhost:3306/xsxk?useSSL=true&characterEncoding=utf-8";
String user="root";
String password = "root";
try{
Class.forName("com.mysql.jdbc.Driver");
}catch (Exception E){
System.out.println("load JDBC fail");
}
try{
con = DriverManager.getConnection(url,user,password);
}catch (Exception e){
System.out.println("connection failed");
}
try{
sql = con.createStatement();
sql.executeUpdate("insert into try values (3,'Zhang San')");
sql.executeUpdate("update try set name = 'Chen Chen' where id = 1 ");
sql.executeUpdate("delete from try where name='Li Si'");
con.close();
}catch(Exception e){
System.out.println("operation failed");
}
}
}
The records in the table before operation are:
The records in the table after operation are:
Use preprocessing statements
The object preprocessing mechanism provided by Java statement is called object preprocessing, which is more efficient
1. Advantages of preprocessing statements
Send an SQL statement to the database, such as select * from try. The SQL interpreter in the database is responsible for generating the underlying internal command from the SQL statement, and then executing the command to complete relevant operations. If you constantly submit SQL statements to the database, it is bound to increase the burden on the SQL interpreter in the database and affect the execution speed. If the application can target the connected database, the implementation will interpret the SQL statement as the internal command at the bottom of the database, and then directly let the database execute this command, which obviously not only reduces the burden of the database, but also improves the speed of accessing the database.
For JDBC, if Connection is used to establish a Connection object con with a database, the con can call the preparedStatement(String sql) method to precompile the statement specified by the parameter sql to obtain a preprocessed statement object.
2. Use wildcards
Wildcards can be used when preprocessing SQL statements? (English question mark) to replace the value of the field, but the specific value represented by the wildcard must be set before the preprocessing object is executed.
The following example represents the first wildcard? The value represented is "Zhang San", the second wildcard? The value represented is "Li Si":
sql.setString(1,"Zhang San"); sql.setString(2,"Li Si");
Set wildcard when using preprocessing statement object? The commonly used methods are
void setDate(int preparedIndex,Date x); void setDouble(int preparedIndex,Double x); void setFloat(int preparedIndex,Float x); void setInt(int preparedIndex,Int x); void setLong(int preparedIndex,Long x); void setString(int preparedIndex,String x);
The following example uses wildcards to insert a record named "use wildcards" with sequence number of 100 into the table
package Example11;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Example11_5 {
public static void main(String[] args) {
Connection con = null;
String url = "jdbc:mysql://localhost:3306/xsxk?useSSL=true&characterEncoding=utf-8";
String user = "root";
String password = "root";
PreparedStatement sql;
try{
Class.forName("com.mysql.jdbc.Driver");
}catch(Exception e){
System.out.println("Loading failed");
}
try{
con = DriverManager.getConnection(url,user,password);
}catch(Exception e){
System.out.println("connection failed");
}
try{
sql = con.prepareStatement("insert into try values(?,?)");
sql.setInt(1,100);
sql.setString(2,"Use Wildcards ");
sql.executeUpdate();
con.close();
}catch(Exception e){
System.out.println("operation failed");
}
}
}
The results are shown in the figure below:
