JDBC

JDBC Questions

Q) What Class.forName will do while loading drivers?
A) It is used to create an instance of the driver and register with the DriverManager.

Q) JDBC connection

import java.sql.*;
public class JDBCSample {
public static void main(java.lang.String[] args) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch (ClassNotFoundException e) {
System.out.println("Unable to load Driver Class");
return;
}
try {
Connection con = DriverManager.getConnection("jdbc:odbc:companydb","", "");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT FIRST_NAME FROM EMPLOYEES");
while(rs.next()) {
System.out.println(rs.getString("FIRST_NAME"));
}
rs.close();
stmt.close();
con.close();
}
catch (SQLException se) {
System.out.println("SQL Exception: " + se.getMessage());
}
}
}



Q) 4th type driver

class.forName(“oracle.jdbcdriver.oracledriver”);
//DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
// com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource for Initial Context
//
connection con = driverManager.getConnection(“jdbc:oracle:thin:@hostname:portno:oracleservice”,”uid”, “pwd”);

Q) Steps to connect to JDBC?
A) 1. First thing is using jdbc you have to establish a connection to the data base this is 2 steps process (i) you must load the jdbc driver (ii) then make a connection, to do this we can call the getConnection() method of driver manager class.
2. To execute any sql commands using jdbc connection you must first create a statement object to create this call statement st = con.createSteatement().
This is done by calling the createStatement () method in connection interface. Once the statement is created you can executed it by calling execute () method of the statement interface.

Q) JDBC connection pool
When you are going to create a pool of connection to the data base. This will give access to a collection of already opened data base connections, which will reduce the time it takes to service the request and you can service “n” number of request at once.

Q) Why you need JDBC if ODBC is available?
A) ODBC is purely written in “c” so we cannot directly connect with java. JDBC is a low level pure java API used to execute SQL statements. (i) ODBC is not appropriate for direct use from java because it uses “c” interfaces. A call from java to native “c” code has number of drawbacks in the security, implementation and robustness.

Q) Can we establish the connection with ODBC itself?
A) Yes, using java native classes we have to write a program.

Q) What is necessity of JDBC in JDBC-ODBC Bridge?
A) The purpose of JDBC is to link java API to the ODBC, ODBC return high level “c” API so the JDBC converts “c” level API to java API.

Q) JDBC Drivers
o JDBC-ODBC Bridge Driver
o Native API - Partly Java Driver
o Network protocol – All Java Driver
o Native Protocol - Pure Java Driver
Tier Driver mechanism Description
Two JDBC-ODBC JDBC access via most ODBC drivers, some ODBC binary code and client code must be loaded on each client machine. This driver is commonly used for prototyping. The JDBC-ODBC Bridge is JDBC driver which implements JDBC operations by translating them to JDBC operations.
Two Native API - Partly - Java driver This driver converts JDBC calls to database specific native calls. Client requires database specific libraries.
Three JDBC - Net -All Java driver This driver converts JDBC calls into DBMS independent network protocol that is sent to the middleware server. This will translate this DBMS independent network protocol into DBMS specific protocol, which is sent to a particular database. The results are again rooted back to middleware server and sent back to client.
Two Native protocol - All - Java driver They are pure java driver, they communicate directly with the vendor database. They convert JDBC commands.

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

Q) Is the JDBC-ODBC Bridge multi-threaded?
A) No. The JDBC-ODBC Bridge does not support concurrent access from different threads. The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC
Q) Dynamically creating Tables
Statement st = con.cretaeStatement ();
Int n = st.executeUpdate (“create table “+ uname+ “(sno int, sentby varchar (10), subject varchar (15)”);

Q) Statements in JDBC

Statement  Does not take any arguments; in this statement it will check syntax error and execute it every time.

Prepare statement  P.S is precompiled statements once we compile the statements and send it to the server for later use. P.S are partially compiled statements placed at server side with place holders. Before execution of these statements user has to supply values for place holders, it will increase performance of application.

PreparedStatement pst = con.prepareStatement("SELECT * FROM EMP WHERE deptno=?");
DataInputStream dis = new DataInputStream(“System.in”);
Int dno = Integer.ParseInt(dis.readLine());
pst.setInt(1, dno);
ResultSet rs = pst.executeQuery();

execute () return type boolean and executeUpdate () return type is int.

Callable statement  C.S used to retrieve data by invoking stored procedures, stored procedure are program units placed at data base server side for reusability. These are used by n-number of clients. Stored procedures are precompiled in RDBMS, so they can run faster than the dynamic sql.
Callable statement will call a single stored procedure, they perform multiple queries and updates without net work traffic.

callableStatement cst = con.prepareCall(“{CALL procedure-name(??)} ”);
DataInputStream dis = new DataInputStream(“System.in”);
Int enum = Integer.ParseInt(dis.readLine());
cst.setInt(1, enum);
cst.registerOutParameter(2, types.VARCHAR)
resultset rs = cst.execute();

In  used to send information to the procedure.
Out  used to retrieve information from data base.
InOut  both.

Q) Retrieving very large values from database?
A) getASSCIISteram()  read values which are character in nature.
GetBinaryStream()  used to read images.

Q) ResultSetMetaData
It is used to find out the information of a table in a data base.
ResultSet rs = stmt.executeQuery("SELECT * FROM "+ table);
ResultSetMetaData rsmd = rs.getMetaData();

Methods in ResultSetMetaData  getColumnCount(), getColumnName(), getColumnLabel(), getColumnType(), getTableName(),

Q) Database MetaData
You need some information about the “data base” & “dictionary” we use this .To find out tables, stored procedure names, columns in a table, primary key of a table we use this, this is the largest interface in java.sql package

Connection con = DriverManager.getConnection(jdbcURL, "", "");
DatabaseMetaData dbmd = con.getMetaData();
ResultSet rs= dbmd.getxxx();

Methods in DatabaseMetaData  getColumns(), getTableTypes(), getTables(), getDriverName(), getMajorVersion(), get MinorVersion(), getProcedures(), getProcedureColumns(), getTables().

Q) Procedure
Procedure is a subprogram will perform some specific action; sub programs are naming PL/SQL blocks that can take parameters to be invoked.

create or replace procedure procedure-name (id IN INTEGER , bal IN OUT FLOAT) IS
BEGIN
select balance into bal from accounts where account_id = id;
bal := bal + bal * 0.03;
update accounts set balance = bal where account_id = id;
END;

Q) Trigger
Trigger is a stored PL/SQL block associated with a specific database table. Oracle executes triggers automatically when ever a given SQL operation effects the table, we can associate 12 data base triggers with in a given table.

Create/Replace trigger before Insert (or) Delete (or) Update on emp for each row
Begin
Insert into table-name values(:empno; :name)
end


Q) Stored Images into a table

Public class img
{
Public static void main(String args[]){
Class.forName();
Connection con = DriverManager.getConnection();
Preparestatement pst = con.prepareStatement(“insert into image value(?));
FileInputStream fis = new FileInputStream(“a.gif”);
Pst.setBinaryStream(1, fis, fis.available);
Int I = pst.executeUpadate();
}

Retrieve Image

Statement st = con.CreateStatement();
ResultSet rs = st.executeQuery(“select * from img”);
rs.next();
InputStream is = rs.getBinaryStream(1);
FileOutPutStream fos = new FileOutPutStream(“g2.gif”);
Int ch;
While((ch=is.read(1))!=!-1)
{
fos.write(ch);
}


Q) Resultset Types
rs.beforeFirst()  goto 1st record
rs.afterLast()  goto last record
res.absolute(4)  will got 4th record in result set.
rs.first(), rs.last()
rs.deleteRow()
rs.updateRow(), rs.updateRow(3,88)  value in column 3 of resultset is set to 88.