What is CallableStatement in java?

A procedure exists in database.
JDBC provides CallableStatement to invoke stored procedure available in database.
Below is simple procedure which accepts student id and produces student name as OUT parameter.

DELIMITER //
CREATE PROCEDURE getStdntName1(IN stdntid BIGINT, OUT stdntname VARCHAR(500))
BEGIN
SELECT sname INTO stdntname FROM students WHERE sid=stdntid;
END //
DELIMITER ;

Steps: #1. Save above stored procedure as a abcd.sql in C drive.
#2. From MySQL prompt, run the command “create database aaaa;
#3. Then run “use aaaa;
#4. Then create procedure in db by running “source c:\abcd.sql

Below is Java Program to invoke above stored procedure.

//STEP 1. Import required packages
import java.sql.*;

public class CallableStatementDemo {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/trial";

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "abcdfe";
   
   public static void main(String[] args) {
   Connection conn = null;
   CallableStatement stmt = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName(JDBC_DRIVER);

      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Execute a query
      System.out.println("Creating statement...");
      String sql = "{call getStdntName1(?, ?)}";//? is place holder for IN and OUT parameters
      stmt = conn.prepareCall(sql);
      
      //Bind IN parameter first, then bind OUT parameter
      int student_id = 9;
      stmt.setInt(1/*parameter number*/, student_id); // This would set ID
      // Because second parameter is OUT so register it
      stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
      
      //Use execute method to run stored procedure.
      System.out.println("Executing stored procedure..." );
      stmt.execute(); //now stored procedure gets executed in the database

      //Retrieve student name with getXXX method
      String student_name = stmt.getString(2);//2 is OUT parameter number
      
      
      System.out.println("Student with ID:" +student_id + " is " + student_name);
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
         conn.close();
      }catch(SQLException se2){
      }// nothing we can do
   }//end try
   System.out.println("Done!");
}//end main
}//end JDBCExample

You may also like to read:

How to Connect Java Program to a Database

Advantages of Hibernate Framework

Traditional way of storing objects in a database table, is to break up object into primitive data types, and store each primitive data in a table column. Application developer need to spend more time on writing mapping source code between class data members and table columns,etc… Situation worsens when relationship exists between objects, which need to be stored in database.

Hibernate provides solution for above problems.

Below is anatomy of Java Application, which uses Hibernate Framework.

hibernate-advantages
hibernate-advantages

1.Reduces size of code, hence development of Application is faster, which in turn controls cost of overall project.
When Hibernate is used, you need not write code to perform below routine activities
Connecting to database, retrieve individual columns from database table and creating object out of it , and vice versa.

2.Transaction Control: Hibernate has built in Transaction Management. A Transaction can be started, commited or rolled back by using Hibernate.
3.Automatic creation and updation of tables or columns. Required tables and columns are automatically created or updated, by provding hbm2ddl property in hbm file. Hibernate gets details of table names, column names, type,etc… from Hibernate Mapping file(i..e hbm.xml)

4.Provides HQL, Hibernate Query language, using which developer need to provide queries in terms of class names, and data members, instead of focusing on tables and columns.

5.Hibernate Caching, caches objects, so that when same object(s) are queried next, object from cache is returned, and database hit is avoided. This improves overall performance of Application.

In you may find, few program statements used again and again, like openSession(), beginTransaction(), commit(), rollBack(), close(),etc…which can be avoided multiple times, by better designing your application. Another alternative to further reduce Hibernate code, is to use combination of Spring and Hibernate.

Other facts:
Hibernate Framework further depends on JDBC, to interact with underlying database. Hence JDBC driver jar file need to be added to projects using Hibernate.

Such Framework is referred as ORM(Object Relational Mapping)

You may also like to read:
Mapping Type in HIbernate Mapping file
DDL with JDBC Statments
How many types of JDBC Drivers exist

How to Connect Java Program to a Database

Below are steps involved in connecting a Java Program to MySQL database, and querying for data.
#1. import java.sql.*; to use classes or interfaces to connect Java App to database.
#2. Register and load JDBC Driver, using Class.forName(“JDBC_Driver_Class”);
#3. Invoke DriverManager.getConnection(), by passing database URL, username, password.
#4. Using Connection returned from step #3. create Statement
#5. Execute SQL Query, which returns ResultSet
#6. Now iterate through ResultSet,
#7. fetch each Column data, and print.
#8. Any problem in connecting to db, db name, and db credentials causes SQLException.

Also note that, JDBC jar file need to be added to the project, to connect to database. For example mysql JDBC driver jar file is available here. https://dev.mysql.com/downloads/connector/j/5.0.html

//Step 1
import java.sql.*;

public class DatabaseApp {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost:3306/mondaybt";
//jdbc:mysql is the driver used to connect to underlying db   
//abcd is database name
   //localhost is domain name where database server is available
   //3306 is port number
   

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "rswxyz";
   
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      //Step 2, Register JDBC driver
      Class.forName(JDBC_DRIVER);

      //Step 3, Open connection to db server
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //Step 4, create Statement object
      System.out.println("Creating statement...");
      stmt = conn.createStatement();
      String sql;
      sql = "SELECT * FROM student where sname like 'k%'";

      //Step 5, execute SQL Query
      ResultSet rs = stmt.executeQuery(sql);

      //Step 6, iterate thru result set
      while(rs.next()){
         //Retrieve by column name
         //Can retrieve values with column index also eg. getInt(int column_index); 
         int id=0;
          id  = rs.getInt("sid");
         String name = rs.getString("sname");
         //Step 7, fetch each Column data
         String addr = rs.getString("saddr");
         String fname = rs.getString("sfather_name");
         //Display values
         System.out.println("Row Number:"+rs.getRow());
         System.out.print("ID: " + id);
         
         System.out.print(", Name: " + name);
         System.out.print(", Address: " + addr);
         System.out.print(", Father name: " + fname);
      }
      
      //Step 6, Close ResultSet,Statement
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Step 7
      se.printStackTrace();
   }catch(Exception e){
      e.printStackTrace();
   }
   System.out.println("Exiting Program...");
}//end main
   }

How many types of JDBC Drivers are there?
Can we create db table, from Java App
Difference between Statement and PreparedStatement

SQL DDL with JDBC Statements

There are five different types of SQL statements. SQL stands for Structured Query Language.
In this post we are considering MySQL as example.

#1.DDL, Data Definition Language:
Below SQL statements are considered as DDL
CREATE DATABASE
CREATE TABLE
ALTER
DROP

#2.DML, Data Manipulation Language:
Below SQL statements are considered as DML
INSERT
UPDATE
DELETE
TRUNCATE

#3.DQL, Data Query Language:
Below SQL statement are considered as DQL
SELECT

#4.TCL, Transaction Control Language:
Below SQL statements are considered as TCL
COMMIT
ROLLBACK

#5.DCL, Data Control Language:
Below SQL statements are considered as DCL
GRANT
REVOKE

Can DDL statements be invoked from JDBC?
Yes, DDL statements like CREATE, ALTER, DROP can be executed from JDBC Statement. MySQL JDBC jar need to be added to Project, for this Program to connect to Database.

Below is an example, which is self explanatory

import java.sql.*;

public class DDLJDBC {

    public static void main(String[] args)
    {
    try
    {
        Class.forName("com.mysql.jdbc.Driver");

        System.out.println("Connecting to database...");

        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mondaybt",
                "myuser","mypassword");

        System.out.println("Creating Statement...");

        Statement stmt = con.createStatement();
        stmt.execute("create table institute(id bigint, sname varchar(50), saddr varchar(50))");

        System.out.println("Creating Table...");
        stmt.execute("alter table institute add column sfather_name varchar(100)");

        System.out.println("Altering Table...");

        stmt.close();
        con.close();
    }
    catch(SQLException se)
    {
        se.printStackTrace();
    }
    catch(Exception ep)
    {
        ep.printStackTrace();
    }
    }
}

You may also like to Read:
What is JDBC?

Different Types of JDBC Drivers

These days almost no Enterprise Application exist without Database, to store data. And for Java Applications to interact with underlying databases, JDBC Driver is required.

JDBC Driver acts as an interface between Java Application and Database server, as shown in below picture.

JDBC Driver
JDBC Driver

Due to historical reasons there are four different types of JDBC Drivers.

Type 1 JDBC Driver: JDBC-ODBC Bridge driver (Bridge): An ODBC driver uses the Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data.  JDBC-ODBC bridge further interacts with ODBC driver, which further interacts with database. These Type of drivers were used during initial versions of Java in 90’s. There is performance hit as JDBC-ODBC bridge causes additional overhead, when Java app interacts with database.

Type 2 JDBC Driver: Native-API/partly Java driver (Native): These drivers again use a Java (JDBC) API, and further interact with local shared libraries in C or C++, which really interact with the Database Server. This approach uses Java Native API feature to interact with C/C++ code. When performance tuning is done in C/C++ libraries there is a chance that Type 2 Drivers can perform similar or better than Type 4.

Type 3 JDBC Driver: AllJava Net-protocol driver (Middleware): Here Java Application send request to Network Proxy Server, which further interacts with Database Server.

Type 4 JDBC Driver: All Java Native-protocol driver (Pure). These type of Driver are completely implemented in Java, and hence they are platform independent. Currently, Type 4 drivers are being widely used. Type 4 Driver for each database are provided by respective Database vendor, as a JAR file. This JAR file need to be added to Java Project, which needs to interact with database. For eg. MySQL  JDBC Driver is provided in below link  MySQL JDBC Driver Download(Jar)
This jar file can be used on any platform.

Reason to have above different types, is due to performance and portability reasons.

Type 1 has worst Performance and Portability, while Type 4 has better Performance and Portability, comparing with other Driver Types.

Currently Type 4 Drivers are used widely.