DBObjectManager.java

Go to the documentation of this file.

package cedar.jetweb.db;

import java.sql.*;
import java.lang.reflect.*;
import java.util.Vector;
import java.util.HashMap;
import java.util.Arrays;
import java.util.List;
import java.io.ByteArrayInputStream;
import java.util.Collections;

import cedar.jetweb.*;

public class DBObjectManager{

    //map giving the tables in each database
    private HashMap<String, Vector<String> > tableNames_ = 
    new HashMap<String, Vector<String> > ();

    private static HashMap<String, HashMap<String, Vector<Method> > >
    availableMethodsMap_ = 
    new HashMap<String, HashMap<String, Vector<Method> > >();

    private static HashMap<Class, String> allowedTypes_
    = new HashMap<Class, String>();
    private static HashMap<Class, Method> listTypes_
    =new HashMap<Class, Method>();
    private static boolean allowedTypesInit_ = true;

    private static HashMap<String, String> primitiveTypes_
    = new HashMap<String, String>();
    private static boolean primitiveTypesInit_ = true;

    private DBConnectionManager db_=null;

    public DBObjectManager passDBConnection(DBConnectionManager theDataBase){

    db_ = theDataBase;

    return(this);
    }

    public DBConnectionManager getDBConnection(){
    return(db_);
    }

    public synchronized int fillObject(Object obj, ResultSet dbrs)
    throws SQLException{

    int numberFilled = 0;
    
    //Examine the database metadata and put the available
        //columns in a vector
    
        ResultSetMetaData metaData = dbrs.getMetaData();
    HashMap<String, Vector<String> > availableColumns = 
        new HashMap<String, Vector<String> >();
        int numberOfColumns = metaData.getColumnCount();
        ++numberOfColumns;
    
        for(int ii=1; ii!=numberOfColumns; ++ii){

        String colName = metaData.getColumnName(ii);

        Vector<String> colInf = new Vector<String>();
        colInf.add(colName);
        colInf.add(metaData.getColumnClassName(ii));

        availableColumns.put
        (strip_(colName), colInf);
    }
    
        --numberOfColumns;

    Vector<Method> methods = availableMethods(obj, "set");

    initPrimitiveTypes();

    for(int ii=0; ii!= methods.size(); ++ii){

        Method method = methods.get(ii);

        String columnName = method.getName().substring(3);

        char toDeCapitalise = columnName.charAt(0);
        columnName = Character.toLowerCase(toDeCapitalise) +
        columnName.substring(1);

        Vector<String> colInf = availableColumns.get
        (columnName);

        if(colInf!=null){

        String columnType = colInf.get(1);
        
        Class[] parameterTypes = method.getParameterTypes();
        
        if(parameterTypes.length == 1 &&
           columnType!=null){
            
            String parameterType = parameterTypes[0].getName();
            
            if(parameterType == columnType ||
               primitiveTypes_.get(parameterType)==columnType){
            
            Object[] param = {dbrs.getObject(colInf.get(0))};
            try{

                method.invoke(obj, param);
                ++numberFilled;
            }catch (IllegalAccessException e){
                System.out.println(e.getMessage());
            }catch (InvocationTargetException e){
            System.out.println(e.getMessage());
            }
            }else if(columnType.equals("[B")){// can always attempt 
                                           // to convert blob into
                                           // anything for filling.
            
            Blob blob = dbrs.getBlob(colInf.get(0));

            Object param = 
                DBBlob2Array.convert
                 (blob, parameterTypes[0]);

            if(param!=null){
                try{
                Object paramArray[] = {param};
                method.invoke(obj, paramArray);
                ++numberFilled;
                }catch (IllegalAccessException e){
                System.out.println(e.getMessage());
                }catch (InvocationTargetException e){
                System.out.println(e.getMessage());
                }
            }
            }
        }
        }
    }

    return(numberFilled);
    };
    

    public synchronized void writeObject(Object obj, 
                     String tableName,
                     String dbName) 
    throws JetWebException {

    Vector<String> foundTables=null;
    boolean overWriteTableNames = false;
    
    String connectedDBName = null;
    DatabaseMetaData metaData = null;

    try{
        
        connectedDBName = db_.getConnection(dbName).getCatalog();
        
        metaData = db_.getConnection(dbName).getMetaData();
        
        //ResultSet existingTables = metaData.getCatalogs();
       
        if(!tableNames_.containsKey(dbName)){
        
        overWriteTableNames = true;
        
        ResultSet existingTables = 
            metaData.getTables
            (db_.getConnection(dbName).getCatalog(), null, "", null);
        
        foundTables = new Vector<String>();
        while(existingTables.next()){
                    
            foundTables.add
            (existingTables.getString("TABLE_NAME"));
        }
        
        }else{
        foundTables = tableNames_.get(dbName);
        }
        
    }catch(SQLException e){
        System.out.println("Unable to get metadata for catalogue "+
                   tableName +
                   " in database "+dbName);
        System.out.println(e.getMessage());
        throw new JetWebException(e);
    }
    
    //does the table we want exist.
    
    boolean tableExists = foundTables.contains(tableName);

    if(!tableExists){
        
        tableExists = createTable(obj, tableName, dbName);
        foundTables.add(tableName);
        overWriteTableNames = true;
    }

    if(overWriteTableNames){
        tableNames_.put(dbName, foundTables);
    }

    Vector<Method> methods = availableMethods(obj, "get");
    int numColumns = 0;

    HashMap<String, Object>  columnsToWrite = 
        new HashMap<String, Object>();

    HashMap<String, Vector<String> > columnMap = 
        new HashMap<String, Vector<String> >();

    try{
        ResultSet existingColumns = 
        metaData.getColumns
        (db_.getConnection(dbName).getCatalog(), null,tableName, "");
            
        while(existingColumns.next()){
        
        Vector<String> colInf = new Vector<String>();
        colInf.add(existingColumns.getString("TYPE_NAME"));
        colInf.add(existingColumns.getString("COLUMN_SIZE"));
        colInf.add(existingColumns.getString("COLUMN_NAME"));
        
        columnMap.put
            (strip_(existingColumns.getString("COLUMN_NAME")), colInf);

        }

    }catch(SQLException e){
        System.out.println(e.getMessage());
    }
    
    Statement stmt = null;

    try{
        stmt = db_.getConnection(dbName).createStatement();
    }catch (SQLException e){
        System.out.println(e.getMessage());
        throw new JetWebException(e);
    }

    Vector<Vector<String> > stuffToWrite = new Vector<Vector<String> >();
    Vector<ByteArrayInputStream> blobsToWrite = 
        new Vector<ByteArrayInputStream>();
    int numBlobs = 0;

    int numRows = 0;

    for( Method method: methods){

        String colName = method.getName().substring(3);

        Vector<String> colInf = columnMap.get(deCapitalise(colName));

        if(colInf !=null){// Means a column with the same name as 
                      // the method exists.
        String methodType = null;
        String dbType = colInf.get(0);
        //colInf.add(colName);
        
        try{
            methodType = sqlType(obj, method);

        }catch(Exception e){
            System.out.println(e.getMessage());
            throw new JetWebException(e);
        }

        if(methodType.substring
           (0, dbType.length()).equalsIgnoreCase(dbType)||
           dbType.equalsIgnoreCase("blob")||//can always write a blob!
           dbType.equalsIgnoreCase("enum")||
           dbType.equalsIgnoreCase("set")){//can always attempt
                                           //to write an enum 
            
            //then we write it to the database.

            Object result = null;
            try{
            result = method.invoke(obj);
            }catch (IllegalAccessException e){
            System.out.println(e.getMessage());
            }catch (InvocationTargetException e){
            System.out.println(e.getMessage());
            }

            boolean isAList = false;
            boolean isABlob = dbType.equalsIgnoreCase("blob");
            boolean notNull = false;

            if(result!=null){
            notNull = true;
            Class[] interfaces = result.getClass().getInterfaces();
            
            //boolean carryOn = true;
            for(int kk=0; 
                kk!= interfaces.length && 
                !isAList; ++kk){
                if(interfaces[kk].equals(List.class)){
                //carryOn = false;
                isAList = true;
                }
            }
            }
            int maxEntryLength = 0;

            if(isAList && !isABlob){

            try{
                Class[] getParamType = {Integer.class};
                Method size = result.getClass().getMethod("size");

                Method[] resultMethods = 
                result.getClass().getMethods();

                Method get =null;
                for(int kk=0; kk!=resultMethods.length; ++kk){
                if(resultMethods[kk].getName().equals
                     ("get")){
                    get = resultMethods[kk];
                }
                }
 
                Integer resultSize = (Integer) size.invoke(result);
                for(Integer kk = 0; !resultSize.equals(kk); ++kk){
                Object[] kkArray = {kk};
                Object returnedObj = 
                    get.invoke(result, kkArray);
                if(returnedObj!=null){
                    String entry = "'"+
                    get.invoke(result, kkArray).toString()
                    +"'";
                    if(entry.length()>maxEntryLength){
                    maxEntryLength = entry.length();
                    }
                    
                    colInf.add(entry);
                }else{
                    colInf.add(null);
                }
                }
            }catch(Throwable e){
                System.out.println(e.getClass().getName()+
                           ": "+
                           e.getMessage());
                System.out.println("Caused by "+
                           e.getCause());
            }
            
            }else if(notNull&&isABlob){
            String entry="?";
                ++numBlobs;

                ByteArrayInputStream blobInput = null;

                blobInput = new ByteArrayInputStream
                (DBBlob2Array.byteMe(result));
    
                blobsToWrite.add(blobInput);
                colInf.add(entry);
            }else if(notNull&&!isAList){

            String entry = "'"+result.toString()+"'";
            
            colInf.add(entry);
            maxEntryLength = entry.length();
            }

            if(colInf.size()>numRows) numRows = colInf.size();

            stuffToWrite.add(colInf);

            Integer colLength = new Integer(colInf.get(1));

            if(dbType.equals("varchar")&&
               colLength<maxEntryLength&&
               tableExists){
            //then we need to increase the length of the column

            String colIncr = "ALTER TABLE " +
                tableName +
                " MODIFY "+
                unCamelMe(colName) +
                " VARCHAR("+
                maxEntryLength+
                ") DEFAULT ''";

            try{
                stmt.execute(colIncr);
            }catch (SQLException e){
                System.out.println(e.getMessage());
            }
            }
        }
        }
    }

    int numCols = stuffToWrite.size()-1;
    Vector<String> cmd1 = new Vector<String>();
    Vector<String> cmd2 = new Vector<String>();

    int numEntries = numRows - 3;

    for(int ii=0; ii!=numEntries; ++ii){
        cmd1.add("INSERT INTO " + tableName +" (");
        cmd2.add("VALUES(");
    }

    for(int jj=0; jj!=numEntries; ++jj){
        boolean thereIsAnEntry = false;
        for(int ii=0; ii!=stuffToWrite.size(); ++ii){
        
        if(stuffToWrite.get(ii).size()>jj+3){
            if(stuffToWrite.get(ii).get(jj+3)!=null){
            if(thereIsAnEntry){
                cmd1.set(jj, cmd1.get(jj)+", ");
                cmd2.set(jj, cmd2.get(jj)+", ");
            }
            
            thereIsAnEntry = true;
            cmd1.set(jj, cmd1.get(jj)+stuffToWrite.get(ii).get(2));

            String val = stuffToWrite.get(ii).get(jj+3);

            if(val.equals("'true'")){
                val="1";
            }else if(val.equals("'false'")){
                val = "0";
            }
            cmd2.set(jj, cmd2.get(jj) + val);
            }
        }
        }
    }

    for(int ii=0; ii!= cmd2.size()&&tableExists; ++ii){
        cmd2.set(ii, cmd1.get(ii) + 
             ") " + 
             cmd2.get(ii) +
             ") ");
        try{
        //System.out.println(cmd2.get(ii));
        
        if(numBlobs==0){
            stmt.execute(cmd2.get(ii));

        }else{

            //System.out.println(cmd2.get(ii));

            PreparedStatement pstmt = 
            db_.getConnection(dbName).
            prepareStatement(cmd2.get(ii));
            if(ii==0){
            for(int jj=0; jj<numBlobs; ++jj){
                pstmt.setBinaryStream
                (jj+1,blobsToWrite.get(jj), 
                 blobsToWrite.get(jj).available());
            }
            }
            pstmt.executeUpdate();
            pstmt.close();
        }
        }catch(SQLException e){
        System.out.println("SQLException: "+e.getMessage());
        }
    }
    
    System.out.println("finished writing object");
    try{
    stmt.close();
    }catch(Throwable err){
        System.out.println(err.getMessage());
    }
    return;
    };
    
    public Object writeToDB(String tableName, String dbName) 
    throws JetWebException {
    writeObject(this, tableName, dbName);
    return(this);
    };
    
    public Object writeToDB(String dbName) throws JetWebException {

    String fullName = this.getClass().getName();

    return(writeToDB
           (fullName.substring(fullName.lastIndexOf(".")+1), dbName));
    };

    //returns a vector of the methods that start with prefix in object.
    //keeps a record of earlier objects to avoid looking things up twice.

    private Vector<Method> 
    availableMethods(Object obj, String prefix){
    
    if(obj==null) return new Vector<Method>(0);

    Vector<Method> methods;
    HashMap<String, Vector<Method> > foundMethods = 
        availableMethodsMap_.get(obj.getClass().toString());
    
    boolean findMethods = false;
    boolean foundObject = false;
    
    if(foundMethods==null){
        findMethods = true;
    }else if(!foundMethods.containsKey(prefix)){
        findMethods = true;
        foundObject = true;
    }else{

        methods = foundMethods.get(prefix);
        return(methods);
    }

    methods = new Vector<Method>();

    if(findMethods){
        Class objClass = obj.getClass();
        Method[] objMethods = objClass.getMethods();
        for(int ii=0; ii!= objMethods.length; ++ii){
        if(objMethods[ii].
           getName().
           substring(0,prefix.length()).
           equals(prefix)){
            methods.add(objMethods[ii]);
        }
        }
        
        if(!foundObject){
        foundMethods = new HashMap<String, Vector<Method> >();
        }

        foundMethods.put(prefix, methods);
    }

    availableMethodsMap_.put(obj.getClass().toString(), foundMethods);
    
    return(methods);
    };
    //creates a table in the database named dbName.
    
    private boolean createTable(Object obj, String tableName, String dbName) 
    throws JetWebException {
    
    String cmd1 = "DROP TABLE If EXISTS `" + tableName + "`";

    String columns = "(";
    Vector<Method> methods = availableMethods(obj, "get");

    int numberOfCommas = methods.size() - 1;

    boolean addComma = false;

    int numColumns = 0;

    for(int ii=0; ii!= methods.size(); ++ii){

        String sqlCommand = null;

        try{

        sqlCommand = sqlType(obj, methods.get(ii));

        //System.out.println("sqlCommand = "+sqlCommand);

        }catch(Exception e){
        System.out.println
            ("Unable to determine return type of method "+
             methods.get(ii).getName()+
             " in object "+obj.getClass().getName());
        System.out.println("IllegalAccessException: "+e.getMessage());
        throw new JetWebException(e);
        }
        
        if(sqlCommand!="null"){
        if(addComma){
            columns = columns + ", ";
        }
        
        ++numColumns;
        columns = columns + 
            unCamelMe(methods.get(ii).getName().substring(3)) +
            " " + 
            sqlCommand;

        if(ii!=numberOfCommas){
            addComma = true;
        }
        }
    }

    columns = columns + ")";
    

    if(numColumns >0){
        String cmd2= 
        "CREATE TABLE `" + 
        tableName +
        "` " +
        columns + 
        " ENGINE=MyISAM DEFAULT CHARSET=latin1";
        
        try{
        Statement stmt = 
            db_.getConnectionAsAdmin(dbName).createStatement();
        stmt.execute(cmd1);
        stmt.execute(cmd2);
        stmt.close();
        }catch(SQLException e){
        System.out.println("Unable to create table " + 
                   tableName + 
                   " in database "+dbName);
        System.out.println("SQL command was: "+cmd2);
        System.out.println("SQLException: "+e.getMessage());
        throw new JetWebException(e);
        }
    }
    return(numColumns>0);
    };

    //gives the sql type we should use.  Descends into an array/vector etc
    //to find the primitive type it contains.

    private String sqlType(Object obj, Method method)
    throws IllegalAccessException, 
       InvocationTargetException,
       NoSuchMethodException {

    if(allowedTypesInit_) initAllowedTypes();

    //System.out.println("method name is "+method.getName());

    Object returnedObject = method.invoke(obj);
    //System.out.println("returnedObject = "+returnedObject);
    Class returnedClass = returnedObject.getClass();

    //Special case for arrays because they are neither proper
    //classes nor primitive types, so we turn it into a list...

    if(returnedClass.getName().substring(0,2).equals("[B")){
        return(allowedTypes_.get(Byte.class));
    }

    if(returnedClass.getName().substring(0,1).equals("[")){
        
        returnedObject = Arrays.asList(returnedObject);
        returnedClass = returnedObject.getClass();
    }

    if(allowedTypesInit_) initAllowedTypes();
       
    //System.out.println("returnedClass = "+returnedClass);


    String typeName = allowedTypes_.get(returnedClass);
    Method get = listTypes_.get(returnedClass);



    boolean carryOn = true;
    int iii = 0;
    int counterMax = 10000;

    while(carryOn){

        //System.out.println("typeName = "+typeName);
        //System.out.println("get = "+get);

        iii++;
        if(typeName!=null){
        carryOn = false;

        }else if(get!=null&&typeName==null) {
        Method size = returnedClass.getMethod("size");

        //System.out.println("size = "+size);

        int ii = 0;
        boolean notFound = true;
        
        //System.out.println(returnedObject);

        Integer listSize = (Integer)size.invoke(returnedObject);

        //System.out.println("the listSize is "+listSize);
        while(notFound && ii != listSize){
            Object tmpObject =  get.invoke(returnedObject,ii);
            if(tmpObject!=null){
            notFound = false;
            returnedObject = tmpObject;

            returnedClass = returnedObject.getClass();

            typeName = allowedTypes_.get(returnedClass);
            get = listTypes_.get(returnedClass);
            }else{
            ++ii;
            }
        }

        if(ii==listSize){
            System.out.println("List is full of nulls");
            System.out.println
            ("- could not determine columns type. Using int");

            System.out.println("method name is "+method.getName());

            typeName = "integer default NULL";
            carryOn = false;
        }

        }else{

        typeName = "null";
        carryOn = false;
        }

        // protect against infinite loops.
        if (iii > counterMax) {
        System.out.println("Could not determine column type. Using int.");
        System.out.println("method name is "+method.getName());

        typeName = "integer default NULL";
        carryOn = false;
        }

    }
    
    return(typeName);
    };
    private static void initAllowedTypes(){

    if(!allowedTypesInit_){
        return;
    }

    allowedTypesInit_ = false;

    Class[] c = {Integer.class, Double.class, Float.class, int.class};

    for(int ii=0; ii!= c.length; ++ii){
        String typeName = c[ii].getName();
        typeName = typeName.substring
        (typeName.lastIndexOf(".")+1).toLowerCase();
        allowedTypes_.put(c[ii], typeName+" default NULL");
    }
    
    allowedTypes_.put(String.class, "varchar(16) default ''");
    allowedTypes_.put(Short.class, "smallint default NULL");
    allowedTypes_.put(Byte.class, "blob default null");
    allowedTypes_.put(Boolean.class, "tinyint default 0");

    Class[] arg = {int.class};

    try{

    listTypes_.put(Vector.class, Vector.class.getMethod("get", arg));
    listTypes_.put(List.class, List.class.getMethod("get",arg));
    listTypes_.put
        (Arrays.asList(new int[0]).getClass(), 
         Arrays.asList(new int[0]).getClass().getMethod("get", arg));

    listTypes_.put
        (Collections.synchronizedList
         (Arrays.asList(new int[0])).getClass(),
         Collections.synchronizedList
         (Arrays.asList(new int[0])).getClass().getMethod("get", arg));
    
    }catch(NoSuchMethodException e){
        
        System.out.println("NoSuchMethodException: " + 
                   e.getMessage());
        
    }

    return;
    };
    private static void initPrimitiveTypes(){
    if (!primitiveTypesInit_) return;
    
    primitiveTypes_.put(int.class.getName(), Integer.class.getName());
    primitiveTypes_.put(double.class.getName(), Double.class.getName());
    primitiveTypes_.put(float.class.getName(), Float.class.getName());
    primitiveTypes_.put(char.class.getName(), String.class.getName());
    primitiveTypes_.put(boolean.class.getName(), Boolean.class.getName());

    return;
    };
    //Strip the "_" from a string and capitalises.
    private static String strip_(String word){
    int ii=word.indexOf("_");
    while(ii>-1){
        char toCapitalise = 0;
        if(ii!= word.length()-2){
        toCapitalise = word.charAt(ii+1);
        }
        word = word.substring(0,ii) + 
        Character.toUpperCase(toCapitalise) + 
        word.substring(ii+2);
        ii = word.indexOf("_");
    }
    return(word);
    }
    // Make the first character of the string lower case
    private static String deCapitalise(String word){
    return(word.substring(0,1).toLowerCase()+word.substring(1));
    }

    private static String unCamelMe(String word){
    
    for(int ii=0; ii< word.length()-1; ++ii){
        char letter = word.charAt(ii);
        if(Character.isUpperCase(letter)){
        String decapitalised = new String();
        decapitalised += Character.toLowerCase(letter);
        if(ii!=0) decapitalised = "_"+decapitalised;
        word = word.substring(0,ii) + 
            decapitalised+ 
            word.substring(ii+1);

        }
    }
    return(word);
    }
}

Generated Wed Jan 17 09:14:27 GMT 2007