Java Database Connectivity with 5 Steps

There are 5 steps to connect any java application with the database using JDBC. These steps are as follows:

1. Register the Driver class
2. Create connection
3. Create statement
4. Execute queries
5. Close connection

1) Register the driver class

The forName() method of Class class is used to register the driver class. This method is used to dynamically load the driver class.

Syntax of forName() method

public static void forName(String className)throws ClassNotFoundException 

Note: Since JDBC 4.0, explicitly registering the driver is optional. We just need to put vender's Jar in the classpath, and then JDBC driver manager can detect and load the driver automatically.

Example to register the OracleDriver class

Here, Java program is loading oracle driver to esteblish database connection.

Class.forName("oracle.jdbc.driver.OracleDriver"); 

2) Create the connection object

The getConnection() method of DriverManager class is used to establish connection with the database.

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException 
2) public static Connection getConnection(String url,String name,String password) 
throws SQLException 

Example to establish connection with the Oracle database

Connection con=DriverManager.getConnection( 
"jdbc:oracle:thin:@localhost:1521:xe","system","password"); 

3) Create the Statement object

The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.

Syntax of createStatement() method

public Statement createStatement()throws SQLException 

Example to create the statement object

Statement stmt=con.createStatement(); 

4) Execute the query

The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.

Syntax of executeQuery() method

public ResultSet executeQuery(String sql)throws SQLException 

Example to execute query

ResultSet rs=stmt.executeQuery("select * from emp"); 
 
while(rs.next()){ 
System.out.println(rs.getInt(1)+" "+rs.getString(2)); 
} 

5) Close the connection object

By closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.

Syntax of close() method

public void close()throws SQLException 

Example to close connection

con.close(); 

Note: Since Java 7, JDBC has ability to use try-with-resources statement to automatically close resources of type Connection, ResultSet, and Statement.

Continue reading

The Introduction of Java Memory Leaks

One of the most significant advantages of Java is its memory management. You simply create objects and Java Garbage Collector takes care of allocating and freeing memory. However, the situation is not as simple as that, because memory leaks frequently occur in Java applications.

This post illustrates what is memory leak, why it happens, and how to prevent them.

1. What is Memory Leak?

Definition of Memory Leak: objects are no longer being used by the application, but Garbage Collector can not remove them because they are being referenced.

To understand this definition, we need to understand objects status in memory. The following diagram illustrates what is unused and what is unreferenced.

From the diagram, there are referenced objects and unreferenced objects. Unreferenced objects will be garbage collected, while referenced objects will not be garbage collected. Unreferenced objects are surely unused, because no other objects refer to it. However, unused objects are not all unreferenced. Some of them are being referenced! That's where the memory leaks come from.

2. Why Memory Leaks Happen?

Let's take a look at the following example and see why memory leaks happen. In the example below, object A refers to object B. A's lifetime (t1 - t4) is much longer than B's (t2 - t3). When B is no longer being used in the application, A still holds a reference to it. In this way, Garbage Collector can not remove B from memory. This would possibly cause out of memory problem, because if A does the same thing for more objects, then there would be a lot of objects that are uncollected and consume memory space.

It is also possible that B hold a bunch of references of other objects. Those objects referenced by B will not get collected either. All those unused objects will consume precious memory space.

3. How to Prevent Memory Leaks?

The following are some quick hands-on tips for preventing memory leaks.

1. Pay attention to Collection classes, such as HashMap, ArrayList, etc., as they are common places to find memory leaks. When they are declared static, their life time is the same as the life time of the application.

2. Pay attention to event listeners and callbacks. A memory leak may occur if a listener is registered but not unregistered when the class is not being used any longer.

3. "If a class manages its own memory, the programer should be alert for memory leaks." Often times member variables of an object that point to other objects need to be null out.

Continue reading

JDBC - Introduction

What is JDBC?

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database usage.

◉ Making a connection to a database.

◉ Creating SQL or MySQL statements.

◉ Executing SQL or MySQL queries in the database.

◉ Viewing & Modifying the resulting records.

Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to an underlying database. Java can be used to write different types of executables, such as −

◉ Java Applications

◉ Java Applets

◉ Java Servlets

◉ Java ServerPages (JSPs)

◉ Enterprise JavaBeans (EJBs).

All of these different executables are able to use a JDBC driver to access a database, and take advantage of the stored data.

JDBC provides the same capabilities as ODBC, allowing Java programs to contain database-independent code.

JDBC Architecture

The JDBC API supports both two-tier and three-tier processing models for database access but in general, JDBC Architecture consists of two layers −

◉ JDBC API: This provides the application-to-JDBC Manager connection.

◉ JDBC Driver API: This supports the JDBC Manager-to-Driver Connection.

The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases.

The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases.

Following is the architectural diagram, which shows the location of the driver manager with respect to the JDBC drivers and the Java application −

Common JDBC Components

The JDBC API provides the following interfaces and classes −

◉ DriverManager: This class manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication sub protocol. The first driver that recognizes a certain subprotocol under JDBC will be used to establish a database Connection.

◉ Driver: This interface handles the communications with the database server. You will interact directly with Driver objects very rarely. Instead, you use DriverManager objects, which manages objects of this type. It also abstracts the details associated with working with Driver objects.

◉ Connection: This interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with database is through connection object only.

◉ Statement: You use objects created from this interface to submit the SQL statements to the database. Some derived interfaces accept parameters in addition to executing stored procedures.

◉ ResultSet: These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data.

◉ SQLException: This class handles any errors that occur in a database application.

The JDBC 4.0 Packages

The java.sql and javax.sql are the primary packages for JDBC 4.0. This is the latest JDBC version at the time of writing this tutorial. It offers the main classes for interacting with your data sources.

The new features in these packages include changes in the following areas −

◉ Automatic database driver loading.

◉ Exception handling improvements.

◉ Enhanced BLOB/CLOB functionality.

◉ Connection and statement interface enhancements.

◉ National character set support.

◉ SQL ROWID access.

◉ SQL 2003 XML data type support.

◉ Annotations.

Continue reading

Java: Introduction to Collections

A collection — sometimes called a container — is simply an object that groups multiple elements into a single unit. Collections are used to store, retrieve, manipulate, and communicate aggregate data. Typically, they represent data items that form a natural group, such as a poker hand (a collection of cards), a mail folder (a collection of letters), or a telephone directory (a mapping of names to phone numbers). If you have used the Java programming language — or just about any other programming language — you are already familiar with collections.

What Is a Collections Framework?

A collections framework is a unified architecture for representing and manipulating collections. All collections frameworks contain the following:

◉ Interfaces: These are abstract data types that represent collections. Interfaces allow collections to be manipulated independently of the details of their representation. In object-oriented languages, interfaces generally form a hierarchy.

◉ Implementations: These are the concrete implementations of the collection interfaces. In essence, they are reusable data structures.

◉ Algorithms: These are the methods that perform useful computations, such as searching and sorting, on objects that implement collection interfaces. The algorithms are said to be polymorphic: that is, the same method can be used on many different implementations of the appropriate collection interface. In essence, algorithms are reusable functionality.

Apart from the Java Collections Framework, the best-known examples of collections frameworks are the C++ Standard Template Library (STL) and Smalltalk's collection hierarchy. Historically, collections frameworks have been quite complex, which gave them a reputation for having a steep learning curve. We believe that the Java Collections Framework breaks with this tradition, as you will learn for yourself in this chapter.

Benefits of the Java Collections Framework

The Java Collections Framework provides the following benefits:

◉ Reduces programming effort: By providing useful data structures and algorithms, the Collections Framework frees you to concentrate on the important parts of your program rather than on the low-level "plumbing" required to make it work. By facilitating interoperability among unrelated APIs, the Java Collections Framework frees you from writing adapter objects or conversion code to connect APIs.

◉ Increases program speed and quality: This Collections Framework provides high-performance, high-quality implementations of useful data structures and algorithms. The various implementations of each interface are interchangeable, so programs can be easily tuned by switching collection implementations. Because you're freed from the drudgery of writing your own data structures, you'll have more time to devote to improving programs' quality and performance.

◉ Allows interoperability among unrelated APIs: The collection interfaces are the vernacular by which APIs pass collections back and forth. If my network administration API furnishes a collection of node names and if your GUI toolkit expects a collection of column headings, our APIs will interoperate seamlessly, even though they were written independently.

◉ Reduces effort to learn and to use new APIs: Many APIs naturally take collections on input and furnish them as output. In the past, each such API had a small sub-API devoted to manipulating its collections. There was little consistency among these ad hoc collections sub-APIs, so you had to learn each one from scratch, and it was easy to make mistakes when using them. With the advent of standard collection interfaces, the problem went away.

◉ Reduces effort to design new APIs: This is the flip side of the previous advantage. Designers and implementers don't have to reinvent the wheel each time they create an API that relies on collections; instead, they can use standard collection interfaces.

◉ Fosters software reuse: New data structures that conform to the standard collection interfaces are by nature reusable. The same goes for new algorithms that operate on objects that implement these interfaces.

Continue reading

How to use Callable Statement in Java to call Stored Procedure? JDBC Example

The CallableStatement of JDBC API is used to call a stored procedure from Java Program. Calling a stored procedure follows the same pattern as creating PreparedStatment and than executing it. You first need to create a database connection by supplying all the relevant details e.g. database URL, which comprise JDBC protocol and hostname, username, and password. Make sure your JDBC URL is acceptable by JDBC driver you are using to connect to the database. Every database vendor uses different JDBC URL and they have different driver JAR which must be in your classpath before you can run the stored procedure from Java Program.

Once you are done with initial setup, you can obtain CallableStatement from Connection by calling prepareCall(String SQL) method, where SQL must be in the format required by your database vendor e.g. Microsoft SQL Server requires curly braces e.g.
{call Books.BookDetails_Get(?)}.

This stored proc requires an INPUT parameter which must be set by calling setXXX() method on the CallableStatement object before you can execute the query.

Once this is done, just call the executeQuery() method of CallableStatement and it will return the ResultSet contains all the rows returned by this stored proc.

Just loop through ResultSet and extract all the rows. You have successfully run the stored procedure from Java Program using CallableStatement.

Steps to call a stored procedure from Java

1) Create a database connection.

Connection con = null;
try {
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   String url = "jdbc:sqlserver://localhost:42588;";
   con = DriverManager.getConnection(url, "username", "pw");
} catch (Exception e) {
   e.printStackTrace();
}

2) Create a SQL String

You need to create SQL using a String variable to call the stored procedure, for example, CallableStatement e.g. {call Books.BookDetails_Get(?)}. This is database dependent, for Oracle the format is different it starts with BEGIN and ends with ENDS instead of curly braces e.g.

String SQL = "{call Books.BookDetails_Get(?)}" // for Microsoft SQL Server
String Oracle = "BEGIN BOOKDETAILS_GET(?); END;";

3) Create CallableStatement Object

You can create a CallableStatement by calling Connection.prepareCall(SQL) method, pass the SQL created in the previous step.

CallableStatement cs = con.prepareCall(SQL);

4)  Provide Input Parameters

You can set the input parameter by calling various setXXX() method depending upon the data type of query parameters on the CallableStatement object, similar to PreparedStatment e.g.

cs.setString(1, "982928");

5)  Call Stored Procedure

You can execute a stored procedure on the database by calling executeQuery() method of CallableStatement class, as shown below:

ResultSet rs = cs.executeQuery();

This will return a ResultSet object which contains rows returned by your stored procedure.

6) Extract Rows from ResultSet

You can get the data from the ResultSet by Iterating over ResultSet and print the results or create Java objects, as shown below:

while(rs.next()){
  System.out.println(rs.getString(1));
}

This will print the first column of every row. You should also close the ResultSet object once you are done with it.

Java Program to call Stored Procedure in SQL Server using CallableStatement

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 *
 * A Simple example to use CallableStatement in Java Program.
 */
public class Hello {

  public static void main(String args[]) {
   
    Connection con = null;
    try {
       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
       String url = "jdbc:sqlserver://localhost:42588;";
       con = DriverManager.getConnection(url, "username", "pw");
    } catch (Exception e) {
       e.printStackTrace();
    }

    String SQL = "{call Books.dbo.usp_BookDetails_Get(?)}";

    CallableStatement cs = con.prepareCall(SQL);

    cs.setString(1, "978-0132778046");

    cs.setString(2, "978-0132778047");

    ResultSet rs = cs.executeQuery();
   
    while(rs.next()){
      System.out.println(rs.getString(1));
    }
   
    rs.close();
  }
}

That's all about how to run CallableStatement in Java JDBC. If you are thinking to build your Data Access layer around stored procedures, which is a great design, then you should have a good understanding of CallableStatement.

They are the ones which are used to run the stored procedure from Java programs. By encapsulating your data access logic and SQL on a stored procedure, allow you to change them easily on SQL editor without making any change on Java side, which means you can implement new functionalities without building and deploying a new JAR file on Java side.

Continue reading

How to convert JSON String to Java object - Jackson Example

JSON stands for JavaScript object notation, is a lightweight text or string representation of an object and quickly becoming a popular data exchange format. Though it's pretty early to say that JSON is going to replace XML as popular data interchange format, It is certainly providing an alternative. JSON represent data in two format either an object or an array. JSON object is an unordered collection of key and value, similar to String representation of hash table. On the other hand, JSON Array is an ordered collection of values. The main difference between  JSON Object and  JSON array is there representation. JSON object is started with left brace { and ends with right brace } and key values are separated using a colon (:). On the other hand, JSON Array starts with left bracket [ and ends with right bracket ] and each value is separated by comma. By looking at structure, You can write  your JSON parser to parse JSON object or array to Java object, but you don't need to.

There are lot of open source library in Java which provides tried and tested way of converting JSON String to Java object e.g. Jackson and GSON. In this Java tutorial we will see example of converting a JSON String to Java object using Jackson library

How to convert JSON String to Java object using Jackson

It's very easy to create Java object from JSON String using Jackson library. It's literally require two lines of code to do this, as shown in following Java example. If you look at code, most of code is for creating Java class e.g. User in this case, while code required to convert JSON String to Java object is just two lines in fromJson(String json) method.

This method takes an Jon String which represent a User object in JSON format and convert it into Java User object. In this Java example I have create User as nested static class for convenience, You may create a separate top level class if needed.

import java.io.IOException;

import org.apache.log4j.Logger;
import org.codehaus.jackson.JsonParseException;
import org.codehaus.jackson.map.JsonMappingException;
import org.codehaus.jackson.map.ObjectMapper;

/*
 * Java program to convert JSON String into Java object using Jackson library.
 * Jackson is very easy to use and require just two lines of code to create a Java object
 * from JSON String format.
 *
 * @author http://javarevisited.blogspot.com
 */
public class JsonToJavaConverter {

        private static Logger logger = Logger.getLogger(JsonToJavaConverter.class);
   
   
        public static void main(String args[]) throws JsonParseException
                                                    , JsonMappingException, IOException{

                JsonToJavaConverter converter = new JsonToJavaConverter();
           
                String json = "{\n" +
                "    \"name\": \"Garima\",\n" +
                "    \"surname\": \"Joshi\",\n" +
                "    \"phone\": 9832734651}";
           
                //converting JSON String to Java object
                converter.fromJson(json);
        }
   
   
        public Object fromJson(String json) throws JsonParseException
                                                   , JsonMappingException, IOException{
                User garima = new ObjectMapper().readValue(json, User.class);
                logger.info("Java Object created from JSON String ");
                logger.info("JSON String : " + json);
                logger.info("Java Object : " + garima);
           
                return garima;
        }
   
   
        public static class User{
                private String name;
                private String surname;
                private long phone;
           
                public String getName() {return name;}
                public void setName(String name) {this.name = name;}

                public String getSurname() {return surname;}
                public void setSurname(String surname) {this.surname = surname;}

                public long getPhone() {return phone;}
                public void setPhone(long phone) {this.phone = phone;}

                @Override
                public String toString() {
                        return "User [name=" + name + ", surname=" + surname + ", phone="
                                        + phone + "]";
                }
           
             
        }
}

Output:
2013-01-07 01:15:05,287 0    [main] INFO  JsonToJavaConverter  - Java Object created from JSON String
2013-01-07 01:15:05,287 0    [main] INFO  JsonToJavaConverter  - JSON String : {
    "name": "Garima",
    "surname": "Joshi",
    "phone": 9832734651}
2013-01-07 01:15:05,287 0    [main] INFO  JsonToJavaConverter  - Java Object : User [name=Garima, surname=Joshi, phone=9832734651]

Dependency

As I said, You can either use Jackson or Gson to convert JSON String to Java object and in this Java tutorial we have used Jackson library for JSON to Java object conversion. If you are using Maven for dependency management than you can add following dependency in POM.xml :

<dependency>
      <groupId>org.codehaus.jackson</groupId>
      <artifactId>jackson-xc</artifactId>
      <version>1.9.11</version>
</dependency>

Or you can simply add following JAR files into your application’s classpath :

jackson-xc-1.9.11.jar
jackson-core-asl-1.9.11.jar
jackson-mapper-asl-1.9.11.jar

That's all on How to convert JSON String to Java object using Jackson library. Though, this is a trivial example and actual object could be more complex, it demonstrate the process of creating Java object from JSON String. You can also use other JSON library like GSON instead of Jackson to convert JSON String to Java object.

Continue reading