A list of jdbc interview questions recently asked in interview are as follows(with answers),
1) What is JDBC ?
JDBC itself stands for Java Database Connectivity, and is used to connect and execute sql query to the database using jdbc drivers.
2) What are different types of JDBC Drivers?
JDBC Drivers can help to interact with java application and the database. There are 4 types of JDBC drivers.
1) JDBC - ODBC bridge driver
JDBC - ODBC driver uses ODBC driver to connect to the database. We should install ODBC driver in the client machine so thats'y this driver is not using at all.
2) Native - API driver(partially java driver)
This driver converts JDBC calls to the client API for the database servers. We should have database client API installed. This driver also not preferred driver because extra dependency on client API.
3) Pure Java driver (Network protocol driver)
This driver sends the JDBC calls to a middle-ware server that can connect to different type of databases. We should have a middle-ware server installed in the client machine. This adds to a extra network calls and slow performance and thatsy this driver also not using widely.
4) Thin driver or type 4 driver
This driver converts JDBC calls directly into the vendor specific database protocol . So, it is known as thin driver. It is fully written in Java language.
3) What are the steps in java to make JDBC connectivity ?
The following are the basic steps to connecting the java application with database using JDBC.
1) Register the driver
The forName() method of class is used to register the driver class. This method is used to dynamically load the driver class.
Syntax : -
public static void forName(String className) throws ClassNotFoundException
Example of forName() method of MySQL Driver :--
Class.forName("com.mysql.jdbc.Driver");
2) Create the Connection object
The getConnection() method of DriverManager class is used to create the Connection object.
Syntax :-
1) public static Connection getConnection(String url) throws SQLException
2) public static Connection getConnection(String url, String name, String password) throws SQLException
Example of getConnection() method of MySQL driver:--
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/dbName", "username", "password");
3) Create the Statement object
The Statement is an interface to execute the query with database. There are three types of statement in Java 1) Statement 2) PreparedStatement 3) CallableStatement
Example :
Statement st = con.createStatement(); //Statement
PreparedStatement pst = con.prepareStatement(String sqlQuery); //PreparedStatement
CallableStatement cst = con.prepareCall(String sqlQuery); // CallableStatement
4) Executing the Query
The execute(), executeQuery() and executeUpdate() method of Statement interface is used to execute the queries to the database.
Example:--
ResultSet rs = st.executeQuery();
int i = st.executeUpdate();
boolean success = st.execute();
5) Closing the Connection
The close() method of the Connection interface is used to close the connection object.
con.close();
4 ) What does setAutoCommit(false) do?
A JDBC Connection is created in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and will be automatically committed as soon as it is executed. If you require two or more statements to be grouped into a transaction then you need to disable auto-commit using below command,
con.setAutoCommit(false);
Once auto-commit mode is disabled , no SQL statements will be committed until you explicitly call the commit method.
Example:--
Connection con = null;
5) How to get the Database server details in Java?
We can use DatabaseMetaData interface to get the database server details. When database connection is created then you call meta data object by calling getMetaData() method. There are so many methods available in DatabaseMetaData to get the product name, major version, minor version and product version etc.
DatabaseMetaData dbmd = con.getMetaData();
System.out.println("Database Product Name "+dbmd.getDatabaseProductName);
System.out.println("Database Product Version"+dbmd.getDatabaseProductVersion);
System.out.println("Database Major Version "+dbmd.getDatabaseMajorVersion);
System.out.println("Database Minor Version "+dbmd.getDatabaseMinorVersion);
// Driver information
System.out.println("Driver Name "+dbmd.getDriverName);
System.out.println("Driver Version "+dbmd.getDriverVersion);
System.out.println("Driver Major Version "+dbmd.getDriverMajorVersion);
System.out.println("Driver Minor Version "+dbmd.getDriverMinorVersion);
6) Difference between Statement and PrepareStatement in Java?
Statement : - This statement is used for executing a static SQL statements and it can not accept parameters at run time. Statement is slower as compared to preparedStatement. Statement enforces SQL injection . This statement can't be used for storing/retrieving images and files in database
PreparedStatement:- This PreparedStatement is used for executing precompiled SQL statement and it can accept parameters at run time. Execution process is fast compared to Statement. PreapredStatement prevents the SQL injection. This statement can be used for storing/retrieving images and files in database.
7) Why PreparedStatement is faster than Statement ?
If you have to run the same Statement with multiple times, with different data then you can use PreparedStatement because it can validate query only once and run multiple times. i.e In PreparedStatement pre compilation is done only once so it can take less execution time. But in simple Statement every time it can validate and runs the query so it is slow compared to PreparedStatement.
8) How can we execute stored procedures and functions?
We can execute stored procedures and functions by using Callable statement interface. This statement can extends Prepared Statement interface.
For example,
CallableStatement callStmt = con.prepareCall("{call calculateTotalAmount(?,?)}") ;
In the above query, calculateTotalAmount is the stored procedure or function.
9) How the ResultSet Object works in JDBC ?
Refer this Working with JDBC ResultSet object
10) What are different types of ResultSet ?
ResultSet contains the result of the SQL query. There are different types of ResultSet object that we can get based on the user input while creating the Statement. In createStatement() and preparedStatement() methods of Statement and PreparedStatement, we are passing Resultset type and concurrency as an argument.
There are three types of ResultSet types are as follows,
1) ResultSet.TYPE_FORWARD_ONLY
This is the default type and cursor can only move forward direction
2) ResultSet.TYPE_SCROLL_INSENSITIVE
The cursor can move both forward and backward direction and resulset is not sensitive to changes made by others to the database after the resultset was created.
3) ResultSet.TYPE_SCROLL_SENSITIVE
The cursor can move both forward and backward direction and resultset is sensitive to changes made by others to the database after the resultset was created.
Based on the concurrency,
1) ResultSet.CONCUR_READ_ONLY
The resultset is read only and this is the default concurrency type.
2) ResultSet.CONCUR_UPDATABLE
This resultset method is used to update the rows data.
Syntax of connection methods to create statements with desired Resultset types,
Statement st = con.createStatement(int RSType, int RSConcurrency);
PreparedStatement pst = con.prepareStatement(String Sql, int RSType, int RSConcurrency);
CallableStatement cst = con.prepareCall(String sql, int RSType, int RSConcurrency);
11) What is ResultSetMetaData in Java?
The ResultSetMetaData is an interface and it can used to get information about the name of columns, number of columns and data types of the columns in ResultSet object.
Example : --
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con =DriverManager.getConnection("mysql:jdbc//localhost:3306
//dbName,","username","password");
pst = con.prepareStatement("SELECT * FROM employee");
rs = pst.executeQuery();
while (rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(rsmd.getColumnCount()); //column count
System.out.println(rsmd.getColumnName());//column name
System.out.println(rsmd.getColumnTypeName());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
}
output : - 3
emp_useraname
VARCHAR
12) How do you handle transaction in JDBC using connection interface ?
By default auto-commit is turned ON which commits to database for every SQL statement is sent/issued to database.
Turned OFF auto-commit, commit to the database at the end using manually commit() method.
connection.setAutoCommit(false);
// do some operations like insert, update, delete so on
// in finally block do commit
connection.commit();
If any error in between transaction, then rollback the connection using rollback() method.
connection.rollback();
13) Difference between java.sql.Time, java.sql.Timestamp and java.sql.Date.
java.sql.Time represents only time information e.g hours,minutes and seconds without any date information. java.sql.Date represents only date information like day, month and year, no time information. java.sql.Timestamp represents both time and Date including nanoseconds also.
java.sql.Time and java.sql.Timestamp extends java.util.Date but java.sql.Date is independent.
14) What is the design pattern followed by JDBC ?
JDBC follows a bridge design patten.
15) How to insert images into database using BLOB datatype? Write a sample code.
BLOB is a datatype, stands for Binary Large Object. and it can be used for storing large amount of data into the database like images, voice and graphical data.
CLOB is also a datatype and stands for Character Large Object and it can be used for storing single byte character data like text files, pdf, doc and docx.
Below code is a sample example of insert a image into database using BLOB datatype(using mysql database).
Table Structure:--
CREATE TABLE candidate_photo
(reg_id INT, user_photo BLOB);
16) How to read the image from the database? write a sample code.
Connection con = null;
This driver converts JDBC calls to the client API for the database servers. We should have database client API installed. This driver also not preferred driver because extra dependency on client API.
3) Pure Java driver (Network protocol driver)
This driver sends the JDBC calls to a middle-ware server that can connect to different type of databases. We should have a middle-ware server installed in the client machine. This adds to a extra network calls and slow performance and thatsy this driver also not using widely.
4) Thin driver or type 4 driver
This driver converts JDBC calls directly into the vendor specific database protocol . So, it is known as thin driver. It is fully written in Java language.
3) What are the steps in java to make JDBC connectivity ?
The following are the basic steps to connecting the java application with database using JDBC.
1) Register the driver
The forName() method of class is used to register the driver class. This method is used to dynamically load the driver class.
Syntax : -
public static void forName(String className) throws ClassNotFoundException
Example of forName() method of MySQL Driver :--
Class.forName("com.mysql.jdbc.Driver");
2) Create the Connection object
The getConnection() method of DriverManager class is used to create the Connection object.
Syntax :-
1) public static Connection getConnection(String url) throws SQLException
2) public static Connection getConnection(String url, String name, String password) throws SQLException
Example of getConnection() method of MySQL driver:--
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/dbName", "username", "password");
3) Create the Statement object
The Statement is an interface to execute the query with database. There are three types of statement in Java 1) Statement 2) PreparedStatement 3) CallableStatement
Example :
Statement st = con.createStatement(); //Statement
PreparedStatement pst = con.prepareStatement(String sqlQuery); //PreparedStatement
CallableStatement cst = con.prepareCall(String sqlQuery); // CallableStatement
4) Executing the Query
The execute(), executeQuery() and executeUpdate() method of Statement interface is used to execute the queries to the database.
Example:--
ResultSet rs = st.executeQuery();
int i = st.executeUpdate();
boolean success = st.execute();
5) Closing the Connection
The close() method of the Connection interface is used to close the connection object.
con.close();
4 ) What does setAutoCommit(false) do?
A JDBC Connection is created in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and will be automatically committed as soon as it is executed. If you require two or more statements to be grouped into a transaction then you need to disable auto-commit using below command,
con.setAutoCommit(false);
Once auto-commit mode is disabled , no SQL statements will be committed until you explicitly call the commit method.
Example:--
Connection con = null;
PreparedStatement insertStmt = null;
PreparedStatement updateStmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306//dBName","username","password");
con.setAutoCommit(false);
insertStmt = con.prepareStatement(String insertQuery);
insertStmt.executeQuery();
updateStmt = con.prepareStatement(String updateQuery);
updateStmt.executeUpdate();
con.commit();
} catch(Exception) {
con.rollback();
} finally {
if(insertStmt != null) {
insertStmt.close();
}
if (updateStmt != null) {
updateStmt.close();
}
if (con != null) {
con.close();
}
} 5) How to get the Database server details in Java?
We can use DatabaseMetaData interface to get the database server details. When database connection is created then you call meta data object by calling getMetaData() method. There are so many methods available in DatabaseMetaData to get the product name, major version, minor version and product version etc.
DatabaseMetaData dbmd = con.getMetaData();
System.out.println("Database Product Name "+dbmd.getDatabaseProductName);
System.out.println("Database Product Version"+dbmd.getDatabaseProductVersion);
System.out.println("Database Major Version "+dbmd.getDatabaseMajorVersion);
System.out.println("Database Minor Version "+dbmd.getDatabaseMinorVersion);
// Driver information
System.out.println("Driver Name "+dbmd.getDriverName);
System.out.println("Driver Version "+dbmd.getDriverVersion);
System.out.println("Driver Major Version "+dbmd.getDriverMajorVersion);
System.out.println("Driver Minor Version "+dbmd.getDriverMinorVersion);
6) Difference between Statement and PrepareStatement in Java?
Statement : - This statement is used for executing a static SQL statements and it can not accept parameters at run time. Statement is slower as compared to preparedStatement. Statement enforces SQL injection . This statement can't be used for storing/retrieving images and files in database
PreparedStatement:- This PreparedStatement is used for executing precompiled SQL statement and it can accept parameters at run time. Execution process is fast compared to Statement. PreapredStatement prevents the SQL injection. This statement can be used for storing/retrieving images and files in database.
7) Why PreparedStatement is faster than Statement ?
If you have to run the same Statement with multiple times, with different data then you can use PreparedStatement because it can validate query only once and run multiple times. i.e In PreparedStatement pre compilation is done only once so it can take less execution time. But in simple Statement every time it can validate and runs the query so it is slow compared to PreparedStatement.
8) How can we execute stored procedures and functions?
We can execute stored procedures and functions by using Callable statement interface. This statement can extends Prepared Statement interface.
For example,
CallableStatement callStmt = con.prepareCall("{call calculateTotalAmount(?,?)}") ;
In the above query, calculateTotalAmount is the stored procedure or function.
9) How the ResultSet Object works in JDBC ?
Refer this Working with JDBC ResultSet object
10) What are different types of ResultSet ?
ResultSet contains the result of the SQL query. There are different types of ResultSet object that we can get based on the user input while creating the Statement. In createStatement() and preparedStatement() methods of Statement and PreparedStatement, we are passing Resultset type and concurrency as an argument.
There are three types of ResultSet types are as follows,
1) ResultSet.TYPE_FORWARD_ONLY
This is the default type and cursor can only move forward direction
2) ResultSet.TYPE_SCROLL_INSENSITIVE
The cursor can move both forward and backward direction and resulset is not sensitive to changes made by others to the database after the resultset was created.
3) ResultSet.TYPE_SCROLL_SENSITIVE
The cursor can move both forward and backward direction and resultset is sensitive to changes made by others to the database after the resultset was created.
Based on the concurrency,
1) ResultSet.CONCUR_READ_ONLY
The resultset is read only and this is the default concurrency type.
2) ResultSet.CONCUR_UPDATABLE
This resultset method is used to update the rows data.
Syntax of connection methods to create statements with desired Resultset types,
Statement st = con.createStatement(int RSType, int RSConcurrency);
PreparedStatement pst = con.prepareStatement(String Sql, int RSType, int RSConcurrency);
CallableStatement cst = con.prepareCall(String sql, int RSType, int RSConcurrency);
11) What is ResultSetMetaData in Java?
The ResultSetMetaData is an interface and it can used to get information about the name of columns, number of columns and data types of the columns in ResultSet object.
Example : --
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con =DriverManager.getConnection("mysql:jdbc//localhost:3306
//dbName,","username","password");
pst = con.prepareStatement("SELECT * FROM employee");
rs = pst.executeQuery();
while (rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(rsmd.getColumnCount()); //column count
System.out.println(rsmd.getColumnName());//column name
System.out.println(rsmd.getColumnTypeName());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
}
output : - 3
emp_useraname
VARCHAR
12) How do you handle transaction in JDBC using connection interface ?
By default auto-commit is turned ON which commits to database for every SQL statement is sent/issued to database.
Turned OFF auto-commit, commit to the database at the end using manually commit() method.
connection.setAutoCommit(false);
// do some operations like insert, update, delete so on
// in finally block do commit
connection.commit();
If any error in between transaction, then rollback the connection using rollback() method.
connection.rollback();
13) Difference between java.sql.Time, java.sql.Timestamp and java.sql.Date.
java.sql.Time represents only time information e.g hours,minutes and seconds without any date information. java.sql.Date represents only date information like day, month and year, no time information. java.sql.Timestamp represents both time and Date including nanoseconds also.
java.sql.Time and java.sql.Timestamp extends java.util.Date but java.sql.Date is independent.
14) What is the design pattern followed by JDBC ?
JDBC follows a bridge design patten.
15) How to insert images into database using BLOB datatype? Write a sample code.
BLOB is a datatype, stands for Binary Large Object. and it can be used for storing large amount of data into the database like images, voice and graphical data.
CLOB is also a datatype and stands for Character Large Object and it can be used for storing single byte character data like text files, pdf, doc and docx.
Below code is a sample example of insert a image into database using BLOB datatype(using mysql database).
Table Structure:--
CREATE TABLE candidate_photo
(reg_id INT, user_photo BLOB);
Connection con = null;
PreparedStatement pst = null;
InputStream is = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverMabnager.getConnection("jdbc:mysql://localhost:3306//dbName", "username", "password");
pst = con.prepareStatement("insert into candidate_photo values(?,?)");
pst.setInt(1, 1);
is = new FileInputStream(new File("D://Documents//Image.jpg"));
int fileLength = (int) new File("D://Documents//Image.jpg").length();
pst.setBinaryStream(2, is, fileLength);
pst.execute();
}
catch(Exception e ) {
e.printStackTrace();
}
finally {
if (pst != null ) {
pst.close();
}
if (is != null) {
is.close();
}
if (con != null) {
con.close();
}
}16) How to read the image from the database? write a sample code.
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
ResultSet rs = null;
InputStream is = null;
FileOutputStream os = null;
FileOutputStream os = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverMabnager.getConnection("jdbc:mysql://localhost:3306//dbName", "username", "password");
pst = con.prepareStatement("select user_photo from candidate_photo where reg_id=1");
rs = pst.executeQuery();
if (rs.next()) {
is = rs.getBinaryStream(1);
}
FileOutputStream os = new FileOutputStream("D://Documents//Image1.jpg");
byte[] content = new byte[1024];
int size = 0;
while((size = is.read(content)) != -1){
os.write(content, 0, size);
}
is = rs.getBinaryStream(1);
}
FileOutputStream os = new FileOutputStream("D://Documents//Image1.jpg");
byte[] content = new byte[1024];
int size = 0;
while((size = is.read(content)) != -1){
os.write(content, 0, size);
}
}
catch(Exception e ) {
e.printStackTrace();
}
finally {
if (pst != null ) {
pst.close();
}
if (is != null) {
is.close();
}
if (os != null) {
if (os != null) {
is.close();
}
if (con != null) {
con.close();
}
}
Nice tutorial, please keep on adding the posts.
ReplyDeleteStill want more clarity on callable statement and how result set is work (if possible please explain with example).
Can you pls explain how pagination works with example.
Sure Kiran,i will update as soon as possible.
ReplyDelete