Thoroughly understand the process of running JDBC
A few days ago the author published a blog , handwritten mybatis thoroughly understand the principles of the framework . In order to help beginners better understand the mybatis framework, this time to explain the process of running Java’s JDBC.
JDBC role
JDBC full name is JavaDataBaseConnection, that is, Java Database Connection, we can use it to operate relational databases. jdbc interfaces and related classes in the java.sql package and javax.sql package. We can use it to connect to a database, execute SQL queries, stored procedures, and process the returned results.
The JDBC interface allows Java programs and JDBC drivers to be loosely coupled, making it easier to switch between different databases.
JDBC Connection Steps
To perform a JDBC connection, there are six steps:
1. Import packages
Include in your program the JDBC classes needed for database programming. In most cases, the use of importjava.sql.* is sufficient
2. Register the JDBC driver
The driver needs to be initialized so that communication with the database can be opened.
3. Open a connection
Use the DriverManager.getConnection() method to create a Connection object, which represents a physical connection to a database.
4. Execute a query
You need to use an object of type Statement or PreparedStatement (see later for the difference between the two) and submit a SQL statement to the database to execute the query.
5. Extracting data from the result set
In this step it is demonstrated how to get the data from the database for the query result. Use ResultSet.getXXX() method to retrieve the data results
6. Clean up the environment resources
After using JDBC to interact with the data to manipulate the data in the database, you should explicitly shut down all the database resources in order to minimize the waste of resources. This article uses the trywithresources approach to close resources, which is JDK7 syntactic sugar, readers can search for themselves.
The complete code is as follows.
JDBC Best Practices
How does JDBC enable loose coupling of Java programs and JDBC drivers?
JDBCAPI uses Java’s reflection mechanism to achieve loose coupling between Java programs and JDBC drivers. Take a look at the JDBC example above and you will see that all operations are done through the JDBC interface and the driver is only loaded when it is loaded through the Class.forName reflection mechanism.
This is one of the best practices of the reflection mechanism in the core Java libraries, which allows isolation between the application and the driver and makes migrating databases much easier.
Difference between Statement and PreparedStatement
Pre-compilation
Difference at Creation:
Difference at Execution:
As you can see from the above, PreparedStatement has the process of pre-compilation, which has already bound the sql, and after that, no matter how many times it is executed /p>
Placeholders
PreparedStatement can replace variables in SQL statements that can contain ? , which can be replaced with ? which can be replaced with variables.
And Statements can only be spliced with strings.
JDBC’s ResultSet
After querying a database, a ResultSet is returned, which is like a data table for the query result set.
The default ResultSet cannot be updated and the cursor can only move down. That means you can only traverse it from the first row to the last. However, it is possible to create a ResultSet that can be rolled back or updated, like the following.
The ResultSet object is also automatically closed when the Statement object that generated the ResultSet is closed or re-executed or when the next ResultSet is fetched.
Column data can be obtained by passing the column name or the ordinal number starting from 1 through the getter method of the ResultSet.
Different Types of ResultSet
Depending on the input parameters when creating the Statement, there will be different types of ResultSet corresponding to different types of ResultSet. if you look at the methods of Connection, you will see that the createStatement and prepareStatement methods are overloaded to support different ResultSet and concurrency types.
There are three types of ResultSet objects.
ResultSet has two concurrency types.
How to use JDBC to connect and operate the Oracle database
Previously when learning . Previously, the use of ODBC for connecting to the database, and in Java usually _ with JDBC to connect to the database, here to oracle database as an example of a simple summary of how to use JDBC to connect and operate the database.
1, connection
publicclassDbUtil{
publicstaticConnectiongetConnection(){
Connectionconn=null;
try{
Class.forName(“oracle.jdbc.driver.OracleDriver”);//find the class where the oracle driver is located
Stringurl=”jdbc:oracle:thin:@localhost:1521:bjpowernode”; //URL address
Stringusername=”drp”;
Stringpassword=”drp”;
conn=DriverManager.getConnection(url,username,password);
}catch(ClassNotFoundExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch( SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnconn;
}
< prename=”code” class=”java”>
publicstaticvoidclose(PreparedStatementpstmt){
if(pstmt!=null){
try{
pstmt. close();
}catch(SQLExceptione){
e.printStackTrace();
}
}
}
}
publicstaticvoidclose (ResultSetrs){
if(rs!=null){
try{
rs.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock<
e.printStackTrace();
}
}
}}
When you run Class.forName, you have to find the address where OracleDriver is located. oracle\proct\10.2.0\db_1\jdbc\lib to find ojdbc14.jar
2, and then find ojdbc14.jar\oracle\jdbc\driver following oracleeldriver so that you can find the driver file to use
2, Operation of the database – join
publicvoidaddUser(Useruser){
Stringsql=”insertintot_user(user_id,user_name,PASSWORD,CONTACT_TEL,EMAIL,CREATE _DATE)values(? ,?
,? ,? ,?
,?)” ;//? is a _number placeholder
Connectionconn=null;
PreparedStatementpstmt=null;//Often operated with PreparedStatement, performance is optimized
try{
conn= DbUtil.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,user.getUserId());
pstmt.setString(2 user.getUserName());
pstmt.setString(3,user.getPassword());
pstmt.setString(4,user.getContactTel());
pstmt. setString(5,user.getEmail());
//pstmt.setTimestamp(6,newTimestamp(System.currentTimeMillis()));
pstmt. newTimestamp(newDate().getTime()));//get the current system time
pstmt.executeUpdate();//run the add, delete, and change operation
}catch(SQLExceptione){
e. printStackTrace();
}finally{
DbUtil.close(conn);
DbUtil.close(pstmt);
}
}
3. Operations Database – Query
publicUserfindUserById(StringuserId){
Stringsql=”selectuser_id,user_name,password,contact_tel,email,create_ datefromt_userwhereuser_id=?” ;
Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;//define the result set that holds the query results
Useruser=null;
try{
conn=DbUtil.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,userId);
rs=pstmt. executeQuery();//run the query operation
if(rs.next()){
user=newUser();
user.setUserId(rs.getString(“user_Id”));
user. setUserName(rs.getString(“user_name”));
user.setPassword(rs.getString(“password”));
user.setContactTel(rs.getString(” contact_Tel”));
user.setEmail(rs.getString(“email”));
user.setCreateDate(rs.getTimestamp(“create_date”));
}
< p> }catch(SQLExceptione){
e.printStackTrace();
}finally{
//close in order
DbUtil.close(rs);
DbUtil.close( pstmt);
DbUtil.close(conn);
}
returnuser;
}
Four, summarize
1, PreparedStatement and Statement simple differences
Statement for a SQL statement to generate a runtime plan, assuming different values of _. Will generate a different sql statement, the number of times to run the corresponding _ value number. Assuming just one SQL statement to run, it is best _ to do this with Statement.
PreparedStatement reuses the run plan using bind variables. Different _values corresponding to the query statement. Only one sql statement will be generated. Greatly improves the efficiency of the run. Is precompiled. Improves efficiency when operating on large batches of statements, the same time can be _used ‘? ‘ to represent _number, can prevent SQL injection. Higher security.
2, with the previous knowledge of the connection
Eye exposure to the amount of JDBC in the connection object Connection this with the previous ODBC Connection role of the same, for the database connection object. PraparedStatement and Statement are similar to the command object in ODBC. They are used to run SQL statements. Query method used in the ResultSet is used before the DataSet or DataTable function is similar, so a connection, it seems that the connection and the use of the process has become much simpler!
How to connect to and manipulate an Oracle database using JDBC
Tags:–retoca performancetrycalproctexease