JDBC
JDBC Interview Questions :-
1) What is JDBC?
Ans:-JDBC is JavaDatabaseConnection .JDBC is a technique which can communicate with the database and we can perform different database operation
2)What are drivers available?
Initially we have 256 drivers are there,which are organized into four types.
They are:
I)JDBC-ODBC Bridge driver--Type-1
II)Native API Partly-Java driver--Type-2
III)JDBC-Net Pure Java driver--Type-3
IV)Native-Protocol Pure Java driver--Type-4
3)Explain steps for connecting with the database?
1.loading the driver:-
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");(this is a type-1 driver)
Class.forName("oracle.jdbc.driver.OracleDriver");(this is a type-4 driver)
When the driver is loaded, it registers itself with the java.sql.DriverManager class as an available database driver
2.Preparing URL:-
String url="jdbc:odbc:DSN"(this URL is for Type-1 driver)
String url="jdbc:oracle:thin:@hostname:portnumber:servicename");(this URL is for Type-4 driver)
3)Making a connection with database :-
To open a connection to a given database DriverManager.getConnection( ) method is used.
Connection con = DriverManager.getConnection (url,“user”,”password”);
After getting the connection perform the database operations by using Statement,PreparedStatement or CallableStatement etc.
4)close the connection by using close() method of Connection interface.
con.close();
4)How many types of statements we have in JDBC?
ans:We have three types of statements are there.
1)Statement -- To be used createStatement( ) method for executing single SQL statement
Statement st=con.createStatement();
2)PreparedStatement -- To be used prepareStatement( ) method for executing same SQL statement over and over
PreparedStaement ps=con.prepareStatement("sql query");
3)CallableStatement -- To be used prepareCall( ) method for multiple SQL statements over and over
CallableStatement csmt=con.prepareCall("{call procedurename(?,?....)}");
5)What Class.forName will do while loading drivers?
ans: It is used to create an instance of a driver and register the driver with the DriverManager.
When you have loaded a driver, it is available for making a connection with a DBMS.
6) How can you retrieve data from the ResultSet?
Ans: JDBC returns the results in a ResultSet object, we have to declare an instance of the ResultSet to hold the results.
Eg.
ResultSet rs = con.createStatement().executeQuery("select * from employee");
while(rs.next())
{
rs.getxxx();
}
We are By using get() methods we can retrieve the data from the table.
7)Why use PreparedStatement in Java JDBC ?
ans:-
PreparedStatement is several ways to execute SQL queries using JDBC API. it is used for general purpose queries, executing parametric query.
Prepared Statement queries are pre-compiled on database and there access plan will be reused to execute further queries which allows them to execute much quicker than normal queries generated by Statement object.
we want to insert 4 records into the database then we use Statement interface then we will do recompile and execute 4 times .So resolve this problem we are using PreparedStatement interface.
Note:By using PreparedStatement then no need to execute the sql query multiple times.
8) What is the mean of “dirty read“ in database?
if one transaction updated the value(changed)and then second transaction read this value before the original change has been commited or rollback .This is known as dirty read.In this case the first transaction read may be changed or rollback and the second transaction read invalid value
9) How cursor works in scrollable result set?
ans:-
In JDBC 2.0 API is added to move cursor in resultset backward forward and also in a particular row .
We have three constant in result set for move cursor.
·TYPE_FORWARD_ONLY: creates a nonscrollable result set, the cursor moves forward direction only.
·TYPE_SCROLL_INSENSITIVE : a scrollable result set does not reflects changes why because that are made to it while it is open.
·TYPE_SCROLL_SENSITIVE: a scrollable result set reflects changes why because that are made to it while it is open
10)What setAutoCommit does?
ans:- When a connection is established, it is in auto-commit mode. the individual SQL statement is treated as a transaction and it will be automatically committed after it is executed.
Note:-The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode
Eg.
con.setAutoCommit(false);//con is Connection variable
Once disable the auto-commit mode then no transaction will be committed until you call the method commit.
Eg.
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement(
"UPDATE Employee SET EID= ? WHERE ENAME LIKE ?");
ps.setInt(1, 50);
ps.setString(2, "xyz");
ps.executeUpdate();
con.commit();
con.setAutoCommit(true);//Here automcommit mode is enabled
11)How to call a Strored Procedure from JDBC?
step1:To create a CallableStatement object. with Statement an and PreparedStatement objects, it will be open the connection. this object contains a call to a stored procedure;
Eg.
CallableStatement cs = con.prepareCall("{call procedure}");
12)How to Retrieve Warnings?
ans:- SQLWarning deal with database access warnings this objects is a subclass of SQLException this Warnings do not stop the execution of an application.
A warning can be reported on a Connection , Statement object (including PreparedStatement and CallableStatement objects), ResultSet objects. Each of these Interface has a getWarnings method, we must invoke in order to see the first warning reported on the calling object
Eg.
SQLWarning warn = stmt.getWarnings();
if (warn != null) {
System.out.println("\n~~~you are getting Warning~~~\n");
while (warn != null) {
System.out.println("Message: " + warn.getMessage());
System.out.println("SQLState: " + warn.getSQLState());
System.out.println(warn.getErrorCode());
}
}
13) How can you Move the Cursor in Scrollable Result Sets ?
ans : In JDBC 2.0 API is added to move cursor in resultset backward as well as forward and also in a particular row .
Eg:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEE");
The first argument is one of three constants added to the ResultSet to indicate the type of the ResultSet object:
TYPE_FORWARD_ONLY,
TYPE_SCROLL_INSENSITIVE ,
TYPE_SCROLL_SENSITIVE .
The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE .
Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, the cursor moves only forward direction. we do not specify any constants for the type and updatability of a ResultSet object, we will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
14)What’s the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE?
ans: we'll get a scrollable ResultSet object if we are specify these ResultSet constants.The main difference between the two has to do with whether a ResultSet reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes.
Generally a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open.another one is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened
Eg:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT EID FROM EMPLOYEE");
rs.afterLast();
while (rs.previous()) {
String name = rs.getString("ENAME");
float price = rs.getFloat("ESAL");
System.out.println(name + " " + price);
}
15)How to Make Updates to Updatable Result Sets?
ans: In JDBC 2.0 API is the ability to update rows in a result set using methods.we need to create a ResultSet object that is updatable. we supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.
Eg.
Connection con = DriverManager.getConnection(URL,User_name,Pass_word);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEE");
16)How do I start debugging problems related to the JDBC API?
ans:- JDBC calls are perform is to enable JDBC tracing. The JDBC trace contains a detailed listing of the activity occurring in the system that is related to JDBC operations.
If we are using the DriverManager facility to establish your database connection, we use the DriverManager.setLogWriter() method to enable tracing of JDBC operations. If we are use a DataSource object to get a connection, we are use the DataSource.setLogWriter() method to enable tracing.
17)What is JDBC 2.0?
The JDBC 2.0 is Application Programming Interface (API),we will be able the following
•Scroll forward and backward in a result set or move to a specific row (TYPE_SCROLL_SENSITIVE, last(), absolute(),previous(), relative(), etc....)
•Make updates to database tables using methods in the Java programming language instead of using SQL commands.( insertRow(),updateRow(), deleteRow(),etc....)
•Send multiple SQL statements to the database as a unit or batch (addBatch(), executeBatch())
•Use the new SQL3 datatypes as column values like Blob, Clob, Array, Struct, Ref.
18)How to insert and delete a row programmatically?
ans:-BY using resultSet is updatable
1. move the cursor to the specific position.
rs.moveToCurrentRow();
2. set value for each column.
rs.moveToInsertRow();//to set up for insert
rs.updateString("col1" "strvalue");
rs.updateInt("col2", 10);
...
3. call insertRow() method to finish the row insert process.
rs.insertRow();
To delete a row: cursor move to the specific position and call deleteRow() method:
rs.absolute(5);
rs.deleteRow();//delete row 5
To see the changes call refreshRow();
rs.refreshRow();
19)What are four types of JDBC driver?
ans:
Type 1 Drivers:
the jdbc-odbc bridge. ODBC to transfer the SQL calls to the database and also often rely on native code. It is not a serious solution for an application
Type 2 Drivers :
Use the existing database API to communicate with the database on the client. Faster than Type 1, but need native code and require additional permissions to work in an applet. Client machine requires software to run.
Type 3 Drivers
JDBC-Net pure Java driver:
It translates JDBC calls to a DBMS-independent network protocol, which is then translated to a DBMS protocol by a server. Flexible. Pure Java and no native code.
Type 4 Drivers
Native-protocol pure Java driver. It converts JDBC calls to network protocol used by DBMSs. it will allow a direct call from the user to the DBMS server. It don't need any special native code on the user.
20)Which type of JDBC driver is the fastest one?
ans: JDBC Net pure Java driver(Type 4 driver) is the fastest driver because it converts the jdbc calls into netowk protocol calls and it directly interacts with the database.
21)What is the query used to display all tables names in SQL Server?
ans: select * from information_schema.tables
22)There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set?
No, we can easily find the number of rows by using scrollable result set and the methods.
i)rs.last() ii)rs.getRow() . the result is not scrollable, we can either count the rows by iterating through the result set or get the number of rows by submitting a query with a COUNT column in the SELECT clause.
23)What are the standard isolation levels defined by JDBC?
The values are defined in the class java.sql.Connection and are:
-> TRANSACTION_NONE
->TRANSACTION_READ_COMMITTED
-> TRANSACTION_READ_UNCOMMITTED
->TRANSACTION_REPEATABLE_READ
->TRANSACTION_SERIALIZABLE
24)What isolation level is used by the DBMS when inserting, updating and selecting rows from a database?
we are not explicitly set the isolation level the DBMS default is used. we can determine the default using DatabaseMetaData.getDefaultTransactionIsolation()
for the current Connection Connection.getTransactionIsolation () for change the transcation Connection.setTransactionIsolation(int level).
25)How can I write to the log used by DriverManager and JDBC drivers?
By using DriverManager.println(String message);
26)How do I check in my code whether a maximum limit of database connections have been reached?
Use DatabaseMetaData.getMaxConnections() and then compare to the number of connections currently open.
Note the return value of zero is unlimited or,unfortunately, unknown. if the driverManager.getConnection() will throw an exception if a Connection can not be obtained.
27)How do I disallow NULL values in a table?
ans:-Null capability is a column integrity constraint, normally applied at table creation time. Most tables allow a default value for the column.
The following SQL statement shows the NOT NULL constraint:
CREATE TABLE Employee(
Type VARCHAR(25) NOT NULL,
Pounds INTEGER NOT NULL,
Price NUMERIC(5, 2) NOT NULL
);
Note: that some databases won't allow the constraint to be applied after table creation.
28)What is DML?
DML is an abbreviation for Data Manipulation Language. This portion of the SQL standard is concerned with manipulating the data in a database as opposed to the structure of a database. the DML's are SELECT, INSERT, DELETE, UPDATE, COMMIT and ROLLBACK
29)How do I create a java.sql.Date object?
java.sql.Date extends from java.util.Date There are two methods to create a Date object.
1) use a Calendar object, setting the year, month and day portions to the desired values. The hour, minute, second and millisecond values must be set to zero. by using Calendar.getTime().getTime() is invoked to get the java.util.Date milliseconds. That value is then passed to a java.sql.Date constructor:
Calendar cal = Calendar.getInstance();
// set Date portion to July 25, 2014
cal.set( cal.YEAR, 2014 );
cal.set( cal.MONTH, cal.July);
cal.set( cal.DATE, 25);
cal.set( cal.HOUR_OF_DAY, 0 );
cal.set( cal.MINUTE, 0 );
cal.set( cal.SECOND, 0 );
cal.set( cal.MILLISECOND, 0 );
java.sql.Date date =
new java.sql.Date( cal.getTime().getTime() );
2) java.sql.Date's valueOf method. valueOf() accepts a String, which must be the date in JDBC time escape format - "yyyy-mm-dd".
java.sql.Date date = java.sql.Date.valueOf( "2014-06-25" );
creates a Date object representing July 25, 2014. To use this method with a Calendar object.
java.sql.Date date = java.sql.Date.valueOf(
cal.get(cal.YEAR) + ":" +
cal.get(cal.MONTH) + ":" +
cal.get(cal.DATE) );
30)How do I create a java.sql.Time object?
java.sql.Time extends from java.util.Date,There are two methods to create a Time object.
1) Calendar object, setting the year, month and day portions to July 23, 2014, The millisecond value must also be set to zero. by using Calendar.getTime().getTime() is invoked to get the time in milliseconds. That value is then passed to a Time constructor:
Calendar cal = Calendar.getInstance();
// set Date portion to July 25, 2014
cal.set( cal.YEAR, 2014 );
cal.set( cal.MONTH, cal.JULY );
cal.set( cal.DATE, 125);
cal.set( cal.MILLISECOND, 0 );
java.sql.Time time =
new java.sql.Time( cal.getTime().getTime() );
2)Time's valueOf method. valueOf() accepts a String, which must be the time in JDBC time escape format - "hh:mm:ss".
eg:
java.sql.Time time = java.sql.Time.valueOf( "18:05:00" );
creates a Time object representing 4:05 p.m.
java.sql.Time time = java.sql.Time.valueOf(
cal.get(cal.HOUR_OF_DAY) + ":" +
cal.get(cal.MINUTE) + ":" +
cal.get(cal.SECOND) );
31)How do I get runtime information about the JDBC Driver?
ans:
using the DatabaseMetaData methods:
getDriverMajorVersion()
getDriverMinorVersion()
getDriverName()
getDriverVersion()
32)Could we get sample code for retrieving more than one parameter from a stored procedure?
Assume we have a stored procedure with this signature:
xyzprocedure (IN I1 INTEGER, OUT O1 INTEGER, INOUT IO1 INTEGER)
The code snippet to retrieve the OUT and INOUT parameters follows:
CallableStatement cs = connection.prepareCall( "(CALL xyzprocedure(?, ?, ?))" );
cs.setInt(1, 1); // set the IN parm I1 to 1
cs.setInt(3, 3); // set the INOUT parm IO1 to 3
cs.registerOutParameter(2, Types.INTEGER); // register the OUT parm O1
cs.registerOutParameter(3, Types.INTEGER); // register the INOUT parm IO1
cs.execute();
int iParm2 = cs.getInt(2);
int iParm3 = cs.getInt(3);
cs.close();
33)How do I extract SQL table column type information?
Use the getColumns() method of the java.sql.DatabaseMetaData interface to investigate the column type information of a particular table.
Note that most arguments to the getColumns() method
eg:
public static void main(String[] args) throws Exception
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url="jdbc:odbc:DSN";
Connection con = DriverManager.getConnection("url",
"User_name", "Password");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all column types for the table "sysforeignkeys", in schema
// "dbo" and catalog "test".
ResultSet rs = dbmd.getColumns("test", "dbo", "sysforeignkeys", "%");
// Printout table data
while(rs.next())
{
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbColumnName = rs.getString(4);
String dbColumnTypeName = rs.getString(6);
int dbColumnSize = rs.getInt(7);
int dbDecimalDigits = rs.getInt(9);
String dbColumnDefault = rs.getString(13);
int dbOrdinalPosition = rs.getInt(17);
String dbColumnIsNullable = rs.getString(18);
// Printout
System.out.println("Col(" + dbOrdinalPosition + "): " + dbColumnName
+ " (" + dbColumnTypeName +")");
System.out.println(" Nullable: " + dbColumnIsNullable +
", Size: " + dbColumnSize);
System.out.println(" Position in table: " + dbOrdinalPosition
+ ", Decimal digits: " + dbDecimalDigits);
}
// Free database resources
rs.close();
conn.close();
}
34)What is Metadata and why should I use it?
Metadata ('data about data') is information about one of two things:
1. Database information (java.sql.DatabaseMetaData), or
2. Information about a specific ResultSet (java.sql.ResultSetMetaData).
Use DatabaseMetaData to find information about the database.
35)What is the difference b/w ResultSet and RowSet?
A ResultSet maintains a connection to a database ResultSet contains the result of the SQL query and it has the connectivity with the records in the database. To avoid
the connectivity after getting the result use RowSet or
CacheRowSet. The ResultSet is the parent of Rowset
and CacheRowSet.
36) what is the need of DatabaseMetadata?
Ans:-DatabaseMetaData provides comprehensive information about the database. This interface is implemented by the driver vendors to allow the user to obtain information about the tables of a relational database as a part of JDBC application. User can use this interface to deal with various underlying DBMSs.
37) How do you implement Connection Pooling?
Connection Pooling can be implemented by the following way.
1. A javax.sql.ConnectionPoolDataSource interface that serves as a resource manager connection factory for pooled java.sql.Connection objects. Each database user provides the implementation for that interface.
eg:
oracle.jdbc.pool.oracleConnectionPoolDataSource Class.
A javax.sql.PooledConnection interface encapsulates the physical connection for the database. Again, the user provides the implementation.
Code for connecting the connection pooling
InitalContext ic=new InitialContext();
Hashtable ht=new Hashtable();
ht.put(“Context.INITIAL_PROVIDER”,weblogic.jndi.InitialCla)
//u have to set weblogic properties first and the jndi name that u r defining in
//weblogic while creating the connectionpool
ht.put(“Context.PROVIDER_URL”,t3://localhost:7001);
ht.put(“Context.SECURITY_PRINCIPAL”,username);
ht.put(“Context.SECURITY_CREDENTIALS”,passwordof weblogic);
DataSource ds=(DataSource)ic.lookup(“jndiname”);
Connection con=ds.getConnection();
Statement stmt=con.createStatement();
38) What is the difference between ResultSetMetaData and DatabaseMetaData
ResultSetMetaData
You can interrogate JDBC for detailed information about a query’s result set using a ResultSetMetaData object.
ResultSetMetaData is a class that is used to find information about the ResultSet returned from a executeQuery call.
It contains information about the number of columns, the types of data they contain, the names of the columns, and so on.
Two of the most common methods in the ResultSetMetaData are getColumnName and getColumnTypeName. These retrieve the name of a column, and the name of its associated data type, respectively, each in the form of a String.
DatabaseMetaData
DatabaseMetaData is a class that can be used to fetch information about the database you are using. Use it to answer questions such as:
* What kind of catalogs are in the database?
* What brand of database am I working with?
* What username am I?
ex: username = dbmd.getUserName();
Some Database Questions:-
Q)How can you compare a part of the name rather than the entire name?
ans:-SELECT * FROM people WHERE empname LIKE '%ab%'
Would return a recordset with records consisting empname the sequence 'ab' in empname .
Q)How could I get distinct entries from a table?
ans:-The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database. The values selected from the database table would of course depend on the various conditions that are specified in the SQL query.
Example
SELECT DISTINCT empname FROM emptable
Q)How to get the results of a Query sorted in any order?
ans: You can sort the results and return the sorted results to your program by using ORDER BY keyword thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting.
eg:
SELECT empname, age, city FROM emptable ORDER BY empname
Q)How can I find the total number of records in a table?
ans:You could use the COUNT keyword , example
SELECT COUNT(*) FROM emp WHERE age>40
Q)What is GROUP BY?
ans:The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.
Q)What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.
ans:Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes
Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete
Delete : (Data alone deleted), Doesn’t perform automatic commit
Q)What are the Large object types suported by Oracle?
ans:Blob and Clob.
Q)Difference between a "where" clause and a "having" clause.
ans:Having clause is used only with group functions whereas Where is not used with.
Q)What's the difference between a primary key and a unique key?
ans:Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
Q)What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
ans:Cursors allow row-by-row prcessing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors.
Q)What are triggers? How to invoke a trigger on demand?
ans:Triggers are special kind of stored procedures that get executed automatically when an DML Command(INSERT, UPDATE or DELETE operation) gets on a table.
Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Q)What is a join and explain different types of joins.
ans:Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
Q)What is a self join?
ans:Self join is just like any other join, except that two instances of the same table will be joined in the query.
1) What is JDBC?
Ans:-JDBC is JavaDatabaseConnection .JDBC is a technique which can communicate with the database and we can perform different database operation
2)What are drivers available?
Initially we have 256 drivers are there,which are organized into four types.
They are:
I)JDBC-ODBC Bridge driver--Type-1
II)Native API Partly-Java driver--Type-2
III)JDBC-Net Pure Java driver--Type-3
IV)Native-Protocol Pure Java driver--Type-4
3)Explain steps for connecting with the database?
1.loading the driver:-
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");(this is a type-1 driver)
Class.forName("oracle.jdbc.driver.OracleDriver");(this is a type-4 driver)
When the driver is loaded, it registers itself with the java.sql.DriverManager class as an available database driver
2.Preparing URL:-
String url="jdbc:odbc:DSN"(this URL is for Type-1 driver)
String url="jdbc:oracle:thin:@hostname:portnumber:servicename");(this URL is for Type-4 driver)
3)Making a connection with database :-
To open a connection to a given database DriverManager.getConnection( ) method is used.
Connection con = DriverManager.getConnection (url,“user”,”password”);
After getting the connection perform the database operations by using Statement,PreparedStatement or CallableStatement etc.
4)close the connection by using close() method of Connection interface.
con.close();
4)How many types of statements we have in JDBC?
ans:We have three types of statements are there.
1)Statement -- To be used createStatement( ) method for executing single SQL statement
Statement st=con.createStatement();
2)PreparedStatement -- To be used prepareStatement( ) method for executing same SQL statement over and over
PreparedStaement ps=con.prepareStatement("sql query");
3)CallableStatement -- To be used prepareCall( ) method for multiple SQL statements over and over
CallableStatement csmt=con.prepareCall("{call procedurename(?,?....)}");
5)What Class.forName will do while loading drivers?
ans: It is used to create an instance of a driver and register the driver with the DriverManager.
When you have loaded a driver, it is available for making a connection with a DBMS.
6) How can you retrieve data from the ResultSet?
Ans: JDBC returns the results in a ResultSet object, we have to declare an instance of the ResultSet to hold the results.
Eg.
ResultSet rs = con.createStatement().executeQuery("select * from employee");
while(rs.next())
{
rs.getxxx();
}
We are By using get() methods we can retrieve the data from the table.
7)Why use PreparedStatement in Java JDBC ?
ans:-
PreparedStatement is several ways to execute SQL queries using JDBC API. it is used for general purpose queries, executing parametric query.
Prepared Statement queries are pre-compiled on database and there access plan will be reused to execute further queries which allows them to execute much quicker than normal queries generated by Statement object.
we want to insert 4 records into the database then we use Statement interface then we will do recompile and execute 4 times .So resolve this problem we are using PreparedStatement interface.
Note:By using PreparedStatement then no need to execute the sql query multiple times.
8) What is the mean of “dirty read“ in database?
if one transaction updated the value(changed)and then second transaction read this value before the original change has been commited or rollback .This is known as dirty read.In this case the first transaction read may be changed or rollback and the second transaction read invalid value
9) How cursor works in scrollable result set?
ans:-
In JDBC 2.0 API is added to move cursor in resultset backward forward and also in a particular row .
We have three constant in result set for move cursor.
·TYPE_FORWARD_ONLY: creates a nonscrollable result set, the cursor moves forward direction only.
·TYPE_SCROLL_INSENSITIVE : a scrollable result set does not reflects changes why because that are made to it while it is open.
·TYPE_SCROLL_SENSITIVE: a scrollable result set reflects changes why because that are made to it while it is open
10)What setAutoCommit does?
ans:- When a connection is established, it is in auto-commit mode. the individual SQL statement is treated as a transaction and it will be automatically committed after it is executed.
Note:-The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode
Eg.
con.setAutoCommit(false);//con is Connection variable
Once disable the auto-commit mode then no transaction will be committed until you call the method commit.
Eg.
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement(
"UPDATE Employee SET EID= ? WHERE ENAME LIKE ?");
ps.setInt(1, 50);
ps.setString(2, "xyz");
ps.executeUpdate();
con.commit();
con.setAutoCommit(true);//Here automcommit mode is enabled
11)How to call a Strored Procedure from JDBC?
step1:To create a CallableStatement object. with Statement an and PreparedStatement objects, it will be open the connection. this object contains a call to a stored procedure;
Eg.
CallableStatement cs = con.prepareCall("{call procedure}");
12)How to Retrieve Warnings?
ans:- SQLWarning deal with database access warnings this objects is a subclass of SQLException this Warnings do not stop the execution of an application.
A warning can be reported on a Connection , Statement object (including PreparedStatement and CallableStatement objects), ResultSet objects. Each of these Interface has a getWarnings method, we must invoke in order to see the first warning reported on the calling object
Eg.
SQLWarning warn = stmt.getWarnings();
if (warn != null) {
System.out.println("\n~~~you are getting Warning~~~\n");
while (warn != null) {
System.out.println("Message: " + warn.getMessage());
System.out.println("SQLState: " + warn.getSQLState());
System.out.println(warn.getErrorCode());
}
}
13) How can you Move the Cursor in Scrollable Result Sets ?
ans : In JDBC 2.0 API is added to move cursor in resultset backward as well as forward and also in a particular row .
Eg:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEE");
The first argument is one of three constants added to the ResultSet to indicate the type of the ResultSet object:
TYPE_FORWARD_ONLY,
TYPE_SCROLL_INSENSITIVE ,
TYPE_SCROLL_SENSITIVE .
The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE .
Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, the cursor moves only forward direction. we do not specify any constants for the type and updatability of a ResultSet object, we will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
14)What’s the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE?
ans: we'll get a scrollable ResultSet object if we are specify these ResultSet constants.The main difference between the two has to do with whether a ResultSet reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes.
Generally a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open.another one is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened
Eg:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT EID FROM EMPLOYEE");
rs.afterLast();
while (rs.previous()) {
String name = rs.getString("ENAME");
float price = rs.getFloat("ESAL");
System.out.println(name + " " + price);
}
15)How to Make Updates to Updatable Result Sets?
ans: In JDBC 2.0 API is the ability to update rows in a result set using methods.we need to create a ResultSet object that is updatable. we supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.
Eg.
Connection con = DriverManager.getConnection(URL,User_name,Pass_word);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEE");
16)How do I start debugging problems related to the JDBC API?
ans:- JDBC calls are perform is to enable JDBC tracing. The JDBC trace contains a detailed listing of the activity occurring in the system that is related to JDBC operations.
If we are using the DriverManager facility to establish your database connection, we use the DriverManager.setLogWriter() method to enable tracing of JDBC operations. If we are use a DataSource object to get a connection, we are use the DataSource.setLogWriter() method to enable tracing.
17)What is JDBC 2.0?
The JDBC 2.0 is Application Programming Interface (API),we will be able the following
•Scroll forward and backward in a result set or move to a specific row (TYPE_SCROLL_SENSITIVE, last(), absolute(),previous(), relative(), etc....)
•Make updates to database tables using methods in the Java programming language instead of using SQL commands.( insertRow(),updateRow(), deleteRow(),etc....)
•Send multiple SQL statements to the database as a unit or batch (addBatch(), executeBatch())
•Use the new SQL3 datatypes as column values like Blob, Clob, Array, Struct, Ref.
18)How to insert and delete a row programmatically?
ans:-BY using resultSet is updatable
1. move the cursor to the specific position.
rs.moveToCurrentRow();
2. set value for each column.
rs.moveToInsertRow();//to set up for insert
rs.updateString("col1" "strvalue");
rs.updateInt("col2", 10);
...
3. call insertRow() method to finish the row insert process.
rs.insertRow();
To delete a row: cursor move to the specific position and call deleteRow() method:
rs.absolute(5);
rs.deleteRow();//delete row 5
To see the changes call refreshRow();
rs.refreshRow();
19)What are four types of JDBC driver?
ans:
Type 1 Drivers:
the jdbc-odbc bridge. ODBC to transfer the SQL calls to the database and also often rely on native code. It is not a serious solution for an application
Type 2 Drivers :
Use the existing database API to communicate with the database on the client. Faster than Type 1, but need native code and require additional permissions to work in an applet. Client machine requires software to run.
Type 3 Drivers
JDBC-Net pure Java driver:
It translates JDBC calls to a DBMS-independent network protocol, which is then translated to a DBMS protocol by a server. Flexible. Pure Java and no native code.
Type 4 Drivers
Native-protocol pure Java driver. It converts JDBC calls to network protocol used by DBMSs. it will allow a direct call from the user to the DBMS server. It don't need any special native code on the user.
20)Which type of JDBC driver is the fastest one?
ans: JDBC Net pure Java driver(Type 4 driver) is the fastest driver because it converts the jdbc calls into netowk protocol calls and it directly interacts with the database.
21)What is the query used to display all tables names in SQL Server?
ans: select * from information_schema.tables
22)There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set?
No, we can easily find the number of rows by using scrollable result set and the methods.
i)rs.last() ii)rs.getRow() . the result is not scrollable, we can either count the rows by iterating through the result set or get the number of rows by submitting a query with a COUNT column in the SELECT clause.
23)What are the standard isolation levels defined by JDBC?
The values are defined in the class java.sql.Connection and are:
-> TRANSACTION_NONE
->TRANSACTION_READ_COMMITTED
-> TRANSACTION_READ_UNCOMMITTED
->TRANSACTION_REPEATABLE_READ
->TRANSACTION_SERIALIZABLE
24)What isolation level is used by the DBMS when inserting, updating and selecting rows from a database?
we are not explicitly set the isolation level the DBMS default is used. we can determine the default using DatabaseMetaData.getDefaultTransactionIsolation()
for the current Connection Connection.getTransactionIsolation () for change the transcation Connection.setTransactionIsolation(int level).
25)How can I write to the log used by DriverManager and JDBC drivers?
By using DriverManager.println(String message);
26)How do I check in my code whether a maximum limit of database connections have been reached?
Use DatabaseMetaData.getMaxConnections() and then compare to the number of connections currently open.
Note the return value of zero is unlimited or,unfortunately, unknown. if the driverManager.getConnection() will throw an exception if a Connection can not be obtained.
27)How do I disallow NULL values in a table?
ans:-Null capability is a column integrity constraint, normally applied at table creation time. Most tables allow a default value for the column.
The following SQL statement shows the NOT NULL constraint:
CREATE TABLE Employee(
Type VARCHAR(25) NOT NULL,
Pounds INTEGER NOT NULL,
Price NUMERIC(5, 2) NOT NULL
);
Note: that some databases won't allow the constraint to be applied after table creation.
28)What is DML?
DML is an abbreviation for Data Manipulation Language. This portion of the SQL standard is concerned with manipulating the data in a database as opposed to the structure of a database. the DML's are SELECT, INSERT, DELETE, UPDATE, COMMIT and ROLLBACK
29)How do I create a java.sql.Date object?
java.sql.Date extends from java.util.Date There are two methods to create a Date object.
1) use a Calendar object, setting the year, month and day portions to the desired values. The hour, minute, second and millisecond values must be set to zero. by using Calendar.getTime().getTime() is invoked to get the java.util.Date milliseconds. That value is then passed to a java.sql.Date constructor:
Calendar cal = Calendar.getInstance();
// set Date portion to July 25, 2014
cal.set( cal.YEAR, 2014 );
cal.set( cal.MONTH, cal.July);
cal.set( cal.DATE, 25);
cal.set( cal.HOUR_OF_DAY, 0 );
cal.set( cal.MINUTE, 0 );
cal.set( cal.SECOND, 0 );
cal.set( cal.MILLISECOND, 0 );
java.sql.Date date =
new java.sql.Date( cal.getTime().getTime() );
2) java.sql.Date's valueOf method. valueOf() accepts a String, which must be the date in JDBC time escape format - "yyyy-mm-dd".
java.sql.Date date = java.sql.Date.valueOf( "2014-06-25" );
creates a Date object representing July 25, 2014. To use this method with a Calendar object.
java.sql.Date date = java.sql.Date.valueOf(
cal.get(cal.YEAR) + ":" +
cal.get(cal.MONTH) + ":" +
cal.get(cal.DATE) );
30)How do I create a java.sql.Time object?
java.sql.Time extends from java.util.Date,There are two methods to create a Time object.
1) Calendar object, setting the year, month and day portions to July 23, 2014, The millisecond value must also be set to zero. by using Calendar.getTime().getTime() is invoked to get the time in milliseconds. That value is then passed to a Time constructor:
Calendar cal = Calendar.getInstance();
// set Date portion to July 25, 2014
cal.set( cal.YEAR, 2014 );
cal.set( cal.MONTH, cal.JULY );
cal.set( cal.DATE, 125);
cal.set( cal.MILLISECOND, 0 );
java.sql.Time time =
new java.sql.Time( cal.getTime().getTime() );
2)Time's valueOf method. valueOf() accepts a String, which must be the time in JDBC time escape format - "hh:mm:ss".
eg:
java.sql.Time time = java.sql.Time.valueOf( "18:05:00" );
creates a Time object representing 4:05 p.m.
java.sql.Time time = java.sql.Time.valueOf(
cal.get(cal.HOUR_OF_DAY) + ":" +
cal.get(cal.MINUTE) + ":" +
cal.get(cal.SECOND) );
31)How do I get runtime information about the JDBC Driver?
ans:
using the DatabaseMetaData methods:
getDriverMajorVersion()
getDriverMinorVersion()
getDriverName()
getDriverVersion()
32)Could we get sample code for retrieving more than one parameter from a stored procedure?
Assume we have a stored procedure with this signature:
xyzprocedure (IN I1 INTEGER, OUT O1 INTEGER, INOUT IO1 INTEGER)
The code snippet to retrieve the OUT and INOUT parameters follows:
CallableStatement cs = connection.prepareCall( "(CALL xyzprocedure(?, ?, ?))" );
cs.setInt(1, 1); // set the IN parm I1 to 1
cs.setInt(3, 3); // set the INOUT parm IO1 to 3
cs.registerOutParameter(2, Types.INTEGER); // register the OUT parm O1
cs.registerOutParameter(3, Types.INTEGER); // register the INOUT parm IO1
cs.execute();
int iParm2 = cs.getInt(2);
int iParm3 = cs.getInt(3);
cs.close();
33)How do I extract SQL table column type information?
Use the getColumns() method of the java.sql.DatabaseMetaData interface to investigate the column type information of a particular table.
Note that most arguments to the getColumns() method
eg:
public static void main(String[] args) throws Exception
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url="jdbc:odbc:DSN";
Connection con = DriverManager.getConnection("url",
"User_name", "Password");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all column types for the table "sysforeignkeys", in schema
// "dbo" and catalog "test".
ResultSet rs = dbmd.getColumns("test", "dbo", "sysforeignkeys", "%");
// Printout table data
while(rs.next())
{
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbColumnName = rs.getString(4);
String dbColumnTypeName = rs.getString(6);
int dbColumnSize = rs.getInt(7);
int dbDecimalDigits = rs.getInt(9);
String dbColumnDefault = rs.getString(13);
int dbOrdinalPosition = rs.getInt(17);
String dbColumnIsNullable = rs.getString(18);
// Printout
System.out.println("Col(" + dbOrdinalPosition + "): " + dbColumnName
+ " (" + dbColumnTypeName +")");
System.out.println(" Nullable: " + dbColumnIsNullable +
", Size: " + dbColumnSize);
System.out.println(" Position in table: " + dbOrdinalPosition
+ ", Decimal digits: " + dbDecimalDigits);
}
// Free database resources
rs.close();
conn.close();
}
34)What is Metadata and why should I use it?
Metadata ('data about data') is information about one of two things:
1. Database information (java.sql.DatabaseMetaData), or
2. Information about a specific ResultSet (java.sql.ResultSetMetaData).
Use DatabaseMetaData to find information about the database.
35)What is the difference b/w ResultSet and RowSet?
A ResultSet maintains a connection to a database ResultSet contains the result of the SQL query and it has the connectivity with the records in the database. To avoid
the connectivity after getting the result use RowSet or
CacheRowSet. The ResultSet is the parent of Rowset
and CacheRowSet.
36) what is the need of DatabaseMetadata?
Ans:-DatabaseMetaData provides comprehensive information about the database. This interface is implemented by the driver vendors to allow the user to obtain information about the tables of a relational database as a part of JDBC application. User can use this interface to deal with various underlying DBMSs.
37) How do you implement Connection Pooling?
Connection Pooling can be implemented by the following way.
1. A javax.sql.ConnectionPoolDataSource interface that serves as a resource manager connection factory for pooled java.sql.Connection objects. Each database user provides the implementation for that interface.
eg:
oracle.jdbc.pool.oracleConnectionPoolDataSource Class.
A javax.sql.PooledConnection interface encapsulates the physical connection for the database. Again, the user provides the implementation.
Code for connecting the connection pooling
InitalContext ic=new InitialContext();
Hashtable ht=new Hashtable();
ht.put(“Context.INITIAL_PROVIDER”,weblogic.jndi.InitialCla)
//u have to set weblogic properties first and the jndi name that u r defining in
//weblogic while creating the connectionpool
ht.put(“Context.PROVIDER_URL”,t3://localhost:7001);
ht.put(“Context.SECURITY_PRINCIPAL”,username);
ht.put(“Context.SECURITY_CREDENTIALS”,passwordof weblogic);
DataSource ds=(DataSource)ic.lookup(“jndiname”);
Connection con=ds.getConnection();
Statement stmt=con.createStatement();
38) What is the difference between ResultSetMetaData and DatabaseMetaData
ResultSetMetaData
You can interrogate JDBC for detailed information about a query’s result set using a ResultSetMetaData object.
ResultSetMetaData is a class that is used to find information about the ResultSet returned from a executeQuery call.
It contains information about the number of columns, the types of data they contain, the names of the columns, and so on.
Two of the most common methods in the ResultSetMetaData are getColumnName and getColumnTypeName. These retrieve the name of a column, and the name of its associated data type, respectively, each in the form of a String.
DatabaseMetaData
DatabaseMetaData is a class that can be used to fetch information about the database you are using. Use it to answer questions such as:
* What kind of catalogs are in the database?
* What brand of database am I working with?
* What username am I?
ex: username = dbmd.getUserName();
Some Database Questions:-
Q)How can you compare a part of the name rather than the entire name?
ans:-SELECT * FROM people WHERE empname LIKE '%ab%'
Would return a recordset with records consisting empname the sequence 'ab' in empname .
Q)How could I get distinct entries from a table?
ans:-The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database. The values selected from the database table would of course depend on the various conditions that are specified in the SQL query.
Example
SELECT DISTINCT empname FROM emptable
Q)How to get the results of a Query sorted in any order?
ans: You can sort the results and return the sorted results to your program by using ORDER BY keyword thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting.
eg:
SELECT empname, age, city FROM emptable ORDER BY empname
Q)How can I find the total number of records in a table?
ans:You could use the COUNT keyword , example
SELECT COUNT(*) FROM emp WHERE age>40
Q)What is GROUP BY?
ans:The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.
Q)What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.
ans:Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes
Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete
Delete : (Data alone deleted), Doesn’t perform automatic commit
Q)What are the Large object types suported by Oracle?
ans:Blob and Clob.
Q)Difference between a "where" clause and a "having" clause.
ans:Having clause is used only with group functions whereas Where is not used with.
Q)What's the difference between a primary key and a unique key?
ans:Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
Q)What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
ans:Cursors allow row-by-row prcessing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors.
Q)What are triggers? How to invoke a trigger on demand?
ans:Triggers are special kind of stored procedures that get executed automatically when an DML Command(INSERT, UPDATE or DELETE operation) gets on a table.
Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Q)What is a join and explain different types of joins.
ans:Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
Q)What is a self join?
ans:Self join is just like any other join, except that two instances of the same table will be joined in the query.
Comments