JDBC

1) What is JDBC?

JDBC may stand for Java Database Connectivity. It is also a trade mark. JDBC is a layer of abstraction that allows users to choose between databases. It allows you to change to a different database engine and to write to a single API. JDBC allows you to write database applications in Java without having to concern yourself with the underlying details of a particular database.
2) What are the two major components of JDBC?

One implementation interface for database manufacturers, the other implementation interface for application and applet writers.
3) What is JDBC Driver interface?

The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendors driver must provide implementations of the java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet and Driver.
4) 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
5) What packages are used by JDBC?

There are at least 8 packages: • java.sql.Driver • Connection • Statement • PreparedStatement • CallableStatement • ResultSet • ResultSetMetaData • DatabaseMetaData
6) What are the flow statements of JDBC?

A URL string -->getConnection-->DriverManager-->Driver-->Connection-->Statement-->executeQuery-->ResultSet.


7) What are the steps involved in establishing a connection?

This involves two steps: (1) loading the driver and (2) making the connection.

8) How many types of JDBC Drivers are present and what are they

There are 4 types of JDBC Drivers Type 1: JDBC-ODBC Bridge Driver Type 2: Native API Partly Java Driver Type 3: Network protocol Driver Type 4: JDBC Net pure Java Driver


9) 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: Eg. 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: Eg. Class.forName("jdbc.DriverXYZ");

10) What Class.forName will 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.

11) How can you make the connection?

In establishing a connection is to have the appropriate driver connect to the DBMS. The following line of code illustrates the general idea: Eg. String url = "jdbc:odbc:Fred"; Connection con = DriverManager.getConnection(url, "Fernanda", "J8");

12) How can you create JDBC statements?

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. Eg. 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 stmt : Statement stmt = con.createStatement();

13) How to make a query?

Create a Statement object and calls the Statement.executeQuery method to select data from the database. The results of the query are returned in a ResultSet object. Statement stmt = con.createStatement(); ResultSet results = stmt.executeQuery("SELECT data FROM aDatabase ");

14) How can you retrieve data from the ResultSet?

Step 1. 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. Eg. ResultSet rs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES"); Step2. 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

15) How to navigate the ResultSet?

By default the result set cursor points to the row before the first row of the result set. A call to next() retrieves the first result set row. The cursor can also be moved by calling one of the following ResultSet methods: • beforeFirst(): Default position. Puts cursor before the first row of the result set. • first(): Puts cursor on the first row of the result set. • last(): Puts cursor before the last row of the result set. • afterLast() Puts cursor beyond last row of the result set. Calls to previous moves backwards through the ResultSet. • absolute(pos): Puts cursor at the row number position where absolute(1) is the first row and absolute(-1) is the last row. • relative(pos): Puts cursor at a row relative to its current position where relative(1) moves row cursor one row forward.

16) How to update a ResultSet?

You can update a value in a result set by calling the ResultSet.update method on the row where the cursor is positioned. The type value here is the same used when retrieving a value from the result set, for example, updateString updates a String value and updateDouble updates a double value in the result set. rs.first(); updateDouble("balance", rs.getDouble("balance") - 5.00); The update applies only to the result set until the call to rs.updateRow(), which updates the underlying database. To delete the current row, use rs.deleteRow(). To insert a new row, use rs.moveToInsertRow().

17) What are the different types of Statements?

1.Statement (use createStatement method) 2. Prepared Statement (Use prepareStatement method) and 3. Callable Statement (Use prepareCall)

18) How can you use PreparedStatement?

This special type of statement is derived from the more general class, Statement.If you want to execute a Statement object many times, it will normally reduce execution time 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. Eg. PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

19) What setAutoCommit does?

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 Eg. con.setAutoCommit(false); Once auto-commit mode is disabled, no SQL statements will be committed until you call the method commit explicitly. Eg. 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);

20) How to call a Strored 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; Eg. CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery();

21) How to 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 Eg. SQLWarning warning = stmt.getWarnings(); if (warning != null) { System.out.println("\n---Warning---\n"); while (warning != null) { S2ystem.out.println("Message: " + warning.getMessage()); System.out.println("SQLState: " + warning.getSQLState()); System.out.print("Vendor error code: "); System.out.println(warning.getErrorCode()); System.out.println(""); warning = warning.getNextWarning(); } }

22) 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. Eg. 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

23) 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 Eg. Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES"); srs.afterLast(); while (srs.previous()) { String name = srs.getString("COF_NAME"); float price = srs.getFloat("PRICE"); System.out.println(name + " " + price); }

24) 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. Eg. Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName"); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

25) 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.

26) 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.

27) What is cold backup, hot backup, warm backup recovery?

a. cold backup - All these files must be backed up at the same time, before the databaseis restarted. b. 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.

28) 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.

29) 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.

30) 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

31) 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

32) 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.

33) 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

34) Why cannot java.util.Date match with java.sql.Date?

Because java.util.Date represents both date and time. SQL has three types to represent date and time. java.sql.Date -- (00/00/00) java.sql.Time -- (00:00:00) java.sql.Timestamp -- in nanoseconds Note that they are subclasses of java.util.Date.