Announcement Announcement Module
Collapse
No announcement yet.
How to use preparedstatement functionality Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to use preparedstatement functionality

    Hello all,

    I am still new to using Spring/Hibernate but I am having great success with the Hibernate support portion of the Spring framework, but now I need to also use the JdbcTemplate for some parts of my JSF application. How do you handle the following situation using jdbctemplate?

    1) I have all my sql code in xml file(s) and I use an xml reader class to pass the sql to a method

    2) My sql contains the necessary placeholders so I use an .addParms method to add the necessary parameters to a preparedstatement.

    3) I execute my sql object and get either a list or a status result.

    I have been using SqlExecutor in the past so it creates the preparedstatement object for me and has helper methods to add the necessary parameters.

    My xml stanzas look something like this:

    <updatePermission>
    UPDATE SEC_PERMISSION
    SET TAG = ?,
    DESCRIPTION = ?,
    DATE_MODIFIED = ?
    WHERE PERMISSION_ID = ?
    </updatePermission>

    and my java code that passes this data to SqlExecutor looks something like this:

    String sql = XmlResourceReader.getDocumentElementValue("updateP ermission");
    try {
    QueryService.setJbossConnection(dataSource);
    QueryService.addParam(strTagName);
    QueryService.addParam(strDesc);
    QueryService.addParam(date);
    QueryService.addParam(Integer.toString(Id));
    SQLResults rs = QueryService.runJBossQueryCloseCon(sql);
    log.info(sql);
    } catch (Exception e) {
    String msg = "VariableManager.deleteMemberValues(): " + e.toString();
    throw new VariableException(msg,sql,e);
    }
    All calls to the QueryService instance check to see if parameters exists and if so add them in the order in which they appear in the list.

    I am sure Spring has similar functionality and I have come across the PreparedStatementCreator and PreparedStatementSetter but I am not sure how to use these Interfaces (can someone provide an example of how Spring developers do the equivelant of what I do with SqlExecutor?).

    Thanks!

  • #2
    You could setup a jdbc template, like,

    Code:
    DriverManagerDataSource dataSource = new DriverManagerDataSource&#40;&#41;;
    dataSource.setDriverClassName&#40;"drivername"&#41;;
    dataSource.setUrl&#40;"dburl"&#41;;
    dataSource.setUsername&#40;"user"&#41;;
    dataSource.setPassword&#40;"password"&#41;;        
    
    JdbcTemplate template = new JdbcTemplate&#40;dataSource&#41;;
    The data source and templates could also be defined and got from the application context.

    And do your update using, JdbcTemplate's: update(String sql, Object[] args): int

    Code:
    template.update&#40;XmlResourceReader.getDocumentElementValue&#40;"updatePermission"&#41;, new Object&#91;&#93; &#123;strTagName, strDesc, date, id&#125;&#41;;
    Or alternatively use, JdbcTemplate's: update(String sql, PreparedStatementSetter): int

    Code:
            template.update&#40;XmlResourceReader.getDocumentElementValue&#40;"updatePermission"&#41;,
    					 			   new PreparedStatementSetter&#40;&#41; &#123;
    							            public void setValues&#40;PreparedStatement ps&#41; throws SQLException &#123;
                                       ps.setString&#40;1, strTagName&#41;;
                                       ps.setString&#40;2, strDesc&#41;;
                                       ps.setDate&#40;3, date&#41;;
                                       ps.setInt&#40;4, id&#41;;
    							            &#125;
    					        	  &#125;                
            &#41;;
    Or alternatively use, JdbcTemplate's: update(PreparedStatementCreator psc): int

    Code:
    		template.update&#40; new PreparedStatementCreator&#40;&#41; &#123;
    			public PreparedStatement createPreparedStatement&#40;Connection conn&#41; throws SQLException &#123;
    				PreparedStatement ps = conn.prepareStatement&#40;XmlResourceReader.getDocumentElementValue&#40;"updatePermission"&#41;&#41;;
                                       ps.setString&#40;1, strTagName&#41;;
                                       ps.setString&#40;2, strDesc&#41;;
                                       ps.setDate&#40;3, date&#41;;
                                       ps.setInt&#40;4, id&#41;;
    				return ps;
    			&#125;
    		&#125;&#41;;
    Regards,

    Amit

    Comment


    • #3
      Hello Amit,

      Thank you so much! That's eactly what I needed.

      Cheers

      Comment


      • #4
        How to use callablestatement functionality

        Hi,

        Can you please tell me how to implement callablestatement in the same way as preparestatement.

        Thanks in advance
        haritha

        Comment

        Working...
        X