JDBC Table Methods

I am working on some classes to deal with table definitions,
inserts, selects, updates and delete in a somewhat
simple minded way but easy to use. I am not doing for
optimum performance but for easy integration in
servlets.

Take a look at a sample of using these classes and let
me know if anyone is interested in/looking for such a
thing. If so I will GPL it when done, but if it is not of
any interest, I won't bother.

After the code is the SQL generated by this code snip to
give you a feel for what it is doing. Among other things, it
tracks if a field has changed to automatically reflect in
an update statement. Also, this is designed to easily
add a connection pool later on:

----- Original Message -----
From: Frank Morton <fmorton(at)einstein(dot)base2inc(dot)com>
Sent: Sunday, January 17, 1999 9:35 PM

>public class test {
>
>JDBCConnection c;
>
>//-------------------------------------------------------------------------
-----
>// main
>//-------------------------------------------------------------------------
-----
>public static void main(String[] argv) {
>
//-------------------------------------------------------------------------
> // open a database connection
>
//-------------------------------------------------------------------------
> try {
> new test(argv);
> }
> catch(JDBCException e) {
> System.err.println(e.toString());
> System.exit(1);
> }
> }
>
>//-------------------------------------------------------------------------
-----
>// construct
>//-------------------------------------------------------------------------
-----
>public test(String[] argv) throws JDBCException {
> try {
>
//--------------------------------------------------------------------
> // open a database connection
>
//--------------------------------------------------------------------
> c = new JDBCConnection();
>
> c.setDatabaseName("jdbc:postgresql:inventory");
> c.setDatabaseUser("fmorton");
> c.setDatabasePassword("");
> c.setDebug(true);
>
> c.connect();
>
>
//--------------------------------------------------------------------
> // build the table
>
//--------------------------------------------------------------------
> JDBCTable table = new JDBCTable();
>
> table.addTableName("orders");
>
>
//--------------------------------------------------------------------
> // delete entire contents of the table
>
//--------------------------------------------------------------------
> table.delete(c);
>
>
//--------------------------------------------------------------------
> // define the fields
>
//--------------------------------------------------------------------
> table.addFieldName("dbname","pmcompany",true);
> table.addFieldName("orderjobnumber","123456789",true);
> table.addFieldName("orderdescription");
> table.addFieldName("ordertoone");
> table.addFieldName("ordertotwo");
> table.addFieldName("ordertothree");
> table.addFieldName("ordertofour");
> table.addFieldName("orderattention");
> table.addFieldName("orderdate","1/17/1999");
> table.addFieldName("ordercustomerponumber","ponum5567");
> table.addFieldName("ordercustomerjobnumber");
> table.addFieldName("orderdeliverydate","2/1/1999");
> table.addFieldName("orderspecialinstructions","special");
> table.addFieldName("orderstatus","OPEN");
>
>
//--------------------------------------------------------------------
> // optional ways to set a key
>
//--------------------------------------------------------------------
> //table.setFieldKeysByName("dbname","pmcompany");
>
> table.setFieldKeysByName("dbname");
> table.setFieldKeysByName("orderjobnumber");
>
>
//--------------------------------------------------------------------
> // show appropriate sql
>
//--------------------------------------------------------------------
> //System.err.println("DEBUG: insert: " + table.sqlInsert());
> //System.err.println("DEBUG: insert(true): " +
table.sqlInsert(true));
> //System.err.println("DEBUG: insert(false): " +
table.sqlInsert(false));
> //System.err.println("DEBUG: delete: " + table.sqlDelete());
> //System.err.println("DEBUG: select all: " +
table.sqlSelectAll());
> //System.err.println("DEBUG: select: " + table.sqlSelect());
> //System.err.println("DEBUG: update: " + table.sqlUpdate());
>
>
//--------------------------------------------------------------------
> // make 6 new orders
>
//--------------------------------------------------------------------
> table.setFieldContentByName("orderjobnumber","10001");
> table.setFieldContentByName("orderdescription","Order Number
10001");
> table.insert(c);
> table.setFieldContentByName("orderjobnumber","10002");
> table.setFieldContentByName("orderdescription","Order Number
10002");
> table.insert(c);
> table.setFieldContentByName("orderjobnumber","10003");
> table.setFieldContentByName("orderdescription","Order Number
10003");
> table.insert(c);
> table.setFieldContentByName("orderjobnumber","10004");
> table.setFieldContentByName("orderdescription","Order Number
10004");
> table.insert(c);
> table.setFieldContentByName("orderjobnumber","10005");
> table.setFieldContentByName("orderdescription","Order Number
10005");
> table.insert(c);
> table.setFieldContentByName("orderjobnumber","10006");
> table.setFieldContentByName("orderdescription","Order Number
10006");
> table.insertAll(c);
>
>
//--------------------------------------------------------------------
> // select the second order
>
//--------------------------------------------------------------------
> table.setFieldContentByName("orderjobnumber","10002");
> table.selectAll(c);
>
>
//--------------------------------------------------------------------
> // look at each row
>
//--------------------------------------------------------------------
> String fieldName = table.getFieldName(6);
>
> while(table.next()) {
> table.setFieldContentByName(fieldName,"NEW STUFF");
> table.setFieldContentByName("orderjobnumber","new key");
> table.setFieldContentByName(table.getFieldName(9),"Other NEW
STUFF");
> table.update(c);
> }
> }
> finally {
>
//--------------------------------------------------------------------
> // always close the connection
>
//--------------------------------------------------------------------
> if(c != null) c.close();
> }
> }
>}

delete from orders;
insert into orders
(dbname,orderjobnumber,orderdescription,ordertoone,ordertotwo,ordertothree,o
rdertofour,orderattention,orderdate,ordercustomerponumber,ordercustomerjobnu
mber,orderdeliverydate,orderspecialinstructions,orderstatus) values
('pmcompany','10001','Order Number
10001','','','','','','1/17/1999','ponum5567','','2/1/1999','special','OPEN'
);
insert into orders
(dbname,orderjobnumber,orderdescription,ordertoone,ordertotwo,ordertothree,o
rdertofour,orderattention,orderdate,ordercustomerponumber,ordercustomerjobnu
mber,orderdeliverydate,orderspecialinstructions,orderstatus) values
('pmcompany','10002','Order Number
10002','','','','','','1/17/1999','ponum5567','','2/1/1999','special','OPEN'
);
insert into orders
(dbname,orderjobnumber,orderdescription,ordertoone,ordertotwo,ordertothree,o
rdertofour,orderattention,orderdate,ordercustomerponumber,ordercustomerjobnu
mber,orderdeliverydate,orderspecialinstructions,orderstatus) values
('pmcompany','10003','Order Number
10003','','','','','','1/17/1999','ponum5567','','2/1/1999','special','OPEN'
);
insert into orders
(dbname,orderjobnumber,orderdescription,ordertoone,ordertotwo,ordertothree,o
rdertofour,orderattention,orderdate,ordercustomerponumber,ordercustomerjobnu
mber,orderdeliverydate,orderspecialinstructions,orderstatus) values
('pmcompany','10004','Order Number
10004','','','','','','1/17/1999','ponum5567','','2/1/1999','special','OPEN'
);
insert into orders
(dbname,orderjobnumber,orderdescription,ordertoone,ordertotwo,ordertothree,o
rdertofour,orderattention,orderdate,ordercustomerponumber,ordercustomerjobnu
mber,orderdeliverydate,orderspecialinstructions,orderstatus) values
('pmcompany','10005','Order Number
10005','','','','','','1/17/1999','ponum5567','','2/1/1999','special','OPEN'
);
insert into orders values ('pmcompany','10006','Order Number
10006','','','','','','1/17/1999','ponum5567','','2/1/1999','special','OPEN'
);
select * from orders where dbname='pmcompany' and orderjobnumber='10002';
update orders set orderjobnumber='new key',ordertofour='NEW
STUFF',ordercustomerponumber='Other NEW STUFF' where dbname='pmcompany' and
orderjobnumber='10002';