06:39
1) What is JDBC?
Java Database Connectivity (JDBC) is an application programming interface (API) for Java, which defines how a client may access a relational database. It provides methods to query and update data in a database. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the Java virtual machine (JVM) host environment.
2) What is a JDBC Driver and what are the types of JDBC driver?
JDBC driver contains classes and interfaces that enables Java applications to interact with a database. There are 4 types of JDBC drivers:
Type 1 driver or JDBC-ODBC bridge driver – The JDBC-ODBC bridge driver uses native ODBC driver to connect to the database. It converts JDBC method calls into the ODBC function calls.
Type 2 driver or Native-API, partly Java driver – The Native API driver uses the client-side libraries of the database. The driver converts JDBC calls into database calls by using native API provided by database. This driver is database specific so once you switch from one database to another you need to change this driver. Native database library must be loaded on each client machine that uses this type of driver.
Type 3 driver or Network Protocol, pure Java driver – The Network Protocol driver uses server-side middleware that converts JDBC calls into the vendor-specific database protocol.
Type 4 driver or Native-protocol, pure Java driver – This is the most widely used driver nowadays. The driver converts JDBC calls directly into vendor-specific database protocol. Many of these protocols are proprietary, hence the database vendors themselves will be the primary source for this type of driver.
3) Which Driver should be Used?
If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4.
If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver.
4) What are the main steps to connect to database using JDBC connectivity?
Register the Driver
Class.forName() is used to load the driver class explicitly.
Example to register with JDBC-ODBC Driver
Create a Connection
getConnection() method of DriverManager class is used to create a connection.
getConnection(String url)
getConnection(String url, String username, String password)
getConnection(String url, Properties info)
Example establish connection with Oracle Driver
Create SQL Statement
createStatement() method is invoked on current Connection object to create a SQL Statement.
public Statement createStatement() throws SQLException
Example to create a SQL statement
Execute SQL Statement
executeQuery() method of Statement interface is used to execute SQL statements.
public ResultSet executeQuery(String query) throws SQLException
Example to execute a SQL statement
Closing the connection
After executing SQL statement you need to close the connection and release the session. The close() method of Connection interface is used to close the connection.
public void close() throws SQLException
Example of closing a connection
5) What are the types of statements in JDBC?
There are 3 JDBC statements.
Statement - Use the for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters. An object of Statement class can be created using Connection.createStatement() method.
PreparedStatement - Use the when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.A SQL statement is pre-compiled and stored in a PreparedStatement object. An object of PreparedStatement class can be created using Connection.prepareStatement() method.
CallableStatement - Use when you want to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters.An object of CallableStatement class can be created using Connection.prepareCall() method.
6) What is JDBC Connection interface?
The Connection interface maintains a session with the database. It can be used for transaction management. It provides factory methods that returns the instance of Statement, PreparedStatement, CallableStatement and DatabaseMetaData.
7) What is JDBC ResultSet interface?
The ResultSet object represents a row of a table. It can be used to change the cursor pointer and get the information from the database.
8) What is JDBC ResultSetMetaData interface?
The ResultSetMetaData interface returns the information of table such as total number of columns, column name, column type etc.
9) What is database connection pooling? What are the advantages of using a connection pool?
Connection pooling is the mechanism by which we reuse connection objects which are used to make connection with the database. It allows multiple clients to share a cached set of connection objects. Getting connection and disconnecting are costly operation, which can affect the application performance, so we should avoid creating multiple connection objects during multiple database interactions.
A pool contains set of database connection objects which are already connected to the database, and any client who wants to use it can take it from pool and it can be returned back to the pool when done with using.
Apart from performance this also saves you resources as there may be limited database connections available for your application.
10) How do you iterate ResultSet in the reverse order?
You can traverse a ResultSet backwards if you have a scrollable resultset. You can get this by passing ResultSet.TYPE_SCROLL_INSENSITIVE and ResultSet.CONCUR_READ_ONLY parameters when creating the statement object.
Then you can use resultset.afterLast() to move the cursor to the end of the ResultSet object, just after the last row and traverse backwards using resultset.previous() method.
Here is a sample code.
Java Database Connectivity (JDBC) is an application programming interface (API) for Java, which defines how a client may access a relational database. It provides methods to query and update data in a database. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the Java virtual machine (JVM) host environment.
2) What is a JDBC Driver and what are the types of JDBC driver?
JDBC driver contains classes and interfaces that enables Java applications to interact with a database. There are 4 types of JDBC drivers:
Type 1 driver or JDBC-ODBC bridge driver – The JDBC-ODBC bridge driver uses native ODBC driver to connect to the database. It converts JDBC method calls into the ODBC function calls.
Type 2 driver or Native-API, partly Java driver – The Native API driver uses the client-side libraries of the database. The driver converts JDBC calls into database calls by using native API provided by database. This driver is database specific so once you switch from one database to another you need to change this driver. Native database library must be loaded on each client machine that uses this type of driver.
Type 3 driver or Network Protocol, pure Java driver – The Network Protocol driver uses server-side middleware that converts JDBC calls into the vendor-specific database protocol.
Type 4 driver or Native-protocol, pure Java driver – This is the most widely used driver nowadays. The driver converts JDBC calls directly into vendor-specific database protocol. Many of these protocols are proprietary, hence the database vendors themselves will be the primary source for this type of driver.
3) Which Driver should be Used?
If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4.
If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver.
4) What are the main steps to connect to database using JDBC connectivity?
Register the Driver
Class.forName() is used to load the driver class explicitly.
Example to register with JDBC-ODBC Driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Create a Connection
getConnection() method of DriverManager class is used to create a connection.
getConnection(String url)
getConnection(String url, String username, String password)
getConnection(String url, Properties info)
Example establish connection with Oracle Driver
Connection con = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:XE","username","password");
Create SQL Statement
createStatement() method is invoked on current Connection object to create a SQL Statement.
public Statement createStatement() throws SQLException
Example to create a SQL statement
Statement s=con.createStatement();
Execute SQL Statement
executeQuery() method of Statement interface is used to execute SQL statements.
public ResultSet executeQuery(String query) throws SQLException
Example to execute a SQL statement
ResultSet rs=s.executeQuery("select * from user");
while(rs.next())
{
System.out.println(rs.getString(1)+" "+rs.getString(2));
}
Closing the connection
After executing SQL statement you need to close the connection and release the session. The close() method of Connection interface is used to close the connection.
public void close() throws SQLException
Example of closing a connection
con.close();
5) What are the types of statements in JDBC?
There are 3 JDBC statements.
Statement - Use the for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters. An object of Statement class can be created using Connection.createStatement() method.
PreparedStatement - Use the when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.A SQL statement is pre-compiled and stored in a PreparedStatement object. An object of PreparedStatement class can be created using Connection.prepareStatement() method.
CallableStatement - Use when you want to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters.An object of CallableStatement class can be created using Connection.prepareCall() method.
6) What is JDBC Connection interface?
The Connection interface maintains a session with the database. It can be used for transaction management. It provides factory methods that returns the instance of Statement, PreparedStatement, CallableStatement and DatabaseMetaData.
7) What is JDBC ResultSet interface?
The ResultSet object represents a row of a table. It can be used to change the cursor pointer and get the information from the database.
8) What is JDBC ResultSetMetaData interface?
The ResultSetMetaData interface returns the information of table such as total number of columns, column name, column type etc.
9) What is database connection pooling? What are the advantages of using a connection pool?
Connection pooling is the mechanism by which we reuse connection objects which are used to make connection with the database. It allows multiple clients to share a cached set of connection objects. Getting connection and disconnecting are costly operation, which can affect the application performance, so we should avoid creating multiple connection objects during multiple database interactions.
A pool contains set of database connection objects which are already connected to the database, and any client who wants to use it can take it from pool and it can be returned back to the pool when done with using.
Apart from performance this also saves you resources as there may be limited database connections available for your application.
10) How do you iterate ResultSet in the reverse order?
You can traverse a ResultSet backwards if you have a scrollable resultset. You can get this by passing ResultSet.TYPE_SCROLL_INSENSITIVE and ResultSet.CONCUR_READ_ONLY parameters when creating the statement object.
Then you can use resultset.afterLast() to move the cursor to the end of the ResultSet object, just after the last row and traverse backwards using resultset.previous() method.
Here is a sample code.
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet resultSet = statement.executeQuery("select * from XX");
resultSet.afterLast();
while (resultSet.previous()) {
// do something
}
11) What are stored procedures? How to call stored procedure using JDBC API?
A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are pre-compiled and stored in a relational database management system as a group, so it can be reused and shared by multiple programs.Stored procedures can be compiled and executed with different parameters and results and may have any combination of IN/OUT parameters. Stored procedures can be called using CallableStatement class in JDBC API.
Example :
CallableStatement cs = connection.prepareCall("{call STORED_PROCEDURE_NAME}");
ResultSet rs = cs.executeQuery();
12) What is DriverManager class?
The DriverManager class acts as an interface between user and drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver().
13) What is the use of setAutoCommit() method?
When a connection is created, it is in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. By setting auto-commit to false no SQL statements will be committed until you explicitly call the commit() method.
14) What is a “dirty read”?
There can be a situation where one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value.
No comments
Post a Comment