MySQL JDBC programming

    We learned about mysql Command line programming for , Now learn about using java Language to connect the database for programming , This method is the mainstream method in actual development . Use here mysql5 Version of . Then encapsulate the code .

    Some prerequisites for database programming , Like programming languages , What language to develop , Here it is java. Database usage mysql. Database driver package is also required between the two . Different databases , Different database driver packages are provided for different programming languages ,MySQL Provides Java Driver package mysql-connector-java, Need to be based on Java operation MySQL That is, you need the driver package . This database driver package needs to go mysql Download from the official website . Different versions correspond to different packets .
mysql5.1.47 Database driver package https://pan.baidu.com/s/1-en3mb1NuWLpmQU0hbk0SQ?pwd=1111 Extraction code ：1111.

    Java Database programming JDBC,java Database connection . It's one for execution SQL Of the statement Java API, It is Java Database connection specification in . This API from java.sql.*,javax.sql.* Some classes and interfaces in the package , It's for Java A standard way for developers to manipulate databases is provided API, It can provide unified access for a variety of relational databases .

Use JDBC With his advantages ：

（1） Operation database is completely oriented to abstract interface programming ;

（2） The development of database applications is not limited to the specific database manufacturers API;

（3） The portability of the program is greatly enhanced .

Jar Package import

    stay idea Create a project , After project creation , Create a... In the project root directory lib Folder , Download the jar Package is copied to the lib Under the folder . Then right-click jar package , find add as library Then click , Click the pop-up box OK that will do .

Click when finished jar package , You'll see jar Interfaces and methods in the package .

Connect to the database and release resources

    Before connecting , We first in mysql Create a table , such as student surface , This table only contains id int,name varcahr(20).

create table student
(
    id int,
    name varchar(20)
);

    Next, connect to the database . Create a java After the file, in main Method . To operate the database , You need to connect to the database server first , Describe the location of the cleanup server . stay JDBC Inside , Connect to database , Then use DataSource This class describes the location of the database .

    public static void main(String[] args) {
        DataSource dataSource = new MysqlDataSource();
    }

    DataSource It's an interface , Cannot instantiate directly , Use MysqlDataSource Implemented this interface . This process is upward transformation . This class is from the driver package , The package is imported correctly , To display properly . To connect to the database , Need to know the database server url, User and password . Yes mysql Come on ,url The format is as follows ：

//MySQL Data connection URL The parameter format is as follows ：
jdbc:mysql:// Server address : port / Database name ? Parameter name = Parameter values 

    public static void main(String[] args) throws SQLException {
        //1、 To operate the database , First connect , Describe the server location 
        // Use dataSource  describe MySQL Location of the server 
        //DataSource It's an interface , Cannot instantiate directly 
        // This interface comes from the driver package 
        // Upward transformation 
        DataSource dataSource = new MysqlDataSource();

        // Describe the server location  -- url
        //jdbc:mysql://  The name of the agreement   This web site jdbc Connect mysql Use 
        //127.0.0.1 IP Address , Describe the host location on the Internet , Use a string to describe 
        //3306  Port number   Distinguish which program on the current host 
        //blog  Database name 
        //? The following parameters set the character set used by the client to connect to the server 
        //characterEncoding  Character set 
        //useSSL  Is it encrypted 
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/blog?characterEncoding=utf8&useSSL=false");
        
        // User name and password 
        ((MysqlDataSource) dataSource).setUser(" Your username ");
        ((MysqlDataSource) dataSource).setPassword(" Your password ");

        //2、 Establish a connection with the database server 
        // Use getConnection Establishing a connection , Use java.sql Of 
        //SQLException  Common exceptions in database programming , By checked exception 
        // When the connection fails , It throws an exception 
        Connection connection = dataSource.getConnection();

        // A result similar to this [email protected] -->  Successful connection 
        //access denied  Description user name / Wrong password 
        System.out.println(connection);
        
        //3、 structure sql sentence 
        
        //4、 disconnect , Release resources 
        // Those created later should be disconnected first 
        connection.close();
    }

    1、 Describe the server location . In this process, we need to make an upward transformation first , Then transition down . To do so , The data source obtained is DataSource type , It's a generic type , It can refer to any database . If the database needs to be replaced later , Only a small change is needed . Be careful url Be sure to input carefully , Or directly copy and paste and modify the location of your own database .

    2、 Establish a connection with the database server . In network communication , There are two ways to connect ：“ There is a connection ” and “ There is no connection ”. The database is connected , The advantage of this is to check whether the communication link is unblocked , But remember that different connections must be released in time .

There are two ways to connect databases ：

（1） adopt DriverManager（ Driver management ） Static method to get

// load JDBC The driver

Class.forName("com.mysql.jdbc.Driver");

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

（2） adopt DataSource（ data source ） Object acquisition . It will be used in practical application DataSource object

DataSource ds = new MysqlDataSource(); ((MysqlDataSource) ds).setUrl("jdbc:mysql://localhost:3306/test"); ((MysqlDataSource) ds).setUser("root"); ((MysqlDataSource) ds).setPassword("root"); Connection connection = ds.getConnection();

    DriverManager Class to get Connection Connect , Can't be reused , Release resources after each use when , adopt connection.close() Is to close the physical connection .

    DataSource Provide connection pool support . When the connection pool is initialized, a certain number of database connections will be created , These connections It can be reused , Every time you use the database connection , Release resource call connection.close() Are all the Conncetion Connection object recycling . This is the most common way .

    Use dataSource Of getConnection Method connection , Be sure to import the correct package , come from java.sql. meanwhile alt+enter Throw an exception .

3、 structure sql sentence , This will be explained later .

4、 disconnect , Release resources . Use close() Method .

structure sql sentence

Above sql Statement performs an insert operation . Be careful not to insert repeatedly （1, Zhang San ）, It comes with a primary key .

In order to sql Statements are sent to the database ,JDBC Three objects are provided ：

（1）Statement, Execute simple without parameters sql sentence ;

（2）PreparedStatement, Execute with or without parameters sql sentence .sql Statements are automatically precompiled in the database system , Faster than statement object , The most commonly used .

（3）CallableStatement, Execute the call of database stored procedure .

If we want to input in the console , So in the picture sql The writing is not very good . Placeholders need to be used in actual development ？.

    public static void main(String[] args) throws SQLException {

        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/blog?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        Scanner scanner = new Scanner(System.in);

        System.out.println(" Please enter your student number ");
        int id = scanner.nextInt();
        System.out.println(" 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);

        long num = statement.executeLargeUpdate();
        System.out.println(num);
        
        statement.close();
        connection.close();
    }

Addition, deletion and modification are applicable to this grammar and format .

    executeUpdate() The return value of the method is an integer , Indicates the number of lines affected , Usually used for update、insert、delete sentence .

The syntax and format of query are different from that of addition, deletion and modification .

    The query method is executeQuery() Method returns the of a single result set after execution , Usually used for select sentence , Its result does not return a value , It is ResultSet object .

    ResultSet Objects are called result sets , Represents compliance with SQL All lines of the statement condition , And through a set getXXX Method provides access to the data in these rows . ResultSet The data in the are arranged row by row , Each row has multiple fields , And there is a record pointer , The data row indicated by the pointer is called the current data row , We can only manipulate the current data row . If we want to get a record , Then use ResultSet Of next() Method , If we want to get ResultSet All the records in the , You should use while loop .

JDBC encapsulation

Create a new one Jdbc package , Create a new class as shown in the following figure . The structure of the whole package is shown in the figure .

The watch used is student(id int, name varchar(20)).

Util class

One connection is enough , So turn this class into a static class .

package Jdbc;

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

import javax.sql.DataSource;
import java.sql.*;

/**
 * @author: Naion
 * @create: 2022-07-18 11:11
 * @Description:
 **/

// This class is essentially a static class , There is no need to instantiate 
public class Util {

    // Static and immutable 
    private static final DataSource dataSource = new MysqlDataSource();

    // Static code block , There is no need to connect more than once 
    static {
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/blog?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser(" Your username ");
        ((MysqlDataSource) dataSource).setPassword(" Your password ");
    }

    public static void main(String[] args) {
        System.out.println(getConnection());
    }

    // Database connection 
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(" Failed to get database connection ", e);
        }
    }

    // Release resources 
    public static void close(Statement statement, Connection connection, ResultSet resultSet) {
        try {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(" Database operation exception ", e);
        }
    }
}

Insert class

package Jdbc;

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

/**
 * @author: Naion
 * @create: 2022-07-18 11:23
 * @Description:
 **/

public class Insert {

    public void insert() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = Util.getConnection();
            Scanner scanner = new Scanner(System.in);
            System.out.println(" Please enter the new id");
            int id = scanner.nextInt();
            System.out.println(" Please enter the new name");
            String name = scanner.next();
            String sql = "insert into student values(?, ?)";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, name);

            int result = preparedStatement.executeUpdate();
            System.out.println(" Returns the number of entries : " + result);
            
        } catch (SQLException e) {
            throw new RuntimeException(" Failed to get database connection ", e);
        } finally {
            Util.close(preparedStatement, connection, null);
        }
    }
}

Delete class

package Jdbc;

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

/**
 * @author: Naion
 * @create: 2022-07-18 15:16
 * @Description:
 **/

public class Delete {

    public void delete() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = Util.getConnection();
            Scanner scanner = new Scanner(System.in);
            System.out.println(" Please enter the to delete id");
            int id = scanner.nextInt();
            String sql = "delete from student where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            int result = preparedStatement.executeUpdate();
            System.out.println(" Returns the number of entries : " + result);

        } catch (SQLException e) {
            throw new RuntimeException(" Failed to get database connection ", e);
        } finally {
            Util.close(preparedStatement, connection, null);
        }
    }
}

Update class

package Jdbc;

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

/**
 * @author: Naion
 * @create: 2022-07-18 11:52
 * @Description:
 **/

public class Update {

    public void update() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = Util.getConnection();
            Scanner scanner = new Scanner(System.in);
            System.out.println(" Please enter the... To be modified id");
            int id = scanner.nextInt();
            System.out.println(" Please enter the modified name");
            String name = scanner.next();
            String sql = "update student set name = ? where id = ?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, id);

            int result = preparedStatement.executeUpdate();
            System.out.println(" Returns the number of entries : " + result);

        } catch (SQLException e) {
            throw new RuntimeException(" Failed to get database connection ", e);
        } finally {
            Util.close(preparedStatement, connection, null);
        }
    }
}

Select class

package Jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author: Naion
 * @create: 2022-07-18 12:04
 * @Description:
 **/

public class Select {

    public void select() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = Util.getConnection();
            String sql = "select * from student";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("id: " + id + " name: " + name);
            }

        } catch (SQLException e) {
            throw new RuntimeException(" Failed to get database connection ", e);
        } finally {
            Util.close(preparedStatement, connection, resultSet);
        }
    }
}

Jdbc class

package Jdbc;

/**
 * @author: Naion
 * @create: 2022-07-18 11:57
 * @Description:
 **/

public class Jdbc {

    public void insert() {
        new Insert().insert();
    }

    public void update() {
        new Update().update();
    }

    public void select() {
        new Select().select();
    }

    public void delete() {
        new Delete().delete();
    }
}

    Instantiate directly when using Jdbc Class to use . This package is simple , The table used is also a relatively simple table . If you need more complex functions, you can modify and supplement them yourself . Encapsulating functions can also be regarded as a small project , The whole process , The difficulty lies in how to write a framework , Once the framework is written , What we need to do is to supplement and improve .