| /* |
| * Licensed to the Apache Software Foundation (ASF) under one or more |
| * contributor license agreements. See the NOTICE file distributed with |
| * this work for additional information regarding copyright ownership. |
| * The ASF licenses this file to You 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 org.apache.commons.dbutils2; |
| |
| 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.apache.commons.dbutils2.handlers.BeanHandler; |
| import org.apache.commons.dbutils2.handlers.BeanListHandler; |
| |
| /** |
| * Executes SQL queries with pluggable strategies for handling |
| * <code>ResultSet</code>s. This class is thread safe. |
| * |
| * @see ResultSetHandler |
| */ |
| public class QueryRunner { |
| /** |
| * 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(")"); |
| |
| // 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); |
| |
| // 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 ")); |
| |
| // 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 ")); |
| |
| // 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 ")); |
| |
| // 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); |
| } |
| } |
| } |
| |
| } |