Cainiao JDBC (2)

Previous Article (http://blog.csdn.net/rowandjj/article/details/8883383) we learned how to connect to the MySQL database through JDBC, and through a simple code example to demonstrate the specific operation, here a brief review of the process: 1. Load the driver (class. forname ()....); 2. Establish a connection with the database through the drivermanager class (connection conn = drivermanager. getconnection ()....); 3. Create an SQL statement (statement ST = conn. createstatement ()....); 4.execute the SQL statement (st.exe cute (string SQL )...); 5. process the query result (if it is a select or other query statement, a resultset result set will be returned, which can be operated on );I believe that everyone has a preliminary understanding of how to connect to the database. Next we will optimize the last code. The Optimization content is as follows: 1. Optimize the database closure (Exception Handling ); 2. Optimize the Query Process (statement and preparedstatement ). 3. Optimize the architecture (repeated operations such as JDBC tool class, encapsulation and database connection, registration driver, and disconnection );Let's write a JDBC tool class first! Note: the connection and other classes imported in the Code are in the Java. SQL package. Do not import the MySQL package!Solution 1:

Package demo; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. resultset; import Java. SQL. sqlexception; import Java. SQL. statement; public final class jdbcutils {private jdbcutils () {}// because it is a tool class, you do not need a new object (because all are static methods) Private Static string drivername = "com. mySQL. JDBC. driver "; Private Static string url =" JDBC: mysql: // localhost/db_test "; Private Static string username =" root "; priva Te static string Password = "sjjhong"; // The registration driver is placed in the static code block. Ensure that it is registered only once, when the class is loaded into the virtual machine, it will execute -----> do not explicitly call static {try {class. forname (drivername);} catch (classnotfoundexception e) {// Catch Block automatically generated by todo throw new exceptionininitializererror (); // initialization failed} public static connection getconnection () throws sqlexception {// establish the connection return drivermanager. getconnection (URL, username, password);} public static void free (Res Ultset RS, statement stat, connection conn) {try {If (RS! = NULL) // otherwise, a null pointer exception may be thrown. Rs. close (); RS = NULL;} catch (sqlexception e) {e. printstacktrace ();} finally {try {If (stat! = NULL) stat. close (); stat = NULL;} catch (sqlexception e) {// Catch Block E automatically generated by todo. printstacktrace ();} finally {try {If (Conn! = NULL) Conn. Close (); Conn = NULL;} catch (sqlexception e) {e. printstacktrace ();}}}}}

In the tool class above, we have encapsulated some common methods, such as loading drivers, establishing connections, and disabling resources. Because these operations occur frequently, they are involved in methods such as addition, deletion, modification, and query. After encapsulation, code reuse is improved. In addition, we have optimized the database resource closure and handled exceptions. For details, refer to the Code to find out why. Solution 2 ):

Package demo; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. resultset; import Java. SQL. sqlexception; import Java. SQL. statement; public final class singdemo {Private Static string drivername = "com. mySQL. JDBC. driver "; Private Static string url =" JDBC: mysql: // localhost/db_test "; Private Static string username =" root "; Private Static string Password =" sjjhong "; private singde Mo () {} Private Static singdemo sin = NULL; // The lazy loading mode is used here (it is best to use the hunger style for security and convenience) public static singdemo getinstance () // obtain the instance {If (sin! = NULL) {synchronized (singdemo. Class) // get the lock and process concurrent operations {If (sin! = NULL) {sin = new singdemo () ;}} return sin ;}static {try {class. forname (drivername);} catch (classnotfoundexception e) {Throw new exceptionininitializererror () ;}} public connection getconnection () throws sqlexception {return drivermanager. getconnection (URL, username, password);} public void free (resultset RS, statement stat, connection conn) {try {If (RS! = NULL) // otherwise, a null pointer exception RS will be thrown. close (); RS = NULL;} catch (sqlexception e) {e. printstacktrace ();} finally {try {If (stat! = NULL) stat. close (); stat = NULL;} catch (sqlexception e) {// Catch Block E automatically generated by todo. printstacktrace ();} finally {try {If (Conn! = NULL) Conn. Close (); Conn = NULL;} catch (sqlexception e) {e. printstacktrace ();}}}}}

We continue to optimize it. If we change the database or user name and password, we need to re-change the source code, which is inconvenient. How can we solve this problem? We can solve this problem through the configuration file! After writing the configuration file, we can modify the configuration file to directly modify the database to be connected. The error probability is lower. Solution 3: Write the driver name, URL, and other content to the configuration file ):

Package biogdemo; import Java. io. fileinputstream; import Java. io. filenotfoundexception; import Java. io. ioexception; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. resultset; import Java. SQL. sqlexception; import Java. SQL. statement; import Java. util. properties; public class dbutils {Private Static string drivername = NULL; Private Static string user = NULL; Private Static string URL = NULL; Private Static string Password = NULL; Private Static dbutils instance = NULL; public static dbutils getinstance () {If (instance = NULL) {synchronized (dbutils. class) {If (instance = NULL) {instance = new dbutils () ;}} return instance;} private dbutils () {try {properties prop = new properties (); prop. load (New fileinputstream (getpropertyfilepath (); // load the configuration file // read the configuration file and assign a value to drivernam E = prop. getproperty ("drivername"); User = prop. getproperty ("user"); url = prop. getproperty ("url"); Password = prop. getproperty ("password"); Class. forname (drivername);} catch (classnotfoundexception e) {// Catch Block E automatically generated by todo. printstacktrace ();} catch (filenotfoundexception e) {// Catch Block E automatically generated by todo. printstacktrace ();} catch (ioexception e) {// Catch Block E automatically generated by todo. printstacktrace ();}} Public String getpropertyfilepath () {stringbuilder sb = new stringbuilder (); sb. append (system. getproperty ("user. dir "); // The current path sb. append ("\ SRC \\"). append ("biogdemo \\"). append ("DB. properties "); return sb. tostring ();} public connection getconnection () throws sqlexception {return drivermanager. getconnection (URL, user, password);} public static void free (connection Conn, statement stat, resultset rs) {Try {If (RS! = NULL) {Rs. close () ;}rs = NULL;} catch (sqlexception e) {// Catch Block E automatically generated by todo. printstacktrace ();} finally {try {If (stat! = NULL) {stat. close () ;}stat = NULL;} catch (sqlexception e) {// Catch Block E automatically generated by todo. printstacktrace ();} finally {try {If (Conn! = NULL) {conn. Close () ;}conn = NULL ;}catch (sqlexception e) {e. printstacktrace ();}}}}}

The following content is written into the DB. properties file:

driverName = com.mysql.jdbc.Driverpassword = sjjhongurl = jdbc:mysql://localhost/testuser = root 

Well, this optimization is here, which is far from enough. The blog post will be further improved (combined with the connection pool...). Let's simply use our tool class! (Solution 1)

Package biogdemo; import Java. SQL. connection; import Java. SQL. preparedstatement; import Java. SQL. resultset; import Java. SQL. resultsetmetadata; import Java. SQL. sqlexception; import demo. jdbcutils;/*** @ author rowand JJ ***/public class main {public static void main (string [] ARGs) {connection conn = NULL; preparedstatement PS = NULL; resultset rs = NULL; string SQL = "select ID, name, birthday, money from t B _9 where ID>? "; Int id = 2; try {conn = jdbcutils. getconnection (); // establish a connection PS = Conn. preparestatement (SQL); PS. setint (1, ID); // set the wildcard (?) in the SQL statement (?) Id // resultsetmetadata: the object that can be used to obtain the type and attribute information of columns in the resultset object. resultsetmetadata rsmd = ps. getmetadata (); int COUNT = rsmd. getcolumncount (); // obtain the number of columns rs = ps.exe cutequery (); // execute the query while (RS. next () {for (INT I = 1; I <= count; I ++) {system. out. print (RS. getObject (I) + "");} system. out. println ("\ n") ;}} catch (sqlexception e) {Throw new runtimeexception ("query failed! ");} Finally {// release, you know. Don't forget! Jdbcutils. Free (RS, PS, Conn );}}}

In the above example, we didn't use statement but changed it to preparedstatement. What is the difference between this class and statement? Let's take a look at the description of preparedstatement in this document! As you can see, the preparedstatement inherits the statement and is more powerful! The most obvious thing is the flexibility in processing query statements. Can I use it? Replace the query conditions, and provide a series of Set methods to replace ?. Differences:Statement: (used to execute simple SQL statements without parameters)

Each time you execute an SQL statement,
All databases need to compile SQL statements. It is better to execute only one query and return results, which is more efficient than preparedstatement.
Preparedstatement: (used to execute pre-compiled SQL statements with or without the in parameter)

1. The executed SQL statements can contain parameters and support batch execution of SQL statements. Because the cache mechanism is used, pre-compiled statements will be placed in the cache. The next time you execute the same SQL statement, you can obtain the statements directly from the cache, therefore, batch data with the same operation is more efficient. 2. Relatively safe to prevent SQL InjectionWe strongly recommend that you use preparedstatement.So far, this article will introduce the basic addition, deletion, modification, and query operations and the processing of different data types! Coming soon.