I have a spring boot API project with h2 database. I added some data in the data.sql but the data wasn’t inserted into the database however tables are created successfully when I call the findAll API I get an empty list due to the database tables are empty.
Application.properties
spring.datasource.url=jdbc:h2:mem:testdb spring.datasource.driverClassName=org.h2.Driver spring.datasource.username=sa spring.datasource.password=password spring.jpa.database-platform=org.hibernate.dialect.H2Dialect server.port=8888
data.sql
DROP TABLE IF EXISTS device;
DROP TABLE IF EXISTS gateway;
CREATE TABLE gateway (
id INT AUTO_INCREMENT PRIMARY KEY,
serial_number VARCHAR(250) UNIQUE,
name VARCHAR(250),
address VARCHAR(15) NOT NULL
);
CREATE TABLE device (
id INT AUTO_INCREMENT PRIMARY KEY,
UID INT UNIQUE,
vendor VARCHAR(250),
created DATE,
status BOOLEAN,
gateway_id INT,
FOREIGN KEY (gateway_id) REFERENCES gateway(id)
);
INSERT INTO gateway (id, serial_number, name, address) VALUES (1,'GTW-1','Gateway 1','123.187.65.1');
INSERT INTO gateway (id, serial_number, name, address) VALUES (2,'GTW-2','Gateway 2','123.187.65.2');
INSERT INTO gateway (id, serial_number, name, address) VALUES (3,'GTW-3','Gateway 3','123.187.65.3');
INSERT INTO gateway (id, serial_number, name, address) VALUES (4,'GTW-4','Gateway 4','123.187.65.4');
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (1,001,'huawei', parsedatetime('17-09-2012', 'dd-MM-yyyy'), true, 1);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (2,002,'intel', parsedatetime('11-07-2013', 'dd-MM-yyyy'), false, 1);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (3,003,'sony', parsedatetime('01-01-2015', 'dd-MM-yyyy'),true, 1);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (4,004,'sony', parsedatetime('17-09-2012', 'dd-MM-yyyy'),true, 2);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (5,005,'huawei', parsedatetime('11-10-2013', 'dd-MM-yyyy'),true, 2);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (6,006,'dell', parsedatetime('04-05-2010', 'dd-MM-yyyy'),true, 2);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (7,007,'sony', parsedatetime('13-09-2020', 'dd-MM-yyyy'),false, 3);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (8,008,'dell', parsedatetime('05-09-2012', 'dd-MM-yyyy'),false, 3);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (9,009,'intel', parsedatetime('12-12-2013', 'dd-MM-yyyy'),true, 3);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (10,010,'dell', parsedatetime('17-07-2009', 'dd-MM-yyyy'),false, 3);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (11,011,'sony', parsedatetime('15-05-2015', 'dd-MM-yyyy'),true, 3);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (12,012,'toshiba', parsedatetime('09-09-2017', 'dd-MM-yyyy'),true, 3);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (13,013,'intel', parsedatetime('10-10-2018', 'dd-MM-yyyy'),true, 3);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (14,014,'dell', parsedatetime('11-11-2011', 'dd-MM-yyyy'),true, 3);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (15,015,'sony', parsedatetime('01-06-2021', 'dd-MM-yyyy'),false, 3);
INSERT INTO device (id, UID, vendor, created, status, gateway_id) VALUES (16,016,'sony', parsedatetime('17-06-2012', 'dd-MM-yyyy'),true, 3);
Pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.3</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>gateways</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>gateways</name>
<description>gateways</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
Main Class
@SpringBootApplication
@EnableAutoConfiguration
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
Gateway.java
@Entity
@Table(name = "gateway")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Gateway {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(name="serial_number", unique = true)
private String serialNumber;
@Column(name="name", unique = true)
private String name;
@Column(name="address")
private String address;
@OneToMany(mappedBy = "gateway")
private List<Device> devices;
}
Repository
@Repository
public interface GatewayRepository extends JpaRepository<Gateway, Long>{
}
Controller
@CrossOrigin("*")
@RestController
@RequestMapping("/gateway")
public class GatewayController {
@Autowired
GatewayRepository gatewayRepository;
@GetMapping()
public ResponseEntity<List<Gateway>> getAllGateways() {
try {
List<Gateway> gateways = gatewayRepository.findAll();
if (gateways.isEmpty()) {
return new ResponseEntity<>(HttpStatus.NO_CONTENT);
}
return new ResponseEntity<>(gateways, HttpStatus.OK);
} catch (Exception e) {
return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
}
}
}
project structure
Advertisement
Answer
Add the following to your application properties to stop JPA auto generating your gateway table over the top of your data.sql.
spring.jpa.hibernate.ddl-auto=none
By the way, this is the only thing you need in your application.properties, as all the rest is default.