catalogue
2.1 Driver interface implementation class
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