Overview  Package  Class  Use  Tree  Deprecated  Index  Help 
 PREV PACKAGE   NEXT PACKAGE FRAMES    NO FRAMES    

Package ir.thinkinsql

ThinkInSQL by Independent Reach

See:
          Description

Interface Summary
IReader Specifies the behavior of objects that provide data from ResultSets to IRow implementers.
IRecord Specifies the behavior of objects that represent a row of a table.
IRow Specifies the behavior of objects that represent a row of a ResultSet.
UpdateListener Specifies the behavior of objects that receive notification of update activity performed through a Database.
 

Class Summary
BaseRecord Provides an IRecord adapter, and BaseRow enhancer.
BaseRow Provides an IRow adapter.
ChangedColumn Returned from IRecord.getChanges.
ColDef Is a container for column metadata.
Database Provides access to persistence services.
XmlMapper Used to externalize SQL statements.
 

Package ir.thinkinsql Description

ThinkInSQL by Independent Reach

Since jdbc drivers provide metadata and java provides reflection, all we need on the bottom tier is to map columns to fields.

Author


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"?>
<sql>

<CustomerRec>
    <selectWholesale><![CDATA[
        select * from customer where type_code='WH'
    ]]></selectWholesale>
</CustomerRec>

<SaleRec>
    <isCustomerPoUsed><![CDATA[
        select 1 from sale where customer_po=? and sale<>? 
    ]]></isCustomerPoUsed>
    <selectByCustomerPo><![CDATA[
        select * from sale where customer_po=? order by sale_id desc
    ]]></selectByCustomerPo>
    <selectForCustomer><![CDATA[
        select * from sale where customer_id=? order by sale_id desc
    ]]></selectForCustomer>
    <updateCustomerPo><![CDATA[
        update sale set customer_po=? where sale_id=?
    ]]></updateCustomerPo>
</SaleRec>

<SaleRow>
    <selectLotsOfSales><![CDATA[
        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
    ]]></selectLotsOfSales>
</SaleRow>

</sql>
The advantages of SQL externalization are: Some related recommendations:

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<SaleRec> 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<SaleRec> 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:

...
<LegacyAddressRow>
    <selectFrom><![CDATA[
        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  
    ]]></selectFrom>
    <whereCustomerEquals><![CDATA[
        INNER JOIN lgcadd a ON a.atacl#=s.psacn# and atexpd>? and a.atapls=1  
        where a.atcus#=? 
    ]]></whereCustomerEquals>
    <wherePostalCodeEquals><![CDATA[
        where upper(psazip)=?
    ]]></wherePostalCodeEquals>
    <whereSuffix><![CDATA[ 
        and psasts<>'D' order by psacn#
    ]]></whereSuffix>
</LegacyAddressRow>
...

public class LegacyAddressRow extends BaseRow
{
    ...
    public static List<LegacyAddressRow> 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<LegacyAddressRow> 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:

Mapping Conventions

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

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.
Note that Java Enum types can be bound to database columns holding strings or numbers. We recommend strings - using the name of the enum is more change-tolerant the then ordinal.

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<SaleRec> 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. 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

Overview  Package  Class  Use  Tree  Deprecated  Index  Help 
 PREV PACKAGE   NEXT PACKAGE FRAMES    NO FRAMES