CODEWITHSUNDEEP
javasqlspring-mvcspring-jdbcjdbctemplate
jhone
jhone

Reputation: 85

How to write one query insert data to table and update another one table using spring jdbc template

I have two data table category and icon. Category table has a one column iconId it foreign key from the icon table.Now I want to insert data into category table and update icon table flag column how to do it in sping jdbc template

private final String addCategorySql  ="INSERT INTO CATEGORY(TYPE,ICONID)"
        +" VALUES(?,?)  UPDATE ICON SET FLAG=? WHERE ICONID=? ";
public boolean addNewCategory(Category  category){
    Object [] parameters = {category.getType(),category.getIconId(),1,category.getIconId()};
    try {
        int rows = jdbcTemplate.update(addCategorySql, parameters);

        if (rows == 1) {
            return true;
        } else {
            return false;
        }
    } catch (Exception e) {
        logger.error("Exception  :  " , e);
        throw e;
    }

Upvotes: 1

Views: 1325

Answers (1)

Danila Zharenkov
Danila Zharenkov

Reputation: 1863

Why don't split the sql into 2 statments? It will be more clear and your can understand was the category inserted or not, and was the icon updated or not.

private final String addCategorySql = "INSERT INTO CATEGORY(TYPE,ICONID)"
        + " VALUES(?,?);"
private final String updateIconSql = "UPDATE ICON SET FLAG=1 WHERE ICONID=? ";

public boolean addNewCategory(Category category) {
    try {
        int updatedRows = 0;
        int insertedRows = jdbcTemplate.update(addCategorySql, category.getType(), category.getIconId());
        if (insertedRows == 1) { //we are updating Icon table only when any category inserted. Otherwise we return false;
            updatedRows = jdbcTemplate.update(updateIconSql, category.getIconId());
            if (updatedRows == 1) {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }
    } catch (Exception e) {
        logger.error("Exception  :  ", e);
        throw e;
    }
}

Upvotes: 1

Related Questions