Dbutils is used basically, and dbutils is used

Source: Internet
Author: User

Dbutils is used basically, and dbutils is used

Dbutils queries mainly use the query method, adding, modifying, and deleting all the update methods.

You only need to create different implementation class objects of the ResultSetHandler interface to obtain the desired query result.

Next we will explain some of the frequently used:

The following code throws an exception. To make it simple, I have removed the exception here.

ArrayHandler class: encapsulate the 'first row' record of the result set into an array,
Test code:

QueryRunner qr = new QueryRunner(dataSource);    String sql = "select * from user";    Object[] objArr = qr.query(sql, new ArrayHandler());}
View Code


ArrayListHandler class: encapsulate each row of the result set into an array, and then put these arrays into a collection to return
Test code:

QueryRunner qr = new QueryRunner(dataSource);    String sql = "select * from user";    List<Object[]> list = qr.query(sql, new ArrayListHandler());}
View Code


BeanHandler class: the 'first row' record of the result set is returned as a javaBean object (premise: the field name of the result set (aliases can be used) is consistent with the attribute name of the javaBean object)
Test code:

QueryRunner qr = new QueryRunner(dataSource);String sql = "select * from user";User user = qr.query(sql, new BeanHandler<User>(User.class));
View Code


BeanListHandler class: encapsulate each row of the result set into a javaBean object, and then put these objects into a List set to return
Test code:

QueryRunner qr = new QueryRunner();String sql = "select * from user";List<User> userList = qr.query(conn, sql, new BeanListHandler<User>(User.class));
View Code


ScalarHandler class: return a value in the first row of the 'result set', depending on the parameters of the constructor. the return value type depends on the field type in the table.
Three constructor methods are available for aggregate queries.
New ScalarHandler (); returns the value of the first column in the first row of the result set.
New ScalarHandler (int columnIndex); returns the value of the columnIndex column in the first row of the result set (the column starts from 1)
New ScalarHandler (String columnName); returns the value of columnName in the first row of the result set.
Test code:

QueryRunner qr = new QueryRunner (); String SQL = "select id, username, Hober as hob from user"; Integer obj1 = qr. query (conn, SQL, new ScalarHandler <Integer> (); // The value of String obj2 = qr in the first column of the First row in the result set. query (conn, SQL, new ScalarHandler <String> (2); // The value of String obj3 = qr in Column 2nd of the first row of the result set. query (conn, SQL, new ScalarHandler <String> ("hob") // the name of the first row in the result set is hob.
View Code


ColumnListHandler class: stores data from a column in the result set to the List. Similar to ScalarHandler, there are three constructor methods.
Test code:

QueryRunner qr = new QueryRunner();String sql = "select * from user";// List<Integer> idList = qr.query(conn, sql, new ColumnListHandler<Integer>());// List<String> idList = qr.query(conn, sql, new ColumnListHandler<String>("hobby"));List<String> list = qr.query(conn, sql, new ColumnListHandler<String>(4));
View Code


MapHandler: encapsulate the first row of data in the result set into a Map. The key is the column name, and the value is the corresponding value.
Test code:

QueryRunner qr = new QueryRunner();String sql = "select * from user";Map<String, Object> map = qr.query(conn, sql, new MapHandler());
View Code


MapListHandler: encapsulate each row of data in the result set in a Map, and store the data in the List.
Test code:

QueryRunner qr = new QueryRunner();String sql = "select * from user";List<Map<String, Object>> listMap = qr.query(conn, sql, new MapListHandler());
View Code

Here are examples without parameters. If there are parameters, what are the parameters used in SQL? And then pass the parameters in order using the query method with Parameters

For more information about how to connect to a database, see the following article: how to connect c3p0 to a database:

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.