Connecting to Sybase SQL Anywhere Database in Java
Sybase SQL Anywhere is a powerful database server suitable for enterprise applications. It is also a common database backend for Java Web applications.
This article provides a step by step tutorial on connecting and querying data from a Sybase SQL Anywhere database using Java. We will also look at a sample Java class which returns database server date using SQL.
Java platform provides a standardized API interface for connecting to databases known as JDBC (Java Database Connectivity). Applications can use this standard API to connect to any relational database. Database vendors provide specific java library files (drivers) which can connect to their database and expose a standard interface through JDBC.When we write programs, we will be directly using Java JDBC API and under the hood Java will make use the driver library to connect to database and execute SQL commands.
This tutorial is written for Sybase SQL Anywhere 12, Java 1.6 and Sybase JDBC 4.0 driver. You may need different versions of Sybase JDBC drivers for other configurations. However the overall approach remains the same.
Sybase JDBC driver (sajdbc4.jar) is part of Sybase database installation. It is located under the folder C:\Program Files\SQL Anywhere 12\Java in a Windows machine(assuming default installation). Copy sajdbc4.jar to the folder where SybaseExample.java file (see below) is located.
Java Program to Connect to Sybase SQL Anywhere - JDBC 4.0
Since we are using JDBC 4.0, we don't need to specify the driver name using DriverManager.registerDriver. The following Java program connects to Sybase SQL Anywhere 12 database with JDBC 4.0 driver. It then prints the system date received from the database server,
// Example Java Program - Sybase SQL Anywhere 12 Database Connectivity with JDBC 4.0 import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SybaseExample { public static void main(String[] args) throws SQLException { // uid - user id // pwd - password // eng - Sybase database server name // database - sybase database name // host - database host machine ip String dburl = "jdbc:sqlanywhere:uid=DBA;pwd=DBA;eng=devdb;database=devdb;links=tcpip(host=172.20.20.20)"; // Connect to Sybase Database Connection con = DriverManager.getConnection(dburl); Statement statement = con.createStatement(); // We use Sybase specific select getdate() query to return date ResultSet rs = statement.executeQuery("SELECT GETDATE()"); if (rs.next()) { Date currentDate = rs.getDate(1); // get first column returned System.out.println("Current Date from Sybase is : "+currentDate); } rs.close(); statement.close(); con.close(); } }
In order to compile or run the above program, you need to add sajdbc4.jar to the classpath of your program. If you are using IDE such as NetBeans or Eclipse, you can add sajdbc4.jar as a dependent library and NetBeans/Eclipse will automatically add it to classpath.
If you are running the above program from command line, copy sajdbc4.jar to the folder where the above Java program is located and then compile the file using the following command (this adds sajdbc4.jar to classpath),
javac -classpath ./sajdbc4.jar SybaseExample.java
Run the Java program using the following command (sajdbc4.jar is added to classpath),
java -classpath "./sajdbc4.jar;." SybaseExample
Note that when you are running SybaseExample, you need both the JDCB jar file and the current folder in the classpath. If everything went well, you will see the following output.
Java Program to Connect to Sybase SQL Anywhere 12 Using JDBC 3.0 Driver
If you want to connect to Sybase SQL Anywhere 12 with JDBC 3.0 driver (sajdbc.jar) use the following code. The only additional item in this program is the loading of driver file. Without the driver configuration, you will encounter the following error,
No suitable driver found for jdbc:sqlanywhere
// Example Java Program - Sybase SQL Anywhere 12 Database Connectivity with JDBC 3.0 import java.sql.*; public class SybaseExample2 { public static void main(String[] args) throws Exception { DriverManager.registerDriver( (Driver) Class.forName( "sybase.jdbc.sqlanywhere.IDriver" ).newInstance() ); // uid - user id // pwd - password // eng - Sybase database server name // database - sybase database name // host - database host machine ip String dburl = "jdbc:sqlanywhere:uid=DBA;pwd=DBA;eng=devdb;database=devdb;links=tcpip(host=172.20.20.20)"; // Connect to Sybase Database Connection con = DriverManager.getConnection(dburl); Statement statement = con.createStatement(); // We use Sybase specific select getdate() query to return date ResultSet rs = statement.executeQuery("SELECT GETDATE()"); if (rs.next()) { Date currentDate = rs.getDate(1); // get first column returned System.out.println("Current Date from Sybase is : "+currentDate); } rs.close(); statement.close(); con.close(); } }