Transcript jdbc

Adv Java
• Chapter 1.JDBC
• Chapter 2.Servlets
• Chapter 3.JSP
JDBC
• Every Enterprise application is required to access data base
either for retrieving data or storing data. Java offers a clean
approach for data base connectivity through JDBC using which
a java application can be connected virtually to any
DBMS/RDBMS.
• JDBC is not a software product. It is an API,which defines
interfaces and classes for writing data base applications in Java.
This API contains java.sql and javax.sql packages that provide
necessary library support for the data base aware java
applications.
• Our JDBC applications consists of Java application, JDBC
API,Driver Manager, JDBC Driver,ODBC Driver,Data base.
Types of Drivers
• The type 1 driver,JDBC-ODBC Bridge: This translates all Jdbc
calls into Odbc calls and sends them to the Odbc driver. As
such, the client must have Odbc driver and client data base.
• Client
Server
JDBC-ODBC Bridge
ODBC Driver
Vendor DB Library
Data Base Server
•Advantages: As the client have its own Odbc driver and client data
base, it can access any data base.
•DisAdv: Jdbc calls are translated into Odbc calls and the sent to
the Odbc driver, later these calls are passed to data base, the
response also comes back in the same process. And hence
performance degradation.
•Type 2:native ApI/partly Java driver
This converts JDBC calls into database calls so that it directly
communicates with the data base server.
• Client
Server
Native –API
Data Base server
Vendor DB Library
•Adv: performance is good than type1.
•Dis Adv: vendor Data base library must be loaded on client
Type 3-Net protocol/all java driver:
The JDBC calls are passed to server through a network that
consist of a middle tier server. This middle server translates the
JDBC calls into data base calls then sent to the data base server.
client
middle level server
database server
Net protocol
•Adv: This driver fully java based,there is no need of vendor
data base to be had at client. This is small and fast to load.
•Dis Adv: Data base coding should be done at middle tier server.
Type 4-Native protocol/all java driver:
The JDBC calls are translated into data base calls so that client
direct ly communicates with the data base server. This driver is
ful ly implemented in Java hence platform independent
client
database sever
Native protocol
•Adv: .elimination of ODBC layer and no need to have vendor data base
library at client.
•Dis Adv: The user needs a different driver for each data base.
Standard steps in JDBC:
1.Loading the data base driver: The driver is loaded with Class.forName()
method.
2.Define the connection URL: the URL indicates server host, port, and data
base name with which to establish a connection .
3.Establish a connection: With the connection URL, user name and password
, a network connection is established, data base queries can be reformed
until the connection is closed.
4.Create a statement object: This sends queries and commands to the data
base.
5.Execute a query: using execute, executeQuery ,
execute update or executeBatch methods.
6.Process the result: When a data base query is executed ,a
Resultset is returned. This denotes a set of rows and columns
that we can process by calls to next() and various get xxx
methods.
7.Close the connection: closing the connection releases the
resources to the data base.
•Statement
A Statement object is used for executing a static SQL statement
and obtaining the results it produces. Statement defines these
three methods for executing SQL statements, which handle SQL
commands returning different kinds of results:
executeUpdate(String sql): Execute a SQL INSERT, UPDATE, or
DELETE statement, which returns either a count of rows affected
or zero.
executeQuery(String sql): Execute a SQL statement that returns a
single ResultSet.
execute(String sql): Execute a SQL statement that may return
multiple results.
The Statement object defines the following supporting methods:
getMoreResults
getResultSet
getUpdateCount
These methods let you navigate through multiple results. You
can use getResultSet() or getUpdateCount() to retrieve the result
and getMoreResults()to move to any subsequent results.
PreparedStatement
• PreparedStatements are nothing more than statements that
are precompiled.
• Precompilation means that these statements can be executed
more efficiently than simple statements, particularly in
situations where a Statement is executed repeatedly in a loop.
• PreparedStatements can contain placeholders for variables
known as IN parameters, which are set using setter methods.
A typical setter method looks like this:
public void setObject(int parameterIndex, Object x) throws
SQLException
eg: pstmt.setInt(1, 2);
public class PreparedStmt{
public static void main(String args[]){
int qty; float cost; String name; String desc;
String query = ("SELECT * FROM Stock WHERE Item_Number =
?“);
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver";
Connection con =
DriverManager.getConnection ("jdbc:odbc:Inventory");
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setInt(1, 2);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
name = rs.getString("Name");desc = rs.getString("Description");
qty = rs.getInt("Qty");
cost = rs.getFloat("Cost");
System.out.println(name+", "+desc+"\t: "+qty+"\t@ $"+cost);
} }
catch(ClassNotFoundException e){
e.printStackTrace();
Chapter 4:Introduction to JDBC
}
catch(SQLException e){
e.printStackTrace();
}}}
CallableStatement
The CallableStatement object allows you to call a database
stored procedure from a Java application. A CallableStatement
object contains a call to a stored procedure; itdoes not contain
the stored procedure itself, as the stored procedure is stored in
the database.
import java.sql.*;
public class CallableStmt{
public static void main(String args[]){
int orderNo;
String name;
String storedProc = "create procedure SHOW_ORDERS_BY_STATE
"+"@State CHAR (2) "+"as "+
"select c.Last_Name+', '+c.First_Name AS Name,"+
"o.Order_Number "+ "from CUSTOMERS c, ORDERS o "+
"where c.Customer_Number = o.Customer_Number "+
"AND c.State = @State "+ "order by c.Last_Name;";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con =
DriverManager.getConnection("jdbc:odbc:Customers");
Statement stmt = con.createStatement();
stmt.executeUpdate(storedProc);
CallableStatement cs = con.prepareCall("{call
SHOW_ORDERS_BY_STATE(?)}");
cs.setString(1,"NJ");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
name = rs.getString("Name");
orderNo = rs.getInt("Order_Number");
System.out.println(name+": "+orderNo);
}
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
catch(SQLException e){
e.printStackTrace();
}}}
Getting an output parameter from a stored procedure
CallableStatement cs =
con.prepareCall("{call CHECK_USER_NAME(?,?,?)}");
cs.setString(1,"Corleone");
cs.setString(2,"Vito");
cs.registerOutParameter(3, java.sql.Types.VARCHAR);
cs.executeUpdate();
System.out.println(cs.getString(3));
con.close();
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
catch(SQLException e){
e.printStackTrace();
}}}
•Transactions
•The capability to group SQL statements for execution as a single
entity is provided through SQL's transaction mechanism. A
transaction consists of one or more
•statements that are executed, completed, and either committed
or rolled back as a group. When the method commit or rollback
is called, the current transaction ends,and another one begins.
•A new JDBC connection is usually in auto-commit mode by
default, meaning that when a statement is completed, the
method commit is called on that statement automatically.
•Auto-commit mode is controlled by this method:
public void setAutoCommit(boolean autoCommit) throws
SQLException
•If auto-commit mode has been disabled, a transaction will not
terminate until either the commit method or the rollback
method is called explicitly,
•Transaction Savepoints
Transaction savepoints are JDBC 3.0 enhancements that offer
finer control over transaction commit and rollback. During a
transaction, a named savepoint may be inserted between
operations to act as a marker, so that the transaction may be
rolled back to that marker, leaving all of the operations before
the marker in effect.
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.executeUpdate(update1);
Savepoint savePoint1 = con.setSavepoint("SavePoint1");
stmt.executeUpdate(update2);
stmt.executeUpdate(update3);
con.rollback(savePoint1);
con.commit();
• Batch Updates
• A batch update is a set of update statements submitted to the
database for processing as a batch. This can be more efficient
than sending update statements separately.
• Commands used to manage batch updates include the
following:
• AddBatch (add SQL commands to the batch list)
• clearBatch (empty the batch list)
• executeBatch (execute all statements in the list as a batch)
• Note :1.Only statements that produce an update count can be
used in a batch update. Statements that return a ResultSet
object, such as a SELECT statement, cannot be used in a batch.
• 2. The batch list associated with a Statement is initially empty.
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO CUSTOMERS VALUES('Homer',
'Simpson')");
stmt.addBatch("INSERT INTO CUSTOMERS VALUES('Bart',
'Simpson')");
stmt.addBatch("INSERT INTO CUSTOMERS VALUES('Marge',
'Simpson')");
int [] updateCounts = stmt.executeBatch();
con.commit();
con.setAutoCommit(true);
ResultSets
A ResultSet is the data a SQL Query returns, consisting of all the
rows that satisfy theconditions of that query arranged in rows
accessible through the ResultSet's methods.
ResultSets are arranged as a table, with the column headings and
values returned in the order specified in the statement,
satisfying the conditions of the query.
while (rs.next()) {
System.out.print(rs.getString("Name")+"\t");
System.out.print(rs.getString("Description")+"\t");
System.out.print(rs.getInt("Qty")+"\t");
System.out.println(rs.getFloat("Cost"));
}
ResultSet rs = stmt.executeQuery("SELECT First_Name,
Last_Name FROM Customers");
while (rs.next()){
System.out.println(rs.getString(2)+’,’+rs.getString(1));
}
• ResultSetMetaData
• Information about the columns in a ResultSet is available by
calling the getMetaData() method. The ResultSetMetaData
object returned gives the number, types, and properties of its
ResultSet object's columns.
• Some of the methods available to access ResultSetMetaData
are as follows:
getColumnCount() — Returns the number of columns in the
ResultSet
• getColumnDisplaySize(int column)— Returns the column's
normal max width in chars
• getColumnLabel(int column) — Returns the column title for
use in printouts and displays
• getColumnName(int column) — Returns the column name
• getColumnType(int column) — Returns the column's SQL
data-type index
• getColumnTypeName(int column)— Returns the name of the
column's SQL data type
• getPrecision(int column)— Returns the number of decimal
digits in the column
• getScale(int column) — Returns the number of digits to right
of the decimal point
• getTableName(int column) — Returns the table name
• isAutoIncrement(int column) — Returns true if the column is
automatically numbered
• isCurrency(int column) — Returns true if the column value is
a currency
• isNullable(int column)— Returns true if the column value can
be set to NULL
Statement st=con.createStatement();
String s="insert into student values('sri',23)";
String q="insert into student values('java',24)";
st.executeUpdate(s);
st.executeUpdate(q);
ResultSet rs=st.executeQuery("select * from student");
ResultSetMetaData rsmd=rs.getMetaData();
int count=rsmd.getColumnCount();
System.out.println("no of Cols"+count);
for(int i=1;i<count;i++)
{
System.out.println("names of Cols"+rsmd.getColumnName(i));
System.out.println("Types of
Cols"+rsmd.getColumnTypeName(i));
}
Scrollable ResultSet: We can move the cursor as we desire in any
direction. By default the cursor moves in forward direction only.
Connection interface has 2 overloaded methods.
1.createStatement()
2. createStatement(int resultsettype,int resultsetconcurrency)
The second createStatement(int re,int re) method
Supports scrollable resultset.
The first argument takes the following parameters.
ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_SENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE
The second argument takes the following.
ResultSet.CONCUR_READ_ONLY
ResultSet.CONCUR_UPDATABLE
By default ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY
To get scrollable we must specify TYPE_SCROLL_SENSITIVE
TYPE_SCROLL_SENSITIVE
The diffrence between these two is INSENSITIVE does not reflect
changes made by ResultSet and SENSITIVE does
import java.io.*;
import java.sql.*;
class scroll{
public static void main(String args[]) throws Exception{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection
con=DriverManager.getConnection("jdbc:odbc:java");
Statement
st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,Re
sultSet.CONCUR_READ_ONLY);
ResultSet rs=st.executeQuery("select * from student");
rs.first();
System.out.println(rs.getString(1));
rs.last();
System.out.println(rs.getString(1));
rs.absolute(4);
System.out.println(rs.getString(1)); }}
UpDatable ResultSet: Without using SQL commands we can
update ,insert,delete rows programmatically. To have this
ability,we need to create a ResultSet object that is updatable.For
this we must supply the ResulSet constant CONCUR_UPDATABLE
to createStatement object.
Updating a row: Move the cursor to the required row.
Call the updatexxx() method .
Call updateRow() method.
Inserting a row: To move the cursor to the required row,call
moveToInsertRow() method.
To set a value in each column of the row call updatexxx()
call the method insertRow().
Delete a row: Move the cursor to the required row.
call the method deleteRow() method.
class updatable{
public static void main(String args[]) throws Exception
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection
con=DriverManager.getConnection("jdbc:odbc:java");
Statement
st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs=st.executeQuery("select * from student");
rs.last();
rs.updateInt(2,30);
rs.updateRow();
System.out.println("1 row effected");
rs.moveToInsertRow();
rs.updateString(1,"java");
rs.updateInt(2,35);
rs.insertRow();
}
}