java 在 jpa/EclipseLink 创建表后执行 sql 脚本？

Execute sql script after jpa/EclipseLink created tables?

提问by gerry

is there a possibility to execute an sql script, after EclipseLink generated the ddl?
In other words, is it possible that the EclipseLink property "eclipselink.ddl-generation" with "drop-and-create-tables" is used and EclipseLink executes another sql-file (to insert some data into some tables just created) after creating the table definition?

在 EclipseLink 生成 ddl 之后，是否有可能执行 sql 脚本？
换句话说，是否有可能使用带有“drop-and-create-tables”的 EclipseLink 属性“eclipselink.ddl-generation”，并且 EclipseLink 在创建后执行另一个 sql 文件（将一些数据插入刚刚创建的某些表中）表定义？

I'm using EclipseLink 2.x and JPA 2.0 with GlassFish v3.

我将 EclipseLink 2.x 和 JPA 2.0 与 GlassFish v3 一起使用。

Or can I init the tables within a java method which is called on the project (war with ejb3) deployment?

或者我可以在项目（与 ejb3 的War）部署中调用的 java 方法中初始化表吗？

采纳答案by Carl Düvel

It is called BEFORE ddl-execution. And there seems to be no nice way to adapt it, as there is no suitable event one could use.

它被称为 BEFORE ddl 执行。而且似乎没有很好的方法来适应它，因为没有合适的事件可以使用。

回答by Frelling

I came across this question for the same reasons, trying to find an approach to run an initialization script after DDL generation. I offer this answer to an old question in hopes of shortening the amount of "literary research" for those looking for the same solution.

出于同样的原因，我遇到了这个问题，试图找到一种在 DDL 生成后运行初始化脚本的方法。我为一个老问题提供了这个答案，希望能够为那些寻求相同解决方案的人缩短“文学研究”的时间。

I'm using GlassFish 4 with its default EclipseLink 2.5 JPA implementation. The new Schema Generation feature under JPA 2.1 makes it fairly straightforward to specify an "initialization" script after DDL generation is completed.

我使用 GlassFish 4 及其默认的 EclipseLink 2.5 JPA 实现。JPA 2.1 下的新模式生成特性使得在 DDL 生成完成后指定“初始化”脚本变得相当简单。

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="cbesDatabase" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>java:app/jdbc/cbesPool</jta-data-source>
        <properties>
            <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
            <property name="javax.persistence.schema-generation.create-source" value="metadata"/>
            <property name="javax.persistence.schema-generation.drop-source" value="metadata"/>
            <property name="javax.persistence.sql-load-script-source" value="META-INF/sql/load_script.sql"/>
            <property name="eclipselink.logging.level" value="FINE"/> 
        </properties>
    </persistence-unit>
</persistence>

The above configuration generates DDL scripts from metadata (i.e. annotations) after which the META-INF/sql/load_script.sqlscript is run to populate the database. In my case, I seed a few tables with test data and generate additional views.

上述配置从元数据（即注释）生成 DDL 脚本，然后META-INF/sql/load_script.sql运行脚本以填充数据库。就我而言，我为一些带有测试数据的表设置种子并生成其他视图。

Additional information on EclipseLink's use of JPA's properties can be found in the DDL Generation section of EclipseLink/Release/2.5/JPA21. Likewise, Section 37.5 Database Schema Creationin Oracle's Java EE 7 Tutorial and TOTD #187offer a quick introduction also.

可以在EclipseLink/Release/2.5/JPA21的 DDL 生成部分中找到有关 EclipseLink 使用 JPA 属性的其他信息。同样，Oracle 的 Java EE 7 教程和TOTD #187 中的第 37.5 节数据库模式创建也提供了快速介绍。

回答by Pascal Thivent

Have a look at Running a SQL Script on startup in EclipseLinkthat describes a solution presented as a kind of equivalent to Hibernate's import.sqlfeature¹. Credits to Shaun Smith:

看一看在 EclipseLink中启动时运行 SQL 脚本，它描述了一个解决方案，该解决方案与 Hibernate 的import.sql特性¹等效。归功于肖恩·史密斯：

Running a SQL Script on startup in EclipseLink

Sometimes, when working with DDL generation it's useful to run a script to clean up the database first. In Hibernate if you put a file called "import.sql" on your classpath its contents will be sent to the database. Personally I'm not a fan of magic filenames but this can be a useful feature.

There's no built in support for this in EclipseLink but it's easy to do thank's to EclipseLink's high extensibility. Here's a quick solution I came up with: I simply register an event listener for the session postLogin event and in the handler I read a file and send each SQL statement to the database--nice and clean. I went a little further and supported setting the name of the file as a persistence unit property. You can specify this all in code or in the persistence.xml.

The ImportSQLclass is configured as a SessionCustomizerthrough a persistence unit property which, on the postLoginevent, reads the file identified by the "import.sql.file" property. This property is also specified as a persistence unit property which is passed to createEntityManagerFactory. This example also shows how you can define and use your own persistence unit properties.

import org.eclipse.persistence.config.SessionCustomizer;
import org.eclipse.persistence.sessions.Session;
import org.eclipse.persistence.sessions.SessionEvent;
import org.eclipse.persistence.sessions.SessionEventAdapter;
import org.eclipse.persistence.sessions.UnitOfWork;

public class ImportSQL implements SessionCustomizer {

    private void importSql(UnitOfWork unitOfWork, String fileName) {
        // Open file
        // Execute each line, e.g.,
        // unitOfWork.executeNonSelectingSQL("select 1 from dual");
    }

    @Override
    public void customize(Session session) throws Exception {
        session.getEventManager().addListener(new SessionEventAdapter() {
            @Override
            public void postLogin(SessionEvent event) {
                String fileName = (String) event.getSession().getProperty("import.sql.file");
                UnitOfWork unitOfWork = event.getSession().acquireUnitOfWork();
                importSql(unitOfWork, fileName);
                unitOfWork.commit() 
            }    
        });
    }

---

public static void main(String[] args) {
    Map<String, Object> properties = new HashMap<String, Object>();

    // Enable DDL Generation
    properties.put(PersistenceUnitProperties.DDL_GENERATION, PersistenceUnitProperties.DROP_AND_CREATE);
    properties.put(PersistenceUnitProperties.DDL_GENERATION_MODE, PersistenceUnitProperties.DDL_DATABASE_GENERATION);
    // Configure Session Customizer which will pipe sql file to db before DDL Generation runs
    properties.put(PersistenceUnitProperties.SESSION_CUSTOMIZER, "model.ImportSQL");
    properties.put("import.sql.file","/tmp/someddl.sql");

    EntityManagerFactory emf = Persistence
            .createEntityManagerFactory("employee", properties);
}

在 EclipseLink 中启动时运行 SQL 脚本

有时，在使用 DDL 生成时，首先运行脚本来清理数据库会很有用。在 Hibernate 中，如果您在类路径上放置一个名为“import.sql”的文件，其内容将被发送到数据库。我个人不是魔法文件名的粉丝，但这可能是一个有用的功能。

EclipseLink 中没有对此的内置支持，但由于 EclipseLink 的高可扩展性，这很容易做到。这是我想出的一个快速解决方案：我只是为会话 postLogin 事件注册一个事件侦听器，并在处理程序中读取一个文件并将每个 SQL 语句发送到数据库 - 很好而且干净。我更进一步，支持将文件名设置为持久性单元属性。您可以在代码或persistence.xml 中指定这一切。

所述ImportSQL类被配置为SessionCustomizer通过一个持久单元属性其中，在postLogin事件中，读出由“import.sql.file”属性标识的文件。此属性也指定为传递给 的持久性单元属性 createEntityManagerFactory。此示例还展示了如何定义和使用自己的持久性单元属性。

import org.eclipse.persistence.config.SessionCustomizer;
import org.eclipse.persistence.sessions.Session;
import org.eclipse.persistence.sessions.SessionEvent;
import org.eclipse.persistence.sessions.SessionEventAdapter;
import org.eclipse.persistence.sessions.UnitOfWork;

public class ImportSQL implements SessionCustomizer {

    private void importSql(UnitOfWork unitOfWork, String fileName) {
        // Open file
        // Execute each line, e.g.,
        // unitOfWork.executeNonSelectingSQL("select 1 from dual");
    }

    @Override
    public void customize(Session session) throws Exception {
        session.getEventManager().addListener(new SessionEventAdapter() {
            @Override
            public void postLogin(SessionEvent event) {
                String fileName = (String) event.getSession().getProperty("import.sql.file");
                UnitOfWork unitOfWork = event.getSession().acquireUnitOfWork();
                importSql(unitOfWork, fileName);
                unitOfWork.commit() 
            }    
        });
    }

---

public static void main(String[] args) {
    Map<String, Object> properties = new HashMap<String, Object>();

    // Enable DDL Generation
    properties.put(PersistenceUnitProperties.DDL_GENERATION, PersistenceUnitProperties.DROP_AND_CREATE);
    properties.put(PersistenceUnitProperties.DDL_GENERATION_MODE, PersistenceUnitProperties.DDL_DATABASE_GENERATION);
    // Configure Session Customizer which will pipe sql file to db before DDL Generation runs
    properties.put(PersistenceUnitProperties.SESSION_CUSTOMIZER, "model.ImportSQL");
    properties.put("import.sql.file","/tmp/someddl.sql");

    EntityManagerFactory emf = Persistence
            .createEntityManagerFactory("employee", properties);
}

I'm not sure it's a strict equivalent though, I'm not sure the script will run after the database generation. Testing required. If it doesn't, maybe it can be adapted.

我不确定它是否是严格的等价物，但我不确定脚本是否会在数据库生成后运行。需要测试。如果没有，也许它可以适应。

_{¹Hibernate has a neat little feature that is heavily under-documented and unknown. You can execute an SQL script during the SessionFactory creation right after the database schema generation to import data in a fresh database. You just need to add a file named import.sql in your classpath root and set either create or create-drop as your hibernate.hbm2ddl.auto property.}

_{¹Hibernate 有一个简洁的小功能，但它的文档记录不足且未知。您可以在数据库模式生成后立即在 SessionFactory 创建期间执行 SQL 脚本以将数据导入到新数据库中。您只需要在类路径根目录中添加一个名为 import.sql 的文件，并将 create 或 create-drop 设置为您的 hibernate.hbm2ddl.auto 属性。}

回答by Andreas

This might help as there is a confusion here: Use exactly the same set of properties (except logger) for data seeding.

这可能会有所帮助，因为这里有一个混淆：使用完全相同的一组属性（记录器除外）进行数据播种。

DO NOT USE:

不使用：

<property name="eclipselink.ddl-generation" value="create-tables"/>
<property name="eclipselink.ddl-generation.output-mode" value="database"/>

DO USE:

使用：

<property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
<property name="javax.persistence.schema-generation.create-source" value="metadata"/>
<property name="javax.persistence.schema-generation.drop-source" value="metadata"/>

I confirm this worked for me.

我确认这对我有用。

回答by Andreas

:) Just substitue with your data

:) 只需用您的数据替换

<property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
<property name="javax.persistence.schema-generation.create-source" value="metadata-then-script"/>
<property name="javax.persistence.sql-load-script-source" value="META-INF/seed.sql"/>

回答by Neo

This process offers executing sql before DDL statments whereas what would be nice (for example, to insert seed data ) is to have something which executes after DDL statements. I don't if I am missing something here. Can somebody please tell me how to execute sql AFTER eclipselink has created tables (when create-tables property is set to tru)

这个过程提供了在 DDL 语句之前执行 sql 而好的（例如，插入种子数据）是在 DDL 语句之后执行的东西。如果我在这里遗漏了什么，我不会。有人可以告诉我如何在 eclipselink 创建表后执行 sql（当 create-tables 属性设置为 tru 时）