# 03.4-Data Stores and Rersistence Lesson4:Introduction to Connecting to Data Sources
###### tags: `Udacity`
[ToC]

# 01 Introduction to Connecting to Data Sources
{%youtube avGTKAhvA7o%}
> You are Here

**Lesson Summary**
* Set up an external database.
* Connect to external databases.
* Configure and initialize multiple data sources.
* Configure data sources for unit tests.

# 02 Data Sources Exercise 0
{%youtube TO8Yp_I73aI%}

For this lesson, you will need to install and configure a database to connect to. These instructions are for MySQL, though you may use another database provider if you wish.
1. Visit dev.mysql.com/downloads/.
2. Run the installer and select the components: workbench, server, java connector, and documentation. If you prefer to use other sql server management tools, you can just install the server and java connector.
3. After installing, continue through the prompts to configure your server. Select Standalone MySQL Server and choose the Development Computer config type. Then we'll choose Strong Password Encryption and create a root password. This is the password you'll use when connecting to MySQL from the workbench as the system admin.
4. On Windows, you'll choose to configure MySQL Server as a Windows Service and start on startup using a Standard account. On Mac, start the MySQL Server once installation is complete.
5. Click Execute to apply the configuration on windows, or Finish on mac.

Note that these instructions may vary depending on which version of MySQL you install.
> Use Strong Password Encryption

> Password To Connect Via Workbench

# 03 Java DataSources
### Udacity
{%youtube XzrUBArT3uM%}

**Data Source vs. DataSource**
Sometimes we use these words interchangeable, so just to clarify:

**Data Source**: A source of data, such as a database.
**DataSource**: Java utility class that manages connections to physical or logical databases.


**Additional Resources**
[Java Documentation on DataSource class](https://docs.oracle.com/javase/8/docs/api/javax/sql/DataSource.html)

## ShannonNote
分散式交易 Distributed Transactions
- 將操作或是任務分散在各個不同的地方執行，這些動作有順序且是獨立的，如果其中一個動作發生問題，那就進行rollback

# 04 Spring DataSources
## Udacity

**Spring DataSource Properties**
Spring automatically builds a DataSource for us on startup. You can configure all the properties of the datasource by setting values in your application.properties file. [Here is a List of Spring Data Properties](https://docs.spring.io/spring-boot/docs/current/reference/html/appendix-application-properties.html#data-properties). Anything beginning with spring.datasource refers to DataSource configuration.

Example application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/exampledb
spring.datasource.username=sa
spring.datasource.password=sa1234

The url property uses the format:
`subprotocol : serverName[:port] / databaseName : properties`

We have not specified any properties as part of the URL string in this example, because we are specifying them using additional rows in our properties file.

**Note:** Depending on your driver version and locale, you may run into issues with Spring Boot retrieving the server timezone from MySQL and deciding that it is invalid. This is because certain time zones, such as ‘PDT’, Pacific Daylight Time, are not recognized by some Java MySQL connectors.
`spring.datasource.url=jdbc:mysql://localhost:3306/exampledb?serverTimezone=UTC
`
**Creating Initial Database**
Before using your application to connect to a database server, you should create a database instance on that server to store your information. Then you should create an admin user for that database. You can use the MySQL Workbench to do this, or any other tool that allows you to execute sql against your database.

create user 'sa'@'localhost' identified by 'sa1234'; -- Create the user
grant all on exercise1.* to 'sa'@'localhost'; -- Gives all privileges to that user on new db

## ShannonNote
- Spring 會自動建立索取資源的地方，然後會給data source建立connections.

- spring.datasoucr的一些屬性可以使用在application.properties，主要目的是在改變default data source object
- `spring.datasource.url = jdbc:mysql://localhost:3036/exampledb`
- `spring.datasource.url`: JDBC URL of the database
- `jdbc:mysql:`是subprotocol
- `localhost:3306`是serverName[:port]
- `exampledb`是databaseInstanceName:properties
- `spring.datasource.username=sa`
- Login username of the database.
- `spring.datasource.password=sa1234`
- Login password of the database.

- 如果沒有提供`spring.datasource.url`的話default行為是甚麼呢?
- Create a DataSource for an in-memory database if one is found on the classpath.

# 05 Data Sources Exercise1
For this exercise, we’ll configure your local database to hold results from our plant delivery project. Note that you’ll need to make sure your pom.xml includes the dependency for the mysql-connector-java artifact in order to complete this exercise:

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>

* Create a new schema on your database server called ‘plant’
* Create an admin user that is authorized to ‘plant’
* Update your application.properties file with your new database url and credentials
* Set the initialization-mode property to ‘always’ so that it continues to operate on our external db
* Launch your application and verify that it starts successfully

> 你可以使用spring.datasource.initialization-mode來設定SpringBoot要不要自動create the schema of an embedded DataSource.如果你想要always初始化DataSource且不管他的type那就加入參數always

If you get an access denied exception when connecting to your database, make sure the user credentials you created match the ones in your application.properties. Ensure that you granted that user all permissions on the database you’re trying to access.

# 06 Solution: Data Sources Exercise 1
Example application.properties contents

spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/plant
spring.datasource.username=sa
spring.datasource.password=sa1234

Example SQL Execution

CREATE SCHEMA `plant` ; -- Create the plant database

CREATE USER 'sa'@'localhost' IDENTIFIED BY 'sa1234'; -- Create the user if you haven’t yet
GRANT ALL ON plant.* TO 'sa'@'localhost'; -- Gives all privileges to the new user on plant

# 07 Programmatic Configuration
## Udacity
{%youtube _3_AS9qNbYw%}
**`@Bean`**
The `@Bean` annotation marks methods that should create beans during Spring initialization. Each method will be executed and the returned object will be added as a Bean for Spring to use in dependency injection. These can also be configured by xml. We’re using the `@Bean` annotation to identify methods that return DataSource instances. Spring will inject these into persistence classes as needed.

**Programmatic Configuration**
DataSources can also be configured programmatically by creating a class annotated with `@Configuration` that returns a `@Bean` of the desired type. Spring will inject this DataSource whenever a DataSource is requested. This could be useful if you need to retrieve some DataSource properties dynamically.

@Configuration
public class DatasourceConfig {

   @Bean
   public DataSource getDatasource() {
       DataSourceBuilder dsb = DataSourceBuilder.create();
       dsb.username("sa2");
       dsb.password(securePasswordService());
       dsb.url("jdbc:mysql://localhost:3306/exercise1");
       return dsb.build();
   }

   private String securePasswordService() {
       return "sa1234";
   }
}

**`@ConfigurationProperties`**
You can also combine properties and programmatic configuration by using the `@ConfigurationProperties` annotation. Spring will attempt to inject any properties found beginning with the provided prefix into the returned @Bean.

> 關於Bean 跟 ConfigureationProperties 可以查看 補充04, 補充05
## ShannonNote

@Configuration
public class DatasourceConfig {
    //spring就可以使用這個method whenever it wants to generate an instance of our Datasource object
    //如果spring想要產生datasource實體物件就會使用這個方法
    @Bean
    //load properties into this DataSource from our properties file
    //we pass in a prefix string that identifies which values in the file are to be loaded
    //foo.datasource: all the entries in my application file which begin with foo.datasource will be used to populate the attributes of the datasource i returned from the method
    @ConfigurationProperties("foo.datasource")
    public DataSource getDatasource(DataSourceProperties properties){
        //Use DataSourceBuilder to create a DataSource
        DataSourceBuilder dsb = DataSourceBuilder.create();
        //set the properties on
        dsb.username("shannon");
        dsb.password(securePasswordService());
        dsb.url("jdbc:mysql://localhost:3306/plant");
        //return a DataSource object from my builder
        return dsb.build();
    }

    private String securePasswordService() {
        return "hung0602";
    }
}

> Whaen are the values from `@ConfigurationProperties` injected?
- When the `@Bean` is returned, overwriting properties set programmatically. Any properties you specify in the properties file will overwrite those you set programmatically.

# 08 Data Sources Exercise 2
For this assignment, we’d like to store our credentials in a property file, but load our datasource URL programmatically.

First, create a new class to hold your DataSource `@Bean`. Use DataSourceBuilder to create and return a new instance of DataSource.

Now add new properties to your `application.properties` for a prefix of your choice. Our solution example will begin with ‘com.udacity’. Create a property for url. Our solution property will be called ‘com.udacity.datasource.url’, for example.

Update your DataSource bean to retrieve the url from your properties file and set the username and password programmatically. Run your application to make sure it still connects!

# 09 Solution: Data Sources Exercise2
All right! Now we’re more flexible than ever.
**DataSourceConfig.java**

@Configuration
public class DataSourceConfig {

   @Bean
   @Primary
   @ConfigurationProperties(prefix="com.udacity.datasource")
   public DataSource getDataSource() {
       DataSourceBuilder dsb = DataSourceBuilder.create();
       dsb.url("jdbc:mysql://localhost:3306/plant");
       return dsb.build();
   }
}

**application.properties**
You may have more properties than this, but these are the minimum:

com.udacity.datasource.username=sa
com.udacity.datasource.password=sa1234

==Notic==
請注意，POM.xml裡面一定要有jpa，否則無法執行...我發現的
因此我建議把h2, jpa, web, mysql的dependency都先下好

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

# 10 Custom/Multiple Data Sources
## Udacity
{%youtube EPHDSYj7ZzM%}

**DataSourceProperties**
Different DataSource providers sometimes have different names for their properties. You can use a DataSourceProperties object to manage converting between the standard spring.datasource properties and your desired DataSource type by creating a `@Bean` that returns a DataSourceProperties object you populate from your properties file.

We will annotate both the DataSourceProperties and DataSource beans with `@Primary` so that spring knows which beans to use by default of that type.

@Bean
@Primary
@ConfigurationProperties("spring.datasource")
public DataSourceProperties getDataSourceProperties(){
   return new DataSourceProperties();
}

@Bean
@Primary
@ConfigurationProperties(prefix = "spring.datasource.configuration")
public DataSource getDatasource(DataSourceProperties properties) {
   return properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
}

**Multiple DataSources**
If your data is stored in multiple locations, you can access it by creating multiple DataSource @Beans. You will have to provide beans for an EntityManagerFactory and a TransactionManager as well. To support Spring Data repositories, we also use the @EnableJpaRepositories annotation to reference the specific classes.

> 一般情況下資料可能存放在多個地方，我們可以透過@Bean EntityManagerFactory and TransactionManager去支援Spring Data repositories，同時可以使用`@EnableJpaRepositories`指向我們想要特定的classes.

Here are the definitions for two different config classes. The first one defines everything we need to load spring.datasource properties into one DataSource for storing our Humanoid Entities. The next one uses the properties from spring.datasource2 to access our Outfit Entities.

DatasourceConfig.java

@Configuration
@EnableJpaRepositories(basePackageClasses = Humanoid.class, entityManagerFactoryRef = "humanoidFactory")
public class DatasourceConfig {

   @Bean
   @Primary
   @ConfigurationProperties("spring.datasource")
   public DataSourceProperties getDataSourceProperties(){
       return new DataSourceProperties();
   }

   @Bean
   @Primary
   @ConfigurationProperties(prefix = "spring.datasource.configuration")
   public DataSource getDatasource(DataSourceProperties properties) {
       return properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
   }

   @Bean(name = "humanoidFactory")
   @Primary
   public LocalContainerEntityManagerFactoryBean humanoidEntityManagerFactory(
           EntityManagerFactoryBuilder entityManagerFactoryBuilder) {
       return entityManagerFactoryBuilder
               .dataSource(getDatasource(getDataSourceProperties()))
               .packages(Humanoid.class)
               .persistenceUnit("humanoid")
               .build();
   }

   @Bean(name = "humanoidTx")
   @Primary
   public PlatformTransactionManager humanoidTransactionManager(@Qualifier("humanoidFactory")EntityManagerFactory entityManagerFactory){
       return new JpaTransactionManager(entityManagerFactory);
   }

}

**DatasourceConfig2.java**

@Configuration
@EnableJpaRepositories(basePackageClasses = {Outfit.class, OutfitRepository.class}, entityManagerFactoryRef = "outfitFactory")
public class Datasource2Config {

   @Bean
   @ConfigurationProperties("spring.datasource2")
   public DataSourceProperties getDataSource2Properties(){
       return new DataSourceProperties();
   }

   @Bean
   @ConfigurationProperties(prefix = "spring.datasource2.configuration")
   public DataSource getDatasource2(DataSourceProperties properties) {
       return properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
   }

   @Bean(name = "outfitFactory")
   public LocalContainerEntityManagerFactoryBean outfitEntityManagerFactory(
           EntityManagerFactoryBuilder entityManagerFactoryBuilder) {
       return entityManagerFactoryBuilder
               .dataSource(getDatasource2(getDataSource2Properties()))
               .packages(Outfit.class)
               .persistenceUnit("outfit")
               .build();
   }

   @Bean(name = "outfitTx")
   public PlatformTransactionManager outfitTransactionManager(@Qualifier("outfitFactory")EntityManagerFactory entityManagerFactory){
       return new JpaTransactionManager(entityManagerFactory);
   }

}

Additional Resources
[Spring Boot documentation on custom and multiple DataSources](https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-data-access)

## ShannonNote
- DataSourceProperties
- 不同DataSource有時候會提供properties不同的name，這時候你可以使用DataSourceProperties object去管理standard spring.datasource properties 和你期望的DataSource type之間的轉換，只要藉由@Bean which returns a DataSourceProperties object you propulate from properties file.

# 11 Initialization
## Udacity
{%youtube TGqoIQOsJ0k?t=86%}

**spring.datasource.initialization-mode values**
**Embedded**: Default. Initialization performed on embedded databases only.
**Always**: Initialization for both embedded and external databases.
**Never**: No initialization for either embedded or external databases.

**Additional Resources**
[Spring Boot Documentation on Initialization](https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-database-initialization)

## ShannonNote
`spirng.datasource.initialzation-mode`通常我們會使用它來做資料庫初始化的動作產生schema，裡面有三種值。
1. `Embedded`表示指初始化已經被兼入的資料庫，像是h2。
2. `Always`表示外面的資料庫，又或是兼入資料庫都自動產生Schema。
3. `Never`不做任何初始化。

通常我們會使用never這樣就可以手動測試各種情況看有沒有奇怪的反應。

# 12 Initialization with Hibernate
{%youtube 0-NIcLaGDX0%}
`spring.jpa.hibernate.ddl-auto`
This property allows you to customize Hibernate’s initialization behavior.

`create`: Drop all tables for defined Entities, then create them.
`create-drop`: Create tables, drop them when application stops.
`update`: Attempt to migrate previous version of tables to match current Entities.
`validate`: Throw an exception if tables or columns are missing.
`none`: Do not initialize tables.

**show-sql**
Spring offers a useful command to print all generated sql commands to the console:

`spring.jpa.show-sql=true`
There’s also a hibernate property for formatting the sql output that makes it easier to read:

`spring.jpa.properties.hibernate.format_sql=true`
Additional Resources
Spring Boot Documentation on Initializing a database with Hibernate

## ShannonNote
如果你在part11使用自動產生schema，你應該也可以改變一些自動產生的內部動作，像是你使用`spring.jpa,hibernate.ddl-auto`就可以針對初始化動作做一些設定。裡面的值有五種
- create: 表示清除所有table for defined entities然後製造出他們
- create-drop: 如果關掉application我就清除所有tables，同時這也是default value
- update: 表示將之前的版本合併到現在的entities
- validate: throw an exception if tables or columns are missing
- none: 不要初始化table

如果你希望spring初始化做的所有動作都可以看的到sql語法來了解，有幾個方便的使用方式提供你去辨識。
- `spring.jpa.show-sql=true` : 你可以在console裡面看到跑過的Sql語法
- `spring.jpa.properties.hibernate.format_sql=true`:讓console裡面的一堆sqlcommand有縮排，比較容易閱讀。

**Additional Resources**
[Spring Boot Documentation on Initializing a database with Hibernate](https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-initialize-a-database-using-hibernate)

# 13 Data Sources Exercise 3
This is a simple exercise to continue our exploration of data source initialization. First, update your application.properties to always initialize the datasource and set it to create tables on startup. Next, set the properties to show and format sql.

Run your application again and check out the execution of create statements in your console. Head over to the MySQL Workbench and verify that all the tables have been created for your Plant and Delivery Entities.

Data Sources Exercise 3 Task List
Complete the following tasks:
1. Update application.properties to always initialize your datasource and tell hibernate to create tables
2. Set the properties to show formatted SQL output
3. Run your application
4. Verify the tables were creted for Plant and Delivery Entities

# 14 Solution: Data Sources Exercise 3
Example application.properties

spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/plant
spring.datasource.username=sa
spring.datasource.password=sa1234
spring.datasource.initialization-mode=always
spring.jpa.hibernate.ddl-auto=create

After launching your application, make sure tables have been created for ‘delivery’ as well as ‘plant’ and its subclasses ‘flower’ and ‘shrub’ in your database. Since we’re using the JOINED inheritance strategy, you can see that flower and shrub tables don’t contain the same columns as plant.

Initialization scripts

Hibernate: 

    alter table flower 
       drop 
       foreign key FKdn67xpln3filirucdvdwyo5ix
Hibernate: 

    alter table plant 
       drop 
       foreign key FKiq1ux1y4sjxoh9ty39rk7j0am
Hibernate: 

    alter table shrub 
       drop 
       foreign key FK511y91sacosf2eyo1pv096jer
Hibernate: 

    drop table if exists delivery
Hibernate: 

    drop table if exists flower
Hibernate: 

    drop table if exists hibernate_sequence
Hibernate: 

    drop table if exists plant
Hibernate: 

    drop table if exists shrub
Hibernate: 

    create table delivery (
       id bigint not null,
        address_full varchar(500),
        completed char(1),
        delivery_time datetime(6),
        name nvarchar(255),
        primary key (id)
    ) engine=InnoDB
Hibernate: 

    create table flower (
       color varchar(255),
        id bigint not null,
        primary key (id)
    ) engine=InnoDB
Hibernate: 

    create table hibernate_sequence (
       next_val bigint
    ) engine=InnoDB
Hibernate: 

    insert into hibernate_sequence values ( 1 )
Hibernate: 

    insert into hibernate_sequence values ( 1 )
Hibernate: 

    create table plant (
       id bigint not null,
        name nvarchar(255),
        price decimal(12,4),
        delivery_id bigint,
        primary key (id)
    ) engine=InnoDB
Hibernate: 

    create table shrub (
       height_cm integer not null,
        width_cm integer not null,
        id bigint not null,
        primary key (id)
    ) engine=InnoDB
Hibernate: 

    alter table flower 
       add constraint FKdn67xpln3filirucdvdwyo5ix 
       foreign key (id) 
       references plant (id)
Hibernate: 

    alter table plant 
       add constraint FKiq1ux1y4sjxoh9ty39rk7j0am 
       foreign key (delivery_id) 
       references delivery (id)
Hibernate: 

    alter table shrub 
       add constraint FK511y91sacosf2eyo1pv096jer 
       foreign key (id) 
       references plant (id)

> List of Tables in Schema 'plant'

# 15 Unit Test Data Source
{%youtube jzn2bkAI0Bs%}

**Configuring Unit Tests with application.properties**
One way to configure the unit test datasource is to simply provide a second application.properties file. You could add the following properties to force H2 for unit tests:

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:db
spring.datasource.username=sa
spring.datasource.password=sa

Remember, the default initialization-mode is `embedded`, so you do not need to specify this property when using H2 for unit tests. Also recall that the default ddl-auto property for in-memory databases is `create-drop`, so you also do not need to set that property unless you wish some other behavior.

**`@SpringBootTest`**
This annotation goes on your unit test class. creates an entire Spring ApplicationContext when running unit tests. It is used if you need to test controller or service classes, or perform integration tests spanning multiple layers.

**`@DataJpaTest`**
This annotation provides an alternate way to test your data layer without providing an application.properties file. It disables Spring autoconfiguration and automatically uses an in-memory database if available. It only loads Entities and Spring Data JPA repositories, but not your Services or Controllers.

**`TestEntityManager`**
`TestEntityManager` is a class provided by Spring Boot that provides useful methods for persisting test data inside persistence unit tests. It is still available in `@DataJpaTests` despite the rest of the app not being wired up.

**`@AutoConfigureTestDatabase`**
This annotation can be used with either @SpringBootTest or @DataJpaTest. You can use it to customize Spring’s behavior for replacing the normal datasource. For example, the following annotation could be used in conjunction with @DataJpaTest to indicate that Spring should NOT replace the datasource with an in-memory datasource.

@AutoConfigureTestDatabase(replace=Replace.NONE)

==ShannonNote==
schema generation可以很方便地做unit test因為它會自動build拆除所有schema重新建造一個環境給我們測試，我猜拉

`@SpringBootTest`
簡單來說就是重新創造一個完整的spring applicationContext去跑你的測試，不會影響原本的東西

`@DataJpaTest`
他不會啟動那些自動的設定，然後記憶體則是使用內存資料庫，而不是啟動整個applicationContext所以你可以用這個單獨測試資料庫的Unit Test,除此之外，他還會載入entities and Spring Data JPA repositories但其他的Bean就不會載入了。這有一個好處，在資料的層面上就不會有過大的cost of the entire application，他只能夠存取data layer components。如果使用DataJpaTest就不需要額外在test多設定第二個application.properties，我在想是因為它本身就表示使用in-memories的東西在測試。

`TestEntityManager`
我們之前使用EntityManager去做一些sql指令或是動作，其實在test裡面，TestEntityManager就是EntityManager的替代方案，它提供了所有EntityManager的方法給你去測試，有點像是模仿EntityManager的刑未只是說運用在test裡面，我們之前看過的方法像是`persist(E entity)`表示這個entity被管理了，又或是`merge(E entity)`,`remove(Object entity)`等等。

merge跟persist差別可以在[這裡](https://www.itdaan.com/tw/ab097a6c72d4f3213d067fc766e2cf1)查看，我覺得很詳細~

`@AutoConfigureTestDatabase(replace=Replace.NONE)`
他可以自動配置Test Database，使用內存資料庫，他主要的作用是啟動內存資料庫，而不是真的資料庫，可以用在`@SpringBootTest`或是`@DataJpaTest`擇一，但是也可以用它來設定某些行為，如果設定成replace.none就表示不要用內存資料庫，用原本的資料庫來測試。

# 16 Data Sources Exercise 4
For this exercise, you’ll create a unit test to validate the methods in the `PlantRepository` interface. In particular, you should create the following two unit tests:

1. `testPriceLessThan()` - Adds two new plants to your database with two different prices. Use the PlantRepository.findByPriceLessThan method to make sure the correct plant is returned.
2. `testDeliveryCompleted()` - Create a new Plant and a new Delivery. Set both sides of their bi-directional relationship. Verify that PlantRepository.deliveryCompleted returns false for the plant you just created. Then, set the Delivery to true and verify that deliveryCompleted returns true.

Use the `@DataJpaTest` annotation and inject a `TestEntityManager` to prepare the data for the above tests.

==ShannonNote==
1. 如果發生`Consider defining a bean of type 'com.udacity.lesson4.Repository.PlantRepository' in your configuration.`可以看看有沒有在Configuration把`@EnableJpaRepositories(basePackageClasses = Humanoid.class, entityManagerFactoryRef = "humanoidFactory")`拿掉

2. 請記得，你使用的是使用hibernate_sequence來進行自動增加id的動作，所以如果insert plant做了兩次，下一次的insert delivery匯是，但是如果你設定的Object id使用`@GeneratedValue(strategy = GenerationType.IDENTITY)`，如果換成新的object就會使用新的id編號從1開始

# 17 Solution: Data Sources Exercise 4
Here’s the unit test class I created to demonstrate this assignment, but there are many different ways you might have set up the test data. Try to make sure you test reasonable boundary conditions and don’t simply validate that one example works how you expect.

Note that, to facilitate writing these tests, I added extra constructors to Plant and Delivery that may take in extra arguments. Feel free to do the same!

**Lesson3ExercisesApplicationTests.java**

@DataJpaTest
class Lesson3ExercisesApplicationTests {

  @Autowired
  TestEntityManager testEntityManager;

  @Autowired
  PlantRepository plantRepository;

  @Test
  public void testPriceLessThan() {
    //test boundary conditions
     Plant p = testEntityManager.persist(new Plant("Foo Leaf", 4.99)); 
     testEntityManager.persist(new Plant("Bar Weed", 5.01));

     List<Plant> cheapPlants = plantRepository.findByPriceLessThan(BigDecimal.valueOf(5));
     Assertions.assertEquals(1, cheapPlants.size(), "Size");
     Assertions.assertEquals(p.getId(), cheapPlants.get(0).getId(), "Id");
  }

  @Test
  public void testDeliveryCompleted() {
     Plant p = testEntityManager.persist(new Plant("Baz Root", 9.99));
     Delivery d = testEntityManager.persist(new Delivery("Leonard Bernstein", "234 West Side", LocalDateTime.now()));

     d.setPlants(Lists.newArrayList(p));
     p.setDelivery(d);

     //test both before and after
     Assertions.assertFalse(plantRepository.deliveryCompleted(p.getId()));
     d.setCompleted(true);
     Assertions.assertTrue(plantRepository.deliveryCompleted(p.getId()));
  }
}

Example constructors:

**Delivery.java**

@NamedQuery(name = "Delivery.findByName", query = "select d from Delivery d where d.name = :name")
@Entity
public class Delivery {
   @Id
   @GeneratedValue
   private Long id;

   @Nationalized
   private String name;
   @Column(name = "address_full", length = 500)
   private String address;
   private LocalDateTime deliveryTime;
   @Type(type = "yes_no")
   private Boolean completed = false;

   // changed CascadeType to ALL
   @OneToMany(fetch = FetchType.LAZY, mappedBy = "delivery", cascade = CascadeType.ALL)
   private List<Plant> plants;

  // Convenience Constructor
   public Delivery(String name, String address, LocalDateTime deliveryTime) {
       this.name = name;
       this.address = address;
       this.deliveryTime = deliveryTime;
   }

   public Delivery() {

   }

   /* getters and setters */
}

Plant.java

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Plant {
   @Id
   @GeneratedValue
   private Long id;

   @JsonView(Views.Public.class)
   @Nationalized
   private String name;

   @JsonView(Views.Public.class)
   @Column(precision=12, scale=4)
   private BigDecimal price;

   //don't retrieve delivery if we don't need it
   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = "delivery_id")
   private Delivery delivery;

  // Convenience Constructor
   public Plant(String name, double price){
       this.name = name;
       this.price = BigDecimal.valueOf(price);
   }

   public Plant() {

   }
   /* getters and setters */
}

# 18 Connecting to DataSources
{%youtube https://youtu.be/__vjMkvo3Ik%}

n this lesson, we learned how to set up and connect to our external database. We covered customizing our DataSource objects and writing unit tests for our Repositories.

**Definitions**
- Data Source
A source of data, such as a database.

- DataSource
Java utility class that manages connections to physical or logical databases.

- spring.datasource properties
The list of all the datasource variables that can be configured in your application.properties file. List of Spring Data Properties

- @Bean
Annotation that specifies a method returns a component normally configured and injected by Spring Boot. Used inside a class with @Configuration annotation.

- @ConfigurationProperties
Annotation that allows you to inject values from the specified property prefix into the return object provided by your @Bean annotation.

- spring.datasource.initialization-mode values
This property allows you to specify whether database initialization should run when the application starts up.
- Embedded: Default. Initialization performed on embedded databases only.
- Always: Initialization for both embedded and external databases.
- Never: No initialization for either embedded or external databases.

- spring.jpa.hibernate.ddl-auto
This property allows you to customize Hibernate’s initialization behavior.
- create: Drop all tables for defined Entities, then create them.
- create-drop: Create tables, drop them when application stops.
- update: Attempt to migrate previous version of tables to match current Entities.
- validate: Throw an exception if tables or columns are missing.
- none: Do not initialize tables.

- @SpringBootTest
This annotation goes on your unit test class. creates an entire Spring ApplicationContext when running unit tests. It is used if you need to test controller or service classes, or perform integration tests spanning multiple layers.

- @DataJpaTest
This annotation provides an alternate way to test your data layer without providing an application.properties file. It disables Spring autoconfiguration and automatically uses an in-memory database if available. It only loads Entities and Spring Data JPA repositories, but not your Services or Controllers.

- TestEntityManager
TestEntityManager is a class provided by Spring Boot that provides useful methods for persisting test data inside persistence unit tests. It is still available in @DataJpaTests despite the rest of the app not being wired up.

- @AutoConfigureTestDatabase
This annotation can be used with either `@SpringBootTest` or `@DataJpaTest`. You can use it to customize Spring’s behavior for replacing the normal datasource. For example, the following annotation could be used in conjunction with `@DataJpaTest` to indicate that Spring should NOT replace the datasource with an in-memory datasource.

# 補充01: Spring Data Properties
# 補充02: What is Schema?
參考:https://database.guide/what-is-a-database-schema/
一個資料庫的詞語，是指Database的組織與架構。中文會被稱為「模式」。
A database schema can be represented in a visual diagram, which shows the database objects and their relationship with each other.


## CREATE SCHEMA name
在[mysql](https://dev.mysql.com/doc/refman/8.0/en/create-database.html)裡面表示其實他就是CREATE DATABASE statement

/*CREATE SCHEMA is a synonym for CREATE DATABASE*/
/*如果db_name存在會發生錯誤所以要用if*/
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

/*Each create_option 表示Database的特性*/
create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}

CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE.

## CREATE USER ... indentified by ...
參考:https://www.jianshu.com/p/cf270697b5af

1. 如果使用`%`表示可以被遠程訪問

create user 'test'@'%'

2. 如果使用`localhost`表示只能本地訪問

create user 'test'@'localhost'

## GRANT .. on ..to..
grant表示授權開發人員，像是create, 索引, view, 儲存過程函數等等權限。
> 給user所有數據庫的權限

grant all on exampledb.* to 'sa'@'localhost';`

# 補充03: MySQL的使用者建立
參考:https://ithelp.ithome.com.tw/articles/10030141
之前我們在練習的時候看到的

create user 'sa'@'localhost' identified by 'sa1234';
grant all on exampledb.* to 'sa'@'localhost';

- 其實`sa`指的是username, `sa1234`是password

mysql> CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...

- 一般來說root帳號的權限是最高且最完整的，如果root想指定另一個帳號也有創見帳號的權限，可以使用GRANT指定來完成工作，一般來說可以使用這個指定來執行`GRANT ALL(所有權限) ON 資料庫.資料表 TO 使用者`
# 補充04: @Bean?
* Bean?
- 如果在某個方法的上方增加Bean的標註，那就是在告訴spring初始化的時候，就產生這個bean並且執行他，增加了bean的方法回傳物件會被作為一個Bean給spring去使用在依賴注入裡面。

> 結論: 那就是方法如果使用Bean，不用特別呼叫該方法，啟動spring的時候他就會幫你自動執行那個方法，那個方法的Return值會存在spring裡面，如果要使用隨時可以使用不用重新呼叫method。

# 補充05: @ConfigurationProperties ?
參考: https://kknews.cc/zh-tw/code/x6vm5l8.html

通常在spring裡面為了更好的模板整合，可以將大量的參數配置在application.properties文件中，如果想要獲取這些參數就可以使用@ConfigurationProperties註解。

# 補充06: 關於@Primary
參考: [在spring中常被忽视的注解 @Primary](https://blog.csdn.net/xy0513042058/article/details/54439021?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param)

簡單來說一般使用@Autowire來自動注入的時候會去找符合type的bean去塞進去，如果同時遇到兩個type一樣的介面就會出現`NoUniqueBeanDefinitionException`，代表Spring不知道要使用哪一個去注入，這時候就可以@Primay來告訴Spring應該優先使用哪一個。

Example:
1. 有一個interface叫做 Singer
2. 然後有兩個實現class 加上 @Bean
3. @Autowired private Singer singer;

# 補充07: 關於Part10-Data Access
## Configure a Custom DataSource
參考:
- [SpringBoot2 | SpringBoot2 Hikari資料來源配置](https://www.itread01.com/content/1544780382.html)
- [documentation](https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-data-access)
官方給了兩種方式去自訂義資料來源
1. 使用DataSourceBuilder來建構資料來源

@Bean
//這裡的app.datasource是自己定義的namespace
@ConfigurationProperties("app.datasource")
public HikariDataSource dataSource() {
	return DataSourceBuilder.create().type(HikariDataSource.class).build();
}

2. 第二種是使用DataSourceProperties來建構資料來源

# 補充08: select...for update
這是屬於悲觀鎖的部分，begin ... 到commit之間，如果使用了select ... for update，該筆資料就被鎖定住不能進行任何修改或是刪除，直到commit為止。

參考:https://www.notion.so/a501a99e767a461191ca67dfa1d900ec?v=89acb1cf388f44278cea0a59626daf55

# 補充09: 關於alter table ... add constraint ...

原本可以寫這樣，產生talbe的時候順便連結FK。

    create table flower (
       color varchar(255),
        id bigint not null FOREIGN KEY REFERENCES plant(id),
        primary key (id)
    ) engine=InnoDB

但也可以這樣寫，先建立table事後再做reference的動作

> create table first

    create table flower (
       color varchar(255),
        id bigint not null,
        primary key (id)
    ) engine=InnoDB

> then reference foreign key

 alter table flower 
       add constraint FKdn67xpln3filirucdvdwyo5ix 
       foreign key (id) 
       references plant (id)

- 上面兩種有一樣的效果