package ;
import java.sql.*;
import java.util.*;
import java.io.*;
/*
*
*
* $Author: jd $
* $Date: 2002/08/10 02:57:13 $
* $Revisions: $
* $Source: /cvs/sqlObject/oracleObjectPersisterTemplate.xsl,v $
* To create a new
*
* instance = new
* .. call the set methods // Set the fields as needed
* String key = instance.store(); // .store() inserts record to database
* // DB sequence "" is used to get
* // next primary key
*
* To get a from the database
* // To retrieve a from database
* instance = new ("1234"); // pass the primary key to constructor
* ... call the get() methods // and then call the getter methods
*
*
* To update an existing
* // To update an existing OrderObj
* instance = new ("1234"); // get it from database, call the setter methods
* .. call the set methods // and then call store()
* instance.store();
*
*
*/
public class {
private long key = 0;
private =
""
0
0
0
0
null
""
""
;
public = null;
private ArrayList arrayListOfs = null; //Store sub-objects to this one
private ArrayList arrayListOfs = null; //Store sub-objects to this one
private boolean isDirty = false;
/**
*
* To get an existing call constructor with primary key
*
*/
public (long keyIn) {
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
stmt = conn.createStatement();
} catch (SQLException exp) {
log("Driver(key) constructor, Exception: " + exp.getMessage());
}
key = keyIn;
ResultSet rset = doSelect(conn, stmt);
= new (rset);
try {
rset.close();
stmt.close();
conn.close();
} catch (SQLException exp) {
log("(key) constructor, Exception: " + exp.getMessage());
}
loads();
loads();
}
/**
*
* To populate a where the sql select was done earlier by another object
* call this constructor with the result set.
*
*/
public (ResultSet rset) {
applyResultSet(rset); //Populate this object with values from db
= new (rset);
}
/***
* To create a new call no args constructor
* then call the "set()" methods, and finally "store()"
*/
public () {
= new ();
}
/**
*
* Persist our properties to the db. Also call store() on any dependent objects.
* If no Connection was passed in then we are responisible for beginning the transaction
* Turn off auto commit, and rollback if we get exception from this or any dependent objects
*
*/
public long store() throws Exception {
long returnKey = key;
Connection conn = getConnection();
try {
conn.setAutoCommit(false);
log(".store(), Starting transaction");
returnKey = store(conn);
conn.commit();
log(".store(), No Exceptions Committing");
}
catch (Exception exp) { //Any exception thrown by store causes a rollback.
log(".store(), Exception: " + exp.getMessage());
try {
log(".store(), Failed: RollingBack");
conn.rollback();
} catch (SQLException rollbackExp) {
log(".store(), Exception: " + rollbackExp.getMessage());
}
throw exp; //Throw exception back up so that user interface can report error
}
finally {
try {
conn.close();
} catch (SQLException closeExp) {
log(".store(), Exception: " + closeExp.getMessage());
}
}
return returnKey;
}
/**
*
* Persist our properties to the db. Also call store() on any dependent objects.
* If a Connection was passed in then a object higher up is handling the transactions
* for us. Throw on any exceptions so that the higher object can catch them and rollback.
* This method will do an insert for new and a update for existing ones
*
*/
public long store(Connection conn) throws Exception {
long returnKey = key;
if ( key == 0 ) {
= .store(conn);
returnKey = insertToDB(conn);
}
else {
.store(conn);
if ( isDirty ) { updateOnDB(conn); }
}
return returnKey;
}
/**
*
*/
private ResultSet doSelect(Connection conn, Statement stmt) {
ResultSet rset = null;
try {
String sqlStmt = "select \n" +
" . as _, \n" +
" . as _
, \n" +
" . as _
\n" +
" from , \n" +
" \n" +
" where . = " + key
+ " and \n";
log("sqlStmt=" + sqlStmt);
rset = stmt.executeQuery(sqlStmt);
rset.next();
applyResultSet(rset);
}
catch (Exception exp) {
log(".doSelect(), SQLException: " + exp.getMessage());
}
return rset;
}
/**
*
*/
private void applyResultSet(ResultSet rset) {
TimeZone timezone = TimeZone.getDefault();
GregorianCalendar gc = null;
java.sql.Clob textClob = null;
InputStream in = null;
int b = 0;
StringBuffer stringBuffer = null;
try {
key = rset.getLong("_");
= rset.getString("_");
= rset.getInt("_");
= rset.getLong("_");
= rset.getFloat("_");
= rset.getDouble("_");
if ( rset.getTimestamp("_") != null) {
gc = new GregorianCalendar(timezone);
gc.setTime(new java.util.Date( rset.getTimestamp("_").getTime()));
= gc;
}
textClob = rset.getClob("_");
in = textClob.getAsciiStream();
b = 0;
stringBuffer = new StringBuffer();
if ( in != null) {
while ( (b = in.read()) != -1 ) {
stringBuffer.append( (char)b);
}
= stringBuffer.toString();
}
in = rset.getBinaryStream("_");
if ( in != null) {
stringBuffer = new StringBuffer(5000);
while ( (b = in.read()) != -1 ) {
stringBuffer.append( (char)b);
}
= stringBuffer.toString();
}
isDirty = false;
}
catch (Exception exp) {
log(".applyResultSet(): SQLException:" + exp.getMessage());
}
}
/**
*
*/
private long insertToDB(Connection conn) throws Exception {
long newKey = 0;
String sqlStmt = "";
ByteArrayInputStream bs = null;
InputStream in = null;
Statement stmt = null;
ResultSet rset = null;
sqlStmt = "select .nextval from dual \n";
stmt = conn.createStatement();
rset = stmt.executeQuery(sqlStmt);
rset.next();
newKey = rset.getLong(1);
log("newKey=" + newKey);
sqlStmt = "insert into " +
" ( " +
" , \n" +
" , \n" +
" ) " +
" values ( ?, ? sysdate, )";
log("Sql stmt=" + sqlStmt);
PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
pStmt.setLong(1,newKey);
int rowsInserted = pStmt.executeUpdate();
log(" rows inserted =" + rowsInserted);
if ( rowsInserted != 1) {
log("Error: .insertToDB failed no rows inserted");
}
pStmt.close();
isDirty = false;
return newKey;
}
/**
*
*/
private void updateOnDB(Connection conn) throws Exception {
ByteArrayInputStream bs = null;
InputStream in = null;
String sqlStmt = "update " +
" set " +
" = sysdate ?, \n" +
" where = '" + key + "'";
log("Sql stmt=" + sqlStmt);
PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
int rowsUpdated = pStmt.executeUpdate();
if ( rowsUpdated != 1) {
log("Error: .insertToDB failed no rows inserted");
}
pStmt.close();
isDirty = false;
}
/**
*
* Delete an
* Also call delete() on any dependent objects.
* If no Connection was passed in then we are responisible for beginning the transaction
* Turn off auto commit, and rollback if we get exception from this or any dependent objects
*
*/
public void delete() throws Exception {
long returnKey = key;
Connection conn = getConnection();
try {
conn.setAutoCommit(false);
delete(conn);
conn.commit();
}
catch (Exception exp) { //Any exception thrown by store causes a rollback.
log(".delete(), Exception: " + exp.getMessage());
try {
conn.rollback();
} catch (SQLException rollbackExp) {
log(".delete(), Exception: " + rollbackExp.getMessage());
}
throw exp; //Throw exception back up so that user interface can report error
}
finally {
try {
conn.close();
} catch (SQLException closeExp) {
log(".delete(), Exception: " + closeExp.getMessage());
}
}
}
/**
*
*
*/
public void delete(Connection conn) throws Exception {
.delete(conn);
Iterator iteratorOfs = lists();
while (iteratorOfs.hasNext() ) {
object = ()iteratorOfs.next();
object.delete();
}
// Before deleting this remove all s linked to it.
// By deleting the row on the xref table , that links them.
Iterator iteratorOfs = lists();
while (iteratorOfs.hasNext() ) {
object = ()iteratorOfs.next();
remove(conn, object);
}
String sqlStmt = "delete from " +
"where = ?";
PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
pStmt.setLong(1,key);
int rowsDeleted = pStmt.executeUpdate();
log(" Deleted row from where primary key was " + key);
if ( rowsDeleted != 1) {
log("Error: .delete failed no rows deleted");
}
}
public long getKey() { return key; }
public get() {
return ;
}
public void set( in) {
= in;
isDirty = true;
}
// Get Methods for joined objects in
public get() {
return ;
}
// Getter Methods for properties in
public get() {
return .get();
}
// Setter Methods for properties in
public void set( in) {
.set(in);
}
/**
* Get all s for this
*/
private void loads() {
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
arrayListOfs = new ArrayList();
String sqlStmt = "select \n" +
" . as _, \n" +
" from , " +
" \n" +
" where . = . \n" +
" and . = " + key;
try {
conn = getConnection();
stmt = conn.createStatement();
log("sqlStmt=" + sqlStmt);
rset = stmt.executeQuery(sqlStmt);
while ( rset.next() ) {
object = new (rset);
arrayListOfs.add(object);
}
}
catch (Exception exp) {
log("gets(), SQLException: " + exp.getMessage());
}
finally {
try {
rset.close();
stmt.close();
conn.close();
} catch (SQLException exp) {
log("gets()- closing rset,stmt,conn: Exception: " + exp.getMessage());
}
}
}
/**
* Get Iterator of s
*/
public Iterator lists() {
return arrayListOfs.iterator();
}
/**
* Add a
* To add a instantiate one,
* call it's set() methods, but dont store(). Call this method passing in the
* . This method will link it with this
* object/table and store() it.
*/
public long add( in) throws Exception {
long newKey = 0;
in.set(key);
newKey = in.store();
loads();
return newKey;
}
/**
* Remove a
* To delete a call this method.
* This will delete this row compeletly no just de-link it from this row.
*/
public void remove( in) throws Exception {
in.delete();
loads();
}
/**
* Get all s for this
* Join is done through an xref table:
*/
private void loads() {
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
arrayListOfs = new ArrayList();
String sqlStmt = "select \n" +
" from \n" +
" where = " + key;
try {
conn = getConnection();
stmt = conn.createStatement();
log("sqlStmt=" + sqlStmt);
rset = stmt.executeQuery(sqlStmt);
while ( rset.next() ) {
object = new (rset.getLong(1));
arrayListOfs.add(object);
}
}
catch (Exception exp) {
log("gets(), SQLException: " + exp.getMessage());
}
finally {
try {
rset.close();
stmt.close();
conn.close();
} catch (SQLException exp) {
log("gets()- closing rset,stmt,conn: Exception: " + exp.getMessage());
}
}
}
/**
* Get Iterator of s
*/
public Iterator lists() {
return arrayListOfs.iterator();
}
/**
* Add to this
* by inserting a row to the xref table linking them.
*/
public void add(Connection conn, in) throws Exception {
String sqlStmt = null;
sqlStmt = "insert into " +
" ( , ) " +
" values ( ?, ? )";
log("Sql stmt=" + sqlStmt);
PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
pStmt.setLong(1, key);
pStmt.setLong(2, in.getKey());
int rowsInserted = pStmt.executeUpdate();
log(" rows inserted =" + rowsInserted);
if ( rowsInserted != 1) {
log("Error: insert to xref table failed no rows inserted");
}
pStmt.close();
}
/**
* same as above but used when Connection object not supplied
*/
public void add( in) throws Exception {
Connection conn = getConnection();
add(conn, in);
}
/**
* Remove a from this
* by deleting the row on the xref table , that links
* them.
*/
public void remove(Connection conn, in) throws Exception {
String sqlStmt = null;
sqlStmt = "delete from " +
" where = ? and = ? ";
log("Sql stmt=" + sqlStmt);
PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
pStmt.setLong(1, key);
pStmt.setLong(2, in.getKey());
int rowsDeleted = pStmt.executeUpdate();
log(" rows deleted =" + rowsDeleted);
if ( rowsDeleted != 1) {
log("Error: delete from xref table failed no rows deleted");
}
pStmt.close();
}
/**
* same as above but used when Connection object not supplied
*/
public void remove( in) throws Exception {
Connection conn = getConnection();
remove(conn, in);
}
/**
* Write to Log
*/
private void log(String messageIn) {
System.out.println(messageIn);
}
/**
* Get JDBC Connection to the DataBase
* Note: this should be returning connections from a pool.
*/
private Connection getConnection() {
Connection conn = null;
String dbServer = "@myserver.mycompnay.com";
String dbPort = "1526";
String dbSid = "mysid";
String dbUserId = "username";
String dbPassword = "password";
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String connectString = "jdbc:oracle:thin:" + dbServer + ":" + dbPort + ":" + dbSid;
conn = DriverManager.getConnection (connectString, dbUserId, dbPassword);
}
catch (SQLException exp) {
log("Exception: " + exp.getMessage() );
}
return conn;
}
/**
* A main method for testing
*/
public static void main(String args[] ) {
object = new (1);
System.out.println(" = " + object. );
}
}