JDBC Drivers

The Tomcat package bundled with PCQLinux 2005 includes JDBC (Java DataBase Connectivity) drivers for various databases. These drivers are installed in the common/lib directory upon a Tomcat installation. This makes these drivers available to all the Web applications that will be hosted on Tomcat. In this article we look at code examples to use these JDBC drivers to connect to the various databases. Lets start with installing and setting up Tomcat.

Install Tomcat





Before installing Tomcat you will need to install JDK (Java Development Kit). For this install the RPM named pcqlinux-j2se-installer-1-0.i386.rpm found on PCQLinux CD 3. This will produce a directory named pcqlinux-j2se-installer in /opt. Change to this directory and issue the following.

./jdk-1_5_0_01-linux-i586-rpm.bin

Type in 'yes' for the license agreement, which will then install JDK in the directory /usr/java/jdk1.5.0_01. Next, to install Tomcat, install the RPM named tomcat-5.5-4.i386.rpm found on PCQLinux CD 3. This will install Tomcat in the directory /opt/tomcat. Next issue the following.

export JAVA_HOME=/usr/java/jdk1.5.0_01

Append the above line to the file named profile found in /etc directory to make this setting persistent across reboots. Start Tomcat by issuing the following.

/opt/tomcat/bin/catalina.sh start

Create a webapp





Change to the directory /opt/tomcat/webapp and create a directory named pcquest. Within pcquest, create directories WEB-INF, WEB-INF/classes and WEB-INF/lib. Create a file named web.xml with the following content.

http://java.sun.com/xml/ns/j2ee" 



xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 


xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml 


version="2.4"> 

Now place it under the WEB-INF subdirectory of pcquest. Next we show you code snippets for connecting to the different database servers, namely PostgreSQL, MySQL and MS SQL. 

Connect to PostgreSQL





First, to install PostgreSQL database on PCQLinux 2005, install the RPMs named postgresql-server-7.4.6-1.FC3.1.i386.rpm and

postgresql-7.4.6-1.FC3.1.i386.rpm found on CD 3 of PCQLinux 2005. 

Create a file named postgresql in the directory named /etc/sysconfig/pgsql with the following line in it.

PGOPTS="-i"

Append the following line to the file named pg_hba.conf.

host all all 127.0.0.1 255.255.255.255 md5

Start the database server as:



service postgresql start

Then issue:

su postgres

To create a database named pcquestdb. issue: 

createdb pcquestdb

Next we create a database user who will be able to connect to this database:

createuser --password

When prompted, type in shekhar for the name of user. Type 'n' for 'allowed to create database' and for 'create new users'. For password supply your preferred password (say secret123). 

Next issue 'psql pcquestdb'. On the pcquestdb=# prompt, you can issue any SQL command. So populate the database with tables and data by issuing SQL queries. Then issue the following to grant privileges to the user shekhar to the database:

grant all on database pcquestdb to shekhar

Coming to the coding part, following is the code required to connect to the PostgreSQL database:

private Connection getConnection() throws

SQLException,ClassNotFoundException{



Class.forName("org.postgresql.Driver");


String connectionStr = "jdbc:postgresql://localhost/pcquestdb";


return DriverManager.getConnection(connectionStr,"shekhar","secret123");


}

The above method will return a connection object, which you can use subsequently to issue queries against the database. Compile a class with the above connection code and place it in the directory pcquest/WEB-INF/classes and subsequently call the class and its getConnection method from a JSP or

Servlet. 

Connect to MySQL





To install MySQL database server on PCQLinux 2005, install the RPMs named MySQL-server-4.0.23-0.i386.rpm found on CD 3 and MySQL-client-4.0.23-0.i386.rpm found on CD 2. To start the database server, issue:

/etc/init.d/mysql start

The default installation of MySQL allows anonymous logins from the localhost and has the root (root MySQL user and not the Linux root user) password set to blank. To set up a password for the the root MySQL user, issue:

mysqladmin -u root password passwd

Substitute passwd with your preferred password. To delete anonymous logins, issue:

mysql -u root -p

When prompted, supply the password that you had specified in the previous step. You will be dropped into a mysql> prompt. Issue the following at this prompt.

use mysql;



delete from user where user='';

Now create a database (pcquestdb) in MySQL by issuing:

mysaladmin -u root -p create pcquestdb

When prompted, supply the password set for MySQL's root user in the previous step. Next issue:

mysql -u root -p

Issue the following - at the mysql> prompt - to create a user (say shekhar) with privileges to access the pcquestdb database. 

grant all on pcquestdb.* to shekhar@localhost identified by 'secret123'

Next issue SQL statements to create tables and populate the tables with data. Following is the code required to connect to the PostgreSQL database.

private Connection getConnection() throws SQLException,ClassNotFoundException{



Class.forName("org.gjt.mm.mysql.Driver");


String connectionStr = "jdbc:mysql://localhost/pcquestdb";


return DriverManager.getConnection(connectionStr,"shekhar","secret123");


}

Connect to MS SQL Server





We assume that you are running Ms SQL Server on a Windows machine (IP address 192.168.1.10, for instance) on your network. Tomcat on PCQLinux 2005 bundles open source jTDS drivers which can be used to connect to MS SQL Server. 

Launch SQL Server's Enterprise Manager and create a database named pcquestdb. Create a user named shekhar with password secret123 for this database. Following is the code to connect to the MS SQL Server:

private Connection getConnection() throws SQLException,ClassNotFoundException{



Class.forName("net.sourceforge.jtds.jdbc.Driver");


String connectionStr = "jdbc:jtds:sqlserver://192.168.1.10/pcquestdb";


return DriverManager.getConnection(connectionStr,"shekher","secret123");


}

Compile a class with the above connection code and place it in the directory pcquest/WEB-INF/classes and subsequently call the class and its getConnection method from a JSP or

Servlet.