Announcement Announcement Module
Collapse
No announcement yet.
DDL with parameters in JdbcTemplate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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:
    Code:
      @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:
    Code:
    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
    Code:
    create user myuser identified by mypassword
    works great, whereas
    Code:
    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.

    Comment

    Working...
    X