Auto generate id in Spring MVC

Question

I'm trying to save data collected by a JSP page within a database (Postgres). At first I tried to insert any values manually (including id) in a form and I had no problem saving the data in my db. Now I'm trying to auto generate the id values, but I'm a little bit confused about how to do it properly.

My model - Product

public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String description;
    private double price;
    @DateTimeFormat(pattern = "dd/MM/yyyy")
    private Date date;

    public Product() { }

    public Product(Long id, String description, double price, Date date) {
        this.id = id;
        this.description = description;
        this.price = price;
        this.date = date;
    }
    //getters and setters
}

My controller - DBConnection

@Controller
public class DBConnection {

    @Autowired
    private ProductDao prDao;

    @RequestMapping(value = "/newproduct", method = RequestMethod.GET)
    public ModelAndView showForm() {
        Product product = new Product();
        return new ModelAndView("newproduct", "product", product);
    }

    @RequestMapping(value = "/newproduct", method = RequestMethod.POST)
    public ModelAndView submitForm(@ModelAttribute("product") Product product) {
        ModelAndView mav = new ModelAndView();

        prDao.addProduct(product.getId(), product.getDescription(), product.getPrice(), product.getDate());
        mav.setViewName("product");
        mav.addObject("product", product);
        return mav;
    }
}

ProductDaoImpl

public class ProductDaoImpl implements ProductDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    @Override
    public void setDataSource(DataSource ds) {
        this.dataSource = ds;
        this.jdbcTemplate = new JdbcTemplate(dataSource);

    }

    @Override
    public void addProduct(Long id, String description, double price, Date date) {
        jdbcTemplate.update("INSERT INTO products values(?, ?, ?, ?)", id, description, price, date);
    }
}

My form in newproduct.jsp

<form:form method="POST" action="newproduct" modelAttribute="product">
    <table>
        <tr>
            <td><form:label path="description">Description</form:label></td>
            <td><form:input path="description" /></td>
        </tr>
        <tr>
            <td><form:label path="price">Price</form:label></td>
            <td><form:input path="price" /></td>
        </tr>
        <tr>
            <td><form:label path="date">Date (dd/mm/yyyy)</form:label></td>
            <td><form:input path="date" /></td>
        </tr>
        <tr>
            <td><input type="submit" value="Submit" /></td>
        </tr>
    </table>
</form:form>

Error when I submit the form

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO products values(?, ?, ?, ?)]; ERROR: null values in column "id" violates not-null constraint
  Detail: The row contains error (null, nnvfe, 10.00, 2010-10-10).; nested exception is org.postgresql.util.PSQLException: ERROR: null values in column "id" violates not-null constraint
  Detail: The row contains error (null, nnvfe, 10.00, 2010-10-10).

I think the problem is in the addProduct method: the id value isn't yet created when I tried to get it.

How can I implements an auto generated id? Is the JPA annotation the correct way to do that thing?

Solution

Now I'm trying to auto generate the id values, but I'm a little bit confused about how to do it properly

If you want to automatically generate the id, then do not provide one, even it's null. So, remove the id from addProduect method:

@Override
public void addProduct(String description, double price, Date date) {
    jdbcTemplate.update("INSERT INTO products values(?, ?, ?)", description, price, date);
}

Also, make your id field auto-increment, as in this question.

Or, change the addProduct method and use EntityManager#persist method:

@Override
public void addProduct(Product product) {
    entityManager.persist(product);
}

About the error:

ERROR: null values in column "id" violates not-null constraint

Since your form does not take the id value from the client, the /newproduct endpoint would have a Product with null as its id value:

@RequestMapping(value = "/newproduct", method = RequestMethod.POST)
public ModelAndView submitForm(@ModelAttribute("product") Product product) { ... }

Then you pass this null value as the parameter to the addProduct method:

prDao.addProduct(product.getId(), ...)

And finally addProduct would try to save that null value as the value of the Primary Key, which has a Non-Null constraint, so you're got that error.

Also, using Entity objects as the means of communication with client, e.g. Product, is not a good practice. Try to define some auxiliary abstractions like Forms or DTOs and use them for form handling or response assembling.

Comments

[Ali Dehghani]

Oh yeah..Do not use JdbcTemplate, unless you configured the db field as auto-increment. Use Session's save method.

[andy]

I have already tried this solution but doesn't work.. I get this error: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO products values(?, ?, ?)]; nested exception is org.postgresql.util.PSQLException: ERROR: the "id" column is of type bigint but the expression is of type character varying It seems as if it's trying to put the description field into the id field. Thank tou for the tip about the Entity objects.

[Ali Dehghani]

INSERT INTO products values(?, ?, ?) would work only if you define your id as autoincrement in Postgre.

[andy]

Thank you, fixed! I had to change my addProduct method too: jdbcTemplate.update("INSERT INTO products(description, price, date) values(?, ?, ?)", description, price, date);

[Ali Dehghani]

You can also use Spring Data JPA or even Spring Boot, if you need nicer abstractions for Data Access

[andy]

Which is the de facto standard in this case?

[Ali Dehghani]

Spring Boot is just an umbrella project and uses the likes of Data JPA, Security, etc. under the hood. So, i highly recommend to take look at Spring Boot and use it for your new projects