Java tutorial
/* (c) 2015 Open Source Geospatial Foundation - all rights reserved * This code is licensed under the GPL 2.0 license, available at the root * application directory. */ package org.geoserver.jdbcstore.internal; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import javax.sql.DataSource; import org.apache.commons.io.input.ProxyInputStream; import org.geoserver.jdbcconfig.internal.Util; import org.geoserver.platform.resource.Resource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; /** * Convencience classes and methods for JDBC Access and building queries. * * @author Kevin Smith, Boundless * @author Niels Charlier * */ public class JDBCQueryHelper { private static final Logger LOGGER = org.geotools.util.logging.Logging.getLogger(JDBCQueryHelper.class); /** * * Query DataType * * * @param <T> */ public static interface Type<T> { void setValue(PreparedStatement s, int index, T value) throws SQLException; T getValue(ResultSet rs, String colName) throws SQLException; } public final static Type<Integer> TYPE_INT = new Type<Integer>() { @Override public void setValue(PreparedStatement s, int index, Integer value) throws SQLException { s.setInt(index, value); } @Override public Integer getValue(ResultSet rs, String colName) throws SQLException { int i = rs.getInt(colName); if (rs.wasNull()) return null; return i; } }; public final static Type<Boolean> TYPE_BOOLEAN = new Type<Boolean>() { @Override public void setValue(PreparedStatement s, int index, Boolean value) throws SQLException { s.setInt(index, index); } @Override public Boolean getValue(ResultSet rs, String colName) throws SQLException { boolean b = rs.getBoolean(colName); if (rs.wasNull()) return null; return b; } }; public final static Type<String> TYPE_STRING = new Type<String>() { @Override public void setValue(PreparedStatement s, int index, String value) throws SQLException { s.setString(index, value); } @Override public String getValue(ResultSet rs, String colName) throws SQLException { return rs.getString(colName); } }; public final static Type<Timestamp> TYPE_TIMESTAMP = new Type<Timestamp>() { @Override public void setValue(PreparedStatement s, int index, Timestamp value) throws SQLException { s.setTimestamp(index, value); } @Override public Timestamp getValue(ResultSet rs, String colName) throws SQLException { return rs.getTimestamp(colName); } }; public final static Type<InputStream> TYPE_BLOB = new Type<InputStream>() { @Override public void setValue(PreparedStatement s, int index, InputStream value) throws SQLException { s.setBinaryStream(index, value); } @Override public InputStream getValue(ResultSet rs, String colName) throws SQLException { return rs.getBinaryStream(colName); } }; /** * * Query Field. * * @param <T> */ public static class Field<T> { private final String fieldName; private final String fieldExpression; private final Type<T> type; public Field(String fieldName, String fieldExpression, Type<T> type) { super(); this.fieldName = fieldName; this.fieldExpression = fieldExpression; this.type = type; } public String getFieldName() { return fieldName; } public String getFieldExpression() { return fieldExpression; } public Type<T> getType() { return type; } public T getValue(ResultSet rs) throws SQLException { return type.getValue(rs, fieldName); } } /** * * Query parameter. * * @param <T> */ public static class Parameter<T> { private final Type<T> type; private final T value; public Parameter(Type<T> type, T value) { this.type = type; this.value = value; } public Type<T> getType() { return type; } public T getValue() { return value; } public void setValue(PreparedStatement s, int index) throws SQLException { type.setValue(s, index, value); } } /** * Builds a prepared statement from a query * */ public static class QueryBuilder { private StringBuilder stringBuilder = new StringBuilder(); private ArrayList<Parameter<?>> parameters = new ArrayList<Parameter<?>>(); public QueryBuilder() { } public QueryBuilder(String string) { stringBuilder.append(string); } public void append(String s) { stringBuilder.append(s); } public void addParameter(Parameter<?> parameter) { parameters.add(parameter); } private void parameters(PreparedStatement ps) throws SQLException { for (int i = 0; i < parameters.size(); i++) { parameters.get(i).setValue(ps, i + 1); } } public PreparedStatement toStatement(Connection c) throws SQLException { PreparedStatement ps = c.prepareStatement(stringBuilder.toString()); try { parameters(ps); return ps; } catch (SQLException e) { ps.close(); throw e; } } public PreparedStatement toStatement(Connection c, int autoGeneratedKeys) throws SQLException { PreparedStatement ps = c.prepareStatement(stringBuilder.toString(), autoGeneratedKeys); try { parameters(ps); return ps; } catch (SQLException e) { ps.close(); throw e; } } public String toString() { StringBuilder sb = new StringBuilder(stringBuilder.toString()); for (Parameter<?> pam : parameters) { sb.append(": " + pam.getValue()); } return sb.toString(); } } /** * * Different ways to provide filter part of the query. * */ public static interface Selector { QueryBuilder appendCondition(QueryBuilder qb); } /** * * Filter or field. * */ public static class FieldSelector<T> implements Selector { private final Field<T> field; private final T value; FieldSelector(Field<T> field, T value) { this.field = field; this.value = value; } @Override public QueryBuilder appendCondition(QueryBuilder sb) { sb.append(field.getFieldExpression() + " = ?"); sb.addParameter(new Parameter<T>(field.getType(), value)); return sb; } } /** * Provides an assignment for an update query. * * @param <T> */ protected static class Assignment<T> { private final Field<T> field; private final T value; public Assignment(Field<T> field, T value) { this.field = field; this.value = value; } public Field<T> getField() { return field; } public T getValue() { return value; } public QueryBuilder appendAssignment(QueryBuilder sb) { sb.append(field.getFieldName() + " = ? "); return addAsParameter(sb); } public QueryBuilder addAsParameter(QueryBuilder sb) { sb.addParameter(new Parameter<T>(field.getType(), value)); return sb; } } private final DataSource ds; public JDBCQueryHelper(DataSource ds) { this.ds = ds; } private QueryBuilder createSelect(String table, Selector sel, Field<?>... fields) { QueryBuilder builder = new QueryBuilder(); builder.append("SELECT "); { int i = 0; for (Field<?> field : fields) { if (i++ > 0) builder.append(", "); builder.append(field.fieldExpression); } } builder.append(" FROM " + table + " WHERE "); sel.appendCondition(builder); builder.append(";"); return builder; } public Map<String, Object> selectQuery(String table, Selector sel, Field<?>... fields) { return anyQuery(createSelect(table, sel, fields), fields); } public List<Map<String, Object>> multiSelectQuery(String table, Selector sel, Field<?>... fields) { return anyMultiQuery(createSelect(table, sel, fields), fields); } public InputStream blobQuery(String table, Selector sel, Field<InputStream> field) { return anyBlobQuery(createSelect(table, sel, field), field); } public int deleteQuery(String table, Selector sel) { QueryBuilder builder = new QueryBuilder(); builder.append("DELETE FROM " + table + " WHERE "); sel.appendCondition(builder); builder.append(";"); return anyUpdateQuery(builder); } public int updateQuery(String table, Selector sel, Assignment<?>... assignments) { QueryBuilder builder = new QueryBuilder(); builder.append("UPDATE " + table + " SET "); { int i = 0; for (Assignment<?> assign : assignments) { if (i++ > 0) builder.append(", "); assign.appendAssignment(builder); } } builder.append(" WHERE "); sel.appendCondition(builder); builder.append(";"); return anyUpdateQuery(builder); } public Integer insertQuery(String table, Assignment<?>... assignments) { QueryBuilder builder = new QueryBuilder(); builder.append("INSERT INTO " + table + "( "); { int i = 0; for (Assignment<?> assign : assignments) { if (i++ > 0) builder.append(", "); builder.append(assign.getField().getFieldName()); } } builder.append(") VALUES ( "); { int i = 0; for (Assignment<?> assign : assignments) { if (i++ > 0) builder.append(", "); builder.append("?"); assign.addAsParameter(builder); } } builder.append(");"); List<Integer> result = anyInsertQuery(builder); assert (result == null || result.size() == 1); return result == null ? null : result.get(0); } public List<Map<String, Object>> anyMultiQuery(QueryBuilder query, Field<?>... fields) { LOGGER.log(Level.FINEST, query.toString()); try (Connection c = ds.getConnection()) { try (PreparedStatement stmt = query.toStatement(c)) { try (ResultSet rs = stmt.executeQuery()) { ArrayList<Map<String, Object>> results = new ArrayList<Map<String, Object>>(); while (rs.next()) { Map<String, Object> result = new HashMap<String, Object>(); for (int i = 0; i < fields.length; i++) { result.put(fields[i].getFieldName(), fields[i].getValue(rs)); } results.add(result); } return results; } } } catch (SQLException ex) { throw new IllegalStateException("MultiSelectQuery Failed", ex); } } public Map<String, Object> anyQuery(QueryBuilder query, Field<?>... fields) { LOGGER.log(Level.FINEST, query.toString()); try (Connection c = ds.getConnection()) { try (PreparedStatement stmt = query.toStatement(c)) { try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) { assert (rs.isLast()); Map<String, Object> result = new HashMap<String, Object>(); for (int i = 0; i < fields.length; i++) { result.put(fields[i].getFieldName(), fields[i].getValue(rs)); } return result; } else { return null; } } } } catch (SQLException ex) { throw new IllegalStateException("SelectQuery Failed", ex); } } public int anyUpdateQuery(QueryBuilder query) { LOGGER.log(Level.FINEST, query.toString()); try (Connection c = ds.getConnection()) { try (PreparedStatement stmt = query.toStatement(c)) { return stmt.executeUpdate(); } } catch (SQLException ex) { throw new IllegalStateException("UpdateQuery Failed", ex); } } public List<Integer> anyInsertQuery(QueryBuilder query) { LOGGER.log(Level.FINEST, query.toString()); try (Connection c = ds.getConnection()) { try (PreparedStatement stmt = query.toStatement(c, Statement.RETURN_GENERATED_KEYS)) { if (stmt.executeUpdate() <= 0) { return null; } try (ResultSet rs = stmt.getGeneratedKeys()) { List<Integer> list = new ArrayList<Integer>(); while (rs.next()) { list.add(rs.getInt(1)); } return list; } } } catch (SQLException ex) { throw new IllegalStateException("InsertQuery Failed", ex); } } /** * * Blobs should always be queried with this method, not with the regular selects! * */ public InputStream anyBlobQuery(QueryBuilder query, Field<InputStream> field) { LOGGER.log(Level.FINEST, query.toString()); Connection c; boolean closeConnection = false; try { c = ds.getConnection(); } catch (SQLException ex) { throw new IllegalStateException("Could not connect to DataSource.", ex); } try { try (PreparedStatement stmt = query.toStatement(c)) { try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) { assert (rs.isLast()); InputStream is = field.getValue(rs); return is == null ? null : new ClosingInputStreamWrapper(is, c); } else { closeConnection = true; return null; } } } } catch (SQLException ex) { throw new IllegalStateException("BlobQuery Failed", ex); } finally { if (closeConnection) { try { c.close(); } catch (SQLException ex) { throw new IllegalArgumentException("Error while closing connection.", ex); } } } } protected void runScript(Resource script) { NamedParameterJdbcOperations template = new NamedParameterJdbcTemplate(ds); try (InputStream in = script.in()) { Util.runScript(in, template.getJdbcOperations(), null); } catch (IOException ex) { throw new IllegalArgumentException("Could not execute provided sql script", ex); } } /** * * Ensures the connection is closed after returned inputstream of BLOB is closed. * */ static protected class ClosingInputStreamWrapper extends ProxyInputStream { Connection conn; public ClosingInputStreamWrapper(InputStream proxy, Connection conn) { super(proxy); this.conn = conn; } @Override public void close() throws IOException { try { super.close(); } finally { try { conn.close(); } catch (SQLException ex) { throw new IOException("Exception while closing connection", ex); } } } } }