News: Think In SQL Initial Release

  1. Think In SQL Initial Release (9 messages)

    ThinkInSQL can be downloaded from http://www.independentreach.com/thinkinsql.htm. Online javadocs are available for viewing at http://www.independentreach.com/ThinkInSql/index.html. Since jdbc drivers provide metadata and java provides reflection, all we need on the bottom tier is to map columns to fields. The examples and guidelines below will refer to the following data objects: public class CustomerRec extends BaseRecord { private long customer_id = 0; private String customer_name=""; private boolean is_active=false; //property gets and sets... } // public class SaleRec extends BaseRecord { private long customer_id = 0; private String customer_po=""; private BigDecimal sale_amount=new BigDecimal(0); private String sale_desc=""; private long sale_id = 0; //property gets and sets... } // public class SaleRow extends BaseRow { private long customer_id = 0; private long sale_id = 0; private String sale_desc=""; private String customer_name=""; //property gets and sets... } Creating Record and Row Objects Use Database.generateClass() to generate a class definition for a table name or select statement. We suggest IRecord implementer names be suffixed with Rec, and read-only IRow implementer names be suffixed with Row. Bear in mind, you do not need to map all columns on a table for an IRecord implementer to be updatable, as long as the primary key is included. Usage by Primary Key Manipulation by primary key is provided as follows: // CustomerRec theCust = new CustomerRec(); // //select by passed keys // if (theCust.select(someValueReceivedFromElsewhere)) System.out.println("Found customer " + someValueReceivedFromElsewhere); // //select embodied // theCust.setCustomerId(someValueReceivedFromElsewhere); if (theCust.select()) System.out.println("Found customer " + theCust.getCustomerId()); // if (theCust.exists()) { theCust.update(); } else { theCust.insert(); } // // write does the same as exists() ? update() : insert() // theCust.write(); // theCust.delete(); Externalizing SQL ThinkInSql explicitly supports an SQL-externalization method as a best practice with several advantages. Our approach is to place all record and row objects for a given database in the same package, and include in that package an xml resource of SQL statements and fragments like the one below. Use Database.loadNamedStatements(URL) if you want to try our approach, or loadNamedStatements(Map) or neither if you prefer not to use named statements. Override Database.overrideNamedStatement to perform custom substitutions. <!--?xml version="1.0" encoding="UTF-8"?--> select * from customer where type_code='WH' select 1 from sale where customer_po=? and sale<>? select * from sale where customer_po=? order by sale_id desc select * from sale where customer_id=? order by sale_id desc update sale set customer_po=? where sale_id=? select s.*,c.customer_name from sale s inner join customer c on s.customer_id=c.customer_id order by customer_name,sale_id desc The advantages of SQL externalization are:
    • non-ANSI-compliant SQL is isolated
    • within a CDATA block, statements can be formatted for readability
    • one could implement a file-last-modified checker to dynamically refresh SQL on the fly
    • statements can be pasted as-is to and from pl-sql or any other database querying tool
    • IDE outline view allows rapid navigation to particular statements
    • all non-generated sql is in one file for searching, peer review, or analysis
    • Database.testAllNamedStatements can be used to quickly unit test all sql after changes
    Some related recommendations:
    • keep the second and third level elements in alphabetical order - it's just easier to find stuff
    • wrap any one sql statement invocation in its own method, eg. public void updateArbitrary() throws Exception { getDatabase().execute("ThisObjectClass.updateArbitrary",parm1,parm2); }
    • name the second level elements for your IRow and IRecord implementers, and the third-level element for the method that wraps the usage of the statement - this makes the mental-context switch from sql to object/method and back again super easy!
    • name your methods like the sql commands they wrap, ie. prefixed with select, insert, update, delete.
    Selecting Data

    To select a record by alternate key, use Database.selectFirst:

    public static SaleRec selectByCustomerPO(String po) throws Exception { SaleRec r = new SaleRec(); if (r.getDatabase().selectFirst("SaleRec.selectByCustomerPO",r,po)) return r; return null; } For relatively small result sets, implement set selections as static methods and return strongly typed sets to consuming code: public static List selectForCustomer(long cust) throws Exception { SaleRec returnTypeTemplate = new SaleRec(); return r.getDatabase().selectRows("SaleRec.selectForCustomer",returnTypeTemplate,-1,cust); } Related persistence objects can consume each other's methods for relation-traversal with easily readable code: //(instance method on CustomerRec) public List selectSales() throws Exception { return SaleRec.selectForCustomer(this.customer_id); } For potentially large result sets, use Database.selectReader in conjunction with an IRow/IRecord implementer: public static IReader selectLots() throws Exception { SaleRow r = new SaleRow(); return r.getDatabase().selectReader("SaleRow.selectLots",-1); } // // somewhere above the data access tier... // IReader rdr = null; try { rdr = SaleRow.selectLots(); SaleRow row = new SaleRow(); while (rdr.next(row)) {//do stuff } } finally { if (rdr != null) rdr.close(); } For scalar selections, see Database.selectScalar and Database.selectScalarList. If you want to retrieve a relatively small set of objects and you don't want to bother defining an IRow, see Database.selectMatrix. Last, if you want to do something directly in jdbc, you can use Database.getConnection and roll your own.

    If you don't want to use select *, or you want to use field aliases, and you need to select the same fields in more than one way, you can combine multiple sql elements to build a statement:

    ... select psacn# as control_number,psast# as street_number, psasdr as street_direction,psas#t as street_number_to,psastr as street_name, d.stades as city_name,psasda as municipality,psapx1 as exchange from lgaddr s inner join lgcity d on s.psadtc=d.stasab INNER JOIN lgcadd a ON a.atacl#=s.psacn# and atexpd>? and a.atapls=1 where a.atcus#=? where upper(psazip)=? and psasts<>'D' order by psacn# ... public class LegacyAddressRow extends BaseRow { ... public static List selectByPostalCode(String pc) throws Exception { LegacyAddressRow returnTemplate = new LegacyAddressRow(); Database db = returnTemplate.getDatabase(); String sql = db.combineNamedStatements( "LegacyAddressRow.selectFrom", "LegacyAddressRow.wherePostalCodeEquals", "LegacyAddressRow.whereSuffix"); return db.selectRows(sql,returnTemplate,1000, pc); } public static List selectForCustomer(long cust) throws Exception { LegacyAddressRow returnTemplate = new LegacyAddressRow(); Database db = returnTemplate.getDatabase(); String sql = db.combineNamedStatements( "LegacyAddressRow.selectFrom", "LegacyAddressRow.whereCustomerEquals", "LegacyAddressRow.whereSuffix"); return db.selectRows(sql,r,1000,todayYmdLongObj,cust); } ... } Arbitrary Updates When you don't want to update all the mapped columns, simply use Database.execute: public void updateCustomerPo() throws Exception { getDatabase().execute("SaleRec.updateCustomerPo",sale,customer_po); } Transactions Use Database.beginTransaction, commit, and rollback, but read the javadocs for each first. ThinkInSql has been used with XA transactions in production. Events ThinkInSql provides plug points for applications to interject processing at different points in the life of IRows, IRecords and Databases:
    • implement IRow.setValueSpecial() to transform data after reading it from a result set and before assigning it to an object member
    • implement IRow.afterRead() to perform processing after loading data from a result set, like building an array
    • implement IRecord.bindSpecial() to provide special binding from an IRow property to a prepared statement
    • implement IRecord.afterUpdate() and/or IRecord.afterDelete() to cascade updates
    • implement IRecord.afterInsert() for post insert processing
    • implement IRecord.beforeUpdate() and/or beforeInsert() and/or beforeDelete() to provide defaults, fixups or exceptional constraints
    • implement IRecord.getNullableNumerics() to implement optional numeric foreign keys
    • implement an UpdateListener to provide centralized code to invoke for all updates
    Mapping Conventions

    When generating data objects, ThinkInSQL uses java property names the same as column names except as follows:

    • any characters in the column name not allowed in a java identifier are replaced with underscores
    • all underscores in the result of the previous step are removed, and the character following the underscore is capitalized
    If a jdbc driver returns column names in mixed case, the case will be respected, except for characters following underscores. If you want to override the field-column mapping conventions, you need to implement getColumns and getFields to instruct ThinkInSQL how to match columns to fields. Secondly, for result sets created from select statements, you are encouraged to use column aliases to make mental matching easier - see LegacyAddressRow above. Multi-Database Applications IRow specifies a getDatabase method, but this is only provided to simplify usage with the object's home database. All of the IRow and IRecord operations can be invoked via Database, with the row object used as a parameter, so copying objects from database to database is as simple as: public static void copyToCache(List lst) throws Exception { CacheDb cdb = CacheDb.open(); try { for (SaleRec r : lst) cdb.insert(r); } finally { cdb.close(); } } Performance ThinkInSQL caches database metadata, row class information, connections created via urls, and prepared statements when the jdbc driver allows, and uses ResultSet.TYPE_FORWARD_ONLY and ResultSet.CONCUR_READ_ONLY.
    We do not cache data because we don't know your data or whether anything other than your application is updating it, or how you might be sharing/pooling/reusing/dispensing Database or Connection instances. Secondly, there are plenty of caching solutions available, use one in the tier above ThinkInSQL. Most importantly of all with respect to performance, ThinkInSQL makes it super easy for you to craft, tune, test and maintain your SQL! Portability ThinkInSQL has been used with Oracle, DB2/400, SQL Server, MySQL, HSQLDB, and Sqlite. If you try it with any database and have trouble, notify the maintainers and we'll try to help. Assumptions / Biases / Quirks
    • IRow implementers must have a public default constructor because we use Class.newInstance()
    • extending IRow and IRecord objects is not recommended
    • the authors tend to work in primitives more than some purists might consider healthy
    • null strings and numbers coming from the database get marshalled to "" and 0 and transformed back to nulls at write time where the column is nullable or getNullableNumerics is applied
    • trailing spaces on character columns are removed
    • externalized SQL is beautiful

    Threaded Messages (9)

  2. Re: Think In SQL Initial Release[ Go to top ]

    Uhm...have you guys ever heard of Apache iBatis? http://ibatis.apache.org/ http://svn.apache.org/repos/asf/ibatis/trunk/java/ibatis-2/ibatis-2-docs/en/iBATIS-SqlMaps-2-Tutorial_en.pdf
  3. Uhm...have you guys ever heard of Apache iBatis?
    Uhm...have you ever heard of Toplink before jumping to Hibernate? RJ
  4. Re: Think In SQL Initial Release[ Go to top ]

    Uhm...have you guys ever heard of Apache iBatis?

    Uhm...have you ever heard of Toplink before jumping to Hibernate?

    What are you trying to imply? iBatis is NOT Hibernate.
  5. Re: Uhm...[ Go to top ]

    Reasonable question with hibernate, iBatis, etc., but ThinkInSql.jar with source and binaries is 141 Kb with no dependancies and no xml config.
  6. Re: Uhm...[ Go to top ]

    JLynx is even smaller at 44kb :-) http://code.google.com/p/jlynx-persistence-framework/ And it uses YAML over XML for any config, a very wise choice: http://code.google.com/p/jlynx-persistence-framework/source/browse/trunk/project/src/test/resources/META-INF/jlynx.yaml
  7. Re: Uhm...[ Go to top ]

    Reasonable question with hibernate, iBatis, etc., but ThinkInSql.jar with source and binaries is 141 Kb with no dependancies and no xml config.
    Read the section on externalizing SQL (which is recommended as a best practice).
  8. Persist[ Go to top ]

    Persist http://code.google.com/p/persist/ is the simplest ORM out there.
  9. Re: Think In SQL Initial Release[ Go to top ]

    Looks very much like iBatis... It seems everyone and their aunt is designing new wheels these days....
  10. Columns are not fields.[ Go to top ]

    ...all we need on the bottom tier is to map columns to fields.
    You call THAT ThinkIn SQL? Really a bad name for such framework. Columns are not fields.