java, tutorial, spring, api, rest : simple insert,update,delete (CRUD) dengan NamedParameterJdbcTemplate

Kali ini saya akan menuliskan sebuah tutorial aplikasi REST untuk melakukan CRUD dengan menggunakan NamedParameterJdbcTemplate. Mungkin hampir sama sih kalo kita menggunakan JdbcTemplate

1. untuk pertama mungkin kita perlu membuat sebuah domain, misal domain user dengan isi sebagai berikut.

package com.eka.auth.domain;

/**
 *
 * @author pnukeid
 */
public class Users {



    private String username;
    private String password;
    private Boolean enabled;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Boolean getEnabled() {
        return enabled;
    }

    public void setEnabled(Boolean enabled) {
        this.enabled = enabled;
    }

}

2. selanjutnya adalah membuat dao interface dan dao implementation.


ini adalah contoh interface dao

package com.eka.auth.dao;

import com.eka.auth.domain.Users;
import com.eka.auth.model.InputPagging;
import java.util.List;

/**

 * @author pnukeid
 */

public interface UsersDao  {
    
    public void save(Users users) throws Exception;
    public void update(Users users) throws Exception;
    public void delete(Users users) throws Exception;
    public Users findByUserName(String username) throws Exception;
    public List<users> findAll(InputPagging pagging) throws Exception;
    public Integer count() throws Exception;
}

dan berikutnya adalah implementation dari dao interface nya.

package com.noi.auth.dao.impl;

import com.eka.auth.dao.UsersDao;
import com.eka.auth.domain.Users;
import com.eka.auth.mapper.UsersExcludePasswordMapper;
import com.eka.auth.mapper.UsersMapper;
import com.eka.auth.model.InputPagging;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

/**
 *
 * @author pnukeid
 */
@Repository
public class UsersDaoImpl implements UsersDao {


    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void save(Users users) throws Exception {
        String SQL = "INSERT INTO users (username, password, enabled) VALUES (:username, :password, :enabled)";
        Map namedParameters = new HashMap();
        namedParameters.put("username", users.getUsername());
        namedParameters.put("password", users.getPassword());
        namedParameters.put("enabled", users.getEnabled());
        namedParameterJdbcTemplate.update(SQL, namedParameters);
    }

    public void update(Users users) throws Exception {
        String SQL = "UPDATE users SET password = :password, enabled = :enabled WHERE username = :username";
        Map namedParameters = new HashMap();
        namedParameters.put("username", users.getUsername());
        namedParameters.put("password", users.getPassword());
        namedParameters.put("enabled", users.getEnabled());
        namedParameterJdbcTemplate.update(SQL, namedParameters);
    }

    public void delete(Users users) throws Exception {
        String SQL = "DELETE FROM users WHERE username = :username";
        Map namedParameters = new HashMap();
        namedParameters.put("username", users.getUsername());
        namedParameterJdbcTemplate.update(SQL, namedParameters);
    }

    public Users findByUserName(String username) throws Exception {

        String SQL = "SELECT count(*) FROM users WHERE username = :username";
        SqlParameterSource sqlParam = new MapSqlParameterSource().addValue("username", username);
        Integer usersCount = namedParameterJdbcTemplate.queryForObject(SQL, sqlParam, Integer.class);

        Users users = null;

        if (usersCount != 0) {
            SQL = "SELECT * FROM users WHERE username = :username";
            SqlParameterSource namedParameters = new MapSqlParameterSource("username", username);
            users = (Users) namedParameterJdbcTemplate.queryForObject(SQL, namedParameters, new UsersMapper());
        } 
        return users;
    }

    public List<users> findAll(InputPagging pagging) throws Exception {
//        namedJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplateOauth);
        String SQL = "SELECT * FROM users LIMIT :offset,:count";
        SqlParameterSource sqlParam = new MapSqlParameterSource().addValue("offset", pagging.getOffset()).addValue("count", pagging.getLimit());
        List<users> users = (List<users>) namedParameterJdbcTemplate.query(SQL, sqlParam, new UsersMapper());
        return users;
    }

    public Integer count() throws Exception {
        String SQL = "SELECT count(*) FROM users";
        SqlParameterSource sqlParam = new MapSqlParameterSource().addValue("", "");
        Integer usersCount = -100;
        return usersCount = namedParameterJdbcTemplate.queryForObject(SQL, sqlParam, Integer.class);
    }

}

3. selanjutnya adalah service. service di java ini berperan sebagai rule bisnisnya. jadi segala kegiatan process bisnis berjalan di service ini. ada kalanya definisi service interface ini tidak jauh beda dengan yang sudah kita definisikan di dao interface.

package com.eka.auth.service;


import com.eka.auth.domain.Users;
import com.eka.auth.model.InputPagging;

import java.util.List;

/**

 * @author pnukeid
 */
public interface UsersService {
    
    public void save(Users users) throws Exception;
    public void delete(String username) throws Exception;
    public Users findUserByUserName(String username) throws Exception;
    public Boolean findUserIsAvailableByUserName(String username) throws Exception;
    public List<users> findAll(InputPagging pagging) throws Exception;
}

dan untuk service implementationnya.

package com.eka.auth.service.impl;


import com.eka.auth.dao.UsersDao;
import com.eka.auth.domain.Users;
import com.eka.auth.model.InputPagging;
import com.eka.auth.service.UsersService;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author pnukeid
 */
@Service
public class UsersServiceImpl implements UsersService {
    
    @Autowired
    private UsersDao usersDao;
     
    @Transactional
    public void save(Users users) throws Exception {
        
        Users users = new Users();        
        users.setUsername(userProfiles.getUsername());
        users.setPassword(userProfiles.getPassword());
        users.setEnabled(userProfiles.getEnabled());

        if(usersDao.findByUserName(userProfiles.getUsername()) == null){
            usersDao.save(users);
        }else{
            usersDao.update(users);
        }
    }

    @Transactional
    public void delete(String username) throws Exception {

        Users users = usersDao.findByUserName(username);
        usersDao.delete(users);
    }

    public List<users> findAll(InputPagging pagging) throws Exception {
        return usersDao.findAll(pagging);
    }
 

    public Users findUserByUserName(String username) throws Exception {
        return usersDao.findByUserName(username);
    }

    public Boolean findUserIsAvailableByUserName(String username) throws Exception {
        Boolean usernameIsAvailable = false;
        
        if(usersDao.findByUserName(username) == null)
            usernameIsAvailable = true;
        
        return usernameIsAvailable;
    }
}

untuk yang terakhir adalah mmebuat REST apinya. yang nantinya aplikasi yang selesai dibuat ini akan bisa diakses oleh apa saja.

package com.eka.auth.admin.controller.ajax;

import com.eka.auth.domain.Users;
import com.eka.auth.model.InputPagging;
import com.eka.auth.model.InputUsername;
import com.eka.auth.service.UsersService;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

/**
 *
 * @author pnukeid
 */
@Controller
@RequestMapping("api/v1/admin/user")
public class UserAdminAjaxController {
    
    @Autowired
    private UsersService usersService;
    
    
    /* GET LIST ALL USER FROM DATABASE*/
    @ResponseBody
    @RequestMapping(consumes = {MediaType.APPLICATION_JSON_VALUE}, produces = {MediaType.APPLICATION_JSON_VALUE},
            method = RequestMethod.POST,
            value = "/getdata")
    ResponseEntity<Object> getAllUser(
            @RequestBody InputPagging requestBody,
            HttpServletResponse response) throws Exception {
        Users resultPagging = usersService.findAll(requestBody);
        return new ResponseEntity<Object>(resultPagging, HttpStatus.OK);
    }
    
    /* SAVE / UPDATE USER */
    @ResponseBody
    @RequestMapping(consumes = {MediaType.APPLICATION_JSON_VALUE},
            method = RequestMethod.POST,
            value = "/")
    ResponseEntity<Object> saveUser(
            @RequestBody UsersProfiles requestBody,
            HttpServletResponse response) throws Exception {
        usersService.save(requestBody);
        return new ResponseEntity<Object>("saved", HttpStatus.OK);
    }
    
    /* DELETE USER */
    @ResponseBody
    @RequestMapping(consumes = {MediaType.APPLICATION_JSON_VALUE},
            method = RequestMethod.DELETE,
            value = "/")
    ResponseEntity<Object> deleteUser(
            @RequestBody InputUsername requestBody,
            HttpServletResponse response) throws Exception {
        String username = requestBody.getUsername();
        usersService.delete(username);
        return new ResponseEntity<Object>("deleted", HttpStatus.OK);
    }
    
    /* FIND USERNAME IS AVAILABLE OR NOT*/
    
    @ResponseBody
    @RequestMapping(produces = {MediaType.APPLICATION_JSON_VALUE},
            method = RequestMethod.POST,
            value = "/available")
    ResponseEntity<Object> checkUserName(
            HttpServletRequest request,
            HttpServletResponse response) throws Exception {
        Boolean isAvailable;
        String username = request.getParameter("username");
        isAvailable = usersService.findUserIsAvailableByUserName(username);
        return new ResponseEntity<Object>(isAvailable, HttpStatus.OK);
    }

}
 

sekian.