JDBC linked database

JDBC introduction

JDBC (Java DataBase Connectivity) is a Java API for executing SQL statements. It provides unified access to multiple relational databases. It consists of a set of classes and interfaces written in the Java language. JDBC provides a benchmark against which more advanced tools and interfaces can be built to enable database developers to write database applications, and it is also a brand name.

The JDBC framework

The JDBC API supports two – and three-tier processing models for database access, but in a typical JDBC architecture there are two layers:
JDBC API: Provides an application’s management connection to JDBC.
JDBC Driver API: supports JDBC management of Driver connections.
The JDBC API uses the driver manager and database-specific drivers to provide transparent connections to heterogeneous databases.
The JDBC driver manager ensures that the correct driver accesses each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases.
Here is the JDBC structure diagram, which shows the location of JDBC drivers and Java applications on the driver manager side:

Commonly used the JDBC API

The DriverManager class manages the list of database drivers. Determines whether the content matches the connection request of the correct database driver from the communication subprotocol used by the Java application. The first driver that identifies JDBC in a certain subprotocol will be used to establish a database connection.
Driver This interface handles communication with the database server. Driver objects are rarely used directly. Instead, objects in DriverManager are used to manage objects of this type. It also abstracts the details related to the work of the driver object
Connection All methods for this interface to access the database. The connection object represents the communication context, that is, all communication with the database is through this unique connection object.
Statements can be submitted to the database using SQL statements of objects created by this interface. Some derived interfaces accept arguments in addition to executing stored procedures.
ResultSet These objects are saved from the database after executing SQL queries using the Statement object to retrieve data. It acts as an iterator that can be moved to retrieve the next piece of data.
SQLException this class is used to handle any errors that occur in a database application.

JDBC Usage Procedure

Creating a new database using a JDBC application requires the following steps:

The import package is a package that contains the JDBC classes required for database programming. The jar package for database connection needs to be added to the project to import. Example: mysql-connector-java-5.1.46.jar.
Registering a JDBC driver requires initializing the driver so that the database communication channel can be opened in the program.
Open the Connection you need to use the DriverManager. GetConnection () method to create a Connection object, which represents the physical Connection with the database server. To create a new database, you do not need to provide any database name when preparing the database URL, as described in the following example.
Executing the query requires an object of type Statement to build and submit the SQL Statement to the database.
Cleaning up the environment requires explicitly shutting down all database resources without relying on the JVM’s garbage collection to avoid wasting resources.

JDBC Connecting to the database

Before performing the following examples, make sure you are ready to:

Has database administrator rights to create a database in a given schema. To perform the following example, replace the username and password here with the actual username and password.

The MySQL or database is up and running.

Prepare test data

Create a test database

CREATE DATABASE easilyj;
Copy the code

Create test table

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `user_password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
);
Copy the code

Connecting to a Database

import java.sql.Connection;
import java.sql.DriverManager;
public class CreateDatabase {
    private static String url = "jdbc:mysql://localhost:3306/jsptest"; // Database address
    private static String userName = "root"; // Database user name
    private static String passWord = "123456"; // Database password
    private static Connection conn = null;
    // Get the database connection
    public static Connection getConnection(a) {
        if (null == conn) {
            try {
                Class.forName("com.mysql.jdbc.Driver"); // Load the driver
                conn = DriverManager.getConnection(url, userName, passWord); // Create a database connection
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("JDBC connection to database failed"); }}return conn;
    }
    public static void main(String[] args) {
        System.out.println("JDBC connection to database successful");
        System.out.println("JDBC connection address:"+ getConnection()); }}Copy the code

If the connection is successful, the memory address of the obtained connection is returned.

A JDBC connection database JDBC connection address for success: com. Mysql. JDBC. 5 a10411 JDBC4Connection @Copy the code

JDBC insert data

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class InsertDate {
    private static String url = "jdbc:mysql://localhost:3306/jsptest"; // Database address
    private static String userName = "root"; // Database user name
    private static String passWord = "123456"; // Database password
    private static Connection conn = null;
    public static Connection getConnection(a) {
        if (null == conn) {
            try {
                // Load the driver
                Class.forName("com.mysql.jdbc.Driver");
                // Create a database connection
                conn = DriverManager.getConnection(url, userName, passWord);
            } catch(Exception e) { e.printStackTrace(); }}return conn;
    }
    public static void main(String[] args) {
        / / to SQL
        String sql = "INSERT INTO user(user_name, user_password) VALUES (' xixin ', '123456')";
        Connection conn = getConnection();
        PreparedStatement pst = null;
        try {
            // Create a precompiled statement
            pst = conn.prepareStatement(sql);
            / / SQL execution
            pst.executeUpdate();
            System.out.println("JDBC inserted successfully");
            // Close the resource
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("JDBC insert failed"); }}}Copy the code

Running results:

JDBC Inserted successfullyCopy the code

JDBC update data

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class UpdateDate {
    private static String url = "jdbc:mysql://localhost:3306/jsptest"; // Database address
    private static String userName = "root"; // Database user name
    private static String passWord = "123456"; // Database password
    private static Connection conn = null;
    public static Connection getConnection(a) {
        if (null == conn) {
            try {
                // Load the driver
                Class.forName("com.mysql.jdbc.Driver");
                // Create a database connection
                conn = DriverManager.getConnection(url, userName, passWord);
            } catch(Exception e) { e.printStackTrace(); }}return conn;
    }
    public static void main(String[] args) {
        / / to SQL
        String sql = "Update user set user_name=' user_name ', user_password='654321' where user_id=1";
        Connection conn = getConnection();
        PreparedStatement pst = null;
        try {
            // Create a precompiled statement
            pst = conn.prepareStatement(sql);
            / / SQL execution
            pst.executeUpdate();
            System.out.println("JDBC update successful");
            // Close the resource
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("JDBC update failed"); }}}Copy the code

Running results:

JDBC update successfulCopy the code

JDBC query data

It is recommended to perform the increment again so that the database has two pieces of data for viewing the results.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectDate {
    private static String url = "jdbc:mysql://localhost:3306/jsptest"; // Database address
    private static String userName = "root"; // Database user name
    private static String passWord = "123456"; // Database password
    private static Connection conn = null;
    public static Connection getConnection(a) {
        if (null == conn) {
            try {
                // Load the driver
                Class.forName("com.mysql.jdbc.Driver");
                // Create a database connection
                conn = DriverManager.getConnection(url, userName, passWord);
            } catch(Exception e) { e.printStackTrace(); }}return conn;
    }
    public static void main(String[] args) {
        / / to SQL
        String sql = "select * from user";
        Connection conn = getConnection();
        PreparedStatement pst = null;
        try {
            // Create a precompiled statement
            pst = conn.prepareStatement(sql);
            / / SQL execution
            ResultSet rst = pst.executeQuery();
            while (rst.next()) {
                System.out.print(rst.getInt("user_id"));
                System.out.print(rst.getString("user_name"));
                System.out.println(rst.getString("user_password"));
            }
            System.out.println("JDBC query successful");
            // Close the resource
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("JDBC query failed"); }}}Copy the code

Running results:

1Xi moving654321
2Xi moving123456JDBC query successfulCopy the code

JDBC conditional query data

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectByIdDate {
    private static String url = "jdbc:mysql://localhost:3306/jsptest"; // Database address
    private static String userName = "root"; // Database user name
    private static String passWord = "123456"; // Database password
    private static Connection conn = null;
    public static Connection getConnection(a) {
        if (null == conn) {
            try {
                // Load the driver
                Class.forName("com.mysql.jdbc.Driver");
                // Create a database connection
                conn = DriverManager.getConnection(url, userName, passWord);
            } catch(Exception e) { e.printStackTrace(); }}return conn;
    }
    public static void main(String[] args) {
        / / to SQL
        String sql = "select * from user where user_id = 1";
        Connection conn = getConnection();
        PreparedStatement pst = null;
        try {
            // Create a precompiled statement
            pst = conn.prepareStatement(sql);
            / / SQL execution
            ResultSet rst = pst.executeQuery();
            while (rst.next()) {
                System.out.print(rst.getInt("user_id"));
                System.out.print(rst.getString("user_name"));
                System.out.println(rst.getString("user_password"));
            }
            System.out.println("JDBC conditional query succeeded");
            // Close the resource
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("JDBC conditional query failed"); }}}Copy the code

Running results:

1Cloud xi moving654321The JDBC condition query succeededCopy the code

JDBC delete data

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DeleteDate {
    private static String url = "jdbc:mysql://localhost:3306/jsptest"; // Database address
    private static String userName = "root"; // Database user name
    private static String passWord = "123456"; // Database password
    private static Connection conn = null;
    public static Connection getConnection(a) {
        if (null == conn) {
            try {
                // Load the driver
                Class.forName("com.mysql.jdbc.Driver");
                // Create a database connection
                conn = DriverManager.getConnection(url, userName, passWord);
            } catch(Exception e) { e.printStackTrace(); }}return conn;
    }
    public static void main(String[] args) {
        / / to SQL
        String sql = "delete from user where user_id = 2";
        Connection conn = getConnection();
        PreparedStatement pst = null;
        try {
            // Create a precompiled statement
            pst = conn.prepareStatement(sql);
            / / SQL execution
            pst.executeUpdate();
            System.out.println("JDBC deleted successfully");
            // Close the resource
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("JDBC deletion failed"); }}}Copy the code

Running results:

JDBC deleted successfullyCopy the code

JDBC tools

After the above learning, it will be found that add, delete, change and check have a lot in common, the difference is only the SQL statement and the way to receive the return value is different, so in order to make the program better reuse, we usually extract the same code. The following code demonstrates a new operation using the extracted utility class.

DBUtil tools

import java.sql.Connection;
import java.sql.DriverManager;
public class DBUtil {
    private static String url = "jdbc:mysql://localhost:3306/jsptest"; // Database address
    private static String userName = "root"; // Database user name
    private static String passWord = "123456"; // Database password
    private static Connection conn = null;
    public static Connection getConnection(a) {
        if (null == conn) {
            try {
                // Load the driver
                Class.forName("com.mysql.jdbc.Driver");
                // Create a database connection
                conn = DriverManager.getConnection(url, userName, passWord);
            } catch(Exception e) { e.printStackTrace(); }}return conn;
    }
    public static void main(String[] args) {
        // Test whether the database is connectedSystem.out.println(getConnection()); }}Copy the code

Change the new data to the following code:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class InsertDate {
    public static void main(String[] args) {
        / / to SQL
        String sql = "INSERT INTO user(user_name, user_password) VALUES ('3 ', '123456')";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        try {
            // Create a precompiled statement
            pst = conn.prepareStatement(sql);
            / / SQL execution
            pst.executeUpdate();
            System.out.println("JDBC inserted successfully");
            // Close the resource
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("JDBC insert failed"); }}}Copy the code

Similarly, add, delete, change and check can be changed in this way, making the code more concise, more logical and reusable.

JDBC introduction

Commonly used the JDBC API

JDBC Connecting to the database

Connecting to a Database

JDBC update data

JDBC conditional query data

JDBC tools

Related Posts