Apache_commons_dbutils Database Tools Class Description

These days when learning JDBC, I stumbled upon Apache's Dbutils tool class. Execute SQL statements with Dbutils, and encapsulate query results as JavaBean super easy to use, save time and effort.

Dbutils is a small JDBC lightweight package, the core of which is the encapsulation of the result set, which can encapsulate the result set of the query directly into JavaBean, which is the most tedious and error-prone part of our work.

First, Dbutils download

: Http://apache.fayea.com//commons/dbutils/binaries/commons-dbutils-1.6-bin.zip

After extracting the file, get Commons-dbutils-1.6.jar, this is the required jar package.

Ii. preparatory work

1. SQL script

CREATE TABLE`User' (' ID 'int(4) not NULLauto_increment, ' name 'varchar( -) not NULL, ' password 'varchar( -)DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=4 DEFAULTCHARSET=UTF8;

2. Create a user Java bean corresponding to the database table.

 Packagetestdbutils;/*** java bean corresponding to user table **/ Public classUser {//dbutils The automatic encapsulation of the result set to JavaBean is a demanding requirement: The JavaBean specification must be met, followed by the Bean getter and setter method name and the result set column name one by one corresponding//Private members of JavaBean are not required to correspond to table result set column name one by one. String name;    String password; //Be sure to have an argument-free constructor, or you'll get an error.     PublicUser () {} PublicUser (string name, string password) { This. Name =name;  This. Password =password; }        //The name of the Bean's getter and setter method corresponds to the column name one by one of the result set     PublicString GetName () {returnname; }     Public voidsetName (String name) { This. Name =name; }     PublicString GetPassword () {returnpassword; }     Public voidSetPassword (String password) { This. Password =password; }}        

3, casually write a database connection to get the class

 Packagetestdbutils;ImportJava.sql.*;
 Public classConntools {Private StaticString dirverclassname = "Com.mysql.jdbc.Driver"; Private StaticString url = "Jdbc:mysql://127.0.0.1:3306/test?useunicode=true&characterencoding=utf8"; Private StaticString user = "root"; Private StaticString password = "AAAAAA";  Public StaticConnection makeconnection () {Connection conn=NULL; Try{class.forname (dirverclassname); } Catch(ClassNotFoundException e) {e.printstacktrace (); }             Try{conn=drivermanager.getconnection (URL, user, password); } Catch(SQLException e) {e.printstacktrace (); }             returnConn; } }

Iii. examples

1. Insert, UPDATE, delete

Connection conn = conntools.makeconnection ();  // gets the Queryrunner object for the dbutils. All SQL statements are executed through the Queryrunner object new  queryrunner () String sql = "INSERT into user (Name,password) values (?,?)" ; // Execute SQL, and insert is also performed with the Update method.  runner.update (conn, SQL, User.getname (), User.getpassword ()); // Close Connection     Dbutils.close (conn);　　

2, query, return the encapsulated user Bean object

  Public Static voidTest_find ()throwsSQLException {System.out.println ("-------------Test_find ()-------------"); //Create a connectionConnection conn =conntools.makeconnection (); //Creating the SQL Execution ToolQueryrunner runner =NewQueryrunner (); //execute the SQL query and get the resultslist<user> list = Runner.query (conn, "Select id,name,pswd from User",NewBeanlisthandler (User.class)); //Output Query Results                 for(user user:list) {System.out.println (user); }                 //To close a database connectiondbutils.closequietly (conn); } }

Apache_commons_dbutils Database Tools Class Description