Dbutils 简明教程

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 −

  1. JAVA_HOME − This environment variable should point to the directory where you installed the JDK, e.g. C:\Program Files\Java\jdk1.5.0.

  2. CLASSPATH − This environment variable should have appropriate paths set, e.g. C:\Program Files\Java\jdk1.5.0_20\jre\lib.

  3. 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.

  1. 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.

  2. Go to the Advanced tab and click on the Environment Variables.

  3. 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 −

  1. 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.

  2. 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.

  3. 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.

Step 2

如果数据库服务器尚未运行,请执行以下命令以下启动数据库服务器。

Start the database server by executing the following command, if it is already not running.

C:\Program Files\MySQL\bin>mysqld
C:\Program Files\MySQL\bin>

Step 3

通过执行以下命令来创建 emp 数据库 −

Create the emp database by executing the following command −

C:\Program Files\MySQL\bin> mysqladmin create emp -u root -p
Enter password: ********
C:\Program Files\MySQL\bin>

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>

Step 2

登录数据库,如下所示 −

Login to the database as follows −

C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ********
mysql>

Step 3

如下创建一个 Employee 表 −

Create the table Employee as follows −

mysql> use emp;
mysql> create table Employees
    -> (
    -> id int not null,
    -> age int not null,
    -> first varchar (255),
    -> last varchar (255)
    -> );
Query OK, 0 rows affected (0.08 sec)
mysql>

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.