9

I am using Spring JdbcTemplate with the DAO pattern to access a database. Instead of creating the database tables manually, I am looking for a way to generate the tables in the DAO layer. I understand that I can use the JdbcTemplate to execute statements, I am only looking for the right place to do it.

Is there a best practice for that?

dustin.schultz
  • 13,076
  • 7
  • 54
  • 63
Juri Glass
  • 88,173
  • 8
  • 33
  • 46
  • 4
    Creating tables programmatically is a smell. In real world (and with good datamodels) you normally just have to create tables only once and use them forever. Are you **sure** you need to do so? If not, please post a new question how to change the datamodel so that you don't need to create new table for every hiccup. – BalusC Jan 20 '10 at 14:21
  • +1 for BalusC. If you want a reproducible, automated way of creating tables, create a DDL or some other script that can be executed when your environment is being set up. In other words, this should not be handled in code. – Droo Jan 20 '10 at 15:32
  • @BalusC, a logical place to execute a DDL setup could be an install operation, which might be distributed as part of your code -- why add something other than the current DAO to deal with this often necessary step? – Mark Elliot Jan 21 '10 at 22:15
  • 2
    I agree that it's dangerous for production, but I find that creating the tables in the code saves a ton of time for prototyping and quickly setting up local development environments. I like to create a maven profile to ensure I don't accidentally run the table drop/create code against production configs. For example, it's sometimes super convenient to hand the code to another developer and tell them to simply run something like: `mvn -Pprototype jetty:run`. – Upgradingdave Jul 12 '12 at 15:42

3 Answers3

12

You can use the execute(String) method:

public void execute(String sql) throws DataAccessException

Issue a single SQL execute, typically a DDL statement.
Specified by: execute in interface JdbcOperations

Parameters: sql - static SQL to execute

Throws: DataAccessException - if there is any problem

However as beny23 mentions I would be suspicious of an actual need to do this programatically in a live application.

matt b
  • 138,234
  • 66
  • 282
  • 345
1

Use .update() methods available in the (Simple)JdbcOperations, the number they return is the number of affected rows. They're supposed to be specifically used for both INSERT and UPDATE statements.

Esko
  • 29,022
  • 11
  • 55
  • 82
1

Slightly offtopic:

Is it absolutely necessary that you need to execute the DDL commands from within your code? In fact I do think it is a good idea to have separation between db admin and db usage. Our Oracle database security setup here is actually set up so that the tables are set up using a different database user (DB_OWNER), than the one running the SELECTs, INSERTs, DELETEs are run by DB_USER.

This prevents accidentially deleting tables or modifying the schema and also allows the DB_USER to be setup such that only the privileges that are absolutely necessary are granted, which adds a layer of security.

I suppose it depends on the nature of your service/application, but think about the benefit of creating the tables inside the code (and whether a possible bug in the DDL code could accidentially destroy production data).

beny23
  • 34,390
  • 5
  • 82
  • 85
  • yes, agree on that, we used to have DB_USER which can only SELECT and CALL PROCEDURE, so if happen that hackers somehow steal usernam/pass (used by application exposed on web for instance), they can only select from database with it... or they can call stored procedure, which will handle checks before and insert something which will leave database in consistent state, so the damage would be smaller – ante.sabo Jan 20 '10 at 13:52
  • Suppose you're doing a fresh installation, do you distribute another application to run your DDL, or do you bundle the DDL and have installation logic in your application? – Mark Elliot Jan 21 '10 at 22:17
  • I guess that would depend on the type of application using the database. In a lot of production environment, having a separate DDL script is absolutely necessary to hand over to the DBA team which will then approve the changes before running the script and the code developers wouldn't have any access to the production database and having the app modify the DB would be a definite no-no. Having said that, AFAIK Hibernate can automatically generate the tables for you, though personally I wouldn't use it, as the index and constraint names be unreadable which makes DB maintenance more complicated. – beny23 Jan 22 '10 at 00:39