JDBC: Use the DBUtils tool class, jdbcdbutils

Source: Internet
Author: User

JDBC: Use the DBUtils tool class, jdbcdbutils

Jar package: commons-dbutils-1.6.jar

DbUtils class

1. DbUtils: Provides tools such as shutting down connections and loading JDBC drivers. All the methods in the tool are static. The main method is as follows:
-Public static void close (...) Throws java. SQL. SQLException: The DbUtils class provides three methods for disabling heavy loads. These methods check whether the provided parameters are NULL. If not, they disable Connection, Statement, and ResultSet.
-Public static void closeQuietly (...) : This type of method not only avoids closing when Connection, Statement, and ResultSet are NULL, but also hides SQLEeception thrown in the program.
-Public static void commitAndCloseQuietly (Connection conn): Used to submit a Connection and close the Connection. When the Connection is closed, no SQL exception is thrown.
-Public static boolean loadDriver (java. lang. String driverClassName): this party loads and registers the JDBC driver. If the driver succeeds, true is returned. With this method, you do not need to catch this exception ClassNotFoundException.

1.1QueryRunner class

This class simplifies SQL queries. Combined with ResultSetHandler, this class can complete most database operations and greatly reduce the coding workload.
The QueryRunner class provides two constructor methods:
-Default constructor
-A javax. SQL. DataSource is required for parameter construction.

Main method of QueryRunner class

Public Object query (Connection conn, String SQL, Object [] params, ResultSetHandler rsh) throws SQLException: executes a query operation. In this query, each element value in the object array is used as a replacement parameter of the query statement. This method automatically handles the creation and closure of PreparedStatement and ResultSet.
Public Object query (String SQL, Object [] params, ResultSetHandler rsh) throws SQLException: almost the same as the first method. The only difference is that it does not provide database connections to methods, in addition, it re-obtains the Connection from the data source provided to the constructor or the setDataSource method used.
Public Object query (Connection conn, String SQL, ResultSetHandler rsh) throws SQLException: executes a query operation without replacing parameters.
Public int update (Connection conn, String SQL, Object [] params) throws SQLException: used to perform an update (insert, update, or delete) operation.
Public int update (Connection conn, String SQL) throws SQLException: used to perform an update operation without replacing parameters.

<Span style = "font-size: 14px;">/*** test the QueryRunner Update Method * this method is used for INSERT, UPDATE, DELETE */@ Testpublic void testQueryRunnerUpdate () {// 1. queryRunner: QueryRunner queryRunner = new QueryRunner (); // String SQL = "delete from temp where id in (?,?) "; // String SQL =" insert into temp (id, name, birth) values (?,?,?) "; String SQL =" update temp set name =? Where id =? "; // 2. use updateConnection conn = null; try {conn = JDBC_Tools.getConnection (); queryRunner. update (conn, SQL, "OK", 1);} catch (Exception e) {JDBC_Tools.relaseSource (conn, null) ;}</span>
1.2.ResultSetHandler Interface
This interface is used to process java. SQL. ResultSet and convert data to another form as required.
The ResultSetHandler interface provides a separate method: Object handle (java. SQL. ResultSet. rs ).

<Span style = "font-size: 14px;"> class MyResultSetHandler implements ResultSetHandler <Object >{@ Overridepublic Object handle (ResultSet rs) throws SQLException {// System. out. println ("handler is OK! "); // Return" OVER "; List <Person> persons = new ArrayList <Person> (); while (rs. next () {Integer id = rs. getInt (1); String name = rs. getString (2); Date birth = rs. getDate (3); persons. add (new Person (id, name, birth) ;}return persons ;}} /*** the return value of the Query method for QueryRunner testing depends on the return value of * handle method of the ResultSetHandler parameter */@ Testpublic void testQueryRunnerQuery () {QueryRunner queryRunner = new QueryRunner (); connection conn = null; try {conn = JDBC_Tools.getConnection (); String SQL = "select * from temp"; @ SuppressWarnings ("unchecked ") list <Object> ls = (List <Object>) queryRunner. query (conn, SQL, new MyResultSetHandler (); for (Object p: ls) System. out. println (p);} catch (Exception e) {e. printStackTrace () ;}finally {JDBC_Tools.relaseSource (conn, null) ;}</span>

1.3 implementation class of the ResultSetHandler Interface

ArrayHandler: converts the first row of data in the result set into an array of objects.
ArrayListHandler: converts each row of data in the result set into an array and stores it in the List.
BeanHandler: encapsulate the first row of data in the result set into a corresponding JavaBean instance.
BeanListHandler: encapsulate each row of data in the result set into a corresponding JavaBean instance and store it in the List.

ColumnListHandler: stores the data of a column in the result set to the List.
KeyedHandler (name): encapsulate each row of data in the result set into a Map, and then store these maps in a map. The key is the specified key.
MapHandler: encapsulate the first row of data in the result set into a Map. The key is the column name and the value is the corresponding value.
MapListHandler: encapsulate each row of data in the result set in a Map, and store the data in the List.

<Span style = "font-size: 14px;"> package xuezaipiao1; import java. SQL. connection; import java. SQL. date; import java. SQL. resultSet; import java. SQL. SQLException; import java. util. arrayList; import java. util. list; import java. util. map; import org. apache. commons. dbutils. queryRunner; import org. apache. commons. dbutils. resultSetHandler; import org. apache. commons. dbutils. handlers. beanHandler; import org. apache. commons. Dbutils. handlers. beanListHandler; import org. apache. commons. dbutils. handlers. mapHandler; import org. apache. commons. dbutils. handlers. mapListHandler; import org. apache. commons. dbutils. handlers. scalarHandler; import org. junit. test;/*** Test DbUtils tool class * @ author Kevy */public class testDbUtils {/*** ScalarHandler: convert the result set to a value (which can be any basic data type and string), */@ Testpublic void testScalarHandler () {Connection conn = null; QueryRunner queryRunner = new QueryRunner (); try {conn = JDBC_Tools.getConnection (); // String SQL = "select name from temp where id =? "; String SQL =" select count (id) from temp "; Object s = queryRunner. query (conn, SQL, new ScalarHandler <Person> (); System. out. println (s);} catch (Exception e) {// TODO: handle finished tione. printStackTrace ();} finally {JDBC_Tools.relaseSource (conn, null);}/*** MapListHandler: Convert the result set to a Map List (not a Javabean binding) * A Map corresponds to the record key obtained from a query: name of the SQL query column (not the column alias); Value: column value * MapListHandler: the returned Map set corresponding to multiple records */@ Testpu Blic void testMapListHandler () {Connection conn = null; QueryRunner queryRunner = new QueryRunner (); try {conn = JDBC_Tools.getConnection (); String SQL = "select * from temp "; list <Map <String, Object> ls = queryRunner. query (conn, SQL, new MapListHandler (); for (Map <String, Object> map: ls) {for (Map. entry <String, Object> m: map. entrySet () {System. out. print (m. getKey (); System. out. println (":" + m. getValue () ;}} Catch (Exception e) {// TODO: handle finished tione. printStackTrace ();} finally {JDBC_Tools.relaseSource (conn, null);}/*** MapHandler: return the Map object corresponding to the first record corresponding to the SQL statement * key: the name of the SQL query column (not the column alias), not bound to a Javabean * value: column value */@ Testpublic void testMapHandler () {Connection conn = null; queryRunner queryRunner = new QueryRunner (); try {conn = JDBC_Tools.getConnection (); String SQL = "select id, name, birth from temp"; Map <String, Object> map = queryRunner. query (conn, SQL, new MapHandler (); for (Map. entry <String, Object> entry: map. entrySet () {System. out. print (entry. getKey () + ":"); System. out. println (entry. getValue () ;}} catch (Exception e) {// TODO: handle finished tione. printStackTrace ();} finally {JDBC_Tools.relaseSource (conn, null);}/*** BeanListHandler: Convert the result set to List. The List is not null, but it may be a null set (size () = 0) * If the SQL statement can query records, Lis T stores the object * bound to Javabean corresponding to the Class parameter passed by BeanListHandler. the alias of the column to be queried must be the same as the attribute value of the corresponding java Class */@ Testpublic void testBeanListHandler () {Connection conn = null; QueryRunner queryRunner = new QueryRunner (); try {conn = JDBC_Tools.getConnection (); String SQL = "select * from temp"; List <Person> ls = queryRunner. query (conn, SQL, new BeanListHandler <Person> (Person. class); for (Person p: ls) System. out. println (p);} catch (Exception e) {// TODO: handle finished tione. printStackTrace ();} finally {JDBC_Tools.relaseSource (conn, null );}} /*** BeanHandler converts the first record of the result set into the object corresponding to the Class parameter passed in when the BeanHandler object is created * only one record is returned, and then the Javabean object is bound, the alias of the column to be queried must be the same as the attribute value of the corresponding java class * @ param <T> **/@ Testpublic <T> void testBeanHandler () {Connection conn = null; queryRunner queryRunner = new QueryRunner (); try {conn = JDBC_Tools.getConnection (); String SQL = "selec T * from temp where id =? "; // BeanHandler <T> Person p = queryRunner. query (conn, SQL, new BeanHandler <Person> (Person. class), 1); System. out. println (p);} catch (Exception e) {// TODO: handle finished tione. printStackTrace () ;}finally {aggregate (conn, null) ;}} class MyResultSetHandler implements ResultSetHandler <Object >{@ Overridepublic Object handle (ResultSet rs) throws SQLException {// System. out. println ("handler is OK! "); // Return" OVER "; List <Person> persons = new ArrayList <Person> (); while (rs. next () {Integer id = rs. getInt (1); String name = rs. getString (2); Date birth = rs. getDate (3); persons. add (new Person (id, name, birth) ;}return persons ;}} /*** test the return value of QueryRunner's Query method depends on the return value of * handle method of the ResultSetHandler parameter */@ Testpublic void testQueryRunnerQuery () {QueryRunner queryRunner = new QueryRunner (); Connection conn = Null; try {conn = JDBC_Tools.getConnection (); String SQL = "select * from temp"; @ SuppressWarnings ("unchecked") List <Object> ls = (List <Object>) queryRunner. query (conn, SQL, new MyResultSetHandler (); for (Object p: ls) System. out. println (p);} catch (Exception e) {e. printStackTrace ();} finally {JDBC_Tools.relaseSource (conn, null);}/*** Test the QueryRunner Update Method * this method is used for INSERT, UPDATE, DELETE */@ Test Public void testQueryRunnerUpdate () {// 1. Create the QueryRunner implementation class QueryRunner queryRunner = new QueryRunner (); // String SQL = "delete from temp where id in (?,?) "; // String SQL =" insert into temp (id, name, birth) values (?,?,?) "; String SQL =" update temp set name =? Where id =? "; // 2. use updateConnection conn = null; try {conn = JDBC_Tools.getConnection (); queryRunner. update (conn, SQL, "OK", 1);} catch (Exception e) {JDBC_Tools.relaseSource (conn, null) ;}}</span>



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.