Spring in practice : database and JPA

implementation 'org.springframework.boot:spring-boot-starter-data-jpa' // libs to use JPA in your project
implementation 'com.h2database:h2' // libs to use a H2 database

# Spring boot : datasource
spring.datasource.url=jdbc:h2:mem:faircorp;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver

# Spring boot : H2 datasource
spring.h2.console.enabled=true
spring.h2.console.path=/console

# Spring boot : JPA
spring.jpa.database=h2

try {
  Class.forName("org.h2.Drive"); // (1)
}
catch (ClassNotFoundException e) {
  logger.error("Unable to load JDBC Driver", e);
}
try {
  String database_url = "jdbc:h2:mem:bigcorp;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"; // (2)
  Connection connection = DriverManager.getConnection(database_url, username, password); // (3)
}
catch (SQLException e) {
  logger.error("Unable to connect to the database", e);
}

import javax.persistence.*;

@Entity // (1).
@Table(name = "SP_SENSOR") // (2).
public class Sensor {
    @Id // (3).
    @GeneratedValue
    private Long id;

    @Column(nullable=false, length=255)  // (4).
    private String name;

    private String description;

    @Column(name = "power") // (4).
    private Integer defaultPowerInWatt;

    @Transient // (5).
    private Integer notImportant;

    @Enumerated(EnumType.STRING) // (6).
    private PowerSource powerSource;

    public Sensor() { // (7).
    }

    public Sensor(String name) { // (8).
        this.name = name;
    }

    public Long getId() { // (9).
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getDefaultPowerInWatt() {
        return defaultPowerInWatt;
    }

    public void setDefaultPowerInWatt(Integer defaultPowerInWatt) {
        this.defaultPowerInWatt = defaultPowerInWatt;
    }

    public Integer getNotImportant() {
        return notImportant;
    }

    public void setNotImportant(Integer notImportant) {
        this.notImportant = notImportant;
    }

    public PowerSource getPowerSource() {
        return powerSource;
    }

    public void setPowerSource(PowerSource powerSource) {
        this.powerSource = powerSource;
    }
}

@Service
@Transactional
public class SiteServiceImpl implements SiteService {

    public Site addSite(String name){
        Site site = new Site(name);
        site.addSensor(new Sensor("default", site)
                               .withPowerSource(PowerSource.FIXED)
                               .withDefaultPowerInWatt(1_000_000));
        siteDao.save(site);
        return site;
    }
}

# Spring boot : JPA
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.generate-ddl=true
spring.jpa.show_sql=true
spring.jpa.defer-datasource-initialization=true

@Entity
public class Mayor {
    @Id
    private Long id;
    private String name;

    //...
}


@Entity
public class City {
     @Id
     private Long id;
     private String name;
     @OneToOne
     private Mayor mayor;

     // ...
}

@Entity
public class City {
    @Id
    private Long id;
    private String name;

    @OneToOne
    private Mayor mayor;

    // ...
}

@Entity
public class Mayor {
    @Id
    private Long id;
    private String name;

    @OneToOne(mappedBy = "mayor")
    private City city;

    //...
}

@OneToMany(fetch = FetchType.EAGER)
private Set<Sensor> sensors;

@Repository
public class SensorCustomDaoImpl implements SensorCustomDao {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Sensor> findBySiteText(String searchText) {
        return em.createQuery("select c from Sensor c inner join c.site s where lover(s.name) like :searchText",
                              Sensor.class)
                 .setParameter("searchText", "%" + searchText.toLowerCase() + "%")
                 .getResultList();
    }
}

public enum HeaterStatus { ON, OFF}

// (1)
// (2)
public class Window {
    // (3)
    private Long id;

    // (4)
    private String name;

    // (5)
    private WindowStatus windowStatus;

    public Window() {
    }

    public Window(String name, WindowStatus status) {
        this.windowStatus = status;
        this.name = name;
    }

    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public WindowStatus getWindowStatus() {
        return windowStatus;
    }

    public void setWindowStatus(WindowStatus windowStatus) {
        this.windowStatus = windowStatus;
    }
}

Hibernate: drop table if exists heater CASCADE
Hibernate: drop table if exists room CASCADE
Hibernate: drop table if exists room_windows CASCADE
Hibernate: drop table if exists rwindow CASCADE
Hibernate: drop sequence if exists hibernate_sequence
Hibernate: create sequence hibernate_sequence start with 1 increment by 1
Hibernate: create table heater (id bigint not null, heater_status varchar(255) not null, name varchar(255) not null, power bigint, room_id bigint not null, primary key (id))
Hibernate: create table room (id bigint not null, current_temperature double, floor integer not null, name varchar(255) not null, target_temperature double, primary key (id))
Hibernate: create table room_windows (room_id bigint not null, windows_id bigint not null, primary key (room_id, windows_id))
Hibernate: create table rwindow (id bigint not null, name varchar(255) not null, window_status varchar(255) not null, room_id bigint not null, primary key (id))
Hibernate: alter table room_windows add constraint UK_ojyqj1kyod1v2m4eoltwj7sfa unique (windows_id)
Hibernate: alter table heater add constraint FKba10hbsubifukw0monybjx3a0 foreign key (room_id) references room
Hibernate: alter table room_windows add constraint FKbpyirwvjc67pkk61hnh5qpn6y foreign key (windows_id) references rwindow
Hibernate: alter table room_windows add constraint FKaihtbjcqqmheg9s19aala347k foreign key (room_id) references room
Hibernate: alter table rwindow add constraint FK621arbu4dtuis4nf2o3xtapfn foreign key (room_id) references room

INSERT INTO ROOM(id, name, floor, current_temperature, target_temperature) VALUES(-10, 'Room1', 1, 22.3, 20.0);
INSERT INTO ROOM(id, name, floor) VALUES(-9, 'Room2', 1);

INSERT INTO HEATER(id, heater_status, name, power, room_id) VALUES(-10, 'ON', 'Heater1', 2000, -10);
INSERT INTO HEATER(id, heater_status, name, power, room_id) VALUES(-9, 'ON', 'Heater2', null, -10);

INSERT INTO RWINDOW(id, window_status, name, room_id) VALUES(-10, 'CLOSED', 'Window 1', -10);
INSERT INTO RWINDOW(id, window_status, name, room_id) VALUES(-9, 'CLOSED', 'Window 2', -10);
INSERT INTO RWINDOW(id, window_status, name, room_id) VALUES(-8, 'OPEN', 'Window 1', -9);
INSERT INTO RWINDOW(id, window_status, name, room_id) VALUES(-7, 'CLOSED', 'Window 2', -9);