package tk.puncha.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import tk.puncha.models.Owner;

import javax.sql.DataSource;
import java.util.List;

@Component
public class OwnerDAO extends JdbcDaoSupport {

  private final String SQL_QUERY_ALL = "SELECT * FROM owners";
  private final String SQL_QUERY_BY_ID = "SELECT * FROM owners WHERE id = ?";
  private final String SQL_DELETE_BY_ID = "DELETE FROM owners WHERE id = ?";
  private final String SQL_INSERT = "INSERT INTO owners(first_name, last_name, address, city, telephone) VALUES (?,?,?,?,?)";
  private final String SQL_UPDATE = "UPDATE owners SET first_name = ?, last_name = ?, address = ?, city = ?, telephone = ? WHERE id = ?";

  private RowMapper<Owner> ownerRowMapper;


  @Autowired
  public OwnerDAO(DataSource dataSource) {
    setDataSource(dataSource);

    ownerRowMapper = (rs, rowNum) -> {
      Owner owner = new Owner();
      owner.setId(rs.getInt("id"));
      owner.setFirstName(rs.getString("first_name"));
      owner.setLastName(rs.getString("last_name"));
      owner.setAddress(rs.getString("address"));
      owner.setCity(rs.getString("city"));
      owner.setTelephone(rs.getString("telephone"));
      return owner;
    };
  }

  public List<Owner> getAllOwners() {
    return this.getJdbcTemplate().query(SQL_QUERY_ALL, ownerRowMapper);
  }

  public Owner getOwnerById(int ownerId) {
    return this.getJdbcTemplate().queryForObject(SQL_QUERY_BY_ID, ownerRowMapper, ownerId);
  }

@Transactional
public Owner insertOwner(Owner owner) {
  this.getJdbcTemplate().update(
      SQL_INSERT, owner.getFirstName(), owner.getLastName(),
      owner.getAddress(), owner.getCity(), owner.getTelephone());

  int id = getJdbcTemplate().queryForObject(
      "CALL IDENTITY()", (rs, rowNum) -> {
        int identity = rs.getInt(1);
        logger.info("The generated id is: " + identity);
        return identity;
      });
  owner.setId(id);
  return owner;
}

  
  public Owner updateOwner(Owner owner) {
    this.getJdbcTemplate().update(
        SQL_UPDATE, owner.getFirstName(), owner.getLastName(),
        owner.getAddress(), owner.getCity(), owner.getTelephone(),
        owner.getId());
    return owner;
  }

  // Note, owners table has a foreign key constraint,
  // the caller should make sure the owner's pets are
  // deleted.
  @Transactional
  public void deleteOwner(int id) {
    this.getJdbcTemplate().update(SQL_DELETE_BY_ID, id);
  }


}

@@ -128,16 +128,78 @@
 Mapper;%0A
+import org.springframework.jdbc.core.simple.SimpleJdbcInsert;%0A
 import o
@@ -247,24 +247,24 @@
 DaoSupport;%0A
-
 import org.s
@@ -426,16 +426,42 @@
 Source;%0A
+import java.util.HashMap;%0A
 import j
@@ -757,134 +757,8 @@
 ?%22;%0A
-  private final String SQL_INSERT = %22INSERT INTO owners(first_name, last_name, address, city, telephone) VALUES (?,?,?,?,?)%22;%0A
   pr
@@ -892,16 +892,57 @@
  = ?%22;%0A%0A
+  private SimpleJdbcInsert insertActor;%0A%0A
   privat
@@ -1064,16 +1064,123 @@
 Source);
+%0A    insertActor = new SimpleJdbcInsert(dataSource).withTableName(%22owners%22).usingGeneratedKeyColumns(%22id%22);
 %0A%0A    ow
@@ -1819,31 +1819,18 @@
 );%0A  %7D%0A%0A
-@Transactional%0A
+  
 public O
@@ -1867,77 +1867,151 @@
 %7B%0A  
-this.getJdbcTemplate().update(%0A      SQL_INSERT, owner.getFirstName()
+  HashMap%3CString, Object%3E parameters = new HashMap%3C%3E(5);%0A    parameters.put(%22first_name%22, owner.getFirstName());%0A    parameters.put(%22last_name%22
 , ow
@@ -2031,51 +2031,87 @@
 me()
-,
+);
 %0A    
-  owner.getAddress(), owner.getCity()
+parameters.put(%22address%22, owner.getAddress());%0A    parameters.put(%22city%22
 , ow
@@ -2121,240 +2121,138 @@
 .get
-Telephone
+City
 ());%0A
-%0A
   
-int id = getJdbcTemplate().queryForObject(%0A      %22CALL IDENTITY()%22, (rs, rowNum) -%3E %7B%0A        int identity = rs.getInt(1);%0A        logger.info(%22The generated id is: %22 + identity);%0A        return identity;%0A      %7D);%0A
+  parameters.put(%22telephone%22, owner.getTelephone());%0A    Number newId = insertActor.executeAndReturnKey(parameters);%0A  
   ow
@@ -2261,21 +2261,32 @@
 r.setId(
-i
+(int) newI
 d);%0A
+  
   return
@@ -2293,21 +2293,20 @@
  owner;%0A
-%7D%0A%0A
   
+%7D%0A
 %0A  publi

Use `SimpleJdbcInsert` to rewrite owner insert codes.