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