JDBC FAQs

 

JDBC Interview Questions

1. What are the steps involved in establishing a JDBC connection? This action involves two steps: loading the JDBC driver and making the connection.
2. How can you load the drivers?
   Loading the driver or drivers you want to use is very simple and involves just one line of code. If, for example, you want to use the JDBC-ODBC Bridge driver, the following code will load it:

Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);

Your driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverXYZ, you would load the driver with the following line of code:

Class.forName(”jdbc.DriverXYZ”);

1. What will Class.forName do while loading drivers? It is used to create an instance of a driver and register it with the
   DriverManager. When you have loaded a driver, it is available for making a connection with a DBMS.
2. How can you make the connection? To establish a connection you need to have the appropriate driver connect to the DBMS.
   The following line of code illustrates the general idea:

String url = “jdbc:odbc:Fred”;
Connection con = DriverManager.getConnection(url, “Fernanda”, “J8?);

1. How can you create JDBC statements and what are they?
   A Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery. For statements that create or modify tables, the method to use is executeUpdate. It takes an instance of an active connection to create a Statement object. In the following example, we use our Connection object con to create the Statement object

Statement stmt = con.createStatement();

1. How can you retrieve data from the ResultSet?
   JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs.

ResultSet rs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);
String s = rs.getString(”COF_NAME”);

The method getString is invoked on the ResultSet object rs, so getString() will retrieve (get) the value stored in the column COF_NAME in the current row of rs.

1. What are the different types of Statements?
   Regular statement (use createStatement method), prepared statement (use prepareStatement method) and callable statement (use prepareCall)
2. How can you use PreparedStatement? This special type of statement is derived from class Statement.If you need a
   Statement object to execute many times, it will normally make sense to use a PreparedStatement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement’s SQL statement without having to compile it first.

9.           PreparedStatement updateSales =

10.                con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

1. What does setAutoCommit do?
   When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode:

con.setAutoCommit(false);

Once auto-commit mode is disabled, no SQL statements will be committed until you call the method commit explicitly.

con.setAutoCommit(false);

PreparedStatement updateSales =

  con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

updateSales.setInt(1, 50); updateSales.setString(2, "Colombian");

updateSales.executeUpdate();

PreparedStatement updateTotal =

  con.prepareStatement("UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?");

updateTotal.setInt(1, 50);

updateTotal.setString(2, "Colombian");

updateTotal.executeUpdate();

con.commit();

con.setAutoCommit(true);

1. How do you call a stored procedure from JDBC?
   The first step is to create a CallableStatement object. As with Statement an and PreparedStatement objects, this is done with an open
   Connection object. A CallableStatement object contains a call to a stored procedure.

13.                CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");

14.                ResultSet rs = cs.executeQuery();

1. How do I retrieve warnings?
   SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an
   application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a
   Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these
   classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object:

16.      SQLWarning warning = stmt.getWarnings();

17.      if (warning != null)

18.      {

19.                System.out.println("n---Warning---n");

20.                while (warning != null)

21.                {

22.                          System.out.println("Message: " + warning.getMessage());

23.                          System.out.println("SQLState: " + warning.getSQLState());

24.                          System.out.print("Vendor error code: ");

25.                          System.out.println(warning.getErrorCode());

26.                          System.out.println("");

27.                          warning = warning.getNextWarning();

28.                }

29.      }

1. How can you move the cursor in scrollable result sets?
   One of the new features in the JDBC 2.0 API is the ability to move a result set’s cursor backward as well as forward. There are also methods that let you move the cursor to a particular row and check the position of the cursor.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);

The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE. The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int , the compiler will not complain if you switch the order. Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

1. What’s the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE?
   You will get a scrollable ResultSet object if you specify one of these ResultSet constants.The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes. Generally speaking, a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened:

32.      Statement stmt =

33.                con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

34.      ResultSet srs =

35.                stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

36.      srs.afterLast();

37.      while (srs.previous())

38.      {

39.                String name = srs.getString("COF_NAME");

40.                float price = srs.getFloat("PRICE");

41.                System.out.println(name + " " + price);

42.      }

1. How to Make Updates to Updatable Result Sets?
   Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.

44.      Connection con =

45.                DriverManager.getConnection("jdbc:mySubprotocol:mySubName");

46.      Statement stmt =

47.                con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

48.      ResultSet uprs =

 

49.                stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

 

 

JDBC & JSP Interview Questions

1. What is the query used to display all tables names in SQL Server (Query analyzer)?

2.                     select * from information_schema.tables

1. How many types of JDBC Drivers are present and what are they?- There are 4 types of JDBC Drivers
   • JDBC-ODBC Bridge Driver
   • Native API Partly Java Driver
   • Network protocol Driver
   • JDBC Net pure Java Driver
2. Can we implement an interface in a JSP?- No
3. What is the difference between ServletContext and PageContext?- ServletContext: Gives the information about the container. PageContext: Gives the information about the Request
4. What is the difference in using request.getRequestDispatcher() and context.getRequestDispatcher()?- request.getRequestDispatcher(path): In order to create it we need to give the relative path of the resource, context.getRequestDispatcher(path): In order to create it we need to give the absolute path of the resource.
5. How to pass information from JSP to included JSP?- Using <%jsp:param> tag.
6. What is the difference between directive include and jsp include?- <%@ include>: Used to include static resources during translation time. JSP include: Used to include dynamic content or static content during runtime.
7. What is the difference between RequestDispatcher and sendRedirect?- RequestDispatcher: server-side redirect with request and response objects. sendRedirect : Client-side redirect with new request and response objects.
8. How does JSP handle runtime exceptions?- Using errorPage attribute of page directive and also we need to specify isErrorPage=true if the current page is intended to URL redirecting of a JSP.
9. How do you delete a Cookie within a JSP?

12.                Cookie mycook = new Cookie("name","value");

13.                response.addCookie(mycook);

14.                Cookie killmycook = new Cookie("mycook","value");

15.                killmycook.setMaxAge(0);

16.                killmycook.setPath("/");

17.                killmycook.addCookie(killmycook);

1. How do I mix JSP and SSI #include?- If you’re just including raw HTML, use the #include directive as usual inside your .jsp file.

19.                <!--#include file="data.inc"-->

But it’s a little trickier if you want the server to evaluate any JSP code that’s inside the included file. If your data.inc file contains jsp code you will have to use

  <%@ vinclude="data.inc" %>

The <!–#include file=”data.inc”–> is used for including non-JSP files.

1. I made my class Cloneable but I still get Can’t access protected method clone. Why?- Some of the Java books imply that all you have to do in order to have your class support clone() is implement the Cloneable interface. Not so. Perhaps that was the intent at some point, but that’s not the way it works currently. As it stands, you have to implement your own public clone() method, even if it doesn’t do anything special and just calls super.clone().
2. Why is XML such an important development?- It removes two constraints which were holding back Web developments: dependence on a single, inflexible document type (HTML) which was being much abused for tasks it was never designed for; the complexity of full SGML, whose syntax allows many powerful but hard-to-program options. XML allows the flexible development of user-defined document types. It provides a robust, non-proprietary, persistent, and verifiable file format for the storage and transmission of text and data both on and off the Web; and it removes the more complex options of SGML, making it easier to program for.
3. What is the fastest type of JDBC driver?- JDBC driver performance will depend on a number of issues:
   • the quality of the driver code,
   • the size of the driver code,
   • the database server and its load,
   • network topology,
   • the number of times your request is translated to a different API.

In general, all things being equal, you can assume that the more your request and response change hands, the slower it will be. This means that Type 1 and Type 3 drivers will be slower than Type 2 drivers (the database calls are make at least three translations versus two), and Type 4 drivers are the fastest (only one translation).

1. How do I find whether a parameter exists in the request object?

24.      boolean hasFoo = !(request.getParameter("foo") == null

25.                || request.getParameter("foo").equals(""));

or

boolean hasParameter =

  request.getParameterMap().contains(theParameter); //(which works in Servlet 2.3+)

1. How can I send user authentication information while makingURLConnection?- You’ll want to use HttpURLConnection.setRequestProperty and set all the appropriate headers to HTTP authorization.

Java On Oracle Interview Questions

 What is JServer and what is it used for? Oracle JServer Option is a Java Virtual Machine (Java VM) which runs within the Oracle database server’s address space. Oracle also provides a JServer Accelerator to compile Java code natively. This speeds up the execution of Java code by eliminating interpreter overhead.

 How does one install the Oracle JServer Option?Follow these steps to activate the Oracle JServer/ JVM option:

1. Make sure your database is started with large java_pool_size (>20M) and shared_pool_size (>50M) INIT.ORA parameter values.
2. Run the $ORACLE_HOME/javavm/install/initjvm.sql script from SYS AS SYSDBA to install the Oracle JServer Option on a database.
3. Grant JAVAUSERPRIV to users that wants to use Java:
   SQL> GRANT JAVAUSERPRIV TO SCOTT;
4. The rmjvm.sql script can be used to deinstall the JServer option from your database.
5. Follow the steps in the Oracle Migrations Guide to upgrade or downgrade the JServer option from one release to
   another.

 ce code into the database? Use the “CREATE OR REPLACE JAVA SOURCE” command or “loadjava” utility. Loaded code can be viewed by selecting from the USER_SOURCE view.

 Why does one need to publish Java in the database? Publishing Java classes on the database makes it visible on a SQL and PL/SQL level. It is important to publish your code before calling it from SQL statements or PL/SQL code.

 What is JDBC and what is it used for? JDBC is a set of classes and interfaces written in Java to allow other Java programs to send SQL statements to a relational database management system. Oracle provides three categories of JDBC drivers: (a) JDBC Thin Driver (No local Net8 installation required/ handy for applets), (b) JDBC OCI for writing stand-alone Java applications, (c) JDBC KPRB driver (default connection) for Java Stored Procedures and Database JSP’s.

 How does one connect with the JDBC Thin Driver?
The the JDBC thin driver provides the only way to access Oracle from the Web (applets). It is smaller and faster than the OCI drivers, and doesn’t require a pre-installed version of the JDBC drivers.

import java.sql.*;class dbAccess { public static void main (String args []) throws SQLException { DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection (“jdbc:oracle:thin:@hostname:1526:orcl”, “scott”, “tiger”); // @machineName:port:SID, userid, password Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(“select BANNER from SYS.V_$VERSION”); while (rset.next()) System.out.println (rset.getString(1)); // Print col 1 stmt.close(); }}

 How does one connect with the JDBC OCI Driver? One must have Net8 (SQL*Net) installed and working before attempting to use one of the OCI drivers.

import java.sql.*;class dbAccess { public static void main (String args []) throws SQLException { try { Class.forName (“oracle.jdbc.driver.OracleDriver”); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection conn = DriverManager.getConnection (“jdbc:oracle:oci8:@hostname_orcl”, “scott”, “tiger”); // or oci7 @TNSNames_Entry, userid, password Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(“select BANNER from SYS.V_$VERSION”); while (rset.next()) System.out.println (rset.getString(1)); // Print col 1 stmt.close(); }}

 How does one connect with the JDBC KPRB Driver? One can obtain a handle to the default or current connection (KPRB driver) by calling the OracleDriver.defaultConenction() method. Please note that you do not need to specify a database URL, username or password as you are already connected to a database session. Remember not to close the default connection. Closing the default connection might throw an exception in future releases of Oracle.

import java.sql.*;class dbAccess { public static void main (String args []) throws SQLException { Connection conn = (new oracle.jdbc.driver.OracleDriver()).defaultConnection(); Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(“select BANNER from SYS.V_$VERSION”); while (rset.next()) System.out.println (rset.getString(1)); // Print col 1 stmt.close(); }}

 What is SQLJ and what is it used for? SQLJ is an ANSI standard way of coding SQL access in Java. It provides a Java precompiler that translates SQLJ call to JDBC calls. The idea is similar to that of other Oracle Precompilers.

 How does one deploy SQLJ programs? Use the sqlj compiler to compile your *.sqlj files to *.java and *.ser files. The *.ser files contain vendor specific database code. Thereafter one invokes the javac compiler to compile the .java files to *.class files. The *.class and *.ser files needs to be deployed.

 What is JDeveloper and what is it used for? JDeveloper is the Oracle IDE (Integrated Development Environment) for developing SQLJ and JDBC programs, applets, stored procedures, EJB’s, JSP’s etc.

 What is InfoBus DAC and what is it used for? InfoBus DAC (Data Aware Controls) is a standard Java extension used in JDeveloper to create data aware forms. It replaced the JBCL interface that were used in JDeveloper V1 and V2.

 What is a JSP and what is it used for? Java Server Pages (JSP) is a platform independent presentation layer technology that comes with SUN’s J2EE platform. JSPs are normal HTML pages with Java code pieces embedded in them. JSP pages are saved to *.jsp files. A JSP compiler is used in the background to generate a Servlet from the JSP page.

 What is the difference between ASP and JSP? Active Server Pages (ASP) is a Microsoft standard, which is easier to develop than Java Server Pages (JSP). However ASP is a proprietary technology and is less flexible than JSP. For more information about ASP, see the Oracle ASP FAQ.

 How does one invoke a JSP? A JSP gets invoked when you call a *.jsp file from your Web Server like you would call a normal *.html file. Obviously your web server need to support JSP pages and must be configured properly to handle them.

 How does a JSP gets executed? The first time you call a JSP, a servlet (*.java) will be created and compiled to a .class file. The class file is then executed on the server. Output produced by the servlet is returned to the web browser. Output will typically be HTML or XML code.

 What is a Java Stored Procedure/ Trigger? A Java Stored Procedure is a procedure coded in Java (as opposed to PL/SQL) and stored in the Oracle database. Java Stored procedures are executed by the database JVM in database memory space. Java Stored Procedures can be developed in JDBC or SQLJ. Interfacing between PL/SQL and Java are extremely easy. Please note that Java Stored procedures are by default executed with invokers rights. PL/SQL procedures are by default executed with defines rights.

 

Java database Interview Questions

1. How do you call a Stored Procedure from JDBC? - The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure.

2. CallableStatement cs =3. con.prepareCall(“{call SHOW_SUPPLIERS}”);4. ResultSet rs = cs.executeQuery();

1. 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. Multi-threaded Java programs may use the Bridge, but they won’t get the advantages of multi-threading.
2. 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.
3. What is cold backup, hot backup, warm backup recovery? - Cold backup (All these files must be backed up at the same time, before the databaseis restarted). Hot backup (official name is ‘online backup’) is a backup taken of each tablespace while the database is running and is being accessed by the users.
4. When we will Denormalize data? - Data denormalization is reverse procedure, carried out purely for reasons of improving performance. It maybe efficient for a high-throughput system to replicate data for certain data.
5. What is the advantage of using PreparedStatement? - If we are using PreparedStatement the execution time will be less. The PreparedStatement object contains not just an SQL statement, but the SQL statement that has been precompiled. This means that when the PreparedStatement is executed,the RDBMS can just run the PreparedStatement’s Sql statement without having to compile it first.
6. What is a “dirty read”? - Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value. While you can easily command a database to disallow dirty reads, this usually degrades the performance of your application due to the increased locking overhead. Disallowing dirty reads also leads to decreased system concurrency.
7. What is Metadata and why should I use it? - Metadata (’data about data’) is information about one of two things: Database information (java.sql.DatabaseMetaData), or Information about a specific ResultSet (java.sql.ResultSetMetaData). Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns
8. Different types of Transaction Isolation Levels? - The isolation level describes the degree to which the data being updated is visible to other transactions. This is important when two transactions are trying to read the same row of a table. Imagine two transactions: A and B. Here three types of inconsistencies can occur:
   • Dirty-read: A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong if A rolls back his changes and updates his own changes to the database.
   • Non-repeatable read: B performs a read, but A modifies or deletes that data later. If B reads the same row again, he will get different data.
   • Phantoms: A does a query on a set of rows to perform an operation. B modifies the table such that a query of A would have given a different result. The table may be inconsistent.

TRANSACTION_READ_UNCOMMITTED : DIRTY READS, NON-REPEATABLE READ AND PHANTOMS CAN OCCUR.
TRANSACTION_READ_COMMITTED : DIRTY READS ARE PREVENTED, NON-REPEATABLE READ AND PHANTOMS CAN OCCUR.
TRANSACTION_REPEATABLE_READ : DIRTY READS , NON-REPEATABLE READ ARE PREVENTED AND PHANTOMS CAN OCCUR.
TRANSACTION_SERIALIZABLE : DIRTY READS, NON-REPEATABLE READ AND PHANTOMS ARE PREVENTED.

1. What is 2 phase commit? - A 2-phase commit is an algorithm used to ensure the integrity of a committing transaction. In Phase 1, the transaction coordinator contacts potential participants in the transaction. The participants all agree to make the results of the transaction permanent but do not do so immediately. The participants log information to disk to ensure they can complete In phase 2 f all the participants agree to commit, the coordinator logs that agreement and the outcome is decided. The recording of this agreement in the log ends in Phase 2, the coordinator informs each participant of the decision, and they permanently update their resources.
2. How do you handle your own transaction ? - Connection Object has a method called setAutocommit(Boolean istrue)
   - Default is true. Set the Parameter to false , and begin your transaction
3. What is the normal procedure followed by a java client to access the db.? - The database connection is created in 3 steps:

1. Find a proper database URL

2. Load the database driver

3. Ask the Java DriverManager class to open a connection to your database

In java code, the steps are realized in code as follows:

4. Create a properly formatted JDBR URL for your database. (See FAQ on JDBC URL for more information). A JDBC URL has the form
jdbc:someSubProtocol://myDatabaseServer/theDatabaseName

5. Class.forName(”my.database.driver”);

6. Connection conn = DriverManager.getConnection(”a.JDBC.URL”, “databaseLogin”,”databasePassword”);

1. What is a data source? - A DataSource class brings another level of abstraction than directly using a connection object. Data source can be referenced by JNDI. Data Source may point to RDBMS, file System , any DBMS etc.
2. What are collection pools? What are the advantages? - A connection pool is a cache of database connections that is maintained in memory, so that the connections may be reused
3. How do you get Column names only for a table (SQL Server)? Write the Query. -

19. select name from syscolumns20. where id=(select id from sysobjects where name=’user_hdr’)

21. order by colid --user_hdr is the table name