Java tutorial
/** * 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)); } } }