JDBC interview questions and answers

1. What is the JDBC?
A. Java Database Connectivity (JDBC) is a standard Java API to interact with relational database form Java. JDBC has set of classes and interfaces which can use from Java application and talk to database without learning RDBMS details and using Database specific JDBC Drivers.

2. What are the new features added to JDBC 4.0?
A. The major features added in JDBC 4.0 includes:
       • Connection management enhancements
       • Support for RowId SQL type
       • provides SQL DataSet implementation using annotations
       • SQL exception handling enhancements
       • SQL XML support

3. Explain basic steps in writing a java program using JDBC?
A. JDBC makes the interaction with RDBMS simple and interactive. When a java application needs to access database,
  • Loads RDBMS specific JDBC driver because this driver actually communicates with the database.
  • Opens the connection to database which is then used to send SQL statements and get results back.
  • Create JDBC statements object. This object contains SQL query.
  • Execute statement which returns ResultSet. ResultSet contains couple of database table as a result of SQL query.
  • Process the ResultSet.
  • Close the connection.

4. Explain the JDBC architecture?
A. JDBC architecture consists of two layers:
  • The JDBC API, which will provides the application-to-JDBC Manager connection.
  • The JDBC Driver API, which supports JDBC Manager-to-Driver Connection.

       The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous database. The JDBC driver manager ensures that the correct driver is used access each DataSource. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases.

5. What are the main components of JDBC?
A. The life cycle of a JDBC consists of the following phases,
  • DriverManager: manages a list of database drivers. Matches connection requests from Java application with the proper database driver using communication sub-protocol. The first driver that recognizes certain sub-protocol under JDBC will be used to establish a database connection.
  • Driver: The database communication link, handling all communications with databases. Normally, once the driver is loaded,  developer need not call it explicitly.
  • Connection: Interface with all methods for contacting the database. The connection object represents communication context, i.e., all communications with database is through connection object only.
  • Statement: Encapsulates SQL statements which are passed to the database to be parsed, complied, planned and executed.
  • ResultSet: ResultSet represents set of row's retrieved due to query execution.
6. How the JDBC application works?
A. JDBC application can be logically divided into two layers:
         1. Driver layer
         2. Application layer
  • Driver layer consists of the DriverManager class and the available JDBC drivers.
  • An application begins with requesting the DriverManager for the connection.
  • An appropriate driver is choosen and is used for establishing the connection. connection is given to the application which fails under the application layer.
  • The application uses the connection to create statement king of objects, through which SQL commands are sent to backend and obtain the results.

7. How do I load a database driver with Java/JDBC 4.0 ?
A. JDBC provides the jar file containing the driver is properly configured; just place the jar file in the classpath. Java developer no longer need to explicitly load JDBC drivers using code, like Class.forName() to register with JDBC driver. The DriverManager class takes care of this by automatically locating a suitable driver when the DriverManager.getConnection() method called. This feature is backward-compatible, So no changes are needs to the existing JDBC code.

8. What is JDBC Driver interface?
A. JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendor driver must provide implementation of the Connection, Statement, PreparedStatement, CallableStatement, ResultSet and Driver which will present under the java.sql path.

9. What does the Connection object represents?
A. The connection object represents the communications context, i.e., all communications with database is through connection object only.

10. What is Statement?
A. Statement act like a vehicle through which SQL commands can sent, Through the connection object we create statement kind of objects.

         Statement stmt = conn.createStatement();

This method returns object we create statement king of objects.

11. What is PreparedStatement?
A. PreaptedStatement is an SQL statement that is precompiled by the database. Through pre-compilation, PreapredStatement improves the performance of SQL commands that are executed multiple times. Once compiled, PreparedStatement can be customized prior to each execution by altering predefined SQL parameters.

PreparedStatement pst = conn.prepareStatement("UPDATE EMP SET SALARY=? WHERE ID=?");
  pst.setBigDecimal(1,100000.00);
  pst.setInt(2,1545);

Here, conn is an instance of the Connection class and “?” represents parameters. These parameters must be specified before execution.

12.What is the difference between a Statement and PreparedStatement?
A: 
        Statement
      PreparedStatement
A standard statement is used to create a Java representation of a literal SQL statement and execute it on the database.
A PreparedStatement is a precompiled statement. This means that when the PreparedStatement is executed, the RDBMS can just run the PreparedStatement SQL statement without having to compile it first.
Statement has to verify its metadata against the database every time.
While a PreparedStatement has to verify it metadata against the database only once.
If you want to execute the SQL statement once go for STATEMENT.
If you want to execute a single SQL statement multiple number of times, then go for PREPAREDSTATEMENT. PreparedStatement objects can be reused with passing different values to the queries.

13. What are callable statements?
A. CallableStatement are used from JDBC application to invoke stored procedures and functions.

14. How to call a stored procedure from JDBC?
A. PL/SQL stored procedures are called from with in JDBC programs by means of the prepareCall() method of the Connection object created. A call to this method takes variable bind parameters as input parameters as well as output variables and creates and object instance of the CallableStatement class. The following line of code illustrates this,

CallableStatement cst = conn.prepareCall("{call proname(?,?,?)}");

15. What are types of JDBC driver?
A. There are four types of drivers defined by JDBC as follows,
  • Type 1: JDBC/ODBC – these require an ODBC (Open DataBase Connectivity) driver for the database to be installed. This driver works by translating the submitted queries into equivalent ODBC queries and forwards them via native API calls directly to ODBC driver. This provides no host redirection capability.
  • Type2: Native API (partly-java driver) – this type of driver uses a vendor-specific driver or database API to interact with the database. For an example of such an API is Oracle OCI (Oracle Call Interface). This is also provides no host redirection.
  • Type3: Open Protocol-Net – This is not vendor specific and works by forwarding database requests to a remote database source using a net-server component. Net-server component accesses the database is transparent to the client. Client driver communicates with the net server using a database-independent protocol and the server translates this protocol into database calls. Type3 driver can access any database.
  • Type4: Proprietary Protocol-Net (Pure Java driver) – this has a same configuration as a type3 driver but uses a wire protocol specific to a particular vendor and hence can access only that vendor database. Again this all transparent to the client.

16. Which type of JDBC driver is the fastest one?
A. JDBC Net pure java driver (Type4) is the fastest driver because it converts the JDBC calls into vendor specific protocol calls and it directly interacts with the database.

17. Does JDBC-ODBC Bridge support multiple concurrent open statements per connection?
A. You can open only one statement object per connection when you are using the JDBC-ODBC Bridge.

18. Which is the right type of driver to use and when?
  • Type1 driver is handy for prototyping.
  • Type2 driver adds security, caching and connection control.
  • Type3 and Type4 drivers need no pre-installation.

19. What are the standard isolation levels defined by JDBC?
A. The values are declared in the class java.sql.Connection and are,
  • TRANSACTION_NONE
  • TRANSACTION_READ_COMMITTED
  • TRANSACTION_READ_UNCOMMITTED
  • TRANSACTION_REPEATABLE_READ
  • TRANSACTION_SERIALIZABLE

20. What is ResultSet?
A. ResultSet represents set of rows retrieved due to query execution.
       ResultSet rs = st.executeQuery(sqlQuery);

21. What are the types of ResultSet?
A. The values are defined in Connection class, that are
  • TYPE_FORWARD_ONLY specifies that the resultset is not scrollable, rows with in it can be advanced only in the forward direction. This is always insensitive.
  • TYPE_SCROLL_INSENSITIVE specifies that the resultset is scrollable in either direction but is insensitive to changes committed by other transactions or other statements in the same transaction.
  • TYPE_SCROLL_SENSITIVE specifies that the resultset is scrollable in either direction and is affected by changes committed by other transaction or statement within the same transaction.

21.What’s the difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE?
TYPE_SCROLL_INSENSITIVE
TYPE_SCROLL_SENSITIVE
An insensitive resultset is like the snapshot of the data in the database when query was executed.
A sensitive resultset does NOT represent a snapshot of data, rather it contains points to those rows which satisfy the query condition.
After we get the resultset the changes made to data are not visible through the resultset, and hence they are known as insensitive.
After we obtain the resultset if the data is modified then such modifications are visible through resultset.
Performance not effected with insensitive.
Since a trip is made for every ‘get’ operation, the performance drastically get affected.

22. What is RowSet?
A. A rowset is an object that encapsulates a set of rows from either Java Database Connectivity ResultSet or tabular data sources like a file or spreadsheet. Rowset support component-based development models like JavaBean, with a standard set of properties and an event notification mechanism.

24. What are the different types of RowSet?
A. There are two types of RowSet are there. That are,
  • Connected – A connected RowSet object connects to the database once and remains connected until the application terminates.
  • Disconnected – A disconnected RowSet object to database, executes the query to retrieve data from the database and then closes the connection. The program may change the data in a disconnected RowSet while disconnecting. Modified data can be updated in the database after a disconnected RowSet re-establishes the connection with the database.

25. What is the need of BatchUpdates?
A. The BatchUpdates features allow us to group SQL statements together and send to database server in one single trip.

26. What is a DataSource?
A. DataSource object is representation of the data source in the Java programming language. In basic terms,
  • DataSource is the facility to store data.
  • DataSource can be refereced by JNDI.
  • DataSource may points to RDBMS, file system, any DBMS etc,.

27. What are the advantages of DataSource?
A. Few advantages of using DataSource. That are,
  • An implementation does not need to hardcore driver information, as it does with the DriverManager.
  • A DataSource implementation can easily change the properties of Data Source. For example: there is no need to modify application code when making changes to the database details.
  • DataSource facility allow developers to implement a DataSource class to take advantages of features like connection pooling and distributed transactions.

28. What is connection pooling? What is the main advantage of using connection pooling?
A. A connection pool is mechanism to reuse connections created. Connection pooling can increase the performance dramatically by reusing connection rather than creating a new physical connection each time a connection is requested.
Location: Bengaluru, Karnataka 560017, India

Comments

Post a Comment

Popular posts from this blog

Hibernate auto increment with example

In previous example we learn how to map java object with database table for manually(not using auto increment) assigned all the values.  To create primary key auto increment application by using hibernate mapping(hbm.xml) or using annotations continue reading this article.  W e need to create a database and create an employee table by using the below query. Table: CREATE table EMPLOYEE( eid integer ( 10 ) primary key not null , firstname varchar ( 30 ), lastname varchar ( 30 ), designation varchar ( 30 ), salary double( 10 , 2 )); Here, we will create a bean/POJO (Plain Old Java Object) class of employee. Employee.java: package com . javatbrains . hibernate ; import java.io.Serializable ; /*This is the POJO class. It contains only Getter and Setter methods*/ public class Employee implements Serializable { private static final long serialVersionUID = - 75885815725314443L ; private int eid ; private String firstName ;
Read more

how to count the page views by using JSP

To count no.of page's are viewed by user in your application is a challenge in real time application. In this page we will see how to count the page views when user has viewed the page.           Here, we will do one example how many times user has visited each page in our application. In this post we will come to know, how to count the total application page views as well as individually each pages count.           Let us assume my web application name is "Page Counter". For creating the web application in eclipse IDE, Click here . This Page Counter contains three pages, assume that are the different pages in an application. But, there is the link between each other files for navigating from one page to another. Also it will show the total pages view count and individual page view count in all the pages. Let us see, below is my first jsp file,           You can declare the below content inside the < body > tag. Rest of the content in every page will b
Read more

Multithreading in java with example

Multithreading  is one of the most important concept in core java. In this article we will learn what is multithreading? , what is the use of it? and What is the use of Synchronization and when to use it?  with detailed examples. At a time, two or more threads are accessing the same object is called as Multithreading  in Java .  First, we will create two threads for two objects. It is also possible to run two or more threads on a single class object. In this case, there is a possibility to get unreliable results. If the two threads are perform same task, then they need same object to be executed each time. For your better understanding, take an example of any reservations like, railway, movie ticket booking,etc. Let us think only one berth is available in a train and two passengers are asking for that berth. The first person has sent a request to allocate that ticket/berth to him. At the same time, the second person also sent a request to allocate that ticket/berth to him
Read more

How to retrieve data from table by using JDBC with example

To retrieve data from mysql data table by using jdbc, we need to confirm weather which table data we need to retrieve. To confirm open your mysql and view all database names by using the ' show databases ' commands in mysql. Then select which is your database name from showing list of database names, by using the ' use <database name> ' command in mysql. Then we need to see all your existing tables by executing the ' show tables ' command. This command gives you the list of existing tables in your selected database. You need to use existing table only you can follow with that. But, here we will see with entirely new which is not existing in mysql database. Now, We will create simple mysql table with minimal columns. create table emp(eno integer(10), ename varchar(30), sal float); The above command creates a new table, we will move further to insert records into emp table by executing the insert which we mentioned in the below.
Read more

Prime, Fibonacci and Factorial number with example in java

Prime number, Fibonacci series and Factorial number programs are most commonly asked questions in interview. Read this article to know what is and how to write programs for prime number, fibonacci series and factorial number. Prime Number: prime number is natural number greater than 1 that has no positive divisor other than 1 and itself. A natural number greater than 1 is not a prime number, is called Composite number . For example, 7 is a prime number. Because it can divide with 1 and 7 only. Where as 8 is composite number. Since it has the divisor 2 and 4 in addition to the 1 and 8. The below example represents the finding the passing number is prime number or not. If the passing number is prime number it will print true otherwise it will print false. package com . javatbrains . practice ; public class PrimeNumber { public boolean isPrimeNumber ( int number ) { if ( number <= 1 ) return false ; // There's only one
Read more

How to insert images into database using JDBC?

How to insert images into database table by using JDBC? To know about these topic follow the below lines,           Before going to write a JDBC program for inserting images into database table, we need to create a specific table for inserting images column datatype. For creating the database table, execute the below command in MySQL command prompt. 1 2 3 4 CREATE TABLE ` images ` ( ` image_id ` int ( 11 ) NOT NULL AUTO_INCREMENT, ` image_flag ` int ( 11 ) NOT NULL , ` image ` longblob NOT NULL , PRIMARY KEY ( ` image_id ` ));           After executing the above query in MySQL command prompt, execute the below query to know weather the above table query is created or not. 1 Show tables;            When you have executed the above query that will show you the list of table which all exists in the current database. Current table is present in that list, then we will move to write the JDBC program to insert images. Otherwise execute the above C
Read more

How to sort list of objects in java with examples

We could achieve list of objects sorting in java by using sort() method which is available under Collections class as well as by using Comparable and Comparator  interfaces. Comparable and Comparator are two interfaces in java used to sort list of objects. These interfaces are available under java.util package along with Collections  class and other classes and interfaces. Comparable interface contains the method of       public int compareTo(Object obj) Comparator interface contains the method of       public int compare(Object obj1, Object obj2) The compare() and compareTo() methods will return negative integer(-1), or zero(0) or positive integer(1). When the first obj1 is less than obj2 method will return negative integer. When obj1 is equals to obj2 will return zero and when obj1 is grater than obj2 it will return positive integer. If it's objects are stored in any Collection classes like ArrayList, HashSet in Array and that time we need to use compare(
Read more

String interview questions and answers

String interview questions and answers are placed the current post as of my experience in interviews. This will helps who are trying to attend interview as fresher as well as experience. If any one is facing any other questions which are not present in the current page, please drop me an email to " subbareddynallamachu@gmail.com ".  Q1:  String is a class or data type? A: String is a class in java.lang package. But, in java, all classes are also are considered as data types. So we can take String as data type also. Q2:  What is the difference between == and equals() methods in java? A: == operator compares the references of the String object. It does not compare the content of the object. equals() method compares the contents. While comparing the Strings, equals method should be used as it yields the correct result. Q3:  What is String constant pool? A: String constant pool is a separate block of memory where the String objects are held by JVM. If a String object is create
Read more

Exception in thread "main" java.lang.NoClassDefFoundError: javax/transaction/SystemException

Exception in thread "main" java.lang.NoClassDefFoundError: javax/transaction/SystemException error we got in Hibernate 4.3 to work with my MySQL database. We are using hibernate code generation tool in Eclipse and we are able to connect to the database using the Hibernate configuration file. When we try to run code in my Main class application is connecting to the database, but transaction is failing and we got the following error: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Exception in thread "main" java . lang . NoClassDefFoundError : javax / transaction / SystemException at java . lang . Class . forName0 ( Native Method ) at java . lang . Class . forName ( Unknown Source ) at org . jboss . logging . Logger . getMessageLogger ( Logger . java : 2248 ) at org . jboss . logging . Logger . getMessageLogger ( Logger . java : 2214 ) at org . hibernate . cfg . Configuration .< clinit >( Configuration . java : 184 ) at be . comp . permanenti
Read more

Store file into table by using JDBC with example

To store large data files or text files into database table, we will use CLOB (Charecter Large Object) datatype of SQL . By using CLOB it is possible to save entire text book or a file or resume into a column of data table. If your using MySQL database instead of CLOB use LONGTEXT to save file into datatable. For saving files into a data table, we need to create one table which will contains a column type as CLOB to store files. Here, is the query to create a data table. 1 2 3 4 create table myfiles( file_id int ( 10 ), file_name varchar ( 30 ), file_type LONGTEXT); Once table creation completes, these table contains the 3 columns one is file_id represents the file id, file_name represents the name of the file and finally file_type will get save entire file into the table column. Now, we will move to write a JDBC program which will saves the file into the newly creating table. Here is the JDBC program, 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Read more