- JDBC stands for Java Database Connectivity. It is an API which provides easy connection to a wide range of databases.
2) How do you connect to a database?
- To connect to a database we need to load the appropriate driver and then request for a connectionobject.
- The Class.forName(….) will load the driver and register it with the DriverManager.
- Class.forName(“oracle.jdbc.driver.OracleDriver”); //dynamic class loading
- String url = jdbc:oracle:thin:@hostname:1526:myDB;
- Connection myConnection = DriverManager.getConnection(url, “username”, “password”);
- The JDBC Driver provides vendor-specific implementations of the abstract classes provided by the JDBC API.
- This driver is used to connect to the database.
- Class.forName(“oracle.jdbc.driver.OracleDriver”);
- First we need to create an instance of a JDBC driver or load JDBC drivers, then we need to register this driver with DriverManager class.
- Class.forName(“oracle.jdbc.driver.OracleDriver”);
- Then we can open a connection.
- Connection con = DriverManager.getConnection( "jdbc:myDriver:wombat", "myLogin","myPassword");
- By using this connection , we can create a statement object and this object will help us to execute the query.
- Statement stmt = con.createStatement();
- ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
- DriverManager is a class in java.sql package.
- It defines objects, which can connect Java applications to a JDBC driver.
- A class as loaded by the classloader.
7) What is Connection?
- Connection class represents a connection (session) with a specific database.
- Connection con = DriverManager.getConnection( "jdbc:myDriver:wombat", "myLogin","myPassword");
- SQL statements are executed and results are returned within the context of a connection.
- Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on.
- This information is obtained with the getMetaData method.
- A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
- ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
- A ResultSet object maintains a cursor pointing to its current row of data.
- Initially the cursor is positioned before the first row.
- The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.
int x = rs.getInt("a");
String s = rs.getString("b");
float f = rs.getFloat("c");
}
9) What are JDBC Statements?
- A statement object is responsible for sending the SQL statements to the Database.
- Statement objects are created from the connection object and then executed.
- Statement stmt = myConnection.createStatement();
- ResultSet rs = stmt.executeQuery(“SELECT id, name FROM myTable where id =1245”);
- stmt.executeUpdate(“INSERT INTO (field1,field2) values (1,3)”);//to insert/update/delete/create
- The types of statements are:
- Statement (regular statement as shown above)
- PreparedStatement (more efficient than statement due to pre-compilation of SQL)
- PreparedStatement prepStmt = myConnection.prepareStatement("SELECT id, name FROM myTable where id = ? ");
- prepStmt.setInt(1, 1245);
- CallableStatement (to call stored procedures on the database)
- CallableStatement calStmt = myConnection.prepareCall("{call PROC_SHOWMYBOOKS}");
- ResultSet rs = cs.executeQuery();
11) What is a Transaction?
- A transaction is a set of operations that should be completed as a unit. If one operation fails then all the other operations fail as well.
- For example if you transfer funds between two accounts there will be two operations in the set1. Withdraw money from one account.2. Deposit money into other account.
- There are four characteristics (ACID properties) for a Transaction.Atomicity (All the individual operations should either complete or fail.)Consistency (The design of the transaction should update the database correctly.)Isolation (Prevents data being corrupted by concurrent access by two different sources. It keepstransactions isolated or separated from each other until they are finished.)Durability(Ensures that the database is definitely updated once the Transaction is completed.)
12) 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 immediately after it is executed.
- The wayto allow two or more statements to be grouped into a transaction is to disable auto-commit.
modetry{
Connection myConnection = dataSource.getConnection();// set autoCommit to false myConnection.setAutoCommit(false);
withdrawMoneyFromFirstAccount(.............); //operation 1
depositMoneyIntoSecondAccount(.............); //operation 2
myConnection .commit();
}
catch(Exception sqle){
try{
myConnection .rollback();
}catch( Exception e){}
}
finally{
try{
if( conn != null) {
conn.close();
}
} catch( Exception e) {}
}
The above code ensures that both operation 1 and operation 2 succeed or fail as an atomic unit and consequently leaves the database in a consistent state. Also turning auto-commit off will provide better performance.
13) What are stored procedures?
- A stored procedure is a set of statements/commands which reside in the database. The stored procedure is precompiled.
- Each Database has it's own stored procedure language.
- Connection pooling is a technique used for sharing server resources among requesting clients.
- Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request.
- Connection pool manager maintains a pool of open database connections.
15) Have you used a Data Access Object (i.e. DAO) pattern?
- The DataSource interface provides an alternative to the DriverManager for making a connection.
- DataSource makes the code more portable than DriverManager because it works with JNDI and it is created, deployed and managed separately from the application that uses it.
- If the DataSource location changes, then there is no need to change the code but change the configuration properties in the server. This makes your application code easier to maintain.
- DataSource allows the use of connection pooling and support for distributed transactions.
- A DataSource is not only a database but also can be a file or a spreadsheet.
- A DataSource object can be bound to JNDI and an application can retrieve and use it to make a connection to the database.
- J2EE application servers provide tools to define your DataSource with a JNDI name. When the server starts it loads all the DataSources into the application server’s JNDI service.
- DataSource configuration properties are shown below:JNDI Name -> jdbc/myDataSourceURL -> jdbc:oracle:thin:@hostname:1526:myDBUserName, PasswordImplementation classname ->oracle.jdbc.pool.OracleConnectionPoolDataSourceClasspath -> ora_jdbc.jarConnection pooling settings like ->minimum pool size, maximum pool size, connection timeout, statement cache size etc.
- Once the DataSource has been set up, then you can get the connection object as follows:
- Context ctx = new InitialContext();DataSource ds = (DataSource)ctx.lookup("jdbc/myDataSource");
- Connection myConnection = ds.getConnection(“username”,”password”);
- In a basic implementation a Connection obtained from a DataSource and a DriverManager are identical.
- But the J2EE best practice is to use DataSource because of its portability, better performance due topooling of valuable resources and the J2EE standard requires that applications use the container’s resource management facilities to obtain connections to resources.
- Every major web application container provides pooled database connection management as part of its resource management framework.
They are:
Type 1 : JDBC-ODBC Bridge Driver - A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important. For information on the JDBC-ODBC bridge driver provided by Sun.
Type 2: Native API Partly Java Driver- A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
Type 3: Network protocol Driver- A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.
Type 4: JDBC Net pure Java Driver - A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.
18) What is the fastest type of JDBC driver
- Type 4 (JDBC Net pure Java Driver) is the fastest JDBC driver. 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).
- No. The JDBC-ODBC Bridge does not support multi threading. 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.
20) What is cold backup, hot backup, warm backup recovery?
- Cold backup means all these files must be backed up at the same time, before the database is 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
- 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.
- Connection Object has a method called setAutocommit ( boolean flag) . For handling our own transaction we can set the parameter to false and begin your transaction . Finally commit the transaction by calling the commit method.
References:
- Java/J2EE Job Interview Companion by Arulkumaran.
- http://www.allapplabs.com/index.html
- http://www.java-interview.com/index.html
