Java 类java.sql.PreparedStatement 实例源码

@Override
public boolean addUser(long botId, User user) {
    if (user == null) {
        return false;
    }
    String sql = "INSERT INTO " + getTableName() + "(bot_id, user_id, user_first_name, user_last_name, user_username) VALUES(?, ?, ?, ?, ?)"
            + " ON DUPLICATE KEY UPDATE"
            + " user_first_name = VALUES(user_first_name), user_last_name = VALUES(user_last_name), user_username = VALUES(user_username)";
    try (
            Connection conn = this.getDataSource().getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ) {
        ps.setLong(1, botId);
        ps.setLong(2, user.id);
        ps.setString(3, user.firstName);
        ps.setString(4, user.lastName);
        ps.setString(5, user.username);
        return 1 == ps.executeUpdate();
    } catch (SQLException e) {
        getLogger().error(e.getMessage(), e);
    }
    return false;
}

private void save(Connection con) throws SQLException {
    try (PreparedStatement pstmt = con.prepareStatement("MERGE INTO goods (id, seller_id, name, "
            + "description, tags, timestamp, quantity, price, delisted, height, latest) KEY (id, height) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, TRUE)")) {
        int i = 0;
        pstmt.setLong(++i, this.getId());
        pstmt.setLong(++i, this.getSellerId());
        pstmt.setString(++i, this.getName());
        pstmt.setString(++i, this.getDescription());
        pstmt.setString(++i, this.getTags());
        pstmt.setInt(++i, this.getTimestamp());
        pstmt.setInt(++i, this.getQuantity());
        pstmt.setLong(++i, this.getPriceNQT());
        pstmt.setBoolean(++i, this.isDelisted());
        pstmt.setInt(++i, Nxt.getBlockchain().getHeight());
        pstmt.executeUpdate();
    }
}

/**
 * Tests fix for BUG#14609 - Exception thrown for new decimal type when
 * using updatable result sets.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug14609() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        createTable("testBug14609", "(field1 int primary key, field2 decimal)");
        this.stmt.executeUpdate("INSERT INTO testBug14609 VALUES (1, 1)");

        PreparedStatement updatableStmt = this.conn.prepareStatement("SELECT field1, field2 FROM testBug14609", ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        try {
            this.rs = updatableStmt.executeQuery();
        } finally {
            if (updatableStmt != null) {
                updatableStmt.close();
            }
        }
    }
}

public static int DeleteItemImgStatus(String groupId){
    int status = 0;
    Connection connection = null;
    PreparedStatement ps = null;
    try{
        connection = DbConnection.getConnection();
        ps = connection.prepareStatement(
                "DELETE FROM " + "img_detect_status" + " WHERE " + group_id + "=?"
        );
        ps.setString(1, groupId);
        status = ps.executeUpdate();
    } catch (Exception ex){
        System.out.println("Gagal delete item : " + ex.toString());
    } finally {
        DbConnection.ClosePreparedStatement(ps);
        DbConnection.CloseConnection(connection);
    }
    return status;
}

private static void executeDeleteWithPreparedStatement(IPersistent object, DataSource dataSource)
throws SQLException, IllegalAccessException, InvocationTargetException {
    ClassMetaData classMetaData = ConfigurationService.getInstance().getMetaData(PersistentInstanceFactory.getActualPersistentClass(object).getName());
    String sql = PreparedStatementHelper.getDeletePreparedStatementString(classMetaData, dataSource.getEngineType());
    PreparedStatement pstmt = dataSource.getPreparedStatement(sql);

    int i = 0;
    for (Iterator itr = classMetaData.getAllKeyFieldNames().iterator(); itr.hasNext();) {
        ++i;
        String fieldName = (String) itr.next();
        Object value = MoldingService.getInstanceValue(object, classMetaData, fieldName);
        DataTranslator.setAppObject(pstmt, i, value, classMetaData.getSqlType(fieldName), dataSource.getEngineType());
    }

    dataSource.executeUpdate(pstmt);
}

public List<String> getTestNames() {
    try {
        return db.withConnection(new ConnectionAction<List<String>>() {
            public List<String> execute(Connection connection) throws SQLException {
            Set<String> testNames = Sets.newLinkedHashSet();
            PreparedStatement testIdsStatement = connection.prepareStatement("select distinct testId, testGroup from testExecution where resultType = ? order by testGroup, testId");
            testIdsStatement.setString(1, resultType);
            ResultSet testExecutions = testIdsStatement.executeQuery();
            while (testExecutions.next()) {
                testNames.add(testExecutions.getString(1));
            }
            testExecutions.close();
            testIdsStatement.close();
            return Lists.newArrayList(testNames);
            }
        });
    } catch (Exception e) {
        throw new RuntimeException(String.format("Could not load test history from datastore '%s'.", db.getUrl()), e);
    }
}

static void storeRecord(UUID entity, String entityType, Material material, byte blockData,
                        Location location, ItemStack holding) throws SQLException {
    try (Connection con = MySQLThreadPool.getInstance().getConnection()) {
        PreparedStatement bigSt = con.prepareStatement("INSERT INTO block_break_stat " +
                "(entity, entity_type, block_material, block_data, loc_world, loc_x, loc_y, loc_z, holding_item) VALUE " +
                "(UNHEX(?), ?, ?, ?, ?, ?, ?, ?, ?)");
        bigSt.setString(1, entity.toString().replace("-", ""));
        bigSt.setString(2, entityType);
        bigSt.setString(3, material.name());
        bigSt.setByte(4, blockData);
        bigSt.setString(5, location.getWorld().getName());
        bigSt.setInt(6, location.getBlockX());
        bigSt.setInt(7, location.getBlockY());
        bigSt.setInt(8, location.getBlockZ());
        bigSt.setString(9, Util.serialiseItemStack(holding));
        bigSt.execute();

        PreparedStatement smallSt = con.prepareStatement("INSERT INTO block_break_stat_simple " +
                "(player, material, block_data, amount) VALUE (UNHEX(?), ?, ?, ?) ON DUPLICATE KEY UPDATE amount=amount+VALUES(amount)");
        smallSt.setString(1, entity.toString().replace("-", ""));
        smallSt.setString(2, material.name());
        smallSt.setByte(3, blockData);
        smallSt.setInt(4, 1);
        smallSt.execute();
    }
}

/**
 * Remove function In List and in DB
 * @param functionType
 */
public void removeFunction(int functionType)
{
    _function.remove(functionType);
    try (Connection con = DatabaseFactory.getInstance().getConnection();
        PreparedStatement ps = con.prepareStatement("DELETE FROM fort_functions WHERE fort_id=? AND type=?"))
    {
        ps.setInt(1, getResidenceId());
        ps.setInt(2, functionType);
        ps.execute();
    }
    catch (Exception e)
    {
        _log.log(Level.SEVERE, "Exception: Fort.removeFunctions(int functionType): " + e.getMessage(), e);
    }
}

private Collection<MessageId> getMessageIds(Connection txn, GroupId g,
        State state) throws DbException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        String sql = "SELECT messageId FROM messages"
                + " WHERE state = ? AND groupId = ?";
        ps = txn.prepareStatement(sql);
        ps.setInt(1, state.getValue());
        ps.setBytes(2, g.getBytes());
        rs = ps.executeQuery();
        List<MessageId> ids = new ArrayList<MessageId>();
        while (rs.next()) ids.add(new MessageId(rs.getBytes(1)));
        rs.close();
        ps.close();
        return ids;
    } catch (SQLException e) {
        tryToClose(rs);
        tryToClose(ps);
        throw new DbException(e);
    }
}

public static Map<Material, Integer> getSimpleStats(UUID uuid) {
    EnumMap<Material, Integer> map = new EnumMap<>(Material.class);
    try (Connection con = MySQLThreadPool.getInstance().getConnection()) {
        PreparedStatement st = con.prepareStatement("SELECT material,block_data,amount FROM block_break_stat_simple WHERE player=UNHEX(?)");
        st.setString(1, uuid.toString().replace("-", ""));
        ResultSet set = st.executeQuery();
        while (set != null && set.next()) {
            Material mat = Material.getMaterial(set.getString("material"));
            int amount = set.getInt("amount");
            if (map.containsKey(mat)) {
                map.put(mat, map.get(mat) + amount);
            } else {
                map.put(mat, amount);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return map;
}

@Override
public long uploadPackage(DataPackage dataPack) {
    long time = System.currentTimeMillis();
    try {
        Connection conn = DriverManager.getConnection(DB_URL + DATABASE, USER, PASS);

        String sql = "INSERT INTO " + MAIN_TABLE + " ("+ COL_ID +", "+ COL_DATA + ", " + COL_DESC + ") values (?, ?, ?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setLong(1, time);
        statement.setBytes(2, dataPack.getData());
        statement.setString(3, dataPack.getDescription());

        int row = statement.executeUpdate();
        if (row > 0) {
            //Success
        }
        conn.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return time;
}

public void batchDeleteTimeTable(String[] id) {
    Connection con = DBconnection.getConnection();
    String sql="delete from timetable where id in(0"; 

    for(int i=0;i<id.length;i++) 
    { 
      sql+=","+id[i]; 
    } 
    sql+=")";                       
    PreparedStatement prep = null;      
    try {
        prep = con.prepareStatement(sql);               
        prep.executeUpdate();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } finally {
        DBconnection.close(con);
        DBconnection.close(prep);
    }
}

@Test
public void testGetCurrentSchema() throws Exception {
  Connection conn = getTestEnvConnection();
  try {
    String schema = OraOopOracleQueries.getCurrentSchema(conn);
    Assert.assertEquals(OracleUtils.ORACLE_USER_NAME.toUpperCase(), schema
        .toUpperCase());

    PreparedStatement stmt =
        conn.prepareStatement("ALTER SESSION SET CURRENT_SCHEMA=SYS");
    stmt.execute();

    schema = OraOopOracleQueries.getCurrentSchema(conn);
    Assert.assertEquals("SYS", schema);
  } finally {
    closeTestEnvConnection();
  }
}

@Override
public void removeContact(Connection txn, ContactId c)
        throws DbException {
    PreparedStatement ps = null;
    try {
        String sql = "DELETE FROM contacts WHERE contactId = ?";
        ps = txn.prepareStatement(sql);
        ps.setInt(1, c.getInt());
        int affected = ps.executeUpdate();
        if (affected != 1) throw new DbStateException();
        ps.close();
    } catch (SQLException e) {
        tryToClose(ps);
        throw new DbException(e);
    }
}

/**
 * Marshall the fields of a persistent instance to a prepared statement
 */
protected int dehydrate(Serializable id, Object[] fields, boolean[] includeProperty, PreparedStatement st, SessionImplementor session) throws SQLException, HibernateException {

    if ( log.isTraceEnabled() ) log.trace( "Dehydrating entity: " + MessageHelper.infoString(this, id) );

    int index = 1;
    for (int j=0; j<getHydrateSpan(); j++) {
        if ( includeProperty[j] ) {
            getPropertyTypes()[j].nullSafeSet( st, fields[j], index, session );
            index += propertyColumnSpans[j];
        }
    }

    if ( id!=null ) {
        getIdentifierType().nullSafeSet( st, id, index, session );
        index += getIdentifierColumnNames().length;
    }

    return index;

}

/**
 * Tests fix for BUG#5012 -- ServerPreparedStatements dealing with return of
 * DECIMAL type don't work.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug5012() throws Exception {
    PreparedStatement pStmt = null;
    String valueAsString = "12345.12";

    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
        this.stmt.executeUpdate("CREATE TABLE testBug5012(field1 DECIMAL(10,2))");
        this.stmt.executeUpdate("INSERT INTO testBug5012 VALUES (" + valueAsString + ")");

        pStmt = this.conn.prepareStatement("SELECT field1 FROM testBug5012");
        this.rs = pStmt.executeQuery();
        assertTrue(this.rs.next());
        assertEquals(new BigDecimal(valueAsString), this.rs.getBigDecimal(1));
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");

        if (pStmt != null) {
            pStmt.close();
        }
    }
}

public void insertindb(CPost cp)
{
    try (Connection con = DatabaseFactory.getInstance().getConnection();
        PreparedStatement ps = con.prepareStatement("INSERT INTO posts (post_id,post_owner_name,post_ownerid,post_date,post_topic_id,post_forum_id,post_txt) values (?,?,?,?,?,?,?)"))
    {
        ps.setInt(1, cp.postId);
        ps.setString(2, cp.postOwner);
        ps.setInt(3, cp.postOwnerId);
        ps.setLong(4, cp.postDate);
        ps.setInt(5, cp.postTopicId);
        ps.setInt(6, cp.postForumId);
        ps.setString(7, cp.postTxt);
        ps.execute();
    }
    catch (Exception e)
    {
        LOGGER.log(Level.WARNING, "Error while saving new Post to db " + e.getMessage(), e);
    }
}

@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);
}

public static void insertDepartamento(int codDeptoPais,  int numero_fila, int pais, String nombre, String abrev){
     Connection conect=conectar();
 String query = " insert into departamento (id, numero_fila, pais, nombre, abrev)"
            + " values (?, ?, ?,?,?)";
try {

    PreparedStatement preparedStmt;
    preparedStmt = conect.prepareStatement(query);
    preparedStmt.setInt (1, codDeptoPais);
    preparedStmt.setInt (2, numero_fila);
    preparedStmt.setInt (3, 1);
    preparedStmt.setString (4, nombre);
    preparedStmt.setString  (5, abrev);
    preparedStmt.execute();
    conect.close();
} catch (SQLException e) {e.printStackTrace();}
 }

/**
 * Change the clan crest. If crest id is 0, crest is removed. New crest id is saved to database.
 * @param crestId if 0, crest is removed, else new crest id is set and saved to database
 */
public void changeClanCrest(int crestId)
{
    if (crestId == 0)
        CrestCache.removeCrest(CrestType.PLEDGE, _crestId);

    _crestId = crestId;

    try (Connection con = L2DatabaseFactory.getInstance().getConnection())
    {
        PreparedStatement statement = con.prepareStatement("UPDATE clan_data SET crest_id = ? WHERE clan_id = ?");
        statement.setInt(1, crestId);
        statement.setInt(2, _clanId);
        statement.executeUpdate();
        statement.close();
    }
    catch (SQLException e)
    {
        _log.log(Level.WARNING, "Could not update crest for clan " + _name + " [" + _clanId + "] : " + e.getMessage(), e);
    }

    for (L2PcInstance member : getOnlineMembers(""))
        member.broadcastUserInfo();
}

/**
 * Verify that the data type mapping is correct for date columns.
 */
@Test
public void testCorrectDataTypeMappingDate() throws SQLException {
  // Given
  final PreparedStatement statement = mock(PreparedStatement.class, RETURNS_SMART_NULLS);
  when(connection.prepareStatement(anyString())).thenReturn(statement);

  // This is the list of tables that's returned.
  when(statement.executeQuery()).thenAnswer(new ReturnTablesMockResultSet(1)).thenAnswer(new ReturnTablesWithDateColumnMockResultSet(2));

  // When
  final Schema oracleMetaDataProvider = oracle.openSchema(connection, "TESTDATABASE", "TESTSCHEMA");
  assertEquals("Table names", "[AREALTABLE]", oracleMetaDataProvider.tableNames().toString());
  Column dateColumn = Iterables.find(oracleMetaDataProvider.getTable("AREALTABLE").columns(), new Predicate<Column>() {

    @Override
    public boolean apply(Column input) {
      return "dateColumn".equalsIgnoreCase(input.getName());
    }
  });
  assertEquals("Date column type", dateColumn.getType(), DataType.DATE);
}

public List<TriggerKey> selectMisfiredTriggersInState(Connection conn, String state,
        long ts) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        ps = conn.prepareStatement(rtp(SELECT_MISFIRED_TRIGGERS_IN_STATE));
        ps.setBigDecimal(1, new BigDecimal(String.valueOf(ts)));
        ps.setString(2, state);
        rs = ps.executeQuery();

        LinkedList<TriggerKey> list = new LinkedList<TriggerKey>();
        while (rs.next()) {
            String triggerName = rs.getString(COL_TRIGGER_NAME);
            String groupName = rs.getString(COL_TRIGGER_GROUP);
            list.add(triggerKey(triggerName, groupName));
        }
        return list;
    } finally {
        closeResultSet(rs);
        closeStatement(ps);
    }
}

@Override
public int delete(Pet pet)
{
    PreparedStatement psmt = null;
    int result = 0;

    try
    {
        String sql = "DELETE FROM tbl_pet WHERE id = ?";
        psmt = conn.prepareStatement(sql);
        psmt.setInt(1, pet.getId());

        result = psmt.executeUpdate();

    }
    catch(SQLException e)
    {
        LOGGER.catching(e);
    }
    finally
    {
        DBHelper.closeStatement(psmt);
    }

    return result;
}

public static void insertDemografia(Demografia demografia, String usuarioResponsable){
    try {
        Connection conn=ConnectionConfiguration.conectar();

        String query = " insert into demografia (id,nombre,descipcion,tipo_demografica_id_abrev,usuario_responsable)"
        + " values (?, ?, ?, ?, ?, ?)";

        PreparedStatement insert = conn.prepareStatement(query);
        insert.setInt (1, demografia.getId());
        insert.setString (2, demografia.getNombre());
        insert.setString (3, demografia.getDescripcion());
        insert.setInt (4, demografia.getTipo_demografica_id());
        insert.setString (5, demografia.getAbrev());
        insert.setString (6, usuarioResponsable);

        insert.execute();

        conn.close();
    } catch (SQLException e) {e.printStackTrace();}
}

public static int DeleteItem(String groupId, String idd){
    int status = 0;
    Connection connection = null;
    PreparedStatement ps = null;
    try{
        connection = DbConnection.getConnection();
        ps = connection.prepareStatement(
                "DELETE FROM " + groupId + " WHERE " + id + "=?"
        );
        ps.setString(1, idd);
        status = ps.executeUpdate();
    } catch (Exception ex){
        System.out.println("Gagal delete item : " + ex.toString());
    } finally {
        DbConnection.ClosePreparedStatement(ps);
        DbConnection.CloseConnection(connection);
    }
    return status;
}

/**
 * <p>
 * Select the distinct instance names of all fired-trigger records.
 * </p>
 * 
 * <p>
 * This is useful when trying to identify orphaned fired triggers (a 
 * fired trigger without a scheduler state record.) 
 * </p>
 * 
 * @return a Set of String objects.
 */
public Set selectFiredTriggerInstanceNames(Connection conn) 
    throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        Set instanceNames = new HashSet();

        ps = conn.prepareStatement(rtp(SELECT_FIRED_TRIGGER_INSTANCE_NAMES));
        rs = ps.executeQuery();

        while (rs.next()) {
            instanceNames.add(rs.getString(COL_INSTANCE_NAME));
        }

        return instanceNames;
    } finally {
        closeResultSet(rs);
        closeStatement(ps);
    }
}

public ResultSet query(Object... args) throws SQLException {

            PreparedStatement st = null;
            ResultSet rs = null;
            Connection conn = null;

            try {
                conn = fac.getConnection();
                st = conn.prepareStatement(sqlTemplate);
                if (null != args && args.length > 0) {
                    int index = 1;
                    for (Object arg : args) {
                        setPrepareStatementParam(st, index, arg);
                        index++;
                    }
                }

                rs = st.executeQuery();
                return rs;
            }
            catch (SQLException e) {
                throw e;
            }
            finally {
                bulidDAOThreadContext(conn, st, rs);
            }
        }

@Override
public void insertUserRoles(final int userId, final int rolesId) {
    String sql = "insert into user_roles(users_id,roles_id) values (?,?) ";
    this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setInt(1, userId);
            ps.setInt(2, rolesId);
        }
    });
}

@Override
public void updateAndataRitornoPartita(int idPartita, String nomeTorneo, int annoTorneo, String nuovaAndataRitorno) throws RemoteException {
    try{
        query= "UPDATE PARTITA\n "
                + "SET ANDATARIToRNO = '" + nuovaAndataRitorno.toUpperCase() + "'\n "
                + "WHERE IDPARTITA = '" + idPartita + "' AND NOMETORNEO = '" + nomeTorneo.toUpperCase() + "' AND ANNOTORNEO = '" + annoTorneo + "' ;";
        PreparedStatement posted = DatabaseConnection.connection.prepareStatement(query);
        posted.executeUpdate(query);
    }catch(SQLException ex){
        System.out.println("ERROR:" + ex);
    }
}

public int addDocumentScopeAnnotation(int doc_id, String annType, Map<String, List<String>> features) throws ProcessingException {
    PreparedStatement addAnnStatement = getPreparedStatement(PreparedStatementId.AddAnnotation);
    setAddAnnotationStatementValues(addAnnStatement, AnnotationKind.TextBound, IMPORTED_ANNSET_NAME, ALVISNLP_NS, annType);
    int ann_id = executeStatementAndGetId(addAnnStatement);
    @SuppressWarnings("unused") // XXX
    int tscope_id = addTextScope(ann_id, doc_id, null, null, null, 0, Document_TextScope);
    addAnnotationProps(ann_id, features, null);
    return ann_id;
}

public void setBinaryStream(int parameterIndex, InputStream x, long length) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((PreparedStatement) this.wrappedStmt).setBinaryStream(parameterIndex, x, length);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}

public int deleteExtendedTriggerProperties(Connection conn, TriggerKey triggerKey) throws SQLException {
    PreparedStatement ps = null;

    try {
        ps = conn.prepareStatement(Util.rtp(DELETE_SIMPLE_PROPS_TRIGGER, tablePrefix, schedNameLiteral));
        ps.setString(1, triggerKey.getName());
        ps.setString(2, triggerKey.getGroup());

        return ps.executeUpdate();
    } finally {
        Util.closeStatement(ps);
    }
}

@Override
public void insertAddressBatch(Set<Address> addressSet) throws Exception{       
        Address.ADDRESS_TYPE type = addressSet.iterator().next().getType();
        String sql = type == Address.ADDRESS_TYPE.TRANSPARENT ? sqlInsertPublicAddress : sqlInsertPrivateAddress;
        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (Address address : addressSet) {
            pstmt.setString(1, address.getPrivateKey());
            pstmt.setString(2, address.getAddress());
            pstmt.setDouble(3, Double.parseDouble(address.getBalance()));
            pstmt.setString(4, "");
            pstmt.execute();
        }
}

public Long selectMaxPointTimestampByNameAndAggregation(String metricName, String aggregation, Granularity granularity) {
    String sql = String.format(SQL_SELECT_MAX_POINT_TIMESTAMP_BY_NAME_AND_AGGREGATION, granularity);
    Long max = null;

    try (PreparedStatement statement = getConnection().prepareStatement(sql)) {
        statement.setString(1, metricName);
        statement.setString(2, aggregation);
        ResultSet resultSet = statement.executeQuery();

        if (resultSet.next()) {
            Timestamp timestamp = resultSet.getTimestamp("max");

            if (timestamp != null) {
                max = timestamp.getTime();
            }
        }
    } catch (SQLException e) {
        throw new PugSQLException("Cannot select max timestamp of metric %s point aggregated as %s with granulairty %s and statment %s",
                                  metricName,
                                  aggregation,
                                  granularity,
                                  sql,
                                  e);
    }

    return max;
}

@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getCarts =
         databaseConnection.prepareStatement ("SELECT ID FROM PRODUCTCARTS");
      ResultSet res = getCarts.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updateCart =
            databaseConnection
               .prepareStatement ("UPDATE PRODUCTCARTS SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updateCart.execute ();
         updateCart.close ();

         PreparedStatement updateProductsLink =
            databaseConnection
               .prepareStatement ("UPDATE CART_PRODUCTS SET CART_UUID = '" +
                  uuid + "' WHERE CART_ID = " + res.getObject ("ID"));
         updateProductsLink.execute ();
         updateProductsLink.close ();
      }
      getCarts.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }

}

/**
 * 根据软件编号获取截图数据
 * 
 * @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);
}

private void save(Connection con) throws SQLException {
    try (PreparedStatement pstmt = con.prepareStatement("INSERT INTO asset (id, account_id, name, "
            + "description, quantity, decimals, height) VALUES (?, ?, ?, ?, ?, ?, ?)")) {
        int i = 0;
        pstmt.setLong(++i, this.getId());
        pstmt.setLong(++i, this.getAccountId());
        pstmt.setString(++i, this.getName());
        pstmt.setString(++i, this.getDescription());
        pstmt.setLong(++i, this.getQuantityQNT());
        pstmt.setByte(++i, this.getDecimals());
        pstmt.setInt(++i, Nxt.getBlockchain().getHeight());
        pstmt.executeUpdate();
    }
}

/** Clear all registered siege clans from database for castle */
public void clearSiegeClan()
{
    try (Connection con = DatabaseFactory.getInstance().getConnection();
        PreparedStatement statement = con.prepareStatement("DELETE FROM siege_clans WHERE castle_id=?"))
    {
        statement.setInt(1, getCastle().getResidenceId());
        statement.execute();

        if (getCastle().getOwnerId() > 0)
        {
            try (PreparedStatement delete = con.prepareStatement("DELETE FROM siege_clans WHERE clan_id=?"))
            {
                delete.setInt(1, getCastle().getOwnerId());
                delete.execute();
            }
        }

        getAttackerClans().clear();
        getDefenderClans().clear();
        getDefenderWaitingClans().clear();
    }
    catch (Exception e)
    {
        _log.log(Level.WARNING, getClass().getSimpleName() + ": Exception: clearSiegeClan(): " + e.getMessage(), e);
    }
}

public void updateDB()
{
    try (Connection con = DatabaseFactory.getInstance().getConnection();
        PreparedStatement statement = con.prepareStatement(UPDATE_CLANHALL))
    {
        statement.setInt(1, getOwnerId());
        statement.setLong(2, getPaidUntil());
        statement.setInt(3, getResidenceId());
        statement.execute();
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
}

@Override
public void run(OrgaoOrgaoUnidadeUnidadeAcervoGetRequest req,
        OrgaoOrgaoUnidadeUnidadeAcervoGetResponse resp) throws Exception {
    resp.list = new ArrayList<>();

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
        conn = Utils.getConnection();
        pstmt = conn.prepareStatement(Utils.getSQL("acervo"));
        pstmt.setInt(1, Integer.valueOf(req.orgao));
        pstmt.setInt(2, Integer.valueOf(req.unidade));
        rset = pstmt.executeQuery();

        while (rset.next()) {
            Indicador o = new Indicador();
            o.nome = rset.getString("NOME");
            o.descricao = rset.getString("DESCRICAO");
            o.valor = rset.getDouble("VALOR");
            o.memoriaDeCalculo = rset.getString("MEMORIA_DE_CALCULO");
            resp.list.add(o);
        }
    } finally {
        if (rset != null)
            rset.close();
        if (pstmt != null)
            pstmt.close();
        if (conn != null)
            conn.close();
    }
}