JDBC Table Methods - Mailing list pgsql-interfaces

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@einstein.base2inc.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';