Q) Improve Performance in JDBC?
Choosing right Driver
- If you have tiered requirement ( two tier or three tier) , then filter down your choices, for example if your application is three tiered, then you can go for Type three driver. If you want to connect to database from java applet, then you have to use Type four driver because it is only the driver which supports that feature
- Type 3 & 4 drivers are faster than other drivers because Type 3 gives facility for optimization techniques provided by application server such as connection pooling, caching, load balancing etc and Type 4 driver need not translate database calls to ODBC or native connectivity interface. Type 1 drivers are slow because they have to convert JDBC calls to ODBC through JDBC-ODBC Bridge driver initially and then ODBC Driver converts them into database specific calls. Type 2 drivers give average performance when compared to Type 3 & 4 drivers because the database calls have to be converted into database specific calls. Type 2 drivers give better performance than Type 1 drivers.
- Get connection from DataSource. You can get the connection using javax.sql.DataSource interface.
Use Connection pool
Control transaction
By default in JDBC transaction starts and commits after each statement's execution. That is the AutoCommit mode is true. Programmer need not write a commit() method explicitly after each statement. This default mechanism gives good facility if they want to execute a single statement. But it gives poor performance when multiple statements to be executed because commit is issued after each statement by default, that in turn reduces performance by issuing unnecessary commits. The remedy is to flip it back to AutoCommit mode as false and issue commit() method after a set of statements execute, this is called as batch transaction. Use rollback() in catch block to rollback the transaction whenever an exception occurs in your program. The following code illustrates the batch transaction approach.
Choosing a right isolation level for your program
If you write a critical program like bank or stocks analysis program, you can choose TRANSACTION_SERIALIZABLE for maximum safety. Here it is the tradeoff between the safety and performance. If you don't have to deal with concurrent transactions your application, then the best choice is TRANSACTION_NONE to improve performance. If your application needs only committed records, then TRANSACTION_READ_COMMITED isolation is the good choice. If your application needs to read a row exclusively till you finish your work, then TRANSACTION_REPEATABLE_READ is the best choice.
Do batch update & Do batch retrieval using Statement
- You can send multiple queries to the database at a time using batch update feature of statement objects this reduces the number of JDBC calls and improves performance.
- You can get the default number of rows that is provided by the driver. You can improve performance by increasing number of rows to be fetched at a time from database using setFetchSize() method of the statement object.
Close Statement when finished
- Close statement object as soon as you finish working with that, it explicitly gives a chance to garbage collector to recollect memory as early as possible which in turn effects performance
Q) Why we are always doing rs.next() in first line of while loop in retrieving data from database in result set?
Always the Recordset points to the previous to the First record therfore we need to move the pointer to the first record.
Q) JDBC Drivers
| Drivers | Description |
| 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. |
| Native API - Partly Java driver | This driver converts JDBC calls to database specific native calls. Client requires database specific libraries. |
| Net 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. |
| Native protocol - All Java driver | They are pure java driver, they communicate directly with the vendor database. |
Q) Steps to connect to JDBC?
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 appropriate 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) JDBC connection
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”);
Q) What happens when execute Class.forname("sun.jdbc.odbc.JdbcOdbcDriver ")?
forName is the static method which is used to load the driver in the memory, JVM will execute its static blocks after the class is loading and it will identify the driver to process the database stuff. All the JDBC drivers’ hava static blocks that registers itself with drivermanager.
Q) Statements in JDBC
Statement D’t take any arguments, it will check syntax error & execute it every time (it will parse every time).
Prepare statement are precompiled statements once we compile the statements and send it to the server for later use. P.S are partially compiled statements placed at server side with placeholders. Before execution of these statements user has to supply values for place holders, it will increase performance of application.
PreparedStatement pst = con.prepareStatement("SELECT * FROM EMP WHERE deptno=?");
DataInputStream dis = new DataInputStream(“System.in”);
Int dno = Integer.ParseInt(dis.readLine());
pst.setInt(1, dno);
ResultSet rs = pst.executeQuery();
New methods:- setBlob(), setClob(), and setNClob() were added to insert a BLOB object using an InputStream object, and to insert CLOB and NCLOB objects using a Reader object.
Callable statement used to retrieve data by invoking stored procedures, stored procedure are program units placed at data base server side for reusability. These are used by n-number of clients. Stored procedure is precompiled in RDBMS, so they can run faster than the dynamic sql. 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();
In used to send information to the procedure.
Out used to retrieve information from data base.
InOut both.
Q) Dynamically creating Tables
Int n = stmt.executeUpdate(“create table “ + uname+ “(sno int, sentby varchar(10), subject varchar(15)”);
Q) Diff execute() , executeUpdate() and executeQuery() ?
execute() is used for DDL operations, returns a boolean value, which may return multiple results.
executeUpdate() à is used for DML operations, 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) Handle Multiple result sets when Procedure executes?
A stored procedure can return multiple result sets, multiple update counts or some combination of both.
Statement.execute() returns a boolean to tell you the type of response
“true” indicates next result is a ResultSet, cst.getResultSet() to get the ResultSet.
“false” indicates next result is an update count, cst.getUpdateCount() to get the update count.
“false” also indicates no more results, Update count is -1 when no more results (usually 0 or positive)
After processing each response, you use cst.getMoreResults() to check for more results, again returning a boolean.
Ex;-
boolean result = stmt.execute("");
int updateCount = stmt.getUpdateCount();
while (result || (updateCount != -1)) {
if(result) {
ResultSet r = stmt.getResultSet();
// process result set
} else if(updateCount != -1) {
// process update count
}
result = stmt.getMoreResults();
updateCount = stmt.getUpdateCount();
}
Ex;-
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();
} }
| CLOSE_ALL_RESULTS | All previously opened ResultSet objects should be closed when calling getMoreResults(). |
| CLOSE_CURRENT_RESULT | The current ResultSet object should be closed when calling getMoreResults(). |
| KEEP_CURRENT_RESULT | The current ResultSet object should not be closed when calling getMoreResults(). |
Q) How can I retrieve only the 1st / 2nd n rows of a dbase using a particular WHERE clause ? For example, if a SELECT returns a 1000 rows, how do first retrieve the 100 rows, then go back and retrieve the next 100 rows?
Use the Statement.setFetchSize() method to indicate the size of each database fetch. Note that this method is only available in the Java 2 platform.
Ex:- Statement stmt = con.createStatement();
stmt.setFetchSize(400);
ResultSet rs = stmt.executeQuery("select * from customers");
will change the default fetch size to 400. You can also control the direction in which the rows are processed.
stmt.setFetchSize(400);
ResultSet rs = stmt.executeQuery("select * from customers");
will change the default fetch size to 400. You can also control the direction in which the rows are processed.
stmt.setFetchDirection(ResultSet.FETCH_REVERSE)
Q) ResultSetMetaData
Used to find out the information of a table in a data base.
ResultSet rs = stmt.executeQuery("SELECT * FROM "+ table);
ResultSetMetaData rsmd = rs.getMetaData();
Methods getColumnCount(), getColumnName(), getColumnLabel(), getColumnType(), getTableName(),
Q) Database MetaData
Used to find out the information of the “data base” & “dictionary”. 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().
Q) Diff Resultset & Rowset?
A resultset maintains a connection to the database and rowset can be disconnected once it is populated with data from the table. Resultset is not serializable rowset is a serializable version of resultset and also it extends resultset interface, so all the methods are present in rowset also.
Q) How can I get information about foreign keys used in a table?
DatabaseMetaData.getImportedKeys() returns a ResultSet with data about foreign key columns, tables, sequence and update and delete rules
DatabaseMetaData.getImportedKeys() returns a ResultSet with data about foreign key columns, tables, sequence and update and delete rules
Q) SQL Warnings
Warnings may be retrieved from Connection, Statement and ResultSet objects. Trying to retrieve a warning on a connection / statement / 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.
SQLWarning warning = stmt.getWarnings();
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();
}
}
Q) Do I need to commit after an INSERT call in JDBC or does JDBC do it automatically in the DB?
If your autoCommit flag “con.setAutoCommit()” is false, you are required to call the commit(). The default JDBC behavior is to automatically commit each SQL statement after execution.
To change this behavior and execute multiple SQL statements as part of a transaction, you have to invoke the setAutoCommit(false) method on your Connection object. If auto commit is false, then SQL statements won't be committed until you invoke the commit() method on the connection. You also have the option of invoking the rollback() method, to rollback the database changes since the previous commit.
Q) Transactional Savepoints
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
Q Retreiving / Storing / Updating Array of Objects
Array a = rs.getArray(1);
Pstmt.setArray(2, member_array);
rs.updateArray(“last_num”,num);
Q) How to execute no of queries at one go?
By using a batchUpdate's (i.e. throw addBatch() and executeBatch()) in java.sql.Statement interface or by using procedures.
Q) Batch Updates
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
conn.setAutoCommit(false);
stmt.addBatch("INSERT INTO survey(id, name) VALUES('11', 'Alex')");
stmt.addBatch("INSERT INTO survey(id, name) VALUES('22', 'Mary')");
int[] updateCounts = stmt.executeBatch();
conn.commit();
}
catch(BatchUpdateException b) {
System.err.println("SQLException: " + b.getMessage());
System.err.println("SQLState: " + b.getSQLState());
int [] updateCounts = b.getUpdateCounts();
}
conn.setAutoCommit(false);
stmt.addBatch("INSERT INTO survey(id, name) VALUES('11', 'Alex')");
stmt.addBatch("INSERT INTO survey(id, name) VALUES('22', 'Mary')");
int[] updateCounts = stmt.executeBatch();
conn.commit();
}
catch(BatchUpdateException b) {
System.err.println("SQLException: " + b.getMessage());
System.err.println("SQLState: " + b.getSQLState());
int [] updateCounts = b.getUpdateCounts();
}
Q) In distributed architecture (typical three tier consisting of thin client, middleware & database) which type of JDBC driver should be used and why?
Normally in 3-tier architectures, you would preferably connect to database via middleware. Pure java drivers are slower than native driver but they do not require any client-side installation. So for middleware, as performance is important, native JDBC drivers like Oracle's OCI driver should be preferred. Also native installation(like ODBC or Oracle's Sqlnet ) would be required at middleware level but it would not affect the clients. In the exceptional case, if the database is needed to be accessed by thin client than pure Java driver like Oracle's thin driver is recommended as they do not require any client-side installation.
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.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 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
}
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.
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.
Q) Why you need JDBC if ODBC is available?
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.
Q) Can we establish the connection with ODBC itself?
Yes, using java native classes we have to write a program.
Q) What is necessity of JDBC in JDBC-ODBC bridge?
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.
Q) Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.
Q) Is the JDBC-ODBC Bridge multi-threaded?
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
Q) In which interface the methods commit() & rollback() savepoint() defined ?
java.sql.Connection interface
Q) Retrieving very large values from database?
getASSCIISteram() à read values which are character in nature.
GetBinaryStream() à used to read images.
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)
Q) enumerate all the loaded JDBC drivers?
public class Main {
public static void main(String[] args) throws Exception {
Class driverClass = Class.forName("org.hsqldb.jdbcDriver");
DriverManager.registerDriver((Driver) driverClass.newInstance());
java.util.Enumeration e = java.sql.DriverManager.getDrivers();
while (e.hasMoreElements()) {
Object driverAsObject = e.nextElement();
System.out.println("JDBC Driver=" + driverAsObject);
}
}
Q) Procedure
Is a subprogram will perform some specific action, sub programs are name PL/SQL blocks that can take parameters to be invoked.
create (or) replace procedure procedure-name (id IN INTEGER , bal IN OUT FLOAT) IS
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;
Q) Trigger
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.
Create/Replace trigger before Insert (or) Delete (or) Update on emp for each row
Begin
Insert into table-name values(:empno; :name)
end
Q) Stored Images into a table
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();
}
Retrieve Image
Statement st = con.CreateStatement();
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);
}
