Java-dbutils's Exercises

Dbutils is a practical tool for database operations in Java programming, small and simple.
The dbutils encapsulates the operation of JDBC, simplifies JDBC operations, and can write less code.
Dbutils three core functions of the introduction
The API for SQL statement operations is provided in Queryrunner.
The Resultsethandler interface, which defines how the result set is encapsulated after a select operation.
The Dbutils class, which is a tool class that defines how to close resources and transaction processing

Package Cn.incast.homework31dbutils;import Java.math.bigdecimal;import Java.sql.connection;import Java.sql.sqlexception;import Java.util.list;import Org.apache.commons.dbutils.queryrunner;import Org.apache.commons.dbutils.handlers.arrayhandler;import Org.apache.commons.dbutils.handlers.ArrayListHandler; Import Org.apache.commons.dbutils.handlers.beanhandler;import Org.apache.commons.dbutils.handlers.beanlisthandler;import Org.apache.commons.dbutils.handlers.columnlisthandler;import Org.apache.commons.dbutils.handlers.ScalarHandler;    API//UPDATE (Connection conn, String sql, Object ... params) for SQL statement operations in public class Dbutilsdemo {//Queryrunner Query (Connection conn, String sql, resultsethandler<t> rsh, Object ...//params)//Resultsethandler interface for    How to encapsulate the result set after the semantic select operation.      Dbutils class, which is a tool class that defines a method for closing resources and transactions public static void Main (string[] args) throws Exception {//insert ();//     Arrayhandler ();//Arraylisthandler ();//Arraylisthandler ();// Beanhandler ();//Beanlisthandler ();//Columnlisthandler ();    Scalarhandler (); private static void Scalarhandler () throws Exception {//scalarhandler: It is used for single data.        For example Select COUNT (*) from table operation Connection con = jdbcutils.getconnection ();        Queryrunner qr = new Queryrunner (); String sql = "Select AVG (Dscore), dclassroom from Test GROUP by Dclassroom"; Even if a query statement can query multiple rows of data, it takes only the first row of string sql= "select AVG (dscore) from Test";//String sql= "select sum (Dscore), AVG (Dsco  RE), dclassroom from Test ";        Experiment Summary: It can only take out the number of the first column in the first row String score = string.valueof (Qr.query (Con, SQL, New Scalarhandler<integer> ()));    SYSTEM.OUT.PRINTLN (score);        private static void Columnlisthandler () throws exception{//columnlisthandler: Encapsulates the field value of the specified column in the result set into a list collection        Connection con = jdbcutils.getconnection ();        Queryrunner qr =new queryrunner (); String sql= "Select Dname from Test WHERE dscore>?";       Note: Only one column is queried list<string> list =qr.query (Con, sql,new columnlisthandler<string> (), 50);        for (String s:list) {System.out.println (s); }} private static void Beanlisthandler () throws exception{//beanlisthandler: encapsulates each record in the result set into the specified JavaBean, and these Javabe        An in the package to list collection Connection con = jdbcutils.getconnection ();        Queryrunner qr = new Queryrunner ();                String sql = "SELECT * from Test";        list<student> list =qr.query (con, SQL, new Beanlisthandler<student> (Student.class)); for (Student s:list) {System.out.println (S.getid () + "\ T" +s.getdname () + "\ T" +s.getdage () + "\ T" +s.getdclassroom () + "\ t        "+s.getdscore ());         }} private static void Beanhandler () throws Exception {//beanhandler: Encapsulates the first record in the result set into a specified JavaBean        Connection con = jdbcutils.getconnection ();        Queryrunner qr = new Queryrunner ();        String sql= "SELECT * from Test where dname=?";      Object[] param= {"Naruto"};  Student stu= qr.query (Con, SQL, new Beanhandler<student> (Student.class), param);    System.out.println (Stu);        The private static void Arraylisthandler () throws exception{//encapsulates each record in the result set into a object[] array, encapsulating the arrays in the list collection.        Connection con = jdbcutils.getconnection ();        Queryrunner qr = new Queryrunner ();        String sql = "SELECT * from Test";        list<object[]> list =qr.query (con, SQL, New Arraylisthandler ());            For (object[] array:list) {for (Object Obj:array) {System.out.print (obj+ "\ t");        } System.out.println (); }} public static void Insert () throws Exception {try {Connection con = jdbcutils.getconnection (            );            Queryrunner qr = new Queryrunner ();            String sql = "INSERT INTO Test (dname,dscore,dclassroom,dage) VALUES (?,?,?,?)";            int line = Qr.update (con, SQL, "Naruto", +, "Class A", 14);        System.out.println (line); } catch (SQLException e) {e.printstacktrace ();        throw new RuntimeException ("New Failure"); }} public static void Arrayhandler () throws exception{//arrayhandler: encapsulates the first record in the result set into a object[] array, where each element of the array is the record        The value of each field in the recording is Connection con = jdbcutils.getconnection ();        Queryrunner qr = new Queryrunner ();        Object[] params = {"Xiaoming"};        String sql= "SELECT * from Test where dname=?";        object[] Objarray = Qr.query (con, SQL, New Arrayhandler (), params);        for (Object O:objarray) {System.out.print (o+ ""); }                    }}

Connection pool

In real development, "Get Connected" or "release resources" is a very consuming system resources of two processes, in order to solve such performance problems, usually we use the connection pooling technology, to share the connection connection. So that we don't have to create the connection each time, release the connection, these operations are given to the connection pool

Connection Pooling Overview

? Concept
Use pools to manage connection so that you can reuse connection. With the pool, we don't have to create connection ourselves, but instead we get the connection object through the pool. When connection is finished, calling connection's close () method does not actually close the connection, but instead connection "returns" to the pool. The pool will be able to use this connection object again.
? Specification
Java provides a common interface for database connection pooling: Javax.sql.DataSource, each vendor needs to have its own connection pool implement this interface. So the application can easily switch the connection pool of different vendors!
Common connection pools: DBCP, c3p0.

DBCP Connection Pool

DBCP is also an open source connection pool, one of the Apache common members, also more common in enterprise development, Tomcat built-in connection pool

The Jdbcutils tool class that you write can be changed to the following

Java-dbutils's Exercises