imoteb imoteb - 3 months ago 49
MySQL Question

Java Spring JDBC last_insert_id()

I have a web service and a database(MySQL), I made a table called batch which must generate each time I add some value, an unique key.

CREATE TABLE batch (
id INT AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(250) NOT NULL
);


This is the code for the database table and here you are what I wrote so far:

static final String addBatchIdSql = "INSERT INTO batch" + "(description)" + "VALUES (?)";
static final String getBatchIdSql = "SELECT LAST_INSERT_ID()";

@Override
public int getBatchId() {
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String batchIDcreation = dateFormat.format(new Date());
jdbcTemplate.update(addBatchIdSql, batchIDcreation);
SqlRowSet rowSet = jdbcTemplate.queryForRowSet(getBatchIdSql);
while (rowSet.next()) {
System.out.println(rowSet.findColumn("id"));
}
return 0;
}


The problem is that each time I try to get the id it throws exception which states "invalid column name", I have checked the naming properties in my application in general and could not find any issue, what could be wrong here ://

Answer

You can try aliasing the column which is returned from your application's call to LAST_INSERT_ID():

static final String getBatchIdSql = "SELECT LAST_INSERT_ID() AS id";

Full code:

static final String addBatchIdSql = "INSERT INTO batch" + "(description)" + "VALUES (?)";
static final String getBatchIdSql = "SELECT LAST_INSERT_ID() AS id";

@Override
public int getBatchId() {
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String batchIDcreation = dateFormat.format(new Date());
    jdbcTemplate.update(addBatchIdSql, batchIDcreation);
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(getBatchIdSql);
    if (rowSet.next()) {
        System.out.println(rowSet.findColumn("id"));
    }
    return 0;
}