com.pureinfo.studio.db.xls2rpms.XlsImporter.java Source code

Java tutorial

Introduction

Here is the source code for com.pureinfo.studio.db.xls2rpms.XlsImporter.java

Source

/**
 * PureInfo Quake
 * @(#)XlsImporter.java   1.0 2005-9-20
 * 
 * Copyright(c) 2004-2005, PureInfo Information Technology Corp. Ltd. 
 * All rights reserved, see the license file.
 * 
 * www.pureinfo.com.cn
 */

package com.pureinfo.studio.db.xls2rpms;

import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import org.apache.log4j.Logger;
import org.dom4j.Element;

import com.pureinfo.dolphin.DolphinHelper;
import com.pureinfo.dolphin.mapping.EntityMetadata;
import com.pureinfo.dolphin.mapping.PropertyMetadata;
import com.pureinfo.dolphin.mapping.PropertyType;
import com.pureinfo.dolphin.model.DolphinObject;
import com.pureinfo.dolphin.model.IObjects;
import com.pureinfo.dolphin.persister.ISession;
import com.pureinfo.dolphin.persister.IStatement;
import com.pureinfo.force.exception.PureException;
import com.pureinfo.force.lang.NumberUtil;
import com.pureinfo.force.lang.StrConvertor;
import com.pureinfo.force.lang.StrUtil;
import com.pureinfo.force.object.DataTypes;
import com.pureinfo.force.xml.XMLUtil;
import com.pureinfo.studio.db.xls2srm.impl.XlsObjectsImpl;

/**
 * <P>
 * Created on 2005-9-20 13:17:26 <BR>
 * Last modified on 2005-9-20
 * </P>
 * XlsImporter: export SRMUser from old RWSK database.
 * 
 * @author Why
 * @version 1.0, 2005-9-20
 * @since Quake 1.0
 */
public class XlsImporter {
    //logger
    private final static Logger logger = Logger.getLogger(XlsImporter.class.getName());

    //connection provider
    private String m_sProvider;

    //configuration
    private Element m_xmlConfig;

    //runtime cache
    private ISession m_sessionFrom;

    private ISession m_sessionTo;

    private EntityMetadata m_entityMetadata;

    /**
     * Constructor
     */
    public XlsImporter() {
        super();
    }

    /**
     * Constructor
     * 
     * @param _sProvider
     *            connection provider
     */
    public XlsImporter(String _sProvider) {
        this.setProvider(_sProvider);
    }

    /**
     * Returns the provider.
     * 
     * @return the provider.
     */
    public String getProvider() {
        return m_sProvider;
    }

    /**
     * Sets the provider.
     * 
     * @param _sProvider
     *            the provider to set.
     */
    public void setProvider(String _sProvider) {
        m_sProvider = _sProvider;
    }

    public void config(Element _xmlConfig) throws PureException {
        m_xmlConfig = _xmlConfig;
        String sClass = m_xmlConfig.attributeValue("class");
        m_entityMetadata = DolphinHelper.lookupEntityMetadataByName(sClass, true);
    }

    public void clear() {
        if (m_sessionFrom != null) {
            m_sessionFrom.closeQuietly(System.err);
        }
        if (m_sessionTo != null) {
            m_sessionTo.closeQuietly(System.err);
        }
    }

    /**
     * 
     * @throws Exception
     */
    public void run(String _sFileName) throws PureException {
        if (m_xmlConfig == null) {
            throw new PureException(PureException.SETTING_MISSING, "please call config() first");
        }

        long lStart = System.currentTimeMillis();

        IObjects objs = null;
        DolphinObject oldObj, newObj;
        Class clazz = m_entityMetadata.getEntityClass();
        try {
            prepare();

            objs = this.loadOld(_sFileName);
            String sViewProperty = m_xmlConfig.attributeValue("view-property");
            if (sViewProperty == null || (sViewProperty = sViewProperty.trim()).length() == 0) {
                sViewProperty = "ID";
            } else {
                sViewProperty = sViewProperty.toUpperCase();
            }

            int nCount = 1;
            while ((oldObj = objs.next()) != null) {
                System.out.println("[" + nCount + "]to process " + clazz.getName() + "-"
                        + oldObj.getPropertyAsString(sViewProperty) + "...  ");
                newObj = makeNewObject(clazz, oldObj);
                copy(oldObj, newObj);
                convert(oldObj, newObj);
                fill(newObj);
                save(newObj);
                System.out.println("OK!");
                nCount++;
            } //endwhile
            lastUpdate();

            long lEnd = System.currentTimeMillis();
            System.out.println(clazz.getName() + ": all finished - time used: " + (lEnd - lStart) + "ms");
        } catch (Exception ex) {
            throw new PureException(PureException.DATABASE_ACCESS, "class=" + clazz.getName(), ex);
        } finally {
            if (objs != null)
                objs.clear();
        }
    }

    //=========================================================================
    //inside logic

    private DolphinObject makeNewObject(Class _clazz, DolphinObject _oldObj) throws Exception {
        DolphinObject newObj = null;
        Element ele = m_xmlConfig.element("match");
        if (ele != null && ele.hasContent()) {
            String strSQL = ele.elementTextTrim("sql");
            StringTokenizer st = new StringTokenizer(strSQL, "$", false);
            StringBuffer sbuff = new StringBuffer("select * from {this} where ");
            try {
                boolean bPropertyValue = false;
                String sValue;
                while (st.hasMoreElements()) {
                    sValue = (String) st.nextElement();
                    if (bPropertyValue) {
                        sValue = _oldObj.getPropertyAsString(sValue.toUpperCase());
                    }
                    sbuff.append(sValue);
                    bPropertyValue = !bPropertyValue;
                }
                strSQL = sbuff.toString();
            } finally {
                sbuff.setLength(0);
            }

            logger.debug("to find existing object: " + strSQL);
            ISession session = getSession();
            IStatement query = session.createQuery(strSQL, _clazz, 1);
            IObjects objs = null;
            try {
                objs = query.executeQuery();
                newObj = objs.next();
            } finally {
                DolphinHelper.clear(objs, query);
            }
            if (logger.isDebugEnabled()) {
                logger.debug("existing object: " + newObj);
            }
        }

        if (newObj == null) {
            newObj = (DolphinObject) _clazz.newInstance();
        }
        return newObj;
    }

    private ISession getSession() throws Exception {
        if (m_sessionFrom == null) {
            m_sessionFrom = DolphinHelper.createSession(m_sProvider);
        }
        return m_sessionFrom;
    }

    private void prepare() throws Exception {
        this.executeSQLElement("prepare");
    }

    /**
     * Loads the old objects collection.
     * 
     * @return the old objects collection
     * @throws Exception
     */
    private IObjects loadOld(String _sFileName) throws Exception {
        XlsObjectsImpl objs = new XlsObjectsImpl(_sFileName);
        return objs;
    }

    /**
     * Copys the properties form the old object.
     * 
     * @param _oldObj
     * @param _newObj
     * @throws Exception
     */
    private void copy(DolphinObject _oldObj, DolphinObject _newObj) throws Exception {
        List properties = m_xmlConfig.element("copy").elements();
        Element element;
        String sFrom, sTo, sType;
        Object oValue;
        for (int i = 0; i < properties.size(); i++) {
            element = (Element) properties.get(i);
            sTo = element.attributeValue("to");
            if (!isPropertySet(_newObj, sTo)) {
                //skip the property which is not updateable when inserting
                if (_newObj.isValidInstance()) {
                    PropertyMetadata property = m_entityMetadata.lookupPropertyByName(sTo, true);
                    if (!property.isUpdateable()) {
                        continue;
                    }
                }

                sFrom = element.attributeValue("from").toUpperCase();
                oValue = _oldObj.getProperty(sFrom);
                if (oValue != null) {
                    sType = element.attributeValue("type");
                    int nType = PropertyType.lookupByName(sType).getDataType();
                    switch (nType) {
                    case DataTypes.INT:
                        if (!(oValue instanceof Number)) {
                            oValue = new Double(oValue.toString());
                        }
                        oValue = new Integer(((Number) oValue).intValue());
                        break;
                    case DataTypes.STRING:
                        if (!(oValue instanceof String)) {
                            oValue = oValue.toString();
                        }
                        break;
                    case DataTypes.DATE:
                    case DataTypes.DATETIME:
                    case DataTypes.TIME:
                        if (!(oValue instanceof Date)) {
                            oValue = DataTypes.parse(oValue.toString(), nType);
                        }
                        break;
                    default:
                        oValue = DataTypes.parse(oValue.toString(), nType);
                        break;
                    }
                    _newObj.setProperty(sTo, oValue);
                }
            }
        }
    }

    /**
     * Returns <code>true</code> if the specified property has been set.
     * 
     * @param _obj
     *            a dolphin object
     * @param _sProperty
     *            property name
     * @return <code>true</code> if the specified property has been set;
     *         <code>false</code>, otherwise.
     */
    public static boolean isPropertySet(DolphinObject _obj, String _sProperty) {
        if (_obj.isValidInstance()) {
            return _obj.getNewProperty(_sProperty) != null;
        }
        //else
        return _obj.getProperty(_sProperty) != null;
    }

    /**
     * Converts the properties whose type are different from SRM.
     * 
     * @param _oldObj
     * @param _newObj
     * @throws Exception
     */
    private void convert(DolphinObject _oldObj, DolphinObject _newObj) throws Exception {
        List properties = m_xmlConfig.element("convert").elements();
        Element element;
        String sFrom, sTo, sRef, sForeignKey;
        Object value = null;
        for (int i = 0; i < properties.size(); i++) {
            element = (Element) properties.get(i);
            sFrom = element.attributeValue("from").toUpperCase();
            sTo = element.attributeValue("to");
            sRef = element.attributeValue("ref");

            //to convert property value
            if (!_oldObj.hasProperty(sFrom)) {
                continue;
                //throw new PureException(PureException.PROPERTY_NOTFOUND,
                // sFrom);
            }
            if (isPropertySet(_newObj, sTo))
                continue; //skip

            //else
            try {
                if (sRef.equals("#int2str")) {
                    value = _oldObj.getProperty(sFrom);
                    if (value != null) {
                        value = NumberUtil.format(((Number) value), "#");
                    }
                } else if (sRef.equals("#bool2IntStr")) {
                    value = _oldObj.getBoolProperty(sFrom, false) ? "1" : "0";
                } else if (sRef.equals("#str2int")) {
                    value = _oldObj.getStrProperty(sFrom);
                    if (value != null) {
                        value = Integer.valueOf((String) value);
                    }
                } else if (sRef.equals("#float2double")) {
                    value = _oldObj.getProperty(sFrom);
                    if (value != null) {
                        value = Double.valueOf(value.toString());
                    }
                } else {
                    sForeignKey = element.attributeValue("fk");
                    value = this.lookupRefValue(sRef, _oldObj.getProperty(sFrom), sForeignKey);
                }

                if (value != null) {
                    _newObj.setProperty(sTo, value);
                }
            } catch (Exception ex) {
                throw new PureException(PureException.INVALID_VALUE,
                        "convert " + sFrom + "[" + value + "] to " + sTo, ex);
            }
        }
    }

    private Object lookupRefValue(String _sRef, Object _oKey, String _sForeignKey) throws Exception {
        if (_oKey == null)
            return null;

        //to analyze
        int nPos = _sRef.indexOf('.');
        String sRefTable = _sRef.substring(0, nPos);
        String sRefField = _sRef.substring(nPos + 1);

        //to construct SQL
        String fk = (_sForeignKey != null && (_sForeignKey = _sForeignKey.trim()).length() > 0) ? _sForeignKey
                : "ID";
        String strSQL = "SELECT " + sRefField + " AS VALUE FROM " + sRefTable + " WHERE " + fk + "=";
        if (_oKey instanceof String) {
            strSQL += "'" + (String) _oKey + "'";
        } else {
            strSQL += _oKey.toString();
        }

        //to execute query
        ISession session = this.getSession();
        IStatement query = session.createQuery(strSQL, DolphinObject.class, 1);
        IObjects results = query.executeQuery();

        //to fetch the result
        DolphinObject result = results.next();
        results.clear();
        query.clear();

        //to return
        return result == null ? null : result.getProperty("VALUE");
    }

    /**
     * Fills the properties which are required not null in SRM.
     * 
     * @param _newObj
     * @throws Exception
     */
    private void fill(DolphinObject _newObj) throws Exception {
        List properties = m_xmlConfig.element("fill").elements();
        Element element;
        String sName, sValue;
        for (int i = 0; i < properties.size(); i++) {
            element = (Element) properties.get(i);
            sName = element.attributeValue("name");
            if (sName == null) {
                throw new PureException(PureException.SETTING_MISSING,
                        "property name in fill-" + i + ": " + XMLUtil.toString(element));
            }

            //skip the property which is not updateable
            if (_newObj.isValidInstance()) {
                PropertyMetadata property = m_entityMetadata.lookupPropertyByName(sName, true);
                if (!property.isUpdateable()) {
                    continue; //skip
                }
            }

            sValue = element.attributeValue("value");
            if (sValue == null) {
                throw new PureException(PureException.SETTING_MISSING,
                        "property value in fill-" + i + ": " + XMLUtil.toString(element));
            }

            if (!isPropertySet(_newObj, sName)) {
                if (sValue.length() > 0 && sValue.charAt(0) == '@') {
                    //to fill with a specified property
                    sValue = sValue.substring(1); //trim '@'
                    _newObj.setProperty(sName, _newObj.getProperty(sValue));
                } else {
                    //to fill with a constant
                    //DolphinUtil.setPropertyWithString(_newObj,
                    // m_entityMetadata, sName, sValue);
                    _newObj.setProperty(sName, sValue);
                }
            }
        }
    }

    /**
     * Saves the new object into SRM database.
     * 
     * @param _newObj
     * @throws Exception
     */
    private void save(DolphinObject _newObj) throws Exception {
        ISession session = this.getSession();
        IStatement statement = null;
        StringBuffer sbuff = new StringBuffer();
        StringBuffer sbuffValues = new StringBuffer();
        try {
            String sTable = m_xmlConfig.attributeValue("table");
            sbuff.append("INSERT INTO ").append(sTable).append('(');

            Iterator itr = _newObj.getProperties(false).entrySet().iterator();
            Object oValue;
            String sValue;
            while (itr.hasNext()) {
                Map.Entry entry = (Map.Entry) itr.next();
                oValue = entry.getValue();
                if (oValue == null)
                    continue;

                //else
                sValue = StrConvertor.objToStr(oValue).trim();
                sValue = StrUtil.sqlEncode(sValue);
                sValue = StrUtil.escapeEncode(sValue);
                if (sbuffValues.length() > 0) {
                    sbuff.append(',');
                    sbuffValues.append(',');
                }
                sbuff.append((String) entry.getKey());
                sbuffValues.append('\'').append(sValue).append('\'');
            } //endwhile
            sbuff.append(") VALUES (").append(sbuffValues).append(");");

            //to save
            System.out.println(sbuff.toString());
            statement = session.createStatement(sbuff.toString());
            statement.executeUpdate();
        } catch (Exception ex) {
            Iterator itr = _newObj.getProperties(false).entrySet().iterator();
            Object oValue;
            while (itr.hasNext()) {
                Map.Entry entry = (Map.Entry) itr.next();
                oValue = entry.getValue();
                System.out.println(entry.getKey() + "=" + oValue);
            }
            throw ex;
        } finally {
            sbuff.setLength(0);
            sbuffValues.setLength(0);
            if (statement != null)
                statement.clear();
        }
    }

    /**
     * Updates the imported data at last.
     * 
     * @throws Exception
     */
    private void lastUpdate() throws Exception {
        this.executeSQLElement("last-update");
    }

    private void executeSQLElement(String _sName) throws Exception {
        Element ele = m_xmlConfig.element(_sName);
        if (ele == null || "false".equals(ele.attributeValue("enabled"))) {
            return;
        }

        //else
        List eleSQLs = ele.elements("sql");
        if (eleSQLs.isEmpty())
            return;

        //else, to execute the SQLs
        String strSQL;
        ISession session = this.getSession();
        for (int i = 0; i < eleSQLs.size(); i++) {
            strSQL = ((Element) eleSQLs.get(i)).getTextTrim();
            try {
                System.out.println("to execute: " + strSQL);
                IStatement statement = session.createStatement(strSQL);
                this.registerAlias(statement, ele.elements("alias"));
                statement.executeUpdate();
                statement.clear();
            } catch (Exception ex) {
                throw new PureException(PureException.DATABASE_ACCESS, strSQL, ex);
            }
        }
    }

    /**
     * Registers alias for statement.
     * 
     * @param _statement
     * @param _aliasElements
     * @throws Exception
     */
    private void registerAlias(IStatement _statement, List _aliasElements) throws Exception {
        _statement.registerAlias("this", m_entityMetadata.getEntityClass());

        Element eleAlias;
        String sAlias, sClassName;
        for (int i = 0; i < _aliasElements.size(); i++) {
            eleAlias = (Element) _aliasElements.get(i);
            sAlias = eleAlias.attributeValue("name");
            sClassName = eleAlias.attributeValue("class");
            _statement.registerAlias(sAlias, Class.forName(sClassName));
        }
    }

}