Java JDBC: 2. Get database connection

1. Element 1: Driver interface implementation class

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.
- Oracle driver: Oracle jdbc. driver. OracleDriver
- mySql driver: com mySql. jdbc. Driver

Copy the above jar package to a directory of the Java project. It is customary to create a new lib folder.

Right click the driver jar – > build path – > add to build path

Note: if it is a Dynamic Web Project, just put the driver jar in the lib directory in the WEB-INF directory in the WebContent (some development tools are called WebRoot) directory

1.2. Loading and registering 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
- Usually, it is not necessary to explicitly call the registerDriver() method of the DriverManager class to register the instance of the Driver class, because the Driver classes of the Driver interface contain static code blocks in which DriverManager. Com will be called The registerDriver() method to register an instance of itself. The following figure shows the source code of the Driver implementation class of MySQL:

2. Element 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
give an example:

JDBC URL s of several common databases
- MySQL connection URL writing method:
  - jdbc:mysql: / / host name: mysql service port number / database name? Parameter = value & parameter = value
  - jdbc:mysql://localhost:3306/atguigu
  - jdbc: mysql://localhost:3306/atguigu **? Useunicode = true & characterencoding = utf8 * * (if the JDBC program is inconsistent with the server-side character set, it will cause garbled code, then you can specify the server-side character set through parameters)
  - jdbc:mysql://localhost:3306/atguigu?user=root&password=123456
- How to write the connection URL of Oracle 9i:
  - jdbc:oracle:thin: @ host name: oracle service port number: database name
  - jdbc:oracle:thin:@localhost:1521:atguigu
- SQL Server connection URL is written as follows:
  - jdbc:sqlserver: / / host name: sqlserver service port number: DatabaseName = database name
  - jdbc:sqlserver://localhost:1433:DatabaseName=atguigu

3. Element 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

4. Example of database connection mode

4.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", "abc123");

            //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

4.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", "abc123");

            //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.

4.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 = "abc123";
            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.

4.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 = "abc123";
            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.

4.5 connection mode V (final version)

	@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 configuration file is declared in the src directory of the project: [jdbc.properties]

user=root
password=abc123
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.

Keywords: Java JDBC

Added by Med on Sat, 15 Jan 2022 14:14:26 +0200

Programming VIP