Announcement Announcement Module
No announcement yet.
SimpleJdbcInsert - sending in sql to a parameter Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • SimpleJdbcInsert - sending in sql to a parameter

    Couldn't find this answer...

    Anyone know how to send in sql to one of the parameters in a SimpleJdbcInsert?

    For example, how to convert this insert into a SimpleJdbcInsert?

    insert into fault_report values (null,'1111',0,(select id from fault where code = 'UNKNOWN'), 'info',now(),null,null);
    Now i realize that the subquery (selecting the id from the fault table) should arguably be a separate api - fair enough - but is there a way to at least execute 'now()' - or the relevant function for a given db (i'm on mysql)?

    SqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("user_id", msId)
          .addValue("fault_id", faultId)
          .addValue("faultCode", faultCode)
          .addValue("info", info)
          .addValue("when_reported", "now()", Types.DATE); // i tried lots of diff types here
          //.addValue("when_reported", new Date(), Types.DATE);
          Number newId = insertFaultReport.executeAndReturnKey(parameters);
    It works if i send in a date but I would like to utilize the time on the db server not the app server.

    I thought of using a simpleJdbcTemplate.update() with an insert, but then i don't get the id back...

    Last edited by vshankar; Mar 31st, 2010, 10:52 PM.

  • #2
    Hi vshankar,

    Check out :

    As far as spring jdbc support for the same goes:

    Try looking for SqlFunction

    Hope it helps.

    Pankaj Chaswal


    • #3
      Hi vshankar,
      You can use database trigger, If you have no problem to use that. Before insert or update you will set date and id using trigger. We solve this type of problem using trigger in oracle database.


      • #4
        Thanks for the feedback guys, but essentially that was not really what I was after but may help others. Perhaps another way to ask this question is - is there any way with SimpleJdbcInsert to NOT bind all the parameters and I think the answer to that is no which is fair enough.

        I ended up using the old 'jdbcTemplate' and not binding the parameter to which I would like to "execute sql" as follows:

        final String sql = "INSERT INTO fault_report (ms_id,bts_id,fault_id,info,when_reported) "+
              "values (?,?,?,?,now())";
              KeyHolder keyHolder = new GeneratedKeyHolder();
                  new PreparedStatementCreator() {
                      public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                          PreparedStatement ps =
                              connection.prepareStatement(sql, new String[] {"id"});
                          ps.setString(1, msId);
                          ps.setInt(2, btsId);
                          ps.setInt(3, faultId);
                          ps.setString(4, info.toString());
                          return ps;
              faultReportId = keyHolder.getKey().longValue();

        @bornleo26 - thanks for the reference, but imho the SqlFunction really doesn't do heaps more than executing a jdbcTemplate.queryForXXX - in - both cases it's another hit to the db.

        @unam - nice brainstorm with the trigger but i'm really not a fan of triggers except in a few circumstances; hidden and just a bit of overkill imho. Generally very much a fan of keeping things simple!

        thanks for the help anyway!