1

I'm trying to make an insert and get the auto generated id returned. This is an Oracle database. For that i am using org.apache.commons.dbutils.QueryRunner insert which returns the first column.

The problem is i don't know for which reason i am getting added a parameter in the query and it won't let it work.

I have this code:

import javax.annotation.Nonnull;
import javax.inject.Inject;
import javax.inject.Named;
import javax.inject.Singleton;
import javax.sql.DataSource;     
import java.sql.SQLException;
import java.util.Optional;

@Singleton
public class ConceptToBonusRepository extends GenericSQLRepository{

    private static final String QUERY_SAVE_CONCEPT_TO_BONUS = "INSERT INTO concept_to_bonus (ID, CONCEPT_ID, CONCEPT_TYPE)" +
        " VALUES (SEQ_CONCEPT_TO_BONUS_ID.NEXTVAL,?,?)";

    @Inject
    public ConceptToBonusRepository(@Named("OracleDataSource") @Nonnull DataSource dataSource) {
        super(dataSource);
    }

    public Optional<Long> saveConceptToBonus(ConceptToBonus conceptToBonus) 

        try {
            return Optional.ofNullable(
               runInsert(
                    QUERY_SAVE_CONCEPT_TO_BONUS, conceptToBonus.getConceptId(), conceptToBonus.getConceptType()
               )
           );
        } catch (SQLException e) {
        throw new RuntimeException(e);
        }

    }
}

and my GenericlSQLRepository

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.annotation.Nonnull;
import javax.sql.DataSource;
import java.sql.SQLException;

public abstract class GenericSQLRepository {

    private QueryRunner queryRunner;
    private ScalarHandler<Long> autogeneratedIdHandler = new ScalarHandler<>();

    protected GenericSQLRepository(@Nonnull final DataSource dataSource) {
        this.queryRunner = new QueryRunner(dataSource);
    }

    protected Long runInsert(@Nonnull final String sql,
                            Object... args) throws SQLException {
        return queryRunner.insert(sql, this.autogeneratedIdHandler, args);
    }
}

When i try to run this i get this error

"java.sql.SQLException: Wrong number of parameters: expected 3, was given 2 Query: INSERT INTO concept_to_bonus (ID, CONCEPT_ID, CONCEPT_TYPE) VALUES (SEQ_CONCEPT_TO_BONUS_ID.NEXTVAL,?,?) Parameters: [1731472066, ORDER]"

I really don't understand why is it adding a parameter in the parameter count. When i run this insert with a simple execute, it works just fine

  • Try `.... autogeneratedIdHandler = new ScalarHandler<>(1);` using `1` as parameter to indicate that 1st column in this query is autogenerated and should be retrieved by the handler. – krokodilko Aug 24 '18 at 05:13
  • @krokodilko Same result, if you see `new ScalarHandler<>()` default constructor sets column index as 1 and would be the same as doing `new ScalarHandler<>(1)` – Sebastian Kondek Aug 24 '18 at 16:11

0 Answers0