Dbutils Open source JDBC Class library, simple encapsulation for JDBC (function: Simplifies coding effort without impacting program performance)

Dbutils: Improves program performance and simplifies programming

Three rack Packs

Mysql-connector-java-jar

Commons-dbcp-1.4jar

Commons-pool-1.5.5jar Guide in

-------------------------------------------------------------------------------

Database connection pool: Three open-source rack packages in

Database connection Pool Connection code (its method is in the TESTJDBC () class):

Public Connection Testbasicdatasource () throws exception{
Basicdatasource Datasource=null;

1. Creating an DBCP Data source instance
Datasource=new Basicdatasource ();

2. Specify the required properties for the data source instance
Driver=com.mysql.jdbc.driver
Jdbcurl=jdbc:mysql://localhost:3306/lxn
Datasource.setdriverclassname ("Com.mysql.jdbc.Driver");
Datasource.seturl ("Jdbc:mysql://localhost:3306/lxn");
Datasource.setusername ("root");
Datasource.setpassword ("lxn123");

Specify some optional properties for the data source
Datasource.setinitialsize (10);//Specify the number of connections that are initialized in the database connection pool

Datasource.setmaxactive (50);//Specify the maximum number of connections: the number of connections that can be requested concurrently to the database at the same time

Datasource.setminidle (10);//Specifies the minimum number of connections: the minimum number of connections in the connection pool in the database connection pool idle state

Datasource.setmaxwait (1000*5);//wait for the database connection pool to allocate the maximum time, in milliseconds, out of time, throw an exception

Connection connection=datasource.getconnection ();

return connection;
}

--------------------------------------------------------------------------------------------------------

Dbutils inside the various methods:

/*
* Dbutils:
* */
public class Dbutiles {
/*
* Test the Update method of the Queryrunner class, can make the realization of adding and deleting functions;
The return value of the *queryrunner query method depends on the return value of its Resultsethandller
*
* */
Testjdbc t=new Testjdbc ();

@Test

/*
* Scalarhandler: The result is converted to a numeric value (can be any data type, string, date, etc.) returned
* */
public void Testscalarhandler () throws exception{
Connection Connection=null;
Queryrunner queryrunner=new Queryrunner ();
try {
How to get a database connection pool
Connection=t. Testbasicdatasource ();

String sql= "Select name from Customer where id=?";
New Maphandler () This method outputs a result of a key-value pair;
Object result=
Queryrunner.query (Connection, Sql,new Scalarhandler (), 35);
SYSTEM.OUT.PRINTLN (result);
} catch (Exception e) {
E.printstacktrace ();
}finally {
Close (connection, NULL, NULL);
}
}

========================================================
/*
* maplisthanlder: Convert result set to a list of map
* Map: A record of a query; The key is the property name of the column, the value of the property name of the key, the key is not the alias of the column
* and Maplistbanlder: Returns a collection of corresponding maps for multiple records
* */
public void Testmaplisthandler () throws exception{
Connection connection=null;
Queryrunner queryrunner=new Queryrunner ();
try {
//Get the database connection pool method
Connection=t.testbasicdatasource ();

String sql= "Select Id,name,email,birth from Customer";
//new Maphandler () This method outputs a result of a key-value pair;
list<map<string, object>> result=
Queryrunner.query ( Connection, Sql,new Maplisthandler ());
System.out.println (Result);
} catch (Exception e) {
E.printstacktrace ();
} Finally {
Close (connection, NULL, NULL);
}
}

======================================================
/*
* Maphanlder: Returns the Map object corresponding to the first record in SQL
* Output is a key value pair: Key, is the column property name, value, is the value of the corresponding property name of the key;
* Key is not a column alias,
* */
public void Testmaphandler () throws exception{
Connection Connection=null;
Queryrunner queryrunner=new Queryrunner ();
try {
How to get a database connection pool
Connection=t.testbasicdatasource ();

String sql= "Select Id,name,email,birth from Customer";
New Maphandler () This method outputs a result of a key-value pair;
Map<string, object> map=
Queryrunner.query (Connection, Sql,new Maphandler ());
SYSTEM.OUT.PRINTLN (map);
} catch (Exception e) {
E.printstacktrace ();
}finally {
Close (connection, NULL, NULL);
}
}

==================================================================
/*
* Beanlisthanlder: Converts the result set to a list that is not empty, but may be an empty collection (size (), returned by method 0)
* If the SQL statement can actually find the record, the list holds the object corresponding to the class object that created the Beanlisthanlder
* */

public void Testbeanlisthandler () throws exception{
Connection Connection=null;
Queryrunner queryrunner=new Queryrunner ();
try {
How to get a database connection pool
Connection=t.testbasicdatasource ();

String sql= "Select Id,name,email,birth from Customer";
Beanlisthandler (Customer.class) This method outputs all result sets
List<customer> list=
Queryrunner.query (Connection, sql,new Beanlisthandler (Customer.class));
SYSTEM.OUT.PRINTLN (list);
} catch (Exception e) {
E.printstacktrace ();
}finally {
Close (connection, NULL, NULL);
}
}
============================================================
/*
* Beanhanlder: Convert the first record of the result set to the object corresponding to the class parameter that was transferred when the Beanhanlder object was created
* */
public void Testbeanhandler () throws exception{
Connection Connection=null;
Queryrunner queryrunner=new Queryrunner ();
try {
How to get a database connection pool
Connection=t.testbasicdatasource ();
11:string sql= "Select Id,name,email,birth from Customer where id=?";
String sql= "Select Id,name,email,birth from Customer";
New Beanhandler (Customer.class) This method outputs only one result
Customer customer=
Queryrunner.query (Connection, sql,new Beanhandler (Customer.class));

11:customer customer=
Queryrunner.query (Connection, sql,new Beanhandler (Customer.class), 35);
SYSTEM.OUT.PRINTLN (customer);
} catch (Exception e) {
E.printstacktrace ();
}finally {
Close (connection, NULL, NULL);
}
}
===============================================================
Class Myresultsethandler implements resultsethandler{
@Override
Public Object handle (ResultSet ResultSet) throws SQLException {
List<customer> list=new arraylist<customer> ();
while (Resultset.next ()) {
int Id=resultset.getint (1);
String name=resultset.getstring (2);
String email=resultset.getstring (3);
Date birth=resultset.getdate (4);
Customer Customer=new customer (ID, name, email, (java.sql.Date) birth);
List.add (customer);
}
return list;
}
}
Query method
public void Testquery () throws exception{
Queryrunner queryrunner=new Queryrunner ();
Connection Connection=null;
try {
Get database connection pool, t.testbasicdatasource (); it's a good way to build
Connection=t.testbasicdatasource ();
String sql= "Select Id,name,email,birth from Customer";

The return value of the Queryrunner query method depends on the return value of its Resultsethandller
Object obj=queryrunner.query (connection, SQL, New Myresultsethandler ());
System.out.println (obj);
} catch (Exception e) {
E.printstacktrace ();
}finally {
Close (connection, NULL, NULL);
}
}

=========================================================
Implement additions and deletions to change
public void Testqueryrunnerupdate () throws exception{
1. Creating an implementation class for Queryrunner

Delete statement
String sql= "Delete from the customer where ID in (?,?)";

Connection Connection=null;
Queryrunner queryrunner=new Queryrunner ();

try {
Methods for invoking a database connection pool
Connection=t.testbasicdatasource ();
2. Use its Update method to implement additions and deletions
Queryrunner.update (connection, SQL, 32,33);
} catch (Exception e) {
E.printstacktrace ();
}finally {
Close (connection, NULL, NULL);
}
}

=============================================================
Ways to close resources
public void Close (Connection Connection,
PreparedStatement Preparedstatement,resultset ResultSet) throws exception{
if (resultset!=null) {
Resultset.close ();
}if (preparedstatement!=null) {
Preparedstatement.close ();
}if (connection!=null) {
Connection.close ();
}
}
}

Dbutils Open Source JDBC Class library, simple encapsulation of JDBC (function: Simplifying the coding effort without affecting the performance of the program)