Tuesday, October 18, 2011

JDBC FAQS

What is JDBC?
Ans: JDBC is a set of Java API for executing SQL statements. This API consists of a set of classes and interfaces to Java Database applications. The database vendors like Oracle,MySql ,TeraData ….are provided the implementations for some of the classes in JDBC API.

What are drivers available?
Ans: Type 1- JDBC-ODBC Bridge driver
Type 2- Native API Partly-Java driver
Type 3 -JDBC-Net Pure Java driver
Type 4 -Native-Protocol Pure Java driver

What is the difference between JDBC and ODBC?
Ans: a) ODBC(Open Data Base Connectivity) is for Microsoft and JDBC is for Java applications.
b) ODBC can’t be directly used with Java because it uses a C interface.
c) ODBC makes use of pointers which have been removed totally from Java.
d) ODBC mixes simple and advanced features together and has complex options for simple queries. But JDBC is designed to keep things simple while allowing advanced capabilities when required.
e) ODBC requires manual installation of the ODBC driver manager and driver on all client machines. JDBC drivers are written in Java and JDBC code is automatically installable, secure, and portable on all platforms.
f) JDBC API is a natural Java interface and is built on ODBC. JDBC retains some of the basic features of ODBC.

What are the types of JDBC Driver Models and explain them?
Ans: There are two types of JDBC Driver Models and they are:
a) Two tier model and b) Three tier model
Two tier model: In this model, Java applications interact directly with the database. A JDBC driver is required to communicate with the particular database management system that is being accessed. SQL statements are sent to the database and the results are given to user. This model is referred to as client/server configuration where user is the client and the machine that has the database is called as the server.
Three tier model: A middle tier is introduced in this model. The functions of this model are:
a) Collection of SQL statements from the client and handing it over to the database,
b) Receiving results from database to the client and
c) Maintaining control over accessing and updation of the above.

What are the steps involved for making a connection with a database or how do you connect to a database?
Ans:
a) Loading the driver : To load the driver, Class.forName( ) method is used.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
When the driver is loaded, it registers itself with the java.sql.DriverManager class as an available database driver.
b) Making a connection with database : To open a connection to a given database,
DriverMnaager.getConnection( ) method is used.
Connection con = DriverManager.getConnection(“jdbc:odbc:somedb”, “user”, ”password”);
c) Executing SQL statements : To execute a SQL query, java.sql.statements class is used.
createStatement( ) method of Connection to obtain a new Statement object.
Statement stmt = con.createStatement( );
A query that returns data can be executed using the executeQuery( ) method of Statement. This method
executes the statement and returns a java.sql.ResultSet that encapsulates the retrieved data:
ResultSet rs = stmt.executeQuery(“SELECT * FROM some table”);
d) Process the results : ResultSet returns one row at a time. Next( ) method of ResultSet object can be called to move to the next row. The getString( ) and getObject( ) methods are used for retrieving column values:
while(rs.next( ) ) {
String event = rs.getString(“event”);
Object count = (Integer) rs.getObject(“count”);

What type of driver did you use in project?
Ans: JDBC-ODBC Bridge driver (is a driver that uses native(C language) libraries and makes calls to an existing ODBC driver to access a database engine).

What are the types of statements in JDBC?
Ans: Statement -- To be used createStatement( ) method for executing single SQL statement
PreparedStatement -- To be used preparStatement( ) method for executing same SQL statement over and over.
CallableStatement -- To be used prepareCall( ) method for multiple SQL statements over and over

What is stored procedure?
Ans: Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters.

How to create and call stored procedures?
Ans: To create stored procedures:
Create procedure procedure name (specify in, out and in out parameters)
BEGIN
Any multiple SQL statement;
END;

To call stored procedures:
CallableStatement csmt = con.prepareCall(“{call procedure name(?,?)}”);
csmt.registerOutParameter(column no., data type);
csmt.setInt(column no., column name)
csmt.execute( );

What are the differences between executeQuery(…), executeUpdate(…) and execute(…) methods?
Ans:
public ResultSet executeQuery(String sqlquery)

where executeQuery() can be used to execute selection group sql queries to fetch the data from database.When we use selection group sql query with executeQuery() then JVM will send that sql query to the database engine, database engine will execute it, by this database engine(DBE) will fetch the data from database and send back to the java application.

public int executeUpdate(String sqlquery)

where executeUpdate() can be used to execute updation group sql query to update the database. When we provide updation group sql query as a parameter to executeUpdate(), then JVM will send that sql query to DBE, here DBE will execute it and perform updations on the database, by this DBE will identify the number of records got updated value called as “records updated count” and return back to the java application.

public boolean execute(String sqlquery)

where execute() can be used to execute either selection group sql queries or updation group queries. But this method is renturn value is Boolean to get the ResultSet object to call the statement.get ResultSet() method . If we want to get Update count we have to use the statement.getUpdateCount() method.


What is a transaction ?
transaction is collection of logical operation that perform a task
Transaction should ACID properties.
A for Automicity
C for Consistency
I for Isolation
D for Durability.
A transaction can be termed as any operation such as storing, retrieving, updating or deleting records in the table that hits the database.

What is the purpose of setAutoCommit( )
It is set as ConnectionObject.setAutoComit(boolean val);
after any updates through the program cannot be effected to the database.We have to commit the transctions then only it reflect on Database .For this puprpose we can set AutoCommit flag to Connection Object.