天道酬勤,学无止境

How to import initial data to database with Hibernate?

When deploying applications, I often use Hibernate’s capacity to create database schema in order to simplify the deployment. This is easily achievable by configuring hibernate.hbm2ddl.auto property.

However, on occasion I also need to insert some initial data to database, for example root user. Is there a way I could achieve this via hibernate with some kind of load textual file?

I know I could easily program the code that will do so, but just wondering if there is already some utility that can help me achieve the same via configuration?

评论

I found this by doing a search on "Hibernate fixtures" :

Hibernate will create the database when the entity manager factory is created (actually when Hibernate's SessionFactory is created by the entity manager factory). If a file named import.sql exists in the root of the class path ('/import.sql') Hibernate will execute the SQL statements read from the file after the creation of the database schema. It is important to remember that before Hibernate creates the schema it empties it (delete all tables, constraints, or any other database object that is going to be created in the process of building the schema).

Source: http://www.velocityreviews.com/forums/t667849-hibernate-quotfixturesquot-or-database-population.html

Give it a try and let us know if it works!

Adding import.sql to the class path works great, hbm2ddl checks if the file exists and executes it. The only additional detail is that each sql command must be on its own line, otherwise it will fail to execute.

This will also work only if hbm2ddl.auto is set to create or create-drop.

Add hibernate property hibernate.hbm2ddl.import_files in your hibernate configuration. Change hibernate.hbm2ddl.auto property to create. Add initial_data.sql in /classes directory with initial sql code to insert data. Hibernate execute this after create database schema.

<bean id="sessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
            <prop key="hibernate.hbm2ddl.auto">create</prop>
            <prop key="hibernate.hbm2ddl.import_files">initial_data.sql</prop>
        </props>
    </property>
</bean>

If you do not want to add a property in your hibernate configuration you can create a file import.sql in /classes directory and hibernate use this by default if property hibernate.hbm2ddl.auto equals to create

Why hbm2ddl.auto and hbm2ddl.import_files properties are evil

(When misused as a tool for database change management)

Like said elsewhere, using hibernate.hbm2ddl.auto and hibernate.hbm2ddl.import_files for database change management has some serious drawbacks:

  1. Only the structure can be changed. Existing values might be overwritten or - in a worst case scenario - simply sent into Nirvana. Without a tool like liquibase or scriptella, you do not have any ETL capabilities.
  2. This method has no transactions. Both the structure and the data statements will be executed before an transaction manager is taking over. Let's say you have an error in statement 42 of 256. Your database is in an inconsistent state now.
  3. Imvho, you loose transparency and control: where a scriptella script or liquibase change set or usually committed together with the changes in the domain models, you do a change in the domain model and hope (basically) that hibernate will find out what to do. (It doesn't, but that is a different story.)
  4. For integration, system and acceptance test you merely assume that your test databases are in the absolutely, exactly same state as your production database. You have to keep track of that manually (Good luck and have fun with it! ;) ). In case you make an error, just a small slip is sufficient, the results may very we'll be catastrophic.

I personally use liquibase for database change management and have developed the following workflow to reduce the maintenance work:

  • Create a changelog from the command line of my last release structure
  • Create a changelog of my latest database
  • Manually diff both change logs (usually changes are not that huge, and if they are, they usually meet one of the shortcomings of liquibases diff command.
  • create a change set

Even for complicated changes in which one has to implement a customChange, this can be achieved in a matter of hours, including the definition of rollbacks, testing and documentation. For trivial changes, it is a matter of minutes. Basically: you have to do a little more work (I have created customized change sets for 4 database configurations in less than a day), but you get the peace of mind that you have done everything possible to keep the database in a consistent state.

After a couple of hours stumbling with this, I decided to share what I've found, though it's a very old post.

To make it work properly, i had to do the following:

  • hbmddl set to create or create-drop
  • file.sql in classpath root; in my case, i just put it in resources folder, i'm using maven.
  • each sql command in one line
  • each file.sql must have a blank line at the beggining of the file ==> don't know the reason for this one, but if i don't insert that blank line, at the time of execution the servers tells me there's a syntax error near the first character.

Hope that helps.

The standard way to do this in JPA is to use the configuration property javax.persistence.sql-load-script-source.

You can explore various settings related to schema export and test data import listed in Hibernate's AvailableSettings class.

Please make sure that your import.sql is formatted correctly. Start with a one liner insert statement to test.

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • Spring Boot-加载初始数据(Spring Boot - Loading Initial Data)
    问题 我想知道在应用程序启动之前加载初始数据库数据的最佳方法是什么? 我正在寻找的东西将用数据填充我的H2数据库。 例如,我有一个域模型“ User”,我可以通过转到/ users来访问用户,但是最初数据库中没有任何用户,所以我必须创建它们。 无论如何,有没有自动用数据填充数据库的方法? 目前,我有一个由容器实例化并为我创建用户的Bean。 例子: @Component public class DataLoader { private UserRepository userRepository; @Autowired public DataLoader(UserRepository userRepository) { this.userRepository = userRepository; LoadUsers(); } private void LoadUsers() { userRepository.save(new User("lala", "lala", "lala")); } } 但是我非常怀疑这是最好的方法。 还是吗? 回答1 您可以在src / main / resources文件夹中创建一个data.sql文件,它将在启动时自动执行。 在此文件中,您可以添加一些插入语句,例如: INSERT INTO users (username, firstname
  • 4.5、定制化security的数据库
    定制化security的数据库 一、使用H2内嵌数据库1.1、添加依赖1.2、添加依赖1.3、配置使用基于H2的内存数据库1.4、二次启动项目出现报错问题(未整理完善) 二、定制化数据库2.1、schema.sql(在引入jpa后未使用,jpa可自动根据data.sql创建表结构)2.2、data.sql2.3、User2.4、Role2.5、UserRepo2.6、RoleRepo2.7、application.yml 三、定制化登录逻辑3.1、实现UserDetailsService接口3.2、配置3.3、测试 四、实现登录后的密码升级4.1、配置密码转换器4.2、实现UserDetailsPasswordService4.3、测试 一、使用H2内嵌数据库 1.1、添加依赖 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <scope>runtime</scope> </dependency> 1.2、添加依赖 spring
  • Hibernate: “Field 'id' doesn't have a default value”
    I'm facing what I think is a simple problem with Hibernate, but can't solve it (Hibernate forums being unreachable certainly doesn't help). I have a simple class I'd like to persist, but keep getting: SEVERE: Field 'id' doesn't have a default value Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not insert: [hibtest.model.Mensagem] at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91) [ a bunch more ] Caused by: java.sql.SQLException
  • 无法启动Spring以自动创建数据库架构(Unable to get spring boot to automatically create database schema)
    问题 我无法启动启动时自动启动数据库架构的Spring Boot。 这是我的application.properties: spring.datasource.url=jdbc:mysql://localhost:3306/test spring.datasource.username=test spring.datasource.password= spring.datasource.driverClassName = com.mysql.jdbc.Driver spring.jpa.database = MYSQL spring.jpa.show-sql = true spring.jpa.hibernate.ddl-auto = create spring.jpa.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect spring.jpa.hibernate.naming_strategy = org.hibernate.cfg.ImprovedNamingStrategy 这是我的Application.java: @EnableAutoConfiguration @ComponentScan public class Application { public static void main(final
  • 如何将Hibernate代理转换为真实实体对象(How to convert a Hibernate proxy to a real entity object)
    问题 在Hibernate Session期间,我正在加载一些对象,由于延迟加载,其中一些对象已作为代理加载。 一切正常,我不想关闭延迟加载。 但是稍后,我需要通过RPC将一些对象(实际上是一个对象)发送到GWT客户端。 碰巧这个具体对象是代理。 所以我需要将其变成一个真实的对象。 我在Hibernate中找不到类似“实现”的方法。 我如何才能将某些对象从代理变为真实,知道它们的类和ID? 目前,我看到的唯一解决方案是从Hibernate的缓存中逐出该对象并重新加载它,但是由于许多原因,它确实很糟糕。 回答1 这是我正在使用的方法。 public static <T> T initializeAndUnproxy(T entity) { if (entity == null) { throw new NullPointerException("Entity passed for initialization is null"); } Hibernate.initialize(entity); if (entity instanceof HibernateProxy) { entity = (T) ((HibernateProxy) entity).getHibernateLazyInitializer() .getImplementation(); } return entity
  • How to do bulk (multi row) inserts with JpaRepository?
    When calling the saveAll method of my JpaRepository with a long List<Entity> from the service layer, trace logging of Hibernate shows single SQL statements being issued per entity. Can I force it to do a bulk insert (i.e. multi-row) without needing to manually fiddle with EntityManger, transactions etc. or even raw SQL statement strings? With multi-row insert I mean not just transitioning from: start transaction INSERT INTO table VALUES (1, 2) end transaction start transaction INSERT INTO table VALUES (3, 4) end transaction start transaction INSERT INTO table VALUES (5, 6) end transaction to
  • Hibernate 5:-org.hibernate.MappingException:未知实体(Hibernate 5 :- org.hibernate.MappingException: Unknown entity)
    问题 我尝试将hibernate 5.0与mysql集成时收到错误消息org.hibernate.MappingException: Unknown entity 这似乎是hibernate5.0.0和5.0.1的问题。 这与休眠4.3.9正常工作 Maven依赖 <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.0.0.Final</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.36</version> </dependency> hibernate.cfg.xml <session-factory> <!-- Database connection settings --> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://localhost:3307
  • Spring Boot多个数据源(Spring Boot Multiple Datasource)
    问题 我对Spring Boot还是很陌生,我想为我的项目创建一个多数据源。 这是我目前的情况。 我有两个用于多个数据库的实体包。 比方说 com.test.entity.db.mysql ; for entities that belong to MySql com.test.entity.db.h2 ; for entities that belong to H2 Databases 所以,目前我有两个实体类 UserMySql.java @Entity @Table(name="usermysql") public class UserMysql{ @Id @GeneratedValue public int id; public String name; } UserH2.java @Entity @Table(name="userh2") public class Userh2 { @Id @GeneratedValue public int id; public String name; } 我想实现一个配置,如果我从UserMySql创建用户,它将被保存到MySql数据库,如果我从Userh2创建用户,它将被保存到H2数据库。 因此,我也有两个DBConfig,比如说MySqlDbConfig和H2DbConfig。 (com.test
  • 如何使用JpaRepository进行批量(多行)插入?(How to do bulk (multi row) inserts with JpaRepository?)
    问题 当从服务层使用长List<Entity>调用我的JpaRepository的saveAll方法时,Hibernate的跟踪日志记录显示每个实体发出单个SQL语句。 我可以强迫它进行批量插入(即多行),而无需手动处理EntityManger ,事务等,甚至原始的SQL语句字符串吗? 对于多行插入,我的意思是不仅要过渡: start transaction INSERT INTO table VALUES (1, 2) end transaction start transaction INSERT INTO table VALUES (3, 4) end transaction start transaction INSERT INTO table VALUES (5, 6) end transaction 到: start transaction INSERT INTO table VALUES (1, 2) INSERT INTO table VALUES (3, 4) INSERT INTO table VALUES (5, 6) end transaction 但改为: start transaction INSERT INTO table VALUES (1, 2), (3, 4), (5, 6) end transaction 在PROD中
  • compatibility of spring 4.0.0 with hibernate 4.30 [duplicate]
    This question already has answers here: Spring 3.1, Hibernate 4, SessionFactory (6 answers) Closed 5 years ago. I am using Spring 4.0.0 RELEASE jars, hibernate-coe 4.3.0 jar in my spring + hibernate project. I am facing an error with org.hibernate.engine.FilterDefinition is not found. Actually in old hibernate core jars, we are having this class but not in 4.3.0 version. In fact we are having org.hibernate.engine.spi.FilterDefinition in 4.3.0 version. But I am not sure how this class is dependent in run time. Mean while I am using glassfish server to my application. Please let me know the
  • How to java-configure separate datasources for spring batch data and business data? Should I even do it?
    My main job does only read operations and the other one does some writing but on MyISAM engine which ignores transactions, so I wouldn't require necessarily transaction support. How can I configure Spring Batch to have its own datasource for the JobRepository, separate from the one holding the business data? The initial one datasource-configurations is done like the following: @Configuration public class StandaloneInfrastructureConfiguration { @Autowired Environment env; @Bean public LocalContainerEntityManagerFactoryBean entityManagerFactory() { LocalContainerEntityManagerFactoryBean em = new
  • 没有为org.hibernate.proxy.pojo.javassist.Javassist类找到序列化器?(No serializer found for class org.hibernate.proxy.pojo.javassist.Javassist?)
    问题 我正在使用SpringMVC , Hibernate和JSON但出现此错误。 HTTP Status 500 - Could not write JSON: No serializer found for class org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationConfig.SerializationFeature.FAIL_ON_EMPTY_BEANS) ) 请在下面检查我的实体 @Entity @Table(name="USERS") public class User { @Id @GeneratedValue @Column(name="USER_ID") private Integer userId; @Column(name="USER_FIRST_NAME") private String firstName; @Column(name="USER_LAST_NAME") private String lastName; @Column(name="USER_MIDDLE_NAME")
  • Hibernate: Automatically creating/updating the db tables based on entity classes
    I have the following entity class (in Groovy): import javax.persistence.Entity import javax.persistence.Id import javax.persistence.GeneratedValue import javax.persistence.GenerationType @Entity public class ServerNode { @Id @GeneratedValue(strategy = GenerationType.AUTO) Long id String firstName String lastName } and my persistence.xml: <?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0"> <persistence-unit name="NewPersistenceUnit"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <properties> <property name="hibernate
  • 没有命名为EntityManager的持久性提供程序(No Persistence provider for EntityManager named)
    问题 使用META-INF目录下的TopLink ,我的persistence.xml具有相同的名称。 然后,我用以下代码调用它: EntityManagerFactory emfdb = Persistence.createEntityManagerFactory("agisdb"); 但是,我收到以下错误消息: 2009-07-21 09:22:41,018 [main] ERROR - No Persistence provider for EntityManager named agisdb javax.persistence.PersistenceException: No Persistence provider for EntityManager named agisdb at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:89) at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:60) 这是persistence.xml : <?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="http://java
  • JSR303 custom validators being called twice
    I am creating a website using Spring MVC and for persistence I am using Spring Data JPA with Hibernate 4 as my JPA provider. Validation is being handled at present with Hibernate Validator. I have a problem whereby my validators are being called twice and I can't figure out why. The main reason this is a problem is because the second time round, dependencies are not being autowired into the validator and I am getting a null pointer exception. The following is the sequence of calls leading up to the failure: The registration form is submitted and first the NotDefaultSectValidator is called and
  • activiti6.0 本地部署流程
    一、在官网中下载activiti6.0 1、官网地址:https://www.activiti.org/get-started 2、将下载好的文件解压,将解压出来的3个war包直接放入tomcat的webapps的目录中(提前下好tomcat,这里用的tomcat8.x) 3、tomcat直接启动。 二、填坑 1、修改activiti-app中的\webapps\activiti-app\WEB-INF\classes\META-INF\activiti-app目录下的activiti-app.properties文件配置。 因activiti中默认是h2数据库,当tomcat启动时会重置之前的数据内容,因此将文件中关于数据库配置的修改; # # SECURITY # security.rememberme.key=testkey # # DATABASE # datasource.driver=com.mysql.jdbc.Driver datasource.url=jdbc:mysql://ip:端口/数据库?useUnicode=true&characterEncoding=utf8&useSSL=false&maxReconnects=2 datasource.username=用户名 datasource.password=密码 datasource
  • How to generate a ddl creation script with a modern Spring Boot + Data JPA and Hibernate setup?
    Currently, I'm using the default @SpringBootApplication annotation with the following properties in application.properties: spring.datasource.url=jdbc:mysql://localhost/dbname spring.datasource.username=X spring.datasource.password=X spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.jpa.hibernate.naming_strategy=my.package.CustomNamingStrategy Since JPA 2.1, I should be able to use the javax.persistence.schema-generation.* properties, but setting them in my application.properties seems to have no effect. I've seen examples like this that wire up a whole bunch of extra beans, but
  • Hibernate 5 :- org.hibernate.MappingException: Unknown entity
    I am getting the error message org.hibernate.MappingException: Unknown entity when i am trying to integrate hibernate 5.0 with mysql This seems to be an issue with hibernate5.0.0 and 5.0.1 . This works fine with hibernate 4.3.9 Maven dependices <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.0.0.Final</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.36</version> </dependency> hibernate.cfg.xml <session-factory> <!-- Database connection settings --> <property name="connection
  • 当休眠必须是表的主键时,如何使用休眠生成自定义ID(How to generate Custom Id using hibernate while it must be primary key of table)
    问题 这是我的pojo课 @Entity public class Department { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) @Column(name="Department_Id") private Integer deptId; @Column(name="Department_Name",unique=true,nullable=false) private String deptName; @Column(name="Department_Description") @NotNull private String deptDesc; //geters and setters 我想要的是department_id必须是此Department表的主键,并且该键的条目必须为DEP0001,DEP0002,DEP0003 回答1 谢谢大家的回应……最后,我在Department类中做了一些更改,并使用了一个类来生成IDs ....这是我的代码 @Entity public class Department { @Id @GenericGenerator(name = "sequence_dep_id", strategy = "com.xyz.ids.DepartmentIdGenerator")
  • How to solve the “failed to lazily initialize a collection of role” Hibernate exception
    I have this problem: org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: mvc3.model.Topic.comments, no session or session was closed Here is the model: @Entity @Table(name = "T_TOPIC") public class Topic { @Id @GeneratedValue(strategy=GenerationType.AUTO) private int id; @ManyToOne @JoinColumn(name="USER_ID") private User author; @Enumerated(EnumType.STRING) private Tag topicTag; private String name; private String text; @OneToMany(mappedBy = "topic", cascade = CascadeType.ALL) private Collection<Comment> comments = new LinkedHashSet<Comment>();