DDL with parameters in JdbcTemplate

[vinov]

Hi, I can't figure out how to execute DDL with parameters using some of the JdbcTemplate family classes. Specifically I am trying to create a user in oracle database and neither of these works:

  @Test
  public void testJdbcTemplate() {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    HashMap<String, Object> params = new HashMap<String, Object>();
    params.put("username", "myuser");
    params.put("password", "mypassword");
    jdbcTemplate.update("CREATE USER &username IDENTIFIED BY &password", params);
  }
  
  @Test
  public void testJdbcTemplate2() {
    SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
    jdbcTemplate.update("CREATE USER ? IDENTIFIED BY ?", "myuser", "mypassword");
  }

The result is always the same:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [CREATE USER ? IDENTIFIED BY ?]; nested exception is java.sql.SQLException: ORA-01935: missing user or role name

Also notice that in PL/SQL developer

create user myuser identified by mypassword

works great, whereas

create user 'myuser' identified by 'mypassword'

gives ORA-01935.

Is there a better way to do this, other than manually replacing placeholders in SQL?

[davey]

Its probably not good practive to create users within your app if you can get away with it. There are potentially security implications with your DB if you allow this.

Having said that, the problem is that the bind parameters are having 's placed around them as they are strings which Oracle then dislikes.

I can see 2 work arounds for this.

1. Hand craft the SQL string to execute to create the users.
2. Write a stored procedure that creates the users and call the SP from the Java code.