MySQL database JDBC programming (Java connects to MySQL)

1. Basic conditions for database programming

If you want to implement code to operate the database, the following conditions are your prerequisites for implementing it

programming language:

For example, Java, C++, Python, etc., these languages ​​can all operate databases

Operations on a database:

For example, I introduced the operation of MySQL in the previous chapter. To implement other databases such as Oracle, SQL Server, etc., you also need to learn some operations of the corresponding database.

Install the database driver package:

Different databases provide different database driver packages for different programming languages. These driver packages implement the APIs for operating the corresponding databases.

2. Database Programming in Java: JDBC

Since different database vendors implement database APIs differently, many languages ​​encapsulate these database APIs again to form a unified set of APIs. In this way, you can operate multiple different databases through a set of code

In Java, such encapsulation is done by the Java standard library, which encapsulates a unified database API called JDBC.

expand:

Java itself is a cross-platform language. Although different operating systems provide different APIs, Java itself encapsulates these APIs and provides a unified interface in the standard library. Therefore, Java can be compiled once and run everywhere.

JDBC Introduction:

• JDBC, which stands for Java Database Connectivity , refers to Java database connectivity. Is a Java API for executing SQL statements, which is a database connection specification in Java.
• This API consists of some classes and interfaces in the java.sql and javax.sql packages. It provides a standard API for Java developers to operate databases and can provide unified access to multiple relational databases.

Notice:

• The JDBC API is part of the Java standard library and can be used directly, but the MySQL JDBC driver is not part of the system, so you need to download and install it separately.
• The MySQL JDBC driver is actually a specific implementation of some classes and interfaces in the JDBC API.

3. JDBC access database hierarchy

4. Introduction to MySQL database operations

In the previous chapter, I introduced some knowledge about MySQL. If you have any gaps in this area, you can directly supplement it through the following article.

Chapter 1 link: [MySQL Database] Basic knowledge of database

Chapter 2 Link: [MySQL Database] MySQL database operations and data types

Chapter 3 Link: [MySQL Database] Basic Operations on Data Tables

Chapter 4 Link: [MySQL Database] Database Constraints and Data Table Design Principles

Chapter 5 Link: [MySQL Database] Aggregate Query and Union Query Operations

Chapter 6 Link: [MySQL Database] MySQL Indexes and Transactions

5. Download the MySQL driver package and add it to the project

Since the MySQL JDBC driver is not included with the system, you need to download and install it separately.

The official websites of major databases have JDBC drivers for the corresponding databases, but here I recommend using some central repositories for downloading, such as mvnrepository

Driver package download steps:

Go to the mvnrepository website and search for MySQL in the search bar. You can find the following results

Select the first MySQL Connector/J to jump to the download version selection page

Choose the driver that corresponds to your database version (the major versions must correspond, and the minor versions are not much different and can be selected at will). Since I use the 5.x series of MySQL, I will choose the major version 5. After selecting, you will jump to the final download page

Click jar to start downloading (this jar package is to package some .class files in this driver package in the form of a compressed package)

After the download is complete, the driver package has been downloaded to your local computer. You only need to import it into your project to use it.

6. JDBC usage steps

6.1 Create a database source and connect

Create a database source:

DataSource dataSource=new MysqlDataSource();
// DataSource is an interface from the Java standard library, which is used to indicate "where the database is"
// MysqlDataSource is a driver package from MySQL. It is a class that implements the DataSource interface

The database is a server program. DataSource can be used to describe the server address, port, user name, password, database name to be accessed, etc.

Set the database location information to DataSource

// 1) Use a URL to indicate the connected database, database IP, port, database name, encoding method, and whether to encrypt ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&setSSL=false");
// 2) Set the username for logging into the database ((MysqlDataSource)dataSource).setUser("root");
// 3) Set the password for logging into the database ((MysqlDataSource)dataSource).setPassword("1234");

Since setURL , setUser , and setPassword are all implemented MysqlDataSource , they need to be downgraded when used. The above URL is a fixed writing method, for example: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&setSSL=false

• jdbc means using JDBC to access the database
• mysql means the database being accessed is MySQL
• 172.0.0.1 is the IP address. The IP address is used to distinguish which host it is. The IP address 172.0.0.1 represents the host itself that is currently being used. Since my MySQL server is also installed on my computer, I modified it myself.
• 3306 is the port number, which is manually set when installing the database server. The default is usually 3306. It is used to distinguish a program on the host.
• test is the name of the database to be accessed. This is in my database. Modify it yourself
• characterEncoding=utf8 is used to specify the encoding method. Here, utf8 is used. It needs to correspond to the database encoding method used. Please modify it yourself.
• useSSL=false is used to indicate whether encryption is used. Here it means no encryption. Please modify it yourself.

Connect to the database and conduct real network communication:

Connection connection=dataSource.getConnection();

Connection is in the Java standard library, although it is also available in the MySQL driver. It is used to connect to the database server over the network.
getConnection is used to attempt to establish a connection to the specified database URL. If the connection is successful, a Connection object is returned. If it fails, an exception is thrown. Because getConnection may fail to connect (for example, due to input errors such as IP address and port), it is necessary to throw an exception to the upper caller through throws in the method declaration or use try-catch to handle the exception.

6.2 Construct SQL statements to prepare for execution

Construct a sql to be executed through a string

// For example, to perform a new element operation (the table name is student, with two columns id and student
Scanner scanner = new Scanner (System.in);
System.out.print("Please enter id: ");
int id=scanner.nextInt();
System.out.print("Please enter your name: ");
String name = scanner.next();
String sql="insert into student values(?,?)";

1. sql is the constructed SQL statement, which contains the specific operations to be performed
2. There is no need to add semicolons in SQL statements
3. Represents a wildcard character, which can be used to dynamically replace the content in the SQL statement. The content to be replaced is represented by? Instead, use some methods of the PreparedStatement object to replace it with the specific value to be changed.

For example:

void setInt(int paramenterIndex, int x) : paramenterIndex indicates the specific position of the wildcard to be replaced in the SQL statement (starting from 1), and x indicates the specific value to be replaced
void setString(int parameterIndex, String x) : paramenterIndex indicates the specific position of the wildcard to be replaced in the SQL statement (starting from 1), and x indicates the specific value to be replaced. Through the prepareStatement(sql) method, the constructed string sql is converted into the real SQL statement at the bottom of the database.

PreparedStatement statement=connection.prepareStatement(sql);
//Use the setInt method to replace the first wildcard in the SQL statement with a specific value statement.setInt(1,id);
//Use the setString method to replace the second wildcard in the SQL statement with a specific value statement.setString(2,name);

In JDBC, connection connection object is established using Connection and the database. Then connection can call the prepareStatement(String sql) method to compile and preprocess the SQL statement specified by the parameter sql, generate the underlying internal command of the database, and encapsulate the command in the PreparedStatement object.

6.3 Execute SQL and process the result set

Execute SQL through executeQuery or executeUpdate method of the PreparedStatement object

If you want to perform content change operations (add, modify, delete), use the int executeUpdate() method

int ret = statement.executeUpdate();
// executeUpdate returns the number of rows affected by the operation // You can print the return value to display the number of rows affected System.out.println("ret: "+ret);

If you want to perform a query operation, use the ResultSet executeQuery() method

ResultSet resultSet=statement.executeQuery();
// The return result of executeQuery is a temporary table structure found after executing the operation, which is stored in the ResultSet object. // Next, we can traverse it, similar to the traversal of an iterator, as follows while(resultSet.next()){
    // Assume there are two columns id and name
    int id = resultSet.getInt(id);
    String name = resultSet.getString(name);
    System.out.println("id="+id+", name="+name);
}

The SQL query statement returns a ResultSet object, ResultSet is composed of rows of data organized by columns (fields).
The ResultSet object can only see one row of data at a time. Use the next() method to move to the next row of data (similar to i++)
You can use the getXxx() method of ResultSet object to get the fields. Common methods will be introduced later.

6.4 Releasing Resources

When we have finished executing our SQL statements, if we no longer use certain objects, we need to close the connection and release the corresponding resources.

// If a ResultSet object is no longer needed, you need to close the connection resultSet.close();

// When the Connection is no longer needed, you need to close the connection connection.close();

// After PreparedStatement is no longer needed, you need to close the connection statement.close();

Objects such ResultSet , Connection , and PreparedStatement all correspond to some hardware resources of the machine and must be returned in time if not used. It is similar to borrowing books. If people only borrow books from the library and do not return them, the library's book resources will continue to decrease. These objects can use the close() method to close the connection established with the client and the server to release the occupied resources.

6.5 JDBC Programming Template

By following the above five steps, you can perform basic Java JDBC programming. Although there is more than one method, if you don’t know how to do it yet, just master this method. Finally, let's summarize the entire template. Based on it, we can use Java language to perform various operations on MySQL database.

public static void test(){
    // 1. Create a database source and connect Connection
 DataSource dataSource=new MysqlDataSource();
    ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
    ((MysqlDataSource)dataSource).setUser("root");
    ((MysqlDataSource)dataSource).setPassword("1234");
    Connection connection=dataSource.getConnection();

    // 2. Construct an SQL statement to prepare for the operation to be performed String sql="";
    PreparedStatement statement=connection.prepareStatement(sql);

 // 3. Execute SQL and process the result set int ret=statement.executeUpdate();

    // 4. Execution completed, release resources statement.close();
    connection.close();
}

7. JDBC common interfaces and classes

The following interfaces and classes have been introduced in the above JDBC programming steps, so they can complement each other's vulnerabilities.

7.1 DataSource and MysqlDataSource

Replenish:

The above code can also directly use MysqlDataSource to create a data source, so there is no need to use downward transformation. But if you use DataSource, then the rest of the code is actually using the DataSource type, which is a class that has nothing to do with the specific database type. When you need to switch databases, you don't need to modify the code significantly.

7.2 Connection

The Connection interface implementation class is provided by the database. There are usually two ways to obtain a Connection object:

Method 1: Obtain through DataSource (data source) object

// Create a database source DataSource dataSource=new MysqlDataSource();

// Set database details ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
((MysqlDataSource)dataSource).setUser("root");
((MysqlDataSource)dataSource).setPassword("1234");

// Create a database connection Connection connection=dataSource.getConnection();

Method 2: Obtain through the static method of DriverManager (driver management class)

// Load the JDBC driver Class.forName("com.mysql.cj.jdbc.Driver");

// Create a database connection Connection connection=DriverManager.getConnection(url);

The MySQL database driver is encapsulated in the Driver class, the package name of which is com.mysql.cj.jdbc . This class is not a class in the Java runtime class library.

The difference between the above two methods:

• Connection obtained by DriverManager class cannot be reused. When releasing resources after each use, the physical connection is closed through connection.close()
• DataSource provides connection pool support. The connection pool creates a certain number of database connections when it is initialized. These connections can be reused. Each time the database connection is used up, the resources are released through connection.close() , which means that Connection object is recycled.

7.3 PreparedStatement

PreparedStatement is one of the three objects provided by the JDBC API that can send SQL statements to the database. Here is a brief introduction to these three

• Statement : Used to execute simple SQL without parameters
• PreparedStatement :

1. Used to execute SQL statements with or without parameters
2. SQL statements are precompiled in the database system
3. Executes faster than Statement objects

• CallableStatement : Used to execute calls to database stored procedures

7.4 ResultSet

ResultSet object is called a result set, which represents all the rows that meet the conditions of the SQL statement, and it provides access to the data in these rows through a set of getXxx() methods.
The data in ResultSet is arranged row by row. Whenever there are multiple fields, there is a record pointer. The data row pointed by the pointer is called the current data row. We can only operate on the current data row. If we want to get a certain record, we need to use the next() method of ResultSet , which can be combined with a while loop to traverse all the records in the ResultSet.

Common ResultSet object methods:

Notice:

The ResultSet object and the database connection object Connection are tightly connected. Once the connection object is closed, the data in the ResultSet object disappears immediately.

8. Java operation database instance

8.1 Adding data to the table

Add student id and name to the student table:

public static void testInsert() throws SQLException {
    DataSource dataSource=new MysqlDataSource();
    ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
    ((MysqlDataSource)dataSource).setUser("root");
    ((MysqlDataSource)dataSource).setPassword("1234");
    Connection connection=dataSource.getConnection();
    Scanner scanner = new Scanner (System.in);
    System.out.print("Please enter id: ");
    int id=scanner.nextInt();
    System.out.print("Please enter your name: ");
    String name = scanner.next();
    String sql="insert into student values(?,?)";
    PreparedStatement statement=connection.prepareStatement(sql);
    statement.setInt(1,id);
    statement.setString(2,name);
    int ret = statement.executeUpdate();
    System.out.println("ret: "+ret);
    statement.close();
    connection.close();
}

8.2 Deleting data from a table

In the table named student, delete the records in the student table that meet the id condition:

public static void testDelete() throws SQLException {
    DataSource dataSource=new MysqlDataSource();
    ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
    ((MysqlDataSource)dataSource).setUser("root");
    ((MysqlDataSource)dataSource).setPassword("1234");
    Connection connection=dataSource.getConnection();
    Scanner scanner = new Scanner (System.in);
    System.out.print("Please enter the id to be deleted: ");
    int id=scanner.nextInt();
    String sql="delete from student where id=?";
    PreparedStatement statement=connection.prepareStatement(sql);
    statement.setInt(1,id);
    int ret = statement.executeUpdate();
    System.out.println("ret: "+ret);
    statement.close();
    connection.close();
}

8.3 Modifying data in a table

In the table named student, modify the names of students who meet the id condition:

public static void testUpdate() throws SQLException {
    DataSource dataSource=new MysqlDataSource();
    ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
    ((MysqlDataSource)dataSource).setUser("root");
    ((MysqlDataSource)dataSource).setPassword("1234");
    Connection connection=dataSource.getConnection();
    Scanner scanner = new Scanner (System.in);
    System.out.print("Please enter the student ID you want to change: ");
    int id=scanner.nextInt();
    System.out.print("Please enter the student name you want to change: ");
    String name = scanner.next();
    String sql="update student set name=? where id=?";
    PreparedStatement statement=connection.prepareStatement(sql);
    statement.setString(1,name);
    statement.setInt(2,id);
    int ret = statement.executeUpdate();
    System.out.println("ret: "+ret);
    statement.close();
    connection.close();
}

8.4 Finding Data in a Table

Find all the data in the table named student:

public static void testSelect() throws SQLException {
    DataSource dataSource=new MysqlDataSource();
    ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
    ((MysqlDataSource)dataSource).setUser("root");
    ((MysqlDataSource)dataSource).setPassword("1234");
    Connection connection=dataSource.getConnection();
    String sql="select * from student";
    PreparedStatement statement=connection.prepareStatement(sql);
    ResultSet resultSet=statement.executeQuery();
    while(resultSet.next()){
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        System.out.println("id = "+id+", name = "+name);
    }
    resultSet.close();
    statement.close();
    connection.close();
}

This is the end of this article about MySQL database JDBC programming (Java connecting to MySQL). For more relevant Java connecting to MySQL content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!