Spring JDBC

Spring Jdbc Abstraction Layer. The Spring-JDBC component is a part of the Spring framework and is an abstraction on top of the standard Java JDBC API. It takes care of all the low-level API-calls and

• Online Compiler: http://tpcg.io/1kfiQn

   //lets imagine a case with an object named "Candy"
   public class CandyDataService{
       
       private static final String INSERT_QUERY = "INSERT INTO CANDY(param1,param2) VALUES(?,?)";
       private static final String DELETE_QUERY = "DELETE FROM CANDY WHERE ID=?";
       private static final String SELECT_ALL_QUERY = "SELECT * FROM CANDY";
       
       //Local database declarated, just for this examble
       HsqlDatabase db = new HsqlDatabase();
       
       //Database connection
       JdbcTemplate jdbcTemplate = new JdbcTemplate(
           new SingleConnectionDataSource(db.conn, false));
      
       //Insert
       private void insertCandy(Candy candy) {
           jdbcTemplate.update(INSERT_QUERY, param1, param2, param...);
       }
       
       //Multi-Inserts
       public void insertCandies(List<Candy> candies) {
           for (Candy candy : candies) {
               insertCandy(candy);
           }
       }
       
       //Delete Method
       public void deleteCandy(int id) {
           jdbcTemplate.update(DELETE_QUERY, id);
       }
       
       //Get all candies with Auto-Mapping
       public List<Candy> retrieveAllCandies() throws SQLException{
               jdbcTemplate.query(SELECT_ALL_QUERY,
                       new BeanPropertyRowMapper<Candy>(Candy.class));
       }   
       
       //Get all candies with Custom-Mapping
       public List<Candy> retrieveAllCandies() throws SQLException{
           jdbcTemplate.query(SELECT_ALL_QUERY, new RowMapper<Candy>(){
               public Candy mapRow(ResultSet rs, int rowNum) throws SQLException{
                   //OR: Solution 1
                   //Take the results set and set into the Candy object
                   return new Candy(rs.getInt(1), rs.getString(2), rs.getBoolean(3),...);
                   
                   //OR: Solution 2
                   //Using encapsulation
                   Candy candy = new Candy();
                   candy.setParam1(rs.getInt(1));
                   candy.setParam1(rs.getString(2));
                   candy.setParam3(rs.getBoolean(3));
                   return candy;
               }
           }
           //OR: Solution 3
           new CandyMapper());
       }
   }
       
   /**
    * Create a Mapper to help organizing the code in Custom-Mapping method
    * So it becomes reusable
    * Can also be declared as "Public class" and move it to a different Package
    */
   class CandyMapper implements RowMapper<Candy>{
       public Candy mapRow(ResultSet rs, int rowNum) throws SQLException{
           
           Candy candy = new Candy();
           candy.setParam1(rs.getInt(1));
           candy.setParam1(rs.getString(2));
           candy.setParam3(rs.getBoolean(3));
           return candy;
       }
   }