Wednesday, November 24, 2010

JDBC

import java.sql.*;
Load Driver Class
Class.forName("jdbc-driver"); //initializes static block d/b variables initialized
4 types of Drivers:
type 1: ODBC Bridge
type 2 & 3: Needs client s/w to be installed at client d/b
type 4: Direct recommended by Sun
type 2 ---- developed in Native Code
type 3 & 4 ---- developed in java
Connection con = DriverManager.getConnection("jdbc:myDriver:d/bSource", "Login","Password");
The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. An application to use a logical name for a data source instead of having to supply information specific to a particular driver. (preferred)
InitialContext ic = new InitialContext();
DataSource ds = ic.lookup("java:comp/env/jdbc/myDB");
ds.setPort(1527); ds.setHost("localhost"); ds.setUser("APP"); ds.setPassword("APP");
Connection con = ds.getConnection();

a DataSource can usually be configured and managed by the application server instead of your application. It means that the connections you get from a DataSource can come from a connection pool for performance. It means that the connections may participate in a container-managed distributed transaction without you having to worry about the nitty gritty of it.
------------------------------------------------------------------------------------
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) { sop ( rs.getInt("a") + rs.getString("b") + rs.getFloat("c"));
con.close(); //finally block
To update d/b Table
int count = st.executeUpdate("insert int Table1 values (123, b, 123.111)"); //returns rows updated in d/b

Using Prepared Statement -- each time sql statement compilation is not required
PreparedStatement pstmt = con.prepareStatement("insert into Table1 values(?, ?, ?)");
pst.setInt(1,123); pst.setString(2, "xxx"); pst.setFloat(3, 123.111);
int count = pst.executeUpdate();
pst.setInt(1,456); pst.setString(2, "yyy");
int count = pst.executeUpdate();

---------------------------Meta Data------------------------------
ResultSetMetaData metaData = rset.getMetaData();
int count = metaData.getColumnCount();
for( int i=1; i<=count; i++)
{ sop ( metaData.getColumnLabel(i) + metaData.getColumnTypeName(i) + metaData.getColumnTypeSize(i) );

-------------------Commit and Rollback--------------------------
con.setAutoCommit(false); //true by default
con.rollback;
con.commit();

--------------------execute Batch (reduces d/b hits)--------------
PreparedStatement pstmt = con.prepareStatement("insert into Table1 values(?, ?, ?)");
pst.setInt(1,123); pst.setString(2, "xxx"); pst.setFloat(3, 123.111);
pst.addBatch();
pst.setInt(1,456); pst.setString(2, "yyy"); pst.setFloat(3, 123.111);
pst.addBatch();
pst.executeBatch(); //only one d/b hit

----------------Callable Stmt--------------------------------------
Used for d/b stored procedures and functions execution
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();

DAO - data access object (encapsulates all d/b related functionalities)

No comments:

Post a Comment