JDBC - Java Database Connectivity

import java.sql.*;

// Use the JDBC-ODBC bridge as an example
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

// The database URL takes the form jdbc:subprotocol:subname
String url = "jdbc:odbc:dsn"

String user = "guest";
String password = "guest";

// Call the Driver Manager to open a database connection
Connection con = DriverManager.getConnection(url, user, password);

// Transactions are automatically committed when executed
con.setAutoCommit(true);

// Put connection in read-only mode
con.setReadOnly(true).

con.close();

Statement stmt = con.createStatement ();

String query = "SELECT * FROM TABLE_NAME";

ResultSet rs = stmt.executeQuery (query);

boolean found = rs.next();    // returns true of row is read, false if end of result set

/** Issue a getXXXX() call for each column, based on its data type.
  *
  * Common data types are:
  * INTEGER         getInt() or getBigDecimal(,0)
  * NUMBER          getBigDecimal()
  * VARCHAR         getString()
  * LONGVARCHAR     getAsciiStream()
  * TIMESTAMP       getDate(), getTime(), or getTimestamp()
  *
  * Columns may be accessed through a relative position within the row (e.g. column 1, 2, 3, ...)
  * or through its actual column name.
  *
  * SQL NULL values will result in the Java null value being assigned to the Java variable.
  */
int    personKey = rs.getInt(1);                     // first column in the row
String lastName  = rs.getString("LAST_NAME");        // column name provided
String firstName = rs.getString("FIRST_NAME");       // column name provided
Date   birthDate = rs.getDate(4);                    // fourth column in the row

// Close the result set object after row processing is complete
rs.close();

// Close the statement object when row processing is complete
stmt.close();

Statement stmt = con.createStatement ();

String query = "INSERT INTO TABLE_NAME VALUES (1, 'SMITH', 'JOHN', NULL)";

int rowsUpdated = stmt.executeUpdate (query);  // returns number of rows updated

con.commit();

con.rollback();

// Close the statement object after update is complete
stmt.close();

// Stored procedure call
String command = "{call PROCEDURENAME(?,?,?,?)}";     // 4 placeholders

// Stored function call
String command = "{? = call FUNCTIONNAME(?,?,?,?)}";  // 4 placeholders + 1 return value

CallableStatement cstmt = con.prepareCall (command);

// Assign IN parameters (use positional placement)
cstmt.setInt(1, 50);       // first placeholder has an integer value of 50
cstmt.setString(2, "Smith");  // second placeholder has a string value of "Smith"
cstmt.registerOutParameter(3, Types.NUMERIC);    // NUMERIC is preferred over INTEGER with some databases
cstmt.registerOutParameter(4, Types.VARCHAR);    // fourth placeholder is an OUT field of type VARCHAR.

cstmt.execute();

/** Use proper getXXXX() method based on parameter data type.
  * Placeholder 3 will receive a NUMERIC value as a BigDecimal object with 0 decimal places.
  * Placeholder 4 will receive a VARCHAR value as a String object.
  */
BigDecimal num = cstmt.getBigDecimal(3,0);
String str = cstmt.getString(4);

// Close the call object after update is complete
cstmt.close();

DatabaseMetaData dmeta = con.getMetaData();

ResultSetMetaData rsmeta = rs.getMetaData();

import java.net.URL;
import java.sql.*;

class JDBCapp  {
  static myConnection theConn;

  public static void main (String args[]) {
     theConn = new myConnection();
     theConn.Connect2Db("dbPROFILE", "dba", "sql");
     }
  }

class myConnection {
   Connection dbConn = null;
   void Connect2Db(String db, String user, String passw) {
     try {
      // using the driver
      Driver d =
        (Driver)Class.forName
          ("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
      // URL corresponding to the ODBC profile
      String URL = "jdbc:odbc:" + db;
      // DB logon
      dbConn =
         DriverManager.getConnection(URL, user, passw);
      }
     catch (java.lang.Exception e) {
        System.out.println("** myConnection **");
        e.printStackTrace();
      }
     }

  void Disconnect2Db() {
     try { dbConn.close(); }
     catch (java.lang.Exception e) {e.printStackTrace();}
     }
  }

note:the myConnection object was used to connect to the DB

// with a Prepared Statement (only 1 row)
String id = cust_id.getText();
try {
  PreparedStatement prepstmt;
  boolean found = false;
  prepstmt = theConn.dbConn.prepareStatement
    ("select custName, CustAddr from tCust where custId = :id");
  prepstmt.setString(1, id);

  ResultSet rs;
  rs = prepstmt.executeQuery();

  found = rs.next();
  if (found)
      System.out.println(rs.getString(1));
  else
      System.out.println("Customer " + id + " not found!");
   prepstmt.close();
   rs.close();
   }
catch (java.lang.Exception e) {
   e.printStackTrace();
   }

// with a Statement (many rows)
String name = cust_name.getText();
try {
  Statement stmt;
  String sql;

  sql =  "select custName from tCust where custName = "
     += "'" + name + "'";
  stmt = createStatement();

  ResultSet rs;
  rs = stmt.executeQuery();

  while (rs.next()) {
    System.out.println(rs.getString("custName"));
    }
   stmt.close();
   rs.close();
   }
catch (java.lang.Exception e) {
   e.printStackTrace();
   }

note:the myConnection object was used to connect to the DB

Statement stmt;
String sql;
int rows;

sql = "INSERT INTO tCust "
    + "(custId, custName, custAddr) "
    + "VALUES "
    + "('" + custId   + "',"
    + "('" + custName + "',"
    + "('" + custAddr + "')";

stmt = theConn.dbConn.createStatement();
rows = stmt.executeUpdate(sql);
theConn.dbConn.commit();
stmt.close();


note:a PreparedStatement is used to insert data containing QUOTES

PreparedStatement stmt = null;
String sql;
int rows;

try {
  sql = "INSERT INTO tCust"
        + "(custName) "
        + "VALUES "
        + "(:p1)";
  stmt = theConn.dbConn.prepareStatement(sql);
  stmt.setString(1, "Name with \" are permitted!");
  rows = stmt.executeUpdate();
  theConn.dbConn.commit();
  stmt.close();
  System.out.println(sql);
  }
catch (SQLException esql){
   System.out.print(esql);
   }

note:the myConnection object was used to connect to the DB

PreparedStatement prepstmt;
try {
  prepstmt = theConn.dbConn.prepareStatement
   ("UPDATE tCust SET custName = :p1 "+
    " WHERE custId = :p2");
  prepstmt.setString(1,"Smith");
  prepstmt.setString(2, cust_id.getText());
  prepstmt.executeUpdate();

  theConn.dbConn.commit();
  prepstmt.close();
  }
catch (java.lang.Exception e) {
  e.printStackTrace();
  }

note:the myConnection object was used to connect to the DB

PreparedStatement prepstmt;
try {
  prepstmt = theConn.dbConn.prepareStatement
    ("DELETE FROM tCust "+
     " WHERE custId = :p1");

  prepstmt.setString(1,cust_id.getText());
  prepstmt.executeUpdate();

  theConn.dbConn.commit();
  prepstmt.close();
  }
catch (java.lang.Exception e) {
  e.printStackTrace();
  }

// connection  host address, port
OCIDataBase database = OCI.createConnection("127.0.0.1", 1540);

// logon  user/password
database.olog("data/data");

// sql statement
String sql = "SELECT custName FROM tCust "
           + "WHERE custId = "
	   + cust_id.getText();

// define the field
OCIStringParam pname = new OCIStringParam();
pname.wantIndp();
pnoprp.indp[0] = -1;

// parse the SQL statement
OCICursor cursor = database.oopen();
cursor.oparse(sql);

// bind the result
cursor.odefin(1,  pnoprp);

// execute
cursor.oexec();
try {
  cursor.ofetch();
  cust_name = pname.value;
  cursor.oclose();
  database.ologof();
  OCI.closeConnection(database);
  }
catch (Exception esql) {
  System.out.println(esql.toString());
  }

import java.sql.*;

public class connectToOracle extends java.applet.Applet {
   Driver driver;
   Connection conn = null;
   static String driverUsed =
      "oracle.jdbc.driver.OracleDriver";
   static String serverAddress =
      "jdbc:oracle:thin:scott/tiger@www.myCompany.com:1243:myInstance";
      // jdbc:oracle:thin is the driver used
      // scott/tiger is user/password
      // www.myServer.com  is  the same machine from where the Applet was loaded
      // 1234  is the port used
      // myInstance  is  where my data is

   public void init(){
      makeConnection(serverAddress);
      }

   public void makeConnection(String svr) {
      try {
         System.out.println("Loading ... " + driverUsed);
         driver =
               (Driver)Class.forName(driverUsed).newInstance();
         System.out.println("Connecting ... " + svr);
         conn =
               DriverManager.getConnection(svr);
         System.out.println("Ready.");
         }
      catch (Exception e) {
         e.printStackTrace();
         }
      }
   }

java.util.Date today    =        new java.util.Date();
java.sql.Date  sqlToday =        new java.sql.Date( today.getTime() );