Java tutorial
/********************************************************************************* * The contents of this file are subject to the Common Public Attribution * License Version 1.0 (the "License"); you may not use this file except in * compliance with the License. You may obtain a copy of the License at * http://www.openemm.org/cpal1.html. The License is based on the Mozilla * Public License Version 1.1 but Sections 14 and 15 have been added to cover * use of software over a computer network and provide for limited attribution * for the Original Developer. In addition, Exhibit A has been modified to be * consistent with Exhibit B. * Software distributed under the License is distributed on an "AS IS" basis, * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for * the specific language governing rights and limitations under the License. * * The Original Code is OpenEMM. * The Original Developer is the Initial Developer. * The Initial Developer of the Original Code is AGNITAS AG. All portions of * the code written by AGNITAS AG are Copyright (c) 2007 AGNITAS AG. All Rights * Reserved. * * Contributor(s): AGNITAS AG. ********************************************************************************/ package org.agnitas.backend; import java.sql.Blob; import java.sql.Clob; import java.util.Date; import java.sql.Timestamp; import java.sql.SQLException; import javax.sql.DataSource; import java.util.ArrayList; import java.util.Map; import java.util.List; import java.util.HashMap; import java.util.HashSet; import org.springframework.jdbc.datasource.DriverManagerDataSource; import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; import org.springframework.jdbc.core.JdbcOperations; import org.apache.commons.dbcp.ConnectionFactory; import org.apache.commons.dbcp.PoolingDataSource; import org.apache.commons.dbcp.PoolableConnectionFactory; import org.apache.commons.dbcp.DriverManagerConnectionFactory; import org.apache.commons.pool.ObjectPool; import org.apache.commons.pool.impl.GenericObjectPool; import org.apache.commons.pool.KeyedObjectPoolFactory; import org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory; import org.apache.log4j.BasicConfigurator; import org.apache.log4j.Appender; import org.apache.log4j.AppenderSkeleton; import org.apache.log4j.spi.Filter; import org.apache.log4j.spi.LoggingEvent; import org.apache.log4j.Level; import org.agnitas.util.Log; /** Database abstraction layer */ public class DBase { public final int DB_UNSET = 0; public final int DB_MYSQL = 1; public final int DB_ORACLE = 2; /** name for current date in database */ public int dbType = DB_UNSET; public String sysdate = null; public String timestamp = null; public String measureType = null; public String measureRepr = null; /** Reference to configuration */ private Data data = null; /** Reference to data source */ protected DataSource dataSource = null; /** Default jdbc access instance */ private SimpleJdbcTemplate jdbcTmpl = null; /** Collection of free connections */ private ArrayList<SimpleJdbcTemplate> pool = null; static class DBaseFilter extends Filter { @Override public int decide(LoggingEvent e) { Level l = e.getLevel(); if ((l == Level.WARN) || (l == Level.ERROR) || (l == Level.FATAL)) { return Filter.ACCEPT; } return Filter.NEUTRAL; } } static class DBaseAppender extends AppenderSkeleton { private Log log; public DBaseAppender(Log nLog) { super(); log = nLog; } @Override public boolean requiresLayout() { return false; } @Override public void close() { } @Override protected void append(LoggingEvent e) { Level l = e.getLevel(); int lvl = -1; if (l == Level.WARN) { lvl = Log.WARNING; } else if (l == Level.ERROR) { lvl = Log.ERROR; } else if (l == Level.FATAL) { lvl = Log.FATAL; } if (lvl != -1) { String name = e.getLoggerName(); if ((name == null) || name.startsWith("org.springframework.jdbc")) { log.out(lvl, "jdbc", e.getRenderedMessage()); } } } } static class DBDatasource { private HashMap<String, DataSource> cache; private HashSet<String> seen; private Log log; public DBDatasource() { cache = new HashMap<String, DataSource>(); seen = new HashSet<String>(); log = new Log("jdbc", Log.INFO); Appender app = new DBaseAppender(log); app.addFilter(new DBaseFilter()); BasicConfigurator.configure(app); } public DataSource newDataSource(String driver, String connect, String login, String password) { return new DriverManagerDataSource(connect, login, password); } public synchronized DataSource request(String driver, String connect, String login, String password) throws ClassNotFoundException { DataSource rc; String key = driver + ";" + connect + ";" + login + ";*"; ArrayList<DataSource> cur; if (cache.containsKey(key)) { rc = cache.get(key); log.out(Log.DEBUG, "rq", "Got exitsing DS for " + key); } else { if (!seen.contains(driver)) { try { Class.forName(driver); seen.add(driver); log.out(Log.DEBUG, "rq", "Installed new driver for " + driver); } catch (ClassNotFoundException e) { log.out(Log.ERROR, "rq", "Failed to install driver " + driver); throw e; } } rc = newDataSource(driver, connect, login, password); cache.put(key, rc); log.out(Log.DEBUG, "rq", "Created new DS for " + key); } return rc; } } static class DBDatasourcePooled extends DBDatasource { private int dsPoolsize = 12; private boolean dsPoolgrow = true; public void setup(int poolsize, boolean poolgrow) { dsPoolsize = poolsize; dsPoolgrow = poolgrow; } public DataSource newDataSource(String driver, String connect, String login, String password) { ObjectPool connectionPool = new GenericObjectPool(null, dsPoolsize, (dsPoolgrow ? GenericObjectPool.WHEN_EXHAUSTED_GROW : GenericObjectPool.WHEN_EXHAUSTED_BLOCK), 0); ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connect, login, password); // KeyedObjectPoolFactory statementPoolFactory = new GenericKeyedObjectPoolFactory (null); PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null /*statementPoolFactory*/, null, false, true); return new PoolingDataSource(connectionPool); } } private static DBDatasourcePooled dsPool = new DBDatasourcePooled(); public DBase(Data nData) throws Exception { data = nData; dsPool.setup(data.dbPoolsize(), data.dbPoolgrow()); dataSource = dsPool.request(data.dbDriver(), data.dbConnect(), data.dbLogin(), data.dbPassword()); jdbcTmpl = new SimpleJdbcTemplate(dataSource); pool = new ArrayList<SimpleJdbcTemplate>(); } public void setup() throws Exception { dbType = DB_MYSQL; sysdate = "current_timestamp"; timestamp = "change_date"; measureType = "MEASURE_TYPE"; measureRepr = "MEASURE_TYPE"; } /** * Cleanup, close open statements and database connection */ public void done() throws Exception { pool.clear(); pool = null; jdbcTmpl = null; dataSource = null; } private void show(String what, String query, HashMap<String, Object> param) { if ((query != null) && data.islog(Log.DEBUG)) { String m = what + ": " + query; if ((param != null) && (param.size() > 0)) { String sep = " { "; for (String key : param.keySet()) { Object val = param.get(key); String disp; if (val == null) { disp = "null"; } else { try { Class cls = val.getClass(); if ((cls == String.class) || (cls == StringBuffer.class)) { disp = "\"" + val.toString() + "\""; } else if (cls == Character.class) { disp = "'" + val.toString() + "'"; } else if (cls == Boolean.class) { disp = ((Boolean) val).booleanValue() ? "true" : "false"; } else { disp = val.toString(); } } catch (Exception e) { disp = "???"; } } m += sep + key + "=" + disp; sep = ", "; } m += " }"; } data.logging(Log.DEBUG, "dbase", m); } } public SimpleJdbcTemplate jdbc() { return jdbcTmpl; } public SimpleJdbcTemplate jdbc(String query, HashMap<String, Object> param) { show("DB", query, param); return jdbc(); } public SimpleJdbcTemplate jdbc(String query) { return jdbc(query, null); } public JdbcOperations op() { return jdbc().getJdbcOperations(); } public JdbcOperations op(String query, HashMap<String, Object> param) { show("OP", query, param); return op(); } public JdbcOperations op(String query) { return op(query, null); } public SimpleJdbcTemplate request() { SimpleJdbcTemplate temp; if (pool.isEmpty()) { temp = new SimpleJdbcTemplate(dataSource); } else { temp = pool.remove(0); } return temp; } public SimpleJdbcTemplate request(String query, HashMap<String, Object> param) { show("RQ", query, param); return request(); } public SimpleJdbcTemplate request(String query) { return request(query, null); } public SimpleJdbcTemplate release(SimpleJdbcTemplate temp) { if ((temp != null) && (!pool.contains(temp))) { pool.add(temp); } return null; } public SimpleJdbcTemplate release(SimpleJdbcTemplate temp, String query, HashMap<String, Object> param) { show("RL", query, param); return release(temp); } public SimpleJdbcTemplate release(SimpleJdbcTemplate temp, String query) { return release(temp, query, null); } private HashMap<String, Object> pack(Object[] param) { HashMap<String, Object> input = new HashMap<String, Object>(param.length / 2); for (int n = 0; n < param.length; n += 2) { input.put((String) param[n], param[n + 1]); } return input; } private Exception failure(String q, Exception e) { data.logging(Log.ERROR, "dbase", "DB Failed: " + q + ": " + e.toString()); return e; } private int doQueryInt(SimpleJdbcTemplate jdbc, String q, HashMap<String, Object> packed) throws Exception { try { return jdbc.queryForInt(q, packed); } catch (Exception e) { throw failure(q, e); } } public int queryInt(SimpleJdbcTemplate jdbc, String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQueryInt(jdbc, q, packed); } public int queryInt(String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQueryInt(jdbc(q, packed), q, packed); } private long doQueryLong(SimpleJdbcTemplate jdbc, String q, HashMap<String, Object> packed) throws Exception { try { return jdbc.queryForLong(q, packed); } catch (Exception e) { throw failure(q, e); } } public long queryLong(SimpleJdbcTemplate jdbc, String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQueryLong(jdbc, q, packed); } public long queryLong(String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQueryLong(jdbc(q, packed), q, packed); } private String doQueryString(SimpleJdbcTemplate jdbc, String q, HashMap<String, Object> packed) throws Exception { try { return jdbc.queryForObject(q, String.class, packed); } catch (Exception e) { throw failure(q, e); } } public String queryString(SimpleJdbcTemplate jdbc, String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQueryString(jdbc, q, packed); } public String queryString(String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQueryString(jdbc(q, packed), q, packed); } private Map<String, Object> doQuerys(SimpleJdbcTemplate jdbc, String q, HashMap<String, Object> packed) throws Exception { try { return jdbc.queryForMap(q, packed); } catch (Exception e) { throw failure(q, e); } } public Map<String, Object> querys(SimpleJdbcTemplate jdbc, String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQuerys(jdbc, q, packed); } public Map<String, Object> querys(String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQuerys(jdbc(q, packed), q, packed); } private List<Map<String, Object>> doQuery(SimpleJdbcTemplate jdbc, String q, HashMap<String, Object> packed) throws Exception { try { return jdbc.queryForList(q, packed); } catch (Exception e) { throw failure(q, e); } } public List<Map<String, Object>> query(SimpleJdbcTemplate jdbc, String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQuery(jdbc, q, packed); } public List<Map<String, Object>> query(String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doQuery(jdbc(q, packed), q, packed); } private int doUpdate(SimpleJdbcTemplate jdbc, String q, HashMap<String, Object> packed) throws Exception { try { return jdbc.update(q, packed); } catch (Exception e) { throw failure(q, e); } } public int update(SimpleJdbcTemplate jdbc, String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doUpdate(jdbc, q, packed); } public int update(String q, Object... param) throws Exception { HashMap<String, Object> packed = pack(param); return doUpdate(jdbc(q, packed), q, packed); } private void doExecute(SimpleJdbcTemplate jdbc, String q) throws Exception { try { jdbc.getJdbcOperations().execute(q); } catch (Exception e) { throw failure(q, e); } } public void execute(SimpleJdbcTemplate jdbc, String q) throws Exception { doExecute(jdbc, q); } public void execute(String q) throws Exception { doExecute(jdbc(q), q); } /** * Check the string for a minimum length or not all spaces, * otherwise set it to null * @param s the string to validate * @param minLength the minimal length required for the string * @return the modified string */ public String validate(String s, int minLength) { if (s != null) { int len = s.length(); if (len < minLength) { s = null; } else { int n; for (n = 0; n < len; ++n) { if (s.charAt(n) != ' ') { break; } } if (n == len) { s = null; } } } return s; } public String validate(String s) { return validate(s, 1); } public int asInt(Object o, int ifNull) { return o != null ? ((Number) o).intValue() : ifNull; } public int asInt(Object o) { return asInt(o, 0); } public long asLong(Object o, long ifNull) { return o != null ? ((Number) o).longValue() : ifNull; } public long asLong(Object o) { return asLong(o, 0L); } public String asString(Object o, int minLength, String ifNull) { String s = validate((String) o, minLength); return s != null ? s : ifNull; } public String asString(Object o, int minLength) { return asString(o, minLength, null); } public String asString(Object o, String ifNull) { return asString(o, 1, ifNull); } public String asString(Object o) { return asString(o, 1, null); } public String asClob(Object o) { if (o == null) { return null; } else if (o.getClass() == String.class) { return (String) o; } else { Clob clob = (Clob) o; try { return clob == null ? null : clob.getSubString(1, (int) clob.length()); } catch (SQLException e) { failure("clob parse", e); } return null; } } public byte[] asBlob(Object o) { if (o == null) { return null; } else if (o.getClass().getName().equals("[B")) { return (byte[]) o; } else { Blob blob = (Blob) o; try { return blob == null ? null : blob.getBytes(1, (int) blob.length()); } catch (SQLException e) { failure("blob parse", e); } return null; } } public Date asDate(Object o, Date ifNull) { return o != null ? (Date) o : ifNull; } public Date asDate(Object o) { return asDate(o, null); } public Timestamp asTimestamp(Object o, Timestamp ifNull) { return o != null ? (Timestamp) o : ifNull; } public Timestamp asTimestamp(Object o) { return asTimestamp(o, null); } }