Q) What Class.forName will do while loading
drivers?
A) Will create an instance of the driver and register with the DriverManager.
Q) JDBC 3.0 new features?
A) 1.
Transaction Savepoint support:
- Added the Savepoint interface, which contains new methods to set, release, or
roll back a transaction to designated savepoints.
2. Reuse of
prepared statements by connection pools: - to control how prepared statements
are pooled and reused by connections.
3. Connection pool configuration :- Defined a
number of properties for the ConnectionPoolDataSource interface.
These properties can be used to describe how
PooledConnection objects created by DataSource objects should be pooled.
4. Retrieval of parameter metadata: - Added
the interface ParameterMetaData, which describes the number, type
and properties of parameters to prepared
statements.
5. Retrieval of
auto-generated keys: - Added a means of retrieving values from columns
containing automatically
generated values.
6. Multiple open
ResultSet objects: - Added the new method
getMoreResults(int).
7.
Passing parameters to CallableStatement objects by name: - Added methods to
allow a string to identify the parameter to be set for a CallableStatement
object.
8.
Holdable
cursor support: - Added the ability to specify the of holdability of a
ResultSet object.
9.
BOOLEAN data type: - Added the data
type java.sql.Types.BOOLEAN. BOOLEAN is logically equivalent to BIT.
10.
Making
internal updates to the data in Blob and Clob objects: - Added methods to allow
the data contained in Blob and Clob objects to be altered.
11.
Retrieving
and updating the object referenced by a Ref object: - Added methods to retrieve
the object referenced by a Ref object. Also added the ability to update a
referenced object through the Ref object.
12.
Updating of columns containing BLOB, CLOB, ARRAY and REF
types: - Added of the updateBlob, updateClob,
updateArray, and updateRef methods to the ResultSet interface.
- JDBC-ODBC
Bridge Driver
- Native
API - Partly Java Driver
- Network
protocol - All Java Driver
- Native
Protocol - Pure Java Driver
|
Tier |
Driver
mechanism |
Description |
|
Two |
JDBC-ODBC
|
JDBC
access via most ODBC drivers, some ODBC binary code and client code must be
loaded on each client machine. This driver is commonly used for prototyping.
The JDBC-ODBC Bridge is JDBC driver which implements JDBC operations by
translating them to ODBC operations. |
|
Two |
Native
API - Partly Java driver |
This
driver converts JDBC calls to database specific native calls. Client requires
database specific libraries. |
|
Three |
Network protocol - All Java Driver |
This
driver converts JDBC calls into DBMS independent network protocol that is
sent to the middleware server. This will translate this DBMS independent
network protocol into DBMS specific protocol, which is sent to a particular
database. The results are again rooted back to middleware server and sent
back to client. |
|
Two |
Native
protocol - All - Java driver |
They
are pure java driver, they communicate directly with the vendor database. |
import java.sql.*;
public class JDBCSample {
public static void main(java.lang.String[] args) {
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (ClassNotFoundException e) {
System.out.println("Unable to load Driver Class");
return;
}
try
{
Connection con =
DriverManager.getConnection("jdbc:odbc:companydb","",
"");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT FIRST_NAME FROM
EMPLOYEES");
while(rs.next()) {
System.out.println(rs.getString("FIRST_NAME"));
}
rs.close();
stmt.close();
con.close();
}
catch (SQLException se) {
System.out.println("SQL Exception: " + se.getMessage());
}
}
}
Q) 4th type driver
class.forName(“oracle.jdbcdriver.oracledriver”);
connection con =
driverManager.getConnection(“JDBC:oracle:thin:@hostname:portno:oracleservice”,”uid”,
“pwd”);
A) 1. First thing is
using jdbc you have to establish a connection to the data base this is 2 steps
process (i) you must load the jdbc driver (ii) then make a connection, to do
this we can call the getConnection() method of driver manager class.
2. To execute any sql
commands using jdbc connection you must first create a statement object to
create this call statement st = con.createSteatement().
This is done by calling the createStatement()
method in connection interface. Once the statement is created you can executed
it by calling execute() method of the statement interface.
Q) Resultset Types
rs.beforeFirst() à goto 1st record
rs.afterLast() à
goto last record
isFirst()
/ isLast()
res.absolute(4) à
will got 4th record in result set.
rs.deleteRow()
rs.updateRow(3,88) à
value in column 3 of resultset is set to 88.
rs.updateFloat()
rs.relative(2)
Statement stmt = conn.createStatement
();
Int rowcount = stmt.executeUpdate ("insert into etable
(event) values ('TMM')");
Int rowcount = stmt.executeUpdate ("insert into costs (cost)
values (45.0)");
Savepoint sv1 = conn.setSavePoint ("svpoint1"); //
create save point for inserts
Int rowcount = stmt.executeUpdate ("delete from employees");
Conn.rollback (sv1); // discard the delete statement but keep the
inserts
Conn.commit; // inserts are now permanent
rs.next();
Blob data = rs.getClob (1);
Rs.close();
// now let's insert this history into another table
stmt.setClob (1, data); // data is the Clob object we retrieved from the
history table
int InsertCount = stmt.executeUpdate("insert into
EscalatedIncidents (IncidentID, CaseHistory, Owner)"
+ " Values (71164, ?, 'Goodson') ");
Array a = rs.getArray(1);
Pstmt.setArray(2, member_array);
Rs.updateArray(“last_num”,num);
A) By using a
batchUpdate's (i.e. throw addBatch() and executeBatch()) in java.sql.Statement
interface or by using procedures.
CallableStatement stmt = con.prepareCall(“{call employeeInfo
(?)}”);
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe
Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260,
'Shoe')");
// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();
A)
The methods getMoreResults, getUpdateCount, and
getResultSet can be used to retrieve all the results.
CallableStatement
cstmt = connection.prepareCall(procCall);
boolean
retval = cstmt.execute();
if (retval
== false) {
} else {
ResultSet rs1 = cstmt.getResultSet();
retval =
cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT);
if (retval == true) {
ResultSet rs2 = cstmt.getResultSet();
rs2.next();
rs1.next();
}
}
|
|
All previously opened |
|
|
The current |
|
|
The current |
A) execute() returns a boolean value, which may return multiple results.
executeUpdate() is used for nonfetching queries, which returns int value and tell how many rows will be affected.
executeQuery() is used for fetching queries, which returns single
ResulSet object and never return Null value.
Q) How to move the cursor in scrollable resultset?
Type of a ResultSet object:-
TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE, CONCUR_READ_ONLY and CONCUR_UPDATABLE.
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT COLUMN_1,
COLUMN_2 FROM TABLE_NAME");
rs.afterLast();
while (srs.previous()) {
String name =
rs.getString("COLUMN_1");
float salary =
rs.getFloat("COLUMN_2");
rs.absolute(4); // cursor is on the fourth
row
int rowNum = rs.getRow(); // rowNum should
be 4
rs.relative(-3);
int rowNum = rs.getRow(); // rowNum should
be 1
rs.relative(2);
int rowNum = rs.getRow(); // rowNum should
be 3
//...
}
Q) How to “Update” & “Delete”
a resultset programmatically?
Update: -
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery("SELECT COLUMN_1, COLUMN_2
FROM TABLE_NAME");
uprs.last();
uprs.updateFloat("COLUMN_2", 25.55);//update last row's
data
uprs.updateRow();//don't miss this method, otherwise, the data
will be lost.
Delete: -
uprs.absolute(5);
uprs.deleteRow(); // will delete row 5.
Q) JDBC connection pool
When you are going to caret a pool of connection to the database. This will give access to a collection of already opened data base connections, which will reduce the time it takes to service the request and you can service “n” number of request at once.
A) ODBC is purely
written in “c” so we cannot directly connect with java. JDBC is a low level
pure java API used to execute SQL statements. (i) ODBC is not appropriate for
direct use from java because it uses “c” interfaces. Calls from java to native
“c” code has number of drawbacks in the security, implementation and
robustness.
A) Yes, using java
native classes we have to write a program.
A) The purpose of JDBC
is to link java API to the ODBC, ODBC return high level “c” API so the JDBC
converts “c” level API to java API.
A) No. You can open
only one Statement object per connection when you are
using the JDBC-ODBC Bridge.
A) No. The JDBC-ODBC
Bridge does not support concurrent access from different threads. The JDBC-ODBC
Bridge uses synchronized methods to serialize all of the calls that it makes to
ODBC
Statement
st = con.cretaeStatement();
Int
n = st.executeUpdate(“create table “ + uname+ “(sno int, sentby varchar(10),
subject varchar(15)”);
DataInputStream dis = new
DataInputStream(“System.in”);
Int dno = Integer.ParseInt(dis.readLine());
pst.setInt(1, dno);
ResultSet rs = pst.executeQuery();
Callable statement will call a single stored
procedure, they perform multiple queries and updates without network traffic.
callableStatement cst =
con.prepareCall(“{CALL procedure-name(??)} ”);
DataInputStream dis = new DataInputStream(“System.in”);
Int enum = Integer.ParseInt(dis.readLine());
cst.setInt(1, enum);
cst.registerOutParameter(2, types.VARCHAR)
resultset rs = cst.execute();
Out - used to retrieve information from data base.
InOut - both.
A) java.sql.Connection interface
A) getASSCIISteram() - read values which are character in nature.
GetBinaryStream() - used to read images.
It is used to find out the information of a
table in a data base.
ResultSet rs = stmt.executeQuery("SELECT * FROM "+ table);
ResultSetMetaData rsmd = rs.getMetaData();
You need some information about the “data
base” & “dictionary” we use this .To find out tables, stored procedure
names, columns in a table, primary key of a table we use this, this is the largest
interface in java.sql package
Connection con = DriverManager.getConnection(jdbcURL, "", "");
DatabaseMetaData dbmd = con.getMetaData();
ResultSet rs= dbmd.getxxx();
Methods à getColumns(),
getTableTypes(),
getTables(),
getDriverName(), getMajorVersion(), get MinorVersion(), getProcedures(),
getProcedureColumns(), getTables().
Warnings may be retrieved from Connection, Statement, and ResultSet objects. Trying to retrieve a warning on a connection after it
has been closed will cause an exception to be thrown. Similarly, trying to
retrieve a warning on a statement after it has been closed or on a result set
after it has been closed will cause an exception to be thrown. Note that
closing a statement also closes a result set that it might have produced.
Connection.getWarnings()
Statement.getWarnings(),
ResultSet.getWarnings(), Serialized Form
if
(warning != null)
{
while
(warning != null)
{
System.out.println("Message:
" + warning.getMessage());
System.out.println("SQLState:
" + warning.getSQLState());
System.out.print("Vendor
error code: ");
System.out.println(warning.getErrorCode());
warning
= warning.getNextWarning();
}
}
Procedure
is a subprogram will perform some specific action, sub programs are name PL/SQL
blocks that can take parameters to be invoked.
BEGIN
select balance into
bal from accounts where account_id = id;
Bal: = bal + bal * 0.03;
Update accounts set balance = bal where account_id = id;
END;
Trigger
is a stored PL/SQL block associated with a specific database table. Oracle
executes triggers automatically when ever a given SQL operation effects the
table, we can associate 12 data base triggers with in a given table.
Begin
Insert into table-name values(:empno; :name)
end
Public class img
{
Public static void main(String args[]){
Class.forName();
Connection con = DriverManager.getConnection();
Preparestatement pst = con.prepareStatement(“insert into image
value(?));
FileInputStream fis = new FileInputStream(“a.gif”);
Pst.setBinaryStream(1, fis, fis.available);
Int
I = pst.executeUpadate();
}
ResultSet rs = st.executeQuery(“select
* from img”);
Rs.next();
InputStream is = rs.getBinaryStream(1);
FileOutPutStream fos = new
FileOutPutStream(“g2.gif”);
Int ch;
While((ch=is.read(1))!=!-1)
{
fos.write(ch);
}