Showing posts with label JDBC Questions. Show all posts
Showing posts with label JDBC Questions. Show all posts

Saturday, September 5, 2009

JDBC interview Questions

What is 2 phase commit?

Distributed transactions are performed by relational databases. To ensure each and every transaction to perform in an orderly manner, a process is implemented. This process is called Two Phase Commit. In this process, the transactions termination occurs either by committing or rolling back them.

Also read

Java Threading

What is a thread? What are the advantages we derived by programming with thread?
Explain how to create a thread and start it running.
How does thread’s stop method work.
How do we specify pause times in my program?...........

JNDI, Java naming and directory interfaces

What is a naming service?
What is a directory service?
What is LDAP?............

OFBiz Service Engine

Defining and creating a Java service 
Service parameters 
Special unchecked (unmatched) IN/OUT parameters 
Security-related programming 
Calling services from code (using dispatcher) 
IN/OUT parameter mismatch when calling services 
Sending feedback; standard return codes success, error and fail 
Implementing Service Interfaces 
Synchronous and asynchronous services 
Using the Service Engine tools 
ECAs: Event Condition Actions

ONLY QUESTIONS

What is JDBC? 

Difference between Type-2 and Type-3 driver 

How can you make the connection? 

What is a transaction ? 

How to get the resultset of stroedProcedure ? 

How can you create JDBC statements? 

What is the difference between JDBC 1.0 and JDBC 2.0 ? 

What is the purpose of setAutoCommit( ) ? 

How can store images in a data base? 

How can you retrieve data from the ResultSet ? 

What are the two major components of JDBC ? 

What are the three statements in JDBC & differences between them ? 

Why do we use prepared statement when already statement is present ? 

What are the different types of Statements? 

What is JDBC Driver interface ? 

What is stored procedure. How do you create stored procedure ? 

How can you use PreparedStatement ? 

What are the common tasks of JDBC ? 

What are batch updates ? 

How to call a Stored Procedure from JDBC ? 

What packages are used by JDBC ? 

What is the difference between Resultset and Rowset ? 
How to Retrieve Warnings ? 

What are the flow statements of JDBC ? 

What are the steps for connecting to the database using JDBC ? 

How to Make Updates to Updatable Result Sets ? 

What are the steps involved in establishing a connection ? 

What is Connection Pooling ? 

"select * from user" .what are the steps to take for execution of query in JDBC ? 

How can you load the drivers ? 

How do you implement Connection Pooling ? 

What Class.forName will do while loading drivers ? 

What Class.forName( ) method will do ? 

1

2

3

4

5

6

7

8

 

JDBC interview Questions

What is PreparedStatement?

Prepared Statement is called as “pre compiled statement”. An SQL statement is compiled before hand and stored in the PreparedStatement object. The purpose of precompiled statement is that the statement can be executed repeatedly. The required parameter values are to be substituted from time to time.

The following example illustrates the PreparedStatement object usage.

PreparedStatement pStmt = con.prepareStatement("select * from ? where balance>?");

The first ? represents a table name and the second ? represents any numeric attribute. Now the values can be set as follows:

pStmt.setString(1,"emp"); // emp is the table name and substitutes first ?
pStmt.setInt(2,29809); // 29809 is a value for balance field of the database
pStmt.execute(); // executes select statement.

Now the same select statement can be used for another table say “BankAccounts”
The following statements are the examples which use the same prepared statement with another table and attribute value

pStmt.setString(1,”BankAccounts”); // BankAccounts is the table name for first  ?
pStmt.setInt(2,600000); // 600000 is the value for the second ?
pStmt.execute(); // executes the select statement

Thus a “single select” statement for multiple tables is used. As the PreparedStatement is precompiled, it is used for several select statements instead of every time execution.

JDBC Interview - Feb 27, 2009, 22:30 pm by Vidya Sagar

How do you call a Stored Procedure from JDBC?

A stored procedure is invoked by using CallableStatement object. It also requires an open Connection object, as in the case of Statement and Prepared Statement objects. This object contains the call to the stored procedure, but not the procedure itself. The following code snippet illustrates the invocation of a stored procedure using CallableStatement. The part which is enclosed in curly braces is the syntax for stored procedures.

CallableStatement clblStmt = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rltSet = clblStmt.executeQuery();

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

Cold backup is a recovery technique, in which the files must be backed up before the database is restarted at the same time.

Hot backup is a recovery technique for each table space and is taken at the time of accessing and running the database.

Warm back is a recovery technique where all the tables are locked and users cannot access at the time of backing up data. The backup operations can be postponed in case the users are accessing the database.

What is the advantage of using PreparedStatement?

PreparedStatement objects are used to execute repetitive SQL statements. Compared to Statement object execution, Prepared Statement object creation is faster. The reason is the object is pre compiled, by eliminating the compilation task by DBMS. The PreparedStatement object can be used by just replacing the parameters.

What is a “dirty read”?

In typical database transactions, one transaction reads changes the value while the other reads the value before committing or rolling back by the first transaction. This reading process is called as ‘dirty read’. Because there is always a chance that the first transaction might rollback the change which causes the second transaction reads an invalid value.

Different types of Transaction Isolation Levels.

Transaction Isolation levels specifies the visible data to statements within a single transaction. These levels directly impact the various levels of the concurrent access.

Transaction isolation levels

There are five levels of transaction isolation which are as follows:

JDBC_TRANSACTION_NONE: A special constant that JDBC driver does not support transactions.

JDBC_TRANSACTION_READ_UNCOMMITTED: Allows looking after the uncommitted changes to the database.

JDBC_TRANSACTION_READ_COMMITTED: The transactions are externally invisible until the transaction is committed. The dirty reads prevention is possible with this level.

JDBC_TRANSACTION_REPEATABLE_READ: The readable rows retain locks by which another transaction can not change them until the transaction completes. It disallows dirty reads and non repeatable reads.

JDBC_TRANSACTION_SERIALIZABLE: The tables are locked for the transaction which makes the WHERE conditions not to be changed by other transactions. This process prevents all database anomalies.

To change the transaction levels, the setTransactionIsolation() method is utilized.

1

2

3

4

5

6

7

8

 

JDBC interview Questions

What is rowset?

A RowSet is an object that encapsulates a set of rows from either Java Database Connectivity (JDBC) result sets or tabular data sources like a file or spreadsheet. RowSets support component-based development models like JavaBeans, with a standard set of properties and an event notification mechanism.

 

What is the need of BatchUpdates?

The BatchUpdates feature allows us to group SQL statements together and send to database server in one single trip.


What is a DataSource?

A DataSource object is the representation of a data source in the Java programming language. In basic terms,

  • A DataSource is a facility for storing data.
  • DataSource can be referenced by JNDI.
  • Data Source may point to RDBMS, file System , any DBMS etc..

What are the advantages of DataSource?

The few advantages of data source are :

  • An application does not need to hardcode driver information, as it does with the DriverManager.
  • The DataSource implementations can easily change the properties of data sources. For example: There is no need to modify the application code when making changes to the database details.
  • The DataSource facility allows developers to implement a DataSource class to take advantage of features like connection pooling and distributed transactions.


What is connection pooling? what is the main advantage of using connection pooling?

A connection pool is a mechanism to reuse connections created. Connection pooling can increase performance dramatically by reusing connections rather than creating a new physical connection each time a connection is requested..

 

Explain the Java packages which contains JDBC classes and interfaces, Java.SQL, Javax.SQL

java.sql:

java.sql is an API to access and process the data stored in a database, typically a relational database using the java. Different drivers can be installed dynamically for the access of various databases, using a framework which in-built in this JDBC API.

javax.sql:

javax.sql is a JDBC API for the server side for accessing and processing the data from the databases typically a relational database using java. It is the essential part for J2EE. This API provides the facilities such as connection pooling, distributed transactions and row sets for the enterprise applications. An interface by name DataSource is provided in this API as an alternative to DriverManager to establish the connection.

DataSource and RowSet usage is direct, for the applications, where as connection pooling, distributed transactions are implemented by an infrastructure called middle-tier.

Describe how to open a database connection using JDBC.

Opening a database connection:

The database connection should be established after registering the drivers. The getConnection is used to open a database connection. The following code snippet illustrates this:

Connection con;
con = DriverManager.getConnection(url,"scott","tiger");// returns the connection object

where url specifies the database server port, ip address,domain name etc. For ex:
jdbc:oracle:thin:@192.137.63.230:1521:MyCompany

the ip address is an imaginary one and not intended to specify any specific system. The “scott” and “tiger” are the user name and passwords respectively.

Describe with an example how to send SQL statements to databases for execution.

The following illustrates with examples, to send SQL statements to databases for execution:

  • The SQL statements are to be created as objects. These objects are to be assigned to Statement reference. 
    Ex: Statement stmts = con.createStatement();//creates a statement object
  • The specific SQL statements are to be executed and the result should be captured
    Ex: ResultSet rsltSet = stmts.executeQuery(“select * from emp”);
  • To make a row inserted into the database and committed:
    stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

    ResultSet rs=stmt.executeQuery("select * from emp");
             rs.moveToInsertRow();
             rs.updateInt(1,15); // first attribute with value 15
             rs.updateString(2,"Vismay”); // second value with Vismay
             rs.updateFloat(3,4500); // third attribute with 4500
             rs.insertRow();
    con.commit();

1

2

3

4

5

6

7

8