Sunday, May 19, 2019

jdbcTemplate

Q- How to insert/update blog data using jdbcTemplate?

public int updateUserImage(String userId, byte[] image, String fileName) {

        System.out.println("insertImage" + jdbcTemplate);

        int statusRes = 0;

        String sql = "";

        String userIdLower = userId.toLowerCase();

        try {

          closeJdbcTemplateConnection(userId);

          TimeUnit.SECONDS.sleep(10);

         

             LobHandler lobHandler = new DefaultLobHandler();

             statusRes = jdbcTemplate.update(

                     "update  USERS set FILE_CONTENT = ?, FILE_NAME = ? WHERE lower(USER_ID) = ?",

                     new Object[] {new SqlLobValue(image, lobHandler),fileName,userIdLower},

                     new int[] {Types.BLOB,Types.VARCHAR,Types.VARCHAR});



        } catch (DataAccessException e) {

            e.printStackTrace();

        } catch (InterruptedException e) {

   // TODO Auto-generated catch block

   e.printStackTrace();

  } finally{

         try {

    jdbcTemplate.getDataSource().getConnection().close();

   } catch (SQLException e) {

    // TODO Auto-generated catch block

    e.printStackTrace();

   }

        }

      

        return statusRes;





    }


Q- How to close connection in java using jdbcTemplate?

public void closeJdbcTemplateConnection(String userId){



               String sql = "";

  try {

         // empty FILE_CONTENT and FILE_NAME before update

         sql = "update CO_USERS set FILE_CONTENT = '', FILE_NAME = '' WHERE lower(USER_ID) = '" + userId.toLowerCase() + "'";

   jdbcTemplate.update(sql);

   

   jdbcTemplate.getDataSource().getConnection().close();

  } catch (SQLException e) {

   // TODO Auto-generated catch block

   e.printStackTrace();

  }

}

1 comment:

  1. Why would you manually close a connection when using Spring JDBC? Spring handles that for you automatically - it's actually a primary reason why Spring JDBC is used over plain JDBC. Worse, depending on what DataSource you use, closing a connection may be the worst thing to do. Let Spring handle it because you never know what data pool you are using under the hood and instead of releasing the connection back to the pool, you will close it instead. All that ugly, bloated boilerplate code is why Spring is used today - it reduces all that code down to a few lines which leads to a more elegant and readable code.

    ReplyDelete