Please click on any Advertisement, if you like this blog.

Saturday, September 10, 2011

Interview Questions : JDBC : Volume 2

Q. How to update a resultset programmatically? (new feature in JDBC 2.0)                                                         
a. create a scrollable and updatable ResultSet object.                                                                                               
Statement stmt = con.createStatement          (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);                              ResultSet uprs = stmt.executeQuery("SELECT   COLUMN_1,                                   
COLUMN_2 FROM TABLE_NAME");  
b. move the cursor to the specific position and use related method to update data and then, call updateRow() method.                                                                                                                                              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.                                                

Q. How to insert and delete a row programmatically? (new feature in JDBC 2.0)                                  Make sure the resultset is updatable. 
                                                         
1. move the cursor to the specific position.                     
uprs.moveToCurrentRow();  

2.Set value for each column                                              uprs.moveToInsertRow();//to set up for insert                    
uprs.updateString("col1","strvalue"); 
uprs.updateInt("col2",5);                                                                                                                                             
3.call inserRow() method to finish the row insert process.   uprs.insertRow();                                                         
 To delete a row: move to the specific position and call deleteRow() method:                  uprs.absolute(5);                                                                       uprs.deleteRow();//delete row 5              

To see the changes, call 
refreshRow(); 
uprs.refreshRow(); 
 
Q. What are the common tasks of JDBC?                   
Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers
Register a driver
Specify a database
Open a database connection
Submit a query
Receive results
Process results 

Please click on any advertisement if you like this blog.

 
Q. How can I know when I reach the last record in a table, since JDBC doesn't provide an EOF method?                                                                                                                                                      Answer1
You can use last() method of java.sql.ResultSet, if you make it scrollable.
You can also use isLast() as you are reading the ResultSet.
One thing to keep in mind, though, is that both methods tell you that you have reached the end of the current ResultSet, not necessarily the end of the table. SQL and RDBMSes make no guarantees about the order of rows, even from sequential SELECTs, unless you specifically use ORDER BY. Even then, that doesn't necessarily tell you the order of data in the table.

Answer2
Assuming you mean ResultSet instead of Table, the usual idiom for iterating over a forward only resultset is:
ResultSet rs=statement.executeQuery(...);
while (rs.next()) {
// Manipulate row here
}                                                                                                                                                                                 
Q. How can I insert images into a Mysql database?                                                                               This code snippet shows the basics:
File file = new File(fPICTURE);
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps =
ConrsIn.prepareStatement("insert into dbPICTURE values (?,?)");

// ***use as many ??? as you need to insert in the exact order***
ps.setString(1,file.getName());
ps.setBinaryStream(2,fis,(int)file.length());
ps.close();
fis.close();

Q. How do I check in my code whether a maximum limit of database connections have been reached?                                                                                                                                         
 Use DatabaseMetaData.getMaxConnections() and compare to the number of connections currently open. Note that a return value of zero can mean unlimited or, unfortunately, unknown. Of course, driverManager.getConnection() will throw an exception if a Connection can not be obtained. 

Please click on any advertisement on right side if you like this blog.


Q. What is the difference between setMaxRows(int) and SetFetchSize(int)? Can either reduce processing time?                                                                                                                              setFetchSize(int) defines the number of rows that will be read from the database when the ResultSet needs more rows. The method in the java.sql.Statement interface will set the 'default' value for all the ResultSet derived from that Statement; the method in the java.sql.ResultSet interface will override that value for a specific ResultSet. Since database fetches can be expensive in a networked environment, fetch size has an impact on performance.
setMaxRows(int) sets the limit of the maximum nuber of rows in a ResultSet object. If this limit is exceeded, the excess rows are "silently dropped". That's all the API says, so the setMaxRows method may not help performance at all other than to decrease memory usage. A value of 0 (default) means no limit.
Since we're talking about interfaces, be careful because the implementation of drivers is often different from database to database and, in some cases, may not be implemented or have a null implementation. Always refer to the driver documentation. 


Q.What's the best way, in terms of performance, to do multiple insert/update statements, a PreparedStatement or Batch Updates?                                                                                               Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.
A CallableStatement object provides a way to call stored procedures in a standard manner for all DBMSes. Their execution can be faster than that of PreparedStatement object.
Batch updates are used when you want to execute multiple statements together. Actually, there is no conflict here. While it depends on the driver/DBMS engine as to whether or not you will get an actual performance benefit from batch updates, Statement, PreparedStatement, and CallableStatement can all execute the addBatch() method. 


Q.What is the difference between setMaxRows(int) and SetFetchSize(int)? Can either reduce processing time?                                                                                                                       
 setFetchSize(int) defines the number of rows that will be read from the database when the ResultSet needs more rows. The method in the java.sql.Statement interface will set the 'default' value for all the ResultSet derived from that Statement; the method in the java.sql.ResultSet interface will override that value for a specific ResultSet. Since database fetches can be expensive in a networked environment, fetch size has an impact on performance.
setMaxRows(int) sets the limit of the maximum nuber of rows in a ResultSet object. If this limit is exceeded, the excess rows are "silently dropped". That's all the API says, so the setMaxRows method may not help performance at all other than to decrease memory usage. A value of 0 (default) means no limit.
Since we're talking about interfaces, be careful because the implementation of drivers is often different from database to database and, in some cases, may not be implemented or have a null implementation. Always refer to the driver documentation. 


Please click on any advertisement if you like this blog.


Q. What's the best way, in terms of performance, to do multiple insert/update statements, a PreparedStatement or Batch Updates?                                                                                                         Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.
A CallableStatement object provides a way to call stored procedures in a standard manner for all DBMSes. Their execution can be faster than that of PreparedStatement object.
Batch updates are used when you want to execute multiple statements together. Actually, there is no conflict here. While it depends on the driver/DBMS engine as to whether or not you will get an actual performance benefit from batch updates, Statement, PreparedStatement, and CallableStatement can all execute the addBatch() method. 

Q. What is the advantage of using a PreparedStatement?                                                                       For SQL statements that are executed repeatedly, using a PreparedStatement object would almost always be faster than using a Statement object. This is because creating a PreparedStatement object by explicitly giving the SQL statement causes the statement to be precompiled within the database immediately. Thus, when the PreparedStatement is later executed, the DBMS does not have to recompile the SQL statement and prepared an execution plan - it simply runs the statement.
Typically, PreparedStatement objects are used for SQL statements that take parameters. However, they can also be used with repeatedly executed SQL statements that do not accept parameters. 




Please click on any advertisement on right side if you like this blog.

No comments:

Post a Comment

Please provide your precious comments and suggestion