Java 类org.springframework.jdbc.core.PreparedStatementCallback 实例源码

public Boolean deleteFile(final String filename, final String key)
        throws FileNotFoundException, IOException {
    String cloudfilesql = "delete CLOUDFILEDATA where FILENAME=?";

    return jdbcTemplate.execute(cloudfilesql,
            new PreparedStatementCallback<Boolean>() {

                @Override
                public Boolean doInPreparedStatement(PreparedStatement ps)
                        throws SQLException, DataAccessException {
                    ps.setString(1, filename.trim());
                    return ps.execute();
                }

            });
}

/**
 * 从删除一条代表二进制数据的记录
 * 
 * @param id
 *            主键
 * @throws SQLException
 */
protected void deleteFromBlob(final String id) throws SQLException {
    final String sql = "DELETE FROM BDF2_BLOB_STORE WHERE ID_=?";
    int updatedRowCount = super.getJdbcTemplate().execute(sql,
            new PreparedStatementCallback<Integer>() {
                public Integer doInPreparedStatement(
                        PreparedStatement preparedstatement)
                        throws SQLException, DataAccessException {
                    preparedstatement.setString(1, id);
                    return preparedstatement.executeUpdate();
                }
            });
    if (0 == updatedRowCount) {
        throw new SQLException(String.format("未能成功删除大二进制对象[id=%s],请检查其是否存在", id));
    }
}

/**
 * 删除一条代表文本的记录。
 * 
 * @param id
 *            主键
 * @throws SQLException
 */
protected void deleteFromClob(final String id) throws SQLException {
    final String sql = "DELETE FROM BDF2_CLOB_STORE WHERE ID_=?";
    int updatedRowCount = super.getJdbcTemplate().execute(sql,
            new PreparedStatementCallback<Integer>() {
                public Integer doInPreparedStatement(
                        PreparedStatement preparedstatement)
                        throws SQLException, DataAccessException {
                    preparedstatement.setString(1, id);
                    return preparedstatement.executeUpdate();
                }

            });
    if (0 == updatedRowCount) {
        throw new SQLException(String.format("未能成功删除大字符对象[id=%s],请检查其是否存在",
                id));
    }
}

@Override
public List<App> getAppCategoryRank(final int parentId, final int subCatalog, final int top) {
    PreparedStatementCallback<List<App>> cb = new PreparedStatementCallback<List<App>>() {
        @Override
        public List<App> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.setInt(1, parentId);
            ps.setInt(2, subCatalog);
            ps.setInt(3, top);
            ResultSet rs = null;
            try {
                rs = ps.executeQuery();
                if (rs.last()) {
                    int count = rs.getRow();
                    List<App> list = new ArrayList<App>(count);
                    rs.beforeFirst();
                    App app = null;
                    while (rs.next()) {
                        app = appRowMapper.mapRow(rs, rs.getRow());
                        changeOutputImpl.setUrls(app);
                        list.add(app);
                    }
                    return list;
                }

            } catch (Exception e) {
                logger.error("SQL data error:", e);
                return null;
            } finally {
                if (null != rs)
                    rs.close();
            }
            return null;
        }
    };
    return jdbcTemplate.execute(QUERY_APP_CATEGORY_TOP_SQL, cb);
}

@Override
public List<App> getAppDefultRank(final int typeId, final int top) {
    PreparedStatementCallback<List<App>> cb = new PreparedStatementCallback<List<App>>() {
        @Override
        public List<App> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.setInt(1, typeId);
            ps.setInt(2, top);
            ResultSet rs = null;
            try {
                rs = ps.executeQuery();
                if (rs.last()) {
                    int count = rs.getRow();
                    List<App> list = new ArrayList<App>(count);
                    rs.beforeFirst();
                    App app = null;
                    while (rs.next()) {
                        app = appRowMapper.mapRow(rs, rs.getRow());
                        changeOutputImpl.setUrls(app);
                        list.add(app);
                    }
                    return list;
                }

            } catch (Exception e) {
                logger.error("SQL data error:", e);
                return null;
            } finally {
                if (null != rs)
                    rs.close();
            }
            return null;
        }
    };
    return jdbcTemplate.execute(QUERY_APP_DEFAULT_TOP_SQL, cb);
}

@Override
public List<AppType> getAppTypes(final int parentId) {
    PreparedStatementCallback<List<AppType>> cb = new PreparedStatementCallback<List<AppType>>() {
        @Override
        public List<AppType> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ResultSet rs = null;
            try {
                ps.setInt(1, parentId);
                ps.setInt(2, parentId);
                rs = ps.executeQuery();
                // predicate count.
                if (rs.last()) {
                    int count = rs.getRow();
                    List<AppType> list = new ArrayList<AppType>(count);
                    rs.beforeFirst();
                    AppType appType = null;
                    while (rs.next()) {
                        appType = appTypeRowMapper.mapRow(rs, rs.getRow());
                        list.add(appType);
                    }
                    return list;
                } else {
                    return null;
                }
            } finally {
                if (null != rs) {
                    rs.close();
                }
            }
        }
    };
    return this.jdbcTemplate.execute(appTypes, cb);
}

@Override
public Topic getPowerTuiJianTopic(final int topicid) {
    PreparedStatementCallback<Topic> cb = new PreparedStatementCallback<Topic>() {
        @Override
        public Topic doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ResultSet rs = null;
            try {
                ps.setInt(1, topicid);
                rs = ps.executeQuery();
                if (!rs.next()) {
                    return null;
                }
                Topic topic = topicRowMapper.mapRow(rs, 1);
                changeOutputImpl.setUrls(topic);
                return topic;
            } finally {
                if (null != rs)
                    rs.close();
            }
        }
    };
    return jdbcTemplate.execute(topicPowerTuiJian, cb);
}

@Override
public Topic getPowerChannelTuiJianTopic(final int topicid) {
    PreparedStatementCallback<Topic> cb = new PreparedStatementCallback<Topic>() {
        @Override
        public Topic doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ResultSet rs = null;
            try {
                ps.setInt(1, topicid);
                rs = ps.executeQuery();
                if (!rs.next()) {
                    return null;
                }
                Topic topic = topicRowMapper.mapRow(rs, 1);
                changeOutputImpl.setUrls(topic);
                return topic;
            } finally {
                if (null != rs)
                    rs.close();
            }
        }
    };
    return jdbcTemplate.execute(topicPowerChannelTuiJian, cb);
}

@Test
public void testExecute() throws SQLException {
    given(preparedStatement.executeUpdate()).willReturn(1);

    params.put("perfId", 1);
    params.put("priceId", 1);
    Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
            new PreparedStatementCallback<Object>() {
                @Override
                public Object doInPreparedStatement(PreparedStatement ps)
                        throws SQLException {
                    assertEquals(preparedStatement, ps);
                    ps.executeUpdate();
                    return "result";
                }
            });

    assertEquals("result", result);
    verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
    verify(preparedStatement).setObject(1, 1);
    verify(preparedStatement).setObject(2, 1);
    verify(preparedStatement).close();
    verify(connection).close();
}

@Test
public void testExecuteWithTypedParameters() throws SQLException {
    given(preparedStatement.executeUpdate()).willReturn(1);

    params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1));
    params.put("priceId", new SqlParameterValue(Types.INTEGER, 1));
    Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
            new PreparedStatementCallback<Object>() {
                @Override
                public Object doInPreparedStatement(PreparedStatement ps)
                        throws SQLException {
                    assertEquals(preparedStatement, ps);
                    ps.executeUpdate();
                    return "result";
                }
            });

    assertEquals("result", result);
    verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
    verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
    verify(preparedStatement).setObject(2, 1, Types.INTEGER);
    verify(preparedStatement).close();
    verify(connection).close();
}

@Test
public void testExecuteNoParameters() throws SQLException {
    given(preparedStatement.executeUpdate()).willReturn(1);

    Object result = namedParameterTemplate.execute(SELECT_NO_PARAMETERS,
            new PreparedStatementCallback<Object>() {
                @Override
                public Object doInPreparedStatement(PreparedStatement ps)
                        throws SQLException {
                    assertEquals(preparedStatement, ps);
                    ps.executeQuery();
                    return "result";
                }
            });

    assertEquals("result", result);
    verify(connection).prepareStatement(SELECT_NO_PARAMETERS);
    verify(preparedStatement).close();
    verify(connection).close();
}

public Position ack(final List<Record> records) throws YuGongException {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(context.getSourceDs());
    jdbcTemplate.execute(mlogCleanSql, new PreparedStatementCallback() {

        @Override
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            for (Record record : records) {
                OracleIncrementRecord incRecord = (OracleIncrementRecord) record;
                ps.setObject(1, incRecord.getRowId().getValue(), incRecord.getRowId().getColumn().getType());
                ps.addBatch();
            }

            ps.executeBatch();
            return null;
        }
    });

    return null;
}

/**
 * <pre>
 * 常见的物化视图创建语句：
 * 1. CREATE MATERIALIZED VIEW LOG ON test_all_target with primary key; 
 * 
 * 本方法，主要提取生成物化视图的表名
 * </pre>
 * 
 * @param dataSource
 * @param schemaName
 * @param tableName
 * @return
 */
public static String getMLogTableName(final DataSource dataSource, final String schemaName, final String tableName) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String sql = StringUtils.isNotEmpty(schemaName) ? mlogSchemaQuerySql : mlogQuerySql;
    return (String) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            DatabaseMetaData metaData = ps.getConnection().getMetaData();
            String sName = getIdentifierName(schemaName, metaData);
            String tName = getIdentifierName(tableName, metaData);
            ps.setString(1, tName);
            if (StringUtils.isNotEmpty(schemaName)) {
                ps.setString(2, sName);
            }
            ResultSet rs = ps.executeQuery();
            String log = null;
            if (rs.next()) {
                log = rs.getString("log_table");
            }

            rs.close();
            return log;
        }
    });
}

@Override
public void clearMlog(final List<ColumnValue> records, String mlogCleanSql) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(context.getSourceDs());
    jdbcTemplate.execute(mlogCleanSql, new PreparedStatementCallback() {

        @Override
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            for (ColumnValue record : records) {
                ps.setObject(1, record.getValue(), record.getColumn().getType());
                ps.addBatch();
            }
            ps.executeBatch();
            return null;
        }
    });
}

/**
 * 根据mlog物化日志表的SEQUENCE$$字段单独查询
 *
 * @param sequence
 * @param context
 * @param columns
 * @return
 */
private List<ColumnValue> getSingleMlogRecord(final String singleMlogExtractSql, final int sequence, final YuGongContext context, final List<ColumnMeta> mlogCols, final List<ColumnMeta> columns, final List<ColumnValue> rowIds) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(context.getSourceDs());
    final List<ColumnValue> columnValues = new ArrayList<ColumnValue>();
    return (List<ColumnValue>) jdbcTemplate.execute(singleMlogExtractSql, new PreparedStatementCallback() {
        @Override
        public List<ColumnValue> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.setInt(1, sequence);
            ResultSet rs = ps.executeQuery();
            try {
                while (rs.next()) {
                    rowIds.add(new ColumnValue(rowidColumn, rs.getObject("rowid")));
                    columnValues.addAll(buildColumnValue(rs, context.getSourceEncoding(), mlogCols, columns));
                }
            } finally {
                JdbcUtils.closeResultSet(rs);
            }
            return columnValues;
        }
    });
}

/**
 * 获取DRDS下表的拆分字段, 返回格式为 id,name
 *
 * @param dataSource
 * @param schemaName
 * @param tableName
 * @return
 */
public static String getShardKeyByDRDS(final DataSource dataSource, final String schemaName, final String tableName) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    return (String) jdbcTemplate.execute(queryShardKey, new PreparedStatementCallback() {

        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            DatabaseMetaData metaData = ps.getConnection().getMetaData();
            // String sName = getIdentifierName(schemaName, metaData);
            String tName = getIdentifierName(tableName, metaData);

            ps.setString(1, tName);
            ResultSet rs = null;
            try {
                rs = ps.executeQuery();
                String log = null;
                if (rs.next()) {
                    log = rs.getString("KEYS");
                }

                return log;
            } finally {
                JdbcUtils.closeResultSet(rs);
            }
        }
    });
}

public <T> void unsetContent(T metadata) {
    String sql = "DELETE FROM BLOBS WHERE id=" + BeanUtils.getFieldWithAnnotation(metadata, ContentId.class);
       this.template.execute(sql, new PreparedStatementCallback<Integer>() {
           @Override
           public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
               int rc = 0;
               try {
                   rc = ps.executeUpdate();
                   BeanUtils.setFieldWithAnnotation(metadata, ContentId.class, null);
                   BeanUtils.setFieldWithAnnotation(metadata, ContentLength.class, 0);
               } catch (SQLException sqle) {
                   logger.error(String.format("Error deleting content %s", BeanUtils.getFieldWithAnnotation(metadata, ContentId.class)), sqle);
               }
               return rc;
           }
       });
}

@Override
public int commit(DefaultSqlEndpoint defaultSqlEndpoint, Exchange exchange, Object data, NamedParameterJdbcTemplate jdbcTemplate,
                  SqlParameterSource parameterSource, String query) throws Exception {

    final SqlParameterSource param = new ElsqlSqlMapSource(exchange, data);
    final String sql = elSql.getSql(query, new SpringSqlParams(param));
    LOG.debug("commit @{} using sql: {}", query, sql);

    return jdbcTemplate.execute(sql, param, new PreparedStatementCallback<Integer>() {
        @Override
        public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.execute();

            int updateCount = ps.getUpdateCount();
            if (LOG.isTraceEnabled()) {
                LOG.trace("Update count {}", updateCount);
            }
            return updateCount;
        }
    });
}

@Override
public int commitBatchComplete(DefaultSqlEndpoint endpoint, NamedParameterJdbcTemplate namedJdbcTemplate,
                        SqlParameterSource parameterSource, String query) throws Exception {

    final SqlParameterSource param = new EmptySqlParameterSource();
    final String sql = elSql.getSql(query, new SpringSqlParams(param));
    LOG.debug("commitBatchComplete @{} using sql: {}", query, sql);

    return namedJdbcTemplate.execute(sql, param, new PreparedStatementCallback<Integer>() {
        @Override
        public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.execute();

            int updateCount = ps.getUpdateCount();
            if (LOG.isTraceEnabled()) {
                LOG.trace("Update count {}", updateCount);
            }
            return updateCount;
        }
    });
}

@Override
public int commit(final DefaultSqlEndpoint endpoint, final Exchange exchange, final Object data, final JdbcTemplate jdbcTemplate, final String query) throws Exception {

    final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(query, endpoint.isAllowNamedParameters(), exchange);

    return jdbcTemplate.execute(preparedQuery, new PreparedStatementCallback<Integer>() {
        public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
            int expected = ps.getParameterMetaData().getParameterCount();

            Iterator<?> iterator = sqlPrepareStatementStrategy.createPopulateIterator(query, preparedQuery, expected, exchange, data);
            if (iterator != null) {
                sqlPrepareStatementStrategy.populateStatement(ps, iterator, expected);
                LOG.trace("Execute query {}", query);
                ps.execute();

                int updateCount = ps.getUpdateCount();
                if (LOG.isTraceEnabled()) {
                    LOG.trace("Update count {}", updateCount);
                }
                return updateCount;
            }

            return 0;
        };
    });
}

@Override
public int commitBatchComplete(final DefaultSqlEndpoint endpoint, final JdbcTemplate jdbcTemplate, final String query) throws Exception {
    final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(query, endpoint.isAllowNamedParameters(), null);

    return jdbcTemplate.execute(preparedQuery, new PreparedStatementCallback<Integer>() {
        public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
            int expected = ps.getParameterMetaData().getParameterCount();
            if (expected != 0) {
                throw new IllegalArgumentException("Query onConsumeBatchComplete " + query + " cannot have parameters, was " + expected);
            }

            LOG.trace("Execute query {}", query);
            ps.execute();

            int updateCount = ps.getUpdateCount();
            if (LOG.isTraceEnabled()) {
                LOG.trace("Update count {}", updateCount);
            }
            return updateCount;
        };
    });
}

@Test public void testSimpleOperation() {
    jdbcTemplate.execute("create table Person(FirstName varchar2 INDEX, LastName varchar2)");
    jdbcTemplate.execute("insert into Person values(?,?)", new PreparedStatementCallback() {
        public Object doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
            for (int i = 0; i < 10; i++) {
                preparedStatement.setString(1, "FirstName" + i);
                preparedStatement.setString(2, "LastName" + i);
                preparedStatement.executeUpdate();
            }
            return null;
        }
    });
    long count = jdbcTemplate.queryForLong("select count(*) from Person");
    assertEquals(10, count);
}

public boolean insertViolations(List<Violation> violations){
    try{
        for(int i = 0; i < violations.size(); i++){
            Violation v = violations.get(i);
            String sql = "INSERT INTO violations (citation_number,violation_number,violation_description,warrant_status,warrant_number,status,fine_amount,court_cost) VALUES ('"+v.citation_number+"','"+v.violation_number+"','"+v.violation_description+"',"+v.warrant_status+",'"+v.warrant_number+"','"+v.status.name()+"',"+v.fine_amount.toString()+","+v.court_cost.toString()+")";
            jdbcTemplate.execute(sql, new PreparedStatementCallback<Boolean>(){
                @Override
                public Boolean doInPreparedStatement(java.sql.PreparedStatement ps)
                        throws SQLException, DataAccessException {
                    return ps.execute();
                }
            });
        }   
    }catch(Exception e){
        LogSystem.LogDBException(e);
        return false;
    }
    return true;
}

public boolean removeViolations(List<Violation> violations){
    try{
        for(int i = 0; i < violations.size(); i++){
            Violation v = violations.get(i);
            String sql = "DELETE FROM violations WHERE citation_number = '"+v.citation_number+"'";
            jdbcTemplate.execute(sql, new PreparedStatementCallback<Boolean>(){
                @Override
                public Boolean doInPreparedStatement(java.sql.PreparedStatement ps)
                        throws SQLException, DataAccessException {
                    return ps.execute();
                }
            });
        }
    }catch(Exception e){
        LogSystem.LogDBException(e);
        return false;
    }
    return true;
}

@SuppressWarnings("unchecked")
@Ignore
@Test
public void insertsViolations(){
    //This code is not working yet, to Ignored.
    final Violation VIOLATION = new Violation();
       VIOLATION.id = 4;
       final List<Violation> VIOLATIONS = Lists.newArrayList(VIOLATION);

       //when(jdbcTemplate.execute(Matchers.anyString(), Mockito.any(PreparedStatementCallback.class))).thenReturn(true);
       when(jdbcTemplate.execute(Matchers.anyString(), Mockito.any(PreparedStatementCallback.class)))
       .thenAnswer(new Answer<Boolean>(){

        @Override
        public Boolean answer(InvocationOnMock invocation) throws Throwable {
            // TODO Auto-generated method stub
            //http://stackoverflow.com/questions/28633173/increasing-code-coverage-for-jdbctemplate-mocking
            return true;
        }

       });

       Boolean returnValue = mockViolationDAO.insertViolations(VIOLATIONS);
       assertThat(returnValue, is(true));
}

public List<Map<String, ?>> getCountGlossToSumFreqRatio() {
    String sql = "SELECT count_gloss, sum_freq, freq_ratio FROM public.v_count_gloss_to_sum_freq";
    CustomPreparedStatementCreator sc = new CustomPreparedStatementCreator(sql) {};

    PreparedStatementCallback<List<Map<String, ?>>> cb = s -> {
        ResultSet rs = s.executeQuery();

        List<Map<String, ?>> result = new ArrayList<>();
        Map<String, Object> item;
        while (rs.next()) {
            item = new LinkedHashMap<>();

            item.put("countGloss", rs.getInt(1));
            item.put("sumFreq", rs.getInt(2));
            item.put("freqRatio", rs.getFloat(3));

            result.add(item);
        }

        return result;
    };
    return execute(sc, cb);
}

public Boolean execute(final String sql,final Map<String,?> parameters) {
        return this.namedParameterJdbcTemplate.execute(sql,parameters, new PreparedStatementCallback<Boolean>() {
            @Override
            public Boolean doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
//              if(ps.getParameterMetaData()!=null){
//                  int count=ps.getParameterMetaData().getParameterCount();
//                  for(int i=0;i<count;i++){
//                      String name=ps.getParameterMetaData().getParameterTypeName(i);
//                      System.out.println(i+":"+name);
//                      if(name.equalsIgnoreCase("properties") || "config".equalsIgnoreCase(name)){
//                          Blob bv=ps.getConnection().createBlob();
//                          bv.setBytes(0, ((String)parameters.get(name)).getBytes());
//                          ps.setBlob(i, bv);
//                      }
//                  }
//                  return ps.execute();
//              }else{
//                  return ps.execute();
//              }
                return ps.execute();
            }
        });
    }

public <H extends CheckedConsumer<T>> PreparedStatementCallback<H> newPreparedStatementCallback(final H handler) {
    return new PreparedStatementCallback<H>() {
        @Override
        public H doInPreparedStatement(
                PreparedStatement ps)
                throws SQLException, DataAccessException {
            ResultSet rs = ps.executeQuery();
            ResultSetExtractor<H> extractor = resultSetExtractor.newResultSetExtractor(handler);
            try {
                return extractor.extractData(rs);
            } finally {
                rs.close();
            }
        }
    };
}

@Test
public void testExecute() throws SQLException {
    given(preparedStatement.executeUpdate()).willReturn(1);

    params.put("perfId", 1);
    params.put("priceId", 1);
    Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
            new PreparedStatementCallback<Object>() {
                @Override
                public Object doInPreparedStatement(PreparedStatement ps)
                        throws SQLException {
                    assertEquals(preparedStatement, ps);
                    ps.executeUpdate();
                    return "result";
                }
            });

    assertEquals("result", result);
    verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
    verify(preparedStatement).setObject(1, 1);
    verify(preparedStatement).setObject(2, 1);
    verify(preparedStatement).close();
    verify(connection).close();
}

@Test
public void testExecuteWithTypedParameters() throws SQLException {
    given(preparedStatement.executeUpdate()).willReturn(1);

    params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1));
    params.put("priceId", new SqlParameterValue(Types.INTEGER, 1));
    Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
            new PreparedStatementCallback<Object>() {
                @Override
                public Object doInPreparedStatement(PreparedStatement ps)
                        throws SQLException {
                    assertEquals(preparedStatement, ps);
                    ps.executeUpdate();
                    return "result";
                }
            });

    assertEquals("result", result);
    verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
    verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
    verify(preparedStatement).setObject(2, 1, Types.INTEGER);
    verify(preparedStatement).close();
    verify(connection).close();
}

@Test
public void testExecuteNoParameters() throws SQLException {
    given(preparedStatement.executeUpdate()).willReturn(1);

    Object result = namedParameterTemplate.execute(SELECT_NO_PARAMETERS,
            new PreparedStatementCallback<Object>() {
                @Override
                public Object doInPreparedStatement(PreparedStatement ps)
                        throws SQLException {
                    assertEquals(preparedStatement, ps);
                    ps.executeQuery();
                    return "result";
                }
            });

    assertEquals("result", result);
    verify(connection).prepareStatement(SELECT_NO_PARAMETERS);
    verify(preparedStatement).close();
    verify(connection).close();
}

/**
 * Suitable for CRUD operations where no result set is expected.
 * @param params
 * @param query 
 * @return  
 */
public boolean execute(Map<String, String> params, String query) {
    final AtomicBoolean result = new AtomicBoolean(false);
    Tuple2<DataSource, Connection> conn = null;
    try {
        conn = getConnection();
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(conn._1());
        jdbcTemplate.execute(query, params, new PreparedStatementCallback<Void>() {
            @Override
            public Void doInPreparedStatement(PreparedStatement ps) {
                try {
                    result.set(ps.execute());
                } catch(SQLException e) {
                    result.set(false);
                }
                return null;
            }
        });
    } catch (Exception ex) {
        Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex);
        result.set(false);
    } finally {
        returnConnection(conn);
    }
    return result.get();
}

@Override
public MetadataBean getParameterMetadata(String query, final List<Object> params)
{
    try
    {
        query = processQuery(query);

        return (MetadataBean) new JdbcTemplate(getDataSourceHolder().getDataSource()).execute(query,
            new PreparedStatementCallback()
            {
                @Override
                public Object doInPreparedStatement(PreparedStatement statement) throws SQLException,
                    DataAccessException
                {
                    addParamsToStatement(statement, params);
                    try
                    {
                        ParameterMetaData parameterMetaData = statement.getParameterMetaData();
                        return new MetadataBean(parameterMetaData);
                    }
                    catch( SQLException sqle )
                    {
                        return null;
                    }
                }
            });
    }
    catch( Exception e )
    {
        String msg = CurrentLocale.get("com.tle.core.reporting.error.parameter.metadata") + query;
        LOGGER.error(msg, e);
        throw new RuntimeException(msg, e);
    }
}

public long prepareCatchup(AtomicLong serial) {
    PGQueryBuilder b = new PGQueryBuilder(req);
    long clientId = jdbc.queryForObject(PGConstants.NEXT_FROM_CATCHUP_SEQ, Long.class);
    String catchupSQL = b.catchupSQL(clientId);
    return jdbc.execute(catchupSQL, new PreparedStatementCallback<Long>() {

        @Override
        public Long doInPreparedStatement(PreparedStatement ps) throws SQLException,
                DataAccessException {

            log.debug("{} preparing paging for matches after {}", req, serial.get());
            try {
                Stopwatch sw = Stopwatch.createStarted();
                b.createStatementSetter(serial).setValues(ps);
                int numberOfFactsToCatchup = ps.executeUpdate();
                sw.stop();
                if (numberOfFactsToCatchup > 0) {
                    log.debug("{} prepared {} facts for cid={} in {}ms", req,
                            numberOfFactsToCatchup, clientId, sw.elapsed(
                                    TimeUnit.MILLISECONDS));
                    return clientId;
                } else {
                    log.debug("{} nothing to catch up", req);
                    return 0L;
                }
            } catch (SQLException ex) {
                log.error("While trying to prepare catchup", ex);
                throw ex;
            }
        }
    });

}

public boolean updateRevocation(String filename) {
    String newusersql = "update CLOUDFILEDATA set REVOCATION='yes' where filename='"
            + filename.trim() + "'";

    return jdbcTemplate.execute(newusersql,
            new PreparedStatementCallback<Boolean>() {

                @Override
                public Boolean doInPreparedStatement(PreparedStatement ps)
                        throws SQLException, DataAccessException {

                    return ps.execute();
                }

            });

}

/**
 * 根据软件编号获取截图数据
 * 
 * @param softid
 *            软件编号
 * @return
 */
private List<ScreenImage> getAppScreenImages(final int softid) {
    PreparedStatementCallback<List<ScreenImage>> cb = new PreparedStatementCallback<List<ScreenImage>>() {
        @Override
        public List<ScreenImage> doInPreparedStatement(PreparedStatement ps) throws SQLException,
                DataAccessException {
            ResultSet rs = null;
            try {
                ps.setInt(1, softid);
                rs = ps.executeQuery();
                if (rs.last()) {
                    int count = rs.getRow();
                    List<ScreenImage> list = new ArrayList<ScreenImage>(count);
                    rs.beforeFirst();
                    ScreenImage appScreenImage = null;
                    while (rs.next()) {
                        appScreenImage = screenImageRowMapper.mapRow(rs, rs.getRow());
                        changeOutputImpl.setAppScreenUrl(appScreenImage);
                        list.add(appScreenImage);
                    }
                    return list;
                } else {
                    return null;
                }
            } finally {
                if (null != rs)
                    rs.close();
            }
        }
    };
    return this.jdbcTemplate.execute(appScreenImage, cb);
}

@Override
public List<App> findByIds(List<Integer> ids) {
    // Session session = sessions.getCurrentSession();
    // Query query = null;
    // query =
    // session.createQuery("select a.id, a.name, a.downLoadLink, a.logo from App a where id in (:ids)").setParameterList("ids",
    // ids);
    // List<App> list = HibernateHelper.list(query);
    // for (App app : list) {
    // changeOutputImpl.setUrls(app);
    // }
    StringBuilder tmp = new StringBuilder(ids.size() * 6);
    for (Integer id : ids) {
        tmp.append(id).append(',');
    }
    tmp.deleteCharAt(tmp.length() - 1);
    String sql = appsByIds.replace("?", tmp.toString());

    PreparedStatementCallback<List<App>> cb = new PreparedStatementCallback<List<App>>() {
        @Override
        public List<App> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ResultSet rs = null;
            try {
                rs = ps.executeQuery();
                if (rs.last()) {
                    int count = rs.getRow();
                    List<App> list = new ArrayList<App>(count);
                    rs.beforeFirst();
                    App app = null;
                    while (rs.next()) {
                        app = appsRowMapper.mapRow(rs, rs.getRow());
                        changeOutputImpl.setUrls(app);
                        list.add(app);
                    }
                    return list;
                } else {
                    return null;
                }
            } finally {
                if (null != rs) {
                    rs.close();
                }
            }
        }

    };
    return this.jdbcTemplate.execute(sql, cb);
}

private Object getMinId() {
    if (jdbcTemplate != null && StringUtils.isNotBlank(getMinPkSql)) {
        Object min = jdbcTemplate.execute(getMinPkSql, new PreparedStatementCallback() {

            @Override
            public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                ResultSet rs = ps.executeQuery();
                Object re = null;
                while (rs.next()) {
                    re = rs.getObject(1);
                    break;
                }
                return re;
            }
        });

        if (min != null) {
            if (min instanceof Number) {
                min = Long.valueOf(String.valueOf(min)) - 1;
            } else {
                min = "";
            }
        } else {
            if (min instanceof Number) {
                min = 0;
            } else {
                min = "";
            }
        }

        return min;
    } else {
        throw new YuGongException("jdbcTemplate or getMinPkSql is null while getMinId");
    }
}

/**
 * 获取DRDS下表的拆分字段, 返回格式为 id,name
 * 
 * @param dataSource
 * @param schemaName
 * @param tableName
 * @return
 */
public static String getShardKeyByDRDS(final DataSource dataSource, final String schemaName, final String tableName) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    try {
        return (String) jdbcTemplate.execute(queryShardKey, new PreparedStatementCallback() {

            public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                DatabaseMetaData metaData = ps.getConnection().getMetaData();
                // String sName = getIdentifierName(schemaName, metaData);
                String tName = getIdentifierName(tableName, metaData);

                ps.setString(1, tName);
                ResultSet rs = ps.executeQuery();
                String log = null;
                if (rs.next()) {
                    log = rs.getString("KEYS");
                }

                rs.close();
                return log;
            }
        });
    } catch (DataAccessException e) {
        // 兼容下oracle源库和目标库DRDS表名不一致的情况,识别一下表名不存在
        Throwable cause = e.getRootCause();
        if (cause instanceof SQLException) {
            // ER_NO_SUCH_TABLE
            if (((SQLException) cause).getErrorCode() == 1146) {
                return null;
            }
        }

        throw e;
    }
}

/**
 * <pre>
 * 常见的物化视图创建语句：
 * 1. CREATE MATERIALIZED VIEW LOG ON test_all_target with primary key;
 *
 * 本方法，主要提取生成物化视图的表名
 * </pre>
 *
 * @param dataSource
 * @param schemaName
 * @param tableName
 * @return
 */
public static String getMLogTableName(final DataSource dataSource, final String schemaName, final String tableName) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String querySql = StringUtils.isEmpty(schemaName) ? mlogQuerySql : mlogSchemaQuerySql;

    return (String) jdbcTemplate.execute(querySql, new PreparedStatementCallback() {

        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            DatabaseMetaData metaData = ps.getConnection().getMetaData();
            String sName = getIdentifierName(schemaName, metaData);
            String tName = getIdentifierName(tableName, metaData);

            ps.setString(1, tName);
            if (StringUtils.isNotEmpty(schemaName)) {
                ps.setString(2, sName);
            }

            ResultSet rs = null;
            String log;
            try {
                rs = ps.executeQuery();
                log = null;
                if (rs.next()) {
                    log = rs.getString("log_table");
                }
            } finally {
                JdbcUtils.closeResultSet(rs);
            }
            return log;
        }
    });
}