Mar 17

jdbc interview questions part-6

26.Explain different types of ResultSets in jdbc?

There are three result set types :

1. TYPE_FORWARD_ONLY

2. TYPE_SCROLL_INSENSITIVE

3. TYPE_SCROLL_SENSITIVE

 

  1. TYPE_FORWARD_ONLY :

1) This result set is not scrollable.

2) Its cursor moves only in forward direction.

3) The rows in the result set satisfies the query either at the time when query was executed, or when the rows retrieved.

4) This is default result set.

 

2.TYPE_SCROLL_INSENSITIVE

  1. This result set is scrollable.
  2. It can scroll forward and backward.
  3. Rows in result set does not reflect changes made in database.
  4. Rows in result set satisfy query ,either at the time when query was executed or when rows are retrieved.

 

3.TYPE_SCROLL_SENSITIVE

1) This result set is scrollable.

2) It can scroll forward and backward.

3) Rows in result set reflect changes made in database.

 

27.Difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE?

The only difference between those two is 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.

 

Mar 17

jdbc interview questions and answers part 5

21.Explain ResultSet interface ?

  1. ResultSet returns one or more rows returned from database tables.
  2. A ResultSet object maintains cursor pointing current row of data.
  3. When the cursor is returned , it is positioned before first row.
  4. The next() moves the cursor to next row and returns either true or false.
  5. The previous() returns cursor to the previous row.
  6. It returns true if the current row is valid and returns false if there are no more rows.
  7. To get data from ResultSet we use getString() , getBoolean(), getInt(), getLong(), getFloat() etc

 

String sql = “SELECT empno,empname,age FROM Employees”;ResultSet rs = stmt.executeQuery(sql);

 

while(rs.next()){

//Retrieve by column name

long empNo = rs.getLong(“empno”);

String first = rs.getString(“first”);

int age = rs.getInt(“age”);

}

rs.close();

 

22.Explain PreparedStatement ?

  1. PreparedStatement object represents precompiled sql statement.
  2. PreparedStatement extends Statement interface.
  3. Unlike Statement object PreparedStatement object is given a SQL statement when it is created.

4.PreparedStatement has IN parameters for which values are being set using setter methods.

5.Instances of precompiled statements contain sql statements that has already been compiled.

6.Because PreparedStatement objects are precompiled , their execution is faster than Statement objects.

7.The SQL statement contained in a PreparedStatement object may have one or more INparameters.

8. An IN parameter is a parameter whose value is not specified when the SQLstatement is created.

9.Instead, the statement has a question mark (?) as a placeholder foreach IN parameter.

10. The ? is also known as a parameter marker or parameter placeholder. An application must set a value for each parameter marker in a prepared statement beforeexecuting the prepared statement.

11.To increase efficiency SQL statement that is executed many times is often created as PreparedStatement object to increase efficiency.

12.The advantage of using Prepared statement is we can use same statement and supply it with different values each time we execute it.

13.It is the responsibility of developer to make sure that the type in the Java programming language

14.For each IN parameter maps to a JDBC type that is compatible with the JDBC data type expected by the database.

String insertSQL = “INSERT INTO EMPLOYEES”+ “(EMP_NO, EMP_NAME, EMP_AGE) VALUES”

+ “(?,?,?)”;

PreparedStatement ps=connection.prepareStatement(insertSQL);

ps.setInt(1,101);

ps.setString(2,”abc”);

ps.setAge(3,”25″);

ps.executeUpdate();

 

23.Difference between Statement and PreparedStatement?

Statement PreparedStatement
1.Statement is usually parsed and executed each time. 1.PreparedStatement is parsed once and executed with different parameters repeatedly.
2.A statement is a static Sql statement.It does not support parameters. A PreparedStatement is a dynamic Sql statement.It does supports parameters.
3.It is slower because every time the statements get parsed and executed. 3.This is faster because this is precompiled once and gets executed with different parameters.
4.Statement verifies metadata against database everytime. 4.PreparedStatement verifies metadata against database only once.
5.If we want to execute sql statement once it is recommended to use statement. 5.If we want to execute sql statements repeatedly it is recommended to use PreparedStatement.
6.This is suitable for DDL operations. 6.This is suitable for DML operations.

 

 

24. Explain Callable Statement?

(or)

How to call stored procedures in jdbc?

public interface CallableStatement extends PreparedStatement

1) Callable Statement extends PreparedStatement interface.

2) This interface is used to execute stored procedures in jdbc.

3) The first step is to create Callable statement object.

4) We create callable statement object as we did with Statement and PreparedStatement object.

5) we use connection class prepare method to call stored procedure.

6)A callable statement object contains call to StoredProcedure but not StoredProcedure itself.

7) To close callabeStatement object we use close() method.

SYNTAX :

call procedure_name(?,?,?)

Ex :

CallableStatement cs = con.prepareCall(“{call SHOW_EMPLOYEES}”);ResultSet rs = cs.executeQuery();

The first line of code creates call to stored procedure SHOW_EMPLOYEES using the connection object.

The part enclosed in flower braces is the escape syntax , while jdbc driver will translates the escape syntax in to native sql to call stored procedure.

We are using executeQuery because the SHOW_EMPLOYEES procedure returns one resultset.

If the procedure contains one update or one DDL statement, executeUpdate() will be used.

 

CallableStatement cs = con.prepareCall(“{call getData(?,?)}”);ResultSet rs = cs.executeQuery();

The first line of code creates calls stored procedure getData() which takes two input parameters.

The “?” parameters can be either IN,OUT, INOUT parameters depending on the stored procedure.

 

25.Explain Transactions in JDBC?

A Transaction is set of one or more statements that are executed together as unit, so all the statements are executed or none of them are executed.

For example : When hourly salary of an employee is updated , the total salary should be updated accordingly otherwise it leads to inconsistent data.

The way that both actions occur or none of them occur can be called as transaction.

In JDBC when connection is created it is by default in autocommit mode.

Each individual sql statement is treated as transaction and automatically comitted after execution.

If we want to include two or more statements in single transaction set AutoCommit mode to false and commit the transaction manually.

Ex :

conn.setAutoCommit(false);String sql=UPDATE EMPLOYEES SET EMP_AGE=30 WHERE EMP_ID=100”;

Statement statement=conn.createStatement();

ResultSet resultSet=statement.executeUpdate(sql);

String sql=UPDATE EMPLOYEES SET EMP_AGE=40 WHERE EMP_ID=100”;

Statement statement=conn.createStatement();

ResultSet resultSet=statement.executeUpdate(sql);

conn.commit();

 

In the above example as autocommit mode is false the above two statements will be commited together when commit is called.

But if we go to default autocommit each statement will be commited as and when it completes execution.

The main advantage of disabling autocommit mode is to include more than one statement in transaction which improves performance as we are not calling commit after every statement.

Mar 17

jdbc interview questions for experienced-part 4

 16.Explain Connection interface?

  1. Connection interface represents connection object with database.
  2. A connection object is in autocommit mode by default.
  3. In auto-commit mode changes are comitted after executing sql statements.
  4. Auto commit mode can be set to false by calling setAutoCommit.
  5. We can call close() if connection object is not required.

 

Here are list of methods added to Connection interface in jdbc 4.0

createBlob() Creates a Blob object
CreateClob() Creates a Clob object.
CreateNClob() Creates an NClob object.
CreateSQLXML() Creates a SQLXML object

17.Explain Statement interface and how to create statement object?

  1. A java.sql.Statement object represents sql statement.
  2. We can get Statement object by calling createStatement() object on java.sql.Connection object.
Statement statement = connection.createStatement();

Here are the methods to get the statement object.

createStatement() A Statement object is created with resultset of type TYPE_FORWARD_ONLY, and of

concurrency CONCUR_READ_ONLY.

createStatement(int resultSetType, intresultSetConcurrency) A Statement object is created with thespecifed result set type and result set

concurrency.

createStatement(int resultSetType,int resultSetConcurrency, int

resultSetHoldability)

A Statement object is created with thespecifed result set type, concurrency,

and holdability.

18.How to retrieve data from database using statement object in jdbc?

ResultSet executeQuery(String sql):

  1. This method takes sql argument and returns ResultSetObject.
  2. This method should be used for executing sql statements that expect to return data from database.
  3. executeQuery() executes SQL SELECT statement that returns data.
  4. If there is no data executeQuery() returns empty resultset.
String sql=”select empno,empname from employee”;Statement statement=conn.createStatement();

ResultSet resultSet=statement.executeQuery(sql);

19.How to update records in database using statement object in jdbc?

  1. This method takes sql argument and returns the number of affected rows.
  2. excuteUpdate() executes SQL Insert,SQL update,SQL delete.
  3. It is also used to execute DDL statements create, alter and drop.
String sql=”select empno,empname from employee”;Statement statement=conn.createStatement();

statement.executeUpdate(sql);

20.Explain execute() method with example?

  1. execute() method takes sql argument and returns boolean value.
  2. If true is returned then ResultSet object is returned.
  3. If false is returned then it returns updated count.
  4. If the result is ResultSet object we obtain through getResultSet() method.
  5. If the result is updated count we obtain through getUpdateCount() method.
  6. We use this method when SQL is query or update.
  7. This happens when application executes dynamically created sql statements.
Statement statement=connection.createStatement();boolean resultType=statement .execute(“select empno,empname from employee “);

if(resultType==true)

ResultSet resultSet=statement .getResultSet();

else

int updateCount=statement .getUpdateCount();

Mar 12

jdbc interview questions-part 3

11.Explain Type 3 Driver or JDBC Net pure java driver?

  1. Type 3 driver is written purely in java.
  2. JDBC driver on client uses sockets to call middleware application on the server which translates client requests into API specific.
  3. This driver is flexible as it is not required to install software on client system.

Advantage :

  1. Fully written in java which is portable.
  2. Allows to access multiple databases using one driver.

 

12.Explain Type 4 driver?

  1. This is a pure java driver.
  2. It is lightweight and easy to install
  3. This driver does not requires software to be installed on client side.
  4. This driver communicates directly with datasource using java sockets .

Disadvantage:

  1. Needs separate driver for each database.

 

13.Write Steps for connecting to database in jdbc ?

  1. Register or load JDBC Driver
  2. Obtain Connection.
  3. Create Statement
  4. Create ResultSet object if data is returned from database
  5. Close JDBC objects to free resources.

 

14.How to register or load JDBC drivers?

There are two ways to load JDBC driver:

  1. Manually
  2. Dynamically

Manually loading database drivers :

We load database driver manually using java.lang.class forName() static method.

class.forName(driverClass)
Ex : Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Class.forName must be enclosed in try catch block.

Loading drivers dynamically:

With dynamic loading there is no need to call Class.forName because DriverManager searches JDBC drivers in the class path and call Class.forName() in the background.

In JDBC 4.0 DriverManager has added support to getConnection() and getDrivers() which will load drivers automatically.


15.How to obtain connection in jdbc ?

  1. DriverManager class is used to obtain connection with database.
  2. JDBC driver is required to be loaded before obtaining connection.
  3. A JDBC connection is represented by java.sql.Connection object and obtained by calling getConnection() method from DriverManager class.
getConnection(String url ) While invoking we need to pass url to this method which will give connection object.
getConnection(String url,Properties properties) Username and password may be specifedin the Properties Hashtable.
getConnection(String url, String user,String password) Gives a connection with a URL username, and password.
String url=”jdbc:oracle:thin:@localhost:1521:ORCL”;Connection connection = DriverManager.getConnection(url, “abc”, “xyz”);
Mar 12

JDBC interview questions for freshers -part2

6.List important features in JDBC 3.0?

  1. Addition of SavePoints
  2. Defines several Standard Connection pool properties such as maxStatements , initialPoolsize etc.
  3. Supports multiple open resultsets
  4. Two new datatypes have been added java.sql.Types.DATALINK and java.sql.Types.Boolean.

7.List some Features of JDBC 4.0?

  1. In earlier versions we need to manually register and load drivers using class.forName . No need to call class.forName jdbc automatically loads driver .
  2. Redefined subclasses of exceptions.
  3. New methods added to javax.sql.PooledConnection.
  4. Connection pools can listen statement closing and statement error events.
  5. A new interface SQL XML has been added.

8.What is JDBC Driver?

JDBC Driver enables us to connect to database. Driver acts a translator. Every database needs a different driver to connect to database.

9.Explain types of JDBC drivers?

Type1 driver or JDBC-ODBC Bridge Driver :

  1. These drivers uses bridging technology to access database.
  2. These drivers depend on native client library .
  3. Sun JDBC-ODBC Bridge driver is an example
  4. Type1 requires software to be installed on client systems.
  5. These are not good solution for applications that donot allow us to install software on client.
  6. This is not multithreaded.
  7. This driver is used when jdbc was started , it is recommended when there is no alternative.

Advantages:

  1. Access to all databases

Disadvantages:

         1.Not written in java.

2.Slowest driver because jdbc calls has to pass through the bridge to the odbc driver.

3.Requires software to be installed on client systems.

10.Explain Type 2 Driver or NativeAPI partly java driver?

  1. Type2 Drivers are native API drivers.
  2. Driver contains java code that calls native c or c++ methods provided by database vendor to perform database access.
  3. This driver requires software to be installed on client system.
  4. OCI (Oracle call interface is an example for type 2 driver)
  5. If we change database we have to change native api used to connect to database

Advantages :

  1. Offers better performance when compared to type 1 driver because there is no need to pass through bridge to the odbc .

Disadvantages:

  1. Not written in java which leads to portability issue.
  2. If we change database we have to change API.

 

 

Mar 12

jdbc interview questions 2013 part 1

  1. What is jdbc?

JDBC stands for java database connectivity.

JDBC is an standard api in java used to access relational database from a java application.

We can perform the following tasks using jdbc :

  1. Connect to database.
  2. Retrive data and update data in relational databases.
  3. Retrive and process results from database.

By using jdbc we can access different databases.

2) Explain JDBC API?

JDBC API consists of two packages :

  1. java.sql :

This package contains core JDBC API to access and manipulate information in tables.

This package is mainly used to perform basic CRUD (Create,Read,Update,Delete) operations.

This package contain classes and interfaces to create tables, reading data from tables,inserting data in to tables , updating and deleting data in table.

  1. javax.sql

This package supports advance features such as connection pooling, support for JNDI and distributed transactions.

3) Explain different versions of jdbc?

  1. JDBC 1.0
  2. JDBC 2.0
  3. JDBC 3.0
  4. JDBC 4.0

4.List some of the features in jdbc 1.0?

JDBC 1.0 consists of classes and interfaces used to establish connection with database.

Here are features of jdbc 1.0:

  1. Running Sql statements
  2. Retrieving values from result sets.
  3. Using transactions.

Here are important list of classes and interfaces :

java.sql.DriverManger(class) Used to establish connection with database
java.sql.Connection(interface) Represents connection in database
java.sql.Statement(interface) Runs sql statements and returns result sets
java.sql.ResultSet(interface) Returns resultset of table data .
java.sql.PreparedStatement(interface) Represents precompiled statement
java.sql.CallableStatement(interface) Used to execute sql procedures.

5.List some of the features of JDBC 2.0?

JDBC 2.0 supports

  1. Scrollable ResultSets
  2. Methods to update resultsets or database table
  3. Batch updates
  4. Supports datatypes such as BLOB,CLOB, Array, Ref.