JAVA JDBC

What is metadata?

 It is descriptive information about database structure and connections.

e.g.

• Who is connected to the Database?
• Is the database read only? 
• Valid data types for the database 
• Data type of the column. 
• How does the database vendor deals with the stored procedure? 
• How does the database handle cursor across rollback and commit?   
• How many columns were retrieved in each row of the result? 
• What's is the SQL type, column name? 
• What is the Table name? 
• How many decimal digits does a column have? 

 Syntax :

ResultsetMetaData rsmeta=rs.getMetaData();

 Advantages 

• Maintains a persistent connection with the database.  
• Minimizes resource requirements on the Web server by processing locally on the client.  
• Results in performance gains over the network because of the direct interaction with the database.  

Disadvantages 

• The JDBC drivers may not be supported by some commercial browsers. 
• These driver types typically call a native database driver, such as ODBC or another vendor-provided load module (e.g. DLL). These calls may be considered a security violation by the browser, since they create a potential risk for computer viruses. (Browsers with built-in support of JDK 1.1 are not restricted in this way.)  
• Because the applet calls the database directly, the native database drivers must be installed and maintained on each client computer that will be running the applet. This increases the administrative requirements for all clients that require access to the database. 

• Presents no security risk to the client and is supported by commercial browsers.  
• No maintenance is required on the clients, because all database access is performed on the Web server.  
• Provides central control and administration of database logic on the Web server.  

 Disadvantages 

• Some performance loss is expected because of the middle layer (CGI).  
• Unable to maintain a persistent connection to the database.  
• Increases resource requirements on Web server.

Summary  

• JDBC is ready for work  
• Java with JDBC is a sophisticated database development tool  
• JDBC connectivity is good today and getting better  
• Either using JDBC directly or via Java application builders.

Sample Code : (Using JDBC-ODBC Bridge)

class Sample {

static Connection con; // database connection object

/** Main routine */

// Load the JDBC-ODBC bridge driver

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

 // Open the database and display all rows in a table

open();

insert();

select();

 // Add a new row and show the result

insert();

//select("WHERE EmpCode = 11");

 // Update a row and commit the result

update(true);

select("WHERE EmpCode = 11");

 // Update a row and rollback the result

update(false);

select("WHERE EmpCode = 100");

 // Delete a row and show the result

delete();

select("WHERE EmpCode = 100");

 // Close the database

close();

 }

 /** Open a database connection */

static void open() throws SQLException {

 // ODBC data source name where db4 is the database name

// It is a MsAccess database file.

String dsn = "jdbc:odbc:db4";

String user = "guest";

String password = "";

 // Connect to the database

con = DriverManager.getConnection(dsn, user, password);

 // Shut off autocommit

con.setAutoCommit(false);

 }

 /** Commit all pending transactions and close the database connection */

static void close() throws SQLException {

 con.commit();

con.close();

 }

 /** Issue a SQL query with a WHERE clause */

 static void select(String whereClause) throws SQLException {

 Statement stmt; // SQL statement object

String query; // SQL select string

ResultSet rs; // SQL query results

boolean more; // "more rows found" switch

 query = "SELECT Empcode, name "

+ "FROM table1 ";

 stmt = con.createStatement();

rs = stmt.executeQuery(query);

 // Check to see if any rows were read

more = rs.next();

if (!more) {

 System.out.println("No rows found.");

return;

 }

 // Loop through the rows retrieved from the query

while (more) {

 System.out.println("ID: " + rs.getInt("EmpCode"));

System.out.println("Name: " + rs.getString("Name"));

System.out.println("");

more = rs.next();

 }

 rs.close();

stmt.close();

 }

 /** Issue a SQL query without a WHERE clause */

static void select() throws SQLException {

 select("");

 }

 /** Insert a new row */

static void insert() throws SQLException {

 Statement stmt; // SQL statement object

String sql; // SQL insert command

int rows; // Number of rows inserted

 sql = "INSERT INTO Table1 "

+ "VALUES (11, 'Gaurav')";

 stmt = con.createStatement();

rows = stmt.executeUpdate(sql);

con.commit();

stmt.close();

 System.out.println(rows + " row(s) added");

 }

 /** Update a new row */

static void update(boolean commit) throws SQLException {

 Statement stmt; // SQL statement object

String sql; // SQL update command

int rows; // Number of rows inserted

String title; // New title for the update

 if (commit)

title = "Arun";

else

title = "Aniesh";

 sql = "UPDATE Table1 "

+ "SET Name='" + title + "' "

+ "WHERE EmpCode = 2";

 stmt = con.createStatement();

rows = stmt.executeUpdate(sql);

 if (commit)

con.commit();

else

con.rollback();

 stmt.close();

System.out.println(rows + " row(s) updated");

 }

 /** Delete a row */

static void delete() throws SQLException {

 Statement stmt; // SQL statement object

String sql; // SQL delete command

int rows; // Number of rows inserted

 sql = "DELETE FROM Table1 "

+ "WHERE EmpCode = 11";

 stmt = con.createStatement();

rows = stmt.executeUpdate(sql);

con.commit();

stmt.close();

 System.out.println(rows + " row(s) deleted");

 }

 }