< 15 >jdbc_ using Dbutils for updates, query operations

To be Continued ...

Dbutilstest.java

Import java.sql.Connection;
Import Java.sql.Date;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.ArrayList;
Import java.util.List;
Import Java.util.Map;

Import Org.apache.commons.dbutils.QueryRunner;
Import Org.apache.commons.dbutils.ResultSetHandler;
Import Org.apache.commons.dbutils.handlers.BeanHandler;
Import Org.apache.commons.dbutils.handlers.BeanListHandler;
Import Org.apache.commons.dbutils.handlers.MapHandler;
Import Org.apache.commons.dbutils.handlers.MapListHandler;
Import Org.apache.commons.dbutils.handlers.ScalarHandler;
Import Org.junit.Test;

Import Com.kk.jdbc.JDBCTools;

public class Dbutilstest {

/*
* Scalarhandler: Turns the result set to a numeric value (can be any basic data type and string, date, etc.) returned
* */
@Test
public void Testscalarhandler () {

Connection con = null;
try {

Con=jdbctools.getconnection ();
String sql = "SELECT count (ID) from customers";
Object result=qr.query (Con, SQL, New Scalarhandler ());
SYSTEM.OUT.PRINTLN (result);
} catch (Exception e) {
E.printstacktrace ();
}finally{
Jdbctools.release (null, NULL, con);
}
}


/*
* Maplisthandler: Convert the result set to a list of maps,
* Map corresponds to a record of the query, key: The column name of the SQL query (not the alias of the column), value: The value of the column
* Maplisthandler returns a collection of maps for multiple records
* */
@Test
public void Testmaplisthandler () {

Connection con = null;
try {

Con=jdbctools.getconnection ();
String sql = "SELECT name from Customers where id=?";
list<map<string,object>> result = (list<map<string, object>>) qr.query (Con, SQL, new Maplisthandler ());
SYSTEM.OUT.PRINTLN (result);
} catch (Exception e) {
E.printstacktrace ();
}finally{
Jdbctools.release (null, NULL, con);
}
}

/*
* Maphandler: Returns the Map object corresponding to the first record of SQL;
* Key: The column name of the SQL query (not the alias of the column)
* Value: The value of the column
* */
@Test
public void Testmaphandler () {

Connection con = null;
try {

Con=jdbctools.getconnection ();
String sql = "Select Id,name,email,birth from Customers";
map<string,object> result = (map<string, object>) qr.query (Con, SQL, New Maphandler ());
SYSTEM.OUT.PRINTLN (result);
} catch (Exception e) {
E.printstacktrace ();
}finally{
Jdbctools.release (null, NULL, con);
}
}

/*
* Beanlisthandler: Converts the result set to a list that is not empty, but may be an empty collection (the size () method returns 0)
* If the SQL statement can query the record, the list holds the object corresponding to the class object that created the Beanlisthandler.
* */
@Test
public void Testbeanlisthandler () {

Connection con = null;
try {

Con=jdbctools.getconnection ();
String sql = "Select Id,name,email,birth from Customers";
List<customer> customers = (list<customer>) qr.query (Con, SQL, new Beanlisthandler (Customer.class));
SYSTEM.OUT.PRINTLN (Customers);
} catch (Exception e) {
E.printstacktrace ();
}finally{
Jdbctools.release (null, NULL, con);
}
}

/*
* Beanhandler: Convert the first record of the result set to the object corresponding to the class parameter passed in when creating the Beanhandler object
* */
@Test
public void Testbeanhandler () {

Connection con = null;
try {

Con=jdbctools.getconnection ();
String sql = "Select Id,name,email,birth from Customers where id=?";
Customer customer= (Customer) qr.query (Con, SQL, new Beanhandler (Customer.class), 6);
SYSTEM.OUT.PRINTLN (customer);
} catch (Exception e) {
E.printstacktrace ();
}finally{
Jdbctools.release (null, NULL, con);
}
}



1. Create a Queryrunner implementation class
Queryrunner qr = new Queryrunner ();

/*
* The return value of the Queryrunner query method depends on the return value of the handle method of the Resultsethandler parameter
* */
@Test
public void Testquery () {

Connection con = null;
Class Myresultsethandler implements resultsethandler{

@Override
Public Object handle (ResultSet Rs) throws SQLException {

List<customer> customers=new arraylist<> ();
while (Rs.next ()) {
Integer Id=rs.getint (1);
String name=rs.getstring (2);
String email=rs.getstring (3);
Date birth=rs.getdate (4);

Customer Customer=new Customer (Id,name,email,birth);
Customers.add (customer);
}
return customers;
}
}

try {
con = jdbctools.getconnection ();
String sql = "Select Id,name,email,birth from Customers";
Object ob=qr.query (Con, SQL, New Myresultsethandler ());
System.out.println ("Cheriong:" +ob);
} catch (Exception e) {
E.printstacktrace ();
}finally{
Jdbctools.release (null, NULL, con);
}
}

/**
* Test the Update method of the Queryrunner class (INSERT, UPDATE, DELETE)
*/
@Test
public void Testqueryrunnerupdate () {

2. Using the Update method
String sql = "Delete from customers where ID in (?,?)";
Connection con = null;

try {
con = jdbctools.getconnection ();
Qr.update (Con, SQL, 2, 5);

} catch (Exception e) {
E.printstacktrace ();
} finally {
Jdbctools.release (null, NULL, con);
}
}
}

< 15 >jdbc_ use Dbutils for updates, query operations