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

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

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 DATABASE easilyj;
Copy the code

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

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

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

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

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.