DB Operation - CallableStatement

CallableStatement

ResultSet

Retrieve & Modify ResultSet

A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database.

ResultSet object can be created through any object that implements the Statement interface, including PreparedStatement, CallableStatement and RowSet.

You access the data in a ResultSet object through a cursor.

Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet.

ResultSet Types

• TYPE_FORWARD_ONLY : The cursor moves forward only. Starts from before the first row to after the last row. It is default implementation.
• TYPE_SCROLL_INSENSITIVE : Cursor can move both forward and backward and it can move to an absolute position. The result set is insensitive to changes made to data in DB. 
• TYPE_SCROLL_SENSITIVE : It has same properties as that of TYPE_SCROLL_INSENSITIVE. The result set reflects changes made to the underlying data source while the result set remains open.

ResultSet Concurrency

The concurrency of a ResultSet object determines what level of update functionality is supported.
There are two concurrency levels:

• CONCUR_READ_ONLY: The ResultSet object cannot be updated using the ResultSet interface.
• CONCUR_UPDATABLE: The ResultSet object can be updated using the ResultSet interface.

The default ResultSet concurrency is CONCUR_READ_ONLY.

Cursor Objects

• next: Moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.
• previous: Moves the cursor backward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.
• first: Moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows.
• last:: Moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows.
• beforeFirst: Positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect.
• afterLast: Positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect.
• relative(int rows): Moves the cursor relative to its current position.
• absolute(int row): Positions the cursor on the row specified by the parameter row.

------------------------------------------------------------------------------------------------------------

public void modifyName(String oldName, String newName) throws SQLException {

    Statement statement = null;
    try {
        statement = con.createStatement();
        statement = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,                   ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet= stmt.executeQuery("SELECT * FROM STUDENT");

        while (resultSet.next()) {

            String name = resultSet.getString("name");

            if(name.equals(oldName)){

                   resultSet.updateString( "name", newName);
                   resultSet.updateRow();

            }
        }

    } catch (SQLException e ) {
        System.out.println(e.getMessage());
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

DB operations - Using prepared Statement

Prepared statement

• In prepared statement SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute statements multiple times.
• PreparedStatement is extension of Statement interface.

Advantages 

1. Improves performance of application - If same query is executed multiple times.
2. It is easy to insert and update SQL 99 datatype like CLOB, BLOB or OBJECT.
3. Provides programmatic approach for setting values.

-----------------------------------------------------------------------------------------------------------------------

package tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test_prepared {

Connection con=null;
PreparedStatement prstm=null;
ResultSet resultSet=null;


//Constructor that loads the driver manager and establishes connection with DB mentioned in URL
Test_prepared() throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "password");
}


//This method is called to close connection to DB once all the transaction are processed.
void closeConnection() throws SQLException{
if(con!=null)
con.close();
}


//Code to insert records into DB using PreparedStatement
void insertData(int id,String name) throws SQLException{
try{
prstm=con.prepareStatement("insert into Student values(?,?)");
prstm.setInt(1, id);
prstm.setString(2, name);
int count=prstm.executeUpdate();
System.out.println("Number of rows affected due to insertion = "+count);
}
catch(SQLException e){
System.out.println(e.getMessage());
}
finally{
if(prstm!=null){
prstm.close();
}
}
}


//Code to delete records into DB using PreparedStatement
void deleteData(int id) throws SQLException{
try{
prstm=con.prepareStatement("delete from Student where id = ?");
prstm.setInt(1, id);
int count=prstm.executeUpdate();
System.out.println("Number of rows affected due to deletion = "+count);
}
catch(SQLException e){
System.out.println(e.getMessage());
}
finally{
if(prstm!=null){
prstm.close();
}
}
}


//Code to select records from DB using PreparedStatement
void selectData(int id) throws SQLException{
try{
prstm=con.prepareStatement("select * from Student where id = ?");
prstm.setInt(1, id);
ResultSet resultSet=prstm.executeQuery();

while(resultSet.next()){
System.out.print(resultSet.getInt(1)+"\t"+resultSet.getString(2));
}
}
catch(SQLException e){
System.out.println(e.getMessage());
}
finally{
if(prstm!=null){
prstm.close();
}
}
}


//Code to insert records into DB using PreparedStatement and using addBatch()
void batchUpdate(int [] ids,String [] names) throws SQLException{
try{
int i=0;
prstm=con.prepareStatement("insert into Student values(?,?)");
prstm.setInt(1, ids[i]);
prstm.setString(2, names[i]);
prstm.addBatch();
i++;

prstm.setInt(1, ids[i]);
prstm.setString(2, names[i]);
prstm.addBatch();
i++;

prstm.setInt(1, ids[i]);
prstm.setString(2, names[i]);
prstm.addBatch();

int [] count=prstm.executeBatch();

for(int j=0;j<count.length;j++){
System.out.println("Number of rows affected due to query "+j+" = "+count[j]);
}
}
catch(SQLException e){
System.out.println(e.getMessage());
}
finally{
if(prstm!=null){
prstm.close();
}
}
}


public static void main(String[] args) {
try {
Test_prepared tp=new Test_prepared();

  tp.insertData(4, "Roark");
tp.insertData(5, "John");
tp.insertData(7, "Galt");
tp.batchUpdate(new int[]{10,11,12}, new String[]{"Jack","Reacher","Tom"});

tp.deleteData(4);
tp.selectData(10);

tp.closeConnection();
} catch (ClassNotFoundException | SQLException e) {
System.out.println(e.getMessage());
}
}
}

DataBase operations - Using Statement

Statement interface is used to execute SQL statements with no parameters.
In this tutorial we are going to learn how to use Statement interface to

1. create table
2. Insert records into DB
3. Delete records from DB
4. Update records in DB
5. Select records from DB
6. Execute bulk updates using batch

------------------------------------------------------------------------------------------------------------

package tutorial;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test_Statement {

Connection con=null;
Statement statement=null;
ResultSet resultSet=null;

//Constructor to load driver manager and create connection object
Test_Statement() throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "password");

}

//Method to close connection after transactions are completed
void closeConnection() throws SQLException{
if(con!=null)
con.close();
}

//Program to create table, We use execute(String) which returns boolean value on success or failure of execution
void createUserTable() throws SQLException{
try {
statement=con.createStatement();
boolean result=statement.execute("create table Student (id integer,name varchar(20))");
if(result)
System.out.println("Table successfully created !!");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


//Program to insert records into database, executeUpdate(String) is used whose return type is integer
void insertUserTable(int id,String name) throws SQLException{
try {
statement=con.createStatement();
int result=statement.executeUpdate("insert  into Student values("+id+" ,'"+name+"')");
if(result>0){
System.out.println("Number of rows were affected during insertion:: "+result);
}

} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


//Program to Delete record from database, executeUpdate(String) is used whose return type is count of rows affected by query
void deleteUserTable(int id) throws SQLException{
try {
statement=con.createStatement();
int result=statement.executeUpdate("delete from Student where id = "+id);
if(result>0){
System.out.println("Number of rows were affected during deletion:: "+result);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


//Program to demonstrate adding update queries to a batch and executing batch as whole 
void batchTest() throws SQLException{
try {
statement=con.createStatement();
statement.addBatch("insert into student values (10,'Shrey')");
statement.addBatch("insert into student values (14,'Vini')");
statement.addBatch("insert into student values (16,'Sanjay')");
int [] count=statement.executeBatch();
for(int i=0;i<count.length;i++)
System.out.println("Number of rows affected due to Query "+(i+1)+" = "+count[i]);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


//Program to select records from Database
void selectTest() throws SQLException{
try {
statement=con.createStatement();
resultSet=statement.executeQuery("select * from student");
while(resultSet.next()){
System.out.print(resultSet.getString(1));
System.out.print("\t"+resultSet.getString(2));
System.out.println();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


public static void main(String[] args) {
try {

Test_Statement test=new Test_Statement();
test.createUserTable();
test.insertUserTable(2,"vini");
test.deleteUserTable(2);
test.batchTest();
test.selectTest();
test.closeConnection();

} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

}

}

Basic steps involved in execution of SQL query Using JDBC - With sample code

Basic steps involved in execution of a SQL query using JDBC

1. Establish a connection 
2. Create a statement 
3. Execute the Query 
4. Close the connection

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

1. Establish connection with Database

• To load JDBC Driver into JVM we use Class.forName(String).
• DriverManager: This fully implemented class connects an application to a data source, which is specified by a database URL. JDBC 4.0 automatically loads Drivers.

               Class.forName("com.mysql.jdbc.Driver");

               Connection  con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root", "pas");

2. Create statement

A Statement is an interface that represents a SQL statement. Statement allows you to perform following activities.

1. Insert records into DB
2. Retrieve records from DB
3. Update records from DB
4. delete records from DB

There are 3 flavors of using statement

• Statement: Implement simple SQL statements with no parameters.
• PreparedStatement: (Extends Statement) Used for precompiling SQL statements that can contain input parameters.
• CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameter   

                Statement stmt = con.createStatement();

3. Execute SQL queries 

• executeUpdate: Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.
• executeQuery: Returns one ResultSet object - Used for executing select query.
• execute: Use this method if the query could return one or more ResultSet objects. Can be used to create table as well.

               int count = stmt.executeUpdate("insert into Employee values (1,'Sun')");

4. Closing Connection

we call Statement.close to immediately release the resources it is using. When you call this method, its ResultSet objects are closed.
We will also call Connection.close() to close connection with DB.

     finally { 

          if (stmt != null) { stmt.close(); }
          if(connection!=null){connection.close();}
     }

------------------------------------------------------------------------------------------------------------

Example Code 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.DriverManager;

public class Test{

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

Connection con=null;

Statement statement=null;

try{

Class.forName("com.mysql.jdbc.Driver");

con=DriverManager.getConnection("jdbc:mysql://localhost:3036/test","root","password");

statement=con.createStatement();

int rows=statement.executeUpdate("insert into employee values(1,'Sanjay')");

}

catch(ClassNotFoundException ce){

ce.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

finally{

if(statement!=null){

statement.close();
}
   if(connection!=null){
    connection.close();

   }

}

}

}

prerequisites for JDBC

Download the following software's from internet install Eclipse, JDK and MySQL database [J-Connector driver for MySQL] so that we can get started ....

Links ..

http://www.oracle.com/technetwork/java/javase/overview/index.html

http://www.eclipse.org/downloads/packages/eclipse-ide-java-ee-developers/junosr1

http://www.mysql.com/downloads/mysql/
http://www.mysql.com/downloads/connector/j/

Introduction to JDBC

Introduction to JDBC 

What is JDBC API?

JDBC API provides ways for Java applications to communicate with databases.

JDBC Architecture

• Java application that needs to communicate with database has to be programmed with JDBC API.
• The DriverManager class defines objects which can connect Java applications to a JDBC driver
• JDBC driver will support Data source like oracle, mySQL and SQl.

Two Tier Architecture

• A Java application / Applet will directly communicate with data source.
• A user's commands are delivered to the database or other data source, and the results of those statements are sent back to the user.
• The data source may be located on another machine to which the user is connected via a network.

 Three Tier Architecture

• In the three-tier model, commands are sent to a "middle tier" of services, which then sends the commands to the data source. 
• The data source processes the commands and sends the results back to the middle tier, which then sends them to the user. 
• The data source may be located on another machine to which the user is connected via a network. 

• Advantages 
• Middle tier has control over access and updates. 
• Simplifies deployment 
• Performance advantages