JDBC Environment Setup

To start using JDBC development, you should follow the following steps to set up the JDBC environment. We assume you are working on a Windows platform.

Install Java

FromJava Official WebsiteInstall J2SE Development Kit 5.0 (JDK 5.0).

Make sure to set the following environment variables according to the following instructions-

• JAVA_HOME: This environment variable should point to the directory where JDK is installed, for example, C:\Program Files\Java\jdk1.5.0.

• CLASSPATH: This environment variable should set the appropriate path, for example, C:\Program Files\Java\jdk1.5.0_20\jre\lib.

• PATH: This environment variable should point to the corresponding JRE bin, for example, C:\Program Files\Java\jre1.5.0_20\bin.

You may have already set these variables, but just to make sure, here is the check method.

• Go to the Control Panel, then double-click 'System'. If you are a Windows XP user, you may need to open 'Performance and Maintenance' first before you can see the 'System' icon.

• Go to the 'Advanced' tab, then click 'Environment Variables'.

• Now check that all the above variables are set correctly.

You automatically obtain the JDBC packagejava.sqlandjavax.sql,When you install J2SE Development Kit5.0 (JDK 5.0).

Install the database

Of course, the most important thing you will need is a running database that contains tables that can be queried and modified.

Install the database that is most suitable for you. You have many choices, the most common being-

• MySQL DB:MySQL is an open-source database. You can download it fromMySQL Official WebsiteDownload it. We recommend downloading the full Windows installation.

  In addition, download and installMySQL AdministratorandMySQL Query Browser.These are GUI-based tools that can make your development easier.

  Finally, setMySQL Connector / J(MySQL JDBC driver) download and extract it to a convenient directory. For the purpose of this tutorial, we will assume that you have installed the driver in C:\Program Files\MySQL\mysql-connector-java-5.1.8.

  Therefore, set the 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 depending on your installation.

• PostgreSQL DB:PostgreSQL is an open-source database. You can download it fromPostgreSQL official websiteDownload it.

  The Postgres installation includes a GUI-based management tool called pgAdmin III. The JDBC driver is also included as part of the installation.

• Oracle DB:Oracle DB is a commercial database sold by Oracle. We assume that you have the distribution media required to install it.

  The Oracle installation includes a GUI-based management tool called the Enterprise Manager. The JDBC driver is also included as part of the installation.

Install the database driver

The latest JDK includes a JDBC-ODBC Bridge driver, which allows programmers using the JDBC API to use most open database connectivity (ODBC) drivers.

Nowadays, most database vendors provide appropriate JDBC drivers as well as database installations. Therefore, you do not need to worry about this part.

Set database credentials

In this tutorial, we will use the MySQL database. When you install any of the above databases, the administrator ID is set toroot, and provides the conditions for setting the password you choose.

Using the root ID and password, you can create another user ID and password, or use the root ID and password for your JDBC application.

There are various database operations, such as database creation and deletion, which require an administrator ID and password.

For the rest of the JDBC tutorial, we will use the MySQL databaseusernameAs ID andpasswordAs the password.

If you do not have sufficient privileges to create a new user, you can request the database administrator (DBA) to create a user ID and password for you.

Establish a database

To createEMPTo create a database, please follow the following steps-

Step1

Open the command window and change to the installation directory as follows:

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

Note:mysqld.exeThe path may vary depending on the installation location of MySQL on the system. You can also refer to the documentation on how to start and stop the database server.

The2Step

If the database server is not running, please start it by executing the following command.

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

Step 3

Create a database by executing the following commandEMP

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

Establish a table

Create a table in the EMP databaseEmployees, please follow the following steps-

Step1

OpenCommand Promptand change to the installation directory, as follows:

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

The2Step

Log in to the database, as follows:

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

Step 3

Create TableEmployee, as follows:

mysql> use EMP;
mysql> create table Employees
　　　　-> (
　　　　-> id int not null,
　　　　-> age int not null,
　　　　-> first varchar (255),
　　　　-> last varchar (255)
　　　　-> );
mysql>

Create Data Record

Finally, you have created several records in the Employee table as follows:

mysql> INSERT INTO Employees VALUES (100,　18, 'Zara', 'Ali');
mysql> INSERT INTO Employees VALUES (101,　25, 'Mahnaz', 'Fatma');
mysql> INSERT INTO Employees VALUES (102,　30, 'Zaid', 'Khan');
mysql> INSERT INTO Employees VALUES (103,　28, 'Sumit', 'Mittal');
mysql>

To fully understand MySQL database, please learnMySQL Tutorial.

Now, you are ready to start trying JDBC. The next chapter provides examples of JDBC programming.