Apache Common Dbutils

The Apache Common dbutils tool was used in the previous period to leave a print in case you would like to check it out from now on. We all know that the database access layer in the market now has a lot of frameworks, of course, many of them include or-mapping work steps, such as common Hibernate and MyBatis. Of course, if people want a purely encapsulated JDBC tool class, using Apache Common dbutils (hereinafter referred to as ACD) is a good choice, the tool on the basis of JDBC is a little encapsulation is JDBC operation more convenient, you do not need to learn to use this framework in the way 　　Too many API classes, as there are only 3 parts (3 packages) in total. 1. Org.apache.commons.dbutils (The classes in this package mainly help us to operate JDBC more conveniently) 2. Org.apache.commons.dbutils.handlers (The classes in this package are implementation classes that implement the Org.apache.commons.dbutils.ResultSetHandler interface) 3. Org.apache.commons.dbutils.wrappers (The classes in this package are primarily encapsulated with operations on the SQL result set) Some advantages of using this dbutils: 1.　　To prevent the disclosure of resources, writing a JDBC preparation code is not a hassle, but those operations are really time-consuming and cumbersome, which can sometimes cause the database connection to forget to close and cause the exception to be difficult to track.　　2. Clean and tidy Persistence code, the data persisted to the database code is cut, the remaining code can clearly and concisely express the purpose of your operation.　　　　3. Automatically map the tools in the resultsets to the JavaBean, you do not need to manually use setter methods to assign the column values to a corresponding time, each row in the ResultSet table is a complete bean entity. The simplest way to learn how to use this framework is to use it to write a Demo-crud operation, so let's do a prep action to build a test-specific table visitor in MySQL. [SQL] View plaincopy on code to see a snippet derived from my Code slice 01./* create visitor*/02.CREATE TABLE Visitor 03. (All. Id INT (one) not NULL auto_increment, 05. Name VARCHAR (+) is not NULL, 06. Email VARCHAR (+) not NULL, 07. Status INT not NULL DEFAULT 1, 08. Createtime DateTime, 09. PRIMARY KEY (Id) 10.) Building the table structure, we can learn how to use the Utils class in the framework to help us complete the Crud-demo, In fact, the main operation for this framework is the Resultsethandler interface implementation class and the Queryrunner class to create the corresponding JavaBean entity class as follows: [Java] View Plaincopy on code to view the chip derivation to my Code slice 01.package David.apache.model; 03.import Java.text.SimpleDateFormat; 04.import java.util.Date; 06.public class Visitor {07. . private int ID; . private String name; . private String Email; One. private int status; Private Date Createtime; 13.14. Public Visitor () {.//TODO auto-generated constructor stub. Setcreatetime (New Date ()); 17.} 18. . public Visitor (string name, string email) {This.setname (name); This.setemail (email); This.setstatus (1); This.setcreatetime (New Date ()); 24.} 25. . public int GetId () {. return ID; 28.} 29. public void setId (int id) {this.id = ID; 32.} 33. The. Public String GetName () {. return name; 36.} 37. . public void SetName (String name) {this.name = name; 40.} 41. 42. Public String Getemail () {. return email; 44.} 45. Setemail public void (String email) {this.email = email; 48.} 49. public int getStatus () {. return status; 52.} 53. A. public void setStatus (int status) {this.status = status; 56.} 57. . Public Date Getcreatetime () {. return createtime; 60.} 61. . public void Setcreatetime (Date createtime) {this.createtime = Createtime; 64.} 65. 67. @Override. Public String toString () {.//TODO auto-generated method stub. Return String.Format ("{Id:%d, Name:%s, Email:%s, Createtime:%s} ", GetId (), GetName (), Getemail (), 70. New SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss"). Format (Getcreatetime ())); 71.} 72.} First, let's create a new way to get Connection: [Java] View plaincopy on code to see the chip derivation to my code slice 01.private static Connection getconnection () {02. Connection conn = null; The. try {04. Class.forName (CONNECTION_DRIVER_STR); conn = Drivermanager.getconnection (connection_str, "root", "123456"); .} catch (Exception e) {07.E.printstacktrace (); 08.} 09. Return conn; 10.} New method (for the self-increment field inside, we can use a workaround to insert, using the Select LAST_INSERT_ID () method) [Java] View plaincopy on code to see the chip derivation to my code slice 01./* 02. * Added Visitor, Scalarhandler demo 03. */04.public static void Insertvisitor (Visitor Visitor) {05. Connection conn = getconnection (); Queryrunner qr = new Queryrunner (); . String sql = "INSERT into visitor (Name, Email, Status, Createtime) VALUES (?,?,?,?)"; . try {qr.update int count = *. conn, SQL, Visitor.getname (), Visitor.getemail (), 1, New Date ()); BigInteger newId = (BigInteger) qr.query (conn, "select last_insert_id ()", New Scalarhandler (1)); Visitor.setid (Integer.valueof (string.valueof (newId))); System.out.println ("added" + Count + "bar data =>id:" + newId); .} catch (SQLException e) {e.printstacktrace (); 15.} 16.} You can see the steps of the operation is actually very simple, but also to write SQL, for the self-increment field We use the method of select last_insert_id () scalarhandler The entity class to return to the work-around effect. Delete method [Java] View plaincopy on code to view the snippet derivation to my code slice 01.public static void deletevisitor (int id) {02. Connection conn = getconnection (); Queryrunner qr = new Queryrunner (); . String sql = "Delete from visitor where status>0 and id=?"; try {. int count = qr.update (conn, SQL, ID); . System.out.println ("delete" + Count + "bar data.) "); .} catch (SQLException e) {todo:handle//exception. E.printstacktrace (); 11.} 12.} Query method [Java] View plaincopy on code to view the snippet derivation to my code slice 01.public static Visitor retrievevisitor (int id) {02. Connection conn = getconnection (); Visitor Visitor = null; Queryrunner qr = new Queryrunner (); String sql = "SELECT * from visitor where status>0 and id=?"; . try {visitor = (visitor) qr.query (conn, SQL, new Beanhandler (visitor.class), id); System.out.println (visitor); -Visitor return; Ten.} catch (Exception e) {one. E.printstacktrace (); 12.} 13. return visitor; 14.} update operation [Java] view plaincopy on code to see the snippet derivation to my code slice 01.public static void updatevisitor (int id) {02. Visitor Visitor = retrievevisitor (ID); System.out.println ("Pre-update:" + visitor); Connection conn = getconnection (); . String updatefieldstr = Visitor.getname (); Queryrunner qr = new Queryrunner (); . String sql = "Update visitor set Name =?, Email =?, Status =?, Createtime =?" where status>0 and Id =? "; if (Updatefieldstr.contains ("updated")) {updatefieldstr = updatefieldstr.substring (0, Updatefieldstr.indexof ( "Updated")); Ten.} else {one. updatefieldstr = Updatefieldstr + "updated"; 12.} 13. Visitor.setname (UPDATEFIELDSTR); try {. int count = qr.update (conn, SQL, new object[] {visitor.getname (), Visitor.getname (), Visitor.getstatus (), 1 6. Visitor.getcreatetime (), Visitor.getid ()}); System.out.pr.Intln ("updated" + Count + "bar data"); System.out.println ("Updated:" + visitor); .} catch (SQLException e) {.//Todo:handle exception. E.printstacktrace (); 22.} 23.} Beanlisthandler method [Java] View plaincopy on code to view the snippet derived from my code slice 01.public static void Getvisitorlist () {02. Connection conn = getconnection (); Queryrunner qr = new Queryrunner (); *. String sql = "SELECT * from visitor where status>0"; try {06. List ls = qr.query (conn, SQL, new Beanlisthandler (Visitor.class)); . for (Visitor visitor:ls) {08. System.out.println (visitor); 09.} 10. } catch (SQLException e) {one.//TODO auto-generated catch block. E.printstacktrace (); 13.} 14.} Maphandler operation [Java] view plaincopy on code to view the chip derivation to my code slice 01.public static void getvisitwithmap (int id) {02. Connection conn = getconnection (); Queryrunner qr = new Queryrunner (); *. String sql = "SELECT * from visitor where status>0 and id=?"; try {06. Map Map = qr.query (conn, SQL, New Maphandler (), id); . Integer VisitorID = integer.valueof (Map.get ("Id"). toString ()); . String Visitorname = Map.get ("Name"). ToString (); . String Visitoremail = Map.get ("Email"). ToString (); Ten. Integer visitorstatus = integer.valueof (Map.get ("Status"). toString ()); SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss"); Date visitorcreatetime = Sdf.parse (Map.get ("Createtime"). toString ()); Visitor Visitor = new Visitor (Visitorname, Visitoremail); Visitor.setid (VisitorID); Visitor.setstatus (Visitorstatus); Visitor.setcreatetime (Visitorcreatetime); System.out.println (visitor); .} catch (Exception e) {.//Todo:handle Exception. E.printstacktrace (); 21.} 22.} Maplisthandler method [Java] View plaincopy on code to view the snippet derived from my code slice 01.public static void Getvisitwithmapls () {02. Connection conn = getconnection (); Queryrunner qr = new Queryrunner (); *. String sql = "SELECT * from visitor where status>0"; try {06. List > Mapls = qr.query (conn, SQL, New Maplisthandler ()); . for (Map map:mapls) {08. Integer VisitorID = integer.valueof (Map.get ("Id"). toString ()); . String Visitorname = Map.get ("Name"). ToString (); Ten. String visitoremail = Map.get ("Email"). ToString (); One. Integer visitorstatus = integer.valueof (Map.get ("Status"). toString ()); SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss"); Date visitorcreatetime = Sdf.parse (Map.get ("Createtime"). toString ()); Visitor Visitor = new Visitor (Visitorname, Visitoremail); Visitor.setid (VisitorID); Visitor.setstatus (Visitorstatus); Visitor.setcreatetime (Visitorcreatetime); System.out.println (visitor); 19.} 20. } catch (Exception e) {.//Todo:handle Exception. E.printstacktrace (); 23.} 24. After a few examples above, I believe you should know how to use this framework-the framework of the official website address

Apache Common dbutils