Enterprise Java for Linux HOWTO: How to Setup JDBC Support

6. How to Setup JDBC Support

There are several databases that run on Linux that also support a JDBC interface. These include:

IBM DB2
MiniSQL
MySQL
Oracle
PostgreSQL
Sybase

If you are going to try just one DBMS, I suggest you initially try PostgreSQL, principally because it comes with most major Linux distributions and may already be installed on your system.

6.1 IBM DB2

To be written.

See http://www-4.ibm.com/software/data/db2/linux/for more information.

6.2 MiniSQL

To be written.

See http://www.hughes.com.au/for more information.

6.3 MySQL

To be written.

See http://www.mysql.org/for more information.

6.4 Oracle

To be written.

See http://platforms.oracle.com/linux/

PostgreSQL is a sophisticated Object-Relational DBMS, supporting almost all SQL constructs, including subselects, transactions, and user-defined types and functions. It is the most advanced open-source database available anywhere. Commercial Support is also available from PostgreSQL, Inc. The current version is 6.5.3 and is available at any of the many mirror sites or on CD. (From the PostgreSQL website.)

PostgreSQL may have already been shipped with your Linux distribution because of its open source license.

Download and Installation

Rather than downloading from PostgreSQL, I suggest you initially try the PostgreSQL that most likely came with your Linux distribution.

Alternatively, PostgreSQL can be obtained from http://www.postgresql.org.

To confirm that PostgreSQL is installed on your computer, type:

rpm -qa | grep postgresql

which postmaster
which psql

You need the postgresql, postgresql-server, and postgresql-java packages installed to use Java with PostgreSQL.

Make sure PostgreSQL is running. Type:

ps -f -u postgres

You should see postmaster, the PostgreSQL daemon, running.

If postmaster is not running, there will probably be a Sys V Init script that you can use to start it. In many distributions it is located in /etc/rc.d/init.d. To start PostgreSQL, type:

cd /etc/rc.d/init.d
./postgresql start

You can use the above "ps" command to confirm that PostgreSQL is running.

Note: To use JDBC, PostgreSQL needs to have been started with the '-i' parameter indicating support for TCP/IP connections rather than solely UNIX domain sockets. Confirm that postmaster> was started with the '-i' paramter.

Create a test database by typing:

su - postgres
createdb javatest

You should see no error messages.

Create a test table with one test row. First, log in to the interactive PostgreSQL tool and connect to the javatest database you just created by typing (as the postgres user):

psql javatest

You should see confirmation that you are connected to the database: javatest.

Then, create the test table by typing (within psql):

create table test (col1 varchar(255));

You should see the "CREATE" confirmation message.

Next, insert one row by typing (within psql):

insert into test (col1) values ('Hello, from PostgreSQL!');

You should see the "INSERT" confirmation message.

Finally, confirm that the row is there by typing (within psql):

select col1 from test;

You should see the row you just created.

You can exit psql by typing "\ q".

For more assistance on working with PostgreSQL, I suggest you look into the Database-SQL-RDBMS HOW-TO document for Linux (PostgreSQL Object Relational Database System) at http://metalab.unc.edu/mdw/HOWTO/PostgreSQL-HOWTO.html.

You will need to add the appropriate JAR to your CLASSPATH. The PostgreSQL JARs come in the postgresql-jdbc package.

export CLASSPATH=$CLASSPATH:/usr/lib/pgsql/jdbc6.5-1.2.jar

You may need to substitute the path depending you where PostgreSQL is installed on your system.

Confirming Your Installation

You are now ready to compile and run a simple JDBC application that uses PostgreSQL. Create the following program.

import java.sql.*;

class PostgreSQLTest {
  public static void main (String[] args) {
    try {
      Driver driver = (Driver)
        Class.forName("postgresql.Driver").newInstance();
      DriverManager.registerDriver(driver);

      String     url = "jdbc:postgresql:javatest"; 
      Connection con = DriverManager.getConnection(url, "postgres", ""); 
      Statement  stm = con.createStatement(); 
 
      stm.setQueryTimeout(10); 
      ResultSet  rs  = stm.executeQuery("select col1 from test"); 
 
      rs.next(); 
 
      System.out.println(rs.getString(1)); 
 
    } catch (SQLException e) { 
 
      System.out.println("Exception!"); 
      System.out.println(e.toString()); 
  } 
}

Compile the program with the Java compiler.

javac PostgreSQLTest.java

If the compiler produces errors, double check the syntax and confirm your PATH and CLASSPATH.

Run the program with the JVM.

java PostgreSQLTest

If the JVM produces errors, confirm your PATH and CLASSPATH.

You should see the following output:

Hello, from PostgreSQL!

Congratulations, you have installed, set up an environment for, and tested a JDBC interface to PostgreSQL.

More Information

For more information, I suggest you look into the PostgreSQL website at

http://www.postgresql.org/.

6.6 Sybase

Background

Sybase Adaptive Server Enterprise is a commericial RDBMS that is available for the Linux operating system. While Sybase has recently released version 12.0, version 11.9.2 is available for Linux.

According to the Sybase website, "By porting ASE to Linux, Sybase provides the Linux development community with the first highly scalable, high-performance database engine available for the platform. The package includes the standard features of Adaptive Server Enterprise and all related connectivity components. Adaptive Server Enterprise 11.9.2 is offered FREE for development."

Download

The Sybase ASE can be obtained from http://www.sybase.com/products/databaseservers/linux/linux1192_reg.html.

In order to download, you will have to register with the Sybase website and agree to the license online.

The Sybase JDBC driver can be obtained from http://www.sybase.com/products/internet/jconnect/.

Select download jConnect 4.2/5.2.

If you have access to a Sybase server on the network, you only need to download and install the JDBC driver.

Installation

Installation of Sybase is beyond the scope of this HOWTO. This HOWTO will assume that Sybase has been correctly installed and configured and that you can get to Sybase using isql.

Log into isql as sa and create a test user and test database by typing:

create database javatest  
go 
sp_addlogin javatest, javatest, javatest 
go 
use javatest 
go 
sp_dbowner javatest 
go

You should see no error messages.

Create a test table with one test row. First, log in to isql as the javatest user and type:

create table test (col1 varchar(255))  
go

You should see no error messages.

Next, insert one row by typing:

insert into test (col1) values ('Hello, from Sybase!')  
go

You should see no error messages.

Finally, confirm that the row is there by typing:

select col1 from test  
go

You should see the row you just created.

You can exit isql by typing "exit".

For more assistance on working with Sybase, review the documentation that can be downloaded with Sybase.

You will need to add the appropriate JAR to your CLASSPATH.

export CLASSPATH=$CLASSPATH:/usr/local/sybase/jConnect-5_2/classes/jconn2.jar

You may need to substitute the path depending you where jConnect is installed on your system.

Confirming Your Installation

You are now ready to compile and run a simple JDBC application that uses Sybase. Create the following program.

import java.sql.*;  
 
class SybaseTest { 
  public static void main (String[] args) { 
    try { 
      Driver driver = (Driver) 
        Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance(); 
      DriverManager.registerDriver(driver); 
 
      String     host = "127.0.0.1"; 
      String     port = "4100"; 
 
      String     url = "jdbc:sybase:Tds:" + host + ":" + port; 
      Connection con = DriverManager.getConnection(url, "javatest", "javatest"); 
      Statement  stm = con.createStatement(); 
 
      stm.setQueryTimeout(10); 
      ResultSet  rs  = stm.executeQuery("select col1 from test"); 
 
      rs.next(); 
 
      System.out.println(rs.getString(1)); 
 
    } catch (SQLException e) { 
 
      System.out.println("Exception!"); 
      System.out.println(e.toString()); 
  } 
}

You will need to substitute the host and port number of you Sybase server as appropriate. See $SYBASE/interfaces and the $DSQUERY entry for what values to use for the host and port number.

Compile the program with the Java compiler.