Want to make APP Same thing as WeChat , Can run small programs smoothly ？ | Experience will send you to Xinjiang 、 Huawei 、 Cherry keyboard ！>>>

Apache-DBUtils brief introduction

1. commons-dbutils yes Apache An open source provided by the organization JDBC Tool library , It's right JDBC Simple encapsulation , The cost of learning is extremely low , And use dbutils Can greatly simplify jdbc The amount of coding work , At the same time, it will not affect the performance of the program .

It encapsulates the operation of adding, deleting, modifying and querying the database

1. API Introduce ：

• org.apache.commons.dbutils.QueryRunner
• org.apache.commons.dbutils.ResultSetHandler
• Tool class ：org.apache.commons.dbutils.DbUtils

QueryRunner class

• This class simplifies SQL Inquire about , It is associated with ResultSetHandler Together, you can do most of the database operations , Can greatly reduce the amount of coding .

Constructors

• QueryRunner Class provides two constructors ：
  • Default constructor
  • Need one javax.sql.DataSource To be a constructor of parameters

The main method

• to update
  • public int update(Connection conn, String sql, Object… params) throws SQLException: Used to perform an update （ Insert 、 Update or delete ） operation .
  • …
• Insert
  • public T insert(Connection conn,String sql,ResultSetHandler rsh, Object… params) throws SQLException： Only support INSERT sentence , among rsh - The handler used to create the result object from the ResultSet of auto-generated keys. Return value : An object generated by the handler. Automatically generated key value
  • …
• The batch
  • public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException： INSERT, UPDATE, or DELETE sentence
  • public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException： Only support INSERT sentence
  • …
• Inquire about
  • public Object query(Connection conn, String sql, ResultSetHandler rsh,Object… params) throws SQLException： Perform a query operation , In this query , The value of each element in the object array is used as the replacement parameter of the query statement . The method will handle itself PreparedStatement and ResultSet Create and close .
  • …

test

add to

// Test add 
@Test
public void testInsert() throws Exception {

QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection3();
String sql = "insert into customers(name,email,birth)values(?,?,?)";
int count = runner.update(conn, sql, " He Chengfei ", "he@qq.com", "1992-09-08");
System.out.println(" Added " + count + " Bar record ");
JDBCUtils.closeResource(conn, null);
}
### Delete 
```java
// Test to delete 
@Test
public void testDelete() throws Exception {

QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection3();
String sql = "delete from customers where id < ?";
int count = runner.update(conn, sql,3);
System.out.println(" Deleted " + count + " Bar record ");
JDBCUtils.closeResource(conn, null);
}

ResultSetHandler Interface and implementation class

• This interface is used for processing java.sql.ResultSet, Transform data into another form as required .

• ResultSetHandler Interface provides a separate method ：Object handle (java.sql.ResultSet .rs).

• The main implementation class of the interface ：

  • ArrayHandler： Convert the first row of data in the result set to an object array .
  • ArrayListHandler： Turn each row of data in the result set into an array , Store it in List in .
  • **BeanHandler：** Encapsulate the first row of data in the result set into a corresponding JavaBean In the example .
  • **BeanListHandler：** Encapsulate each row of data in the result set into a corresponding JavaBean In the example , Store in List in .
  • ColumnListHandler： Store the data of a column in the result set in List in .
  • KeyedHandler(name)： Encapsulate each row of data in the result set into a Map in , And then put these map Save one more map in , Its key For the specified key.
  • **MapHandler：** Encapsulate the first row of data in the result set into a Map in ,key Is the column name ,value Is the corresponding value .
  • **MapListHandler：** Encapsulate each row of data in the result set into a Map in , Then store it in List
  • **ScalarHandler：** Query a single value object

test

Use QueryRunner The operation of querying one or more records in a table

// It encapsulates the operation of adding, deleting, modifying and querying the database 
public class QueryRunnerTest {

// Test insert 
@Test
public void testInsert() {

Connection conn = null;
try {

QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql = "insert into customers(name,email,birth)values(?,?,?)";
int insertCount = runner.update(conn, sql, " Cai Xukun: A boy devoted to music in "More than Forever" ", "caixukun@123.com", "1995-08-07");
System.out.println(" Added "+insertCount);
} catch (SQLException throwables) {

throwables.printStackTrace();
} finally {

com.flora.util.JDBCUtils.closeResource(conn,null);
}
}
// Test the query 
// Use BeanHander: yes ResultSetHandler Implementation class of interface , Used to encapsulate a record in a table 
@Test
public void testQuery1() throws SQLException {

Connection connection = null;
try {

QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id = ?";
BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
Customer customer = runner.query(connection, sql, handler, 22);
System.out.println(customer);
} catch (SQLException throwables) {

throwables.printStackTrace();
}finally {

com.flora.util.JDBCUtils.closeResource(connection,null);
}
}
@Test
public void testQuery2() {

Connection connection = null;
try {

QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id < ?";
BeanListHandler<Customer> listHandler = new BeanListHandler<>(Customer.class);
List<Customer> list = runner.query(connection, sql, listHandler, 22);
list.forEach(System.out::println);
} catch (SQLException throwables) {

throwables.printStackTrace();
} finally {

com.flora.util.JDBCUtils.closeResource(connection,null);
}
}
}

Use QueryRunner The operation of querying special values in a table

use ScalarHandler class Used to query special values

 @Test
public void testQuery3() {

Connection connection = null;
try {

QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select count(*)from customers";
ScalarHandler scalarHandler = new ScalarHandler();
Long count = (Long) runner.query(connection, sql, scalarHandler);
System.out.println(count);
} catch (SQLException throwables) {

throwables.printStackTrace();
} finally {

com.flora.util.JDBCUtils.closeResource(connection,null);
}
}

Customize ResultSetHandler Implementation class of

 /*
* Customize ResultSetHandler Implementation class of
*/
@Test
public void testQueryInstance1() throws Exception{

QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id = ?";
ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {

@Override
public Customer handle(ResultSet rs) throws SQLException {

System.out.println("handle");
// return new Customer(1,"Tom","tom@126.com",new Date(123323432L));
if(rs.next()){

int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
return new Customer(id, name, email, birth);
}
return null;
}
};
Customer customer = runner.query(conn, sql, handler, 22);
System.out.println(customer);
com.flora.util.JDBCUtils.closeResource(conn, null);
}

DbUtils Close the connection

• DbUtils ： Provide such as closing a connection 、 load JDBC Driver and other routine tools , All the methods in it are static . The main methods are as follows ：
  • public static void close(…) throws java.sql.SQLException： DbUtils Class provides three overloaded closing methods . These methods check whether the parameters provided are NULL, If not , They shut down Connection、Statement and ResultSet.
  • public static void closeQuietly(…): This kind of method can not only be used in Connection、Statement and ResultSet by NULL Avoid shutting down , You can also hide some of the things that are thrown out of the program SQLEeception.
  • public static void commitAndClose(Connection conn)throws SQLException： The transaction used to commit the connection , Then close the connection
  • public static void commitAndCloseQuietly(Connection conn)： Used to submit connections , Then close the connection , And don't throw out when closing the connection SQL abnormal .
  • public static void rollback(Connection conn)throws SQLException： allow conn by null, Because there is a judgment inside the method
  • public static void rollbackAndClose(Connection conn)throws SQLException
  • rollbackAndCloseQuietly(Connection)
  • public static boolean loadDriver(java.lang.String driverClassName)： This party loads and registers JDBC The driver , If you succeed, go back true. Using this method , You don't need to catch this exception ClassNotFoundException.