Hello, I've searched around but haven't found the answer to my question. We currently have an import.sql file we use in our project (src\main\resources) and our system is dependent on this import script being executed. We've been using the hbm2ddl.auto property but after some reading it seems this is a bad idea in production (we are getting ready to deploy.) If we remove this property, hibernate no longer creates the schema automatically. Reading through the docs, it seems we may need to use the schema generation utility and then have to manually execute the script ourselves.

I'm wondering, what are others doing in production environments? Is there a way to automate this, or something in here I misunderstood?

Thank you everyone.

I personally avoid any automagic in production - I would only use tools to generate initial DDL. I would run schema changes separately - in a separate ant task, I believe this is most flexible.

Thanks for the reply. We are actually using maven in our project and I did notice a plugin for Hibernate, which I think will work for us.

I'm thinking (I believe this aligns with kostakys reply), we can use the hibernate maven plugin to generate the database schema. Then in production environment, we would manually run the script to create the database schema.

As far as import.sql, we could continue using this in development environment (which this was designed for?) along with the hbm2dll.auto property.

We would also maintain a second import script, which contains all records we need inserted for a production system to run. This would then need to be executed after the database schema was executed in production environment.

This sounds reasonable to me, but a bit "manual." Would anyone have any suggestions to make this less manual? Or is this typical in practice?

Thanks again

That's where ant (or maven) powers come in - you can control behaviour based on environment, eg. run smth. if in dev, and run smth else if in prod. usually you would want to have the flow as similar as possible, so why not have an empty script for dev and script with data for prod, eg. smth. like

<sql>
<fileset dir="${env.db.scripts}/data" include="*.sql">
</sql>

and in dev you have no files there, but in prod you put whatever you want - does it make sense?

Not sure I follow entirely. I think I get your point of leveraging maven to select an import script based on environment. In fact, I think we will need this as we have "dumby" data we use during development. So in development environment, we would need the required import script, in addition to the dumby data. In production, we just want required import script.

I apologize but I'm not as familiar with ant as I am with maven. What exactly does the <sql> tag do? I did a quick search but didn't really find much.

But when we deploy our artifact (WAR), it will be required for users to manually execute the SQL scripts (database schema and required import script). Am I correct on this? I should note that we want to distribute the WAR without requiring users to build it.

have a look here:

http://ant.apache.org/manual/Tasks/sql.html

Ideally I would use DBMS specific tools, eg. sqlplus for oracle because of its features like @file1.sql inside another.sql etc.
Re. distributing the built artifact - it is a matter of choice - eg. you can even write some java code to run some SQL once WAR is deployed - think about what level of user expertise your target audience should have. IMHO asking users to run ANYTHING manually is not good. Do it yourself to save your time.