How SpringBoot JPA run DDL sql with dynamic tableName?

Question

Yestody,I got this question, how jpa run DDL sql with dynamic tableName? usually,I just used DQL and DML like 'select,insert,update,delete'. such as :

public interface UserRepository extends JpaRepository<User, Integer> {

    @Query(value = "select a.* from user a where a.username = ? and a.password = ?", nativeQuery = true)
    List<User> loginCheck(String username, String password);

}

but when I required run DDL sql below

String sql = "create table " + tableName + " as select * from user where login_flag = '1'";

I don't find a way to solve this with Jpa (or EntityManager). Finally I used JDBC to run the DDL sql,but I think it's ugly...

Connection conn = null;
PreparedStatement ps = null;
String sql=" create table " + tableName + " as select * from user where login_flag = '1' ";
try {
    Class.forName(drive);
    conn = DriverManager.getConnection(url, username, password);
    ps = conn.prepareStatement(sql);
    ps.executeUpdate();
    ps.close();
    conn.close();
} catch (Exception e) {
    e.printStackTrace();
}

So,can jpa run DDL sql(such as CREATE/DROP/ALTER) wiht dynamic tableName in an easy way?

crizzis · Accepted Answer · 2019-10-23T21:01:04.950

1

Your question seems to consist of two parts

The first part

can jpa run DDL sql

Sure, just use entityManager.createNativeQuery("CREATE TABLE ...").executeUpdate(). This is probably not the best idea (you should be using a database migration tool like Flyway or Liquibase for DB creation), but it will work.

Note that you might run into some issues, e.g. different RDBMSes have different requirements regarding transactions around DDL statements, but they can be solved quite easily most of the time.

You're probably wondering how to get hold of an EntityManager when using Spring Data. See here for an explanation on how to create custom repository fragments where you can inject virtually anything you need.

The second part

with dynamic tableName

JPA only supports parameters in certain clauses within the query, and identifiers are not one of them. You'll need to use string concatenation, I'm afraid.

Why dynamic table names, though? It's not like your entity definitions are going to change at runtime. Static DDL scripts are generally less error-prone.

edited Oct 23 '19 at 21:01

answered Oct 23 '19 at 20:56

crizzis

9,978
2
28
47

I try the `entityManager.createNativeQuery("CREATE TABLE ...").executeUpdate()` , but I got some wrong and I can not fix it. I see [spring-data-jpa docs](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.single-repository-behavior), and find no solution. About dynamic tableName, I want backup the db table or take photo to the data Your reminded me that I can realize my needs use another way. – Daiql Oct 24 '19 at 01:27
Well, without seeing the error I can't quite tell what went wrong. BTW I forgot to tell you that DDL should also work with regular repository methods using `@Modifying @Query(nativeQuery = true, value =...)`, without resorting to repository fragments, but then you lose the ability to provide table names dynamically – crizzis Oct 24 '19 at 06:50
Ok,I will think another way – Daiql Oct 28 '19 at 03:21

How SpringBoot JPA run DDL sql with dynamic tableName?

1 Answers1