Struts Framework-Interview-1

Struts Framework

Click

j2ee-JDBC interview questions

JDBC

1) What are the steps in the JDBC connection?
While making a JDBC connection we go through the following steps :
Step 1 : Register the database driver by using :
Class.forName(\" driver classs for that specific database\" );
Step 2 : Now create a database connection using :
Connection con = DriverManager.getConnection(url,username,password);
Step 3: Now Create a query using :
Statement stmt = Connection.Statement(\"select * from TABLE NAME\");
Step 4 : Exceute the query :
stmt.exceuteUpdate();

2) What is JDBC?

JDBC is a set of Java API for executing SQL statements. This API consists of a set of classes and interfaces to enable programs to write pure Java Database applications.

3) What are drivers available?

a) JDBC-ODBC Bridge driver

b) Native API Partly-Java driver

c) JDBC-Net Pure Java driver

d) Native-Protocol Pure Java driver

4) What is the difference between JDBC and ODBC?

a) OBDC 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.

5) What are the types of JDBC Driver Models and explain them?

There are two types of JDBC Driver Models and they are:

a) Two tier model

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 updating of the above.

6) What are the steps involved for making a connection with a database or how do you connect to a database?

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,

DriverManager.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");

7) What type of driver did you use in project?

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

8) What are the types of statements in JDBC?

Statement -- To be used createStatement() method for executing single SQL statement

PreparedStatement -- To be used preparedStatement() method for executing same SQL statement over and over .

CallableStatement -- To be used prepareCall( ) method for multiple SQL statements over and over.

9) What is stored procedure?

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.

10) How to create and call stored procedures?

To create stored procedures:

Create procedure procedurename (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( );

11)Why do we have index table in the database?

Because the index table contain the information of the other tables. It will be faster if we access the index table to find out what the other contain.

12) Give an example of using JDBC access the database.

Answer:

try

{

Class.forName("register the driver");

Connection con = DriverManager.getConnection("url of db", "username","password");

Statement state = con.createStatement();

state.executeUpdate("create table testing(firstname varchar(20), lastname varchar(20))");

state.executeQuery("insert into testing values('phu','huynh')");

state.close();

con.close();

}

catch(Exception e)

{

System.out.println(e);

}

13)What are the two major components of JDBC?

One implementation interface for database manufacturers, the other implementation interface for application and applet writers.

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

15)What are the common tasks of JDBC?

1.Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers; 2. Register a driver; 3. Specify a database; 4. Open a database connection; 5. Submit a query; 6. Receive results.

16)What packages are used by JDBC?

There are 8 packages: java.sql.Driver, Connection,Statement, PreparedStatement, CallableStatement, ResultSet, ResultSetMetaData, DatabaseMetaData.

17)What are the flow statements of JDBC?

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

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

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

19)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:

E.g.

Class.forName("jdbc.DriverXYZ");

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

21)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:

E.g.

String url = "jdbc:odbc:Fred";

Connection con = DriverManager.getConnection(url, "Fernanda", "J8");

22)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. E.g. 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();

23)How can you retrieve data from the ResultSet?

First 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.

E.g.

ResultSet rs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

Second:

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

24)What are the different types of Statements?

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

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

E.g.

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

26)How to 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;

E.g.

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

ResultSet rs = cs.executeQuery();

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

E.g.

SQLWarning warning = stmt.getWarnings();

if (warning != null) {

while (warning != null) {

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

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

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

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

warning = warning.getNextWarning();

}

}

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

E.g.

Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

ResultSet uprs = ("SELECT COF_NAME, PRICE FROM COFFEES");

29). What is JDBC ? what are its advantages ?

It is an API .The latest version of jdbc api is (3.0).

The JDBC 3.0 API is divided into two packages:

(1) java.sql and (2) javax.sql.

Both packages are included in the J2SE and J2EE platforms.

Advantages:

The JDBC API can be used to interact with multiple data sources in a distributed, heterogenous environment.

It can connect to any of the database from java language.

It can switch over to any backend database without changing java code or by minute changes.

30). How many JDBC Drivers are there ? what are they?

There are 4 types of JDBC drivers.

a. JDBC-ODBC Bridge Driver(Type-1 driver)

b. Native API Partly Java Driver(Type-2 driver)

c. Net protocol pure Java Driver(Type-3 driver)

d. Native protocol Pure Java Driver(Type-4 driver)

31. Explain about JDBC-ODBC driver(Type-1) ? When this type of driver is used ?

In this mechanism the flow of execution will be

Java code(JDBC API)<------>JDBC-ODBC bridge driver<------->ODBC API<------->ODBC Layer<-------->DataBase

This type of JDBC Drivers provides a bridge between JDBC API and ODBC API.

This Bridge(JDBC-ODBC bridge) translates standard JDBC calls to Corresponding ODBC Calls, and

send them to ODBC database via ODBC Libraries.

The JDBC API is based on ODBC API.

ODBC(Open Database Connectivity)is Microsoft's API for Database drivers.

ODBC is based on X/Open Call Level Interface(CLI)specification for database access.

The URL and class to be loaded for this type of driver are

Class :- sun.jdbc.odbc.JdbcOdbcDriver

URL :- jdbc:odbc:dsnname

32. Explain about Type-2 driver ? When this type of driver is used ?

The Drivers which are written in Native code will come into this category

In this mechanism the flow of Execution will be

java code(JDBC API)<------>Type-2 driver(jdbc driver)<------->Native API(vendor specific)<------->DataBase

When database call is made using JDBC,the driver translates the request into vendor-specific API calls.

The database will process the requet and sends the results back through the Native API ,which will

forward them back to the JDBC dirver. The JDBC driver will format the results to conform to the JDBC

standard and return them to the application.

33. Explain about Type-3 driver ? When this type of driver is used ?

In this mechanism the flow of Execution will be

java code(JDBC API)<------>JDBC driver<------->JDBC driver server<-------->Native driver<------->DataBase

The Java Client Application sends the calls to the Intermediate data access server(jdbc driver server)

The middle tier then handles the requet using other driver(Type-II or Type-IV drivers) to complete the request.

34. Explain about Type-4 driver ? When this type of driver is used ?

This is a pure java driver(alternative to Type-II drivers).

In this mechanism the flow of Execution will be

java code(JDBC API)<------>Type-4 driver(jdbc driver)<------->DataBase

These type of drivers convert the JDBC API calls to direct network calls using

vendor specific networking protocal by making direct socket connection with

database.

examples of this type of drivers are

1.Tabular Data Stream for Sybase

2.Oracle Thin jdbc driver for Oracle

35. Which Driver is preferable for using JDBC API in Applets?

Type-4 Drivers.

36.Write the Syntax of URL to get connection ? Explain?

Syntax:- jdbc::

jdbc -----> is a protocal .This is only allowed protocal in JDBC.

----> The subprotocal is used to identify a database driver,or the

name of the database connectivity mechanism, choosen by the database driver providers.

-------> The syntax of the subname is driver specific. The driver may choose any

syntax appropriate for its implementation

ex: jdbc:odbc:dsn

jdbc:oracle:oci8:@ database name.

jdbc:orale:thin:@ database name:port number:SID

37.How do u Load a driver ?

Using Driver Class.forName(java.lang.String driverclass) or registerDriver(Driver driver)

38.what are the types of resultsets in JDBC3.0 ?How you can retrieve information of resultset?

ScrollableResultSet and ResultSet.We can retrieve information of resultset by using java.sql.ResultSetMetaData interface.You can get the instance by calling the method getMetaData() on ResulSet object.

39.write the steps to Connect database?

Class.forName(The class name of a spasific driver);

Connection c=DriverManager.getConnection(url of a spasific driver,user name,password);

Statement s=c.createStatement();

(or)

PreparedStatement p=c.prepareStatement();

(or)

CallableStatement cal=c.prpareCall();

Depending upon the requirement.

40.Can java objects be stored in database? how?

Yes.We can store java objects, BY using setObject(),setBlob() and setClob() methods in PreparedStatement

41.what do u mean by isolation level?

Isolation means that the business logic can proceed without

consideration for the other activities of the system.

42.How do u set the isolation level?

By using setTransactionIsolation(int level) in java.sql.Connection interface.

level MEANS:-

static final int TRANSACTION_READ_UNCOMMITTED //cannot prevent any reads.

static final int TRANSACTION_READ_COMMITTED //prevents dirty reads

static final int TRANSACTION_REPEATABLE_READ //prevents dirty reads & non-repeatable read.

static final int TRANSACTION_SERIALIZABLE //prevents dirty reads , non- repeatable read & phantom read.

These are the static final fields in java.sql.Connection interface.

43. What is a dirty read?

A Dirty read allows a row changed by one transaction to be

read by another transaction before any change in the row

have been committed.

This problem can be solved by setting the transaction isolation

level to TRANSACTION_READ_COMMITTED

44. what is a non-repeatable read ?

A non-repeatable read is where one transaction reads a row, a second

transaction alters or deletes the row, and the first transaction

re-reads the row,getting different values the second time.

This problem can be solved by setting the transaction isolation

level to TRANSACTION_REPEATABLE_READ

45. what is phantom read?

A phantom read is where one transaction reads all rows that satisfy

a WHERE condition,a second transaction inserts a row that satisfies

that WHERE condition,and the first transaction re-reads for the same

condition,retrieving the additional 'phantom' row in the second read

This problem can be solved by setting the transaction isolation

level to TRANSACTION_SERIALIZABLE

47.How to retrieve the information about the database ?

We can retrieve the info about the database by using inerface

java.sql.DatabaseMetaData

We can get this object by using getMetaData() method in

Connection interface.

48.what are the Different types of exceptions in jdbc?

BatchUpdateException

DataTruncation

SQLException

SQLWarning

49.How to execute no of queries at one go?

By using a batchUpdate's (ie throw addBatch() and executeBatch())

in java.sql.Statement interface,or by using procedures.

50. What are the advantages of connection pool.?

Performance

51.In which interface the methods commit() & rollback() are defined ?

A.java.sql.Connection interface

52.How to store images in database?

Using binary streams (i.e. getBinaryStream (), setBinaryStream ()). But it is not visible in database, it is stored in form of bytes, to make it visible we have to use any one front-end tool.

53.How to check null value in JDBC?

By using the method wasNull() in ResultSet ,it returns boolean value.

Returns whether the last column read had a value of SQL NULL.

Note that you must first call one of the getXXX methods on a column to try to read its value and then call the method wasNull to see if the value read was SQL NULL.

54.Give one Example of static Synchronized method in JDBC API?

getConnection() method in DriverManager class.Which is used to get object of Connection interface.

55.What is a Connection?

Connection is an interface which is used to make a connection between client and Database (ie opening a session with a particular database).

56.what is the difference between execute() ,executeUpdate() and executeQuery() ? where we will use them?

execute() method returns a boolean value (ie if the given query

returns a resutset then it returns true else false),so depending upon

the return value we can get the ResultSet object (getResultset())or

we can know how many rows have bean affected by our query

(getUpdateCount()).That is we can use this method for Fetching

queries and Non-Fetching queries.

Fetching queries are the queries which are used to fetch the records from database (ie which returns resutset)

ex: Select * from emp.

Non-Fetching queries are the queries which are used to update,insert,create or delete the records from database

ex: update emp set sal=10000 where empno=7809.

executeUpdate() method is used for nonfetching queries.which returns int value.

executeQuery() method is used for fetching queries which returns ResulSet object ,Which contains methods to fetch the values.

58). Using a Prepared Statement

A prepared statement should be used in cases where a particular SQL statement is used frequently. The prepared statement is more expensive to set up but executes faster than a statement. This example demonstrates a prepared statement for getting all rows from a table called ''mytable'' whose column COL_A equals ''Patrick Chan''. This example also demonstrates a prepared statement for updating data in the table. In particular, for all rows whose column COL_B equals 123, column COL_A is set to ''John Doe''.

try {

// Retrieving rows from the database.

PreparedStatement stmt = connection.prepareStatement(

"SELECT * FROM mytable WHERE COL_A = ?");

int colunm = 1;

stmt.setString(colunm, "Patrick Chan");

ResultSet rs = stmt.executeQuery();

// Updating the database.

stmt = connection.prepareStatement(

"UPDATE mytable SET COL_A = ? WHERE COL_B = ?");

colunm = 1;

stmt.setString(colunm, "John Doe");

colunm = 2;

stmt.setInt(colunm, 123);

int numUpdated = stmt.executeUpdate();

} catch (SQLException e) {

}

59.What are the types of JDBC drivers ?

Type 1- JDBC-ODBC Bridge

Type 2- Native API Driver

Type 3 - JDBC-Net,Java Pure Driver

Type 4- Native Protocol, Pure Java Driver

60.Which among the four driver is pure Java driver ?

Type 3 and Type 4

61.How do you connect without the Class.forName (" ") ?

It is not possible not connect

62.What does Class.forName return ?

Class Name

63.Why is preparedStatement,CallableStatement used for?

Preapred Stmt -SQL in the database is a precompiled, by the database for faster execution

CallableStmt-JDBC allows use of stored procedures by the CallableStatement class.

A CallableStatement object is created by the preaprecall()method in the connection object

64) Explain the role of Driver in JDBC?
The JDBC Driver 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.

65) Is java.sql.Driver a class or an Interface ?
It's an interface.

66) Is java.sql.DriverManager a class or an Interface ?
It's a class. This class provides the static getConnection method, through which the database connection is obtained.

67) Is java.sql.Connection a class or an Interface ?
java.sql.Connection is an interface. The implmentation is provided by the vendor specific Driver.

68) Is java.sql.Statement a class or an Interface ?
java.sql.Statement,java.sql.PreparedStatement and java.sql.CallableStatement are interfaces.

69) Which interface do PreparedStatement extend?
java.sql.Statement

70) Which interface do CallableStatement extend?
CallableStatement extends PreparedStatement.

71) What is the purpose Class.forName("") method?
The Class.forName("") method is used to load the driver.

72) Do you mean that Class.forName("") method can only be used to load a driver?
The Class.forName("") method can be used to load any class, not just the database vendor driver class.

73) Which statement throws ClassNotFoundException in SQL code block? and why?
Class.forName("") method throws ClassNotFoundException. This exception is thrown when the JVM is not able to find the class in the classpath.

74) What exception does Class.forName() throw?
ClassNotFoundException.

75) What is the return type of Class.forName() method ?
java.lang.Class

76) Can an Interface be instantiated? If not, justify and explain the following line of code:
Connection con = DriverManager.getConnection("dsd","sds","adsd");
An interface cannot be instantiated. But reference can be made to a interface. When a reference is made to interface, the refered object should have implemented all the abstract methods of the interface. In the above mentioned line, DriverManager.getConnection method returns Connection Object with implementation for all abstract methods.

77) What type of a method is getConnection()?
static method.

78) What is the return type for getConnection() method?
Connection object.

79) What is the return type for executeQuery() method?
ResultSet

80) What is the return type for executeUpdate() method and what does the return type indicate?
int. It indicates the no of records affected by the query.

81) What is the return type for execute() method and what does the return type indicate?
boolean. It indicates whether the query executed sucessfully or not.

82) Is Resultset a Classor an interface?
Resultset is an interface.

83) What is the advantage of PrepareStatement over Statement?
PreparedStatements are precompiled and so performance is better. PreparedStatement objects can be reused with passing different values to the queries.

84) What is the use of CallableStatement?
CallableStatement is used to execute Stored Procedures.

85) Name the method, which is used to prepare CallableStatement?
CallableStament.prepareCall().

86) What do mean by Connection pooling?

Opening and closing of database connections is a costly excercise. So a pool of database connections is obtained at start up by the application server and maintained in a pool. When there is a request for a connection from the application, the application server gives the connection from the pool and when closed by the application is returned back to the pool. Min and max size of the connection pool is configurable. This technique provides better handling of database connectivity.

87. Which methods and classes are used for connection pooling?

88). How will you perform transactions using JDBC?

89). What is difference between PreparedStatement and Statement?

90). What is the difference between ExecuteUpdate and ExecuteQuery?

91). How do you know which driver is connected to a database?

92). What is DSN and System DSN and differentiate these two?

93). How you can know about drivers and database information?

94). If you are truncated using JDBC, How can you know? That how much

data is truncated?

95). What are the Normalization Rules? Define the Normalization?