Connecting to MySQL database using JDBC

catalogue

1. JDBC overview

1.1 JDBC introduction

1.2 JDBC architecture

1.3 JDBC programming steps

2. Connecting elements

2.1 Driver interface implementation class

        2.2 URL

2.3 user name and password

3. Database connection mode

3.1 connection mode I

3.2 connection mode II

3.3 connection mode III

3.4 connection mode IV

3.5. Connection mode V (recommended)

1. JDBC overview

1.1 JDBC introduction

JDBC(Java Database Connectivity) is a common interface (a set of API s) independent of a specific database management system and general SQL database access and operation. It defines the standard Java class libraries used to access the database (java.sql,javax.sql). These class libraries can be used to access database resources in a standard and convenient way.

JDBC provides a unified way to access different databases and shields some details for developers.

The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides JDBC drivers, so that programmers do not need to know too much about the characteristics of a specific database system, which greatly simplifies and speeds up the development process.

If there is no JDBC, the Java program accesses the database as follows:

With JDBC, the Java program accesses the database as follows:

1.2 JDBC architecture

JDBC interface (API) includes two levels:

Application oriented API: Java API, abstract interface, for application developers to use (connect to the database, execute SQL statements, and obtain results)

Data oriented API: Java Driver API for developers to develop database drivers

JDBC is a set of interfaces provided by sun company for database operation. java programmers only need to program for this set of interfaces. Different database vendors need to provide different implementations for this set of interfaces. The collection of different implementations, that is, the drivers of different databases -- interface oriented programming

1.3 JDBC programming steps

Supplement: ODBC(Open Database Connectivity) is launched by Microsoft under the Windows platform. The user only needs to call ODBC API in the program, and the ODBC driver converts the call into a call request to a specific database.

2. Connecting elements

2.1 Driver interface implementation class

                2.1. 1. Introduction to driver interface

                java. sql. The driver interface is the interface that all JDBC drivers need to implement. This interface is provided for database manufacturers. Different database manufacturers provide different implementations.

In the program, you do not need to directly access the classes that implement the Driver interface, but the Driver manager class (java.sql.DriverManager) calls these Driver implementations.
MySQL driver: com mysql. jdbc. Driver
Attach download address: Password: 1234

Steps for importing MySQL driver from idea:

                2.1. 2 load and register JDBC Driver

Load driver: to load the JDBC driver, you need to call the static method forName() of Class class and pass the Class name of the JDBC driver to be loaded

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

Register driver: DriverManager class is the driver manager class, which is responsible for managing drivers

Using drivermanager Register driver (COM. Mysql. JDBC. Driver) to register the driver

Generally, it is not necessary to explicitly call the registerDriver() method of the DriverManager class to register an instance of the Driver class, because the Driver class of the Driver interface contains a static code block in which DriverManager. Is called The registerDriver() method to register an instance of itself.

        2.2 URL

JDBC URL is used to identify a registered driver. The driver manager selects the correct driver through this URL to establish a connection to the database.

The standard JDBC URL consists of three parts separated by colons.  
jdbc: sub protocol: sub name
Protocol: the protocol in the JDBC URL is always jdbc
Sub protocol: the sub protocol is used to identify a database driver
Subname: a method of identifying a database. The sub name can be changed according to different sub protocols. The purpose of using the sub name is to provide sufficient information for locating the database. Including host name (corresponding to the ip address of the server), port number and database name

For example:

MySQL connection URL writing method:

jdbc:mysql: / / host name: mysql service port number / database name? Parameter = value & parameter = value
                        jdbc:mysql://localhost:3306/test
                        jdbc: mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8 (if the JDBC program is inconsistent with the character set on the server side, which will cause garbled code, you can specify the character set on the server side through parameters)
                        jdbc:mysql://localhost:3306/test?user=root&password=123456

2.3 user name and password

User and password can tell the database by "attribute name = attribute value"
You can call the getConnection() method of the DriverManager class to establish a connection to the database

3. Database connection mode

3.1 connection mode I

    @Test
    public void testConnection1() {
        try {
            //1. Provide Java sql. The driver interface implements the object of the class
            Driver driver = null;
            driver = new com.mysql.jdbc.Driver();

            //2. Provide a url to indicate the specific operation data
            String url = "jdbc:mysql://localhost:3306/test";

            //3. Provide the object of Properties, indicating the user name and password
            Properties info = new Properties();
            info.setProperty("user", "root");
            info.setProperty("password", "123456");

            //4. Call the connect() of the driver to get the connection
            Connection conn = driver.connect(url, info);
            System.out.println(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Note: the API of the third-party database appears explicitly in the above code

3.2 connection mode II

@Test
    public void testConnection2() {
        try {
            //1. Instantiate Driver
            String className = "com.mysql.jdbc.Driver";
            Class clazz = Class.forName(className);
            Driver driver = (Driver) clazz.newInstance();

            //2. Provide a url to indicate the specific operation data
            String url = "jdbc:mysql://localhost:3306/test";

            //3. Provide the object of Properties, indicating the user name and password
            Properties info = new Properties();
            info.setProperty("user", "root");
            info.setProperty("password", "123456");

            //4. Call the connect() of the driver to get the connection
            Connection conn = driver.connect(url, info);
            System.out.println(conn);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Note: compared with method 1, the reflection instantiation Driver is used here, which does not reflect the API of the third-party database in the code. It embodies the idea of interface oriented programming.

3.3 connection mode III

@Test
    public void testConnection3() {
        try {
            //1. Four basic elements of database connection:
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "123456";
            String driverName = "com.mysql.jdbc.Driver";

            //2. Instantiate Driver
            Class clazz = Class.forName(driverName);
            Driver driver = (Driver) clazz.newInstance();
            //3. Register driver
            DriverManager.registerDriver(driver);
            //4. Get connection
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

Description: use DriverManager to connect to the database. Experience the four basic elements necessary to obtain connections.

3.4 connection mode IV

@Test
    public void testConnection4() {
        try {
            //1. Four basic elements of database connection:
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "123456";
            String driverName = "com.mysql.jdbc.Driver";

            //2. Load Driver (① instantiate Driver ② register Driver)
            Class.forName(driverName);


            //Driver driver = (Driver) clazz.newInstance();
            //3. Register driver
            //DriverManager.registerDriver(driver);
            /*
            The reason why the above code can be commented out is that the Driver class of mysql declares:
            static {
                try {
                    DriverManager.registerDriver(new Driver());
                } catch (SQLException var1) {
                    throw new RuntimeException("Can't register driver!");
                }
            }

             */


            //3. Get connection
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

Note: there is no need to explicitly register the driver. Because the static code block already exists in the source code of DriverManager, the driver registration is realized.

3.5. Connection mode V (recommended)

Create a new configuration file in the src directory of the project: JDBC properties

user=root
password=123455
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

Description: save the configuration information in the form of configuration file, and load the configuration file in the code

Benefits of using profiles:

① it realizes the separation of code and data. If you need to modify the configuration information, you can modify it directly in the configuration file without going deep into the code
② if the configuration information is modified, the recompilation process is omitted.

@Test
    public  void testConnection5() throws Exception {
    	//1. Load configuration file
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        
        //2. Read configuration information
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //3. Load drive
        Class.forName(driverClass);

        //4. Get connection
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

    }

The following results are output to indicate that the connection is successful

Keywords: Java MySQL

Added by MrBillybob on Tue, 14 Dec 2021 05:11:20 +0200