Category Archives: JDBC

JDBC SQLException Handling with Programming Example

In this chapter you will learn

1. What is Exception Handling in JDBC?
2. How to fix SQLException in JDBC?
3. SQLException Programming Example

What is Exception Handling in JDBC?

Exception is such type of condition when program encounters problem in execution and quit with a problematic error message. In JDBC, when program gets trouble in data source, it throws SQLException. However, there are several many exceptions in Java but while we are studying JDBC, SQLException is most common and we will cover it through this chapter.

A SQLException can occur in JDBC Driver or inside database. In this chapter you will learn how to handle this exception.

SQLException Methods

Method Description
getErrorCode() Gets the error number associated with the exception.
getMessage() Gets the JDBC driver’s error message for an error, handled by the driver or gets the Oracle error number and message for a database error.
getSQLState() Gets the XOPEN SQLstate string. For a JDBC driver error, no useful information is returned from this method. For a database error, the five-digit XOPEN SQLstate code is returned. This method can return null.
getNextException() Gets the next Exception object in the exception chain.
printStackTrace() Prints the current exception, or throwable, and it’s backtrace to a standard error stream.
printStackTrace(PrintStream s) Prints this throwable and its backtrace to the print stream you specify.
printStackTrace(PrintWriter w) Prints this throwable and it’s backtrace to the print writer you specify.

Programming Example

package AdvanceJDBC;

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

public class SQLException_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args) throws SQLException
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query="SELECT * FROM ITEM";
	    
	    //Step 4 : Run Query In ResultSet
	    ResultSet rset = stmt.executeQuery(query);
	    while(rset.next())
	    {
	    	System.out.print("ID: " + rset.getInt(1));
	    	System.out.print(" Product : "+rset.getString(2));
	    	System.out.println(" Price : "+rset.getString(3));
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Summary

In this chapter you learned what is SQLException in JDBC and how to handle it using exception handling.


JDBC Callable Statement – Execute Store Procedure

In this tutorial you will learn

1. What is Callable Statement in JDBC?
2. How to use Store Procedure in JDBC?
3. Programming Example

What is Callable Statement in JDBC?

The CallableStatement Interface is used in accessing and executing Store Procedure and Function. Store Procedure is a group of SQL Statement that encapsulates all the queries and gets compiled. Once store procedure gets compiled it can be executed so many times without compilation. This makes it robust, faster and high performing. It accepts parameter for sql queries that is hidden inside it and gets executed. The Callable Statement in JDBC is used to manipulating these store procedure.

How to use Callable Statement to Call Store Procedure?

Here, I am going to present a simple callable statement example. This example includes following steps.

1. First step is creating a store procedure. I will create a store procedure to insert data into ITEM table.
2. Second Step is Using CallableStatement, I will execute this store procedure and insert data to table.

Programming Example

1. Store Procedure: Creating Store Procedure

You need to execute this statement in your database server sql window. Here, I am using MySQL and used PHPMyAdmin page to create this store procedure.

DELIMITER //
CREATE PROCEDURE InsertProc(IN prd varchar(50), IN prc varchar(10))
BEGIN
  INSERT INTO ITEM(PRODUCT,PRICE) VALUES(prd,prc);
END //
DELIMITER ;

This statement will create InsertProc Store procedure which takes 2 parameter prd and prc.

2. Use CallableStatement in JDBC to call and execute this store procedure.
package AdvanceJDBC;

import java.sql.*;

import com.mysql.jdbc.CallableStatement;

public class Callable_StoreProc
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	CallableStatement csmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize CallableStatement
	    csmt=(CallableStatement)con.prepareCall("{call InsertProc(?,?)}");
	    //Step 3 : Execute CallableStatement with Store Procedure
	    csmt.setString(1, "Intel i7 Processor");
	    csmt.setString(2, "27000");
	    csmt.execute();
	    System.out.println("Record Inserted Successfully");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Summary

In this chapter you learned how to use CallableStatement in JDBC to execute store procedure in MySQL Database. I have kept this tutorial simple and clean with complete programming example. Hope, this will help you to understand basics of callable statement in JDBC.


JDBC – Batch Processing Tutorial with Example

In this tutorial you will learn

1. What is Batch Processing in JDBC?
2. How to Batch Processing in JDBC?
3. Methods and Programming Example

What is Batch Processing in JDBC?

The word “Batch Processing” tells everything itself by its name. Process of executing multiple queries at once is called Batch Processing. Batch Processing makes the performance fast and efficient, that executes multiple sql statements quickly. JDBC Batch Processing provides method for implementing bulk query execution.

Methods

Method Description
void addBatch(String query) It adds query into batch.
int[] executeBatch() It executes the batch of queries.

Programming Example

package AdvanceJDBC;

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

public class BatchProcessing_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args) throws SQLException
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);
	    con.setAutoCommit(false);
	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    
	    //Step 3 : SQL Query
	    String query1="INSERT INTO ITEM(PRODUCT,PRICE) VALUES('Keyboard','770')";
	    String query2="INSERT INTO ITEM(PRODUCT,PRICE) VALUES('WebCam','1150')";
	    String query3="INSERT INTO ITEM(PRODUCT,PRICE) VALUES('HardDrive','3800')";
	    String query4="INSERT INTO ITEM(PRODUCT,PRICE) VALUES('Printer','12000')";
	    String query5="INSERT INTO ITEM(PRODUCT,PRICE) VALUES('Bluetooth','220')";
	    
	    //Step 4 : Adding query to Batch
	    stmt.addBatch(query1);
	    stmt.addBatch(query2);
	    stmt.addBatch(query3);
	    stmt.addBatch(query4);
	    stmt.addBatch(query5);
	    
	    //Step 5 : Execute Batch
	    stmt.executeBatch();
	   
	    con.commit();
	    System.out.println("Batch Update Successful");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    con.rollback();
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output:
Batch Update Successful
Closing the connection.


Summary

In this tutorial you learned how to use Batch Processing in JDBC. The programming example explains this concept clearly. In the next chapter you will learn about Store Procedure in JDBC.


JDBC Transaction Tutorial: Commit() and Rollback() Example

In this tutorial you will learn

1. What is Transaction Management in JDBC?
2. Advantage of Transaction Management
3. Programming Example

What is Transaction Management in JDBC?

Transaction Management works great when you need to execute set of task and each task executes when previous task completes. Transaction Management ensures that all the task executes successfully and if one task fails, the whole task would be rollback to previous state. Simply, either all the statements are executed, or none of the statements is executed.

Fact About Transaction

1. ACID: It represents ACID Properties.

Atomicity means either all successful or none.

Consistency ensures bringing the database from one consistent state to another consistent state.

Isolation ensures that transaction is isolated from other transaction.

Durability means once a transaction has been committed, it will remain so, even in the event of errors, power loss etc.


2. Disable Auto Commit Mode: When the connection is created it is auto commit mode. It means all the individual SQL Statements will be treated as Transaction. When using Transaction, Disable Auto Commit Mode and call it explicitly. After disabling commit mode, no statement will be execute until you call Commit() method explicitly. Each statement will be executed after the previous call of Commit method.

con.setAutoCommit(false);

In JDBC, Connection interface provides methods to manage transaction.

Method Description
void setAutoCommit (boolean status) It is true bydefault means each transaction is committed bydefault.
void commit() commits the transaction.
void rollback() cancels the transaction.

Advantage of Transaction Management

1. Transaction Management widely used in financial application where you want to ensure that if any problem happens meanwhile the payment process, the whole transaction rollback.
2. Transaction Management is also very beneficial when you need to insert multiple row in multiple table simultaneously. It ensures that all the table gets successful execution of statement and if any table fails to execute statement, all the row roll backed and none of table affected.
3. Transaction Treats all the SQL statements as a single logical unit and if one statement fails the entire transaction fails.
4. Commit and Rollback : Commit() method does the changes in database table and Rollback methods undo all the changes done by current connection con.

Programming Example

In this programming example, I will insert two row in a two different table using Transaction. This example explains Commit() method. In the next example you will get Rollback.

Step 1. Create Two Tables table1 and table2 with following description
CREATE TABLE Table1 (
  ID int(11) NOT NULL AUTO_INCREMENT,
  PRODUCT varchar(50) NULL,
  PRICE varchar(10) NULL,
PRIMARY KEY(ID)
)

CREATE TABLE Table2(
  ID int(11) NOT NULL AUTO_INCREMENT,
  PRODUCT varchar(50) NULL,
  PRICE varchar(10) NULL,
PRIMARY KEY(ID)
)

You can use your own table to execute query. If you don’t have any, then create table using above query.

Step 2. Programming Example of Commit() Method.
package AdvanceJDBC;

import java.sql.*;

public class Transaction_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);
	    con.setAutoCommit(false);
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query1="INSERT INTO Table1(PRODUCT,PRICE) VALUES('Laptop','29000')";
	    String query2="INSERT INTO Table2(PRODUCT,PRICE) VALUES('Mouse','305')";
	    
	    stmt.executeUpdate(query1);
	    stmt.executeUpdate(query2);
	    //If you run this program without con.commit you will notice that there is no insert in table1 and table2
	    con.commit();
	    System.out.println("Row Inserted");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Row Inserted
Closing the connection

Rollback Example

In this example I will use Rollback method to ensure all the successful insertion in both table. You will see what happen when statement execution fails in table 2.

Programming Example

Example 1: With Commit(false)

In this program I have Passed long value in PRICE column for table2. It will raise exception because of size of PRICE is set to varchar(10). This is for showing you what happened when one query executed but another get failed in transaction. You will notice that there were no changes in table.

package AdvanceJDBC;

import java.sql.*;

public class Rollback_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args) throws SQLException
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);
	    con.setAutoCommit(false);
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query1="INSERT INTO Table1(PRODUCT,PRICE) VALUES('Mobile','11500')";
	    stmt.executeUpdate(query1);
	    System.out.println("Table1 Successfull");
	    String query2="INSERT INTO Table2(PRODUCT,PRICE) VALUES('Charger','3055241234534')";
	    stmt.executeUpdate(query2);
	    //If you run this program without con.commit you will notice that there is no insert in table1 and table2
	    con.commit();
	    System.out.println("Row Inserted");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    con.rollback();
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output
Table1 Successfull
Cannot connect !
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘PRICE’ at row 1

Table 1
Table 1
Table 2
Table 2

 

Example 2: Without Commit() method or Commit(true)

See this example. In this example I haven’t set Commit(false) so when the program executes query, first statement successfully inserted but raise exception in second statement. It is because I have passed more than 10 character long in PRICE that is set to varchar(10) only.

package AdvanceJDBC;

import java.sql.*;

public class Rollback_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args) throws SQLException
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);
	    con.setAutoCommit(true);
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query1="INSERT INTO Table1(PRODUCT,PRICE) VALUES('Mobile','11500')";
	    stmt.executeUpdate(query1);
	    System.out.println("Table1 Successfull");
	    String query2="INSERT INTO Table2(PRODUCT,PRICE) VALUES('Charger','3055241234534')";
	    stmt.executeUpdate(query2);
	    //If you run this program without con.commit you will notice that there is no insert in table1 and table2
	    con.commit();
	    System.out.println("Row Inserted");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    //con.rollback();
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Table1 Successfull
Cannot connect !
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘PRICE’ at row 1

Table 1
Table 1
Table 2
Table 2

Summary

In this tutorial you learned what is Transaction Management in JDBC and how to use it in Programming. The Commit(false) ensures all the successful execution of statement. If any of them not execute, the whole process gets failed. Rollback() method reverse all the changes that is made by current connection.


Java JDBC – How to Use PreparedStatement in Programming

In this chapter you will learn

1. What is PreparedStatement in Java?
2. How it is used for storing or updating data?
3. Programming Example

What is PreparedStatement?

The PreparedStatement object is derived from Statement class. Statement is used for sending and executing query in database but it is more convenient to use PreparedStatement to send and execute command in database. PreparedStatement uses parameterized query and keeps precompiled sql statement so it is faster than Statement class because it is compiled once and can be used multiple times.

PreparedStatement ps=con.prepareStatement("INSERT INTO ITEM VALUES(?,?)");

PreparedStatement is used to execute parameterized query. It uses ? parameter at the place of value and later store value in it. As we initialize PreparedStatement, it gets compiled and then can be used so many times directly. Once database engine compiles PreparedStatement, It doesn’t get compiled next time and executed directly. It makes it much efficient and faster than Statement class.

Method Description
executeQuery This method executes sql query in PreparedStatement and returns ResultSet.
updateQuery This method executes sql query in PreparedStatement and used for Only with Insert, Update or Delete command or sql statement that return nothing.
public void setInt(int paramIndex, int value) sets the integer value to the given parameter index.
public void setString(int paramIndex, String value) sets the String value to the given parameter index.
public void setFloat(int paramIndex, float value) sets the float value to the given parameter index.
public void setDouble(int paramIndex, double value) sets the double value to the given parameter index.

INSERT RECORD using PreparedStatement

package JavaStatements;
import java.sql.*;

public class PreparedStatement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	//Statement stmt = null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    
	    //Step 2 : Initialize PreparedStatement
	    ps= con.prepareStatement("INSERT INTO ITEM(PRODUCT,PRICE) VALUES(?,?)");
	    
	    //Step 3 : Set Value for ? Parameter    
	    //executing 1st query
	    ps.setString(1,"Java eBook");
	    ps.setString(2, "460");	    
	    ps.executeUpdate();
	    System.out.println("Java eBook Stored Successfully");
	    
	    //executing 2nd query
	    ps.setString(1, "JDBC eBook");
	    ps.setString(2, "830");
	    ps.executeUpdate();
	    System.out.println("JDBC eBook Stored Successfully");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output


Java eBook Stored Successfully
JDBC eBook Stored Successfully
Closing the connection.


UPDATE RECORD using PreparedStatement

package JavaStatements;
import java.sql.*;

public class PreparedStatement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	//Statement stmt = null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    
	    //Step 2 : Initialize PreparedStatement
	    ps= con.prepareStatement("UPDATE ITEM SET PRODUCT = ? WHERE ID = ?");
	    
	    //Step 3 : Set Value for ? Parameter    
	    //executing 1st query
	    ps.setString(1,"MySQL eBook");
	    ps.setInt(2, 13);    
	    ps.executeUpdate();
	    System.out.println("MySQL eBook Updated Successfully");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output


MySQL eBook Updated Successfully
Closing the connection.


DELETE RECORD using PreparedStatement

package JavaStatements;
import java.sql.*;

public class PreparedStatement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	//Statement stmt = null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    
	    //Step 2 : Initialize PreparedStatement
	    ps= con.prepareStatement("DELETE FROM ITEM WHERE ID = ?");
	    
	    //Step 3 : Set Value for ? Parameter   	    
	    ps.setInt(1, 13);    
	    ps.executeUpdate();
	    System.out.println("MySQL eBook Deleted Successfully");
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output


MySQL eBook Deleted Successfully
Closing the connection.


SELECT Record in ResultSet using PreparedStatement

package JavaStatements;
import java.sql.*;

public class PreparedStatement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	//Statement stmt = null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    
	    //Step 2 : Initialize PreparedStatement
	    ps= con.prepareStatement("SELECT * FROM ITEM");
	    ResultSet rset=ps.executeQuery();
	    
	    while(rset.next())
	    {
	    	System.out.println("ID : " + rset.getInt(1) + ", PRODUCT : " + rset.getString(2) + ", PRICE : " + rset.getString(3));
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output


ID : 1, PRODUCT : MousePad, PRICE : 190
ID : 2, PRODUCT : Stationary, PRICE : 2870
ID : 3, PRODUCT : Books, PRICE : 765
ID : 4, PRODUCT : HardDisk, PRICE : 3887
ID : 5, PRODUCT : Ram, PRICE : 953
ID : 6, PRODUCT : Printer, PRICE : 8746
ID : 7, PRODUCT : Keyboard, PRICE : 646
ID : 8, PRODUCT : Mouse, PRICE : 947
ID : 9, PRODUCT : HDMI CABLE, PRICE : 850
ID : 11, PRODUCT : Charger, PRICE : 1800
ID : 12, PRODUCT : MainJava Logo, PRICE : 900
ID : 14, PRODUCT : JDBC eBook, PRICE : 830
Closing the connection.


Summary

In this tutorial you learned how to use PreparedStatement for Storing, Retrieving, Updating or Deleting row in MySQL table using JDBC. This chapter includes all the programming examples that demonstrate all the theory well.


Java JDBC – Statement Object to Execute Insert, Update and Delete

In this chapter you will learn:

1. What is Statement Interface in Java JDBC?
2. How to use Statement Object to execute Insert, Update, Select and Delete Query

What is Statement Interface in Java JDBC?

Statement Object is used for executing sql query like Insert, Update, Delete and Select in the database. It provides methods to execute queries with the database.

Programming Example

Select Record in ResultSet using Statement

package JavaStatements;

import java.sql.*;

public class Statement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    
	    //Select Query
	    String querySelect="SELECT * FROM ITEM";
	    ResultSet rset=stmt.executeQuery(querySelect);
	    while(rset.next())
	    {
	    	System.out.println("ID: "+rset.getInt(1) + ", PRODUCT: " + rset.getString(2) + ", Price: " + rset.getString(3));
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

ID: 1, PRODUCT: MousePad, Price: 190
ID: 2, PRODUCT: Stationary, Price: 2870
ID: 3, PRODUCT: Books, Price: 765
ID: 4, PRODUCT: HardDisk, Price: 3887
ID: 5, PRODUCT: Ram, Price: 953
ID: 6, PRODUCT: Printer, Price: 8746
ID: 7, PRODUCT: Keyboard, Price: 646
ID: 8, PRODUCT: Mouse, Price: 947
ID: 9, PRODUCT: HDMI CABLE, Price: 850
ID: 11, PRODUCT: Charger, Price: 1800
ID: 12, PRODUCT: MainJava Logo, Price: 900
_

Insert Row in Table using Statement

package JavaStatements;

import java.sql.*;

public class Statement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query   
	   	    
	    //Insert Query
	    String queryInsert="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('876','Screen Guard')";
	    stmt.executeUpdate(queryInsert);
	    System.out.println("Row Inserted");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Row Inserted
Closing the connection.

Update Record in Table using Statement

package JavaStatements;

import java.sql.*;

public class Statement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    
	   
	    
	    //Update Query
	      String queryUpdate="UPDATE ITEM SET PRICE = 455 WHERE PRODUCT = 'Screen Guard'";
	      stmt.executeUpdate(queryUpdate);
	      System.out.println("Row Updated");
	    
	        
	    String querySelect="SELECT * FROM ITEM";
	    ResultSet rset=stmt.executeQuery(querySelect);
	    while(rset.next())
	    {
	    	System.out.println("ID: "+rset.getInt(1) + ", PRODUCT: " + rset.getString(2) + ", Price: " + rset.getString(3));
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output
Row Updated
Closing the connection.

Delete Row in Table using Statement

package JavaStatements;

import java.sql.*;

public class Statement_Example
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    	    
	    //Delete Query
	    String queryDelete="DELETE FROM ITEM WHERE PRODUCT = 'Screen Guard'";
	    stmt.executeUpdate(queryDelete);
	    System.out.println("Row Deleted");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Row Deleted
Closing the connection.

Summary

It is very easy to use Statement Interface in JDBC programming for executing queries in database. However, it is not efficient way to run query in database. Instead of Statement you can use PreparedStatement, which is efficient and faster than Statement Object. In the next chapter you will learn about PreparedStatement in JDBC.


JDBC RESULTSET – Insert Row in MySQL Database Table

In this chapter you will learn

1. How to insert row in Database Table using JDBC RowSet

This chapter will teach you, how can you use ResultSet to insert row in database table. Always remember in order to update or insert row in database table you need to set ResultSet TYPE_SCROLL_INSENSITIVE and CONCUR_UPDATABLE.

      Statement stmt = con.createStatement(
                                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);

If you don’t set these two parameter ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, then you will get following error message on screen.

com.mysql.jdbc.NotUpdatable: Result Set not updatable.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.

Programming Example of Updatable RowSet in JDBC

package DataSetExample;

import java.sql.*;

public class JDBC_ResultSet
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
	    //Step 3 : SQL Query
	    String query="SELECT * FROM ITEM";
	    
	    //Step 4 : Run Query In ResultSet
	    ResultSet rset = stmt.executeQuery(query);
	    	    
	    rset.moveToInsertRow();
	    rset.updateString("PRODUCT", "Charger");
	    rset.updateString("PRICE", "1800");
	    rset.insertRow();
	    rset.moveToCurrentRow();
	    System.out.println("Row Inserted");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Row Inserted
Closing the connection.
_

Summary

In this chapter you learn how to insert row in MySQL Database Table using RowSet Object in JDBC.


JAVA JDBC – Update Row in ResultSet to Database Table

In this chapter you will learn:

1. How to Update a Row in ResultSet in JDBC?

You can update a row in table using RowSet updateString() and updateRow() method. There are 3 steps while updating a row in rowset.

1. Select row which you want to update. You can select a row using absolute(int rownumber) method.

rset.absolute(9);

2. Execute updateString(string column_name, string value) method.

rset.updateString("PRODUCT", "HDMI CABLE");

It will update 9th row of PRODUCT column with string value ‘HDMI CABLE’ in RowSet. Remember, this update reflects only rowset and it will not reflect in database table until you use updateRow() method.

3. Execute updateRow() command to make changes in database table.

Update Row in MySQL Database using ResultSet Object

package DataSetExample;

import java.sql.*;

public class ResultSet_Update
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
	    //Step 3 : SQL Query
	    String query="SELECT * FROM ITEM";
	    
	    //Step 4 : Run Query In ResultSet
	    ResultSet rset = stmt.executeQuery(query);
	    	    
	    //Update String
	    
	    //absulute() method puts cursor or pointed at the given row
	    rset.absolute(9);
	    rset.updateString("PRODUCT", "HDMI CABLE");
	    rset.updateRow();
	    System.out.println("Row Updated");
	    
	    //System.out.println(rset.getMetaData());
	    
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Before Update
Before Update
After Update
After Update

Summary

In this chapter you learn how to update a database table row using JDBC RowSet object. In the next chapter you will learn how to insert a row in database table using JDBC RowSet insertRow() method.