Complete single-table curd operations and encapsulation of jdbcutils with JDBC

Overview: JDBC is a set of specifications (a set of interfaces) developed by Oracle, which is a JDBC implementation class that is provided by the database vendor. So we can use a set of specifications to implement different database operations (polymorphism)

The role of JDBC: Connecting to a database, sending SQL statements, processing results

Curd action: It represents create, update, read (Retrieve), and delete operations

JDBC Operation steps:

1. Databases and tables

Here, use the Student table under the exercise database.

2. Create a project

Because it was created under the javase version of Ecplise, the Lib folder was created manually to hold the imported drive jar package.

3. Import Drive Jar Package

Remember to right-click the jar package and click Build Path.

4. Writing code

First, in a hard-coded manner, note that all classes that need to be guided are java.sql packages, and the following are code and comments:

@Test Public voidJDBC1 ()throwsClassNotFoundException, sqlexception{//There are 3 ways to load a class into memory//Registered driver Class.forName ("Com.mysql.jdbc.Driver");                  //Get ConnectionsConnection conn =Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/exercise", "root", "123456"); //Writing SQLString sql = "SELECT * FROM Student"; //Create pre-compiled statement performers        PreparedStatement st = conn.preparestatement (SQL); //Execute SQL, here is the R operation (read operation)ResultSet rs =St.executequery (); //processing the result, the Rs.next initially points to the first one.          while(Rs.next ()) {System.out.println (rs.getstring ("id") + "" "+rs.getstring (" name "))                    + "" +rs.getstring ("Score")); }        //after the first turn offRs.close ();        St.close ();            Conn.close (); }                    

JDBC-API: All the bags are java.sql or javax.sql.

Drivermanger (Driver Manager): Manages a set of JDBC operations, which is a class.

Common methods:

1. Registered driver: static void Registerdriver (Driver Driver)

Why, however, does the registration drive use the Class.forName () instead of the Registerdriver function registration driver?

By the above, execute Registerdriver function, inside need to pass in a driver type object, view source we find

There is a static block of code in the Java.mysql.jdbc.Driver class, and it is well known that static code blocks run only once when the class is loaded into memory. We found that the Registerdriver function was also called in this static code block.

If we write the code itself with the Registerdriver (Driver Driver) function Registration driver, then when loading Driver this class, static code block execution, inside the Registerdriver function will be executed once, equivalent to registering two times the driver. This is not allowed. In order for the driver to be registered only once, we only need to execute the static code block in the driver class, so we just need to let the driver class load into memory.

There are three ways to load a class into memory: Class.forName ("fully qualified name"); Class name The. Class object. GetClass ();

All three methods can load the class into memory and successfully register the driver. The code above uses the first.

2. Get connection: Static Connection getconnection (string url, string user, string password)

The URL tells us what type of database to connect to and which database protocol to connect: database Type: Sub-protocol parameter.
For example:

mysql:jdbc:mysql://localhost:3306/database name

Oracle:jdbc:oracle:[email Protected]:[email Protected] instance thin represents a thin connection

User is the account name, password is the password

Connection: Connection interface

Common methods:

1. Get the statement performer:

Statement createstatement (): Get a SQL injection problem for normal statement performers

PreparedStatement preparestatement (String sql): Getting precompiled statement performers

CallableStatement preparecall (String sql): Gets the statement performer who called the stored procedure.

2. Some ways of doing business:

Setautocommit (FALSE) open transaction manually select False because the default is to not open transactions

Commit () COMMIT Transaction

Rollback () transaction rollback

Statement: Statement Performer interface

PreparedStatement: Pre-compiled statement performer interface

Common methods:

1. Setting parameters

setxxx (int number question mark, Object actual parameter);

2. Execute SQL

ResultSet executeQuery (): Execute R statement return value: Result set

int executeupdate (): Executes the CUD statement return value: The number of rows affected. Typically 1, you can determine whether the SQL statement succeeds by judging the number of rows returned.

ResultSet: Result set interface results returned after executing a query statement

Common methods:

1.boolean next (); Determines whether the next record, if any, returns true, and moves the cursor to the next line. If none, returns false

Note: The cursor starts at the top of the first record , so even if there is only one record in the result, it returns a true.

2.getXxx (int/string); Get specific content. If the argument is int, get the first few columns. Gets the column name (field name) if the argument is string.

GetString () can also get an int value, and getObject () can get arbitrary.

The above is the most basic operation of JDBC, in practice, when the above code is compiled into a. class file, it can be difficult to change the parameters above. In order to facilitate the period, the use of XML or properties files, the inside of all the parameters extracted, encapsulated into a tool class, namely: Jdbcutils Tool class, it will be more convenient. The following is the process of encapsulating the Jdbcutils tool class.

Here we are using the properties file in the same way.

The content format of the properties file is: Key=value, which is placed in the SRC directory.

We can quickly get the configuration information from the ResourceBundle tool class.

To use steps: 1. Get ResourceBundle object: Static ResourceBundle getbundle (" file name without suffix name ") The return value is a ResourceBundle object.

2. Get configuration information from the ResourceBundle object string getString (String key): Gets the specified value by executing key.

Note: The properties file cannot have extra spaces.

Because the configuration information for obtaining parameters and the registration driver only need to be loaded once, they are placed in a static code block.

Static {        // get the properties file.         driverclass = rb.getstring ("Driverclass");         = rb.getstring ("url");         = rb.getstring ("user");         = rb.getstring ("password");                    }

Static {               // This is a registered drive        try  {            class.forname (driverclass)        ; Catch (ClassNotFoundException e) {            //  TODO auto-generated catch block             E.printstacktrace ();        }    }        

Because the configuration information of the parameters is also used to obtain the connection, the constants of these configurations are defined again outside the static code block, and the final decoration is used to indicate that only the value is assigned once.

Static Final String driverclass;     Static Final String URL;     Static Final String USER;     Static Final String PASSWORD;

The Get connection and release connections are defined as static methods respectively.

When the connection is released, the statement interface is passed in instead of the Preparestatement interface. This can improve the reusability of the code.

 Public StaticConnection getconnection ()throwsClassNotFoundException, sqlexception{Connection Conn=drivermanager.getconnection (URL, USER, PASSWORD); returnConn; }         Public Static voidCloseresource (Connection conn,statement st,resultset rs)throwssqlexception{//NOTE: statement is passed in here, not preparestatement, because statement is the parent interfaceCloseconn (conn);        CloseSt (ST);    Closers (RS); }    /*** Release Connection * *@paramConn * Connection*/     Public Static voidCloseconn (Connection conn) {if(Conn! =NULL) {            Try{conn.close (); } Catch(SQLException e) {e.printstacktrace (); } Conn=NULL; }    }    /*** Release Statement performer * *@paramSt * Statement performers*/     Public Static voidCloseSt (Statement St) {if(St! =NULL) {            Try{st.close (); } Catch(SQLException e) {e.printstacktrace (); } St=NULL; }    }    /*** Release Result set * *@paramRS * result set*/     Public Static voidClosers (ResultSet rs) {if(rs! =NULL) {            Try{rs.close (); } Catch(SQLException e) {e.printstacktrace (); } RS=NULL; }    }

The above is the entire code for the tool class Jdbcutils.

However, there are limitations:

That is, when using JDBC, each operation needs to get the connection (create) and then release the connection (destroy).

Workaround: Use connection pooling to optimize the curd operation.

For more information about connection pooling operations and how to customize connection pooling, see the next article.

　　

Get ResourceBundle object:

Complete single-table curd operations and encapsulation of jdbcutils with JDBC