Introduction and case analysis of curd template developed by 4--apache Company

1. Introduction

Commons-dbutils is an open-source JDBC Tool class library provided by the Apache organization, a simple encapsulation of JDBC , very low learning costs, and the use of dbutils can greatly simplify the workload of JDBC coding without affecting program performance. So Dbutils became the first choice for a lot of companies that don't like hibernate.

API Introduction:

Org.apache.commons.dbutils.QueryRunner--bdmanager

org.apache.commons.dbutils.resultsethandler-Processing Result Sets

Tool Class

Org.apache.commons.dbutils.DbUtils,.

2. Dbutils class

dbutils: A tool class that provides routine work such as closing connections, loading JDBC drivers, and all of the methods inside are static . The main methods are as follows:

The following is a brief introduction to the method and a detailed reference to the Help documentation.

The public static void close (...) throws Java.sql.SQLException:DbUtils class provides three overloaded shutdown methods. These methods check that the supplied parameters are null, and if not, they close connection, statement, and ResultSet.

public static void Closequietly (...): This kind of method can not only avoid the shutdown in connection, statement and ResultSet, but also hide some sqleeception thrown in the program. That is to add more we do the closing method of the con, RS, st is empty interpretation statements.

public static void commitandclosequietly (Connection conn): Used to commit a connection, then close the connection, and do not throw a SQL exception when the connection is closed.

public static Boolean loaddriver (Java.lang.String driverclassname): This side loads and registers the JDBC driver and returns true if successful. Using this method, you do not need to capture this exception classnotfoundexception.

3.QueryRunner class

This class simplifies SQL queries, which are combined with Resultsethandler to do most of the database operations and greatly reduce the amount of coding.

The Queryrunner class provides two construction methods:

default method of construction

A javax.sql.DataSource is needed to construct a parameter.

Public Object Query (Connection conn, String sql, object[] params, Resultsethandler rsh) throws SQLException: Perform a query operation, in this check In the query, each element value in the object array is used as the permutation parameter for the queried statement. This method handles the creation and shutdown of PreparedStatement and ResultSet itself.

Public Object query (String sql, object[] params, Resultsethandler rsh) throws SQLException: Almost as the first method The only difference is that it does not provide a database connection to the method, and it is Connection from the data source (DataSource) provided to the construction method or the Setdatasource method used.

Public Object Query (Connection conn, String sql, Resultsethandler rsh) throws SQLException: Performs a query operation that does not require a substitution parameter.

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 that does not require a substitution parameter.

4. Resultsethandler interface

This interface is used to process java.sql.ResultSet, converting data to another form as required. The Resultsethandler interface provides a separate method: Object handle (Java.sql.ResultSet. rs).

Resultsethandler Interface Implementation class

Arrayhandler: Turns the first row of data in the result set into an array of objects .

Arraylisthandler: Each row of data in the result set is converted into an array and stored in the list.

Beanhandler: Encapsulates the first row of data in a result set into a corresponding JavaBean instance .

Beanlisthandler: Encapsulates each row of data in the result set into a corresponding JavaBean instance, which is stored in the list.

Columnlisthandler: stores data from a column in the result set into a list.

Keyedhandler (name): Encapsulates each row of data in the result set into a map, and then saves the map to a map with the key as the specified key.

Maphandler: Encapsulates the first line of data in a result set into a map , the key is the column name, and the value is the equivalent.

Maplisthandler: Encapsulates each row of data in a result set into a map and then stores it in the list

5. Above knowledge Point case analysis

To check the JavaBean of the package com.csdn.demo;
Import java.sql.SQLException;

Import java.util.List;
Import Org.apache.commons.dbutils.QueryRunner;
Import Org.apache.commons.dbutils.handlers.BeanHandler;
Import Org.apache.commons.dbutils.handlers.BeanListHandler;

Import Org.junit.Test;
Import Com.csdn.domain.Users;

Import Com.csdn.util.DBManager; public class Demo1 {@Test the public void Insert () throws sqlexception{queryrunner qr = new Queryrunner (dbmanager.getdat
		Asource ());
		String sql = "INSERT into user (Id,username,password) VALUES (?,?,?)";
		Object[] params = {3, "ASD", "ASD"};
		int i = qr.update (sql,params);
		if (i>0) {System.out.println ("successful");
		@Test public void Delete () throws sqlexception{queryrunner qr = new Queryrunner (Dbmanager.getdatasource ());
		String sql = "Delete from user where id =?";
		int i = qr.update (sql,17);
		if (i>0) {System.out.println ("successful"); @Test public void Update () throws sqlexception{queryrunner qr = new Queryrunner (DBMAnager.getdatasource ()); String sql = "Update user set username=?"
		WHERE id =? ";
		Object[] params = {"AAAAA", 3};
		int i = qr.update (sql,params);
		if (i>0) {System.out.println ("successful");
		@Test public void query () throws sqlexception{queryrunner qr = new Queryrunner (Dbmanager.getdatasource ());
		String sql = "Select Id,username,password from user where id =?";
		Object[] params = {1};
		Users user = (users) qr.query (sql,params,new Beanhandler (Users.class));
	System.out.println (User.getid () + "---" +user.getusername () + "---" +user.getpassword ());
		@Test public void Queryall () throws sqlexception{queryrunner qr = new Queryrunner (Dbmanager.getdatasource ());
		String sql = "Select Id,username,password from User";
		list<users> list = (list<users>) qr.query (sql,new Beanlisthandler (Users.class));
		for (Users users:list) {System.out.println (Users.getid () + "---" +users.getusername () + "---" +users.getpassword ());

The use of other processors package com.csdn.demo; ImPort Java.sql.SQLException;
Import Java.util.Arrays;
Import java.util.List;

Import Java.util.Map;
Import Org.apache.commons.dbutils.QueryRunner;
Import Org.apache.commons.dbutils.handlers.ArrayHandler;
Import Org.apache.commons.dbutils.handlers.ArrayListHandler;
Import Org.apache.commons.dbutils.handlers.ColumnListHandler;
Import Org.apache.commons.dbutils.handlers.KeyedHandler;
Import Org.apache.commons.dbutils.handlers.MapHandler;
Import Org.apache.commons.dbutils.handlers.MapListHandler;

Import Org.junit.Test;

Import Com.csdn.util.DBManager; public class Demo2 {//processors only return one row @Test public void Testarrayhandler () throws sqlexception{queryrunner qr = new Qu
		Eryrunner (Dbmanager.getdatasource ());
		String sql = "Select Id,username,password from User";
		Object[] object = qr.query (sql, New Arrayhandler ());
		System.out.println (object.length);
List output, as System.out.println (Arrays.aslist (object)) as the following enhanced for loop effect; for (Object o:object) {//System.out.println (o);//}} @Test public void Testarraylisthandler () throws sqlexception{queryrunner qr = new Queryrunner (Dbmanager.getdatasource ()
		);
		String sql = "Select Id,username,password from User";
		list<object[]> list = qr.query (sql, New Arraylisthandler ());
		System.out.println (List.size ());
		For (object[] o:list) {System.out.println (Arrays.aslist (o)); @Test public void Testcolumnlisthandler () throws sqlexception{queryrunner qr = new Queryrunner (dbmanager.getdat
		Asource ());
		String sql = "Select Id,username,password from User";
		List List = Qr.query (sql, New Columnlisthandler ());
	SYSTEM.OUT.PRINTLN (list); @Test public void Testkeyedhandler () throws sqlexception{queryrunner qr = new Queryrunner (Dbmanager.getdatasource (
		));
		String sql = "Select Id,username,password from User";
		map<object,map<string,object>> Map = qr.query (sql, New Keyedhandler ()); For (map.entry<object,map<string,object>> Me:map.entrySet ()) {map<string,object&Gt
			Innerme = Me.getvalue (); For (map.entry<string,object> Entry:innerMe.entrySet ()) {System.out.println (Entry.getkey () + "=" +
			Entry.getvalue ()); @Test public void Testmaphandler () throws sqlexception{queryrunner qr = new Queryrunner (Dbmanager.getdataso
		Urce ());
		String sql = "Select Id,username,password from User";
		Map map = Qr.query (sql,new maphandler ());
	SYSTEM.OUT.PRINTLN (map); @Test public void Testmaplisthandler () throws sqlexception{queryrunner qr = new Queryrunner (DBMANAGER.GETDATASOURC
		E ());
		String sql = "Select Id,username,password from User";
		List List = Qr.query (Sql,new maplisthandler ());
	SYSTEM.OUT.PRINTLN (list);
 }

}