Java tutorial
/* * FlexJAXBMappedDBDataEngine.java * * Created on July 15, 2009 10:30:30 AM * * Copyright (C) 2009 Jayson Yu * * This program is free software; you can redistribute it and/or modify it under the terms of the * GNU General Public License as published by the Free Software Foundation; either version 2 of the * License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without * even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * General Public License for more details. * * You should have received a copy of the GNU General Public License along with this program; if not, * write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA * */ package com.flexoodb.engines; import com.flexoodb.FlexContainer; import com.flexoodb.common.FlexUtils; import com.flexoodb.common.ObjectNotFoundException; import com.flexoodb.modeler.FlexAttribute; import com.flexoodb.modeler.FlexElement; import com.flexoodb.pool.ConnectionPool; import com.flexoodb.pool.RecordSet; import com.intigrix.utils.ExceptionTools; import java.io.ByteArrayInputStream; import java.lang.reflect.Method; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.math.BigInteger; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; import java.util.Enumeration; import java.util.Hashtable; import java.util.Iterator; import java.util.List; import java.util.Vector; import java.util.concurrent.ConcurrentHashMap; import javax.xml.datatype.XMLGregorianCalendar; import org.apache.commons.configuration.XMLConfiguration; import org.apache.log4j.Logger; /** * implementation to store JAXB objects to traditional RDBMS. To use this implementation you must already have an existing database running * with all the tables created. * * this implementation has been tested in MySQL6.0 and uses MySQL specific SQL features that may not work in other databases. * * @author Jayson Yu * @version %I%, %G% * @since 1.1 */ public class FlexJAXBMappedDBDataEngine implements FlexDataInterface { private FlexUtils _flexutils = new FlexUtils(); private ConnectionPool _pool = null; private String _dbname = "flexoodb"; private boolean _shared = false; private int _idlength = 10; private ConcurrentHashMap<String, String> _tables = new ConcurrentHashMap<String, String>(); private ConcurrentHashMap<String, FlexElement> _elements = new ConcurrentHashMap<String, FlexElement>(); public Logger _log = Logger.getLogger(this.getClass().getName()); @Override public Object find(String id, Class c) throws Exception { return find(id, c, null, true); } public Object find(String id, Class c, boolean revivechildren) throws Exception { return find(id, c, null, revivechildren); } @Override public Object find(String id, Class c, String targettable) throws Exception { return find(id, c, targettable, true); } @Override public Object find(String id, Class c, String targettable, boolean revivechildren) throws Exception { Object obj = null; Connection conn = null; try { conn = (Connection) _pool.getConnection(); String tablename = (targettable == null ? c.getSimpleName() : targettable).toLowerCase(); String realtablename = null; String proxtablename = null; tablename = tablename.endsWith("type") ? tablename.substring(0, tablename.lastIndexOf("type")) : tablename; FlexElement element = _elements.get(tablename); if (element == null) { // if we cant find the table in the query then we look for the tablename using the passed class proxtablename = c.getSimpleName().toLowerCase(); proxtablename = (proxtablename.endsWith("type") ? proxtablename.substring(0, proxtablename.lastIndexOf("type")) : proxtablename).toLowerCase(); element = _elements.get(proxtablename); if (element == null) { throw new Exception("could not process query, due to: no record artifact table found."); } } else { realtablename = element.getAttribute("realtablename").getValue(); } String idcolumn = element.getAttribute("idcolumn").getValue(); String parentidcolumn = element.getAttribute("parentidcolumn").getValue(); boolean includeidcolumns = element.getAttribute("includeidcolumns").getValue() == null ? false : (element.getAttribute("includeidcolumns").getValue().equalsIgnoreCase("true")); FlexElement idelement = element.getElementByName(idcolumn); PreparedStatement ps = (PreparedStatement) conn.prepareStatement( "select * from " + ((realtablename != null) ? realtablename : tablename.toLowerCase()) + " where " + idcolumn + "=?"); //System.out.println(">>>"+idcolumn+" e:"+idelement+" t:"+idelement+" q:select * from "+tablename.toLowerCase()+" where "+idcolumn+"=?"); if (idelement.getType().equals("string")) { ps.setString(1, id); } else { ps.setInt(1, Integer.parseInt(id)); } ResultSet rec = ps.executeQuery(); RecordSet res = new RecordSet(rec); // check if a record was found if (res != null && res.size() > 0) { String parentid = null; if (parentidcolumn != null) { parentid = res.getString(parentidcolumn); } //obj = new FlexContainer(_flexutils.getObject(FlexUtils.getRDBMSRecordAsXML(tablename, res, idcolumn, parentidcolumn,includeidcolumns),c)); obj = new FlexContainer(_flexutils.getObject( FlexUtils.getRDBMSRecordAsXML((proxtablename != null ? proxtablename : tablename), res, idcolumn, parentidcolumn, includeidcolumns, element), c)); ps.close(); ((FlexContainer) obj).setId(id); ((FlexContainer) obj).setParentId(parentid); // in case the targettable is different (ie most probably a group id) then we add teh prefix or group id so // the engine can use that as the query for the child elements. String prefix = (tablename.indexOf("_") > -1) ? tablename.substring(0, tablename.indexOf("_") + 1) : ""; // we inspect the object so we can revive the complete object reviveObject(id, (FlexContainer) obj, conn, prefix, revivechildren); } else { ps.close(); } } catch (Exception f) { f.printStackTrace(); throw f; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception g) { } } return obj; } private void reviveObject(String parentid, FlexContainer parent, Connection conn, String prefix, boolean revivechildren) throws Exception { Vector v = new Vector(); try { Class c = parent.getObject().getClass(); Method[] methods = c.getMethods(); for (int i = 0; i < methods.length; i++) { Method method = methods[i]; if (method.getName().startsWith("get") && method.getReturnType() != null && !method.getReturnType().getSimpleName().equals("Class")) { Class ret = method.getReturnType(); if (ret.getSimpleName().equals("List")) { Object[] args = null; //List list = (ArrayList) method.invoke(parent.getObject(), args); List list = new Vector<FlexContainer>(); ParameterizedType t = (ParameterizedType) method.getGenericReturnType(); Type type = t.getActualTypeArguments()[0]; String[] s = ("" + type).split(" "); String classname = s[1].substring(s[1].lastIndexOf(".") + 1); String tablename = classname.toLowerCase(); tablename = tablename.endsWith("type") ? tablename.substring(0, tablename.lastIndexOf("type")) : tablename; //FlexElement element = _elements.get(prefix+tablename); FlexElement element = _elements.get(tablename); String idcolumn = element.getAttribute("idcolumn").getValue(); String realtablename = element.getAttribute("realtablename").getValue(); String parentidcolumn = element.getAttribute("parentidcolumn").getValue(); boolean includeidcolumns = element.getAttribute("includeidcolumns").getValue() == null ? false : (element.getAttribute("includeidcolumns").getValue().equalsIgnoreCase("true")); //System.out.println("t:"+tablename+" "+realtablename+" >"+"select * from "+prefix+((realtablename!=null)?realtablename:tablename.toLowerCase())+" where "+parentidcolumn+"=?"+"<"); FlexElement idelement = element.getElementByName(parentidcolumn); PreparedStatement ps = (PreparedStatement) conn.prepareStatement("select * from " + prefix + ((realtablename != null) ? realtablename : tablename.toLowerCase()) + " where " + parentidcolumn + "=?"); //PreparedStatement ps = (PreparedStatement) conn.prepareStatement("select * from "+((realtablename!=null)?realtablename:tablename.toLowerCase())+" where "+parentidcolumn+"=?"); if (idelement.getType().equals("string")) { ps.setString(1, parentid); } else { ps.setInt(1, Integer.parseInt(parentid)); } //System.out.println(">> query:"+ps.toString()); ResultSet rec = ps.executeQuery(); RecordSet res = new RecordSet(rec); // check if a record was found while (res != null && res.next()) { //String id = res.getString("id"); String id = res.getString(idcolumn); //Object o2 = _flexutils.getObject(FlexUtils.getRDBMSRecordAsXML(tablename, res, idcolumn, parentidcolumn,includeidcolumns),Class.forName(s[1])); Object o2 = _flexutils .getObject( FlexUtils.getRDBMSRecordAsXML(tablename, res, idcolumn, parentidcolumn, includeidcolumns), ClassLoader.getSystemClassLoader().loadClass(s[1])); if (id != null && o2 != null) { //instead of adding this in the object's list we add it in the parent flex's //list.add(o2); FlexContainer fo = new FlexContainer(o2); fo.setId(id); fo.setParentId(parentid); list.add(fo); // then we check if this guy has children! enabling this will cause a loop of death //reviveObject(id,o2,conn); } } // finally we add it to the parent flex parent.addChildren(tablename, list); //System.out.println(">>>"+parent.getChildren()); } else if (!ret.getName().startsWith("java") && !ret.getSimpleName().toLowerCase().endsWith("byte[]") && !ret.getSimpleName().toLowerCase().equals("int")) // if complex { String tablename = ret.getSimpleName().toLowerCase(); tablename = tablename.endsWith("type") ? tablename.substring(0, tablename.lastIndexOf("type")) : tablename; FlexElement element = _elements.get(tablename); String idcolumn = element.getAttribute("idcolumn").getValue(); String realtablename = element.getAttribute("realtablename").getValue(); String parentidcolumn = element.getAttribute("parentidcolumn").getValue(); boolean includeidcolumns = element.getAttribute("includeidcolumns").getValue() == null ? false : (element.getAttribute("includeidcolumns").getValue().equalsIgnoreCase("true")); FlexElement idelement = element.getElementByName(parentidcolumn); PreparedStatement ps = (PreparedStatement) conn.prepareStatement("select * from " + prefix + ((realtablename != null) ? realtablename : tablename.toLowerCase()) + " where " + parentidcolumn + "=" + (idelement.getType().equals("string") ? ("'" + parentid + "'") : parentid)); ResultSet rec = ps.executeQuery(); // check if a record was found RecordSet res = new RecordSet(rec); if (res != null && res.size() > 0) { //String id = rec.getString("id"); String id = res.getString(idcolumn); Object o2 = _flexutils.getObject(FlexUtils.getRDBMSRecordAsXML(tablename, res, idcolumn, parentidcolumn, includeidcolumns), ret); if (o2 != null) { String setmethod = "set" + method.getName().substring(3); Object[] args = new Object[1]; args[0] = o2; Class[] cls = new Class[1]; cls[0] = o2.getClass(); Method met = c.getMethod(setmethod, cls); met.invoke(parent.getObject(), args); /*if (revivechildren) { reviveObject(id,o2,conn,prefix,revivechildren); }*/ } if (rec.last()) { break; } } } } } } catch (Exception f) { throw f; } } public Collection<Object> runQuery(String query, Class c) throws Exception { return runQuery(query, c, true); } public Collection<Object> runQuery(String query, Class c, boolean usedefaultimplementation) throws Exception { Vector v = new Vector(); Connection conn = null; try { checkForSQLInjection(query); conn = (Connection) _pool.getConnection(); String tablename = query.split("\\s")[3]; String oldtablename = query.split("\\s")[3]; tablename = (tablename.endsWith("type") ? tablename.substring(0, tablename.lastIndexOf("type")) : tablename).toLowerCase(); String idcolumn = null; String realtablename = null; String parentidcolumn = null; boolean includeidcolumns = false; String proxtablename = null; PreparedStatement ps = null; FlexElement element = _elements.get(tablename); if (element == null) { // if we cant find the table in the query then we look for the tablename using the passed class proxtablename = c.getSimpleName().toLowerCase(); proxtablename = (proxtablename.endsWith("type") ? proxtablename.substring(0, proxtablename.lastIndexOf("type")) : proxtablename).toLowerCase(); element = _elements.get(proxtablename); if (element == null) { throw new Exception("could not process query, due to: no record artifact table found."); } } else { realtablename = element.getAttribute("realtablename").getValue(); } idcolumn = element.getAttribute("idcolumn").getValue(); parentidcolumn = element.getAttribute("parentidcolumn").getValue(); includeidcolumns = element.getAttribute("includeidcolumns").getValue() == null ? false : (element.getAttribute("includeidcolumns").getValue().equalsIgnoreCase("true")); if (usedefaultimplementation) { StringBuffer q = new StringBuffer(); if (query.toUpperCase().indexOf("WHERE") > 0) { q.append(query.substring(query.toUpperCase().indexOf("WHERE"), query.length())); } else { q.append(query.substring(query.toUpperCase().indexOf(oldtablename.toUpperCase()), query.length())); //q.append(query.substring(query.toUpperCase().indexOf(oldtablename.toUpperCase())+oldtablename.length()+1,query.length())); if (q.toString().equalsIgnoreCase(oldtablename)) { q = new StringBuffer(); } } if (query.toUpperCase().indexOf("LIMIT") > 0) { //q.append(query.substring(query.toUpperCase().indexOf("LIMIT"),query.length())); } // we replace parentid column with the one in the table. if (parentidcolumn != null) { q = new StringBuffer( FlexUtils.replaceFirstString(q.toString(), " parentid", " " + parentidcolumn)); } String qry = q.toString().trim(); qry = qry.substring(qry.indexOf(" ") > 0 ? qry.indexOf(" ") : 0).trim(); //System.out.println("parentid:"+parentidcolumn+" orig query:"+qry+" query:select * from "+((realtablename!=null)?realtablename:tablename.toLowerCase())+" "+(qry!=null && !qry.startsWith("order")? (!qry.isEmpty()?"where ":""):"")+qry); ps = (PreparedStatement) conn.prepareStatement( "select * from " + ((realtablename != null) ? realtablename : tablename.toLowerCase()) + " " + (qry != null && !qry.startsWith("order") ? ((!qry.isEmpty() && !qry.startsWith("limit")) ? "where " : "") : "") + qry); } else { //System.out.println(">>>>"+query); ps = (PreparedStatement) conn.prepareStatement(query); } ResultSet rec = ps.executeQuery(); RecordSet res = new RecordSet(rec); while (res != null && res.size() > 0 && res.next()) { String id = res.getString(idcolumn); //String xml = FlexUtils.getRDBMSRecordAsXML(tablename, res, idcolumn, parentidcolumn,includeidcolumns); String xml = FlexUtils.getRDBMSRecordAsXML((proxtablename != null ? proxtablename : tablename), res, idcolumn, parentidcolumn, includeidcolumns, element); String i = idcolumn != null ? res.getString(idcolumn) : res.getString("id"); String p = parentidcolumn != null ? res.getString(parentidcolumn) : res.getString("parentid"); Object o2 = new FlexContainer(_flexutils.getObject(xml, c)); ((FlexContainer) o2).setId(id); ((FlexContainer) o2).setParentId(p); //Object o2 = _flexutils.getObject(xml,c); if (o2 != null) { v.add(o2); } if (rec.isLast()) { break; } } } catch (Exception f) { System.out.println(">>>>QUERY FAULT:[" + query + "]"); f.printStackTrace(); throw f; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception g) { } } return v; } public Object persist(Object obj) throws Exception { return persistenceAction(FlexDataInterface.PERSIST, obj); } public Object persist(Object obj, String targettable) throws Exception { return persistenceAction(FlexDataInterface.PERSIST, obj, targettable); } public Object remove(Object obj) throws Exception { return persistenceAction(FlexDataInterface.REMOVE, obj); } public Object remove(Object obj, String targettable) throws Exception { return persistenceAction(FlexDataInterface.REMOVE, obj, targettable); } public Object refresh(Object obj) throws Exception { return persistenceAction(FlexDataInterface.REFRESH, obj); } public Object refresh(Object obj, String targettable) throws Exception { return persistenceAction(FlexDataInterface.REFRESH, obj, targettable); } public Object merge(Object obj) throws Exception { return persistenceAction(FlexDataInterface.MERGE, obj); } public Object merge(Object obj, String targettable) throws Exception { return persistenceAction(FlexDataInterface.MERGE, obj, targettable); } private Object persistenceAction(int action, Object obj) throws Exception { return persistenceAction(action, obj, 0, null); } private Object persistenceAction(int action, Object obj, String targettable) throws Exception { return persistenceAction(action, obj, 0, targettable); } private Object persistenceAction(int action, Object obj, int attempts) throws Exception { return persistenceAction(action, obj, attempts, null); } private Object persistenceAction(int action, Object obj, int attempts, String targettable) throws Exception { Object o = null; Connection conn = null; try { conn = (Connection) _pool.getConnection(); if (conn != null) { // insert if (action == FlexDataInterface.PERSIST) { o = persist(obj, conn, targettable); } else if (action == FlexDataInterface.REMOVE) { o = remove(obj, conn, targettable); } // update the object else if (action == FlexDataInterface.REFRESH) { o = refresh(obj, conn, targettable); } // update else if (action == FlexDataInterface.MERGE) { o = merge(obj, conn, targettable); } //em.getTransaction().commit(); //em.close(); } } catch (Exception e) { e.printStackTrace(); throw e; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception f) { } } return o; } private Object persist(Object obj, Connection conn, String targettable) throws Exception { // check if the object has a table String tablename = (targettable == null ? ((FlexContainer) obj).getObject().getClass().getSimpleName() : targettable).toLowerCase(); tablename = tablename.endsWith("type") ? tablename.substring(0, tablename.lastIndexOf("type")) : tablename; String id = ((FlexContainer) obj).getId(); String parentid = ((FlexContainer) obj).getParentId(); Object obj2 = ((FlexContainer) obj).getObject(); // retrieve the methods of the object and use as param. FlexElement element = null; if (tablename.indexOf("_") > -1) { element = _elements.get(tablename.substring(tablename.indexOf("_") + 1)); } else { element = _elements.get(tablename); } String idcolumn = element.getAttribute("idcolumn").getValue(); String realtablename = element.getAttribute("realtablename").getValue(); String parentidcolumn = element.getAttribute("parentidcolumn").getValue(); String autoincrement = element.getAttribute("autoincrement").getValue(); boolean includeidcolumns = element.getAttribute("includeidcolumns").getValue() == null ? false : (element.getAttribute("includeidcolumns").getValue().equalsIgnoreCase("true")); if (!autoincrement.equalsIgnoreCase("true")) { if (id == null || id.isEmpty()) { id = getNewId(); FlexUtils.setId(id, (FlexContainer) obj); } if (parentid == null || parentid.isEmpty()) { ((FlexContainer) obj).setParentId(id); } } StringBuffer fields = new StringBuffer(); StringBuffer entries = new StringBuffer(); Hashtable<String, Object[]> fieldswithcontent = getNonNullObjectFields(tablename, idcolumn, parentidcolumn, id, parentid, obj2, element); Enumeration en = fieldswithcontent.keys(); while (en.hasMoreElements()) { String field = (String) en.nextElement(); //boolean ok = true; fields.append(",`" + field + "`"); entries.append(",?"); } //System.out.println(">>>insert into "+tablename.toLowerCase()+" ("+fields.substring(1)+") values ("+entries.substring(1)+")"); PreparedStatement ps = null; if (autoincrement.equalsIgnoreCase("true")) { ps = (PreparedStatement) conn.prepareStatement( "insert into " + ((realtablename != null && !_shared) ? realtablename : tablename.toLowerCase()) + " (" + fields.substring(1) + ") values (" + entries.substring(1) + ")", Statement.RETURN_GENERATED_KEYS); // then we get the preparedstatement updatePreparedStatement(tablename, fieldswithcontent, ps); ps.executeUpdate(); ResultSet keys = ps.getGeneratedKeys(); keys.next(); int newkey = keys.getInt(1); keys.close(); FlexUtils.setId(newkey + "", (FlexContainer) obj); if (parentid == null || parentid.isEmpty()) { ((FlexContainer) obj).setParentId(newkey + ""); } FlexUtils.setObjectMethod(((FlexContainer) obj).getObject(), idcolumn, new BigInteger(newkey + "")); } else { ps = (PreparedStatement) conn.prepareStatement( "insert into " + ((realtablename != null && !_shared) ? realtablename : tablename.toLowerCase()) + " (" + fields.substring(1) + ") values (" + entries.substring(1) + ")"); // then we get the preparedstatement updatePreparedStatement(tablename, fieldswithcontent, ps); ps.executeUpdate(); } ps.close(); return obj; } private boolean remove(Object obj, Connection conn, String targettable) throws Exception { boolean success = false; // check if the object has a table String tablename = (targettable == null ? ((FlexContainer) obj).getObject().getClass().getSimpleName() : targettable).toLowerCase(); tablename = tablename.endsWith("type") ? tablename.substring(0, tablename.lastIndexOf("type")) : tablename; //FlexElement element = _elements.get(tablename); FlexElement element = null; if (tablename.indexOf("_") > -1) { element = _elements.get(tablename.substring(tablename.indexOf("_") + 1)); } else { element = _elements.get(tablename); } String idcolumn = element.getAttribute("idcolumn").getValue(); String realtablename = element.getAttribute("realtablename").getValue(); String id = ((FlexContainer) obj).getId(); FlexElement idelement = element.getElementByName(idcolumn); PreparedStatement ps = (PreparedStatement) conn.prepareStatement( "delete from " + ((realtablename != null && !_shared) ? realtablename : tablename.toLowerCase()) + " where " + idcolumn + "=?"); if (idelement.getType().equals("string")) { ps.setString(1, id); } else { ps.setInt(1, Integer.parseInt(id)); } ps.executeUpdate(); ps.close(); success = true; return success; } private Object refresh(Object obj, Connection conn, String targettable) throws Exception { // check if the object has a table String id = ((FlexContainer) obj).getId(); Object obj2 = find(id, ((FlexContainer) obj).getObject().getClass()); if (obj2 != null) { _flexutils.softTransfer(_flexutils.getAvailableValues(obj2), obj); } else { throw new ObjectNotFoundException( id + " " + ((FlexContainer) obj).getObject().getClass().getSimpleName() + " does not exist."); } return obj; } private Object merge(Object obj, Connection conn, String targettable) throws Exception { // check if the object has a table String tablename = (targettable == null ? ((FlexContainer) obj).getObject().getClass().getSimpleName() : targettable).toLowerCase(); tablename = tablename.endsWith("type") ? tablename.substring(0, tablename.lastIndexOf("type")) : tablename; //FlexElement element = _elements.get(tablename); FlexElement element = null; if (tablename.indexOf("_") > -1) { element = _elements.get(tablename.substring(tablename.indexOf("_") + 1)); } else { element = _elements.get(tablename); } String idcolumn = element.getAttribute("idcolumn").getValue(); String realtablename = element.getAttribute("realtablename").getValue(); String parentidcolumn = element.getAttribute("parentidcolumn").getValue(); boolean includeidcolumns = element.getAttribute("includeidcolumns").getValue() == null ? false : (element.getAttribute("includeidcolumns").getValue().equalsIgnoreCase("true")); String id = ((FlexContainer) obj).getId(); String parentid = ((FlexContainer) obj).getParentId(); Object obj2 = ((FlexContainer) obj).getObject(); FlexElement idelement = element.getElementByName(idcolumn); // get old record in database first PreparedStatement ps = (PreparedStatement) conn.prepareStatement( "select * from " + ((realtablename != null && !_shared) ? realtablename : tablename.toLowerCase()) + " where " + idcolumn + "=?"); if (idelement.getType().equals("string")) { ps.setString(1, id); } else { ps.setInt(1, Integer.parseInt(id)); } ResultSet res = ps.executeQuery(); // check if a record was found if (res != null && res.next()) { Hashtable h = FlexUtils.getRDBMSRecordValues(res); // get available values in the original ps.close(); _flexutils.softTransfer(h, obj2); Hashtable<String, Object[]> fieldswithcontent = getNonNullObjectFields(tablename, idcolumn, parentidcolumn, id, parentid, obj2, element); StringBuffer fields = new StringBuffer(); Enumeration en = fieldswithcontent.keys(); int i = 0; while (en.hasMoreElements()) { i++; String field = (String) en.nextElement(); fields.append(",`" + field + "`=?"); } i++; PreparedStatement ps2 = (PreparedStatement) conn.prepareStatement( "update " + ((realtablename != null && !_shared) ? realtablename : tablename.toLowerCase()) + " set " + fields.substring(1) + " where " + idcolumn + "=?"); if (idelement.getType().equals("string")) { ps2.setString(i, id); } else { ps2.setInt(i, Integer.parseInt(id)); } updatePreparedStatement(tablename, fieldswithcontent, ps2); ps2.executeUpdate(); ps2.close(); } else { throw new ObjectNotFoundException( "update error: " + ((realtablename != null) ? realtablename : tablename.toLowerCase()) + " with id:" + id + " does not exist."); } return obj; } private Hashtable<String, Object[]> getNonNullObjectFields(String tablename, String idcolumn, String parentidcolumn, String id, String parentid, Object obj, FlexElement element) throws Exception { Hashtable<String, Object[]> fields = new Hashtable<String, Object[]>(); Enumeration en = element.retrieveElements(); while (en.hasMoreElements()) { FlexElement el = (FlexElement) en.nextElement(); String field = el.getName(); String type = el.getType(); // we only get columns that are not autoincrement if (!el.getAttribute("autoincrement").getValue().equals("true")) { Object[] payload = new Object[2]; if (field.equalsIgnoreCase(idcolumn)) { //payload[0] = "string"; payload[0] = type; // it can only be either a string or numeric! if (!type.equals("string")) { payload[1] = new BigInteger(id); } else { payload[1] = id; } fields.put(field, payload); } else if (parentidcolumn != null && field.equalsIgnoreCase(parentidcolumn)) { //payload[0] = "string"; payload[0] = type; // it can only be either a string or numeric! if (!type.equals("string")) { payload[1] = new BigInteger(parentid); } else { payload[1] = parentid; } fields.put(field, payload); } else { Object o = null; if (type.equals("string") || type.equals("base64Binary") || type.equals("byte[]") || type.equals("date") || type.equals("dateTime") || type.equals("integer") || type.equals("time") || type.equals("double") || type.equals("float") || type.equals("long")) { o = FlexUtils.getObjectMethodGetResult(obj, field); if (o instanceof byte[]) { if ((new String((byte[]) o)).equalsIgnoreCase("NULL")) { continue; } } //if (o!=null && (!type.equalsIgnoreCase("dateTime") || (type.equalsIgnoreCase("dateTime") && (""+o).indexOf("0003-11-30") < 0))) /*if (type.equalsIgnoreCase("dateTime") && (""+o).indexOf("0003-11-30") > -1) { System.out.println("dateitiome:"+(""+o)); }*/ if (o != null) { //System.out.println(field+" "+type+" "+o); // we dont update blank dates? payload[0] = type; payload[1] = o; fields.put(field, payload); } } else { throw new Exception("unknown type [" + type + "] for field [" + field + "] encountered while trying to update table [" + tablename + "]."); } } } } return fields; } private void updatePreparedStatement(String tablename, Hashtable<String, Object[]> fieldswithcontent, PreparedStatement ps) throws Exception { Enumeration en = fieldswithcontent.keys(); int i = 0; while (en.hasMoreElements()) { i++; try { String field = (String) en.nextElement(); Object[] o2 = fieldswithcontent.get(field); String type = (String) o2[0]; Object o = o2[1]; //System.out.println(field+" "+type+" "+o); if (type.equals("string")) { ps.setString(i, (String) o); } else if (type.equals("byte[]") || type.equals("base64Binary")) { ps.setBinaryStream(i, new ByteArrayInputStream((byte[]) o)); } else if (type.equals("dateTime")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; Date d = null; if (cal.toString().indexOf("0003-11-30") > -1) { ps.setString(i, "0000-00-00 00:00:00"); } else { d = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString()); ps.setTimestamp(i, new java.sql.Timestamp(d.getTime())); } } else if (type.equals("date")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString()); ps.setDate(i, java.sql.Date.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(d1))); } else if (type.equals("time")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; String c = cal.toString(); c = c.replaceFirst("0003-11-30", "0000-00-00"); Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(c); ps.setTime(i, new java.sql.Time(d1.getTime())); } else if (type.equals("integer")) { ps.setInt(i, ((BigInteger) o).intValue()); } else if (type.equals("double")) { ps.setDouble(i, (Double) o); } else if (type.equals("float")) { ps.setFloat(i, (Float) o); } else if (type.equals("long")) { ps.setLong(i, (Long) o); } else { throw new Exception("unknown type [" + type + "] for field [" + field + "] encountered while trying to update table [" + tablename + "]."); } } catch (Exception e) { e.printStackTrace(); throw e; } } } private synchronized String getNewId() { //return FlexUtils.generateRandomCode(_idlength); return FlexUtils.generateKey(_idlength); } // initialization public void initialize(Object conf) throws Exception { if (conf != null) { XMLConfiguration config = (XMLConfiguration) conf; _pool = new ConnectionPool(); int i = 0; String idlength = config.getString("flexoodb[@idlength]"); if (idlength != null) { _idlength = Integer.parseInt(idlength); if (_idlength < 10) { throw new Exception("flexoodb ID's must be >=10 in length."); } } _dbname = config.getString("flexoodb[@dbname]"); if (_dbname == null) { _dbname = "flexoodb"; } _shared = config.getString("flexoodb[@shared]") != null ? config.getString("flexoodb[@shared]").equalsIgnoreCase("true") : false; while (!_pool.set(config.getString("flexoodb[@odbcurl]"), config.getString("flexoodb[@username]"), config.getString("flexoodb[@password]"), config.getString("flexoodb[@odbcclass]"), config.getInt("flexoodb[@initconnections]"), config.getInt("flexoodb[@maxconnections]"), "FlexOODB") && i < 2) { try { Thread.sleep(2000); } catch (Exception e) { } i++; } Connection conn = null; try { conn = (Connection) _pool.getConnection(); // first we get the tenants if any Vector<String> tenants = new Vector<String>(); List tl = config.getList("flexoodb.tenants.tenant[@name]"); for (int j = 0; j < tl.size(); j++) { String tenant = config.getString("flexoodb.tenants.tenant(" + j + ")[@name]"); String description = config.getString("flexoodb.tenants.tenant(" + j + ")[@description]"); String status = config.getString("flexoodb.tenants.tenant(" + j + ")[@status]"); if (status != null && status.equalsIgnoreCase("Active")) { tenants.add(tenant); } } // then we get the table structures List tables = config.getList("flexoodb.table[@name]"); for (int j = 0; j < tables.size(); j++) { String tablename = config.getString("flexoodb.table(" + j + ")[@name]"); String alias = config.getString("flexoodb.table(" + j + ")[@alias]"); String idcolumn = config.getString("flexoodb.table(" + j + ")[@idcolumn]"); String parentidcolumn = config.getString("flexoodb.table(" + j + ")[@parentidcolumn]"); String includeidcolumns = config.getString("flexoodb.table(" + j + ")[@includeidcolumns]"); String parenttable = config.getString("flexoodb.table(" + j + ")[@parenttable]"); String autoincrement = config.getString("flexoodb.table(" + j + ")[@autoincrement]", "false"); String createstmt = config.getString("flexoodb.table(" + j + ").create"); FlexElement element = new FlexElement((alias != null ? alias : tablename)); element.addAttribute((new FlexAttribute("realtablename")).setValue(tablename)); element.addAttribute((new FlexAttribute("idcolumn")).setValue(idcolumn)); element.addAttribute((new FlexAttribute("parentidcolumn")).setValue(parentidcolumn)); element.addAttribute((new FlexAttribute("includeidcolumns")).setValue(includeidcolumns)); element.addAttribute((new FlexAttribute("parenttable")).setValue(parenttable)); element.addAttribute((new FlexAttribute("autoincrement")).setValue(autoincrement)); //element.addAttribute((new FlexAttribute("createstmt")).setValue(createstmt)); PreparedStatement ps = null; ResultSet res = null; String sampletenant = ""; if (createstmt != null) { try { // we check if the tables exists. if (createstmt != null && !createstmt.isEmpty()) { if (tenants.size() > 0 && _shared) { Iterator it = tenants.iterator(); while (it.hasNext()) { String tenant = (String) it.next(); if (sampletenant.isEmpty()) { sampletenant = sampletenant + tenant; } ps = (PreparedStatement) conn.prepareStatement(createstmt.replaceAll( "::" + tablename + "::", (_shared ? (tenant + "_" + tablename) : (tenant + tablename)))); ps.execute(); } } else // must be a single instance then { ps = (PreparedStatement) conn.prepareStatement(createstmt .replaceAll("::" + tablename + "::", sampletenant + tablename)); ps.execute(); } } } catch (Exception e) // if we cant describe then doesnt exist? { System.out.println("unable to initialize table:" + tablename); e.printStackTrace(); } } // we store the structure as well //PreparedStatement ps = (PreparedStatement) conn.prepareStatement("select * from "+tablename.toLowerCase()+" LIMIT 1"); ps = (PreparedStatement) conn .prepareStatement("describe " + (sampletenant.isEmpty() ? tablename.toLowerCase() : (sampletenant + "_" + tablename.toLowerCase()))); res = ps.executeQuery(); FlexElement structure = FlexUtils.getRDBMSTableDescriptionAsFlexElement(tablename, res); Enumeration en = structure.retrieveElements(); while (en.hasMoreElements()) { element.addElement((FlexElement) en.nextElement()); } // add field aliases if any List fields = config.getList("flexoodb.table(" + j + ").field[@name]"); for (int k = 0; k < fields.size(); k++) { String fieldname = config.getString("flexoodb.table(" + j + ").field(" + k + ")[@name]"); String fieldalias = config.getString("flexoodb.table(" + j + ").field(" + k + ")[@alias]"); FlexElement fieldelement = new FlexElement(fieldname.trim().toLowerCase()); fieldelement.addAttribute((new FlexAttribute("alias")).setValue(fieldalias)); element.addElement(fieldelement); } _elements.put((alias != null ? alias : tablename), element); //_elements.put(tablename, element); } } catch (Exception e) { e.printStackTrace(); throw e; } finally { if (conn == null) { throw new SQLException( "Could not connect to " + (String) config.getString("database[@odbcurl]")); } _pool.releaseConnection(conn); } } } public void stop() throws Exception { try { _pool.disconnect(); } catch (Exception e) { } } @Override public boolean reindex(Object obj) throws Exception { throw new UnsupportedOperationException("Not supported yet."); } @Override public boolean drop(Object obj) throws Exception { throw new UnsupportedOperationException("Not supported yet."); } @Override public RecordSet rawQuery(String query) throws Exception { RecordSet ret = null; Connection conn = null; try { checkForSQLInjection(query); conn = (Connection) _pool.getConnection(); // get the table name first String tablename = query.toLowerCase().substring(query.indexOf("from") + 4, query.length()) .toLowerCase().trim(); tablename = tablename .substring(0, tablename.indexOf(" ") > 0 ? tablename.indexOf(" ") : tablename.length()).trim(); try { String replacement = tablename.substring(0, tablename.length() - 4); query = FlexUtils.replaceString(query.toLowerCase(), tablename, replacement); String parentidcolumn = null; FlexElement element = _elements .get(replacement.indexOf("_") > -1 ? replacement.substring(replacement.indexOf("_") + 1) : replacement); if (element != null) { parentidcolumn = element.getAttribute("parentidcolumn").getValue(); if (query.indexOf(" parentid") > -1) { query = FlexUtils.replaceFirstString(query, " parentid", " " + parentidcolumn); } } } catch (Exception e) { // if not best effort then } PreparedStatement ps = (PreparedStatement) conn.prepareStatement(query); ResultSet res = ps.executeQuery(); ret = new RecordSet(res); } catch (Exception f) { f.printStackTrace(); throw f; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception g) { } } return ret; } @Override public int rawUpdate(String update) throws Exception { int res = 0; Connection conn = null; try { conn = (Connection) _pool.getConnection(); PreparedStatement ps = (PreparedStatement) conn.prepareStatement(update); res = ps.executeUpdate(); } catch (Exception f) { throw f; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception g) { } } return res; } private void checkForSQLInjection(String query) throws Exception { String q = query.toLowerCase().trim(); q = FlexUtils.replaceString(q, " ", " "); if (q.indexOf(";") > -1 || q.indexOf("insert into") > -1 || q.indexOf("delete from") > -1 || q.indexOf("drop table") > -1 || q.indexOf("drop database") > -1) { try { throw new Exception("possible SQL injection:[" + query + "]"); } catch (Exception e) { _log.warn(ExceptionTools.stackTraceToString(e)); } throw new SQLException("invalid query."); } } private void queryTest(String query) throws Exception { /*Vector v = new Vector(); String tablename = query.split("\\s")[3]; StringBuffer q = new StringBuffer(); boolean idonly = true; if (query.toUpperCase().indexOf("WHERE") > 0) { String sub = query.substring(query.toUpperCase().indexOf("WHERE")+5); sub = sub.replaceAll("="," = "); sub = sub.replaceAll(">"," > "); sub = sub.replaceAll("<"," < ").trim(); System.out.println("from:"+sub); boolean done = false; boolean id = false; int seq = 0; String condition = null; while (!done) { int x = sub.indexOf(" "); String word = sub.substring(0,x<0?sub.length():x); int wlen = word.length(); if (word.startsWith("'")) { word = sub.substring(1,sub.indexOf("'",1)); wlen = word.length()+2; } System.out.println(seq+" w:"+word+"< "+wlen+" wl:"+word.length()+" "+q); // check if its a predicate if (":like:=:>:<:".indexOf(":"+word.toLowerCase()+":") > -1) { condition = word; seq = 2; } else if (":and:or:not:".indexOf(":"+word.toLowerCase()+":") > -1) { q.append(" "+word.trim()+" "); seq = 0; } else if (seq==0)// it must be a field! { seq = 1; // fields sequence if (word.trim().equalsIgnoreCase("parentid") || word.trim().equalsIgnoreCase("id")) { q.append(" "+word.trim()); id = true; } else if (word.trim().equalsIgnoreCase("limit") || word.trim().equalsIgnoreCase("element") || word.trim().equalsIgnoreCase("desc") || word.trim().equalsIgnoreCase("asc")) { q.append(" "+sub); done = true; } else { q.append(" (element='"+word.trim()+"'"); } } else if (seq == 2) { word = word.replaceAll("'"," "); if (id) { q.append(""+condition+"'"+word.trim()+"'"); } else { q.append(" and value "+condition+" '"+word.trim()+"')"); } seq = 0; condition = null; id = false; } sub = sub.substring(wlen).trim(); if (x < 0 || sub.length() == 0) { done = true; } } } else { q = new StringBuffer(query); } System.out.println(">>>"+q); * */ String tablename = query.toLowerCase().substring(query.indexOf("from") + 4, query.length()).toLowerCase() .trim(); tablename = tablename.substring(0, tablename.indexOf(" ") > 0 ? tablename.indexOf(" ") : tablename.length()) .trim(); String replacement = tablename.substring(0, tablename.length() - 4); query = FlexUtils.replaceString(query.toLowerCase(), tablename, replacement); } static public void main(String[] args) throws Exception { FlexJAXBMappedDBDataEngine e = new FlexJAXBMappedDBDataEngine(); //e.queryTest("select * from _grouptype where parentid='123' and id='234' and name='Group 4 1' or test='223 ' and field like '%abc%' and field2 like %123% limit 1,3 desc"); //e.queryTest("select * from _grouptype limit 1,2"); //e.queryTest("select * from _invoicetype where parentid='a' limit 0,10"); //e.queryTest("select max(cast(value as decimal))+1 as nextval from _subscriptiontype_index where element='subscriptionid'"); e.queryTest("select count(*) as cnt from flextestchildtype"); e.queryTest("select count(*) as cnt from flextestchildtype where parentid='9'"); } }