A JDBC Wrapper--In Python!
by C. K. Tan

Listing One
PreparedStatement ins = null, upd = null, sel = null, del = null;
Connection con = DriverManager.getConnection(url, "mylogin", "mypasswd");
boolean commit = false;
try {
    Calendar c = Calendar.getInstance();
    c.clear();
    c.set(2000, 0, 1);

    ins = con.prepareStatement(
       "insert into EMPLOYEE(id, name, salary, hire_date) " +
       "   values (?, ?, ?, ?)");
    upd = con.prepareStatement(
       "update EMPLOYEE set salary = ? " +
       "   where id = ?");
    sel = con.prepareStatement(
       "select id, name, salary, hire_date " +
       "  from EMPLOYEE " +
       " where id = ?");
    del = con.prepareStatement(
       "delete from EMPLOYEE where id = ?");

    ins.setLong(1, 1000);
    ins.setString(2, "John Smith");
    ins.setFloat(3, 100000);
    ins.setTimestamp(4, new Timestamp(c.getTime().getTime()));
    ins.executeUpdate();
    System.out.println("inserted 1000 John Smith 100000");

    upd.setFloat(1, 120000);
    upd.setLong(2, 1000);
    upd.executeUpdate();
    System.out.println("updated 1000 120000");

    sel.setLong(1, 1000);
    ResultSet rs = sel.executeQuery();
    try {
        if (rs.next()) {
            System.out.println("selected " + rs.getLong(1) + " "
                       + rs.getString(2) + " "
               + rs.getFloat(3) + " "
               + rs.getTimestamp(4));
        }
        else {
            System.out.println("error: cannot find employee id 1000");
        }
    }
    finally {
        rs.close();
    }
    del.setLong(1, 1000);
    del.executeUpdate();
    System.out.println("deleted 1000");    

    commit = true;
}
finally {
    if (ins != null)
        ins.close();
    if (sel != null)
        sel.close();
    if (upd != null)
        upd.close();
    if (del != null)
        del.close();

    if (commit)
        con.commit();
    else
        con.rollback();
    con.close();
}

Listing Two
boolean commit = false;
DbTrans trans = new DbTrans();
try {
    Calendar c = Calendar.getInstance();
    c.clear();
    c.set(2000, 0, 1);
    InsertEmployee.execute(trans, 1000, "John Smith", 100000, 
                                new Timestamp(c.getTime().getTime()));
    System.out.println("inserted 1000 John Smith 100000");

    UpdateEmployeeSalaryById.execute(trans, 120000, 1000);
    System.out.println("updated 1000 120000");

    SelectEmployeeById.Row row = SelectEmployeeById.getOneRow(trans, 1000);
    if (row != null) {
    System.out.println("selected " + row.id + " " + row.name  + " "
                                       + row.salary + " " + row.hireDate);
    }
    else {
    System.out.println("error: cannot find employee id 1000");
    }
    DeleteEmployeeById.execute(trans, 1000);
    System.out.println("deleted 1000");

    commit = true;
}
finally {
    trans.close(commit ? trans.COMMIT : trans.ROLLBACK);
}
System.out.println("Statistics dump:");
Statistic.dump(System.out);


Listing Three
/* for oracle database */
DbConnection.init(20, "dev1", "dev1", 
          "jdbc:oracle:thin:@192.168.123.7:1521:SAMPLEDB",
          "oracle.jdbc.driver.OracleDriver");
/* for db2 database */
DbConnection.init(20, "dev1", "dev1", "jdbc:db2:SAMPLEDB",
                             "COM.ibm.db2.jdbc.app.DB2Driver");


Listing Four
("SelectEmployeeById", '''\
select [id:long id], [name:String name], [salary:float salary], 
                                         [hire_date:Timestamp hireDate]
  from EMPLOYEE
 where id = [?:long id]''')


Listing Five
("InsertEmployee", '''\
insert into EMPLOYEE(id, name, salary, hire_date)
 values ([?:long id], [?:String name], [?:float salary], 
                                       [?:Timestamp hireDate])''')



3