Commons-dbutils Learning --queryrunner class and Resultsethandler interface introduction
First, Commons-dbutils.jar
Commons-dbutils.jar is a member of the Apache Commons component, which is an open Source Tool class library for simple encapsulation of JDBC.
Second, the Commons-dbutils.jar Queryrunner class and Resultsethandler interface
- Methods of Queryrunner
- Queryrunner (): Construction method;
- Update (): Perform INSERT, update, delete;
- Query (): Executes the SELECT statement;
- Batch (): Perform batch processing.
- Resultsethandler interface
(1) Beanhandler: A single-line processor, the constructor needs to pass in a class-type parameter to convert a row of results to a JavaBean object of the specified type.
@Test
public void Demo1 () {
DataSource ds = Jdbcutils.getdatasource ();
Queryrunner qr = new Queryrunner (DS);
String sql = "SELECT * from T_user where username=?";
User user = Qr.query (sql,new beanhandler (user) (User.class), "ZS");
SYSTEM.OUT.PRINTLN (user);
}
Results queried (single line): User [Username=zs, Password=zs, age=22, gender= women]
(2) Beanlisthandler: Multi-line processor, convert the result set to List<map<string,object>> The constructor also requires a class-type parameter to convert a row of result sets to a javabean. So many lines are converted into list objects, a bunch of javabean.
@Test
public void Demo2 () throws sqlexception{
Queryrunner qr = new Queryrunner (Jdbcutils.getdatasource ());
String sql = "SELECT * from T_user";
List
(3) Maphandler: A single-line processor that transforms a row of result sets into a map object.
@Test
public void Demo3 () throws sqlexception{
Queryrunner qr = new Queryrunner (Jdbcutils.getdatasource ());
String sql = "SELECT * from T_user WHERE username=?";
Object[] params = {"Cym"};
Map map = qr.query (sql, New Maphandler (), params);
SYSTEM.OUT.PRINTLN (map);
}
Query results (single line): {password=cym, gender=, age=20, USERNAME=CYM}
(4) Maplisthandler: Multi-line processor to convert the result set to list<map<string,object>>.
@Test
public void Demo4 () throws sqlexception{
Queryrunner qr = new Queryrunner (Jdbcutils.getdatasource ());
String sql = "SELECT * from T_user";
List<map<string,object>> MAPL = qr.query (sql, New Maplisthandler ());
System.out.println (MAPL);
}
Query to results (multiple lines):
[{PASSWORD=CYM, gender= man, Age=20, username=cym},
{password=123, gender= man, Age=20, Username= Zhang San},
{Password=zs, gender=, age=22, Username=zs},
{password=ls, gender= man, age=23, username=ls}]
(5) Scalarhandler: Single row, often with count ().
public void Fun4 () throws sqlexception{
Queryrunner qr = new Queryrunner (Jdbcutils.getdatasource ());
String sql = "SELECT count () from T_user";
Number number = (number) qr.query (SQL, New Scalarhandler ());
int i = Number.intvalue ();
System.out.println (i);
}
The result of the query (single-line): 4.
It is important to note that the type queried by query requires the conversion of number, the types returned by different versions of the jar package are differential, and the return type of the Oracle driver package is also different (BigInteger), in order to avoid unnecessary problems after updating the driver package in the future, We need to do something, these numeric types have a common parent class of number, so you can convert:
Number number = (number) qr.query (SQL, New Scalarhandler ());
Convert to the appropriate type as needed
int i = Number.intvalue ();
Commons-dbutils learning--queryrunner class and Resultsethandler interface introduction