Apache-DBUtils Realization CRUD operation
Apache-DBUtils brief introduction
- 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
- 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 ", "[email protected]", "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" ", "[email protected]", "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","[email protected]",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.