Java tutorial
/* * Copyright (C) 2014 SOP4J * * Licensed under the Apache License, Version 2.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.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.sop4j.dbutils; import java.lang.reflect.InvocationTargetException; import java.sql.Connection; import java.sql.SQLException; import java.util.Collection; import java.util.Collections; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set; import javax.persistence.Entity; import javax.sql.DataSource; import org.apache.commons.beanutils.PropertyUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.sop4j.dbutils.handlers.BeanHandler; import com.sop4j.dbutils.handlers.BeanListHandler; /** * Executes SQL queries with pluggable strategies for handling * <code>ResultSet</code>s. This class is thread safe. * * @see ResultSetHandler */ public class QueryRunner { private static final Logger LOG = LoggerFactory.getLogger(QueryRunner.class); /** * The DataSource to retrieve connections from. */ private final DataSource ds; /** * Constructor for QueryRunner. */ public QueryRunner() { ds = null; } /** * Constructor for QueryRunner that takes a <code>DataSource</code> to use. * * Methods that do not take a <code>Connection</code> parameter will retrieve connections from this * <code>DataSource</code>. * * @param ds The <code>DataSource</code> to retrieve connections from. */ public QueryRunner(final DataSource ds) { this.ds = ds; } /** * Returns the <code>DataSource</code> this runner is using. * <code>QueryRunner</code> methods always call this method to get the * <code>DataSource</code> so subclasses can provide specialized behavior. * * @return DataSource the runner is using */ public DataSource getDataSource() { return this.ds; } /** * Factory method that creates and initializes a <code>Connection</code> * object. <code>QueryRunner</code> methods always call this method to * retrieve connections from its DataSource. Subclasses can override this * method to provide special <code>Connection</code> configuration if * needed. This implementation simply calls <code>ds.getConnection()</code>. * * @return An initialized <code>Connection</code>. * @throws SQLException if a database access error occurs */ protected Connection prepareConnection() throws SQLException { if (this.getDataSource() == null) { throw new SQLException("QueryRunner requires a DataSource to be " + "invoked in this way, or a Connection should be passed in"); } return this.getDataSource().getConnection(); } /** * Close a <code>Connection</code>. This implementation avoids closing if * null and does <strong>not</strong> suppress any exceptions. Subclasses * can override to provide special handling like logging. * * @param conn Connection to close * @throws SQLException if a database access error occurs */ private void close(Connection conn) throws SQLException { DbUtils.close(conn); } /** * Creates an {@link BatchExecutor} for the given SQL. * <code>Connection</code> is retrieved from the <code>DataSource</code> * set in the constructor. This <code>Connection</code> must be in * auto-commit mode or the insert will not be saved. The <code>Connection</code> is * closed after the call. * * @param sql The SQL statement to execute. * * @return An {@link BatchExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public BatchExecutor batch(String sql) throws SQLException { return this.batch(this.prepareConnection(), true, sql); } /** * Creates an {@link BatchExecutor} for the given SQL statement and connection. * The connection is <b>NOT</b> closed after execution. * * @param conn The connection to use for the batch call. * @param sql The SQL statement to execute. * * @return An {@link BatchExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public BatchExecutor batch(Connection conn, String sql) throws SQLException { return this.batch(conn, true, sql); } /** * Creates an {@link BatchExecutor} for the given SQL statement and connection. * * @param conn The connection to use for the batch call. * @param closeConn True if the connection should be closed, false otherwise. * @param sql The SQL statement to execute. * * @return An {@link BatchExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public BatchExecutor batch(Connection conn, boolean closeConn, String sql) throws SQLException { if (conn == null) { throw new SQLException("Null connection"); } if (sql == null) { if (closeConn) { close(conn); } throw new SQLException("Null SQL statement"); } return new BatchExecutor(conn, sql, closeConn); } /** * Creates an {@link QueryExecutor} for the given SQL. * <code>Connection</code> is retrieved from the <code>DataSource</code> * set in the constructor. This <code>Connection</code> must be in * auto-commit mode or the insert will not be saved. The <code>Connection</code> is * closed after the call. * * @param sql The SQL statement to execute. * * @return A {@link QueryExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public QueryExecutor query(String sql) throws SQLException { return this.query(this.prepareConnection(), true, sql); } /** * Creates an {@link QueryExecutor} for the given SQL statement and connection. * The connection is <b>NOT</b> closed after execution. * * @param conn The connection to use for the update call. * @param sql The SQL statement to execute. * * @return An {@link QueryExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public QueryExecutor query(Connection conn, String sql) throws SQLException { return this.query(conn, false, sql); } /** * Creates an {@link QueryExecutor} for the given SQL statement and connection. * * @param conn The connection to use for the query call. * @param closeConn True if the connection should be closed, false otherwise. * @param sql The SQL statement to execute. * * @return An {@link QueryExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public QueryExecutor query(Connection conn, boolean closeConn, String sql) throws SQLException { if (conn == null) { throw new SQLException("Null connection"); } if (sql == null) { if (closeConn) { close(conn); } throw new SQLException("Null SQL statement"); } return new QueryExecutor(conn, sql, closeConn); } /** * Creates an {@link UpdateExecutor} for the given SQL. * <code>Connection</code> is retrieved from the <code>DataSource</code> * set in the constructor. This <code>Connection</code> must be in * auto-commit mode or the insert will not be saved. The <code>Connection</code> is * closed after the call. * * @param sql The SQL statement to execute. * * @return An {@link UpdateExecutor} for this SQL statement. * @throws SQLException if a database access error occurs */ public UpdateExecutor update(String sql) throws SQLException { return this.update(this.prepareConnection(), true, sql); } /** * Creates an {@link UpdateExecutor} for the given SQL statement and connection. * The connection is <b>NOT</b> closed after execution. * * @param conn The connection to use for the update call. * @param sql The SQL statement to execute. * * @return An {@link UpdateExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public UpdateExecutor update(Connection conn, String sql) throws SQLException { return this.update(conn, false, sql); } /** * Creates an {@link UpdateExecutor} for the given SQL statement and connection. * * @param conn The connection to use for the update call. * @param closeConn True if the connection should be closed, false otherwise. * @param sql The SQL statement to execute. * * @return An {@link UpdateExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public UpdateExecutor update(Connection conn, boolean closeConn, String sql) throws SQLException { if (conn == null) { throw new SQLException("Null connection"); } if (sql == null) { if (closeConn) { close(conn); } throw new SQLException("Null SQL statement"); } return new UpdateExecutor(conn, sql, closeConn); } /** * Creates an {@link InsertExecutor} for the given SQL. * <code>Connection</code> is retrieved from the <code>DataSource</code> * set in the constructor. This <code>Connection</code> must be in * auto-commit mode or the insert will not be saved. The <code>Connection</code> is * closed after the call. * * @param sql The SQL statement to execute. * * @return An {@link InsertExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public InsertExecutor insert(String sql) throws SQLException { return insert(this.prepareConnection(), true, sql); } /** * Creates an {@link InsertExecutor} for the given SQL and connection * The connection is <b>NOT</b> closed after execution. * * @param conn The connection to use for the query call. * @param sql The SQL statement to execute. * * @return An {@link InsertExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public InsertExecutor insert(Connection conn, String sql) throws SQLException { return insert(conn, false, sql); } /** * Creates an {@link InsertExecutor} for the given SQL and connection. * * @param conn The connection to use for the insert call. * @param closeConn True if the connection should be closed, false otherwise. * @param sql The SQL statement to execute. * * @return An {@link InsertExecutor} for this SQL statement. * @throws SQLException If there are database or parameter errors. */ public InsertExecutor insert(Connection conn, boolean closeConn, String sql) throws SQLException { if (conn == null) { throw new SQLException("Null connection"); } if (sql == null) { if (closeConn) { close(conn); } throw new SQLException("Null SQL statement"); } return new InsertExecutor(conn, sql, closeConn); } // // Entity methods // /** * Creates a new entity in the database by calling insert. * @param entity the entity to insert. * @throws SQLException if there is a problem inserting the entity. */ public <T> void create(final Class<? extends T> entityClass, final T entity) throws SQLException { internalEntityCreate(entityClass, entity, new HashSet<String>()).execute(); } /* * Internal method that returns the InsertExecutor making it easier to extend. */ protected <T> InsertExecutor internalEntityCreate(final Class<? extends T> entityClass, final T entity, final Set<String> excludeColumns) throws SQLException { final String tableName = EntityUtils.getTableName(entity.getClass()); final Map<String, String> columns = EntityUtils.getColumns(entityClass); final StringBuilder sb = new StringBuilder("insert into "); // create the SQL command sb.append(tableName); sb.append(" ("); sb.append(EntityUtils.joinColumnsWithComma(columns.keySet(), null)); sb.append(") values("); sb.append(EntityUtils.joinColumnsWithComma(columns.keySet(), ":")); sb.append(")"); LOG.debug("INSERT: {}", sb.toString()); // create the executor final InsertExecutor exec = new InsertExecutor(this.prepareConnection(), sb.toString(), true); for (String column : columns.keySet()) { // don't bind the exclude columns if (excludeColumns.contains(column)) { continue; } try { // bind all of the values final Object value = PropertyUtils.getSimpleProperty(entity, columns.get(column)); if (value == null) { exec.bindNull(column); } else { exec.bind(column, value); } } catch (final IllegalAccessException e) { throw new SQLException(e); } catch (final InvocationTargetException e) { throw new SQLException(e); } catch (final NoSuchMethodException e) { throw new SQLException(e); } } return exec; } /** * Reads all of the entities of a given type. * @param entity an entity marked with the {@link Entity} annotation. * @return a list of the entities read . * @throws SQLException If there are database or parameter errors. */ public <T> List<T> read(final Class<T> entityClass) throws SQLException { final Entity annotation = entityClass.getAnnotation(Entity.class); if (annotation == null) { throw new IllegalArgumentException(entityClass.getName() + " does not have the Entity annotation"); } // get the table's name final String tableName = EntityUtils.getTableName(entityClass); final StringBuilder sb = new StringBuilder("select * from "); sb.append(tableName); LOG.debug("SELECT: {}", sb.toString()); // setup the QueryExecutor final QueryExecutor exec = new QueryExecutor(prepareConnection(), sb.toString(), true); // execute using the BeanHandler return exec.execute(new BeanListHandler<T>(entityClass)); } /** * Reads a given entity based off the @Id columns. * @param entityClass an entity marked with the {@link Entity} annotation. * @param entity the entity to read. * @return the entity read from the db. * @throws SQLException If there are database or parameter errors. */ public <T> T read(final Class<T> entityClass, final T entity) throws SQLException { final Entity annotation = entityClass.getAnnotation(Entity.class); final Map<String, String> idColumns = EntityUtils.getIdColumns(entityClass); if (annotation == null) { throw new IllegalArgumentException(entityClass.getName() + " does not have the Entity annotation"); } if (idColumns.isEmpty()) { throw new SQLException( "Cannot read " + entityClass.getName() + " because it does not have any @Id columns"); } // get the table's name final String tableName = EntityUtils.getTableName(entityClass); final StringBuilder sb = new StringBuilder("select * from "); sb.append(tableName); sb.append(" where "); sb.append(EntityUtils.joinColumnsEquals(idColumns.keySet(), " and ")); LOG.debug("SELECT: {}", sb.toString()); // setup the QueryExecutor final QueryExecutor exec = new QueryExecutor(prepareConnection(), sb.toString(), true); // bind all the id columns bindColumnValues(exec, idColumns, entity, Collections.<String>emptySet()); // execute using the BeanHandler return exec.execute(new BeanHandler<T>(entityClass)); } /** * Constructs an {@link UpdateEntityExecutor} used to update entities. * @param entity an entity marked with the {@link Entity} annotation. * @return a {@link UpdateEntityExecutor} used to update entities. * @throws SQLException If there are database or parameter errors. */ public <T> int update(final Class<T> classType, final T entity) throws SQLException { return update(classType, entity, Collections.<String>emptySet()); } /** * Constructs an {@link UpdateEntityExecutor} used to update entities that excludes columns during binding. * @param entity an entity marked with the {@link Entity} annotation. * @param excludeColumns a collection of columns to exclude. * @return a {@link UpdateEntityExecutor} used to update entities. * @throws SQLException If there are database or parameter errors. */ public <T> int update(final Class<T> entityClass, final T entity, final Collection<String> excludeColumns) throws SQLException { final Map<String, String> updateColumns = EntityUtils.getColumns(entityClass, true); final Map<String, String> idColumns = EntityUtils.getIdColumns(entityClass); final Entity annotation = entityClass.getAnnotation(Entity.class); if (annotation == null) { throw new IllegalArgumentException(entityClass.getName() + " does not have the Entity annotation"); } if (idColumns.isEmpty()) { throw new SQLException( "Cannot update " + entityClass.getName() + " because it does not have any @Id columns"); } // get the table's name final String tableName = EntityUtils.getTableName(entityClass); final StringBuilder sb = new StringBuilder("update "); // create the SQL command sb.append(tableName); sb.append(" set "); sb.append(EntityUtils.joinColumnsEquals(updateColumns.keySet(), ", ")); sb.append(" where "); sb.append(EntityUtils.joinColumnsEquals(idColumns.keySet(), " and ")); LOG.debug("UPDATE: {}", sb.toString()); // setup the QueryExecutor final UpdateExecutor exec = new UpdateExecutor(prepareConnection(), sb.toString(), true); // bind all the update column values bindColumnValues(exec, updateColumns, entity, excludeColumns); // bind all the id columns bindColumnValues(exec, idColumns, entity, Collections.<String>emptySet()); // execute using the BeanHandler return exec.execute(); } /** * Constructs an {@link DeleteEntityExecutor} used to delete entities. * @param entity an entity marked with the {@link Entity} annotation. * @return a {@link DeleteEntityExecutor} used to delete entities. * @throws SQLException If there are database or parameter errors. */ public <T> int delete(final Class<T> entityClass, final T entity) throws SQLException { final Map<String, String> idColumns = EntityUtils.getIdColumns(entityClass); final Entity annotation = entityClass.getAnnotation(Entity.class); if (annotation == null) { throw new IllegalArgumentException(entityClass.getName() + " does not have the Entity annotation"); } if (idColumns.isEmpty()) { throw new SQLException( "Cannot update " + entityClass.getName() + " because it does not have any @Id columns"); } // get the table's name final String tableName = EntityUtils.getTableName(entityClass); final StringBuilder sb = new StringBuilder("delete from "); sb.append(tableName); sb.append(" where "); sb.append(EntityUtils.joinColumnsEquals(idColumns.keySet(), " and ")); LOG.debug("DELETE: {}", sb.toString()); // setup the QueryExecutor final UpdateExecutor exec = new UpdateExecutor(prepareConnection(), sb.toString(), true); // bind all the id columns bindColumnValues(exec, idColumns, entity, Collections.<String>emptySet()); // execute using the BeanHandler return exec.execute(); } /** * Binds values to an executor. * @param exec * @param columns * @param entity * @param excludes * @throws SQLException */ private <T> void bindColumnValues(final AbstractExecutor<?> exec, final Map<String, String> columns, final T entity, final Collection<String> excludes) throws SQLException { for (String column : columns.keySet()) { // skip anything in the exclude set if (excludes.contains(column)) { continue; } try { // bind all of the values final Object value = PropertyUtils.getSimpleProperty(entity, columns.get(column)); if (value == null) { exec.bindNull(column); } else { exec.bind(column, value); } } catch (final IllegalAccessException e) { throw new SQLException(e); } catch (final InvocationTargetException e) { throw new SQLException(e); } catch (final NoSuchMethodException e) { throw new SQLException(e); } } } }