Announcement Announcement Module
No announcement yet.
DDL with parameters in JdbcTemplate Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • DDL with parameters in JdbcTemplate

    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:
      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);
      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?

  • #2
    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.