Martapura Oku Timur: Java Database Connectivity

JDBC (Java Database Connectivity) is designed to allow users to use SQL(Structured Query Language) to query databases. It makes the tasks of the developers easy as it handles all low-level concerns about particular database types.

JDBC is similar to Microsoft’s ODBC with the plus point “Platform Independence”. To use JDBC, you need to have database driver to communicate with the database. Normally drivers are installed while installing the database. Like if you install MS SQL Server, Oracle or DB2, database drivers will be installed. If you are working with MySQL, PostgreSQL or some third party database, you need to put its driver (Jar fileI into the class path.

JDBC Drivers

JDBC drivers can be broadly divided into four categories depending upon the driver implementation. The four categories/types are:

· Type 1: JDBC-ODBC Bridge
· Type 2: Native-API/partly Java driver
· Type 3: Net-protocol/all-Java driver
· Type 4: Native-protocol/all-Java driver

I will briefly talk about each type:

JDBC-OBC bridge driver is pure Java and is include in java.sql.*. The client needs ODBC driver manager and ODBC driver for data source. It is ideal in situations, when ODBC driver is available for the database and is already installed on the client machine.

Type 2 is Native code driver. It implements native JDBC interfaces using language functions in the DBMS product’s API. Type 2 drivers need platform specific library, so client and server both may run on same host. Type 2 drivers offer better performance than Type 1 drivers.

Type 3 drivers are pure Java drivers and they use middleware network protocol. They need DBMS server to implement the standard protocol to be middleware specific. The advantage is that there is no nee for any vendor database library to be present on client machines. Interesting thing is, there is no JDBC standard network protocol yet.

Type 4 drivers are pure Java drivers and they use vendor specific network protocol. These use DBMS specific network protocol (Oracle SQL Net, etc).
For the beginners, Type 1 drivers are suitable. Users simply have to make a DSN and start interacting with the database.

Using JDBC-ODBC Bridge

The beginners should start with JDBC-ODBC Bridge since it is simple and easy to work with. Consider that you have a database with tables and data and you want to connect to it in order to carry out operations.

First step is to create an ODBC dsn. It is done from Control panel > Data Sources (ODBC).

Now you have to load the JDBC driver. This is done using static method forName(…) of class called Class. Static method forName(…) takes name of the driver as parameter.

Java Code:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

DriverManager.getConnection() is used to connect to the database. Its signature is as follows:

Java Code:

static Connection getConnection(String url, String user, String password)

It takes JDBC URL (with DSN), username and password as parameters. By doing this, you will connect to the database.

Connection time

Sometimes, it is interesting to know how much time it takes to connect to the database. The code sample below calculates the time it takes to connect to a database referred by dsn.

Java Code:

long connection_time; 

Date start = new Date();  //get start time

String stUrl_= "jdbc:odbc:myDSN"; 

connection_ = DriverManager.getConnection(stUrl,"sa","sa");

Date end = new java.util.Date();  //get end time

connection_time = end.getTime()-start.getTime();

Getting the Warnings

Sometimes it is a wise decision to retrieve the first warning reported by calls on this Connection object. This can be done using getWarnings() method. The code sample below shows how to print all the warnings with their sates and messages.

Java Code:

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;

Connection conn = DriverManager.getConnection( "jdbc:odbc:Database" ) ;

// Print all warnings

for( SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning() )

{

System.out.println( "SQL Warning:" ) ;

System.out.println( "State  : " + warn.getSQLState()  ) ;

System.out.println( "Message: " + warn.getMessage()   ) ;

System.out.println( "Error  : " + warn.getErrorCode() ) ;

}

Adding records in the database tables

Statement object is used to add enteries into the tables. The method used is executeUpdate(…).

Java Code:

Statement st = conn.createStatement();

st.executeUpdate("INSERT INTO customers VALUES (100, 'Laiq', 'Mr.', 'Paris', 2008)");

Using Resultset

ResultSet is an interface found in java.sql package. It actually represents a table in the memory containing all the records fetched from the database in result of a query.

Java Code:

String name, brand ;

float price;

ResultSet rs = stmt.executeQuery("SELECT * FROM customers");

while ( rs.next() ) {

name = rs.getString("name");

brand = rs.getString("brand");

price = rs.getFloat("price");

}

Getting number of rows updated

Statement’s executeUpdate(…) method return no of row modified. So you can easily know how many rows were modified by your update query.

Java Code:

int rows = stmt.executeUpdate( "UPDATE customer SET 

cust_name = ‘Laiq’ WHERE cust_id = 100" ) ;

System.out.println( rows + " Rows modified" ) ;

Accessing MS Access DB without DSN

It is possible to access a MS Access database without creating the DSN although its not a good approach. The example below includes a static method getConnection(…) which return Connection object. The database path is coded in it.

Java Code:

import java.sql.*;

public class PackageTest {

    // loading driver and making connection with database

    public static Connection getConnection() throws Exception{

        Connection con;

        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        // set this to a MS Access DB you have on your machine

        String filename = "c:/mydb.mdb";

        String database = "jdbc:odbc:Driver={Microsoft Access Driver

                    (*.mdb)};DBQ="; database+= filename.trim() + ";

                    DriverID=22;READONLY=true}";

        con = DriverManager.getConnection( database ,"","");

        return con;

    }

    public static void showPackages(Statement stmt) throws Exception

    {

        // displaying contents of Package table

        String query = "select * from package";

        ResultSet result = stmt.executeQuery(query);

        System.out.println("n" +"Contents of table Package");

        System.out.println ("Package ID - Package Name - Duration - Cost");

        while (result.next()) {

            System.out.print(result.getString("pid") + " - ");

            System.out.print(result.getString("pname")+ "- " );

            System.out.print(result.getString("duration")+ " - ");

            System.out.println(result.getString("cost"));

        }

        stmt.close();

    }

        public static void main(String[] args)throws Exception {

        Statement stmt =Fitness.getConnection().createStatement();

        showPackages(stmt);

    }

}

Connection to PostgreSQL database and querying the database

PostgreSQL is a popular open source object-relational database management system that is being used for small and medium sized applications. We have two options to connect to PostgreSQL. One is to use odbc/jdbc connection and the other is to use PostgreSQL JDBC driver. In this post, I will write about how to use PostgreSQL JDBC driver to connect to PostgreSQL database.

First of all, you will need PostgreSQL database driver. It can be downloaded from
Download
The driver provides are reasonably complete implementation of the JDBC 3 specification in addition to some PostgreSQL specific extensions.
Once you have the jar file, include it into class path.
For demonstrating an example, I created a database in PostgreSQL called testdb. A table named “languages” is also created with following schema:
id (integer)
name (text)
comments (text)
I want to connect to the database using Postgres jdbc driver and want to display the contents of the languages table. Lets see how to do that:

Java Code:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class Db {

    static String dbname = "testdb";

    static String dbuser = "postgres";

    static String dbpass = "postgres";

    static String dbhost = "localhost";

    static String dbport = "5432";

    static String dbtable = "languages";

    public static void main(String[] args) throws Exception {

        Connection conn;

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

        String url = "jdbc:postgresql://" + dbhost + 

                ":" + dbport + "/" + dbname;

        System.out.println("getConnection: url="+url);

        conn = DriverManager.getConnection(url, dbuser, dbpass);

        Statement s = conn.createStatement();

        String query = "select * from programming";

        ResultSet rs;

        rs = s.executeQuery(query);

        while(rs.next())

        {

            System.out.print(rs.getString("name"));

            System.out.println(" - " + rs.getString("comments"));

        }

        }

}

Output:

Java Code:

getConnection: url=jdbc:postgresql://localhost:5432/testdb

Java - version 6

C++ - Borland

<acronym title="vBulletin">VB</acronym> – Microsoft

So desired output is displayed. Driver is loaded using Class.forName statement and then connection is established using DriverManager.getConnection(…) which takes connection parameters. After that we created the Statement and executed the query. ResultSet is used to move through and the outcome of the executed query.
If Postgre jdbc driver is not included in the class path, you will get following exception:

Java Code:

Exception in thread "main" java.lang.ClassNotFoundException: org.postgresql.Driver

Using Batches

Updating a record from Java using executeUpdate() method of Statement object is used commonly by JDBC programmers. Thing to note is that this is a costly activity in terms of time, as a database trip is made. As a programmer/developer, our aim should be to write efficient Java code. In this post, I will write about how you can efficiently make several updates.

Batch update allows us to execute several update statements in a batch. It means only one database trip will be make, thus saving processing time. Batch updates won’t work on select statements.
Time for an example. I want to update 3 records in a Postge SQL database. So 3 update statements are require. I will use batch to execute there.

Java Code:

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

String url = "jdbc:postgresql://" + dbhost + ":" + dbport 

+ "/" + dbname;

System.out.println("getConnection: url="+url);

conn = DriverManager.getConnection(url, dbuser, dbpass);

Statement stmt = conn.createStatement();

stmt.addBatch("UPDATE programming SET comments = \'none\' WHERE id = " + 1);

stmt.addBatch("UPDATE programming SET comments = \'none\' WHERE id = " + 2);

stmt.addBatch("UPDATE programming SET comments = \'none\' WHERE id = " + 3);

int[] updateCount = stmt.executeBatch();

Things look simple here. I used addBatch(…) method of Statement object to create the batch. A batch can have as many as you like statements. Once the batch is ready, executeBatch() method is use to execute it. The method executeBatch() return int array which contains update count of each command. For example, at zero index, you will find the no of records updated by first update statement in the batch and so on.

Calling stored procedures

Stored procedures contain set of SQL statements and are stored on the database server. Their query execution plan is already define, so they run faster and improve the performance. In this post, I will talk about how to call stored procedures.

An object of CallebleStatement (extends PrepareStatement) is used to execute store procedures. PrepareCall() method is use to create CallebleStatement’s object.

Java Code:

CallebleStatement cst;

Cst = conn.prepareCall(“{call mySProc()}”);

The syntax for calling store procedure is vendor specific. It may very for MS SQL server, Oracle, MySQL and Postgre SQL. So check the syntax before use.
Stored procedures may also take arguments therefoe, CallebleStatement can create placeholders for the arguments.

Java Code:

CallebleStatement cst;

Cst = conn.prepareCall(“{call mySProc(?,?)}”);

setXXX(…) methods can be used to set the arguments. Index is important when setting the placeholders.

Java Code:

cst.setString(1, “Java”);

cst.setInt(2, 1000);

cst.executeUpdate();

After providing all the arguments, executeUpdate() is called to execute the stored procedure.
Stored procedure may also return some value. For that, we have to use registerOutParameter before executing the stored procedure.

Java Code:

CallebleStatement cst;

Cst = conn.prepareCall(“{call mySProc(?,?,?)}”);

cst.setString(1, “Java”);

cst.setInt(2, 1000);

cst. RegisterOutParameter(3, java.sql.Types.INTEGER);

cst.execute();

int rValue = cst.getInt(3);

Using prepaid statements

SQL statements are executed on a database tables using Statement object. If the sql statement takes parameter, then it is a wise decision to use PreparedStatement as they are more flexible and makes coding easier.

Creating SQL statements that take parameters provides flexible statements to work with. We can use the same statement and supply it with different values each time we execute it.
We use place holders (question marks) in the query, which are supplied values to make queries flexible. Values are supplied using setXx(…) methods for example:

Java Code:

void setDouble(int parameterIndex, double x)

void setFloat(int parameterIndex, float x)

void setInt(int parameterIndex, int x)

void setLong(int parameterIndex, long x)

…

One has to specify the index as well while setting values. If we miss a parameter or supply an extra parameter, then org.postgresql.util.PSQLException is thrown.
Time for an example. I will connect to Postgres database and will display the contents of a table. Then I will use PreparedStatement statement to update a record and for confirmation, I will again display the contents of the table.

Java Code:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

public class Db {

    static String dbname = "testdb";

    static String dbuser = "postgres";

    static String dbpass = "postgres";

    static String dbhost = "localhost";

    static String dbport = "5432";

    static String dbtable = "languages";

    public static void main(String[] args) throws Exception {

        Connection conn;

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

        String url = "jdbc:postgresql://" + dbhost + ":"

                + dbport + "/" + dbname;

        System.out.println("getConnection: url="+url);

        conn = DriverManager.getConnection(url, dbuser, dbpass);

        Statement s = conn.createStatement();

        String query = "select * from programming";

        ResultSet rs;

        System.out.println("Before update.");

        rs = s.executeQuery(query);

        while(rs.next())

        {

            System.out.print(rs.getString("name"));

            System.out.println(" - " + rs.getString("comments"));

        }

        PreparedStatement updateSales = conn.prepareStatement

                ("UPDATE programming 

                SET comments = ? WHERE name LIKE ? ");

        updateSales.setString(1, "Mustang - Added"); 

        updateSales.setString(2, "Java"); 

                updateSales.executeUpdate();

                System.out.println("After update.");

        rs = s.executeQuery(query);

        while(rs.next())

        {

            System.out.print(rs.getString("name"));

            System.out.println(" - " + rs.getString("comments"));

        }

        }

}

Output:

Java Code:

getConnection: url=jdbc:postgresql://localhost:5432/testdb

Before update.

C++ - Borland

<acronym title="vBulletin">VB</acronym> - Microsoft

Java - Mustang

Aater update.

C++ - Borland

<acronym title="vBulletin">VB</acronym> - Microsoft

Java - Mustang – Added

Lets use PreparedStatement with simple select queries.

Java Code:

PreparedStatement ps = conn.prepareStatement("select * from programming 

                                 where name LIKE ?");

ps.setString(1, "Java");

        ResultSet rs = ps.executeQuery();

        while(rs.next())

        {

            System.out.print(rs.getString("name"));

            System.out.println(" - " + rs.getString("comments"));

        }

Try the code samples presented above and dig deep. Play around. I hope this helps.

navigation

Saturday, August 4, 2012

Java Database Connectivity

No comments:

Post a Comment

Followers