Java Database Connectivity



Introduction to databases and SQL

Enterprise (business organization) data is stored in a database. The database is the heart of an enterprise system. A database is the collection of related data and the relationships supporting the data. Database Management System (DBMS) is software that is used to access data in the database. The primary objective of a DBMS is to provide a convenient environment to retrieve and store database information. SQL (Structured Query Language) is used to communicate with the DBMS to deal with database data. IBM developed this language in 1970s. SQL comprises of the following important sub languages.

1. Data Definition Language (DDL): - It is used to create, alter and drop tables.

2. Data Manipulation Language (DML): - It is used to INSERT, UPDATE and DELETE data.

3. Data Query Language (DQL): - It is used to retrieve data from the from the database table using SELECT.

In a Relational Database Management System, for example in Oracle, data is stored in the form of tables. Each table is made up of one or more rows. Each row contains a number of fields, corresponding to the columns or attributes of the table. Customers’ information, Employees’ information, Students’ information is represented in the form of corresponding tables.

Student table

|ROLLNO |NAME |MARKS |

|1001 |Rama |70 |

|1002 |David |60 |

|1003 |Rahim |65 |

In the above table, ROLLNO, NAME and MARKS are the columns of the table, which are the attributes of each student. Each row in the table is known as a record. Each record represents one student information. Before we create such a table and store data in it, we need to know few SQL data types. While creating a table we need to specify the column type. In the above table, ROLLNO type is NUMBER. NAME is VARCHAR and MARKS also NUMBER type.

Creating a table

In order to create a table in the database, we follow the given syntax.

CREATE TABLE TABLENAME(COLUMN1 TYPE(SIZE), COLUMN2 TYPE(SIZE));

SQL>create table student(rollno number(5),name varchar(12),marks number(3));

The above command at the SQL prompt creates a student table with three columns. rollno column is of type number that can have a maximum of 5 digits. name column is of type varchar that can store a string of length 12. marks column is of type number that can store a number with a maximum of 3 digits.

Q) Create a table EMPLOYEE with the following columns. EMPNO, NAME and SALARY.

A) We have to give the following command at the SQL prompt to create the table.

create table employee(empno number(5),name varchar(12),salary number(7,2));

Note:- In the above command, for the salary column number(7,2) means, it can store a maximum of 7 digit decimal number. After decimal point, 2 digits can occur.

Inserting data into the database table

In order to insert records into the table, we use the following SQL syntax.

INSERT INTO TABLENAME VALUES(value1,value2,value3);

Note: - For inserting strings into the table we enclose them with single quotes.

Q) Write SQL command to store employee information into EMPLOYEE TABLE.

A) INSERT INTO EMPLOYEE VALUES(1001,’Rama’,5000);

|EMPNO |NAME |SALARY |

|1001 |Rama |5000 |

Note: - If we submit the above command at the SQL prompt, one employee record is stored into employee table. But it will not become permanent. To make the storage permanent we have to say

SQL>commit;

Deleting data from the table

In order to delete records from the table we use the DELETE command.

Syntax: - SQL> DELETE FROM TABLENAME;

If we use the above syntax, all the records will be deleted from the table. In order to delete specified record(s), we have to specify the criteria using WHERE clause.

Q) Write an SQL command to delete all the records from the employee table.

A) DELETE FROM EMPLOYEE;

Note: - With the above command all the records are deleted temporarily. To make the deletion permanent, we have to say SQL>commit;

Q) Write an SQL command to delete all the records from the employee table whose salary is greater than 5000.

A) SQL>DELETE FROM EMPLOYEE WHERE SALARY>5000;

Updating table data

We use UPDATE command to modify column values of the records of the table.

Syntax: - UPDATE TABLENAME SET COLUMNAME=NEW VALUE;

With the above syntax, all the records in the table will be modified. Specified column of each record is set to new value. If we want to modify the column values of specified record(s), we have to use WHERE CLAUSE.

Q) Write an SQL command that increases salaries of all employees by Rs. 2000.

A) SQL>UPDATE EMPLOYEE SET SALARY=SALARY+2000;

Q) Write an SQL command that increases the salary of each employee by Rs. 1000 whose salary is less than Rs. 5000.

A) SQL> UPDATE EMPLOYEE SET SALARY=SALARY+1000 WHERE SALARY SELECT NAME, SALARY FROM EMPLOYEE;

Q) Write an SQL command to display all the details of each employee.

A) SQL> SELECT * FROM EMPLOYEE;

Q) Write an SQL command that displays employee number and salary of all the employees whose salary is more than 5000.

A) SQL>SELECT EMPNO, SALARY FROM EMPLOYEE WHERE SALARY>5000;

Note: - Database programmers perform so many operations on the table data than what we have discussed here. Application developers, i.e. java programmers, .NET professionals etc. mostly perform DML and DQL operations only. It is strongly recommended for application developers to master SQL and PL/SQL programming skills.

Database programming using JDBC

Even though Java has many features, it became famous and widely accepted in the industry because of its platform independency. The acceptance does not become hundred percent complete unless business community endorses java. Every enterprise (business) application is required to access the database either for retrieving the data to be processed or for storing the processed data. Java offers a clean and simple approach for database connectivity through JDBC using which a java application can connect virtually to any database. Therefore enterprises endorsed java and rest is the history.

What is JDBC?

JDBC is a trademark name from Sun. It is not an acronym for Java Database Connectivity. JDBC is an API. Using this API we can develop java applications that can communicate with databases. JDBC API support is made available to Java applications through 2 packages.

1. java.sql

2. javax.sql

java.sql package offers a set of fundamental interfaces and classes used for java-database communication. Extension package is used for advanced Java-database operations.

JDBC ARCHITECTURE

Java Application: - It can be a stand-alone java program, a servlet, a jsp, a Java bean or an EJB, which uses the JDBC API to get connected and perform operations on the database data.

JDBC API: - It is a set of classes and interfaces used in a java program for database operations. java.sql & javax.sql packages provide the necessary library support.

DriverManager: - Java program uses DriverManager to get the connection with the database.

Driver: - It is the software that establishes connection with the database. It is the translation software that translates the JDBC method calls. This software enables the communication between java program and the database.

Database: - Enterprise data is stored in the database.

Standard steps to connect to the database from a Java program

1. Loading the driver

2. Preparing the connection string

3. Requesting for the connection

4. After performing database operations closing the connection.

Loading the Driver: - Important entity in the Java-Database communication is the driver. JDBC driver is a class. We have to load this Java class programmatically into memory. We have so many kinds of drivers. Initially we make use of Sun Microsystems developed driver. Fully qualified name of that driver class is “sun.jdbc.odbc.JdbcOdbcDriver”. To load the class dynamically, we use the following static method. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Preparing the connection string: - We need to supply the database URL for connectivity purpose. This URL is known as connection string. When we are using Sun driver, connection string will be in the following format.

String cs=”jdbc:odbc:dsn”;

Data Source Name (DSN) we have to configure externally. After configuring, we give some name to the configuration. That name we use in the connection string.

Requesting for connection: - Java program calls the following method on the DriverManager to get the database connection.

Connection con=DriverManager.getConnection(cs,”username”,”password”);

DriverManager initializes the connection process on the driver. Driver creates the connection and gives to the DriverManager. In turn, it gives to the Java program.

Closing the connection: - After performing database operations (Insertion, deletion, updation or selection) we close the database connection.

Task: - stand-alone Java application that connects to the oracle database.

/*

Source code: -ConnectToOracle.java

*/

import java.sql.*;

class ConnectToOracle

{

public static void main(String args[]) throws Exception

{

String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;

String cs=”jdbc:odbc:student”;

String user=”scott”;

String pwd=”tiger”;

Class.forName(driver);

System.out.println(“Driver loaded”);

Connection con=DriverManager.getConnection(cs,user,pwd);

System.out.println(“Connected to the oracle database”);

con.close();

}//main()

}//class

If the above program is successfully executed, we get the following output.

Driver loaded

Connected to the oracle database

Observations to be made

1. In the connection string, “student” is the DSN. We need to configure it using Data Source Administrator.

2. The user name “scott” and password “tiger” is the default user name and password to login to oracle database. The database administrators change them. Accordingly we have to change them in our program.

3. forName method throws ClassNotFoundException. getConnection and close methods throw SQLException. Either we have to handle them explicitly by writing try catch OR pass on them by using throws class for the main. Otherwise, compiler generates syntactical errors and forces us to deal with exceptions.

Performing Database operations

java.sql.Statement is used to perform database operations from the Java program. Its object is produced as follows.

Statement st=con.createStatement();

“st” is the JDBC object used to submit SQL statements from a Java program. It has 2 important methods.

1. executeUpdate()

2. executeQuery()

The first method is used to submit DML statements to the database. I.e. when we want to insert a record, delete records or update records of a database table from a Java program we have to use executeUpdate method of the Statement object. The second method is used to submit SELECT statement to the database to retrieve data from the table. Both methods throw SQLException.

Task: - Java application inserting a record into the database table.

// Source code: - InsertRecord.java

import java.sql.*;

class InsertRecord

{

public static void main(String args[]) throws Exception

{

String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;

String cs=”jdbc:odbc:student”;

String user=”scott”;

String pwd=”tiger”;

String sqlstmt=”INSERT INTO EMPLOYEE VALUES(1001,’Rama’,6000)”;

Class.forName(driver);

Connection con=DriverManager.getConnection(cs,user,pwd);

Statement st=con.createStatement();

int updatecount=st.executeUpdate(sqlstmt);

System.out.println(updatecount+ “ row created”);

st.close();

con.close();

}//main()

}//class

Observations to be made

1. executeUpdate method returns an integer that indicates the number of rows effected in the database. In this program “updatecount” is holding that value.

2. Before we close the connection, we need to close the Statement object.

Task: - Java application deleting multiple records from the database table.

/*

Source code: - DeleteRecords.java

*/

import java.sql.*;

class DeleteRecords

{

public static void main(String args[]) throws Exception

{

String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;

String cs=”jdbc:odbc:student”;

String user=”scott”;

String pwd=”tiger”;

String sqlstmt=”DELETE FROM EMPLOYEE WHERE SALARY>5000”;

Class.forName(driver);

System.out.println(“driver loaded”);

Connection con=DriverManager.getConnection(cs,user,pwd);

System.out.println(“Java program connected to the oracle database”);

Statement st=con.createStatement();

int updatecount=st.executeUpdate(sqlstmt);

System.out.println(updatecount+ “ rows deleted”);

st.close();

con.close();

}//main()

}//class

If the above program is successfully executed, we get the following output.

driver loaded

Java program connected to the oracle database

n rows deleted

Note: - ‘n’ depends upon the number of matching records present in the database table.

Retrieving data from database table

When we submit SELECT statement to the database, it returns a table of records. Using JDBC API we retrieve each column of each row in the returned records in object oriented manner. We submit the SELECT statement to the database as follows. ResultSet rs=st.executeQuery(“SELECT …..”);

java.sql.ResultSet object holds the table of records returned from the database. When the ResultSet is opened, the cursor points to the zero record. Using the ResultSet methods we have to move the cursor to each record and get the column values. Most important method from ResultSet that is used to move the cursor is next method. ResultSet gives getter methods to retrieve the column values.

Task: - Java application that retrieves a single record from the table.

//Source code: - RetrieveRecord.java

import java.sql.*;

class RetrieveRecord

{

public static void main(String args[]) throws Exception

{

String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;

String cs=”jdbc:odbc:student”; String user=”scott”; String pwd=”tiger”;

String sqlstmt=”SELECT * FROM EMPLOYEE WHERE EMPNO=1001”;

Class.forName(driver);

Connection con=DriverManager.getConnection(cs,user,pwd);

Statement st=con.createStatement();

ResultSet rs=st.executeQuery(sqlstmt);

if(rs.next())

{

System.out.println(“Employee Name:”+rs.getString(2));

System.out.println(“Employee Salary:”+rs.getFloat(3));

}

else

System.out.println(“Employee does not exist”);

rs.close(); st.close();

con.close();

}//main()

}//class

If the above program successfully executes, name and salary of the employee whose empno is 1001 will be displayed. With that number if no employee exists the same is displayed.

Observations to be made

1. next() method moved the cursor from zero record to the first record. It returns true if the record exists. Otherwise, it returns false.

2. The kind of get method we have to call depends upon the column type. If the column type is VARCHAR, we called getString method. To this method we supplied the column number as argument. It returns the column value. If the column type is decimal number, we called getFloat method on the ResultSet object.

3. getter methods of ResultSet act upon the columns of that row to which the cursor is currently pointing.

4. We have to close the ResultSet befoe Statement.

Task: - Java application that retrieves multiple records from the table.

//Source code: - RetrieveRecords.java

import java.sql.*;

class RetrieveRecords

{

public static void main(String args[]) throws Exception

{

String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;

String cs=”jdbc:odbc:student”; String user=”scott”; String pwd=”tiger”;

String sqlstmt=”SELECT * FROM EMPLOYEE”; Class.forName(driver);

Connection con=DriverManager.getConnection(cs,user,pwd);

Statement st=con.createStatement();

ResultSet rs=st.executeQuery(sqlstmt);

while(rs.next())

System.out.println(rs.getInt(1)+” “+rs.getString(2)+” “+rs.getFloat(3));

rs.close(); st.close(); con.close();

}//main()

}//class

The above program displays all the records of the employee table. In each iteration of while loop cursor points to the next record and getter methods get column values.

java.sql.PreparedStatement

If we are executing similar SQL statements multiple times, using parameterized (or “prepared”) statements can be more efficient than executing a raw query each time. The idea is to create a parameterized statement in a standard from that is sent to the database for compilation before actually being used. We use a question mark to indicate the places where a value will be substituted into the statement. Each time we use the prepared statement; we simply replace the marked parameters, using a setxxx method call corresponding to the entry we want to set (using 1 based indexing) and the type of parameter. To execute the prepared statement object we call executeUpadate().

The PreparedStatement interface inherits from Statement interface and differs from it in two ways.

1. Instances of PrepaedStatement contain an SQL statement that has already been compiled. This is what makes a Statement “prepared”.

2.The SQL statement contained in a PreparedStatement object may have one or more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead the statement has a question mark(?) as a placeholder for each IN parameter. A value for each question mark must be supplied by the appropriate setxxx method before the statement is executed. Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement. Being a subclass of Statement, PreparedStatement inherits all the functionality of Statement. In addition, it adds a set of methods that are needed for setting the values to be sent to the database in the place of placeholders for IN parameters. To create the preparedStatement we call the prepareStatement() method on the connection object.

PreparedStatement ps=con.prepareStatement(“insert into emp values(?,?,?)”);

While creating the Statement object we do not supply any SQL statement for the

createStatement () method. But here we are supplying the SQL statement.

Task: - Example program on PreparedStatement. Perform the database operations repeatedly and graphically.

[pic]

In this application, we make use of Swing-JDBC-Database communication. For each database operation one button is given. For each button one separate listener class is written. In each listener class, event handler is implemented. Loading the driver, creating the connection and building the PreparedStatement object are done in the constructor of the window class.

/*

Source Code: EmployeeForm.java

*/

import java.awt.*; import java.awt.event.*;

import javax.swing.*;

import java.sql.*;

class EmployeeForm extends JFrame

{

JLabel l1,l2,l3,title,info;

JTextField t1,t2,t3;

JButton b1,b2,b3,b4,exit;

Connection con;

PreparedStatement insertps;

PreparedStatement updateps;

PreparedStatement deleteps;

PreparedStatement selectps;

EmployeeForm()

{

setSize(355,300);

setLocation(100,100);

Container c=getContentPane();

title= new JLabel(" EMPLOYEE DETAILS ");

title.setFont(new Font("Dialog",Font.BOLD,15));

l1=new JLabel("EMPNO");

l2=new JLabel("NAME");

l3=new JLabel("SALARY");

t1=new JTextField(10);

t2=new JTextField(10);

t3=new JTextField(10);

b1=new JButton("insrt");

b2=new JButton("delet");

b3=new JButton("updt");

b4=new JButton("show");

exit=new JButton("exit");

c.setLayout(null);

title.setBounds(60,10,160,20);

c.add(title);

l1.setBounds(40,40,50,20);

c.add(l1);

t1.setBounds(95,40,108,20);

c.add(t1);

l2.setBounds(40,70,50,20);

c.add(l2);

t2.setBounds(95,70,108,20);

c.add(t2);

l3.setBounds(40,100,50,20);

c.add(l3);

t3.setBounds(95,100,108,20);

c.add(t3);

b1.setBounds(10,140,65,40);

c.add(b1);

b2.setBounds(77,140,65,40);

c.add(b2);

b3.setBounds(144,140,65,40);

c.add(b3);

b4.setBounds(211,140,65,40);

c.add(b4);

exit.setBounds(278,140,65,40);

c.add(exit);

info=new JLabel("Getting connected to the database");

info.setFont(new Font("Dialog",Font.BOLD,15));

info.setBounds(20,190,330,30);

c.add(info);

b1.addActionListener(new InsertListener());

b2.addActionListener(new DeleteListener());

b3.addActionListener(new UpdateListener());

b4.addActionListener(new ShowListener());

exit.addActionListener(new ExitListener());

setVisible(true);

getConnection();

}//Constructor

void getConnection()

{

try

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

String url="jdbc:odbc:student";

con=DriverManager.getConnection(url,"scott","tiger");

info.setText("Connection is established with the database");

insertps=con.prepareStatement("insert into employee values(?,?,?)");

updateps=con.prepareStatement("update employee set name=?

, salary=? where empno=?");

deleteps=con.prepareStatement("delete from employee where

empno=?");

selectps=con.prepareStatement("select * from employee where empno=?");

}//try

catch(ClassNotFoundException e)

{

System.out.println("Driver class not found....");

System.out.println(e);

}

catch(SQLException e)

{

info.setText("Unable to get connected to the database");

}

}//getConnection()

class InsertListener implements ActionListener

{

public void actionPerformed(ActionEvent e)

{

try

{

int empno=Integer.parseInt(t1.getText());

String name=t2.getText();

float salary=Float.parseFloat(t3.getText());

insertps.setInt(1,empno);

insertps.setString(2,name);

insertps.setFloat(3,salary);

insertps.executeUpdate();

info.setText("One row inserted successfully");

insertps.clearParameters();

t1.setText("");

t2.setText("");

t3.setText("");

}//try

catch(SQLException se)

{

info.setText("Failed to insert a record...");

}

catch(Exception de)

{

info.setText("enter proper data before insertion...");

}

}//actionPerformed()

}//Listener class

class DeleteListener implements ActionListener

{

public void actionPerformed(ActionEvent e)

{

try

{

int empno=Integer.parseInt(t1.getText());

deleteps.setInt(1,empno);

deleteps.executeUpdate();

deleteps.clearParameters();

info.setText("One row deleted successfully");

t1.setText("");

t2.setText("");

t3.setText("");

}//try

catch(SQLException se)

{

info.setText("Failed to delete a record...");

}

catch(Exception de)

{

info.setText("enter proper empno before deletion..");

}

}//event handler

}// listener class

class UpdateListener implements ActionListener

{

public void actionPerformed(ActionEvent e)

{

try

{

int empno=Integer.parseInt(t1.getText());

String name=t2.getText();

float salary=Float.parseFloat(t3.getText());

updateps.setString(1,name);

updateps.setFloat(2,salary);

updateps.setInt(3,empno);

updateps.executeUpdate();

info.setText("One row updated successfully");

updateps.clearParameters();

t1.setText("");

t2.setText("");

t3.setText("");

}//try

catch(SQLException se)

{

System.out.println(se);

info.setText("Failed to update the record...");

}

catch(Exception de)

{

System.out.println(de);

info.setText("enter proper data before selecting updation..");

}

}//event handler

}

class ShowListener implements ActionListener

{

public void actionPerformed(ActionEvent e)

{

try

{

int empno=Integer.parseInt(t1.getText());

selectps.setInt(1,empno);

selectps.execute();

ResultSet rs=selectps.getResultSet();

rs.next();

t2.setText(rs.getString(2));

t3.setText(""+rs.getFloat(3));

info.setText("One row displayed successfully");

selectps.clearParameters();

}//try

catch(SQLException se)

{

info.setText("Failed to show the record...");

}

catch(Exception de)

{

info.setText("enter proper empno before selecting show..");

}

}//event handler

}//listener class

class ExitListener implements ActionListener

{

public void actionPerformed(ActionEvent e)

{

try

{

insertps.close();deleteps.close();

updateps.close();selectps.close();

if(con!=null) con.close();

System.exit(0);

}

catch(SQLException se){System.out.println(se);}

}

}

public static void main(String args[])

{

new EmployeeForm();

}

}//EmployeeForm class

Calling a stored procedure from a Java program

With a CallableStatement, we can execute a stored procedure or function in a database. A stored procedure is stored in a database. The call to the stored procedure is what a CallbleStatement contains.

Calling a stored procedure in a database involves five basic steps.

1. Define the call to the database procedure: - As with prepared statement, you use special syntax to define a call to a stored procedure. The procedure call uses escape syntax, where the appropriate ? defines input and output parameters.

2. Prepare a CallableStatement for the procedure: We obtain a CallableStatement from a connection by calling prepareCall().

CallableStatement st=con.prepareCall(String escapesyntax);

3. Register the output parameter types: - Before executing the procedure, you must declare the type of each output parameter. registerOutParameter (int parameterIndex, int jdbc Type) registers the OUT parameter in ordinal position parameterIndex to the JDBC type jdbcType. All the OUT parameters must be registered before a stored procedure is executed. The JDBC type specified by jdbcType for an OUT parameter determines the java Type that must be used in the method getxxx() to read the value of that parameter.

4. provide values for the input parameters:- Before executing the procedure, you must supply the input parameter values. Passing in any IN parameter values to a CallableStatement object is done using the setxxx methods inherited form PreparedStatement. The type of the value being passed in determines which setxxx method to use. The setxxx() method provides a java value that the driver converts to a JDBC value before sending it to the database. For example, setFloat () is used to pass in a float value, setBoolean to pass in a Boolean value.

5. execute the stored procedure:- To execute the database stored procedure, call execute() on the CallbleStatement.

Example Stored Procedure1 developed by the PL/SQL programmer

create or replace procedure proc2(n number, nm out varchar)

as

begin

select name into nm from emp10 where id=n;

end proc2;

The above stored procedure name is proc2. It will take employee number as in parameter and gives employee name into the out parameter.

Task: - Example program to call the stored procedure

//Source code: - StoredProcedure.java

import java.sql.*;

class StoredProcedure

{

public static void main(String args[]) throws Exception

{

String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;

String cs=”jdbc:odbc:student”;

String user=”scott”;

String pwd=”tiger”;

Class.forName(driver);

Connection con=DriverManager.getConnection(cs,user,pwd);

CallableStatement cst=con.prepareCall("{call proc2(?,?)}");

cst.registerOutParameter(2,Types.VARCHAR);

cst.setInt(1,1001);

cst.execute();

String str=cst.getString(2);

System.out.println("Employee Name:"+str);

cst.close();

con.close();

}//main

}//class

If the program is successfully executed, the following output is displayed.

Employee Name:Rama

Observations to be made

1. The second parameter is of type VARCHAR. It is an out parameter. Therefore, we registered it by supplying javax.sql.Types class constant VARCHAR.

2. To retrieve the out parameter value, we called the getString method on the CallableStatement object. The kind of method we call depends upon the Types class constant.

3. To release the JDBC resources, we closed the CallableStatement object.

Example Stored Procedure2 developed by the PL/SQL programmer

create or replace procedure addintrest(id in number,bal out number)as

begin

select balance into bal from account where id=id;

bal :=bal+bal*.05;

update account set balance=bal where id=id;

end;

The above stored procedure name is addintrest. It will take account number as in parameter, calculates the interest and adds it to the balance and stores the resultant balance into the out parameter.

Task: - Example program to call the stored procedure

//Source code: - StoredProcedure.java

import java.sql.*;

class StoredProcedure

{

public static void main(String args[]) throws Exception

{

String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;

String cs=”jdbc:odbc:student”;

String user=”scott”;

String pwd=”tiger”;

Class.forName(driver);

Connection con=DriverManager.getConnection(cs,user,pwd);

CallableStatement cst=con.prepareCall(" { call addintrest(?,?)}");

cst.registerOutParameter(2,Types.FLOAT);

cst.setInt(1,accno);

cst.execute();

System.out.println("New Balance of A/c "+ accno+ " is : Rs "+cst.getFloat(2));

cst.close();

con.close();

}//main

}/class

Batch Updates

A batch update is a set of multiple update statements that is submitted to the database for processing as a batch. Sending multiple update statements to the database together as a unit can, in some situations, be much more efficient than sending each update statement separately. This ability to send updates as a unit, referred to as the batch update facility, is one of the features provided with the JDBC 2.0 API.

The addBatch() method of Statement and PreparedStatement is used to add individual statements to the batch. The executeBatch() method is used to start the execution of all the statements grouped together. This method returns an array of integers, and each element of the array represents the update count for the respective update statement.

int updateCounts=stmts.executeBatch();

We get a BatchUpdateException when you call the method executeBatch if

1. one of the SQL statements you added to the batch produces a ResultSet (usually a query) or

2. one of the SQL statements in the batch does not execute successfully for some other reason.

BatchUpdateException is derived from SQLException. This means that you can use all of the methods available to an SQLException object with it.

A BatchUpdateException contains an array of update counts that is similar to the array returned by the method executeBatch. In both cases, the update counts are in the same order as the commands that produced them. This tells you how many commands in the batch executed successfully and which ones they are. For example, if five commands executed successfully, the array will contain five numbers: the first one being the update count for the first command, the second one being the update count for the second command, and so on. You should not add a query (a SLECT statement) to a batch of SQL commands because the method executeBatch, which returns an array of update counts, expects an update count from each SQL command that executes successfully. This means that only commands that return an update count (commands such as INSERT INTO, UPDATE, DELETE) or that return 0 (such as CREATE TABLE, DFROP TABLE, ALTER TABLE) can be successfully executed as a batch with the executeBatch method. clearBatch () method of the statement object explicitly clears the statements in the batch.

Task: - Example program to implement batch updates

//Source code: - BatchUpdates.java

import java.sql.*;

class BatchUpdates

{

public static void main(String args[]) throws Exception

{

String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;

String cs=”jdbc:odbc:student”;

String user=”scott”;

String pwd=”tiger”;

Class.forName(driver);

Connection con=DriverManager.getConnection(cs,user,pwd);

Statement st=con.createStatement();

st.addBatch("insert into student values(3,'David')");

st.addBatch("delete from student where sno=1");

st.addBatch("update student set sname='raju' where sno=2");

int effectedRows[]=st.executeBatch();

int rows=0;

for(int i=0;i ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download