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:automacorp;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);
}

public void insertSite(Site site) {
    try(Connection conn = dataSource.getConnection()){
        String sql = "insert into SITE (id, name) values (?, ?)";
        try(PreparedStatement stmt = conn.prepareStatement(sql)){
          stmt.setString(1, site.getId());
          stmt.setString(2, site.getName());
          stmt.executeUpdate();
        }
    }
    catch(SQLException e) {
        throw new DatabaseException("Impossible to insert site " +
            site.getName(), e);
    }
}

public void insertSite(Site site) {
        try(Connection conn = dataSource.getConnection()){
            conn.setAutoCommit(false); // 1.
            String sql = "insert into SITE (id, name) values (?, ?)";

            try(PreparedStatement stmt = conn.prepareStatement(sql)){
                stmt.setString(1, site.getId());
                stmt.setString(2, "toto");
                stmt.executeUpdate();
                conn.commit(); // 2.
            }
            catch(SQLException e) {
                conn.rollback(); // 3.
                throw new DatabaseException("Impossible insérer site " + site.getName(), e);
            }
        }
        catch(SQLException e) {
            throw new DatabaseException("Impossible insérer site " + site.getName(), e);
        }
    }

import jakarta.persistence.*;

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

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

    @Column(name = "sensor_value") // (5)
    private Double value;

    @Column(name = "sensor_type") // (5).
    @Enumerated(EnumType.STRING) // (6).
    private SensorType sensorType;

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

    public SensorEntity() { // (8).
    }

    public SensorEntity(SensorType sensorType, String name) { // (9).
        this.name = name;
        this.sensorType = sensorType;
    }

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

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

    public String getName() {
        return name;
    }

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

    public Double getValue() {
        return value;
    }

    public void setValue(Double value) {
        this.value = value;
    }

    public SensorType getSensorType() {
        return sensorType;
    }

    public void setSensorType(SensorType sensorType) {
        this.sensorType = sensorType;
    }

    public Integer getNotImportant() {
        return notImportant;
    }

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

@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 = Set.of();

@Repository
public class SensorCustomDaoImpl implements SensorCustomDao {

    @PersistenceContext
    private EntityManager em;

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

INSERT INTO SP_SENSOR(id, name, sensor_value, sensor_type) VALUES(-10, 'Temperature room 2', 21.3, 'TEMPERATURE');
INSERT INTO SP_SENSOR(id, name, sensor_value, sensor_type) VALUES(-9, 'Window 1 status room 1', 1.0, 'STATUS');
INSERT INTO SP_SENSOR(id, name, sensor_value, sensor_type) VALUES(-8, 'Window 2 status room 1', 0.0, 'STATUS');
INSERT INTO SP_SENSOR(id, name, sensor_value, sensor_type) VALUES(-7, 'Window 1 status room 2', 0.0, 'STATUS');
INSERT INTO SP_SENSOR(id, name, sensor_value, sensor_type) VALUES(-6, 'Window 2 status room 2', 0.0, 'STATUS');

INSERT INTO SP_ROOM(id, name, floor) VALUES(-10, 'Room1', 1);
INSERT INTO SP_ROOM(id, name, floor, current_temperature_id, target_temperature) VALUES(-9, 'Room2', 1, -10, 20.0);

INSERT INTO SP_WINDOW(id, window_status_id, name, room_id) VALUES(-10, -9, 'Window 1', -10);
INSERT INTO SP_WINDOW(id, window_status_id, name, room_id) VALUES(-9, -8, 'Window 2', -10);
INSERT INTO SP_WINDOW(id, window_status_id, name, room_id) VALUES(-8, -7, 'Window 1', -9);
INSERT INTO SP_WINDOW(id, window_status_id, name, room_id) VALUES(-7, -6, 'Window 2', -9);