CODEWITHSUNDEEP
javamysqlsqlspring
Nik
Nik

Reputation: 7273

Spring JdbcTemplate Cannot Get Insert ID from MySQL

I am trying to insert a row into a MySQL table and get it's insert ID. I am aware of the MySQL last_insert_id() function, I just cannot seem to get it to work. Currently, I am trying to use a function annotated as a transaction and I am only getting 0 returned. I am using Spring 3.1.

    @Transactional (propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    private long insertTransactionRecord 
    (
        int custID, 
        int porID, 
        String date, 
        short crvID
    ) {
        m_template.update ("INSERT INTO " +
                           "    transaction " +
                           "( " +
                           "    por_id, " +
                           "    cust_id, " +
                           "    trans_date, " +
                           "    crv_id " +
                           ") " +
                           "VALUES " +
                           "( " +
                           "    ?, " +
                           "    ?, " +
                           "    ?, " +
                           "    ? " +
                           ")",
                           new Object[] {
                               porID,
                               custID,
                               date,
                               crvID
                           });
        return m_template.queryForLong ("SELECT " +
                                        "    last_insert_id() " +
                                        "FROM " +
                                        "    transaction " +
                                        "LIMIT 1");
    }

Upvotes: 6

Views: 15280

Answers (3)

pap
pap

Reputation: 27614

Use Spring's built-in support for this rather than doing it yourself.

SqlUpdate insert = new SqlUpdate(ds, "INSERT INTO company (name) VALUES (?)");
insert.declareParameter(new SqlParameter(Types.VARCHAR)); 
insert.setReturnGeneratedKeys(true);
// assuming auto-generated col is named 'id'
insert.setGeneratedKeysColumnNames(new String[] {"id"}); 
insert.compile();
....
GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
insert.update(new Object[]{"test"}, keyHolder);
System.out.println(keyHolder.getKey().longValue());

Upvotes: 9

JB Nizet
JB Nizet

Reputation: 691735

The documentation reveals that the syntax for getting the last insert ID is:

SELECT LAST_INSERT_ID()

Upvotes: 1

Sergey Benner
Sergey Benner

Reputation: 4431

taken from here http://www.codefutures.com/spring-dao/

public int createCompany(Company company) throws SQLException {
        jdbcTemplate.update(
                "INSERT INTO company (name) VALUES (?)",
                company.getName()
        );
        return jdbcTemplate.queryForInt( "select last_insert_id()" );
    }

if you noticed there's no FROM there

Upvotes: 8

Related Questions