background
How does JDBC connect to the database?
Most people only know a few steps of JDBC, but they don’t really know how the underlying database is connected.
The interviewer is not asking too many questions, but if it is a beginner or intermediate level, it is.
But wouldn’t it be great if you knew the answer at an elementary or intermediate level?
Most people don’t know how to answer this question, but for a little bit of thinking, we can guess that today we are going to find out how JDBC is connected to the database at the bottom. No more guessing in the future.
On the other hand, if an interviewer asks you about JDBC, you can see how the underlying database is connected, which is likely to confuse many relatively inexperienced interviewers.
What is a JDBC?
JDBC (Java DataBase Connectivity) is a bridge between Java and databases, a “specification” rather than an implementation that can execute SQL statements. JDBC consists of a set of classes and interfaces written in the Java language. There are implementations for different types of databases. Note that the code in this article is implemented for the MySQL database.
The JDBC framework
It is divided into two-tier architecture and three-tier architecture.
double
What it does: In this architecture, a Java Applet or application accesses a data source directly.
Condition: The Driver is required to interact with the accessed database.
Mechanism: User commands are passed to a database or other data source and the results are returned.
Deployment: The data source can be on another machine and the user is connected over a network, called a C/S configuration (which can be an Intranet or the Internet).
Three layers
The side architecture is special in that it introduces mid-tier services.
Flow: Commands and structures pass through this layer.
Attractive: can increase enterprise data access control, as well as multiple types of updates; It also simplifies application deployment and, in most cases, provides performance benefits.
Historical trends: In the past, the middle layer was written in C or C++ because of performance problems. With the development of optimized compilers (which convert Java bytecode into efficient specific machine code) and technology such as EJB, Java began to be used for the development of the middle layer. With the use of Java as the server code language, JDBC has gained prominence.
An introduction to case
Here is a JDBC entry level example:
public class JdbcDemo {
public static final String URL = "jdbc:mysql://localhost:3306/mblog";
public static final String USER = "root";
public static final String PASSWORD = "123456";
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id, name, age FROM m_user where id =1");
while(rs.next()){
System.out.println("name: "+rs.getString("name") +" 年龄："+rs.getInt("age")); }}}Copy the codeJDBC steps
Database driver:
Class.forName("com.mysql.jdbc.Driver");
Copy the codeGet a connection:
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
Copy the codeCreate a Statement or PreparedStatement object:
Statement stmt = conn.createStatement();
Copy the codeExecute SQL database query:
ResultSet rs = stmt.executeQuery("SELECT id, name, age FROM m_user where id =1");
Copy the codeParsed result set:
System.out.println("name: "+rs.getString("name") +" 年龄："+rs.getInt("age"));
Copy the codeFinally, the shutdown of various resources.
Database driver
MySql driver class:
Class.forName("com.mysql.jdbc.Driver");
Copy the codeAfter we have installed the database, our application can not directly use the database, must be through the corresponding database driver, through the driver to deal with the database. In fact, it is the DATABASE vendor’s JDBC interface implementation, that is, the Connection interface implementation class JAR file.
Driver interface
Java.sql.Driver This interface is provided for implementation by database vendors. MySQL, for example, depends on the corresponding JAR package.
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.016.</version>
</dependency>
Copy the codeMySQL database implementation driver implementation class:
package com.mysql.cj.jdbc;
import java.sql.SQLException;
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
static {
try {
// Register the driver
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!"); }}public Driver(a) throws SQLException {}}Copy the codeDriverManager is a rt.jar class, (rt= Runtime), we need to register the driver class.
Method in the DriverManager class
public static synchronized void registerDriver(java.sql.Driver driver, DriverAction da)
throws SQLException {
/* Register the driver if it has not already been added to our list */
if(driver ! =null) {
registeredDrivers.addIfAbsent(new DriverInfo(driver, da));
} else {
// This is for compatibility with the original DriverManager
throw new NullPointerException();
}
println("registerDriver: " + driver);
}
Copy the codeLoad the Oracle driver accordingly:
Class.forName("oracle.jdbc.driver.OracleDriver");
Copy the codeSql Server driver:
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Copy the codeFor a link
Give us what looks like this line of code:
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
Copy the codeNow let’s take a closer look at this line of code. How does the underlying database connect?
The getConnection method takes three parameters: the link address, user name, and password.
public static Connection getConnection(String url, String user, String password) throws SQLException {
java.util.Properties info = new java.util.Properties();
if(user ! =null) {
info.put("user", user);
}
if(password ! =null) {
info.put("password", password);
}
return (getConnection(url, info, Reflection.getCallerClass()));
}
Copy the codeCreate a Properties object, which is a subclass of HashTable.
public class Properties extends Hashtable<Object.Object> {
/ /...
}
Copy the codeLook at the getConnection method:
// Worker method called by the public getConnection() methods.
private static Connection getConnection( String url, java.util.Properties info, Class
caller) throws SQLException { ClassLoader callerCL = caller ! =null ? caller.getClassLoader() : null;
SQLException reason = null;
// Iterate over the database driver registered with the valve
for(DriverInfo aDriver : registeredDrivers) {
try {
// Get the connection
Connection con = aDriver.driver.connect(url, info);
if(con ! =null) {
// Success!
println("getConnection returning " + aDriver.driver.getClass().getName());
return(con); }}catch (SQLException ex) {
if (reason == null) { reason = ex; }}}}Copy the codeThe key to this code is this line:
Connection con = aDriver.driver.connect(url, info);
Copy the codeThe connet() method is each database-driven implementation.
package com.mysql.cj.jdbc;
public class NonRegisteringDriver implements java.sql.Driver {
@Override
public java.sql.Connection connect(String url, Properties info) throws SQLException {
// Some non-essential code is omitted
// Here are the main points
ConnectionUrl conStr = ConnectionUrl.getConnectionUrlInstance(url, info);
switch (conStr.getType()) {
//SINGLE_CONNECTION("jdbc:mysql:", HostsCardinality.SINGLE), //
case SINGLE_CONNECTION:
return com.mysql.cj.jdbc.ConnectionImpl.getInstance(conStr.getMainHost());
case LOADBALANCE_CONNECTION:
return LoadBalancedConnectionProxy.createProxyInstance((LoadbalanceConnectionUrl) conStr);
case FAILOVER_CONNECTION:
return FailoverConnectionProxy.createProxyInstance(conStr);
case REPLICATION_CONNECTION:
return ReplicationConnectionProxy.createProxyInstance((ReplicationConnectionUrl) conStr);
default:
return null; }}}Copy the codeConnectionUrl from this class name should be able to guess that there is no real connection, just create a ConnectionUrl related information wrapper.
public abstract class ConnectionUrl implements DatabaseUrlContainer {
private static final String DEFAULT_HOST = "localhost";
private static final int DEFAULT_PORT = 3306;
/ /...
}
Copy the codeFamiliar figure, MySQL database default port. Let’s move on to the next important line of code:
ConnectionImpl.getInstance(conStr.getMainHost());
So this is getting an instance, and not surprisingly, this is where the connection is made. Continue to:
//ConnectionImpl
public static JdbcConnection getInstance(HostInfo hostInfo) throws SQLException {
return new ConnectionImpl(hostInfo);
}
Copy the codeThe ConnectionImpl constructor calls the createNewIO method:
@Override
public void createNewIO(boolean isForReconnect) {
synchronized (getConnectionMutex()) {
try {
if (!this.autoReconnect.getValue()) {
connectOneTryOnly(isForReconnect);
return;
}
connectWithRetries(isForReconnect);
} catch (SQLException ex) {
}
}
}
private void connectOneTryOnly(boolean isForReconnect) throws SQLException {
Exception connectionNotEstablishedBecause = null;
JdbcConnection c = getProxy();
// See the familiar connet method again,
this.session.connect(this.origHostInfo, this.user, this.password, this.database, DriverManager.getLoginTimeout() * 1000, c);
this.session.setQueryInterceptors(this.queryInterceptors);
}
Copy the codeSession is NativeSession.
public void connect(HostInfo hi, String user, String password, String database, int loginTimeout, TransactionEventHandler transactionManager)
throws IOException {
SocketConnection socketConnection = new NativeSocketConnection();
socketConnection.connect(this.hostInfo.getHost(), this.hostInfo.getPort(), this.propertySet, getExceptionInterceptor(), this.log, loginTimeout);
this.protocol.connect(user, password, database); this.protocol.getServerSession().setErrorMessageEncoding(this.protocol.getAuthenticationProvider().getEncodingForHandshake());
}
Copy the codeIn this method, we see that the Socket name starts with the class, ha ha, is used to communicate with Socket.
Highlights continue:
socketConnection.connect(this.hostInfo.getHost(), this.hostInfo.getPort(), ...) ;Copy the codeGo to the NativeSocketConnection class method:
//com.mysql.cj.protocol.a.NativeSocketConnection
@Override
public void connect(String hostName, int portNumber, PropertySet propSet, ExceptionInterceptor excInterceptor, Log log, int loginTimeout) {
this.mysqlSocket = this.socketFactory.connect(this.host, this.port, propSet, loginTimeout);
/ /...
}
Copy the codeSo the socketFactory here is the StandardSocketFactory. So we call StandardSocketFactory’s connect method:
//StandardSocketFactory
public <T extends Closeable> T connect(String hostname, int portNumber, PropertySet pset, int loginTimeout) throws IOException {
this.rawSocket = createSocket(pset);
this.rawSocket.connect(sockAddr, getRealTimeout(connectTimeout));
}
protected Socket createSocket(PropertySet props) {
return new Socket();
}
Copy the codeHere even if in the end, say plain JDBC bottom is to use ** “Socket” ** to connect to the database.
Commonly used method
To obtain the Statement
Three types of
To execute SQL statements, you must obtain an instance of java.sql.Statement, which can be of one of three types:
- Execute static SQL statements. This is usually implemented through the Statement instance.
- Execute dynamic SQL statements. This is typically implemented through a PreparedStatement instance.
- Execute database stored procedures. This is usually implemented through the CallableStatement instance.
Specific acquisition method
Statement stmt = con.createStatement() ;
PreparedStatement pstmt = con.prepareStatement(sql) ;
CallableStatement cstmt = con.prepareCall("{CALL demoSp(? , ?)}");Copy the codeThe similarities and differences between Statement and PreparedStatement as well as their advantages and disadvantages
Both are used to execute SQL statements
Different: A PreparedStatement is created based on an SQL Statement. It can set parameters and specify corresponding values instead of using string concatenation.
Advantages of preparedStatements:
1, its use of parameter Settings, readable, not easy to remember mistakes. String concatenation in statement results in poor readability and maintenance.
2. It has a precompilation mechanism and performs faster than Statement.
3, it can effectively prevent SQL injection attacks.
Execute vs. executeUpdate
Similarities: Both can add, delete, and modify operations.
Difference:
1. Execute the query statement and fetch the result from getResult. ExecuteUpdate cannot execute the query.
Execute Returns Boolean. True indicates that a query statement is executed. False indicates that an INSERT, delete, or update statement is executed. The return value from executeUpdate is int, indicating how many pieces of data were affected.
ResultSet ResultSet processing
The result set returned here in the previous introductory case is ResultSetImpl
ResultSetImpl class diagram
Commonly used methods of getting values
- GetString (int index) and getString(String columnName) : Obtain data objects of the types vARCHAR and CHAR in the database.
- GetFloat (int Index), getFloat(String columnName) : Get data objects that are of type Float in the database.
- GetDate (int index), getDate(String columnName) : Obtain data of the Date type in the database.
- GetBoolean (int index), getBoolean(String columnName) : Get data in the database that is of Boolean type.
- GetObject (int index), getObject(String columnName) : Obtains data of any type in the database.
Common method of getting rows
- Next () : Moves to the next line
- Previous() : Moves to the Previous row
- Absolute (int row) : Moves to a specified row
- BeforeFirst () : Moves the first of a resultSet.
- AfterLast () : Moves to the end of the resultSet.
Common data type conversion
So that’s the result set, and that’s it.
Resources to shut down
Resource closing is not a part of the business code, mainly for some resources to close, so as not to hold resources all the time. In addition, resource closures that we handle are usually handled in finally.
conclusion
This paper mainly talks about the following contents:
- What is JDBC?
- How are database driven loads and registrations handled?
- The highlight is what we call JDBC connecting to a database. How do you connect to a database at the bottom?
- Common methods of result set processing