• The DbUtils class (org.apache.commons.dbutils.DbUtils) is responsible for routine work of loading drivers and closing connections.
  Method:
  
  • Close: Check if the parameters provided are NULL, and if not, they close connections, declarations, and result sets.
  • CloseQuietly: Avoid closing connections, declarations, or result sets that are NULL
  • CommitAndCloseQuietly(Connection conn): Used to submit a connection, then close the connection, and do not throw up some SQL exceptions that occur when the connection is closed.
  • LoadDriver (String Drive ClassName): Load and register the JDBC driver and return TRUE if successful.
• The QreryRunner class (org.apache.commons.dbutils.QueryRunner) significantly simplifies the SQL query, and working with ResultSetHandler will greatly reduce the amount of coding.
  
  • query(Connection conn, String sql, Object[] params, ResultSetHandler rsh): Execute a selection query in which the value of the object array is used as a replacement parameter for the query.
  • query(String sql, Object[] params, ResultSetHandler rsh): The method itself does not provide a database connection and executes a selection query in which the value of the object array is used as a replacement parameter for the query.
  • query(Connection conn, String sql, ResultSetHandler rsh): Execute select queries without parameters.
  • update(Connection conn, String sql, Object[] params): Used to perform insert, update, or delete (DML) operations.

• The ResultSetHandler interface (org.apache.commons.dbutils.ResultSethandler) performs processing of a result set object, transforming and processing data into any form for use by other applications.

  • Object handle (java.sql.ResultSet.rs): ResultSet is passed into the method as a parameter, which processes the result set and returns an object.

    ArrayHandler
    
    ArrayListHandler
    
    BeanHandler
    
    BeanListHandler
    
    MapHandler
    
    MapListHandler
    
    ScalarHandler
    

After learning two classes and one interface of this component, the following code is written for reference:

import java.sql.Connection;

import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.BeanListHandler;

public class TestQueryRunner {

         public static void main(String[] args) throws SQLException {

                   // TODO Auto-generated method stub

                   //queryOracle();

                   update();

         }



         private static void update() throws SQLException{

                   QueryRunner runner = new QueryRunner();

                   Connection conn = DBManager.getConnection();

                   //Delete employees with salaries below 5000 in non-manager s

                   String sql = "UPDATE examstudent SET student_name = ? WHERE flow_id = ?";

                   Object [] params = new Object[]{"Jerry", 5000};



                   runner.update(conn, sql, params);

         }



         private static void insert() throws SQLException{

                   QueryRunner runner = new QueryRunner();

                   Connection conn = DBManager.getConnection();

                   //Delete employees with salaries below 5000 in non-manager s

                 String sql = "INSERT INTO examstudent(flow_id, type, id_card, exam_card, student_name, location, grade) VALUES(?, ?, ?, ?, ?, ?, ?)";

                   Object [] params = new Object[]{5000, 6, "ID", "Admission ticket", "Tom", "Beijing", 99};



                   runner.update(conn, sql, params);

         }



         private static void delete() throws SQLException{

                   QueryRunner runner = new QueryRunner();

                   Connection conn = DBManager.getConnection();

                   //Delete employees with salaries below 5000 in non-manager s

                   String sql = "delete from employees " +

                                                "where employee_id not in " +

                                                "     (select distinct d.manager_id from departments d where d.manager_id is not null) " +

                                                "and salary < ?";

                   System.out.println(sql);

                   Object [] params = new Object[]{5000};



                   runner.update(conn, sql, params);

         }



         private static void queryOracle() throws SQLException{

                   QueryRunner runner = new QueryRunner();

                   Connection conn = DBManager.getConnection();

                   //Aliases in oracle can be aliased

                   String sql = "SELECT flow_id flowid, type, id_card idcard, exam_card examcard, student_name studentname, location, grade FROM examstudent";

                   Object obj = runner.query(conn, sql, new BeanListHandler(ExamStudent.class));

                   System.out.println(obj);

         }

         private static void query() throws SQLException {

                   //1. Create an instance of Query Runner

                   QueryRunner runner = new QueryRunner();





                   Connection conn = DBManager.getConnection();

                   String sql = "SELECT id, name, address, phone FROM customers WHERE name LIKE ?";

                   Class type = Customer.class;

                   Object [] params = new Object[]{"%%"};



                   //2. Query operation

                   //conn: database connection needed for query, sql: SQL statement used for query, rsh: how to transform the result set of query, params: array filled with SQL statement parameters

                   Object obj = runner.query(conn, sql, new BeanListHandler(type), params);

                   //System.out.println("^^" + obj);



                   sql = "SELECT flow_id flowid, type, id_card idcard, exam_card examcard, student_name studentname, location, grade FROM examstudent";

                   type = ExamStudent.class;



                   obj = runner.query(conn, sql, new BeanListHandler(ExamStudent.class));

                   System.out.println(obj);

                   }

}

public class JdbcUtil {

private static ComboPooledDataSource dataSource =new ComboPooledDataSource();
public static ComboPooledDataSource getDataSource()
{
return dataSource;
}

}

src Directory c3p0-config.xml

Important Code Extracts

Take count(*) data from the database:

    int topicNum=0;

    QueryRunner runner= new QueryRunner(JdbcUtil.getDataSource());
    String sql ="select count(*) from topic where type_id= ? order by time desc";
    Object[] params={typeId};

    topicNum=(int)(long) runner.query(sql,new ScalarHandler(),params);

    return topicNum;

Take a piece of data in which the field name and class name of the table should correspond to the same:

         Topic newlyTopic=null;
         QueryRunner runner= new QueryRunner(JdbcUtil.getDataSource());
         String sql ="select * from topic where type_id= ? order by time desc";
         Object[] params={typeId};
         newlyTopic= runner.query(sql,new BeanHandler<Topic>(Topic.class),params);
         return newlyTopic;

The field name and class name of the table returned from the collection data List<> should correspond to the same:

         List<Topic> topicList=new ArrayList<Topic>();
         QueryRunner runner= new QueryRunner(JdbcUtil.getDataSource());
         String sql ="select * from topic where type_id= ? order by time desc";
         Object[] params={typeId};
         topicList=runner.query(sql, new BeanListHandler<Topic>(Topic.class),params);
         return topicList;

Insert:

          QueryRunner runner= new QueryRunner(JdbcUtil.getDataSource());
          String sql ="insert into topic(name,author,content,time,type_id) values(?,?,?,?,?)";
          Object[] params={topic.getName(),topic.getAuthor(),topic.getContent(),topic.getTime(),topic.getTypeId()};
          try {
              //Transaction start
              runner.update(sql,params);
              //Transaction submission
          } catch (SQLException e) {
              e.printStackTrace();
             //rollback
             throw e;
         }

To update:

         QueryRunner runner= new QueryRunner(JdbcUtil.getDataSource());
         String sql ="update topic set name=? , content=? , time=? where id= ?";
          Object[] params={topic.getName(),topic.getContent(),topic.getTime(),topic.getId()};
          try {
              //Transaction start
             runner.update(sql,params);
              //Transaction submission
          } catch (SQLException e) {
              e.printStackTrace();
             //rollback
             throw e;
         }

Reprint: Original address