[Database JDBC] Establish a connection between the database and java code

Foreword:
Hello everyone, I am Liangchen, JDBC is called Java Database Connectivity, which can be called Java Database Connectivity. It can connect java code with sql statement. Isn’t it amazing? Let’s slowly learn the related operations of JDBC.

Personal homepage: Good day needle does not poke
Column: Database
Inspirational statement: Life may make us bruised and bruised, but in the end these wounds will become our lifetime wealth.
I look forward to your three consecutive links, attention, likes, and favorites.
The author’s ability is limited, and there may be mistakes. Welcome to correct me.
Wish to be your companion and explore the ocean of Java together.

JDBC

  • 1. A brief introduction to JDBC
  • 2. JDBC driver package
  • 3. Writing JDBC code
    • 3.1 Create a good data source
    • 3.2 The code establishes a connection with the database
    • 3.3 Let the user enter the data to be inserted through the console.
    • 3.4 Operating the database
    • 3.5 Executing the database
    • 3.6 Release resources
  • 4. JDBC insert data complete code

1. Brief JDBC

JDBC, the full name is Java Database Connectivity, java database connection. 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.,javax.sql. package. It provides a standard API for Java developers to operate databases, and can provide a variety of relational databases. Unified access.

After working in the future, seldom use sql statement to directly operate the database, we will do a little bit through code operation or software provided by the company.

Then the question arises, there are so many database software, and it is not a company, so when they connect with java code, do various database software provide the same API? Obviously different APIs, different developers, did not carry out Any agreement, then the API they get must be different.

If there are different APIs, programmers need to use different APIs when using different database software, then it is really crying for programmers, and they have to learn various APIs. At this time, java gives them One requirement is that if you want to use java, you must package the respective APIs, so that no matter which database software you use, java programmers can call the API with the same name.

Advantages of JDBC:

  • Java language access database operation is completely oriented to abstract interface programming
  • The development of database applications does not need to be limited to the API of a specific database vendor
  • Program portability is greatly enhanced

2. JDBC driver package

The so-called database driver package is a database manufacturer that provides a program to complete the conversion of the API (encapsulation of the native API), and then forms the shape of JDBC according to the requirements provided by java.

If programmers want to write JDBC code, they must import the corresponding database driver package in the project. To import the driver package, they must first download the driver package.

Java officially provides us with a central warehouse, maven warehouse, which is equivalent to the application store on our mobile phones. When we download a certain game, we don’t need to go to the official website to download it, just download it directly in the application store. You don’t need to go to Remember the official website, just search the maven warehouse directly on the browser.

Click the above arrow to download. Note that the driver package we downloaded must be consistent with the major version of the database. What is a major version? Take my mysql software as an example. My version is above 8. As shown in the figure below, we are downloading When using JDBC, the major version is 8, and the minor version is not required.

What needs to be emphasized here is that the downloaded driver package does not need to be decompressed , and java can recognize the compressed package file of the database driver package.

At this point, we need to import the JDBC driver package into our code. We first create a folder lib to store the driver package file, directly copy the compressed package file, click the lib file, and ctrl + v to copy it.

Then we right-click lib and select Add as Library. After this step, idea can recognize the jar in the directory
Wenj

3. Writing JDBC code

3.1 Create a good data source

DataSource dataSource = new MysqlDataSource();
        // Set the address where the database is located
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/stu2?characterEncoding=utf8 & amp;useSSL=false");
        // Set the username to log in to the database
        ((MysqlDataSource) dataSource).setUser("root");
        // This is the password to set the login database
        ((MysqlDataSource) dataSource).setPassword("");

3306 is the default port number of the database, SSL is the encryption protocol, false means unencrypted, 127.0.0.1 is the loop ip (means the host ip), root is the user name, the default is root, and the password is the password of the database, which I have not set Password, so an empty string.

3.2 The code establishes a connection with the database

Connection connection = dataSource. getConnection();

3.3 Let the user enter the data to be inserted through the console.

 Scanner scanner = new Scanner(System.in);
        System.out.println("Please enter the student number: ");
        int id = scanner. nextInt();
        System.out.println("Please enter your name: ");
        String name = scanner. next();

3.4 Operating the database

 String sql = "insert into student values(?, ?)";
        // It is not enough to just use a String type of sql here, you need to wrap this String into a "statement object"
        PreparedStatement statement = connection. prepareStatement(sql);
        // Perform replacement operation.
        statement.setInt(1, id);
        statement.setString(2, name);
        System.out.println("statement: " + statement);

3.5 Executing the database

 int ret = statement. executeUpdate();
        System.out.println(ret);

3.6 Release resources

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

Then let’s execute the code

Then, let’s take a look at the information in the database.

4. JDBC insert data complete code

//import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class TestJDBC {<!-- -->
    public static void main(String[] args) throws SQLException {<!-- -->
        Scanner scanner = new Scanner(System.in);

        // 1. Create a good data source
        DataSource dataSource = new MysqlDataSource();
        // Set the address where the database is located
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/stu2?characterEncoding=utf8 & amp;useSSL=false");
        // Set the username to log in to the database
        ((MysqlDataSource) dataSource).setUser("root");
        // This is the password to set the login database
        ((MysqlDataSource) dataSource).setPassword("");

        // 2. Let the code establish a connection with the database server
        Connection connection = dataSource. getConnection();

        // 2.5 Let the user enter the data to be inserted through the console.
        System.out.println("Please enter the student number: ");
        int id = scanner. nextInt();
        System.out.println("Please enter your name: ");
        String name = scanner. next();

        // 3. Operate the database -> insert data
        // The key is to construct a SQL statement~
        // SQL constructed in JDBC, don't need to bring ;
        // ; It is only used to distinguish different statements on the command line. Now it is directly operated in the code~~
        String sql = "insert into student values(?, ?)";
        // It is not enough to just use a String type of sql here, you need to wrap this String into a "statement object"
        PreparedStatement statement = connection. prepareStatement(sql);
        // Perform replacement operation.
        statement.setInt(1, id);
        statement.setString(2, name);
        System.out.println("statement: " + statement);

        // 4. Execute SQL, which is equivalent to scanning the code to pick up the item
        // In SQL, if it is insert, update, delete, all use the executeUpdate method.
        // If it is select in SQL, use the executeQuery method.
        // The return value indicates that this operation has affected several lines. It is equivalent to the number obtained after entering sql in the console~
        int ret = statement. executeUpdate();
        System.out.println(ret);

        // 5. At this point, the SQL has been executed. Then the resources need to be released.
        statement. close();
        connection. close();
    }
}

Sequence:
After reading this article, everyone must have a certain understanding and understanding of JDBC programming. I hope this small article can help you. See you in the next article.