SimpleJdbcInsert equivalent for update

Question

I am using Spring's SimpleJdbcInsert class to create entities - eg:

final SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource).withTableName("abc");

insert.execute(new BeanPropertySqlParameterSource(abc));

Is there some equivalent of this class for doing updates? As an example, something like the below would be a convenient interface, assuming we are dealing with a single column primary key:

final SimpleJdbcUpdate update = new SimpleJdbcUpdate(dataSource).withTableName("abc").withIdColumn("abcId");

update.execute(new BeanPropertySqlParameterSource(abc));

Does Spring provide this functionality out-of-the-box somewhere?

Thanks Jay

Solution 1

You have to use JdbcTemplate

See: 13.2.1.1 Examples of JdbcTemplate class usage

E.X:

this.jdbcTemplate.update(
    "update t_actor set = ? where id = ?", 
    "Banjo", 5276L);

Solution 2

For any future readers - I came up with a convenience function using reflection;

Works for simple pojos:

public void dao_update(NamedParameterJdbcTemplate database, String table, Object pojo, String[] keys) {

        StringBuilder sqlBuilder = new StringBuilder("UPDATE ");
        sqlBuilder.append(table);
        sqlBuilder.append(" SET ");
        boolean first = true;
        for (Field field : pojo.getClass().getDeclaredFields()) {
            if (!first) {
                sqlBuilder.append(",");
            }
            first = false;
            sqlBuilder.append(field.getName());
            sqlBuilder.append(" = :");
            sqlBuilder.append(field.getName());
        }


        first = true;
        for (String key : keys) {
            if (first) {
                sqlBuilder.append(" WHERE ");
            } else {
                sqlBuilder.append(" AND ");
            }
            first = false;
            sqlBuilder.append(key);
            sqlBuilder.append("= :");
            sqlBuilder.append(key);
        }
        database.getJdbcOperations().update(sqlBuilder.toString(), new BeanPropertySqlParameterSource(pojo));
    }

Example usage:

dao_update(database, "employee", my_employee, "id");

Generates:

UPDATE employee SET id = :id, name = :name, salary = :salary WHERE id = :id

Solution 3

There is an issue in the Spring JIRA about the lack of a SimpleJdbcUpdate class: https://jira.springsource.org/browse/SPR-4691. You might want to upvote it there.

Solution 4

You can get more similar effect by using SimpleJdbcTemplate instead of JdbcTemplate and by extending SimpleJdbcDaoSupport all DB operations can be put in one DAO class:

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

@Repository
public class BankDaoImpl extends SimpleJdbcDaoSupport implements BankDao {


    @Autowired
    public BankDaoImpl(@Qualifier("dataSource") DataSource dataSource) {
        setDataSource(dataSource);
    }

    @Override
    public void insert(Bank bank) {
        String sql = "INSERT INTO BANK (id, oib, short_name, name, street, town, postal_code, homepage_url, last_change) VALUES (NEXT VALUE FOR bank_seq, :oib, :shortName, :name, :street, :town, :postalCode, :homepageUrl, CURRENT_TIMESTAMP)";
        SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(
                bank);

        getSimpleJdbcTemplate().update(sql, parameterSource);
    }

    @Override
    public void update(Bank bank) {
        String sql = "UPDATE BANK SET oib=:oib, short_name=:shortName, name=:name, street=:street, town=:town, postal_code=:postalCode, homepage_url=:homepageUrl, last_change=CURRENT_TIMESTAMP WHERE id=:id";
        SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(
                bank);

        getSimpleJdbcTemplate().update(sql, parameterSource);
    }

    @Override
    public void delete(String id) {
        String sql = "DELETE FROM BANK WHERE id=:id";

        getSimpleJdbcTemplate().update(sql,
                new MapSqlParameterSource("id", id));
    }

    @Override
    public Bank findById(String id) {
        String sql = "select b.ID, b.OIB, b.SHORT_NAME, b.NAME, b.STREET, b.TOWN, b.POSTAL_CODE, b.HOMEPAGE_URL, b.LAST_CHANGE, CASE WHEN count(f.id) = 0 THEN 0 ELSE 1 END AS ready " +
                "from BANK WHERE b.ID = :id"; 

        return getSimpleJdbcTemplate().queryForObject(sql,
                BeanPropertyRowMapper.newInstance(Bank.class),
                new MapSqlParameterSource("id", id));
    }
}

Solution 5

The easy way to do this is:(source)

public void setName(int id, String name) {
    this.jdbcTemplate.update("update mytable set name = ? where id = ?", 
    new Object[] {name, new Integer(id)});
}

View more solutions

Comments

[James Hargreaves]

Thanks JavaGeek. I was hoping there would be something more convenient - I am working with a table with 50 columns, clearly specifying these in the bean AND in the update statement is duplication. Maybe I'll take a look at SimpleJdbcInsert and see how they implement the insert...

[Michael W]

Personally Id be using hibernate and JPA. All you need then is a java pojo (domain object) to map to your table and some connection config in the spring xml. After that the statements are very simple and you can also use native queries similiar to the above.

[James Hargreaves]

Ha, yes well I would like to use JPA too :) In fact I have been using JPA on this project but have had to ditch it as the server we are using is archaic and the default JPA implementation (OpenJPA 1.0.0) is buggy...

[the hand of NOD]

@JamesHargreaves: You can also use Spring Data JDBC which does not have the complexity of JPA but also the useful SpringRepositories for an easier handling of beans. see: spring.io/projects/spring-data-jdbc