# 03.5-Data Stores and Rersistence Lesson5:Persistence Without JPA
###### tags: `Udacity`
[ToC]

# 01 Introduction to Persistence without JPA
**Persistence Without JPA**
{%youtube 1mem6oToBgc%}

> Your are Here

**Lesson Outline**
* Initialization with SQL
* Data Object vs. Entities
* Data Access Objects (DAOs)
* JdbcTemplate
* Performance

> 簡單來說教你最原始的方式去做SQL 存取資料庫，而非JPA，也沒有Hibernate的幫助

# 02 Initialization with SQL
## Udacity
{%youtube s7gPjGda4-c%}
**SQL Initialization Scripts**
Spring attempts to execute two scripts by default when the application starts:
* schema.sql - Create or update the schema.
* data.sql - Initialize or modify the data in your tables.
The default directory for these files is src/main/resources

**Properties**
1. Still controlled by the same property:
- `spring.datasource.initialization-mode=[always|embedded|never]`

2. Make sure to disable hibernate initialization if you use these scripts to avoid conflicts:
- `spring.jpa.hibernate.ddl-auto=none`

**Platform-specific initialization**
1. You may provide additional initialization scripts using the naming pattern:
- schema-${platform}.sql
- data-${platform}.sql
2. By setting the platform property, you can control which scripts get loaded:
- `spring.datasource.platform=foo`

3. The above property will cause Spring to try and execute `schema-foo.sql` and `data-foo.sql`.

For additional information, see the [Spring boot documentation on initialization behavior](https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-initialize-a-database-using-spring-jdbc)

## ShannonNote
1. 為了測試方便，我們可以先設定`spring.datasource.initialization-mode=always`他的意思就是spring boot會使用建立的schema.sql檔案，自動產生資料庫結構，這很適合用於測試環境，但是請記住，他會每次在你重開機時就刪除你的資料。

2. 接下來建立`schema.sql`來建構資料庫table架構，如果你想要提供其他初始化設定`schema-${platform}.sql`的名稱就可以囉。如果你要使用請確認說你的`spring.jpa.hibernate.ddl-auto=none`因為在jpa-based app, 不能同時讓hibernate自動產生Shema又使用shcema.sql

3. 如果你建立了`data.sql`，那就表示會在`schema.sql`執行並建立好資料庫table架構之後，就會呼叫這個檔案，它的目的在載入初始資料進去剛建立好的資料庫，如果想要自己做額外的初始化資料設定，請使用這個格式`data-${platform}.sql`。

4. 如果你有設定這些自訂化的初始化檔案，像是`data-shannon.sql`或是`schema-shannon.sql`這種，記得在application.properties裡面設定`spring.datasource.platform=shannon`。這樣他才會找到你的那些資料。This allows you to switch to database-specific scripts if necessary, For example, you might choose to set it to the vendor name of the database (hsqldb, h2, oracle, mysql, postgresql, and so on).

> data.sql 跟 schema.sql請存在`src/main/resources`裡面

- 重新複習一下`spring.jpa.hibernate.ddl-auto`的幾個用法
- create: 表示每次載入hibernate的時候就會刪除原本的表，然後根據model類來建立table，適合測試用...
- create-drop: 如果關掉sessionFactory，所有表就會刪除。
- update: 比較常用，如果要部屬的時候，他不會刪掉你的表，就算表的欄位改變，原本的資料就還在，但是如果部署到伺服器後錶的結構不會被馬上建立，要等第一次執行才會。寫專題可能會使用到
- validate: 他不會建立表，只會驗證一下目前的資料庫結構是否妥當還有對表進行比較，且還能夠插入新的資料。
- none: 不做任何反應，如果你已經設定好schema.sql我就建立你設定為none即可，否則如果用create它會自動建立schema.sql。

> 如果你想要在console看到Hibernate語法，那就必須餒設置成`spring.jpa.hibernate.ddl-auto=create || create-drop` 他才會自動產生schema，否則如果使用none就不會顯示在console，設定`spring.jpa.show-sql`也沒姣用。

# 03 Exercise1
For this exercise, we’re going to add a new table that’s not built by Hibernate. Our flower delivery company is branching out (heh) and acquired a candy business. We want to create a new table in our database that matches their desired format.

Create a schema.sql for your project that creates a new table called ‘candy’. It should have columns to support:
* Long id
* String name
* BigDecimal price

If you’re unsure of the syntax to do this, turn on the spring.jpa.show-sql property and look at what statements are used to generate similar tables in your existing project (like the ‘plant’ table). You could also use the `create table if not exists` syntax to avoid exceptions when re-running the script.

Now create an initialization script in data.sql that populates your candy table with some candy names and prices when the application starts. You can use the `INSERT IGNORE` syntax to ignore attempts to insert values that already exist. For example:

insert ignore into candy
   set id = 1,
       name = 'Gummy Badgers',
       price = '3.50';

# 04 Solution: Exercise 1
Make sure to check that your application can start repeatedly and doesn’t produce duplicate data. Note that your existing Entities are still created automatically by hibernate. This is okay since we’re using them both in the same project, but you would probably set ddl-auto to ‘none’ outside of a development environment.

Here are some sample schema.sql and data.sql files:
**schema.sql**

create table if not exists candy (
   id bigint not null,
   name nvarchar(255),
   price decimal(12,4),
   primary key (id)
);

**data.sql**

insert ignore into candy
   set id = 1,
       name = 'Gummy Badgers',
       price = '3.50';

insert ignore into candy
set id = 2,
   name = 'Chocolate CreepyDepartmentStoreMannequin',
   price = '18.50';

insert ignore into candy
set id = 3,
   name = 'Lemon Icosahedrons',
   price = '6.20';

insert ignore into candy
set id = 4,
   name = 'Halm',
   price = '2.99';

# 05 Data Object Design
{%youtube 7c6t_yH9Qlg%}
SQL-based persistence layers often use Data Objects to map to their database tables. Data Objects usually have a one-to-one mapping of table fields to object attributes, though projections are frequently used outside ORM as well.

The below images demonstrate how associations are often mapped into secondary id fields of Data Objects rather than populating the associated objects.

> Representing an Association in an Entity


> Representing an Association in a Data Object


其實就是如果要跟資料庫連結的entity就要設定一對多的群組型態，可是如果只是拿出來放到Java Object方便取用的話，那就不需要那個群組型態了。

# 06 Data Access Objects
{%youtube XEFG-pIvtjg%}

**DAO vs. Repository**
The Data Access Object (DAO) pattern is an alternative to the Repository pattern. A DAO is a programmatic interface to a table or collection of related tables. Whereas a Repository presents a collection of Entities that you modify to update the database, a DAO will often provide methods that expose various actions, along with standard CRUD operations.

> 之前學的都是使用repository存取資料庫，DAO也是另一種替代方案，他是一種interface，自己自訂method然後傳入必要的值，去資料庫更改，然後也只傳會必要的值回來

**Data Object Projection Scope**
Data Object scope can vary depending on the needs of the application. It is more common to project directly into DTOs from a DAO, but remember that @JSONView still works with Data Objects, so it is often still worth adhering to a similar set of boundaries to what you would observe with Entities.

> 之前看到的DTO 結合 View其實也可以跟DAO一起使用，DAO把回傳的值丟到DTO裡面，但是DTO是跟object一起使用別搞混囉。

**DAO pattern in Hibernate**
While the Repository pattern is common with Hibernate, because most modifications can be done simply by editing Entities, there is nothing that prevents us from using the DAO pattern in either a Hibernate or SQL-based persistence model.

> Comparison of Update - Repository vs. DAO

- 你可以發現使用repsoitory還是比較方便，因為他傳回來的是所有衣服，你可以一次性的更改，但是如果使用DAO能傳回來的值有限...得一個個更改，DAO不適合提供collection class interface，也無法回傳entities。但是可以塞在DTO裡面當作object使用。

# 07 Exercise2
We need to start putting our new candy table to work. Our customers want to be able to add candy to their flower deliveries. Each delivery could have multiple pieces of candy, and the same types of candy can, of course, be included on different deliveries. That means we want to represent a many-to-many relationship between candy and deliveries.

Let’s create a mapping table that can be used to add candy to our deliveries. We’ll call the table ‘candy_delivery’ and it should have a column for both candy_id and delivery_id.

Now let’s make a CandyData class. This class maps directly to the candy table. It should have one field for each column in the candy table.

Lastly, let’s define an interface for a DAO. Create an interface called CandyDAO. Add three method signatures:
1. Get a list of all the available candy
2. A method that allows you to add a candy item to a delivery by id
3. A method that lets you get a list of all the candy for a specific delivery

# 08 Solution: Exercise 2
First we’ll add a new create statement to our schema.sql:

create table if not exists candy_delivery (
   candy_id bigint not null,
   delivery_id bigint not null
);

Next, we’ll create a CandyData.java:

public class CandyData {
   private Long id;
   private String name;
   private BigDecimal price;
   /* also include getter and setters */
}

Lastly, we’ll add an interface for our CandyDAO.java:

public interface CandyDAO {
   List<CandyData> list();
   void addToDelivery(Long candyId, Long deliveryid);
   List<CandyData> findByDelivery(Long deliveryId);
}

Note: You may wish to add some foreign key constraints to your candy_delivery table. This could cause some conflicts with the default hibernate ddl-auto behavior if hibernate attempts to delete the delivery table after adding the foreign key relationship, but if you were defining all your tables in sql it would definitely be something to keep in mind. This example will only allow you to add rows to candy_delivery if the candy and delivery referenced exists, and deleting the delivery will automatically remove the corresponding row from candy_delivery.

create table if not exists candy_delivery (
   candy_id bigint not null,
   delivery_id bigint not null,
   foreign key (candy_id) references candy(id),
   foreign key (delivery_id) references delivery(id) on delete cascade
);

## ShannonNote
我發現，如果想要更新table裡面的欄位但是你的ddl又設定為none，有兩種方法，再schema.sql裡面先刪掉那個table然後再重新新增一次，或是直接去mysql使用sql語法刪掉`delete table plant.candy;`

# 09 JdbcTemplate
## Udacity
{%youtube 9CmW9Yk8gB0%}

**JdbcTemplate**
* `JdbcTemplate` functions like the EntityManager: it provides connections, executes queries, and manages transactions.
* `NamedParameterJdbcTemplate` allows you to use named parameters in your query string.

-- with standard JdbcTemplate
select * from person where id = ? and age >= ?

-- with NamedParameterJdbcTemplate
select * from person where id = :id and age >= :minAge

**queryForObject**
To query for a single object, you can use the `jdbcTemplate.queryForObject` method. This example takes three parameters:
1. Query String
2. A map of parameter names to parameter values
3. A RowMapper that returns the instance of the object for which we’re querying

@Autowired
NamedParameterJdbcTemplate jdbcTemplate;

private static final String SELECT_PERSON_BY_ID = 
   "SELECT * FROM person " + 
   "WHERE id = :id";

public PersonData getPersonById(Long id){
   return jdbcTemplate.queryForObject(
           SELECT_PERSON_BY_ID,
           new MapSqlParameterSource().addValue("id", id),
           new BeanPropertyRowMapper<>(PersonData.class));
}

**update**
The `jdbcTemplate.update` method allows you to execute SQL statements that modify the database, such as `INSERT`, `UPDATE`, or `DELETE`.

Instead of taking a RowMapper, it takes an optional KeyHolder object that can contain the id of the new row.

private static final String INSERT_PERSON = 
   "INSERT INTO person (name, age, favorite_composer) " + 
   "VALUES(:name, :age, :favoriteComposer)";

public Long addPerson(PersonData personData) {
   KeyHolder key = new GeneratedKeyHolder();
   jdbcTemplate.update(
           INSERT_PERSON,
           new BeanPropertySqlParameterSource(personData),
           key);
   return key.getKey().longValue();
}

**SimpleJdbcInsert**
The `SimpleJdbcInsert` class allows us to do inserts without writing a query, which can make it easier to maintain our code if the composition of our tables change.

// does the same as the previous addPerson method
public Long addPersonInsert(PersonData personData) {
   SimpleJdbcInsert sji = new SimpleJdbcInsert(jdbcTemplate.getJdbcTemplate())
           .withTableName("person")
           .usingGeneratedKeyColumns("id");
   return sji.executeAndReturnKey(new 
  BeanPropertySqlParameterSource(personData)).longValue();
}

**Additional Resources**
1. [Spring Boot documentation on using JdbcTemplate](https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#boot-features-using-jdbc-template)
2. [Spring JdbcTemplate Javadoc](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html)

## ShannonNote

**JDPCTemplate**
JdbcTemplate有點像enetitymanager，依樣可以用來做CRUD的動作。透過@Autowire的方式去實作jdbcTemplate，如果你使用NamedParameterJdbcTemplate 的話就可以透過parameters的名稱去設定你要放入的參數。像這樣

-- with NamedParameterJdbcTemplate
select * from person where id = :id and age >= :minAge

* NamedParameterJdbcTemplate JdbcTemplate
**Select**
1. 如果要查詢 jdbcTemplate.queryForObject(sql指令, 指令裡面的參數, 回傳的東西)
- sql指令:`Select * from person where id = :id`
- 指令內的參數: `new MapSqlParameterSource().addValue("id",id)`
- 回傳的東西: `new BeanPropertyRowMapper<>(PersonData.class)`可以選擇要把回傳的值塞到哪一個class裡面

**Update**
2. 如果要更新 jdbcTemplate.update(sql指令, 要塞進去的object, keyholder to retrieve the value) ，然後通常update會回傳更新的多少數量的值
- sql指令:`INSERT INTO person (name, age, favorite_composer)VALUES(:name, :age, :favoriteComposer)`
- 要更新的物件:`new BeanPropertySqlParameterSource(personData)`
- KeyHolder can contain the id of the new row:
1. 首先新增KeyHolder`KeyHolder key = new GeneratedKeyHolder();`
2. 再來把key放進去抓取更新的id
3. 最後return的時候可以透過key來回傳更新的id

**SimpleJdbcInsert**
simpleJdbcInsert允許我們不需要且query就可以做CRUD的動作!!!

// does the same as the previous addPerson method
public Long addPersonInsert(PersonData personData) {
   SimpleJdbcInsert sji = new SimpleJdbcInsert(jdbcTemplate.getJdbcTemplate())
   			//要insert的table name
           .withTableName("person") 
		    //要針對哪一
           .usingGeneratedKeyColumns("id");
		   
    //simplejdbcinsert有方法可以直接回傳key
   return sji.executeAndReturnKey(new 
  BeanPropertySqlParameterSource(personData)).longValue();
}

# 10 Querying for Complex Object
{%youtube bTd3sLUZiMc%}
**OneToOne with RowMapper**
We will write a query to return this object:

public class PersonWithOneOutfit {
   private String name;
   private int age;
   private String favoriteComposer;
   private OutfitData outfit;
  /* getters and setters */
}

We can populate an object representing a OneToOne relationship between tables with a single query by combining multiple `RowMapper`'s in the same statement. The query returns all the values necessary to build both objects, and then we use an anonymous RowMapper to make the results into two different objects.

private static final String SELECT_PERSON_WITH_ONE_OUTFIT =
       "SELECT * FROM person p " +
               "JOIN outfit o " +
               "ON p.id = o.person_id " +
               "WHERE p.id = :personId AND o.id = :outfitId";


private static final BeanPropertyRowMapper<PersonWithOneOutfit> personWithOneOutfitRowMapper = new BeanPropertyRowMapper<>(PersonWithOneOutfit.class);
private static final BeanPropertyRowMapper<OutfitData> outfitRowMapper = new BeanPropertyRowMapper<>(OutfitData.class);

public PersonWithOneOutfit addOutfitForPerson(Long personId, OutfitData outfitData) {
   outfitData.setPersonId(personId);
   Long outfitId = addOutfit(personId, outfitData);

   return jdbcTemplate.queryForObject(SELECT_PERSON_WITH_ONE_OUTFIT,
           new MapSqlParameterSource()
                   .addValue("personId", personId)
                   .addValue("outfitId", outfitId),
           //anonymous row mapper lambda
           (resultSet, i) -> {
               PersonWithOneOutfit person = personWithOneOutfitRowMapper.mapRow(resultSet, i);
               person.setOutfit(outfitRowMapper.mapRow(resultSet, i));
               return person;
           });
}

{%youtube PZgHONN5ILQ%}
**OneToMany with ResultSetExtractor**
We will now look at a query to retrieve this object:

public class PersonWithAllOutfits {
   private String name;
   private int age;
   private String favoriteComposer;
   private List<OutfitData> outfits;
}

If you want to construct an object with more complex relationships, you have two main choices:
1. Query twice and assemble the objects
2. Query once and loop through every row to build objects from the result set

**jdbcTemplate.query**
To retrieve a list of items representing the second part of your OneToMany association, you can use jdbcTemplate.query. Here is a query returning all the outfits for a specific person. By using this in conjunction with a query to retrieve a single PersonData, you can then populate PersonWithAllOutfits.

private static final String SELECT_OUTFIT_BY_PERSON_ID = "SELECT * FROM outfit o WHERE o.person_id = :personId";

public List<OutfitData> getOutfitsByPerson(Long personId){
   return jdbcTemplate.query(SELECT_OUTFIT_BY_PERSON_ID,
           new MapSqlParameterSource().addValue("personId", personId),
           new BeanPropertyRowMapper<>(OutfitData.class));
}

**ResultSetExtractor**
If we wrote a single query that retrieved all the data for both `PersonData` and our `List<OutfitData>`, we can use a `ResultSetExtractor` to build the objects for us from the data.

private static final String SELECT_PERSON_WITH_ALL_OUTFITS =
       "SELECT * FROM person p " +
       "JOIN outfit o " +
       "ON p.id = o.person_id " +
       "WHERE p.id = :personId";

private static final BeanPropertyRowMapper<PersonWithAllOutfits> personWithAllOutfitsRowMapper = new BeanPropertyRowMapper<>(PersonWithAllOutfits.class);
public PersonWithAllOutfits addOutFitForPersonReturnAllFancy(Long personId, OutfitData outfitData) {
   //add the outfit to db
   outfitData.setPersonId(personId);
   addOutfit(personId, outfitData);

   //iterate over the result set to construct the outfit list
   return jdbcTemplate.query(
           SELECT_PERSON_WITH_ALL_OUTFITS,
           new MapSqlParameterSource().addValue("personId", personId),
           //anonymous ResultSetExtractor lambda
           resultSet -> {
               PersonWithAllOutfits person = null;
               List<OutfitData> outfits = new ArrayList<>();
               int row = 0;
               while(resultSet.next()) {
                   if(person == null){
                       person = personWithAllOutfitsRowMapper.mapRow(resultSet, row);
                   }
                   outfits.add(outfitRowMapper.mapRow(resultSet, row++));
               }
               if(person != null) {
                   person.setOutfits(outfits);
               }
               return person;
           });
}

[Spring Documentation on JdbcTemplate](https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access.html#jdbc)

# 11 Transactions in JDBC
**@Transactional**
Many operations in DAOs involve combining multiple queries into a single request, so transaction management is just as important as before! Fortunately, `JdbcTemplate` provides a Spring-managed connection, so we can simply use `@Transactional` to set our transaction boundaries by method just like we did when using Hibernate.

> Multiple Database Requests Occuring Inside Transaction

## ShannonNote
Transaction的部分，我們可以使用TRansactionTemplate或是PlatformTransactionManager去開始跟結束交易，但其實做簡單跟最快速的方式是使用`@Transactional`標註

# 12 Exercise 3
For this exercise, we’d like to implement the DAO interface we designed earlier. You’ll create a new class called CandyDAOImpl that implements CandyDAO. Use a NamedParameterJdbcTemplate to complete implementations of the three methods we declared before:

List<CandyData> list();
void addToDelivery(Long candyId, Long deliveryid);
List<CandyData> findByDelivery(Long deliveryId);

Your DAO implementation should still use the @Repository annotation, because it is a Spring Component that handles database interactions. You should also use the @Transactional annotation either at the class or method level to ensure all your methods execute inside a transaction.

# 13 Solution: Exercise3
Let’s look at an example implementation of CandyDAO. We’ve organized our queries and made reusable code into constants. You can see that our implementation methods are actually quite simple!

@Repository
@Transactional
public class CandyDAOImpl implements CandyDAO {

   @Autowired
   NamedParameterJdbcTemplate jdbcTemplate;

   //we can avoid some typo-based errors by using string constants
   private static final String CANDY_ID = "candyId";
   private static final String DELIVERY_ID = "deliveryId";

   private static final String SELECT_ALL_CANDY =
           "SELECT * FROM candy";

   private static final String INSERT_DELIVERY =
           "INSERT INTO candy_delivery (candy_id, delivery_id) " +
           "VALUES (:" + CANDY_ID + ", :" + DELIVERY_ID + ")";

   private static final String FIND_CANDY_BY_DELIVERY =
           "SELECT c.* FROM candy_delivery AS cd " +
           "JOIN candy AS c on c.id = cd.candy_id " +
           "WHERE cd.delivery_id = :" + DELIVERY_ID;

   private static final RowMapper<CandyData> candyDataRowMapper =
           new BeanPropertyRowMapper<>(CandyData.class);

   @Override
   public List<CandyData> list() {
       //no parameters, so we can use a version of .query that only takes two arguments
       return jdbcTemplate.query(SELECT_ALL_CANDY, candyDataRowMapper);
   }

   @Override
   public void addToDelivery(Long candyId, Long deliveryid) {
       //we don't have an object of the right type to use SimpleJdbcInsert, so we'll just do a normal .update
       jdbcTemplate.update(INSERT_DELIVERY,
               new MapSqlParameterSource()
                       .addValue(CANDY_ID, candyId)
                       .addValue(DELIVERY_ID, deliveryid));
   }

   @Override
   public List<CandyData> findByDelivery(Long deliveryId) {
       return jdbcTemplate.query(FIND_CANDY_BY_DELIVERY,
               new MapSqlParameterSource(DELIVERY_ID, deliveryId),
               candyDataRowMapper);
   }
}

# 14 Why Not Both?

**JPA vs. JDBC**
* JPA
* Convenient
* Table relationships defined in java
* SQL is generated
* JDBC
* Can be faster
* Table relationships defined in Database
* SQL is written

Generally, optimizing for convenience will save you more time in the long run. If you need to improve the performance of specific operations, there are a few properties you can use to help measure performance. This will cause Hibernate to report on the number of JDBC Statements prepared and executed during each session, as well as their time costs.
> 下面這兩條的目的在於Hibernate 會收集內部統計數據，比如查詢的性能、緩存命中情況等

原文網址：https://kknews.cc/tech/5vg8e8.html

spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.stat=DEBUG

For Hibernate 5.4.5 and later you can also use this property to cause any single query longer than the specified duration to print a message to the log.
> 你還可以透用該屬性設定說如果有query超過25豪秒，就把它印出來，這樣也比較好看說哪一條執行的很沒效率，到時候再從JPA換到JDBC。

hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS=25

{%youtube hhBWdj8yHZc%}
**Comparing Performance - Simple Example**
Consider 3 different implementations of the same solution:

public void setFavoriteComposerModifyEntity(Long personId, String favoriteComposer) {
   personRepository.getOne(personId).setFavoriteComposer(favoriteComposer);
}

private static final String SET_FAVORITE_COMPOSER = 
    "update Person p set p.favoriteComposer = :favoriteComposer where p.id =:id";
public void setFavoriteComposerJpql(Long personId, String favoriteComposer){
   entityManager.createQuery(SET_FAVORITE_COMPOSER)
           .setParameter("favoriteComposer", favoriteComposer)
           .setParameter("id", personId)
           .executeUpdate();
}

private static final String SET_FAVORITE_COMPOSER_NATIVE = 
    "update humanoid h set h.favorite_composer = :favoriteComposer where h.id = :id";
public void setFavoriteComposerNative(Long personId, String favoriteComposer){
   entityManager.createNativeQuery(SET_FAVORITE_COMPOSER_NATIVE)
           .setParameter("favoriteComposer", favoriteComposer)
           .setParameter("id", personId)
           .executeUpdate();
}

The first solution executes 2 JDBC Statements, one to look up, another to modify. If favoriteComposer is unchanged, then it only executes 1 JDBC Statement.

The second and third solutions both execute 1 JDBC Statement. However, the native SQL example requires knowing the Person is stored in a table called humanoid, while the JPQL does not. Their performance is otherwise similar.

In practice, only about a third of the time in each Session is preparing and executing statements, and the performance difference between these situations is nominal, so it could be hard to justify not using the first example.
**Comparing Performance - Complex Example**
Let’s consider a more expensive operation - updating every outfit a Person owns.

public void setShoeColorModifyEntity(Long personId, String shoeColor) {
   personRepository.findById(personId).get().getOutfits().stream()
           .forEach(o -> o.setShoes(shoeColor));
}

private static final String SET_SHOE_COLOR_NATIVE =
       "update Outfit o " +
               "set o.shoes = :color " +
               "where o.id in " +
               "(select ho.outfits_id " +
               "from humanoid_outfits ho " +
               "where ho.humanoid_id = :id)";
public void setShoeColorNative(Long humanoidId, String color){
   entityManager.createNativeQuery(SET_SHOE_COLOR_NATIVE)
           .setParameter("color", color)
           .setParameter("id", humanoidId)
           .executeUpdate();
}

In this case, the first example is vastly simpler. It doesn’t require knowledge of the database structure, is easy to understand, and can be easily modified. However, it executes 1 JDBC Statement for every single outfit a person owns, plus 1 JDBC statement to find a person and 1 JDBC statement to write them. If you modify the shoe color of someone with 30 pairs of shoes, you will prepare and execute 32 JDBC statements.

The second example is messy, but only requires a single JDBC statement. However, it’s faster than the first example, taking about a third the time.

The question is whether that kind of performance improvement is worth the complexity. If it was a 30x performance for cases with 30 outfits, perhaps so, but I think it would be an error to perform this optimization for a 2-3x improvement before you know it’s going to be a problem.

Note: This is not a robust performance testing setup, it is only meant to demonstrate the statistics provided by hibernate for discussion.

Note 2: MySQL prohibits subselects containing the same table you modify in an update statement. As such, you cannot use the following JPQL, because it is converted into a SQL statement containing a subselect of Outfit.
> 請不要再update某個table的時候去牽涉到那個Table，像下面的例子，他想要改outfit的內容，但資料來源卻是humanoid.outfit其實還是同一個東西。下面humanoid可以用humanoid_outfits去替換。

update Outfit o 
set o.shoes = :color 
where o.id in 
(select o2.id from Humanoid h 
join h.outfits o2 
where h.id = :id)

# 15 Persistence Without JPA Repository
{%youtube 1QkRphkpjvY%}
**Lesson Outline**
- Initialization with SQL
- Data Object vs. Entities
- Data Access Objects (DAOs)
- JdbcTemplate
- Performance

**Definitions**
1. SQL Initialization Scripts
Spring attempts to execute two scripts by default when the application starts:
* schema.sql - Create or update the schema.
* data.sql - Initialize or modify the data in your tables.

2. Platform-specific initialization
You
may provide additional initialization scripts using the naming pattern:
* schema-${platform}.sql
* data-${platform}.sql

3. Data Object
A Java class that maps to a specific database.

4. Data Access Object (DAO)
A design pattern for the data layer that presents a programmatic interface to one or more related tables. Whereas a Repository presents a collection of Entities that you modify to update the database, a DAO will often provide methods that expose various actions, along with standard CRUD operations.

5. JdbcTemplate
Provides connections, executes queries, and manages transactions.

6. NamedParameterJdbcTemplate
A JdbcTemplate that allows you to use named parameters in your query strings.

7. RowMapper
Class that takes a single row for a query result set and returns an object representing that row.

8. ResultSetExtractor
Class that takes an entire result set and returns one object representing all the results.

9. spring.jpa.properties.hibernate.generate_statistics
Property that enables a feature causing Hibernate to print all transaction statistics when a transaction ends.

# 16 Course Recap
{%youtube 2__yAirWxhs%}
> Look at Everything You've Covered!
You've made it! Congratulations. I've enjoyed learning with you and can't wait to see what you create.
You can find all the Hibernate documentation here: https://hibernate.org/orm/

Direct link to the Hibernate 5.4 User Guide referenced by this course.

You can find all the primary Spring Boot documentation here: https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/

Direct link to the Datasource configuration referenced by this course.

# 補充01: Using Jdbc Template
參考:https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#boot-features-using-jdbc-template

spring 裡面的jdbcTemplate跟NamedParameter是spring自動設定的，因此可以透過`@Autowire`來自動注入，我們09章節的例子，jdbc是透過NameParameter class去產生的，其實你也可以使用jdbcTemplate class。NamedplarameterJdbcTemplate會在背後重複使用JdbcTemplate實例

如果你想要設定template的一些屬性可以在properties file裡面設置相關`spring.jdbc.template.*`。

spring.jdbc.template.max-rows=500