Java tutorial
/* * FlexDataEngine.java * * Created on Mar 19, 2009 9:01:49 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.Element; import com.flexoodb.common.FlexUtils; import com.flexoodb.common.ObjectNotFoundException; import com.flexoodb.pool.ConnectionPool; import com.flexoodb.pool.RecordSet; import java.lang.reflect.Method; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.Enumeration; import java.util.Hashtable; import java.util.List; import java.util.Vector; import java.util.concurrent.ConcurrentHashMap; import org.apache.commons.configuration.XMLConfiguration; /** * implementation to store JAXB objects to XML format in a database. To use this implementation you must already have an existing database running * and have extracted the file flexoodb.xml and reconfigured it for your database. Please note that the connection must have table creation and * deletion rights as tables are created on-the-fly. * * this implementation has been tested in MySQL6.0, if you are using a database other than mysql, please change the table creation and dropping * in the flexoodb.xml to the appropriate and corresponding syntax for your target database. We'd like to hear where you've used the API, please * email json@flexoodb.com if you are able to make it run on other databases. * * @author Jayson Yu * @version %I%, %G% * @since 1.1 */ public class FlexJAXBDBDataEngine implements FlexDataInterface { private FlexUtils _flexutils = new FlexUtils(); private ConnectionPool _pool = null; private String _dbname = "flexoodb"; private ConcurrentHashMap<String, String> _tables = new ConcurrentHashMap<String, String>(); private ConcurrentHashMap<String, String> _sqlstatements = new ConcurrentHashMap<String, String>(); private boolean _showsql = false; @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; if (checkTable(tablename, conn, false)) { PreparedStatement ps = (PreparedStatement) conn.prepareStatement( "select parentid,content from " + tablename.toLowerCase() + " where id='" + id + "'"); ResultSet res = ps.executeQuery(); // check if a record was found if (res != null && res.next()) { String parentid = res.getString("parentid"); obj = new FlexContainer(_flexutils.getObject(res.getString("content"), 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).getObject(), conn, prefix, revivechildren); /*JAXBContext context = JAXBContext.newInstance(((FlexContainer)obj).getObject().getClass()); Marshaller m = context.createMarshaller(); m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true); m.marshal(new JAXBElement(new QName("",c.getSimpleName()),((FlexContainer)obj).getObject().getClass(), ((FlexContainer)obj).getObject()),System.out); */ } else { ps.close(); } } } catch (Exception f) { throw f; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception g) { } } return obj; } private void reviveObject(String parentid, Object o, Connection conn, String prefix, boolean revivechildren) throws Exception { Vector v = new Vector(); try { Class c = o.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(o, args); 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 = prefix + classname.toLowerCase(); if (checkTable(tablename, conn, true)) { PreparedStatement ps = (PreparedStatement) conn.prepareStatement( "select id,content from " + tablename + " where parentid='" + parentid + "'"); ResultSet rec = ps.executeQuery(); // check if a record was found while (rec != null && rec.next()) { String id = rec.getString("id"); //Object o2 = _flexutils.getObject(rec.getString("content"),Class.forName(s[1])); Object o2 = _flexutils.getObject(rec.getString("content"), ClassLoader.getSystemClassLoader().loadClass(s[1])); if (id != null && o2 != null && !id.equalsIgnoreCase(parentid)) { list.add(o2); } } } } else if (!ret.getName().startsWith("java") && !ret.getSimpleName().toLowerCase().endsWith("byte[]") && !ret.getSimpleName().toLowerCase().equals("int")) // if complex { String tablename = prefix + ret.getSimpleName().toLowerCase(); if (checkTable(tablename, conn, true)) { PreparedStatement ps = (PreparedStatement) conn .prepareStatement("select distinct id,content from " + tablename + " where parentid='" + parentid + "'"); ResultSet rec = ps.executeQuery(); // check if a record was found if (rec != null && rec.next()) { String id = rec.getString("id"); Object o2 = _flexutils.getObject(rec.getString("content"), ret); if (o2 != null && !id.equalsIgnoreCase(parentid)) { 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(o, args); if (revivechildren) { reviveObject(id, o2, conn, prefix, revivechildren); } //System.out.println(">>> "+o2+" added!"); } /*if (rec.isLast()) { break; }*/ } } } } } } catch (Exception f) { throw f; } } private Hashtable<Method, Class> retrieveMethods(Class c) { Hashtable<Method, Class> methods = new Hashtable<Method, Class>(); try { // if this generates a fault then it must be abstract and we ignore it. c.newInstance(); Method[] m = c.getMethods(); int x = 0; for (int i = 0; i < m.length; i++) { if (m[i].getReturnType() != null && !m[i].getReturnType().getSimpleName().equals("Class")) { methods.put(m[i], c); } } } catch (Exception e) { //e.printStackTrace(); } return methods; } public Collection<Object> runQuery(String query, Class c) throws Exception { return runQuery(query, c, false); } public Collection<Object> runQuery2(String query, Class c, boolean usedefaultimplementation) throws Exception { Vector v = new Vector(); Connection conn = null; try { conn = (Connection) _pool.getConnection(); String tablename = query.split("\\s")[3]; // always search the index! if (checkTable(tablename, conn, false)) { StringBuffer q = new StringBuffer("where "); boolean hasid = false; if (query.toUpperCase().indexOf("WHERE") > 0) { String sub = query.substring(query.toUpperCase().indexOf("WHERE") + 5); sub = sub.replaceAll("<=", " <eq; "); sub = sub.replaceAll(">=", " >eq; "); sub = sub.replaceAll("<>", " &nteq; "); sub = sub.replaceAll("=", " = "); sub = sub.replaceAll(">", " > "); sub = sub.replaceAll("<", " < "); sub = sub.replaceAll("<eq;", "<="); sub = sub.replaceAll(">eq;", ">="); sub = sub.replaceAll("&nteq;", "<>").trim(); //System.out.println("from:"+sub); boolean done = false; boolean id = false; int seq = 0; String col = null; 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("w:"+word+"< "+wlen+" wl:"+word.length()); // 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(" _a." + word.trim()); id = true; hasid = true; } else if (word.trim().equalsIgnoreCase("order")) { String[] order = sub.split("\\s"); if (!order[2].equalsIgnoreCase("id") && !order[2].equalsIgnoreCase("parentid")) { // get the 3rd word -- ie the field if (!q.toString().toUpperCase().endsWith("WHERE")) { q.append(" and "); } q.append(" (_b.element='" + order[2] + "')"); q.append(" " + order[0] + " by _b.value " + sub.substring(sub.indexOf(order[2]) + order[2].length()).trim()); } else { q.append(" " + sub); } done = true; } else if (word.trim().equalsIgnoreCase("element") || word.trim().equalsIgnoreCase("limit") || word.trim().equalsIgnoreCase("desc") || word.trim().equalsIgnoreCase("asc")) { q.append(" " + sub); done = true; } else { word = word.replaceAll("'", "\'").trim(); //q.append(" (element='"+word.trim().replaceAll("'","")+"'"); q.append(" (_b.element='" + word + "'"); //col = word.trim().replaceAll("'",""); col = word; } } else if (seq == 2) { //word = word.replaceAll("'"," "); word = word.replaceAll("'", "\'"); if (id) { q.append("" + condition + "'" + word.trim() + "' and _a.id=_b.id "); } else { boolean valchanged = false; try { // we look for dates! if (col != null) { Method met = c.getMethod( "get" + col.substring(0, 1).toUpperCase() + col.substring(1), (Class[]) null); Class c1 = (Class) met.getGenericReturnType(); if (c1.getSimpleName().equalsIgnoreCase("XMLGregorianCalendar") && !word.isEmpty()) { //q.append(" and str_to_date(value,\"%Y-%m-%d\") "+condition+" '"+word.trim().replaceAll("'","")+"')"); q.append(" and str_to_date(_b.value,\"%Y-%m-%d\") " + condition + " '" + word.trim() + "')"); valchanged = true; } } } catch (Exception e) { e.printStackTrace(); } if (!valchanged) { //q.append(" and value "+condition+" '"+word.trim().replaceAll("'","")+"')"); q.append(" and _b.value " + condition + " '" + word.trim() + "')"); } col = null; } seq = 0; condition = null; id = false; } sub = sub.substring(wlen).trim(); if (x < 0 || sub.length() == 0) { done = true; } } // restructure query with parenthesis int i = q.toString().indexOf("_a.id=_b.id and"); if (i > 0) { String qf = q.toString(); qf = qf.substring(0, i + 16) + " (" + qf.substring(i + 17).trim() + ")"; q = new StringBuffer(qf); } } else { int tl = tablename.length(); q = new StringBuffer(query.substring(query.indexOf(tablename) + tl)); } PreparedStatement ps = null; boolean searchindex = false; System.out.println(">>>>111"); String stmt = "select distinct " + (hasid ? "_a" : "_b") + ".id from " + tablename.toLowerCase() + " _a, " + tablename.toLowerCase() + "_index _b " + q.toString(); System.out.println(">>>>" + stmt); ps = (PreparedStatement) conn.prepareStatement(stmt); if (!usedefaultimplementation) { //ps = (PreparedStatement) conn.prepareStatement("select distinct a.id from "+tablename.toLowerCase()+" a, "+tablename.toLowerCase()+"_index b "+q.toString()); } else { //ps = (PreparedStatement) conn.prepareStatement("select distinct a.id from "+tablename.toLowerCase()+"_index a"+q.toString()); searchindex = true; } if (_showsql) { System.out.println(this.getClass().getName() + " SQL Query:>" + ps.toString() + "<<"); } ResultSet rec = ps.executeQuery(); // check if a record was found while (rec != null && !rec.isClosed() && rec.next()) { String id = rec.getString("id"); try { Object o = null; PreparedStatement ps2 = (PreparedStatement) conn .prepareStatement("select id,parentid,content from " + tablename.toLowerCase() + " where id='" + id + "'"); ResultSet res = ps2.executeQuery(); // check if a record was found if (res != null && res.next()) { String i = res.getString("id"); String p = res.getString("parentid"); o = new FlexContainer(_flexutils.getObject(res.getString("content"), c)); ((FlexContainer) o).setId(i); ((FlexContainer) o).setParentId(p); ps2.close(); } else { ps2.close(); if (searchindex) { // then the values found must be orphans! we delete the index contents removeValues(id, tablename, conn); } } if (o != null) { v.add(o); Enumeration en = v.elements(); while (en.hasMoreElements()) { en.nextElement(); } } } catch (Exception g) { throw g; } } } } catch (Exception f) { throw f; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception g) { } } return v; } public Collection<Object> runQuery(String query, Class c, boolean usedefaultimplementation) throws Exception { Vector v = new Vector(); Connection conn = null; try { //System.out.println(">>>>orginal:["+query+"]"); conn = (Connection) _pool.getConnection(); String tablename = query.split("\\s")[3]; // always search the index! if (checkTable(tablename, conn, false)) { StringBuffer q = new StringBuffer("where "); if (query.toUpperCase().indexOf("WHERE") > 0) { String sub = query.substring(query.toUpperCase().indexOf("WHERE") + 5); sub = sub.replaceAll("<=", " <eq; "); sub = sub.replaceAll(">=", " >eq; "); sub = sub.replaceAll("<>", " &nteq; "); sub = sub.replaceAll("=", " = "); sub = sub.replaceAll(">", " > "); sub = sub.replaceAll("<", " < "); sub = sub.replaceAll("<eq;", "<="); sub = sub.replaceAll(">eq;", ">="); sub = sub.replaceAll("&nteq;", "<>").trim(); sub = sub.replaceAll("<", "<"); sub = sub.replaceAll(">", ">"); //System.out.println("from:"+sub); boolean done = false; boolean id = false; int seq = 0; String col = null; 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("w:"+word+"< "+wlen+" wl:"+word.length()); // 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("order")) { String[] order = sub.split("\\s"); if (!order[2].equalsIgnoreCase("id") && !order[2].equalsIgnoreCase("parentid")) { // get the 3rd word -- ie the field if (!q.toString().toUpperCase().endsWith("WHERE")) { q.append(" and "); } q.append(" (element='" + order[2] + "')"); q.append(" " + order[0] + " by value " + sub.substring(sub.indexOf(order[2]) + order[2].length()).trim()); } else { q.append(" " + sub); } done = true; } else if (word.trim().equalsIgnoreCase("element") || word.trim().equalsIgnoreCase("limit") || word.trim().equalsIgnoreCase("desc") || word.trim().equalsIgnoreCase("asc")) { q.append(" " + sub); done = true; } else { word = word.replaceAll("'", "\'").trim(); //q.append(" (element='"+word.trim().replaceAll("'","")+"'"); q.append(" (element='" + word + "'"); //col = word.trim().replaceAll("'",""); col = word; } } else if (seq == 2) { //word = word.replaceAll("'"," "); word = word.replaceAll("'", "\'"); if (id) { q.append("" + condition + "'" + word.trim() + "'"); } else { boolean valchanged = false; try { // we look for dates! if (col != null) { Method met = c.getMethod( "get" + col.substring(0, 1).toUpperCase() + col.substring(1), (Class[]) null); Class c1 = (Class) met.getGenericReturnType(); if (c1.getSimpleName().equalsIgnoreCase("XMLGregorianCalendar") && !word.isEmpty()) { //q.append(" and str_to_date(value,\"%Y-%m-%d\") "+condition+" '"+word.trim().replaceAll("'","")+"')"); q.append(" and str_to_date(value,\"%Y-%m-%d\") " + condition + " '" + word.trim() + "')"); valchanged = true; } } } catch (Exception e) { e.printStackTrace(); } if (!valchanged) { //q.append(" and value "+condition+" '"+word.trim().replaceAll("'","")+"')"); q.append(" and value " + condition + " '" + word.trim() + "')"); } col = null; } seq = 0; condition = null; id = false; } sub = sub.substring(wlen).trim(); if (x < 0 || sub.length() == 0) { done = true; } } } else { int tl = tablename.length(); q = new StringBuffer(query.substring(query.indexOf(tablename) + tl)); } PreparedStatement ps = null; boolean searchindex = false; if (!usedefaultimplementation) { ps = (PreparedStatement) conn.prepareStatement( "select distinct id from " + tablename.toLowerCase() + " " + q.toString()); } else { ps = (PreparedStatement) conn.prepareStatement( "select distinct id from " + tablename.toLowerCase() + "_index " + q.toString()); searchindex = true; } ResultSet rec = ps.executeQuery(); //System.out.println(">>>Query:["+ps.toString()+"]"); // check if a record was found while (rec != null && !rec.isClosed() && rec.next()) { String id = rec.getString("id"); String xml = null; try { Object o = null; PreparedStatement ps2 = (PreparedStatement) conn .prepareStatement("select id,parentid,content from " + tablename.toLowerCase() + " where id='" + id + "'"); ResultSet res = ps2.executeQuery(); // check if a record was found if (res != null && res.next()) { String i = res.getString("id"); String p = res.getString("parentid"); xml = res.getString("content"); o = new FlexContainer(_flexutils.getObject(FlexUtils.stripNonValidChars(xml), c)); ((FlexContainer) o).setId(i); ((FlexContainer) o).setParentId(p); ps2.close(); } else { ps2.close(); if (searchindex) { // then the values found must be orphans! we delete the index contents removeValues(id, tablename, conn); } } if (o != null) { v.add(o); Enumeration en = v.elements(); while (en.hasMoreElements()) { en.nextElement(); } } } catch (Exception g) { g.printStackTrace(); System.out.println(">>>FlexJAXDBDataEngine Error due to XML:" + xml); throw g; } } } } catch (Exception f) { 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) { 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; if (checkTable(tablename, conn, true)) { String id = ((FlexContainer) obj).getId(); String parentid = ((FlexContainer) obj).getParentId(); if (id == null) { id = getNewId(); FlexUtils.setId(id, (FlexContainer) obj); } if (parentid == null || parentid.isEmpty()) { //((FlexContainer)obj).setParentId(id); ((FlexContainer) obj).setParentId(""); // set it to empty! } PreparedStatement ps = (PreparedStatement) conn.prepareStatement( "insert into " + tablename.toLowerCase() + " (id,parentid,content) values (?,?,?)"); ps.setString(1, id); ps.setString(2, ((FlexContainer) obj).getParentId()); ps.setString(3, _flexutils.getXML(null, ((FlexContainer) obj).getObject(), false, false)); ps.executeUpdate(); ps.close(); // generate index indexValues(id, tablename, obj, conn); } 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; if (checkTable(tablename, conn, false)) { String id = ((FlexContainer) obj).getId(); PreparedStatement ps = (PreparedStatement) conn .prepareStatement("delete from " + tablename.toLowerCase() + " where id='" + id + "'"); ps.executeUpdate(); ps.close(); // update index removeValues(id, tablename, conn); success = true; } return success; } private Object refresh(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; if (checkTable(tablename, conn, false)) { 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; if (checkTable(tablename, conn, false)) { String id = ((FlexContainer) obj).getId(); Object obj2 = ((FlexContainer) obj).getObject(); // get old record in database first PreparedStatement ps = (PreparedStatement) conn .prepareStatement("select content from " + tablename.toLowerCase() + " where id='" + id + "'"); ResultSet res = ps.executeQuery(); // check if a record was found if (res != null && res.next()) { Hashtable h = _flexutils.getAvailableValues(res.getString("content")); // get available values in the original ps.close(); _flexutils.softTransfer(h, obj2); // flush the update ps = (PreparedStatement) conn.prepareStatement( "update " + tablename.toLowerCase() + " set content=? where id='" + id + "'"); ps.setString(1, _flexutils.getXML(obj2, h)); ps.executeUpdate(); ps.close(); // remove the old index removeValues(id, tablename, conn); // add them again indexValues(id, tablename, h, conn); } else { throw new ObjectNotFoundException( "update error: " + tablename.toLowerCase() + " with id:" + id + " does not exist."); } } return obj; } private void removeValues(String id, String tablename, Connection conn) throws Exception { try { PreparedStatement ps = (PreparedStatement) conn .prepareStatement("delete from " + tablename.toLowerCase() + "_index where id='" + id + "'"); ps.executeUpdate(); ps.close(); } catch (Exception f) { // just ignore index errors //f.printStackTrace(); } } private void indexValues(String id, String tablename, Object obj, Connection conn) throws Exception { Hashtable h = null; if (obj instanceof Hashtable) { h = (Hashtable) obj; } else { h = _flexutils.getAvailableValues(((FlexContainer) obj).getObject()); } Enumeration en = h.keys(); while (en.hasMoreElements()) { String k = (String) en.nextElement(); Element e = (Element) h.get(k); Object o = e.getContent(); if (o != null && !e.getType().equals("byte[]") && FlexUtils.indexable(e.getType())) { if (!k.equals("Id")) { try { String v = null; if (o instanceof Date) { //v = (new SimpleDateFormat(FlexUtils._dateformat)).format((Date)o); v = ((Date) o).getTime() + ""; } else { v = o.toString(); } //System.out.println(k+" "+v+" "+o); // we only index non-empty values? //if (v!=null && !v.isEmpty()) //{ PreparedStatement ps = (PreparedStatement) conn.prepareStatement("insert into " + tablename.toLowerCase() + "_index (id,element,value) values (?,?,?)"); ps.setString(1, id); ps.setString(2, k); ps.setString(3, (v.length() > 100) ? v.substring(0, 99) : v); // possible issues in truncation? ps.executeUpdate(); ps.close(); //} } catch (Exception f) { } } } } } private boolean checkTable(String tablename, Connection conn, boolean createifnotexist) throws Exception { boolean exists = false; // check if table exists in lookup if (_tables.containsKey(tablename.toLowerCase())) { exists = true; } else { exists = createTable(tablename.toLowerCase(), conn, createifnotexist); } return exists; } private synchronized boolean createTable(String tablename, Connection conn, boolean createifnotexist) throws Exception { boolean exists = false; tablename = tablename.toLowerCase(); if (_tables.containsKey(tablename)) { return true; } PreparedStatement ps = null; ResultSet res = null; // if not then check if it exists in db try { ps = (PreparedStatement) conn.prepareStatement("select id from " + tablename + " where id='0'"); res = ps.executeQuery(); } catch (Exception f) { //f.printStackTrace(); } // check if a record was found if (res != null) { ps.close(); _tables.put(tablename, ""); exists = true; } else { if (createifnotexist) { try { // if not then create ps = (PreparedStatement) conn .prepareStatement(_sqlstatements.get("table").replace("::table::", tablename)); ps.executeUpdate(); ps.close(); ps = (PreparedStatement) conn .prepareStatement(_sqlstatements.get("index").replace("::table::", tablename)); ps.executeUpdate(); ps.close(); } catch (Exception e) { //e.printStackTrace(); // if we cant create then it must already exist but is just empty? } _tables.put(tablename, ""); exists = true; } } return exists; } private synchronized String getNewId() { //return FlexUtils.generateRandomCode(10)+((new Date()).getTime()); return FlexUtils.generateKey(25); } // initialization public void initialize(Object conf) throws Exception { if (conf != null) { XMLConfiguration config = (XMLConfiguration) conf; _pool = new ConnectionPool(); int i = 0; _dbname = config.getString("flexoodb[@dbname]"); if (_dbname == null) { _dbname = "flexoodb"; } _showsql = config.getBoolean("flexoodb[@showsql]", false); // populate with defaults first _sqlstatements.put("table", "CREATE TABLE `::table::` (`id` varchar(25) NOT NULL default '' PRIMARY KEY,`parentid` varchar(25) NOT NULL default '',`content` LONGBLOB,KEY `parentid` (`parentid`)) ENGINE=InnoDB;"); _sqlstatements.put("index", "CREATE TABLE `::table::_index` (`id` varchar(25) NOT NULL default '',`element` varchar(45) NOT NULL default '',`value` varchar(100) NOT NULL default '',KEY `id` (`id`),KEY `element` (`element`),KEY `value` (`value`),KEY `elementandval` (`element`,`value`),KEY `idelementandval` (`id`,`element`,`value`)) ENGINE=InnoDB;"); _sqlstatements.put("drop", "DROP TABLE `::table::`;"); // then replace with alternatives if available List sqlstatements = config.getList("flexoodb.sql[@name]"); for (int j = 0; j < sqlstatements.size(); j++) { _sqlstatements.put(config.getString("flexoodb.sql(" + j + ")[@name]"), config.getString("flexoodb.sql(" + j + ")")); } //System.out.println("Connecting...."+config.getString("flexoodb[@odbcurl]")+" "+config.getString("flexoodb[@username]")+" "+config.getString("flexoodb[@password]")); 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(); } 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) { } } public boolean drop(Object obj) throws Exception { boolean success = false; Connection conn = null; try { String tablename = null; if (obj instanceof Class) { tablename = ((Class) obj).getSimpleName(); } else { tablename = (obj instanceof String) ? (String) obj : obj.getClass().getSimpleName(); } conn = (Connection) _pool.getConnection(); PreparedStatement ps = (PreparedStatement) conn .prepareStatement(_sqlstatements.get("drop").replace("::table::", tablename)); ps.executeUpdate(); ps.close(); ps = (PreparedStatement) conn .prepareStatement(_sqlstatements.get("drop").replace("::table::", tablename + "_index")); ps.executeUpdate(); ps.close(); success = true; } catch (Exception e) { throw e; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception f) { } } return success; } @Override public boolean reindex(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 { conn = (Connection) _pool.getConnection(); String tablename = query.substring(query.toLowerCase().indexOf("from") + 4).trim(); int x = tablename.indexOf(" "); tablename = x > -1 ? tablename.substring(0, x) : tablename; if (checkTable(tablename, conn, false)) { PreparedStatement ps = (PreparedStatement) conn.prepareStatement(query); ResultSet res = ps.executeQuery(); ret = new RecordSet(res); } } catch (Exception f) { 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; } public 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("<=", " <eq; "); sub = sub.replaceAll(">=", " >eq; "); sub = sub.replaceAll("=", " = "); sub = sub.replaceAll(">", " > "); sub = sub.replaceAll("<", " < ").trim(); sub = sub.replaceAll("<eq;", "<="); sub = sub.replaceAll(">eq;", ">="); 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("order")) { String[] order = sub.split("\\s"); if (!order[2].equalsIgnoreCase("id") && !order[2].equalsIgnoreCase("parentid")) { // get the 3rd word -- ie the field if (!q.toString().toUpperCase().endsWith("WHERE")) { q.append(" and "); } q.append(" (a.element='" + order[2] + "')"); q.append(" " + order[0] + " by a.value " + sub.substring(sub.indexOf(order[2]) + order[2].length()).trim()); } else { q.append(" " + sub); } done = 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(" (a.element='" + word.trim() + "'"); } } else if (seq == 2) { word = word.replaceAll("'", " "); if (id) { q.append("" + condition + "'" + word.trim() + "'"); } else { q.append(" and a.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); } static public void main(String[] args) throws Exception { FlexJAXBDBDataEngine e = new FlexJAXBDBDataEngine(); //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 * from _membertype where username = 'juan' and activationcode = 'juan_7F5123T25Jdu555736lX'"); //e.queryTest("select * from _inventorytype where name = 'Software'"); e.queryTest( "select * from _invoicetype where parentid = '2' and duedate >= '1990-01-01' and duedate <= '2001-01-01' or status='Active' order by score limit 0,10"); //e.queryTest("select * from _invoicetype where parentid = '2' order by duedate asc limit 0,10"); } }