I have a problem regarding the proper setup of a database. The behavior I observe is what I would excpect for "hbm2ddl.auto = create-drop" or "ddl-auto = create-drop." However, I explicitly set "hbm2ddl.auto = validate" and deleted ddl-auto (and tried every other combination of these two and validate), but still get the same behavior as for "create-drop".
First, what I want:
- I want to store data, let's say "MachineData" in a h2 database.
- I want to upload the data just once, by using the h2 interface in the browser (or by using an import.sql file).
- An application will use the data in the database, but won't modify anything
My problem:
- I create the table "MachineData" manually via the H2 interface and load some data with sql statements (see below).
- When I start my application however, the "MachineData" table that I manually created and filled with data is dropped.
- If I provided an import.sql file, the table and data is re-created. When the application is finished, the tables are dropped again. Hence, my database is empty, but the application worked.
- If I do not provide an import.sql file, the application will throw an error since the tables are not present.
- I tried every possible combination of hibernate.ddl-auto = validate and/or/ hibernate.hbm2ddl.auto = validate. However, the results stays the same: tables are always dropped during start of the application.
UPDATED(1): UPDATED(2):
My database is configured using @Configuration:
@Configuration
public class DatabaseConfig {
private final Environment env;
@Autowired
public DatabaseConfig(Environment env) {
this.env = env;
}
@Bean
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(env.getProperty("datasource.driver"));
dataSource.setUrl(env.getProperty("datasource.url"));
dataSource.setUsername(env.getProperty("datasource.username"));
dataSource.setPassword(env.getProperty("datasource.password"));
return dataSource;
}
@Bean
public LocalSessionFactoryBean sessionFactory() {
LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource());
sessionFactoryBean.setPackagesToScan(env.getProperty("entitymanager.packagesToScan"));
Properties hibernateProperties = new Properties();
hibernateProperties.put("hibernate.dialect", env.getProperty("jpa.hibernate.dialect"));
hibernateProperties.put("hibernate.show_sql", env.getProperty("jpa.show-sql"));
hibernateProperties.put("spring.jpa.generate-ddl", env.getProperty("jpa.generate-ddl"));
sessionFactoryBean.setHibernateProperties(hibernateProperties);
return sessionFactoryBean;
}
@Bean
public HibernateTransactionManager transactionManager() {
HibernateTransactionManager transactionManager = new HibernateTransactionManager();
transactionManager.setSessionFactory(sessionFactory().getObject());
return transactionManager;
}
@Bean
public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
return new PersistenceExceptionTranslationPostProcessor();
}
}
I am using a .yaml for the hibernate properties: UPDATE:
spring.profiles: run_GA
datasource:
driver: org.h2.Driver
url: jdbc:h2:tcp://localhost/C:/Users/User/Document/MLDB;DB_CLOSE_DELAY=-1
username: sa
password:
jpa:
show-sql: false
generate-ddl: true
hibernate.dialect: org.hibernate.dialect.H2Dialect
entitymanager.packagesToScan: somePath.mapping
Regarding the mapping, I use the following class:
@Entity
@Table
public class Machinedata {
public int timeInCycles;
public double sensor01;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "timeincycles")
public int getTimeInCycles() {
return timeInCycles;
}
public void setTimeInCycles(int timeInCycles) {
this.timeInCycles = timeInCycles;
}
@Column(name = "sensor01")
public double getSensor01() {
return sensor01;
}
public void setSensor01(double sensor01) {
this.sensor01 = sensor01;
}
When I manually create a table in the h2 database, I use the following sql commands:
CREATE TABLE MACHINEDATA(Time IDENTITY AUTO_INCREMENT, sensor01 DOUBLE);
INSERT INTO Machinedata SELECT * FROM CSVREAD('myPath.csv');
The import.sql file exhibts the following command:
INSERT INTO Machinedata (timeincycles, sensor01) SELECT * FROM CSVREAD('myPath.csv');
I would expect the described behavior if I set hbm2ddl.auto = create-drop. However, as I mentioned above, hbm2ddl.auto = validate in my case. Do you have any ideas what is wrong in my code? Thanks a lot for your help in advance.
