"Java EE Learning Day 16th" "How to use Dbutils"

First, why to use Dbutils

Use Dbutils to greatly simplify code writing, making development progress faster and more efficient

Second, dbutils

http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

The DS that appears is a data source object, either a datasource for a database connection pool or a datasource for a custom database connection pool, where I use the DBCP database connection pool.

1  Packageday16.regular.utils;2 3 Importjava.io.IOException;4 Importjava.sql.Connection;5 Importjava.sql.SQLException;6 Importjava.util.Properties;7 8 ImportJavax.sql.DataSource;9 Ten Importorg.apache.commons.dbcp2.BasicDataSourceFactory; One  A  - //Create a DBCP connection pool using a third-party jar package -  Public classDATASOURCEUTILS_DBCP { the     PrivateDATASOURCEUTILS_DBCP () {}//you can prevent objects from being created by using the private adornment construction method, which ensures that only one DataSource object -     Private StaticDataSource ds; -     Static -     { +Properties properties=NewProperties (); -         Try { +Properties.load (DATASOURCEUTILS_DBCP.class. getClassLoader (). getResourceAsStream ("Config_dbcp.properties")); Ads=Basicdatasourcefactory.createdatasource (properties); at}Catch(IOException e) { - e.printstacktrace (); -}Catch(Exception e) { - e.printstacktrace (); -         } -     } in      Public StaticConnection getconnection () -     { toConnection conn=NULL; +         Try { -conn=ds.getconnection (); the}Catch(SQLException e) { * e.printstacktrace (); $         }Panax Notoginseng         returnConn; -     } the      Public StaticDataSource Getdatasource () +     { A         returnds; the     } +}

Datasourceutils_dbcp.java

Private Static DataSource Ds=datasourceutils_dbcp.getdatasource ();

Example of an Update operation method (delete statement slightly).

 Public void Testinsert ()    {        Queryrunner run=new  Queryrunner (DS);        String SQL= "INSERT into people (Id,name,age,sex) VALUES (?,?,?,?)" ;         Try {            run.update (SQL,"001", "Xiao Qiang", 12, "male")        ; Catch (SQLException e) {            e.printstacktrace ();        }    }

You can use Dbutils to achieve precompilation.

Iv. Arraylisthandler usage (no need to master).

 Public voidTestselect_arraylisthandler ()throwsSQLException {Queryrunner run=NewQueryrunner (DS); String SQL= "SELECT * from people"; List<object[]>arr=run.query (SQL,NewArraylisthandler ()); //executes the method to store all results in a list, and each row of results is saved in an object array         for(Object obj[]:arr) { for(Object o:obj) {System.out.print (o+" ");        } System.out.println (); }        /*** 001 Xiao Qiang 12 men 002 313 men 003 Li 414 Men 004 Kings 514 Men 005 Zhao Liu 14 male 006 Chen 714 male*/    }

V. Beanhandler and Beanlisthandler usage (key Mastery)

　　1.BeanHandler: Call Queryrunner's Query method, and provide the corresponding parameters, you can get an already encapsulated bean object (need to implement the preparation of a JavaBean, field names to exactly match).

 Public voidTestselect_beanhandler ()throwsSQLException {Queryrunner run=NewQueryrunner (DS); String SQL= "SELECT * from people"; People people=run.query (SQL,NewBeanhandler<people> (people.class)); //You can use this method to automatically encapsulate the first row of a result set into a bean object. System.out.println (people); /*** People [id=001, Name= Qiang, age=12, sex= male]*/    }

　　2.BeanListHandler: Call Queryrunner's Query method, and provide the corresponding parameters, you can get a list object, each element of the object is a JavaBean object.

 Public voidTestselect_beanlisthandler ()throwsSQLException {Queryrunner run=NewQueryrunner (DS); String SQL= "SELECT * from people"; List<people>list=run.query (SQL,NewBeanlisthandler<people> (people.class));  for(People people:list) {System.out.println (people); }        /*** People [id=001, Name=, age=12, sex= man] people [id=002, Name= Zhang San, age=13, sex= man] P eople [id=003, Name= John Doe, age=14, sex= male] people [id=004, Name= Harry, age=14, sex= male] people [id=005, Name= Zhao Liu, age=14, sex= male] people [id=006, Name= Chen Seven, age=14, sex= male]*/    }

Vi. Columnlisthandler: Gets all the values of a field and encapsulates it into a list object (not required).

 Public voidTestselect_columnlisthandler ()throwsSQLException {Queryrunner run=NewQueryrunner (DS); String SQL= "SELECT * from people"; List<string>list=run.query (SQL,Newcolumnlisthandler<string> ("Sex"));//default query with no parameters first row         for(String obj:list) {System.out.println (obj); }        /*** male and male male*/    }

Seven, Keyedhandler: Get a key value pair, the key is the primary key of the table, the value is a map object, which encapsulates a row of data key-value pairs, the key is the field name (Focus).

 Public voidTestselect_keyhandler ()throwsSQLException {//This method is not important, do not need to focus on masteringQueryrunner run=NewQueryrunner (DS); String SQL= "SELECT * from people"; Map<string, map<string, object>>map=run.query (SQL,NewKeyedhandler<string> ("id")); Iterator<string>it=Map.keyset (). iterator ();  while(It.hasnext ()) {Map<string,object>m=Map.get (It.next ());        System.out.println (m); }    /*{id=004, name= Harry, age=14, sex= man} {id=005, name= Zhao Liu, age=14, sex= man} {id=006, Name= Chen Seven, age=14, sex= man}        {id=001, name=, age=12, sex= man}        {id=002, Name= Zhang San, age=13, sex= man} {id=003, name= John Doe, age=14, sex= man}*/    }

Viii. Maphandler: Encapsulates the first row of data into a map object (no need to master).

 public  void  Testselect_maphandler () throws   SQLException {Queryrunner Run=new   Queryrunner (DS);        String SQL  = "SELECT * from user" ; Map  <string, object>map=run.query (Sql,new          Maphandler ());  //  The result of the execution encapsulates only the first row.   System.out.println (map);  /*   {id=0001, name=, age=12}  */   

Ix. Maplisthandler: Encapsulates each row of data into a map and saves it to a list. (Key to Master)

 Public voidTestselect_maplisthandler ()throwsSQLException {Queryrunner run=NewQueryrunner (DS); String SQL= "SELECT * from User"; List<Map<String,Object>> list=run.query (SQL,NewMaplisthandler ()); /*** The result of this statement is to store the database's query results in a list, where each row is stored in a map object. */         for(map<string,object>map:list)        {System.out.println (map); }        /*{id=0001, name=, age=12} {id=0002, Name= xiaoming, age=13}*/    }

Scalarhandler: Use this method to simplify writing when using aggregate functions (such as count, sum, and so on) (key mastery)

 Public void throws SQLException    {        Queryrunner run=new  Queryrunner (DS);        String SQL= "SELECT count (1) from user";        Object valuenew scalarhandler<object>());         // Scalarhandler is used to execute queries with aggregate functions, and it is convenient to execute the Count method.          System.out.println (value);    }
/*
2
*/

"Java EE Learning Day 16th" "How to use Dbutils"