1. First explain what JDBC is
Before I saw this noun at first glance, I felt it was necessary to know what its full English name was. Java Database Connectivity, which is literally translated into a connectable Java database. To put it bluntly, I want to provide a set of intermediate links to connect Java code and database so that it can control the database directly by operating Java code.
2. The essence of JDBC
It is a Java solution to access databases. It hopes to access different databases in the same way to achieve a Java operation interface that is not related to the specific database.
JDBC is essentially a set of standardized interfaces. Different database manufacturers implement this interface according to their own database characteristics, and we don’t need to care about the specific methods of implementation.
The main interfaces implemented by database manufacturers in JDBC are as follows:
DriverManager: Driver Management
Connection,
DatabaseMetaData: Connect to the interface
Statement,
PreparedStatement
CallableStatement: Statement object interface
ResultSet,
ResultSetMetaData: ResultSet Interface
3. How JDBC works
The programmer calls the part implemented by the underlying database manufacturer.
That is: 1) Implement the first step of connection through the Connection interface
2) Transfer SQL statements through Statement results
3) The main process of saving the database return results to the ResultSet result set interface can be summarized as:
1) Load the driver and create the connection
2) Create a statement object
3) Execute SQL statements
4) Send back the result set
5) Close the connection
Below I will take the oracle database I learned and cooperate with eclips as an example to explain how to implement JDBC
1) Loading the driver
The method used is: Class.forName ("driver");
What I know about how to get this driver is: expand the JDBC jar package, if I use ojdbc6.jar, find oracle.jdbc.driver, find OracleDriver, then right-click qualitycopy, and paste it in quotes, such as: Class.forName("oracle.jdbc.driver.OracleDriver");
2) Create a connection
The method used is: conn=DriverManager.getConnection("jdbc:oracle:thin:@IP address: 1521:orcl", "database account", "database password");
For example: conn=DriverManager.getConnection("jdbc:oracle:thin:@172.16.3.8:1521:orcl","jsd1601","jsd1601");
3) Create a statement object
The method used is: Statement stmt=conn.createStatement();
It should be noted that the connection created in the second step is used to call the method
Call the executeUpdate method and pass the sql statement into it to execute the written sql statement. It should be noted here that the types of sql that the executeUpdate method can execute are insert, update, delete
For example: Statement stmt=conn.createStatement();
String sql="insert into emp_jiawenzhe(empno,ename,sal,deptno) values(1000,'Jia Wenzhe',1500,10)";
int i=stmt.executeUpdate(sql);
The return value i here is the number of affected rows. We can judge whether the operation is successful based on the number of affected rows.
4) The returned result set mainly refers to the selection operation (not mentioned here)
5) Finally close the connection
For example: conn.close();
List a whole code, including comments:
package jdbc_day01;import java.sql.*;/** * Demonstrate the operation steps of JDBC* 1. Load the driver* 2. Create the connection* 3. Create statement object* 4. Send SQL statement* 5. If the sending a select statement, process the result set* 6. Close the connection* @author jiawenzhe * */public class JDBCDome01 { public static void main(String[] args) throws SQLException { //Requirements: Create an employee, employee number, employee name, salary, department number//1, Connection conn=null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); System.out.println("driver loading successfully"); //2. conn= DriverManager.getConnection("jdbc:oracle:thin:@172.16.3.8:1521:orcl","jsd1601","jsd1601"); System.out.println(conn.getClass().getName()); //3. //Statment statement object. Send and execute SQL statement/* * int exhaustUpdate(String sql); * Send insert, update, delete statement* Return value int represents the number of rows affecting the database table*/ Statement stmt=conn.createStatement(); String sql="insert into emp_jiawenzhe(empno,ename,sal,deptno) " + "values(1000,'Wang Xiaoer',1500,10)"; int i=stmt.executeUpdate(sql); if(i>0){ System.out.println("Save successfully!"); } } catch (ClassNotFoundException e) { e.printStackTrace(); //1. Record the log//2. Notify the caller to throw new RuntimeException("Load driver error",e); } finally{ //Close the connection if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } } }} Note: The jar package that can be used in the oracle database: ojdbc14.jar/ojdbc6.jar (oracle12c uses this)
The jar package that can be used by mysql database: mysql-connector-java-5.0.4-bin.jar
Loading driver class:
Class.forName("com.mysql.jdbc.Driver"); 1. Introduction of packaging method
What touched me the most is that when I am writing a certain series of codes, if there is code reuse, I will definitely choose a method to encapsulate the code to a certain extent, from encapsulating a method to encapsulating a class. The previously mentioned use of JDBC for database operations (addition, deletion, and modification, because the query involves the operation of the result set, and declared separately) is divided into four processes:
1) Loading the driver
2) Create a connection
3) Create statement object and send SQL
4) Close the connection
In other words, when we perform any database operation, we have to perform the above steps, which leads to the redundancy of the code. So we proposed to encapsulate these common steps into a class so that it can become a tool class for my use.
2. Three versions of encapsulation
Version 1
When I was learning, I gradually encapsulated these classes in a gradual manner and improved them gradually, because if the ultimate version of the encapsulation is directly proposed, it is difficult for beginners to accept. I will explain the first, that is, the simplest method of encapsulation:
You will find out earlier that no matter how you want to operate the database, loading the driver is essential, and the loading driver mainly includes declarations, driver name, IP address, port number, database account name, password, etc., and the essence of these is strings, so I define these strings separately, as shown below:
private static String driverclass="oracle.jdbc.driver.OracleDriver"; private static String url="jdbc:oracle:thin:losthost:1521:orcl"; private static String user="system"; private static String password="123";
In this way, when creating the connection, I can directly get the name of the variable to replace the long string. When loading the driver, the method Class.forName is declared in a static block because while loading the data, the driver is loaded.
As shown below:
static{ try { Class.forName(driverclass); } catch (ClassNotFoundException e) { e.printStackTrace(); throw new RuntimeException("Load Driver Error",e); } }For creating a connection, we call the getConnection method of DriverManager and put the corresponding username and password into it. We put this method directly into the method I defined, and then directly call my method to create the connection. It should be noted that the return value of the method is the Connection object, which is easy to understand, because we want to get this connection type object, as shown below:
public static Connection getConnection() throws SQLException{ Connection conn= DriverManager.getConnection(url,user,password); return conn; }Then create a statement object and send SQL. Of course, SQL is the only special place here, because what SQL needs to do is very different, so this step does not need to be encapsulated.
Finally, close the connection, which is called the close() method, as shown below
public static void close(Connection conn){ if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("Close connection error",e); } } } }The overall first version of the package is as follows:
package jbbc_day01;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;/** * Used to manage connections* @author jiawenzhe * */public class DBUtil { private static String driverclass="oracle.jdbc.driver.OracleDriver"; private static String url="jdbc:oracle:thin:losthost:1521:orcl"; private static String user="system"; private static String password="123"; //1. Loading driver static{ try { Class.forName(driverclass); } catch (ClassNotFoundException e) { e.printStackTrace(); throw new RuntimeException("Loading driver error",e); } } } //2. Create a connection/* * How to define a method that can create a connection* Return value type: Whether there is an operation result, if there is, the type of the result is the return value type* Parameter list: * Whether there is uncertain data in the method function participates in the operation, if there is, it is a parameter*/ public static Connection getConnection() throws SQLException{ Connection conn= DriverManager.getConnection(url,user,password); return conn; } //3. Close the connection public static void close(Connection conn){ if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("Close connection error",e); } } } } }Just encapsulate this way, and then call the encapsulation class directly when performing SQL operations. All you need to write is to create your statement object and send your SQL statement
Version 2
In fact, version 2 is very similar to version 1, in order to make up for one of the shortcomings of version 1, that is, when you change the database, the account passwords of different databases are different, so you need to modify the account password and IP address. I have previously encapsulated these in the tool class in the form of a string, which means that we must modify the tool class every time we change the database, which is very inappropriate. So we proposed an improvement method to put these connection data into a configuration file, and the tool class reads this configuration file. We can directly modify the configuration file when modifying it.
Before introducing this version, I introduced a new class, Properties, which is a file that can read and read the configuration file content in the form of a stream and then return it to the tool class.
First, I will give an example of the configuration file. In fact, it doesn't need to be explained. It's clear at a glance, as shown below:
jdbc.driverclass=oracle.jdbc.driver.OracleDriverjdbc.url=jdbc:oracle:thin:localhost:orcljdbc.user=systemjdbc.password=123
The front is the key, the following is the value, and the previous key value is defined by ourselves, just like defining the variable name, the subsequent value is the actual situation of our database. What we need to pay special attention here is that the suffix name of this configuration file must end with .properties, because in this way, the Properties class can be read.
Examples of this section are as follows:
static{ try { //Load property file data Properties pop=new Properties(); pop.load(DBUtil2.class.getClassLoader().getResourceAsStream("db.properties")); url=pop.getProperty("jdbc.url"); driverclass=pop.getProperty("jdbc.driverclass"); user=pop.getProperty("jdbc.user"); password=pop.getProperty("jdbc.password"); Class.forName(driverclass); } catch (ClassNotFoundException e) { e.printStackTrace(); throw new RuntimeException("Load Driver Error",e); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }The method of load() is to read it or it can be considered to be loading the configuration file. Just remember this whole sentence without delving into its meaning. GetProperty() obtains the corresponding value through the key, which is very similar to the form of obtaining a value for the key-value pair set.
The overall code is as follows:
package jbbc_day01;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;/** * Used to manage connections* Database connection information, saved in properties file* @author jiawenzhe * */public class DBUtil2 { private static String driverclass; private static String url; private static String user; private static String password; //1. Load driver static{ try { //Load property file data Properties pop=new Properties(); pop.load(DBUtil2.class.getClassLoader().getResourceAsStream("db.properties")); url=pop.getProperty("jdbc.url"); driverclass=pop.getProperty("jdbc.driverclass"); user=pop.getProperty("jdbc.user"); password=pop.getProperty("jdbc.password"); Class.forName(driverclass); } catch (ClassNotFoundException e) { e.printStackTrace(); throw new RuntimeException("Load Driver Error",e); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //2. Create a connection/* * How to define a method that can create a connection* Return value type: Whether there is an operation result, if there is, the type of the result is the return value type* Parameter list: * Whether there is uncertain data in the method function participates in the operation, if there is, it is a parameter*/ public static Connection getConnection() throws SQLException{ Connection conn= DriverManager.getConnection(url,user,password); return conn; } //3. Close the connection public static void close(Connection conn){ if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("Close connection error",e); } } } } }