What is Java DataBase Connectivity?
The JDBC API defines a set of interfaces that encapsulate major database functionality. Database vendors provide drivers that implement the API for specific databses.
What is the latest version of JDBC ? What are the new features are added in that ?
JDBC 2.0 provides features like treating resultsets as javaBeans, pooling databse connections, and looking up database connection info from a naming service,storing java Objects in databases etc.
What are 4 drivers available in JDBC ?
1) JDBC-ODBC Bridge Driver -> Uses a bridge technology to connect a Java client to an ODBC database.
2) Native API Partly Java Drivers -> LIke Oracle drivers, they are native C++ Api's with a java wrapper.
3) Net Protocol All Java Drivers. -> Define a generic Netowrk protocol that interfaces with custom middleware like WebLogic
4) Native- Protocol All Java Drivers -> Written entirely in Java they can access a database directly over the Net without any additional software.
How do you connect to the Database ?
1. Loading the driver:
Class.forName("oracle.jdbc.driver.OracleDriver");
Class.forName automatically creates an instance of a driver and registers it with the DriverManager. When the driver is loaded it is available for making a connection with a DBMS.
2. Making the Connection :
The second step in establishing a connection is to have the appropriate driver connect to the DBMS.
If you are using the JDBC-ODBC Bridge driver, the JDBC URL will start with jdbc:odbc: . The rest of the URL is generally the data source name or database system. So, if the ODBC data source is called “Fred" the JDBC URL could be jdbc:odbc:Fred .
String url = "jdbc:oracle:thin:"+
userID+"/"+Pswd+
"@"+hostName+":"+oraclePort+":"+oraInstance;
Connection con = DriverManager.getConnection(url);
How many types of JDBC Statements are there?
1) Statement.
2) Prepared Statement.
3) Callable Statement.
What is a statement, how do you create one?
A Statement object sends SQL statement to the DBMS. A Statement object is created and then executed with the appropriate “execute” method with the SQL statement. For SELECT statements, the method used is “executeQuery” and for statements that create or modify tables, the method used is “executeUpdate”. An active connection is required to create a Statement object:
String sqlQuery =” create table temp (name VARCHAR (32), empid INTEGER, salary INTEGER)”;
Statement stmt = con.createStatement();
stmt.executeUpdate(sqlQuery);
“sqlQuery” is a DDL (data definition language) statement. Statements that create, alter, or drop tables are DDL statements and are executed with the method executeUpdate. executeUpdate is also used to execute SQL statements that update a table.
What is a a Prepared Statement, How do you create one?
If you want to execute a Statement object many times, a PreparedStatement object can be used to reduce execution time. Unlike a Statement object, a Prepared Statement is given an SQL statement when it is created. The advantage is that this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains an SQL statement that has been precompiled.
PreparedStatement updateTemp= con.prepareStatement("update temp set salary = ? where name like ?");
updateTemp.clearParameters();
updateTemp.setInt(1,5000);
updateTemp.setSting(2,”Srujan”);
updateTemp.executeUpdate();
The return value of an executeUpdate method is an int with the number of rows updated. When the method executeUpdate is used to execute a DDL statement it returns 0.
What is a Stored Procedure?
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Oracle PL/SQL Stored Procedure
CREATE OR REPLACE PROCEDURE show_temp
( id IN INTEGER
sal IN OUT INTEGER) IS
BEGIN
SELECT salary INTO sal FROM temp
where empid = id ;
sal := sal + sal * 0.12; // 12 % hike :)
UPDATE temp SET salary = sal WHERE empid = id;
END;
The procedure “show_temp” will be compiled and stored in the database as a database object that can be called.
What is a Callable Statement, How do you create one?
A CallableStatement object contains a call to a stored procedure. The first line of code below creates a call to the stored procedure “show_temp” using the connection con . The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call show_temp}”, it will translate this escape syntax into the native SQL used by the database to call the stored procedure named “show_temp” .
CallableStatement cs = con.prepareCall("{call show_temp(?,?)}");
cs.registerOutParameter(2, Types.Interger);
cs.setInt(1,14075);
cs.setInt(2,1000);
cs.executeQuery(); //ResultSet rs can be used as well.
out.println( cs.getInt(2));
If the procedure had contained one update or one DDL statement, the method executeUpdate would have been used. A stored procedure may contain more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the method execute should be used to execute the CallableStatement .
The class CallableStatement is a subclass of PreparedStatement , so a CallableStatement object can take input parameters just as a PreparedStatement object can.
How do you create and use Result Sets.
JDBC returns results in a ResultSet object.
sqlQuery =”select name,empid from temp”;
ResultSet rs = stmt.executeQuery(sqlQuery);
while (rs.next()) {
String s = rs.getString("name");
int n = rs.getInt("empid");
System.out.println(s + " " + n);
}
The variable rs , which is an instance of ResultSet , contains the rows of results. To access the data we go to each row and retrieve the values according to their types. The method next moves what is called a cursor to the next row and makes that row (called the current row) the one upon which we can operate. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next moves the cursor to the first row and makes it the current row. Successive invocations of the method next move the cursor down one row at a time from top to bottom. The JDBC 2.0 API, allows the cursor to move backwards, to specific positions, and to positions relative to the current row in addition to moving the cursor forward.
getXXX methods of the appropriate type are used to retrieve the value of each column. JDBC allows you to use either the column name or the column number as the argument to a getXXX method. Using the column number is slightly more efficient.
String s = rs.getString(1);
Int n = rs.getInt(2);
A ResultSet object is created with a cursor pointing above the first row. The first call to the next method positions the cursor on the first row of rs.
What is a transaction? How do you commit a transaction, how do you disable Auto-commit Mode?
A transaction is a set of SQL Statements that must behave atomically. When a connection is created, it is in auto-commit mode. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode. If auto-commit is disabled, no SQL statements will be committed until the method “commit” is called explicitly.
con.setAutoCommit(false);
con.commit();
con.rollback(); To roll back a transaction if there was an Exception in the catch block.
What is Connection Pooling? How is it implemented?
Opening a connection to a database is a time-consuming process. For shortqueries, it can take much longer to open the connection than to perform theactual database retrieval.A connection pool class should be able to perform the following tasks:
1. Preallocate the connections.
Perform this task in the class constructor.
Assume thatmakeNewConnection simply calls the getConnectionmethod ofDriverManager.
availableConnections = new Vector(initialConnections);
busyConnections = new Vector();
for(int i=0; i < initialConnections; i++)
{
availableConnections.addElement(makeNewConnection());
}
2. Manage available connections.
If a connection is required and an idle connection is available,put it in the list of busy connections and then return it.
public synchronized Connection getConnection()throws SQLException
{
if (!availableConnections.isEmpty())
{
Connection existingConnection =(Connection)availableConnections.lastElement();
int lastIndex = availableConnections.size() -1;
availableConnections.removeElementAt(lastIndex);
if (existingConnection.isClosed())
{
notifyAll();
// Freed up a spot for anybody waiting.
return(getConnection()); // Repeat process.
}
else
{
busyConnections.addElement(existingConnection);
return(existingConnection);
}
}
}
3. Allocate new connections.If a connection is required, there is no idle connection available,and the connection limit has not been reached, then start a background thread to allocate a new connection.
if ((totalConnections() < maxConnections) &&!connectionPending)
{ // Pending = connecting in bg
makeBackgroundConnection();
}
try
{
wait(); // Give up lock and suspend self.
} catch(InterruptedException ie) {}
return(getConnection()); // Try again.
4. Wait for a connection to become available.
This situation occurs when there is no idle connection and you’ve reached the limit on the number of connections. This waiting should be accomplished without continual polling. The natural approach is to use the wait method, which gives up the thread synchronization lock and suspends the thread until notify or notifyAll is called. Since notifyAllcould stemfrom several possible sources, threads that wake up still need totest to see if they can proceed.
try {wait();}
catch(InterruptedException ie) {}
return(getConnection());
It may be that you don’t want to let clients wait and would rather throw an exception when no connections are available and theconnection limit has been reached. In such a case, do the following instead
throw new SQLException("Connection limit reached");
5. Close connections when required.Note that connections are closed when they are garbage col-lected, so you don’t always have to close them explicitly. But,you sometimes want more explicit control over the process.
public synchronized void closeAllConnections()
{
// The closeConnections method loops down Vector, calling
// close and ignoring any exceptions thrown.
closeConnections(availableConnections);
availableConnections = new Vector();
closeConnections(busyConnections);
busyConnections = new Vector();
}
If you are truncated using JDBC, How can you know ..that how much data is truncated ?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment