JDBC
JDBC(Java Database Connectivity)
The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database. JDBC stands for Java Database Connectivity. JDBC works with Java on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX.
What is JDBC?
JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases. The JDBC library includes APIs for each of the tasks commonly associated with database usage:
Making a connection to a database
Creating SQL or MySQL statements
Executing that SQL or MySQL queries in the database
Viewing & Modifying the resulting records
Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to an underlying database. Java can be used to write different types of executables, such as:
Java Applications
Java Applets
Java Servlets
Java ServerPages (JSPs)
Enterprise JavaBeans (EJBs)
All of these different executables are able to use a JDBC driver to access a database and take advantage of the stored data. JDBC provides the same capabilities as ODBC, allowing Java programs to contain database-independent code.
JDBC Architecture:
The JDBC API supports both two-tier and three-tier processing models for database access but in general JDBC Architecture consists of two layers:
JDBC API: This provides the application-to-JDBC Manager connection.
JDBC Driver API: This supports the JDBC Manager-to-Driver Connection.
The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases. The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases.
Following is the architectural diagram, which shows the location of the driver manager with respect to the JDBC drivers and the Java application:
Common JDBC Components:
The JDBC API provides the following interfaces and classes:
DriverManager: This interface manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication subprotocol. The first driver that recognizes a certain subprotocol under JDBC will be used to establish a database Connection.
Driver: This interface handles the communications with the database server. You will interact directly with Driver objects very rarely. Instead, you use DriverManager objects, which manages objects of this type. It also abstracts the details associated with working with Driver objects
Connection : Interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with database is through connection object only.
Statement : You use objects created from this interface to submit the SQL statements to the database. Some derived interfaces accept parameters in addition to executing stored procedures.
ResultSet: These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data.
SQLException: This class handles any errors that occur in a database application.
What is JDBC Driver?
JDBC drivers implement the defined interfaces in the JDBC API for interacting with your database server. For example, using JDBC drivers enable you to open database connections and to interact with it by sending SQL or database commands then receiving results with Java. The java.sql package that ships with JDK contains various classes with their behaviours defined and their actual implementaions are done in third-party drivers. Third party vendors implements the java.sql.Driver interface in their database driver.
JDBC Drivers Types:
JDBC driver implementations vary because of the wide variety of operating systems and hardware platforms in which Java operates. Sun has divided the implementation types into four categories known as:
JDBC-ODBC bridge plus ODBC driver, also called Type 1.
Native-API, partly Java driver, also called Type 2.
JDBC-Net, pure Java driver, also called Type 3.
Native-protocol, pure Java driver, also called Type 4.
Type 1 Driver- the JDBC-ODBC bridge
In a Type 1 driver, a JDBC bridge is used to access ODBC drivers installed on each client machine. Using ODBC requires configuring on your system a Data Source Name (DSN) that represents the target database. When Java first came out, this was a useful driver because most databases only supported ODBC access but now this type of driver is recommended only for experimental use or when no other alternative is available.
The JDBC type 1 driver, also known as the JDBC-ODBC bridge is a database driver implementation that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls. The bridge is usually used when there is no pure-Java driver available for a particular database.
The driver is implemented in the sun.jdbc.odbc.JdbcOdbcDriver class and comes with the Java 2 SDK, Standard Edition. The driver is platform-dependent as it makes use of ODBC which in turn depends on native libraries of the operating system.
Type 1 is the simplest of all but platform specific i.e only to Microsoft platform.
Type 1 drivers are "bridge" drivers. They use another technology such as Open Database Connectivity (ODBC) to communicate with a database. This is an advantage because ODBC drivers exist for many Relational Database Management System (RDBMS) platforms.
Functions:
Translates query obtained by JDBC into corresponding ODBC query, which is then handled by the ODBC driver.
Sun provides a JDBC-ODBC Bridge driver. sun.jdbc.odbc.JdbcOdbcDriver. This driver is native code and not Java, and is closed
source.Client -> JDBC Driver -> ODBC Driver -> Database
There is some overhead associated with the translation work to go from JDBC to ODBC.
Advantages:
Almost any database for which ODBC driver is installed, can be accessed.
Disadvantages:
Performance overhead since the calls have to go through the JDBC overhead bridge to the ODBC driver, then to the native database connectivity interface.
The ODBC driver needs to be installed on the client machine.
Considering the client-side software needed, this might not be suitable for applets.
Type 2 Driver - the Native-API Driver
The JDBC type 2 driver, also known as the Native-API driver is a database driver implementation that uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API.
A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine. It does not have the overhead of the additional ODBC function calls.
Functions:
This type of driver converts JDBC calls into calls to the client API for that database.
Client -> JDBC Driver -> Vendor Client DB Library -> Database
Advantage
Better performance than Type 1 since no jdbc to odbc translation is needed.
Disadvantages
The vendor client library needs to be installed on the client machine.
Cannot be used in internet due the client side software needed.
Not all databases give the client side library.
Type 3 driver - the Network-Protocol Driver
The JDBC type 3 driver, also known as the network-protocol driver is a database driver implementation which makes use of a middle-tier between the calling program and the database. The middle-tier (application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol.
This differs from the type 4 driver in that the protocol conversion logic resides not at the client, but in the middle-tier. However, like type 4 drivers, the type 3 driver is written entirely in Java. These drivers use a networking protocol and middleware to communicate with a server. The server then translates the protocol to DBMS function calls specific to DBMS.
Functions:
Follows a three tier communication approach.
Can interface to multiple databases - Not vendor specific.
The JDBC Client driver written in java, communicates with a middleware-net-server using a database independent protocol, and then this net server translates this request into database commands for that database.
Thus the client driver to middleware communication is database independent.
Client -> JDBC Driver -> Middleware-Net Server -> Any Database
Advantages
Since the communication between client and the middleware server is database independent, there is no need for the vendor db library on the client machine. Also the client to middleware need'nt be changed for a new database.
The Middleware Server (Can be a full fledged J2EE Application server) can provide typical middleware services like caching (connections, query results, and so on), load balancing, logging, auditing etc..
eg. for the above include jdbc driver features in Weblogic.
Can be used in internet since there is no client side software needed.
At client side a single driver can handle any database.(It works provided the middlware supports that database!!)
Disadvantages
Requires database-specific coding to be done in the middle tier.
An extra layer added may result in a time-bottleneck. But typically this is overcome by providing efficient middleware
services described above.
Type 4 - the Native-Protocol Driver
The JDBC type 4 driver, also known as the native-protocol driver is a database driver implementation that converts JDBC calls directly into the vendor-specific database protocol.
The type 4 driver is written completely in Java and is hence platform independent. It is installed inside the Java Virtual Machine of the client. It provides better performance over the type 1 and 2 drivers as it does not have the overhead of conversion of calls into ODBC or database API calls. Unlike the type 1 and 2 drivers, it does not need associated software to work.
A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.
The IBM Toolbox for Java JDBC driver is a Type 4 JDBC driver, indicating that the API is a pure Java networking protocol driver.
Functions
Type 4 drivers are entirely written in Java that communicate directly with a vendor's database through socket connections. No translation or middleware layers, are required, improving performance.
The driver converts JDBC calls into the vendor-specific database protocol so that client applications can communicate directly with the database server.
Completely implemented in Java to achieve platform independence.
e.g include the widely used Oracle thin driver - oracle.jdbc.driver. OracleDriver which connect to jdbc:oracle:thin URL format.
Client Machine -> Native protocol JDBC Driver -> Database server
Advantages
These drivers don't translate the requests into db request to ODBC or pass it to client api for the db, nor do they need a middleware layer for request indirection. Thus the performance is considerably improved.
Disadvantage
At client side, a separate driver is needed for each database.
Which Driver should be used?
If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4.
If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver.
Type 2 drivers are useful in situations where a type 3 or type 4 driver is not available yet for your database.
The type 1 driver is not considered a deployment-level driver and is typically used for development and testing purposes only.
Following table lists down popular JDBC driver names and database URL.
Creating JDBC Application:
There are following six steps involved in building a JDBC application:
Import the packages . Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
Register the JDBC driver . Requires that you initialize a driver so you can open a communications channel with the database.
Open a connection . Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.
Execute a query . Requires using an object of type Statement for building and submitting an SQL statement to the database.
Extract data from result set . Requires that you use the appropriateResultSet.getXXX() method to retrieve the data from the result set.
Clean up the environment . Requires explicitly closing all database resources versus relying on the JVM's garbage collection.
Sample Code:
import java.sql.*;
class ExecuteCreate
{
public static void main(String[] args)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:sydsn");
System.out.println("Connection Created");
Statement st = con.createStatement();
System.out.println("Statement Created");
String query="create table account(accno int,balance float,acctype varchar)";
boolean result = st.execute(query);
if(result!=true)
System.out.println("Table created");
else
System.out.println("Table not created");
st.close();
con.close();
}
catch(Exception ex)
{ System.out.println(ex);}
}
}
DriverManager class:
The DriverManager class acts as an interface between user and drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver().
Useful methods of DriverManager class
Connection interface
A Connection is the session between java application and database. The Connection interface is a factory of Statement, PreparedStatement, and DatabaseMetaData i.e. object of Connection can be used to get the object of Statement and DatabaseMetaData. The Connection interface provide many methods for transaction management like commit(), rollback() etc.
Commonly used methods of Connection interface:
Note: By default, connection commits the changes after executing queries.
Connecting to the MySql database in java
Step 1:
Two ways to load the jar file:
paste the mysqlconnector.jar file in jre/lib/ext folder
set classpath
1) paste the mysqlconnector.jar file in JRE/lib/ext folder:
2) set classpath:
Go to environment variable then click on new tab. In variable name write classpath and in variable value paste the path to the mysqlconnector.jar file by appending mysqlconnector.jar;.; as C:\folder\mysql-connector.jar;.;
Step 2:
Create a table in the mysql database, but before creating table, we need to create database first.
Open MySql prompt and type following:
create database sydb;
use sydb;
create table student(rollno int(5),name varchar(40),marks int(3));
Program which connects to MySql database with database name=sydb id=root and password=msc2:
import java.sql.*;
class MysqlCon
{
public static void main(String args[])
{
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sydb","root","msc2");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from student");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
Note: If you are doing in Netbeans then download mysql-connector.jar and add to to project library.
Once a connection is obtained we can interact with the database. The JDBC Statement, CallableStatement, and PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database.
They also define methods that help bridge data type differences between Java and SQL data types used in a database.
Following table provides a summary of each interface's purpose to understand how do you decide which interface to use?
The Statement Objects:
Creating Statement Object:
Before you can use a Statement object to execute a SQL statement, you need to create one using the Connection object's createStatement( ) method, as in the following example:
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
Once you've created a Statement object, you can then use it to execute a SQL statement with one of its three execute methods.
boolean execute(String SQL) : Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use truly dynamic SQL.
int executeUpdate(String SQL) : Returns the numbers of rows affected by the execution of the SQL statement. Use this method to execute SQL statements for which you expect to get a number of rows affected - for example, an INSERT, UPDATE, or DELETE statement.
ResultSet executeQuery(String SQL) : Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.
The PreparedStatement Objects:
The PreparedStatement interface extends the Statement interface which gives you added functionality with a couple of advantages over a generic Statement object.
This statement gives you the flexibility of supplying arguments dynamically.
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
All parameters in JDBC are represented by the ? symbol, which is known as the parameter marker. You must supply values for every parameter before executing the SQL statement.
The setXXX() methods bind values to the parameters, where XXX represents the Java data type of the value you wish to bind to the input parameter. If you forget to supply the values, you will receive an SQLException.
Each parameter marker is referred to by its ordinal position. The first marker represents position 1, the next position 2, and so forth. This method differs from that of Java array indices, which start at 0.
All of the Statement object's methods for interacting with the database (a) execute(), (b) executeQuery(), and (c) executeUpdate() also work with the PreparedStatement object. However, the methods are modified to use SQL statements that can take input the parameters.
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
class FriendDatabase extends JFrame implements ActionListener
{
JLabel lblNo,lblBal,lblLoc;
JTextField txtBal,txtNo,txtType;
JButton cmdAdd,cmdExitl;
Container cn=getContentPane();
public FriendDatabase()
{
lblNo=new JLabel("account No.");
lblBal=new JLabel("account BALANCE");
lblLoc=new JLabel("account type");
txtBal=new JTextField(10);
txtNo=new JTextField(10);
txtType=new JTextField(10);
cmdAdd=new JButton("Add");
cmdAdd.addActionListener(this);
cmdExit=new JButton("Exit");
cmdExit.addActionListener(this);
cn.setLayout(new GridLayout(4,2));
cn.add(lblNo);
cn.add(txtNo);
cn.add(lblBal);
cn.add(txtBal);
cn.add(lblLoc);
cn.add(txtType);
cn.add(cmdAdd);
cn.add(cmdExit);
setTitle("Frndz DB");
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setSize(300,300);
setVisible(true);
}
public void actionPerformed(ActionEvent ae)
{
Object obj=ae.getSource();
if(obj==cmdAdd)
{ Connection con;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:ty");
String query="insert into account values(?,?,?)";
PreparedStatement ps;
ps=con.prepareStatement(query);
ps.setInt(1,Integer.parseInt(txtNo.getText()));
ps.setString(2,txtBal.getText());
ps.setString(3,txtType.getText());
ps.executeUpdate();
JOptionPane.showMessageDialog(this,"Record Entered","FriendsDatabase",JOptionPane.ERROR_MESSAGE);
txtBal.setText("");
txtNo.setText("");
txtType.setText("");
ps.close();
con.close();
}
catch(Exception e)
{
System.out.println("Error:"+e.getMessage());
}
}
if(obj==cmdExit)
{
System.exit(0);
}
}
public static void main(String args[])throws SQLException
{
FriendDatabase b=new FriendDatabase();
}
}
The CallableStatement Objects:
Just as a Connection object creates the Statement and PreparedStatement objects, it also creates the CallableStatement object which would be used to execute a call to a database stored procedure.
The following code snippet shows how to employ the Connection.prepareCall() method to instantiate a CallableStatement object based on the preceding stored procedure:
The String variable SQL represents the stored procedure, with parameter placeholders.
Using CallableStatement objects is much like using PreparedStatement objects. You must bind values to all parameters before executing the statement, or you will receive an SQLException. If you have IN parameters, just follow the same rules and techniques that apply to a PreparedStatement object; use the setXXX() method that corresponds to the Java data type you are binding. When you use OUT and INOUT parameters you must employ an additional CallableStatement method, registerOutParameter(). The registerOutParameter() method binds the JDBC data type to the data type the stored procedure is expected to return.
Once you call your stored procedure, you retrieve the value from the OUT parameter with the appropriate getXXX() method. This method casts the retrieved value of SQL type to a Java data type.
import java.sql.*;
class CallableJdbcEx
{
public static void main(String[] args)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:sydsn","sa","");
Statement st=con.createStatement();
String query="create procedure proc2 as select * from account";
st.executeUpdate(query);
CallableStatement cs = con.prepareCall("{call proc2}");
ResultSet rs=cs.executeQuery();
while(rs.next())
{
System.out.println("No:"+rs.getString(1)+"\nBal:"+rs.getString(2));
}
con.close();
}
catch(Exception e)
{
System.out.println(e);
e.printStackTrace();
}
}
}
JDBC - Result Sets
he SQL statements that read data from a database query return the data in a result set. The SELECT statement is the standard way to select rows from a database and view them in a result set. The java.sql.ResultSet interface represents the result set of a database query.
A ResultSet object maintains a cursor that points to the current row in the result set. The term "result set" refers to the row and column data contained in a ResultSet object.
The methods of the ResultSet interface can be broken down into three categories:
Navigational methods: used to move the cursor around.
Get methods: used to view the data in the columns of the current row being pointed to by the cursor.
Update methods: used to update the data in the columns of the current row. The updates can then be updated in the underlying database as well.
The cursor is movable based on the properties of the ResultSet. These properties are designated when the corresponding Statement that generated the ResultSet is created.
JDBC provides following connection methods to create statements with desired ResultSet:
createStatement(int RSType, int RSConcurrency);
prepareStatement(String SQL, int RSType, int RSConcurrency);
prepareCall(String sql, int RSType, int RSConcurrency);
The first argument indicate the type of a ResultSet object and the second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable.
Type of ResultSet:
The possible RSType are given below, If you do not specify any ResultSet type, you will automatically get one that is TYPE_FORWARD_ONLY.
Concurrency of ResultSet:
The possible RSConcurrency are given below, If you do not specify any Concurrency type, you will automatically get one that is CONCUR_READ_ONLY.
Our all the examples written so far can be written as follows which initializes a Statement object to create a forward-only, read only ResultSet object:
Navigating a Result Set:
There are several methods in the ResultSet interface that involve moving the cursor, including:
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class TabbedAndDatabaseDemo extends JFrame
{
JTabbedPane tabpane;
TabbedAndDatabaseDemo()
{
tabpane=new JTabbedPane();
getContentPane().add(tabpane);
tabpane.addTab("Entry of Data",new EntryData());
pack();
setTitle(" DB");
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setSize(300,300);
setVisible(true);
}
public static void main(String[] a)
{
new TabbedAndDatabaseDemo();
}
}
class EntryData extends JPanel implements ActionListener
{
JLabel acc_no,acc_bal,acc_type;
JTextField textno, textbal,texttpe;
JTextArea textarea;
JButton search,button,exit,first,next,last,prev;
ResultSet rs;
Statement st;
Connection cn ;
EntryData()
{
setLayout(new GridLayout(6,1));
acc_no=new JLabel("No.");
acc_bal=new JLabel("Balance");
acc_type=new JLabel("Type");
textno=new JTextField();
textbal=new JTextField();
texttpe=new JTextField();
search = new JButton("search");
button = new JButton("submit");
exit = new JButton("exit");
first= new JButton("First");
next= new JButton("next");
last= new JButton("Last");
prev= new JButton("Prev");
button.addActionListener(this);
search.addActionListener(this);
exit.addActionListener(this);
first.addActionListener(this);
next.addActionListener(this);
last.addActionListener(this);
prev.addActionListener(this);
add(acc_no);
add(textno);
add(acc_bal);
add(textbal);
add(acc_type);
add(texttpe);
add(button);
add(exit);
add(first);
add(next);
add(last);
add(prev);
add(search);
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
cn = DriverManager.getConnection("jdbc:odbc:sydsn");
st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = st.executeQuery("SELECT * from account");
}
catch(Exception ex){System.out.println(ex);}
}
public void actionPerformed(ActionEvent ae)
{
String sql="SELECT * from account";
try
{
if(ae.getSource()==button)
{
st = cn.createStatement();
int m = st.executeUpdate("insert into account values("+Integer.parseInt(textno.getText())+","+textbal.getText()+",'"+texttpe.getText()+"');");
JOptionPane.showMessageDialog(this,"Record Entered","FriendsDatabase",JOptionPane.ERROR_MESSAGE);
textno.setText("");
textbal.setText("");
texttpe.setText("");
}
else if(ae.getSource()==search)
{
String ser=JOptionPane.showInputDialog(this,"Enter search account","Search",JOptionPane.INFORMATION_MESSAGE);
sql="SELECT * from account where accno="+ser;
st = cn.createStatement();
rs = st.executeQuery(sql);
if(rs.next())
{
JOptionPane.showMessageDialog(this,"Found","FriendsDatabase",JOptionPane.ERROR_MESSAGE);
prints(rs);
}
else
JOptionPane.showMessageDialog(this,"not Found","FriendsDatabase",JOptionPane.ERROR_MESSAGE);
}
else if(ae.getSource()==exit)
System.exit(0);
else if(ae.getSource()==first)
{
rs.first();
prints(rs);
}
else if(ae.getSource()==next)
{
rs.next();
}
else if(ae.getSource()==last)
{
rs.last();
prints(rs);
}
else if(ae.getSource()==prev)
{
rs.previous();
prints(rs);
}
}catch(Exception ex){System.out.println(ex);}
}
public void prints(ResultSet rs)
{
textno.setText(rs.getString("accno"));
textbal.setText(rs.getString("balance"));
texttpe.setText(rs.getString("acctype"));
}
}
Viewing a Result Set:
The ResultSet interface contains dozens of methods for getting the data of the current row. There is a get method for each of the possible data types, and each get method has two versions:
One that takes in a column name.
One that takes in a column index.
For example, if the column you are interested in viewing contains an int, you need to use one of the getInt() methods of ResultSet:
Similarly there are get methods in the ResultSet interface for each of the eight Java primitive types, as well as common types such as java.lang.String, java.lang.Object, and java.net.URL. There are also methods for getting SQL data types java.sql.Date, java.sql.Time, java.sql.TimeStamp, java.sql.Clob, and java.sql.Blob. Check the documentation for more information about using these SQL data types.
import java.sql.*;
public class NavigateExample
{
public static void main(String[] args)
{
Connection conn;
Statement stmt ;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:sydsn");
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
String sql="SELECT * from account";
ResultSet rs = stmt.executeQuery(sql);
System.out.println("\nMoving cursor to the last...");
rs.last();
int no = rs.getInt("accno");
int bal = rs.getInt("balance");
String type = rs.getString("acctype");
System.out.print("No: " + no);
System.out.print(", Bal: " + bal);
System.out.print(", Type: " + type);
System.out.println("\nMoving cursor to the first row...");
rs.first();
no = rs.getInt("accno");
bal = rs.getInt("balance");
type = rs.getString("acctype");
System.out.print("No: " + no);
System.out.print(", Bal: " + bal);
System.out.print(", Type: " + type);
System.out.println("\nMoving cursor to the next row...");
rs.next();
no = rs.getInt("accno");
bal = rs.getInt("balance");
type = rs.getString("acctype");
System.out.print("No: " + no);
System.out.print(", Bal: " + bal);
System.out.print(", Type: " + type);
rs.close();
stmt.close();
conn.close();
}
catch(SQLException se)
{ se.printStackTrace();}
catch(Exception e)
{ e.printStackTrace();}
}}
Updating a Result Set:
The ResultSet interface contains a collection of update methods for updating the data of a result set. As with the get methods, there are two update methods for each data type:
One that takes in a column name.
One that takes in a column index.
For example, to update a String column of the current row of a result set, you would use one of the following updateString() methods:
There are update methods for the eight primitive data types, as well as String, Object, URL, and the SQL data types in the java.sql package.
Updating a row in the result set changes the columns of the current row in the ResultSet object, but not in the underlying database. To update your changes to the row in the database, you need to invoke one of the following methods.
import java.sql.*;
public class InsertExample
{
public static void main(String[] args)
{
Connection conn;
Statement stmt ;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:sydsn");
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="SELECT * from account";
ResultSet rs = stmt.executeQuery(sql);
printRs(rs);
int newBal;
rs.beforeFirst();
while(rs.next())
{
newBal = rs.getInt("balance") + 50;
rs.updateInt( "balance", newBal);
rs.updateRow();
}
System.out.println("List result set showing new ages...");
printRs(rs);
System.out.println("Inserting a new record...");
rs.moveToInsertRow();
rs.updateInt("accno",104);
rs.updateInt("balance",4000);
rs.updateString("acctype","saving");
//Commit row
rs.insertRow();
System.out.println("List result set showing new set...");
printRs(rs);
// Set position to second record first
rs.absolute( 2 );
rs.deleteRow();
System.out.println("List result set after deleting one records...");
printRs(rs);
rs.close();
stmt.close();
conn.close();
}
catch(SQLException se)
{ se.printStackTrace();}
catch(Exception e)
{ e.printStackTrace();}
}
public static void printRs(ResultSet rs) throws SQLException
{
rs.beforeFirst();
while(rs.next())
{
int no = rs.getInt("accno");
int bal = rs.getInt("balance");
String type = rs.getString("acctype");
System.out.print("No: " + no);
System.out.print(", Bal: " + bal);
System.out.print(", Type: " + type);
System.out.println();
}
}
}
JDBC - Data Types
The JDBC driver converts the Java data type to the appropriate JDBC type before sending it to the database. It uses a default mapping for most data types. For example, a Java int is converted to an SQL INTEGER. Default mappings were created to provide consistency between drivers.
The following table summarizes the default JDBC data type that the Java data type is converted to when you call the setXXX() method of the PreparedStatement or CallableStatement object or the ResultSet.updateXXX() method.
TRANSACTIONS:
There are times when you do not want one statement to take effect unless another one completes. For example, when the proprietor of The Coffee Break updates the amount of coffee sold each week, he will also want to update the total amount sold to date. However, he will not want to update one without updating the other; otherwise, the data will be inconsistent. The way to be sure that either both actions occur or neither action occurs is to use a transaction. A transaction is a set of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements is executed.
Disabling Auto-commit Mode:
When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed.
The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode. This is demonstrated in the following line of code, where con is an active connection:
con.setAutoCommit(false);
Committing a Transaction:
Once auto-commit mode is disabled, no SQL statements are committed until you call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit. The following code, in which con is an active connection, illustrates a transaction:
con.setAutoCommit(false);
PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
updateSales.setInt(1, 50);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
PreparedStatement updateTotal = con.prepareStatement("UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian");
updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);
In this example, auto-commit mode is disabled for the connection con, which means that the two prepared statements updateSales and updateTotal are committed together when the method commit is called. The final line of the previous example enables auto-commit mode, which means that each statement is once again committed automatically when it is completed. Then, you are back to the default state where you do not have to call the method commit yourself. It is advisable to disable auto-commit mode only while you want to be in transaction mode. This way, you avoid holding database locks for multiple statements, which increases the likelihood of conflicts with other users.
Using Transactions to Preserve Data Integrity:
In addition to grouping statements together for execution as a unit, transactions can help to preserve the integrity of the data in a table. For instance, suppose that an employee was supposed to enter new coffee prices in the table COFFEES but delayed doing it for a few days. In the meantime, prices rose, and today the owner is in the process of entering the higher prices. The employee finally gets around to entering the now outdated prices at the same time that the owner is trying to update the table. After inserting the outdated prices, the employee realizes that they are no longer valid and calls the Connection method rollback to undo their effects. (The method rollback aborts a transaction and restores values to what they were before the attempted update.) At the same time, the owner is executing a SELECT statement and printing out the new prices. In this situation, it is possible that the owner will print a price that was later rolled back to its previous value, making the printed
price incorrect.
This kind of situation can be avoided by using transactions, providing some level of protection against conflicts that arise when two users access data at the same time. To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access
by others to the data that is being accessed by the transaction. Once a lock is set, it remains in force until the transaction is committed or rolled back. For example, a DBMS could lock a row of a table until updates to it have been committed. The effect of this
lock would be to prevent a user from getting a dirty read, that is reading a value before it is made permanent. (Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid value.)
How locks are set is determined by what is called a transaction isolation level, which can range from not supporting transactions at all to supporting transactions that enforce very strict access rules. One example of a transaction isolation level is TRANSACTION_READ_COMMITTED, which will not allow a value to be accessed until after it has been committed. In other words, if the transaction isolation level is set to TRANSACTION_READ_COMMITTED, the DBMS does not allow dirty reads to occur. The interface Connection includes five values which represent the transaction isolation levels you can use in JDBC. Normally, you do not need to do anything about the transaction isolation level; you can just use the default one for your DBMS. JDBC allows you to find out what transaction isolation level your DBMS is set to (using the Connection method
getTransactionIsolation) and also allows you to set it to another level (using the Connection method setTransactionIsolation). Keep in mind, however, that even though JDBC allows you to set a transaction isolation level, doing so has no effect unless the driver and DBMS you are using support it.
Example:
import java.sql.*;
import java.io.*;
class TransactionDemo
{
public static void main(String[] args)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:sydsn","sa","");
System.out.println("Connection Created");
con.setAutoCommit(false);
Statement st = con.createStatement();
System.out.println("Statement Created");
String query;int no;
query="insert into account values(108,10000,'saving')";
no=st.executeUpdate(query);
System.out.println(no+"record inserted");
query="insert into account values(109,12000,'saving')";
no=st.executeUpdate(query);
System.out.println(no+"record inserted");
con.commit();
st.close();
con.close();
}
catch(Exception ex)
{
System.out.println(ex);
}
}}
Setting and Rolling Back to a Savepoint:
The JDBC 3.0 API adds the method Connection. Set Save point, which sets a savepoint within the current transaction. The Connection.rollback method has been overloaded to take a savepoint argument. The example below inserts a row into a table, sets the
savepoint svpt1, and then inserts a second row. When the transaction is later rolled back tosvpt1, the second insertion is undone, but the first insertion remains intact. In other words, when the transaction is committed, only the row containing ?FIRST? will be added to TAB1:
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) VALUES (‘FIRST’)");
// set savepoint
Savepoint svpt1 = conn.setSavepoint("SAVEPOINT_1");
rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) VALUES (‘SECOND’)");
...
conn.rollback(svpt1);
...
conn.commit();
Releasing a Savepoint:
The method Connection.rollback() release Savepoint takes a Savepoint object as a parameter and removes it from the current transaction. Once a savepoint has been released, attempting to reference it in a rollback operation causes an SQLException to be
thrown. Any savepoints that have been created in a transaction are automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes
invalid any other savepoints that were created after the savepoint in question.
When to Call the Method rollback:
As mentioned earlier, calling the method rollback aborts a transaction and returns any values that were modified to their previous values. If you are trying to execute one or more
statements in a transaction and get an SQLException, you should call the method rollback to abort the transaction and start the transaction all over again. That is the only way to be sure of what has been committed and what has not been committed. Catching an SQLException tells you that something is wrong, but it does not tell you what was or was not committed. Since you cannot count on the fact that nothing was committed, calling the method rollback is the only way to be sure.
Batch Updation:
Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database.
When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance.
JDBC drivers are not required to support this feature. You should use the DatabaseMetaData.supportsBatchUpdates() method to determine if the target database supports batch update processing. The method returns true if your JDBC driver supports this feature.
The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch. The executeBatch() is used to start the execution of all the statements grouped together.
The executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement.
Just as you can add statements to a batch for processing, you can remove them with the clearBatch() method. This method removes all the statements you added with the addBatch() method. However, you cannot selectively choose which statement to remove.
Batch Processing with Statement Object:
Here is a typical sequence of steps to use Batch Processing with Statement Object −
Create a Statement object using either createStatement() methods.
Set auto-commit to false using setAutoCommit().
Add as many as SQL statements you like into batch using addBatch()method on created statement object.
Execute all the SQL statements using executeBatch() method on created statement object.
Finally, commit all the changes using commit() method.
Example:
import java.sql.*;
public class BatchUpdate
{
public static void main(String[] args)
{ try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:sydsn");
Statement st = con.createStatement();
con.setAutoCommit(false);
String query="insert into account values(111,'King Khan',25500,'Saving')";
st.addBatch(query);
query = "insert into account values(112,'Hrithik',55500,'Saving')";
st.addBatch(query);
query ="update account set accname='Queen Khan',balance=60000 where accno=112";
st.addBatch(query);;
int[] count = st.executeBatch();
con.commit();
st.close();
con.close();
}
catch(Exception e)
{
e.printStackTrace();
}
System.out.println("Goodbye!");
}
}
Batch Processing with PrepareStatement
Here is a typical sequence of steps to use Batch Processing with PrepareStatement Object −
Create SQL statements with placeholders.
Create PrepareStatement object using either prepareStatement()methods.
Set auto-commit to false using setAutoCommit().
Add as many as SQL statements you like into batch using addBatch()method on created statement object.
Execute all the SQL statements using executeBatch() method on created statement object.
Finally, commit all the changes using commit() method.
Example:
import java.sql.*;
import java.util.*;
class PreparedStBatchUpdate
{
public static void main(String[] args)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:sydsn");
String query="insert into account Values(?,?,?,?)";
PreparedStatement ps=con.prepareStatement(query);
con.setAutoCommit(false);
ps.setInt(1,198);
ps.setString(2,"John");
ps.setDouble (3,67890);
ps.setString(4,"saving");
ps.addBatch();
ps.setInt(1,199);
ps.setString(2,"Joyce");
ps.setDouble (3,56789);
ps.setString(4,"saving");
ps.addBatch();
int[] count = ps.executeBatch();
con.commit();
ps.close();
con.close();
System.out.println("Done!");
}
catch(Exception ex)
{
System.out.println(ex);
}
}
}
interface Blob:
An SQL BLOB is a built-in type that stores a Binary Large Object as a column value in a row of a database table. By default drivers implement Blob using an SQL locator(BLOB), which means that a Blob object contains a logical pointer to the SQL BLOB data rather than the data itself. A Blob object is valid for the duration of the transaction in which is was created.
Method Summary
Program to insert image to table:
import java.sql.*;
import java.io.*;
class InsertImg
{
public static void main(String args[])
{
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sydb","root","msc2");
File f= new File("kisi.jpg");
FileInputStream fis = new FileInputStream(f);
PreparedStatement ps=con.prepareStatement("insert into studinfo values(?,?,?)");
ps.setInt(1,201);
ps.setString(2,"trishna");
ps.setBinaryStream(3, fis, (int) f.length());
ps.executeUpdate();
fis.close();
ps.close();
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
Program to read Images from table:
import java.sql.*;
import java.io.*;
import java.util.Scanner;
class ReadImgage
{
public static void main(String args[])
{
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sydb","root","msc2");
Statement st=con.createStatement();
System.out.println("Enter rollno:");
Scanner sc=new Scanner(System.in);
int roll=sc.nextInt();
ResultSet rs=st.executeQuery("select photo from studinfo where rollno="+roll);
byte img[]=new byte[6500];
while(rs.next() )
{ img=rs.getBytes("photo");
}
FileOutputStream fos = new FileOutputStream("e://"+roll+".jpg");
fos.write(img);
}
catch(Exception e)
{
System.out.println(e);
} } }
Interface CLOB:
An SQL CLOB is a built-in type that stores a Character Large Object as a column value in a row of a database table. By default drivers implement a Clob object using an SQL locator(CLOB), which means that a Clob object contains a logical pointer to the SQL CLOB data rather than the data itself. A Clob object is valid for the duration of the transaction in which it was created.
Method Summary
Reviewed by Asst. Prof. Sunita Rai, Computer Sci.. Dept., G.N. Khalsa College, Mumbai
on
January 16, 2022
Rating:
No comments: