Dbutils 简明教程
Apache Commons DBUtils - Overview
Apache Commons DbUtils 库是一组相当小的类，旨在更容易地处理 JDBC 调用而不泄漏资源，且具有更清晰的代码。由于 JDBC 资源清理相当繁琐且容易出错，因此 DBUtils 类有助于抽象出样板代码，以便开发人员仅能专注于与数据库相关的操作。
Apache Commons DbUtils library is a quite small set of classes, which are designed to make easier JDBC call processing without resource leak and to have cleaner code. As JDBC resource cleanup is quite tedious and error prone, DBUtils classes helps to abstract out the boiler plate code, so that the developers can focus on database related operations only.
Advantages of DBUtils
使用 Apache Commons DBUtils 的优点如下：
The advantages of using Apache Commons DBUtils are explained below −
-
No Resource Leakage − DBUtils classes ensures that no resource leakage happen.
-
Clean & Clear code − DBUtils classes provides clean and clear code to do the database operations without any need to write a cleanup or resource leak prevention code.
-
Bean Mapping − DBUtils class supports to automatically populate javabeans from a result set.
DBUtils Design Principles
Apache Commons DBUtils 的设计原则如下：
The design principles of Apache Commons DBUtils are as follows −
-
Small − DBUtils library is very small in size with fewer classes, so that it is easy to understand and use.
-
Transparent − DBUtils library is not doing much work behind the scenes. It simply takes query and executes.
-
Fast − DBUtils library classes do not create many background objects and is quite fast in database operation executions.
Apache Commons DBUtils - Environment Setup
要开始使用 DBUtils 进行开发，您应按照以下所示步骤设置 DBUtils 环境。我们假设您使用的是 Windows 平台。
To start developing with DBUtils, you should setup your DBUtils environment by following the steps shown below. We assume that you are working on a Windows platform.
Install Java
从 Java Official Site 安装 J2SE 开发工具包 5.0 (JDK 5.0)。
Install J2SE Development Kit 5.0 (JDK 5.0) from Java Official Site.
确保以下环境变量按照如下所述进行设置 −
Make sure following environment variables are set as described below −
-
JAVA_HOME − This environment variable should point to the directory where you installed the JDK, e.g. C:\Program Files\Java\jdk1.5.0.
-
CLASSPATH − This environment variable should have appropriate paths set, e.g. C:\Program Files\Java\jdk1.5.0_20\jre\lib.
-
PATH − This environment variable should point to appropriate JRE bin, e.g. C:\Program Files\Java\jre1.5.0_20\bin.
您的计算机可能已设置这些变量，但为了确保万无一失，这里列出了检查方法。
It is possible you have these variable set already, but just to make sure here’s how to check.
-
Go to the control panel and double-click on System. If you are a Windows XP user, it is possible you have to open Performance and Maintenance before you will see the System icon.
-
Go to the Advanced tab and click on the Environment Variables.
-
Now check if all the above mentioned variables are set properly.
Install Database
当然，您将需要最重要的实际运行数据库，其中包含您可以查询和修改的表。
The most important thing you will need, of course is an actual running database with a table that you can query and modify.
安装最适合于你的数据库。你有很多选择，最常见的是 −
Install a database that is most suitable for you. You can have plenty of choices and most common are −
-
MySQL DB: MySQL is an open source database. You can download it from MySQL Official Site. We recommend downloading the full Windows installation. In addition, download and install MySQL Administrator as well as MySQL Query Browser. These are GUI based tools that will make your development much easier. Finally, download and unzip MySQL Connector/J (the MySQL JDBC driver) in a convenient directory. For the purpose of this tutorial we will assume that you have installed the driver at C:\Program Files\MySQL\mysql-connector-java-5.1.8. Accordingly, set CLASSPATH variable to C:\Program Files\MySQL\mysql-connector-java-5.1.8\mysql-connector-java-5.1.8-bin.jar. Your driver version may vary based on your installation.
-
PostgreSQL DB: PostgreSQL is an open source database. You can download it from PostgreSQL Official Site. The Postgres installation contains a GUI based administrative tool called pgAdmin III. JDBC drivers are also included as part of the installation.
-
Oracle DB − Oracle DB is a commercial database sold by Oracle . We assume that you have the necessary distribution media to install it. Oracle installation includes a GUI based administrative tool called Enterprise Manager. JDBC drivers are also included as a part of the installation.
Install Database Drivers
最新的 JDK 包含 JDBC-ODBC 桥接驱动程序，使大多数开放数据库连接 (ODBC) 驱动程序可通过 JDBC API 供程序员使用。
The latest JDK includes a JDBC-ODBC Bridge driver that makes most Open Database Connectivity (ODBC) drivers available to programmers using the JDBC API.
现在，大多数数据库供应商在数据库安装中都提供了适当的 JDBC 驱动程序。因此，您无需担心这一部分。
Now a days, most of the Database vendors are supplying appropriate JDBC drivers along with Database installation. So, you should not worry about this part.
Set Database Credential
在本教程中，我们将使用 MySQL 数据库。当您安装上述任意数据库时，其管理员 ID 设置为 root ，并允许设置您选择的密码。
For this tutorial we are going to use MySQL database. When you install any of the above database, its administrator ID is set to root and gives provision to set a password of your choice.
使用 root ID 和密码，您可以创建另一个用户 ID 和密码，也可以为您的 JDBC 应用程序使用 root ID 和密码。
Using root ID and password you can either create another user ID and password, or you can use root ID and password for your JDBC application.
有各种数据库操作，例如数据库创建和删除，这需要管理员 ID 和密码。
There are various database operations like database creation and deletion, which would need administrator ID and password.
在 JDBC 教程的其余部分中，我们将使用 ID 为 username 、密码为 password 的 MySQL 数据库。
For rest of the JDBC tutorial, we would use MySQL Database with username as ID and password as password.
如果您没有足够权限创建新用户，那么您可以请求数据库管理员 (DBA) 为您创建用户 ID 和密码。
If you do not have sufficient privilege to create new users, then you can ask your Database Administrator (DBA) to create a user ID and password for you.
Create Database
要创建 emp 数据库，请使用以下步骤 −
To create the emp database, use the following steps −
Step 1
打开 Command Prompt 并更改到安装目录，如下所示 −
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>Note: mysqld.exe 的路径可能会根据 MySQL 在你的系统上的安装位置而有所不同。你还可以查看有关如何启动和停止你的数据库服务器的文件说明。
Note: The path to mysqld.exe may vary depending on the install location of MySQL on your system. You can also check documentation on how to start and stop your database server.
Create Table
若要在 emp 数据库中创建 Employees 表，请使用以下步骤 −
To create the Employees table in emp database, use the following steps −
Step 1
打开 Command Prompt 并更改到安装目录，如下所示 −
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>Create Data Records
最后，在 Employee 表中创建一些记录，如下所示 −
Finally you create few records in Employee table as follows −
mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)
mysql>要全面了解 MySQL 数据库，请学习 MySQL Tutorial 。
For a complete understanding on MySQL database, study the MySQL Tutorial.
Download Commons DBUtils Archive
从 commons-dbutils-1.7-bin.zip 、MySql 连接器 mysql-connector-java-5.1.28-bin.jar 、Apache Commons DBCP commons-dbcp2-2.1.1-bin.zip 、Apache Commons Pool commons-pool2-2.4.3-bin.zip 、Apache Commons Logging commons-logging-1.2-bin.zip 下载 Apache Common DBUtils jar 文件的最新版本。在编写本教程时，我们已下载 commons-dbutils-1.7-bin.zip、mysql-connector-java-5.1.28-bin.jar、commons-dbcp2-2.1.1-bin.zip、commons-pool2-2.4.3-bin.zip、commons-logging-1.2-bin.zip 并将其复制到 C:\>Apache 文件夹中。
Download the latest version of Apache Common DBUtils jar file from commons-dbutils-1.7-bin.zip, MySql connector mysql-connector-java-5.1.28-bin.jar , Apache Commons DBCP commons-dbcp2-2.1.1-bin.zip, Apache Commons Pool commons-pool2-2.4.3-bin.zip and , Apache Commons Logging commons-logging-1.2-bin.zip . At the time of writing this tutorial, we have downloaded commons-dbutils-1.7-bin.zip, mysql-connector-java-5.1.28-bin.jar, commons-dbcp2-2.1.1-bin.zip, commons-pool2-2.4.3-bin.zip, commons-logging-1.2-bin.zip and copied it into C:\>Apache folder.
OS |
Archive name |
Windows |
commons-dbutils-1.7-bin.zip |
Linux |
commons-dbutils-1.7-bin.tar.gz |
Mac |
commons-dbutils-1.7-bin.tar.gz |
Set Apache Common DBUtils Environment
设置 APACHE_HOME 环境变量，以指向计算机上存储 Apache jar 的基本目录位置。假设我们在各种操作系统上将 commons-dbutils-1.7-bin.zip 解压到 Apache 文件夹中，如下所示。
Set the APACHE_HOME environment variable to point to the base directory location where Apache jar is stored on your machine. Assuming, we’ve extracted commons-dbutils-1.7-bin.zip in Apache folder on various Operating Systems as follows.
OS |
Output |
Windows |
Set the environment variable APACHE_HOME to C:\Apache |
Linux |
export APACHE_HOME=/usr/local/Apache |
Mac |
export APACHE_HOME=/Library/Apache |
Set CLASSPATH Variable
设置 CLASSPATH 环境变量，以指向 Common IO jar 位置。假设你在各种操作系统上将 commons-dbutils-1.7-bin.zip 存储在了 Apache 文件夹中，如下所示。
Set the CLASSPATH environment variable to point to the Common IO jar location. Assuming, you have stored commons-dbutils-1.7-bin.zip in Apache folder on various Operating Systems as follows.
OS |
Output |
Windows |
Set the environment variable CLASSPATH to %CLASSPATH%;%APACHE_HOME%\commons-dbutils-1.7.jar;mysql-connector-java-5.1.28.jar;commons-dbcp2-2.1.1.jar;commons-pool2-2.4.3.jar;commons-logging-1.2.jar; |
Linux |
export CLASSPATH=$CLASSPATH:$APACHE_HOME/commons-dbutils-1.7.jar:mysql-connector-java-5.1.28.jar:commons-dbcp2-2.1.1:commons-pool2-2.4.3.jar:commons-logging-1.2.jar. |
Mac |
export CLASSPATH=$CLASSPATH:$APACHE_HOME/commons-dbutils-1.7.jar:mysql-connector-java-5.1.28:commons-dbcp2-2.1.1.jar:commons-pool2-2.4.3.jar;commons-logging-1.2.jar. |
你现在就可以开始试验 DBUtils 了。下一章将为你提供有关 DBUtils 编程的示例。
Now you are ready to start experimenting with DBUtils. Next chapter gives you a sample example on DBUtils Programming.
Apache Commons DBUtils - First Application
本章提供了一个使用 DBUtils 库创建简单 JDBC 应用程序的示例。这将向您展示如何打开数据库连接、执行 SQL 查询和显示结果。
This chapter provides an example of how to create a simple JDBC application using DBUtils library. This will show you, how to open a database connection, execute a SQL query, and display the results.
本模板示例中提到的所有步骤都将在本教程的后续章节中进行解释。
All the steps mentioned in this template example, would be explained in subsequent chapters of this tutorial.
Creating JDBC Application
构建一个 JDBC 应用程序涉及以下六个步骤 -
There are following six steps involved in building a JDBC application −
-
Import the packages − Requires that you include the packages containing the JDBC classes which are needed for database programming. Most often, using import java.sql.* will suffice.
-
Register the JDBC driver − Requires that you initialize a driver, so you can open a communication channel with the database.
-
Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.
-
Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to the database.
-
Extract data from result set − Requires that you use the appropriate ResultSet.getXXX() method to retrieve the data from the result set.
-
Clean up the environment − Requires explicitly closing all the database resources versus relying on the JVM’s garbage collection.
Sample Code
当您需要在未来创建自己的 JDBC 应用程序时，该示例可以作为模板。
This sample example can serve as a template, when you need to create your own JDBC application in the future.
此示例代码是基于前一章节中完成的环境与数据库设置编写的。
This sample code has been written based on the environment and database setup done in the previous chapter.
在 MainApp.java 中复制并粘贴以下示例，按如下方式编译并运行 −
Copy and paste the following example in MainApp.java, compile and run as follows −
MainApp.java
MainApp.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
//Step 1: Register JDBC driver
DbUtils.loadDriver(JDBC_DRIVER);
//Step 2: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//Step 3: Create a ResultSet Handler to handle Employee Beans
ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(Employee.class);
try {
Employee emp = queryRunner.query(conn,
"SELECT * FROM employees WHERE first=?", resultHandler, "Sumit");
//Display values
System.out.print("ID: " + emp.getId());
System.out.print(", Age: " + emp.getAge());
System.out.print(", First: " + emp.getFirst());
System.out.println(", Last: " + emp.getLast());
} finally {
DbUtils.close(conn);
}
}
}Employee.java
Employee.java
程序如下所示 −
The program is given below −
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}现在让我们如下编译上述示例：
Now let us compile the above example as follows −
C:\>javac MainApp.java Employee.java
C:\>运行 MainApp ，它会生成以下结果 −
When you run MainApp, it produces the following result −
C:\>java MainApp
Connecting to database...
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:\>Apache Commons DBUtils - Create Query
以下示例将演示如何使用 DBUtils 借助 Insert 查询来创建记录。我们将在 Employees 表中插入一条记录。
The following example will demonstrate how to create a record using Insert query with the help of DBUtils. We will insert a record in Employees Table.
Syntax
创建查询的语法如下 −
The syntax to create a query is given below −
String insertQuery ="INSERT INTO employees(id,age,first,last) VALUES (?,?,?,?)";
int insertedRecords = queryRunner.update(conn, insertQuery,104,30, "Sohan","Kumar");其中，
Where,
-
insertQuery − Insert query having placeholders.
-
queryRunner − QueryRunner object to insert employee object in database.
为了理解与 DBUtils 相关的以上概念，我们来编写一个示例，该示例将运行一个插入查询。为了编写示例，我们来创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run an insert query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
DbUtils.loadDriver(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
try {
int insertedRecords = queryRunner.update(conn,
"INSERT INTO employees(id,age,first,last) VALUES (?,?,?,?)",
104,30, "Sohan","Kumar");
System.out.println(insertedRecords + " record(s) inserted");
} finally {
DbUtils.close(conn);
}
}
}一旦完成创建源文件，我们就来运行应用程序。如果你的应用程序一切正常，它将打印出以下消息 −
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message −
1 record(s) inserted.Apache Commons DBUtils - Read Query
以下示例将演示如何使用 DBUtils 借助 Read 查询来读取记录。我们将读取 Employees 表中的记录。
The following example will demonstrate how to read a record using Read query with the help of DBUtils. We will read a record from Employees Table.
Syntax
读取查询的语法如下 −
The syntax for read query is mentioned below −
ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(Employee.class);
Employee emp = queryRunner.query(conn, "SELECT * FROM employees WHERE first=?", resultHandler, "Sumit");其中，
Where,
-
resultHandler − ResultSetHandler object to map the result set to Employee object.
-
queryRunner − QueryRunner object to read an employee object from database.
为了理解与 DBUtils 相关的以上概念，我们来编写一个示例，该示例将运行一个读取查询。为了编写示例，我们来创建一个示例应用程序。
To understand the above mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
//Step 1: Register JDBC driver
DbUtils.loadDriver(JDBC_DRIVER);
//Step 2: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//Step 3: Create a ResultSet Handler to handle Employee Beans
ResultSetHandler<Employee> resultHandler =
new BeanHandler<Employee>(Employee.class);
try {
Employee emp = queryRunner.query(conn,
"SELECT * FROM employees WHERE id=?", resultHandler, 104);
//Display values
System.out.print("ID: " + emp.getId());
System.out.print(", Age: " + emp.getAge());
System.out.print(", First: " + emp.getFirst());
System.out.println(", Last: " + emp.getLast());
} finally {
DbUtils.close(conn);
}
}
}一旦完成创建源文件，我们就来运行应用程序。如果你的应用程序一切正常，它将打印出以下消息：
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message:
ID: 104, Age: 30, First: Sohan, Last: KumarApache Commons DBUtils - Update Query
以下示例将演示如何使用 DBUtils 借助 Update 查询来更新记录。我们将在 Employees 表中更新一条记录。
The following example will demonstrate how to update a record using Update query with the help of DBUtils. We’ll update a record in Employees Table.
Syntax
更新查询的语法如下 −
The syntax for update query is as follows −
String updateQuery = "UPDATE employees SET age=? WHERE id=?";
int updatedRecords = queryRunner.update(conn, updateQuery, 33,104);其中，
Where,
-
updateQuery − Update query having placeholders.
-
queryRunner − QueryRunner object to update employee object in database.
为了了解与 DBUtils 相关的上述概念，让我们编写一个将运行更新查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above mentioned concepts related to DBUtils, let us write an example which will run an update query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
DbUtils.loadDriver(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
try {
int updatedRecords = queryRunner.update(conn,
"UPDATE employees SET age=? WHERE id=?", 33,104);
System.out.println(updatedRecords + " record(s) updated.");
} finally {
DbUtils.close(conn);
}
}
}一旦完成创建源文件，我们就来运行应用程序。如果你的应用程序一切正常，它将打印出以下消息 −
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message −
1 record(s) updated.Apache Commons DBUtils - Delete Query
下面的示例将演示如何借助 DBUtils 使用删除查询删除记录。我们将删除 Employees 表中的记录。
The following example will demonstrate how to delete a record using Delete query with the help of DBUtils. We will delete a record in Employees Table.
Syntax
删除查询的语法如下所示 −
The syntax for delete query is mentioned below −
String deleteQuery = "DELETE FROM employees WHERE id=?";
int deletedRecords = queryRunner.delete(conn, deleteQuery, 33,104);其中，
Where,
-
deleteQuery − DELETE query having placeholders.
-
queryRunner − QueryRunner object to delete employee object in database.
为了了解与 DBUtils 相关的上述概念，让我们编写一个将运行删除查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a delete query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
DbUtils.loadDriver(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
try {
int deletedRecords = queryRunner.update(conn,
"DELETE from employees WHERE id=?", 104);
System.out.println(deletedRecords + " record(s) deleted.");
} finally {
DbUtils.close(conn);
}
}
}一旦完成创建源文件，我们就来运行应用程序。如果你的应用程序一切正常，它将打印出以下消息 −
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message −
1 record(s) deleted.QueryRunner interface
org.apache.commons.dbutils.QueryRunner 类是 DBUtils 库中的核心类。它用可插入的 ResultSet 处理策略执行 SQL 查询。这个类是线程安全的。
The org.apache.commons.dbutils.QueryRunner class is the central class in the DBUtils library. It executes SQL queries with pluggable strategies for handling ResultSets. This class is thread safe.
Class Declaration
以下是 org.apache.commons.dbutils.QueryRunner 类的声明：
Following is the declaration for org.apache.commons.dbutils.QueryRunner class −
public class QueryRunner
extends AbstractQueryRunnerUsage
-
Step 1 − Create a connection object.
-
Step 2 − Use QueryRunner object methods to make database operations.
Example
以下示例将演示如何使用 QueryRunner 类读取记录。我们将读取 employee 表中可用的记录之一。
Following example will demonstrate how to read a record using QueryRunner class. We’ll read one of the available record in employee Table.
Syntax
ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(Employee.class);
Employee emp =
queryRunner.query(conn, "SELECT * FROM employees WHERE first=?", resultHandler, "Sumit");其中，
Where,
-
resultHandler − ResultSetHandler object to map result set to Employee object.
-
queryRunner − QueryRunner object to read employee object from database.
为了理解上述与 DBUtils 相关的概念，让我们编写一个将运行读取查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
//Step 1: Register JDBC driver
DbUtils.loadDriver(JDBC_DRIVER);
//Step 2: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//Step 3: Create a ResultSet Handler to handle Employee Beans
ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(Employee.class);
try {
Employee emp = queryRunner.query(conn,
"SELECT * FROM employees WHERE id=?", resultHandler, 103);
//Display values
System.out.print("ID: " + emp.getId());
System.out.print(", Age: " + emp.getAge());
System.out.print(", First: " + emp.getFirst());
System.out.println(", Last: " + emp.getLast());
} finally {
DbUtils.close(conn);
}
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
ID: 103, Age: 28, First: Sumit, Last: MittalAsyncQueryRunner interface
org.apache.commons.dbutils.AsyncQueryRunner 类可帮助以异步支持执行长时间运行的 SQL 查询。该类是线程安全的。该类支持与 QueryRunner 相同的方法，但它返回 Callable 对象，可用于稍后检索结果。
The org.apache.commons.dbutils.AsyncQueryRunner class helps to execute long running SQL queries with async support. This class is thread safe. This class supports same methods as QueryRunner but it return Callable objects which can be used later to retrieve the result.
Class Declaration
以下是 org.apache.commons.dbutils.AsyncQueryRunner 类的声明 −
Following is the declaration for org.apache.commons.dbutils.AsyncQueryRunner class −
public class AsyncQueryRunner
extends AbstractQueryRunnerUsage
-
Step 1 − Create a connection object.
-
Step 2 − Use AsyncQueryRunner object methods to make database operations.
Example
下面的示例将演示如何使用 AsyncQueryRunner 类更新记录。我们将更新 employee 表中可用的一个记录。
Following example will demonstrate how to update a record using AsyncQueryRunner class. We’ll update one of the available record in employee Table.
Syntax
String updateQuery = "UPDATE employees SET age=? WHERE id=?";
future = asyncQueryRunner.update(conn,
"UPDATE employees SET age=? WHERE id=?", 33,103);其中，
Where,
-
updateQuery − Update query having placeholders.
-
asyncQueryRunner − asyncQueryRunner object to update employee object in database.
-
future − Future object to retrieve result later.
为了了解与 DBUtils 相关的上述概念，让我们编写一个将以异步模式运行更新查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run an update query in async mode. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.AsyncQueryRunner;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorCompletionService;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws
SQLException, InterruptedException,
ExecutionException, TimeoutException {
Connection conn = null;
AsyncQueryRunner asyncQueryRunner = new AsyncQueryRunner( Executors.newCachedThreadPool());
DbUtils.loadDriver(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
Future<Integer> future = null;
try {
future = asyncQueryRunner.update(conn,
"UPDATE employees SET age=? WHERE id=?", 33,103);
Integer updatedRecords = future.get(10, TimeUnit.SECONDS);
System.out.println(updatedRecords + " record(s) updated.");
} finally {
DbUtils.close(conn);
}
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
1 record(s) updated.ResultSetHandler interface
org.apache.commons.dbutils.ResultSetHandler 接口负责将 ResultSet 转换为对象。
The org.apache.commons.dbutils.ResultSetHandler interface is responsible to convert ResultSets into objects.
Class Declaration
以下是 org.apache.commons.dbutils.ResultSetHandler 类的声明 −
Following is the declaration for org.apache.commons.dbutils.ResultSetHandler class −
public interface ResultSetHandler<T>Usage
-
Step 1 − Create a connection object.
-
Step 2 − Create implementation of ResultSetHandler.
-
Step 3 − Pass resultSetHandler to QueryRunner object, and make database operations.
Example
以下示例将演示如何使用 ResultSetHandler 类来映射记录。我们将读取 Employee 表中可用的一条记录。
Following example will demonstrate how to map a record using ResultSetHandler class. We’ll read one of the available record in Employee Table.
Syntax
Employee emp = queryRunner.query(conn, "SELECT * FROM employees WHERE first=?", resultHandler, "Sumit");其中，
Where,
-
resultHandler − ResultSetHandler object to map result set to Employee object.
-
queryRunner − QueryRunner object to read employee object from database.
为了理解上述与 DBUtils 相关的概念，让我们编写一个将运行读取查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Arrays;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
//Step 1: Register JDBC driver
DbUtils.loadDriver(JDBC_DRIVER);
//Step 2: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//Step 3: Create a ResultSet Handler to handle Employee Beans
ResultSetHandler<Object[]> handler = new ResultSetHandler<Object[]>() {
public Object[] handle(ResultSet rs) throws SQLException {
if (!rs.next()) {
return null;
}
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
Object[] result = new Object[cols];
for (int i = 0; i < cols; i++) {
result[i] = rs.getObject(i + 1);
}
return result;
}
};
try {
Object[] result = queryRunner.query(conn, "SELECT * FROM employees WHERE id=?",
handler, 103);
//Display values
System.out.print("Result: " + Arrays.toString(result));
} finally {
DbUtils.close(conn);
}
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
Connecting to database...
Result: [103, 33, Sumit, Mittal]Apache Commons DBUtils - BeanHandler Class
org.apache.commons.dbutils.BeanHandler 是 ResultSetHandler 接口的实现，负责将第一个 ResultSet 行转换为 JavaBean。该类是线程安全的。
The org.apache.commons.dbutils.BeanHandler is the implementation of ResultSetHandler interface and is responsible to convert the first ResultSet row into a JavaBean. This class is thread safe.
Class Declaration
以下是 org.apache.commons.dbutils.BeanHandler 类的声明 −
Following is the declaration for org.apache.commons.dbutils.BeanHandler class −
public class BeanHandler<T>
extends Object implements ResultSetHandler<T>Usage
-
Step 1 − Create a connection object.
-
Step 2 − Get implementation of ResultSetHandler as BeanHandler object.
-
Step 3 − Pass resultSetHandler to QueryRunner object, and make database operations.
Example
下面的示例将演示如何使用 BeanHandler 类读取记录。我们将读取 Employees 表中可用的一个记录，并将其映射到 Employee bean。
Following example will demonstrate how to read a record using BeanHandler class. We’ll read one of the available record in Employees Table and map it to Employee bean.
Syntax
Employee emp = queryRunner.query(conn, "SELECT * FROM employees WHERE first=?", resultHandler, "Sumit");其中，
Where,
-
resultHandler − BeanHandler object to map result set to Employee object.
-
queryRunner − QueryRunner object to read employee object from database.
为了理解上述与 DBUtils 相关的概念，让我们编写一个将运行读取查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
//Step 1: Register JDBC driver
DbUtils.loadDriver(JDBC_DRIVER);
//Step 2: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//Step 3: Create a ResultSet Handler to handle Employee Beans
ResultSetHandler<Employee> resultHandler
= new BeanHandler<Employee>(Employee.class);
try {
Employee emp = queryRunner.query(conn,
"SELECT * FROM employees WHERE first=?", resultHandler, "Sumit");
//Display values
System.out.print("ID: " + emp.getId());
System.out.print(", Age: " + emp.getAge());
System.out.print(", First: " + emp.getFirst());
System.out.println(", Last: " + emp.getLast());
} finally {
DbUtils.close(conn);
}
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
ID: 103, Age: 28, First: Sumit, Last: MittalBeanListHandler Class
org.apache.commons.dbutils.BeanListHandler 是 ResultSetHandler 接口的实现，负责将 ResultSet 行转换为 Java Bean 列表。该类是线程安全的。
The org.apache.commons.dbutils.BeanListHandler is the implementation of ResultSetHandler interface and is responsible to convert the ResultSet rows into list of Java Bean. This class is thread safe.
Class Declaration
以下是 org.apache.commons.dbutils.BeanListHandler 的声明：
Following is the declaration for org.apache.commons.dbutils.BeanListHandler class −
public class BeanListHandler<T>
extends Object implements ResultSetHandler<List<T>>Usage
-
Step 1 − Create a connection object.
-
Step 2 − Get implementation of ResultSetHandler as BeanListHandler object.
-
Step 3 − Pass resultSetHandler to QueryRunner object, and make database operations.
Example
以下示例将演示如何使用 BeanListHandler 类读取记录列表。我们将读取 Employees 表中可用的记录，并将其映射到 Employee bean 列表中。
Following example will demonstrate how to read a list of records using BeanListHandler class. We’ll read available records in Employees Table and map them to list of Employee beans.
Syntax
List<Employee> empList = queryRunner.query(conn, "SELECT * FROM employees", resultHandler);其中，
Where,
-
resultHandler − BeanListHandler object to map result sets to list of Employee objects.
-
queryRunner − QueryRunner object to read employee object from database.
为了理解上述与 DBUtils 相关的概念，让我们编写一个将运行读取查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
//Step 1: Register JDBC driver
DbUtils.loadDriver(JDBC_DRIVER);
//Step 2: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//Step 3: Create a ResultSet Handler to handle List of Employee Beans
ResultSetHandler<List<Employee>> resultHandler = new BeanListHandler<Employee>(Employee.class);
try {
List<Employee> empList = queryRunner.query(conn, "SELECT * FROM employees", resultHandler);
for(Employee emp: empList ) {
//Display values
System.out.print("ID: " + emp.getId());
System.out.print(", Age: " + emp.getAge());
System.out.print(", First: " + emp.getFirst());
System.out.println(", Last: " + emp.getLast());
}
} finally {
DbUtils.close(conn);
}
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: MittalArrayListHandler Class
org.apache.commons.dbutils.ArrayListHandler 是 ResultSetHandler 接口的实现，负责将 ResultSet 行转换为 object[]。该类是线程安全的。
The org.apache.commons.dbutils.ArrayListHandler is the implementation of ResultSetHandler interface and is responsible to convert the ResultSet rows into a object[]. This class is thread safe.
Class Declaration
以下是 org.apache.commons.dbutils.ArrayListHandler 类的声明：
Following is the declaration for org.apache.commons.dbutils.ArrayListHandler class −
public class ArrayListHandler
extends AbstractListHandler<Object[]>Usage
-
Step 1 − Create a connection object.
-
Step 2 − Get implementation of ResultSetHandler as ArrayListHandler object.
-
Step 3 − Pass resultSetHandler to QueryRunner object, and make database operations.
Example
以下示例将演示如何使用 ArrayListHandler 类读取记录列表。我们将读取 Employees 表中可用的记录作为 object[]。
Following example will demonstrate how to read a list of records using ArrayListHandler class. We’ll read available records in Employees Table as object[].
Syntax
List<Object> result = queryRunner.query(conn, "SELECT * FROM employees", new ArrayListHandler());其中，
Where,
-
resultHandler − ArrayListHandler object to map result sets to list of object[].
-
queryRunner − QueryRunner object to read employee object from database.
为了理解上述与 DBUtils 相关的概念，让我们编写一个将运行读取查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
//Step 1: Register JDBC driver
DbUtils.loadDriver(JDBC_DRIVER);
//Step 2: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
try {
List<Object[]> result = queryRunner.query(conn, "SELECT * FROM employees"
, new ArrayListHandler());
for(Object[] objects : result) {
System.out.println(Arrays.toString(objects));
}
} finally {
DbUtils.close(conn);
}
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
[100, 18, Zara, Ali]
[101, 25, Mahnaz, Fatma]
[102, 30, Zaid, Khan]
[103, 28, Sumit, Mittal]MapListHandler Class
org.apache.commons.dbutils.MapListHandler 实现了ResultSetHandler接口，负责将ResultSet行转换为Map列表。此类是线程安全的。
The org.apache.commons.dbutils.MapListHandler is the implementation of ResultSetHandler interface and is responsible to convert the ResultSet rows into list of Maps. This class is thread safe.
Class Declaration
以下是org.apache.commons.dbutils.MapListHandler类的声明−
Following is the declaration for org.apache.commons.dbutils.MapListHandler class −
public class MapListHandler
extends AbstractListHandler<Map<String,Object>>Usage
-
Step 1 − Create a connection object.
-
Step 2 − Get implementation of ResultSetHandler as MapListHandler object.
-
Step 3 − Pass resultSetHandler to QueryRunner object, and make database operations.
Example
以下示例演示如何使用MapListHandler类读取记录列表。我们将把Employees表中的可用记录作为Map列表进行读取。
Following example will demonstrate how to read a list of records using MapListHandler class. We’ll read available records in Employees Table as list of maps.
Syntax
List<Map<String, Object>> result = queryRunner.query(conn, "SELECT * FROM employees", new MapListHandler());其中，
Where,
-
resultHandler − MapListHandler object to map result sets to list of maps.
-
queryRunner − QueryRunner object to read employee object from database.
为了理解上述与 DBUtils 相关的概念，让我们编写一个将运行读取查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
//Step 1: Register JDBC driver
DbUtils.loadDriver(JDBC_DRIVER);
//Step 2: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
try {
List<Map<String, Object>> result = queryRunner.query(
conn, "SELECT * FROM employees", new MapListHandler());
System.out.println(result);
} finally {
DbUtils.close(conn);
}
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
Connecting to database...
[{id=100, age=18, first=Zara, last=Ali},
{id=101, age=25, first=Mahnaz, last=Fatma},
{id=102, age=30, first=Zaid, last=Khan},
{id=103, age=33, first=Sumit, last=Mittal}]Apache Commons DBUtils - Custom Handler
我们可以通过实现ResultSetHandler接口或通过扩展ResultSetHandler的任何已存在的实现，创建我们自己的自定义处理程序。在下面给出的示例中，我们通过扩展BeanHandler类创建了Custom Handler、EmployeeHandler。
We can create our own custom handler by implementing ResultSetHandler interface or by extending any of the existing implementation of ResultSetHandler. In the example given below, we’ve created a Custom Handler, EmployeeHandler by extending BeanHandler class.
为了理解上述与 DBUtils 相关的概念，让我们编写一个将运行读取查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}以下是 EmployeeHandler.java 文件的内容。
Following is the content of the EmployeeHandler.java file.
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class EmployeeHandler extends BeanHandler<Employee> {
public EmployeeHandler() {
super(Employee.class);
}
@Override
public Employee handle(ResultSet rs) throws SQLException {
Employee employee = super.handle(rs);
employee.setName(employee.getFirst() +", " + employee.getLast());
return employee;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
DbUtils.loadDriver(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
EmployeeHandler employeeHandler = new EmployeeHandler();
try {
Employee emp = queryRunner.query(conn,
"SELECT * FROM employees WHERE first=?", employeeHandler, "Sumit");
//Display values
System.out.print("ID: " + emp.getId());
System.out.print(", Age: " + emp.getAge());
System.out.print(", Name: " + emp.getName());
} finally {
DbUtils.close(conn);
}
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
ID: 103, Age: 28, Name: Sumit, MittalCustom Row Processor
如果数据库表中的列名和等效 javabean 对象的名称不相似，我们便可以使用自定义 BasicRowProcessor 对象对它们进行映射。请参见以下示例。
In case column names in a database table and equivalent javabean object names are not similar then we can map them by using customized BasicRowProcessor object. See the example below.
为了理解上述与 DBUtils 相关的概念，让我们编写一个将运行读取查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}以下是 EmployeeHandler.java 文件的内容。
Following is the content of the EmployeeHandler.java file.
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.BasicRowProcessor;
public class EmployeeHandler extends BeanHandler<Employee> {
public EmployeeHandler() {
super(Employee.class, new BasicRowProcessor(new BeanProcessor(mapColumnsToFields())));
}
@Override
public Employee handle(ResultSet rs) throws SQLException {
Employee employee = super.handle(rs);
employee.setName(employee.getFirst() +", " + employee.getLast());
return employee;
}
public static Map<String, String> mapColumnsToFields() {
Map<String, String> columnsToFieldsMap = new HashMap<>();
columnsToFieldsMap.put("ID", "id");
columnsToFieldsMap.put("AGE", "age");
return columnsToFieldsMap;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
public class MainApp {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws SQLException {
Connection conn = null;
QueryRunner queryRunner = new QueryRunner();
DbUtils.loadDriver(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
EmployeeHandler employeeHandler = new EmployeeHandler();
try {
Employee emp = queryRunner.query(conn,
"SELECT * FROM employees WHERE first=?", employeeHandler, "Sumit");
//Display values
System.out.print("ID: " + emp.getId());
System.out.print(", Age: " + emp.getAge());
System.out.print(", Name: " + emp.getName());
} finally {
DbUtils.close(conn);
}
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
ID: 103, Age: 28, Name: Sumit, MittalApache Commons DBUtils - Using DataSource
到目前为止，我们在使用QueryRunner时使用连接对象。我们还可以无缝使用数据源。以下示例演示了如何在QueryRunner和数据源的帮助下使用Read查询读取记录。我们将从Employees表读取记录。
So far, we’ve using connection object while using QueryRunner. We can also use datasource seemlessly. The following example will demonstrate how to read a record using Read query with the help of QueryRunner and datasource. We’ll read a record from Employees Table.
Syntax
QueryRunner queryRunner = new QueryRunner( dataSource );
Employee emp = queryRunner.query("SELECT * FROM employees WHERE first=?", resultHandler, "Sumit");其中，
Where,
-
dataSource − DataSource object configured.
-
resultHandler − ResultSetHandler object to map result set to Employee object.
-
queryRunner − QueryRunner object to read employee object from database.
为了理解上述与 DBUtils 相关的概念，让我们编写一个将运行读取查询的示例。为了编写我们的示例，让我们创建一个示例应用程序。
To understand the above-mentioned concepts related to DBUtils, let us write an example which will run a read query. To write our example, let us create a sample application.
Step |
Description |
1 |
Update the file MainApp.java created under chapter DBUtils - First Application. |
2 |
Compile and run the application as explained below. |
以下是 Employee.java 的内容。
Following is the content of the Employee.java.
public class Employee {
private int id;
private int age;
private String first;
private String last;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getLast() {
return last;
}
public void setLast(String last) {
this.last = last;
}
}以下是 CustomDatasource.java 的内容。
Following is the content of the CustomDatasource.java.
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class CustomDataSource {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
private static DataSource datasource;
private static final BasicDataSource basicDataSource;
static {
basicDataSource = new BasicDataSource();
basicDataSource.setDriverClassName(JDBC_DRIVER);
basicDataSource.setUsername(USER);
basicDataSource.setPassword(PASS);
basicDataSource.setUrl(DB_URL);
}
public static DataSource getInstance() {
return basicDataSource;
}
}以下是 MainApp.java 文件的内容。
Following is the content of the MainApp.java file.
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class MainApp {
public static void main(String[] args) throws SQLException {
DbUtils.loadDriver(JDBC_DRIVER);
QueryRunner run = new QueryRunner(CustomDataSource.getInstance());
ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(Employee.class);
Employee emp = queryRunner.query("SELECT * FROM employees WHERE id=?",
resultHandler, 103);
//Display values
System.out.print("ID: " + emp.getId());
System.out.print(", Age: " + emp.getAge());
System.out.print(", First: " + emp.getFirst());
System.out.println(", Last: " + emp.getLast());
}
}一旦你创建好源文件，让我们运行应用程序。如果你的应用程序没有问题，它将打印以下消息。
Once you are done creating the source files, let us run the application. If everything is fine with your application, it will print the following message.
ID: 103, Age: 33, First: Sumit, Last: Mittal