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.common.Element; import com.flexoodb.common.FlexUtils; import com.flexoodb.common.ObjectNotFoundException; import com.flexoodb.pool.ConnectionPool; import com.flexoodb.pool.RecordSet; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; 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 data in 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 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.0 */ public class FlexDBDataEngine 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>(); @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 content from " + tablename + " where id='" + id + "'"); ResultSet res = ps.executeQuery(); // check if a record was found if (res != null && res.next()) { obj = _flexutils.getObject(res.getString("content"), c); } ps.close(); } } catch (Exception f) { throw f; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception g) { } } return obj; } public Collection<Object> runQuery(String query, Class c) throws Exception { return runQuery(query, c, false); } public Collection<Object> runQuery(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]; if (checkTable(tablename, conn, false)) { StringBuffer q = new StringBuffer(); boolean idonly = true; if (query.toUpperCase().indexOf("WHERE") > 0) { String sub = query.substring(query.toUpperCase().indexOf("WHERE")); String[] s = (FlexUtils.replaceString(sub, "'", "")).split("\\s"); for (int i = 0; i < s.length; i++) { String[] qa = s[i].split("="); if (qa.length == 2) { if (qa[0].equals("id")) { q.append("(id='" + qa[1] + "')"); } else { String e = qa[0]; if (e.startsWith("(")) { q.append("("); e = FlexUtils.replaceString(FlexUtils.replaceString(e, "(", ""), ")", ""); } String val = qa[1]; boolean closeit = false; if (val.endsWith(")")) { closeit = true; val = FlexUtils.replaceString(FlexUtils.replaceString(val, "(", ""), ")", ""); } q.append("(element='" + e + "' and value='" + val + "')" + (closeit ? ")" : "")); idonly = false; } } else { if (qa.length == 1) { q.append(" " + qa[0] + " "); } } } } PreparedStatement ps = null; boolean searchindex = false; if (idonly) { ps = (PreparedStatement) conn .prepareStatement("select distinct id from " + tablename + " " + q.toString()); } else { ps = (PreparedStatement) conn .prepareStatement("select distinct id from " + tablename + "_index " + q.toString()); searchindex = true; } 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 content from " + tablename + " where id='" + id + "'"); ResultSet res = ps2.executeQuery(); // check if a record was found if (res != null && res.next()) { o = _flexutils.getObject(res.getString("content"), c); 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); } } catch (Exception g) { throw g; } } } } catch (Exception f) { 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 ? obj.getClass().getSimpleName() : targettable; if (checkTable(tablename, conn, true)) { String id = getNewId(); FlexUtils.setId(id, obj); PreparedStatement ps = (PreparedStatement) conn .prepareStatement("insert into " + tablename + " (id,content) values (?,?)"); ps.setString(1, id); ps.setString(2, _flexutils.getXML(null, obj, true, 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 ? obj.getClass().getSimpleName() : targettable; if (checkTable(tablename, conn, false)) { String id = FlexUtils.getId(obj); PreparedStatement ps = (PreparedStatement) conn .prepareStatement("delete from " + tablename + " 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 ? obj.getClass().getSimpleName() : targettable; if (checkTable(tablename, conn, false)) { String id = FlexUtils.getId(obj); Object obj2 = find(id, obj.getClass()); if (obj2 != null) { _flexutils.softTransfer(_flexutils.getAvailableValues(obj2), obj); } else { throw new ObjectNotFoundException(id + " " + obj.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 ? obj.getClass().getSimpleName() : targettable; if (checkTable(tablename, conn, false)) { String id = FlexUtils.getId(obj); // get old record in database first PreparedStatement ps = (PreparedStatement) conn .prepareStatement("select content from " + tablename + " 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, obj); // flush the update ps = (PreparedStatement) conn .prepareStatement("update " + tablename + " set content=? where id='" + id + "'"); ps.setString(1, _flexutils.getXML(obj, 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 + " 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 + "_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(obj); } Enumeration en = h.keys(); while (en.hasMoreElements()) { String k = (String) en.nextElement(); Object o = ((Element) h.get(k)).getContent(); if (o != null && FlexUtils.indexable(o.getClass().getSimpleName())) { if (!k.equals("Id")) { try { String v = null; if (o instanceof Date) { v = (new SimpleDateFormat(FlexUtils._dateformat)).format((Date) o); } else { v = o.toString(); } PreparedStatement ps = (PreparedStatement) conn.prepareStatement( "insert into " + tablename + "_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; 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) { // 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()); } // 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"; } // populate with defaults first _sqlstatements.put("table", "CREATE TABLE `::table::` (`id` varchar(25) NOT NULL default '' PRIMARY KEY,`content` LONGBLOB) 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 + ")")); } 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) { 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; } 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(); 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; } }