tag:blogger.com,1999:blog-47612892115713668812024-02-08T04:17:15.203+05:30Mohan's JDBCBookJDBC Interview QuestionsUnknownnoreply@blogger.comBlogger55125tag:blogger.com,1999:blog-4761289211571366881.post-44185107088675210172013-07-23T14:24:00.001+05:302013-07-23T14:24:37.113+05:30Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?<div class=Section1> <p class=MsoNormal>No, we can open only one statement object when using JDBC-ODBC Bridge.<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-63537981688936425252013-07-23T14:23:00.002+05:302013-07-23T14:24:11.391+05:30What are the locking system in JDBC<div class=Section1> <p class=MsoNormal>One more tough JDBC question to understand and prepare. There are 2 types of locking in JDBC by which we can handle multiple user issue using the record. if two user are reading the same record then there is no issue but what if users are updating the record , in this case changes done by first user is gone by second user if he also update the same record .so we need some type of locking so no lost update.<o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal>Optimistic Locking: optimistic locking lock the record only when update take place. Optimistic locking does not use exclusive locks when reading<o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal>Pessimistic locking: in this record are locked as it selects the row to update<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-28942669419440657532013-07-23T14:23:00.001+05:302013-07-23T14:23:37.671+05:30What do you mean by cold backup, hot backup?<div class=Section1> <p class=MsoNormal>This question is not directly related to JDBC but some time asked during JDBC interviews. Cold back is the backup techniques in which backup of files are taken before the database restarted. In hot backup backup of files and table is taken at the same time when database is running. A warm is a recovery technique where all the tables are locked and users cannot access at the time of backing up data.<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-61875063462871708132013-07-23T14:22:00.004+05:302013-07-23T14:23:07.439+05:30What is connection pooling?<div class=Section1> <p class=MsoNormal>This is also one of the most popular question asked during JDBC Interviews. Connection pooling is the mechanism by which we reuse the recourse like connection objects which are needed to make connection with database .In this mechanism client are not required every time make new connection and then interact with database instead of that connection objects are stored in connection pool and client will get it from there. so it’s a best way to share a server resources among the client and enhance the application performance.<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-87014493164197287062013-07-23T14:22:00.003+05:302013-07-23T14:22:47.735+05:30How cursor works in scrollable result set?<div class=Section1> <p class=MsoNormal>Another tough JDBC Interview question, not many Java programmer knows about using Cursor in Java.<o:p></o:p></p> <p class=MsoNormal>in JDBC 2.0 API new feature is added to move cursor in resultset backward forward and also in a particular row .<o:p></o:p></p> <p class=MsoNormal>There are three constant define in result set by which we can move cursor.<o:p></o:p></p> <p class=MsoNormal>·TYPE_FORWARD_ONLY: creates a nonscrollable result set, that is, one in which the cursor moves only forward<o:p></o:p></p> <p class=MsoNormal>·TYPE_SCROLL_INSENSITIVE : a scrollable result set does not reflects changes that are made to it while it is open<o:p></o:p></p> <p class=MsoNormal>·TYPE_SCROLL_SENSITIVE: a scrollable result set reflects changes that are made to it while it is open<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-87197889575686706972013-07-23T14:22:00.001+05:302013-07-23T14:22:12.467+05:30What are different types of Statement?<div class=Section1> <p class=MsoNormal>This is another classical JDBC interview question. Variants are Difference between Statement, PreparedStatemetn and CallableStatement in Java. Statement object is used to send SQL query to database and get result from database, and we get statement object from connection object.<o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal>There are three types of statement:<o:p></o:p></p> <p class=MsoNormal>1. Statement: it’s a commonly used for getting data from database useful when we are using static SQL statement at runtime. it will not accept any parameter.<o:p></o:p></p> <p class=MsoNormal> Statement stmt = conn.createStatement( );<o:p></o:p></p> <p class=MsoNormal> ResultSet rs = stmt.executeQuery();<o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal>2. PreparedStatement: when we are using same SQL statement multiple time its is useful and it will accept parameter at runtime.<o:p></o:p></p> <p class=MsoNormal> <o:p></o:p></p> <p class=MsoNormal> String SQL = "Update stock SET limit = ? WHERE stockType = ?";<o:p></o:p></p> <p class=MsoNormal> PreparedStatement pstmt = conn.prepareStatement(SQL);<o:p></o:p></p> <p class=MsoNormal> ResultSet rs = pstmt.executeQuery();<o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal>3. Callable Statement: when we want to access stored procedures then callable statement are useful and they also accept runtime parameter. It is called like this<o:p></o:p></p> <p class=MsoNormal> <o:p></o:p></p> <p class=MsoNormal> CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); <o:p></o:p></p> <p class=MsoNormal> ResultSet rs = cs.executeQuery();<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-60537896633915277022013-07-23T14:21:00.001+05:302013-07-23T14:21:11.874+05:30What is 2 phase commit?<div class=Section1> <p class=MsoNormal>This is one of the most popular JDBC Interview question and asked at advanced level, mostly to senior Java developers on J2EE interviews. Two phase commit is used in distributed environment where multiple process take part in distributed transaction process. In simple word we can understand like if any transaction is executing and it will effect multiple database then two phase commit will be used to make all database synchronized with each other.<o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal>In two phase commit, commit or rollback is done by two phases:<o:p></o:p></p> <p class=MsoNormal>1.Commit request phase: in this phase main process or coordinator process take vote of all other process that they are complete their process successfully and ready to commit if all the votes are “yes” then they go ahead for next phase. And if “No “then rollback is performed.<o:p></o:p></p> <p class=MsoNormal>2.Commit phase: according to vote if all the votes are yes then commit is done.<o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal>Similarly when any transaction changes multiple database after execution of transaction it will issue pre commit command on each database and all database send acknowledgement and according to acknowledgement if all are positive transaction will issue the commit command otherwise rollback is done .<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-28486196068609648212013-07-23T14:20:00.003+05:302013-07-23T14:20:40.016+05:30What is the mean of "dirty read" in database?<div class=Section1> <p class=MsoNormal>Answer : This kind of JDBC interview question is asked on 2 to 4 years experience Java programmer, they are expected to familiar with database transaction and isolation level etc. As the name it self convey the meaning of dirty read “read the value which may or may not be correct”. in database when one transaction is executing and changing some field value same time some another transaction comes and read the change field value before first transaction commit or rollback the value ,which cause invalid value for that field, this scenario is known as dirty read.<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-64126655017588885142013-07-23T14:20:00.001+05:302013-07-23T14:20:21.034+05:30What are the main steps in java to make JDBC connectivity?<div class=Section1> <p class=MsoNormal>Another beginner level JDBC Interview question, mostly asked on telephonic interviews. Here are main steps to connect to database.<o:p></o:p></p> <p class=MsoNormal>·Load the Driver: First step is to load the database specific driver which communicates with database.<o:p></o:p></p> <p class=MsoNormal>·Make Connection: Next step is get connection from the database using connection object, which is used to send SQL statement also and get result back from the database.<o:p></o:p></p> <p class=MsoNormal>·Get Statement object: From connection object we can get statement object which is used to query the database<o:p></o:p></p> <p class=MsoNormal>·Execute the Query:Using statement object we execute the SQL or database query and get result set from the query.<o:p></o:p></p> <p class=MsoNormal>·Close the connection:After getting resultset and all required operation performed the last step should be closing the database connection.<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-7945412836707387412013-07-23T14:19:00.001+05:302013-07-23T14:19:55.052+05:30What is JDBC?<div class=Section1> <p class=MsoNormal>One of the first JDBC interview question in most of interviews. JDBC is java database connectivity as name implies it’s a java API for communicating to relational database, API has java classes and interfaces using that developer can easily interact with database. For this we need database specific JDBC drivers.<o:p></o:p></p> </div> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-90518121140463582952012-12-14T11:57:00.003+05:302012-12-14T11:57:34.457+05:30What is the JDBC?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Java Database Connectivity (JDBC) is a standard Java API to interact with relational databases form Java. JDBC has set of classes and interfaces which can use from Java application and talk to database without learning RDBMS details and using Database Specific JDBC Drivers.<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-56341848490037677862012-12-14T11:57:00.001+05:302012-12-14T11:57:06.142+05:30What are the new features added to JDBC 4.0?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
The major features added in JDBC 4.0 include :<br />
Auto-loading of JDBC driver class<br />
Connection management enhancements<br />
Support for RowId SQL type<br />
DataSet implementation of SQL using Annotations<br />
SQL exception handling enhancements<br />
SQL XML support<br />
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-425625895595244162012-12-14T11:56:00.001+05:302012-12-14T11:56:32.789+05:30Explain Basic Steps in writing a Java program using JDBC?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
JDBC makes the interaction with RDBMS simple and intuitive. When a Java application needs to access database :<br />
Load the RDBMS specific JDBC driver because this driver actually communicates with the database (Incase of JDBC 4.0 this is automatically loaded).<br />
Open the connection to database which is then used to send SQL statements and get results back.<br />
Create JDBC Statement object. This object contains SQL query.<br />
Execute statement which returns resultset(s). ResultSet contains the tuples of database table as a result of SQL query.<br />
Process the result set.<br />
Close the connection.<br />
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-46341744307570878452012-12-14T11:55:00.004+05:302012-12-14T11:55:39.458+05:30Exaplain the JDBC Architecture.<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
The JDBC Architecture consists of two layers:<br />
The JDBC API, which provides the application-to-JDBC Manager connection.<br />
The JDBC Driver API, which supports the JDBC Manager-to-Driver Connection.<br />
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. The location of the driver manager with respect to the JDBC drivers and the Java application is shown in Figure 1.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4A-zTHMlEIvfyZp3O5Ltf5pq_mZXfqzpKMI_PTjL6IKZFV_scq8hMZVbGpM_H8A337EYjP03BdIzdGXXFspZyMH8uDPqNLsqd9R8IO8Z3TdI7EyGMXE2Od-itjg43JtntFXb4ZUNJjFhj/s1600/JDBC-Architecture.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="262" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4A-zTHMlEIvfyZp3O5Ltf5pq_mZXfqzpKMI_PTjL6IKZFV_scq8hMZVbGpM_H8A337EYjP03BdIzdGXXFspZyMH8uDPqNLsqd9R8IO8Z3TdI7EyGMXE2Od-itjg43JtntFXb4ZUNJjFhj/s320/JDBC-Architecture.gif" width="320" /></a></div>
<div>
<br /></div>
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-29486585886525622152012-12-14T11:54:00.001+05:302012-12-14T11:54:21.159+05:30What are the main components of JDBC ?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
The life cycle of a servlet consists of the following phases:<br />
DriverManager: 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.<br />
<br />
Driver: The database communications link, handling all communication with the database. Normally, once the driver is loaded, the developer need not call it explicitly.<br />
<br />
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.<br />
<br />
Statement : Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.<br />
<br />
ResultSet: The ResultSet represents set of rows retrieved due to query execution.<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-28435294970543425702012-12-14T11:53:00.002+05:302012-12-14T11:53:28.067+05:30How the JDBC application works?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
A JDBC application can be logically divided into two layers:<br />
1. Driver layer<br />
2. Application layer<br />
Driver layer consists of DriverManager class and the available JDBC drivers.<br />
The application begins with requesting the DriverManager for the connection.<br />
An appropriate driver is choosen and is used for establishing the connection. This connection is given to the application which falls under the application layer.<br />
The application uses this connection to create Statement kind of objects, through which SQL commands are sent to backend and obtain the results.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXrFvxRVjgf1bZU4tmAtmwMDQ6DFhW8pwCzCQaaBVSGbq-OJwsc5E-r56Cebzp911vKiPWV2CaLRq0_Jp0lGvSyqPcfhnDRAvz5kdamivIwIZYtnxfH96gqt7wWMg5r4Vwa5fXrOm5N9Ev/s1600/JDBC-Application.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="108" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXrFvxRVjgf1bZU4tmAtmwMDQ6DFhW8pwCzCQaaBVSGbq-OJwsc5E-r56Cebzp911vKiPWV2CaLRq0_Jp0lGvSyqPcfhnDRAvz5kdamivIwIZYtnxfH96gqt7wWMg5r4Vwa5fXrOm5N9Ev/s320/JDBC-Application.gif" width="320" /></a></div>
<div>
<br /></div>
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-35257127286921999362012-12-14T11:52:00.001+05:302012-12-14T11:52:17.930+05:30How do I load a database driver with JDBC 4.0 / Java 6?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Provided the JAR file containing the driver is properly configured, just place the JAR file in the classpath. Java developers NO longer need to explicitly load JDBC drivers using code like Class.forName() to register a JDBC driver.The DriverManager class takes care of this by automatically locating a suitable driver when the DriverManager.getConnection() method is called. This feature is backward-compatible, so no changes are needed to the existing JDBC code.<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-70666617752581669272012-12-14T11:51:00.003+05:302012-12-14T11:51:48.279+05:30What is JDBC Driver interface?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendor driver must provide implementations of the java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet and Driver.<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-75912798267254284472012-12-14T11:51:00.001+05:302012-12-14T11:51:04.820+05:30What does the connection object represents?<div dir="ltr" style="text-align: left;" trbidi="on">
The connection object represents communication context, i.e., all communication with database is through connection object only.</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-22181575540720273762012-12-14T11:49:00.003+05:302012-12-14T11:49:59.141+05:30What is Statement ?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Statement acts like a vehicle through which SQL commands can be sent. Through the connection object we create statement kind of objects.<br />
Through the connection object we create statement kind of objects.<br />
Statement stmt = conn.createStatement();<br />
This method returns object which implements statement interface.<br />
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-36365674180504183682012-12-14T11:49:00.001+05:302012-12-14T11:49:27.839+05:30What is PreparedStatement?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
A prepared statement is an SQL statement that is precompiled by the database. Through precompilation, prepared statements improve the performance of SQL commands that are executed multiple times (given that the database supports prepared statements). Once compiled, prepared statements can be customized prior to each execution by altering predefined SQL parameters.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>PreparedStatement pstmt = conn.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>pstmt.setBigDecimal(1, 153833.00);<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>pstmt.setInt(2, 110592);<br />
Here: conn is an instance of the Connection class and "?" represents parameters.These parameters must be specified before execution.<br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-30139560537679687882012-12-14T11:48:00.001+05:302012-12-14T11:48:19.022+05:30What is the difference between a Statement and a PreparedStatement?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
A standard Statement is used to create a Java representation of a literal SQL statement and execute it on the database.<br />
<br />
A PreparedStatement is a precompiled statement. This means that when the PreparedStatement is executed, the RDBMS can just run the PreparedStatement SQL statement without having to compile it first.<br />
<br />
Statement has to verify its metadata against the database every time.<span class="Apple-tab-span" style="white-space: pre;"> </span>While a prepared statement has to verify its metadata against the database only once.<br />
<br />
If you want to execute the SQL statement once go for STATEMENT<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
<br />
If you want to execute a single SQL statement multiple number of times, then go for PREPAREDSTATEMENT. PreparedStatement objects can be reused with passing different values to the queries<br />
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-72652827085937171482012-12-14T11:47:00.001+05:302012-12-14T11:47:01.247+05:30What are callable statements ?<div dir="ltr" style="text-align: left;" trbidi="on">
Callable statements are used from JDBC application to invoke stored procedures and functions.</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-91505317373888565112012-12-14T11:46:00.003+05:302012-12-14T11:46:17.060+05:30How to call a stored procedure from JDBC ?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
PL/SQL stored procedures are called from within JDBC programs by means of the prepareCall() method of the Connection object created. A call to this method takes variable bind parameters as input parameters as well as output variables and creates an object instance of the CallableStatement class.<br />
The following line of code illustrates this:<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>CallableStatement stproc_stmt = conn.prepareCall("{call procname(?,?,?)}");<br />
Here conn is an instance of the Connection class.<br />
</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-4761289211571366881.post-46595096123969588712012-12-14T11:45:00.001+05:302012-12-14T11:45:20.257+05:30What are types of JDBC drivers?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
There are four types of drivers defined by JDBC as follows:<br />
Type 1: JDBC/ODBC—These require an ODBC (Open Database Connectivity) driver for the database to be installed. This type of driver works by translating the submitted queries into equivalent ODBC queries and forwards them via native API calls directly to the ODBC driver. It provides no host redirection capability.<br />
<br />
Type2: Native API (partly-Java driver)—This type of driver uses a vendor-specific driver or database API to interact with the database. An example of such an API is Oracle OCI (Oracle Call Interface). It also provides no host redirection.<br />
<br />
Type 3: Open Protocol-Net—This is not vendor specific and works by forwarding database requests to a remote database source using a net server component. How the net server component accesses the database is transparent to the client. The client driver communicates with the net server using a database-independent protocol and the net server translates this protocol into database calls. This type of driver can access any database.<br />
<br />
Type 4: Proprietary Protocol-Net(pure Java driver)—This has a same configuration as a type 3 driver but uses a wire protocol specific to a particular vendor and hence can access only that vendor's database. Again this is all transparent to the client.<br />
Note: Type 4 JDBC driver is most preferred kind of approach in JDBC.<br />
</div>
Unknownnoreply@blogger.com