Changeset 28

Timestamp:

Feb 9, 2011, 10:53:45 PM (14 years ago)

Author:

fmguler

Message:

Refs #3 - Ven.get() is converted to the new format. QueryGenerator and QueryMapper are mostly OK, generateRecursively and mapRecursively are converted and checked. Handles joins (includes/associations) many to one and one to many. For one to many, the reverse join field can be determined in a couple of ways. First way is (prefereed) having VenList as the list implementation which specifies the element class and the join field. Second way is using Java 1.5 generic type to detect element class (not yet implemented) and guessing join field by convention (if multiple joins exist, this won't work). The last way is to have some kind of annotation or configuration, which is of course the least preferred way. VenList has a static method to determine the element class in the object list, which currently calls getElementClass if the list is an instance of VenList. In the future other options can be implemented.

Getting object using joins (includes/associations) are tested using dummy assocations between SomeDomainObject and AnotherDomainObject. The Sample class builds the database, tests the operations and rolls back to the initial state. Database refactoring operations are moved to the LiquibaseUtil for clarity.

In the future, the generated queries will be shortened using hashed aliases, and the criteria subsystem will be implemented.

Location:

trunk/fmgVen

Files:

• etc/test-db/test-db-changelog.xml (modified) (2 diffs)
• src/com/fmguler/ven/QueryGenerator.java (modified) (5 diffs)
• src/com/fmguler/ven/QueryMapper.java (modified) (3 diffs)
• src/com/fmguler/ven/Ven.java (modified) (4 diffs)
• src/com/fmguler/ven/util/VenList.java (added)
• test/com/fmguler/ven/LiquibaseUtil.java (added)
• test/com/fmguler/ven/sample/Sample.java (modified) (5 diffs)
• test/com/fmguler/ven/sample/domain/AnotherDomainObject.java (added)
• test/com/fmguler/ven/sample/domain/SomeDomainObject.java (modified) (3 diffs)

trunk/fmgVen/etc/test-db/test-db-changelog.xml

@@ -15 +15 @@
             <column name="description" type="TEXT(2147483647)"/>
             <column name="date" type="TIMESTAMP WITHOUT TIME ZONE"/>
+            <column name="another_domain_object_id" type="int"/>
         </createTable>
     </changeSet>
     <changeSet author="fmguler" id="2">
-        <tagDatabase tag="tag-single-table"/>
+        <createTable schemaName="public" tableName="another_domain_object">
+            <column autoIncrement="true" name="id" type="serial">
+                <constraints nullable="false" primaryKey="true" primaryKeyName="another_domain_object_pkey"/>
+            </column>
+            <column name="name" type="VARCHAR(100)"/>
+            <column name="description" type="TEXT(2147483647)"/>
+            <column name="date" type="TIMESTAMP WITHOUT TIME ZONE"/>
+            <column name="some_domain_object_id" type="int"/>
+        </createTable>
     </changeSet>
     <changeSet author="fmguler" id="3">
+        <tagDatabase tag="tag-no-data"/>
+    </changeSet>
+    <changeSet author="fmguler" id="4">
         <insert schemaName="public" tableName="some_domain_object">
-            <column name="id" value="1"/>
-            <column name="name" value="name1"/>
-            <column name="description" value="desc1"/>
+            <column name="name" value="sdo1"/>
+            <column name="description" value="sdo desc1"/>
             <column name="date" value="2010-10-13"/>
+            <column name="another_domain_object_id" value="1"/>
         </insert>
         <rollback>
@@ -31 +43 @@
         </rollback>
     </changeSet>
-    <changeSet author="fmguler" id="4">
-        <tagDatabase tag="tag-single-table-data"/>
+    <changeSet author="fmguler" id="5">
+        <insert schemaName="public" tableName="another_domain_object">
+            <column name="name" value="ado1"/>
+            <column name="description" value="ado desc1"/>
+            <column name="date" value="2011-01-19"/>
+            <column name="some_domain_object_id" value="1"/>
+        </insert>
+        <insert schemaName="public" tableName="another_domain_object">
+            <column name="name" value="ado2"/>
+            <column name="description" value="ado desc2"/>
+            <column name="date" value="2011-02-09"/>
+            <column name="some_domain_object_id" value="1"/>
+        </insert>
+        <rollback>
+            delete from another_domain_object;
+        </rollback>
+    </changeSet>
+    <changeSet author="fmguler" id="6">
+        <tagDatabase tag="tag-data"/>
     </changeSet>
     <!-- << TEST SCHEMA -->

trunk/fmgVen/src/com/fmguler/ven/QueryGenerator.java

@@ -19 +19 @@
 
 import com.fmguler.ven.util.Convert;
+import com.fmguler.ven.util.VenList;
 import java.beans.PropertyDescriptor;
 import java.util.Date;
 import java.util.HashSet;
+import java.util.Iterator;
+import java.util.List;
 import java.util.Set;
 import org.springframework.beans.BeanWrapper;
@@ -33 +36 @@
     private Set domainPackages;
     private Set dbClasses;
+    private boolean debug = true;
 
     public QueryGenerator() {
@@ -45 +49 @@
     }
 
-    public String generateSelectQuery() {
-        return null;
+    /**
+     * Generates select query for the specified object class and specified joins.
+     * @param joins set of joins that the query will contain
+     * @param objectClass the object class to select from
+     * @return the select SQL query
+     */
+    public String generateSelectQuery(Class objectClass, Set joins) {
+        long t1 = System.currentTimeMillis();
+        String objectName = Convert.toSimpleName(objectClass.getName());
+        String tableName = Convert.toDB(objectName);
+        StringBuffer selectClause = new StringBuffer("select ");
+        StringBuffer fromClause = new StringBuffer("from " + tableName);
+        generateRecursively(0, tableName, objectName, objectClass, joins, selectClause, fromClause);
+        selectClause.append(" 1=1");
+        if (debug) System.out.println("Ven - query generation time = " + (System.currentTimeMillis() - t1));
+        return selectClause.toString() + " \n" + fromClause.toString();
     }
 
@@ -129 +147 @@
      * @return the delete SQL query
      */
-    public String generateDeleteQuery(Class objectClass){
+    public String generateDeleteQuery(Class objectClass) {
         StringBuffer query = new StringBuffer();
         query.append("delete from ").append(Convert.toDB(Convert.toSimpleName(objectClass.getName()))).append(" where id = :id;");
         return query.toString();
     }
-        
+
     /**
      * Generates sequence query for the specified object
@@ -147 +165 @@
 
     //--------------------------------------------------------------------------
+    //PRIVATE METHODS
+    //recursively generate select query
+    private void generateRecursively(int level, String tableName, String objectPath, Class objectClass, Set joins, StringBuffer selectClause, StringBuffer fromClause) {
+        BeanWrapper wr = new BeanWrapperImpl(objectClass);
+        PropertyDescriptor[] pdArr = wr.getPropertyDescriptors();
+
+        for (int i = 0; i < pdArr.length; i++) {
+            Class fieldClass = pdArr[i].getPropertyType(); //field class
+            String fieldName = pdArr[i].getName(); //field name
+            Object fieldValue = wr.getPropertyValue(fieldName);
+            String columnName = Convert.toDB(pdArr[i].getName()); //column name
+
+            //direct database class (Integer, String, Date, etc)
+            if (dbClasses.contains(fieldClass)) {
+                selectClause.append(tableName).append(".").append(columnName).append(" as ").append(tableName).append("_").append(columnName); //column
+                selectClause.append(", ");
+            }
+
+            //many to one association (object property)
+            if (fieldClass.getPackage() != null && domainPackages.contains(fieldClass.getPackage().getName()) && joinsContain(joins, objectPath + "." + fieldName)) {
+                String joinTableAlias = tableName + "_" + columnName; //alias for table to join since there can be multiple joins to the same table
+                String joinTable = Convert.toDB(Convert.toSimpleName(fieldClass.getName())); //table to join
+                fromClause.append(" left join ").append(joinTable).append(" ").append(joinTableAlias);
+                fromClause.append(" on ").append(joinTableAlias).append(".id = ").append(tableName).append(".").append(columnName).append("_id");
+                generateRecursively(++level, joinTableAlias, objectPath + "." + fieldName, fieldClass, joins, selectClause, fromClause);
+            }
+
+            //one to many association (list property)
+            if (fieldValue instanceof List && joinsContain(joins, objectPath + "." + fieldName)) {
+                Class elementClass = VenList.findElementClass((List)fieldValue);
+                String joinTableAlias = tableName + "_" + columnName; //alias for table to join since there can be multiple joins to the same table
+                String joinTable = Convert.toDB(Convert.toSimpleName(elementClass.getName())); //table to join
+                String joinField = Convert.toDB(findJoinField((List)fieldValue)); //field to join
+                fromClause.append(" left join ").append(joinTable).append(" ").append(joinTableAlias);
+                fromClause.append(" on ").append(joinTableAlias).append(".").append(joinField).append("_id = ").append(tableName).append(".id");
+                generateRecursively(++level, joinTableAlias, objectPath + "." + fieldName, elementClass, joins, selectClause, fromClause);
+            }
+        }
+    }
+
+    //check if the joins contain the specified join
+    private boolean joinsContain(Set joins, String join) {
+        Iterator it = joins.iterator();
+        while (it.hasNext()) {
+            String str = (String)it.next();
+            if (str.startsWith(join)) {
+                if (str.length() == join.length()) return true;
+                else if (str.charAt(join.length()) == '.') return true;
+            }
+        }
+        return false;
+    }
+
+    //return the join field of the elements in the list
+    private String findJoinField(List list) {
+        if (list instanceof VenList) {
+            return ((VenList)list).getJoinField();
+        } else {
+            //find according to 1.5 generic or some convention (e.g. parent_obj_id)
+            return null;
+        }
+    }
+
+    //--------------------------------------------------------------------------
     //SETTERS
+    /**
+     * Add the domain packages that will be considered persistent
+     * @param domainPackage the domain package
+     */
     public void addDomainPackage(String domainPackage) {
         domainPackages.add(domainPackage);

trunk/fmgVen/src/com/fmguler/ven/QueryMapper.java

@@ -18 +18 @@
 package com.fmguler.ven;
 
+import com.fmguler.ven.util.Convert;
+import com.fmguler.ven.util.VenList;
+import java.beans.PropertyDescriptor;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.HashSet;
+import java.util.Iterator;
+import java.util.LinkedList;
 import java.util.List;
+import java.util.Map;
+import java.util.Set;
 import javax.sql.DataSource;
+import org.springframework.beans.BeanWrapper;
+import org.springframework.beans.BeanWrapperImpl;
+import org.springframework.jdbc.core.RowCallbackHandler;
 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 
@@ -28 +43 @@
 public class QueryMapper {
     private NamedParameterJdbcTemplate template;
+    private Set domainPackages;
+    private Set dbClasses;
+    private boolean debug = true;
 
-    public List list() {
-        return null;
+    public QueryMapper() {
+        domainPackages = new HashSet();
+        dbClasses = new HashSet();
+        //the predefined database classes;
+        this.dbClasses.add(Integer.class);
+        this.dbClasses.add(String.class);
+        this.dbClasses.add(Date.class);
+        this.dbClasses.add(Double.class);
+        this.dbClasses.add(Boolean.class);
     }
 
-    //SETTERS-------------------------------------------------------------------
+    /**
+     * Executes the specified query setting the specified parameters,
+     * and maps the results to the instances of the specified objectClass.
+     * @param query select SQL query in the conventional format
+     * @param parameters named query parameter values
+     * @param objectClass the type of the object to be mapped
+     * @return the list of mapped objects
+     */
+    public List list(String query, Map parameters, final Class objectClass) {
+        long t1 = System.currentTimeMillis();
+        final List results = new LinkedList();
+        final String tableName = Convert.toDB(Convert.toSimpleName(objectClass.getName()));
+        final Set columns = new HashSet();
+
+        template.query(query, parameters, new RowCallbackHandler() {
+            public void processRow(ResultSet rs) throws SQLException {
+                enumerateColumns(columns, rs);
+                mapRecursively(rs, columns, tableName, objectClass, results);
+            }
+        });
+
+        System.out.println("Ven - list time = " + (System.currentTimeMillis() - t1));
+        return results;
+    }
+
+    //--------------------------------------------------------------------------
+    //PRIVATE METHODS
+    //recursively map the resultSet to the object and add to the list
+    protected void mapRecursively(ResultSet rs, Set columns, String tableName, Class objectClass, List parentList) {
+        try {
+            if (!columns.contains(tableName + "_id")) return; //this object does not exist in the columns
+            Object id = rs.getObject(tableName + "_id");
+            if (id == null) return; //this object exists in the columns but null, probably because of left join
+
+            //create bean wrapper for the object class
+            BeanWrapper wr = new BeanWrapperImpl(objectClass); //already caches class introspection (CachedIntrospectionResults.forClass())
+            wr.setPropertyValue("id", id); //set the id property
+            Object object = wr.getWrappedInstance();
+            boolean map = true;
+
+            //check if this object exists in the parent list (since SQL joins are cartesian products, do not create new object if this row is just the same as previous)
+            for (Iterator it = parentList.iterator(); it.hasNext();) {
+                Object objectInList = (Object)it.next();
+                if (objectIdEquals(objectInList, id)) {
+                    wr.setWrappedInstance(objectInList); //already exists in the list, use that instance
+                    map = false; // and do not map again
+                    break;
+                }
+            }
+            if (map) parentList.add(object); //could not find in the parent list, add the new object
+
+            PropertyDescriptor[] pdArr = wr.getPropertyDescriptors();
+            for (int i = 0; i < pdArr.length; i++) {
+                PropertyDescriptor pd = pdArr[i];
+                Class fieldClass = pd.getPropertyType(); //field class
+                String fieldName = Convert.toDB(pd.getName()); //field name
+                Object fieldValue = wr.getPropertyValue(pd.getName());
+                String columnName = tableName + "_" + fieldName;
+
+                //database class (primitive property)
+                if (map && dbClasses.contains(fieldClass)) {
+                    if (columns.contains(columnName)) {
+                        if (debug) System.out.println(">>field is found: " + columnName);
+                        wr.setPropertyValue(pd.getName(), rs.getObject(columnName));
+                    } else {
+                        if (debug) System.out.println("--field not found: " + columnName);
+                    }
+                }
+
+                //many to one association (object property)
+                if (map && fieldClass.getPackage() != null && domainPackages.contains(fieldClass.getPackage().getName())) {
+                    if (columns.contains(columnName + "_id")) {
+                        if (debug) System.out.println(">>object is found " + columnName);
+                        List list = new ArrayList(1); //we know there will be single result
+                        mapRecursively(rs, columns, columnName, fieldClass, list);
+                        if (list.size() > 0) wr.setPropertyValue(pd.getName(), list.get(0));
+                    } else {
+                        if (debug) System.out.println("--object not found: " + columnName);
+                    }
+                }
+
+                //one to many association (list property)
+                if (fieldValue instanceof List) { //Note: here recurring row's list property is mapped and add to parent's list
+                    if (columns.contains(columnName + "_id")) {
+                        Class elementClass = VenList.findElementClass((List)fieldValue);
+                        if (debug) System.out.println(">>list is found " + columnName);
+                        mapRecursively(rs, columns, columnName, elementClass, (List)fieldValue);
+                    } else {
+                        if (debug) System.out.println("--list not found: " + columnName);
+                    }
+                }
+            }
+        } catch (Exception ex) {
+            if (debug) {
+                System.out.println("Ven - error while mapping row; ");
+                ex.printStackTrace();
+            }
+        }
+    }
+
+    //enumerate columns from the resultset
+    private void enumerateColumns(Set columns, ResultSet rs) throws SQLException {
+        if (!columns.isEmpty()) return;
+        for (int i = 1; i < rs.getMetaData().getColumnCount() + 1; i++) {
+            columns.add(rs.getMetaData().getColumnName(i));
+        }
+    }
+
+    //check if the specified objects are the same entity (according to id fields)
+    private boolean objectIdEquals(Object object, Object id) {
+        //return obj1.equals(obj2); //objects need to implement equals()
+        //TODO: more efficient (invoke getId method)
+        BeanWrapper wr = new BeanWrapperImpl(object);
+        return id.equals(wr.getPropertyValue("id"));
+    }
+
+    //--------------------------------------------------------------------------
+    //SETTERS
+    /**
+     * @param dataSource used for accessing database
+     */
     public void setDataSource(DataSource dataSource) {
         if (dataSource == null) throw new RuntimeException("fmgVen - DataSource cannot be null");
@@ -39 +184 @@
     }
 
-    public void addDomainPackage(String domainPackage){
-
+    /**
+     * Add the domain packages that will be considered persistent
+     * @param domainPackage the domain package
+     */
+    public void addDomainPackage(String domainPackage) {
+        domainPackages.add(domainPackage);
     }
 }

trunk/fmgVen/src/com/fmguler/ven/Ven.java

@@ -18 +18 @@
 package com.fmguler.ven;
 
+import com.fmguler.ven.util.Convert;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
+import java.util.Set;
 import javax.sql.DataSource;
 import org.springframework.beans.BeanWrapper;
@@ -36 +38 @@
     private QueryGenerator generator;
     private QueryMapper mapper;
+    private boolean debug = true;
 
     public Ven() {
@@ -50 +53 @@
     }
 
-    public Object get(int id, Class objectClass) {
-        return null;
+    public Object get(int id, Class objectClass, Set joins) {
+        String query = generator.generateSelectQuery(objectClass, joins);
+        query += " where 1=1 and " + Convert.toDB(Convert.toSimpleName(objectClass.getName())) + ".id = :___id ";
+
+        Map paramMap = new HashMap();
+        paramMap.put("___id", new Integer(id));
+        if (debug) System.out.println("Ven - SQL: " + query);
+
+        List result = mapper.list(query, paramMap, objectClass);
+        if (result.isEmpty()) return null;
+        if (result.size() > 1) System.out.println("Ven - WARNING >> get(id) returns more than one row");
+        return result.get(0);
     }
 
@@ -114 +127 @@
         if (dataSource == null) throw new RuntimeException("fmgVen - DataSource cannot be null");
         this.template = new NamedParameterJdbcTemplate(dataSource);
+        mapper.setDataSource(dataSource);
     }
 

trunk/fmgVen/test/com/fmguler/ven/sample/Sample.java

@@ -18 +18 @@
 package com.fmguler.ven.sample;
 
+import com.fmguler.ven.LiquibaseUtil;
 import com.fmguler.ven.Ven;
+import com.fmguler.ven.sample.domain.AnotherDomainObject;
 import com.fmguler.ven.sample.domain.SomeDomainObject;
-import java.sql.SQLException;
 import java.util.Date;
+import java.util.HashSet;
 import java.util.List;
-import java.util.Locale;
-import javax.sql.DataSource;
-import liquibase.FileSystemFileOpener;
-import liquibase.exception.JDBCException;
-import liquibase.exception.LiquibaseException;
-import org.springframework.jdbc.datasource.DriverManagerDataSource;
-import liquibase.Liquibase;
-import liquibase.database.Database;
-import liquibase.database.DatabaseFactory;
+import java.util.Set;
 
 /**
@@ -40 +34 @@
     public static void main(String[] args) {
         //build the sample database
-        buildDatabase();
+        LiquibaseUtil.buildDatabase();
 
         //save an object
         testSave();
+        //get an object
+        testGet();
         //delete an object
         testDelete();
 
         //rollback the sample database to original state
-        rollbackDatabase();
+        LiquibaseUtil.rollbackDatabase("tag-init");
     }
 
@@ -76 +72 @@
     public static void testDelete() {
         Ven ven = getVen();
-        ven.delete(1, SomeDomainObject.class);
+        ven.delete(2, SomeDomainObject.class);
     }
 
@@ -84 +80 @@
     public static void testGet() {
         Ven ven = getVen();
-        SomeDomainObject obj = (SomeDomainObject)ven.get(1, SomeDomainObject.class);
+
+        //get with includes
+        Set joins = new HashSet();
+        joins.add("SomeDomainObject.anotherDomainObjects");
+        joins.add("SomeDomainObject.anotherDomainObject");
+        SomeDomainObject obj = (SomeDomainObject)ven.get(1, SomeDomainObject.class, joins);
         System.out.println(obj);
+
+        Set joins2 = new HashSet();
+        joins2.add("AnotherDomainObject.someDomainObject");
+        AnotherDomainObject obj2 = (AnotherDomainObject)ven.get(1, AnotherDomainObject.class, joins2);
+        System.out.println(obj2);
     }
 
@@ -109 +115 @@
     private static Ven getVen() {
         Ven ven = new Ven();
-        ven.setDataSource(getDataSource());
+        ven.setDataSource(LiquibaseUtil.getDataSource());
         ven.addDomainPackage("com.fmguler.ven.sample.domain").addDomainPackage("another.package");
         return ven;
     }
-
-    private static DataSource getDataSource() {
-        DriverManagerDataSource ds = new DriverManagerDataSource();
-        ds.setDriverClassName("org.postgresql.Driver");
-        ds.setUsername("postgres");
-        ds.setPassword("qwerty");
-        ds.setUrl("jdbc:postgresql://127.0.0.1:5432/ven-test");
-        return ds;
-    }
-
-    private static void buildDatabase() {
-        try {
-            Locale currLocale = Locale.getDefault();
-            Locale.setDefault(Locale.ENGLISH);
-            Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(getDataSource().getConnection());
-            Liquibase liquibase = new Liquibase("etc/test-db/test-db-changelog.xml", new FileSystemFileOpener(), database);
-            liquibase.update("");
-            Locale.setDefault(currLocale);
-        } catch (SQLException ex) {
-            ex.printStackTrace();
-        } catch (JDBCException ex) {
-            ex.printStackTrace();
-        } catch (LiquibaseException ex) {
-            ex.printStackTrace();
-        }
-    }
-
-    private static void rollbackDatabase() {
-        try {
-            Locale currLocale = Locale.getDefault();
-            Locale.setDefault(Locale.ENGLISH);
-            Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(getDataSource().getConnection());
-            Liquibase liquibase = new Liquibase("etc/test-db/test-db-changelog.xml", new FileSystemFileOpener(), database);
-            liquibase.rollback("tag-single-table", "");
-            Locale.setDefault(currLocale);
-        } catch (SQLException ex) {
-            ex.printStackTrace();
-        } catch (JDBCException ex) {
-            ex.printStackTrace();
-        } catch (LiquibaseException ex) {
-            ex.printStackTrace();
-        }
-    }
 }

trunk/fmgVen/test/com/fmguler/ven/sample/domain/SomeDomainObject.java

@@ -18 +18 @@
 package com.fmguler.ven.sample.domain;
 
+import com.fmguler.ven.util.VenList;
 import java.util.Date;
+import java.util.List;
 
 /**
@@ -29 +31 @@
     private String description;
     private Date date;
+    private List anotherDomainObjects = new VenList(AnotherDomainObject.class, "someDomainObject");
+    private AnotherDomainObject anotherDomainObject = new AnotherDomainObject();
 
     /**
@@ -86 +90 @@
     }
 
+    /**
+     * @return the list of AnotherDomainObject
+     */
+    public List getAnotherDomainObjects() {
+        return anotherDomainObjects;
+    }
+
+    /**
+     * @return the anotherDomainObject
+     */
+    public AnotherDomainObject getAnotherDomainObject() {
+        return anotherDomainObject;
+    }
+
+    /**
+     * @param anotherDomainObject the anotherDomainObject to set
+     */
+    public void setAnotherDomainObject(AnotherDomainObject anotherDomainObject) {
+        this.anotherDomainObject = anotherDomainObject;
+    }
+
     public String toString() {
-        return id + " " + name + " " + description + " " + date;
+        return id + " " + name + " " + description + " another domain object: {" + anotherDomainObject + "} another domain objects:\n" + anotherDomainObjects;
     }
 }