강좌 69 - 기업형으로 레이어를 나누는 이유
-
간소 버전
-
기업형 버전
강좌 70 - 서비스 함수 찾아내기
-
Servlet에서 업무 Service 분리 -> 별도의 클래스를 만들어서 DB를 이용하는 코드를 여기에 옮김
-
프로그램에서 시스템 단위로 서비스를 나누게 됨
강좌 71 - 서비스 클래스 구현하기
- 이제 컨트롤러에서는 필요한 서비스 클래스를 가져다 쓰기만 하면 되는 것
01. ProductsService.java
package service;
import java.util.List;
import com.mysql.cj.protocol.x.Notice;
import dto.Product;
public class ProductsService {
public List<Notice> getProductList(){
return null;
}
public List<Notice> getProductList(int page){
return null;
}
public List<Notice> getProductList(String field, String query, int page){
return null;
}
public int getProuductCount() {
return 0;
}
public int getProuductCount(String field, String query) {
return 0;
}
public Product getProduct(int id) {
return null;
}
public Product getNextProduct(int id) {
return null;
}
public Product getPrivousProduct(int id) {
return null;
}
}1. 유사한 코드들은 하나만 구현
// ProductsService.java
public List<Notice> getProductList(){
return getProductList("title", "", 1);
}
public List<Notice> getProductList(int page){
return getProductList("title", "", page);
}
// 가장 파라미터가 많은 거만 구현.
public List<Notice> getProductList(String field, String query, int page){
return null;
}
// 이름이 같은 유사한 코드들은 하나만 구현하고, 나머지는 그걸 호출하는 방식으로 해야 간편. 강좌 72 - getNoticeList 메소드의 SQL 쿼리 작성하기
 |  |
|---|
// ProductsService.java
public List<Notice> getProductList(String field, String query, int page){
String sql = " select * from product "
+ " order by productId desc "
+ " limit 0, 5 "; // 0 자리만 변수 1번째부터 5개
return null;
}
강좌 73,74 - getNext, previousNotice 메소드의 SQL 쿼리 작성하기
- 2:05~ getNextNotice는 당장 필요하지 않은 기능이라 다음에
강좌 75 - getNoticeList의 JDBC 코드 구현하기
ProductRepository.java
원래
// 원래
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import dto.Product;
public class ProductRepository {
public ArrayList<Product> getAllProducts() {
ArrayList<Product> listOfProducts = new ArrayList<Product>();
// service 클래스에서 구현할
try {
Connection con = ConnectionDb.getConnection();
Statement stmt = con.createStatement(); // 쿼리 실행
String sql = "select * from product";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
Product p = setProductVo(rs);
listOfProducts.add(p);
}
} catch (Exception e) {
System.out.println(e.getMessage());
System.out.println(e.getStackTrace());
}
return listOfProducts;
}
private Product setProductVo(ResultSet rs) throws SQLException {
Product p = new Product(rs.getString("productId"), rs.getString("pname"), rs.getInt("unitPrice"),
rs.getString("descriptions"), rs.getString("manufacturer"), rs.getString("category"),
rs.getLong("unitsInStock"), rs.getString("filename"));
return p;
}
public Product getProductById(String productId) {
Product p = null;
try {
Connection con = ConnectionDb.getConnection();
String sql = "select * from product where productId = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, productId);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
p = setProductVo(rs);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return p;
}
public void addProduct(Product product) {
try {
Connection con = ConnectionDb.getConnection();
String sql = "insert into product " + " values(" + " ?,?,?,?,?,?,?,?,?" + " );";
PreparedStatement pstmt = con.prepareStatement(sql);
setPstmt(pstmt, product);
pstmt.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
private void setPstmt(PreparedStatement pstmt, Product product) throws SQLException {
pstmt.setString(1, product.getProductId());
pstmt.setString(2, product.getPname());
pstmt.setInt(3, product.getUnitPrice());
pstmt.setString(4, product.getDescription());
pstmt.setString(5, product.getManufacturer());
pstmt.setString(6, product.getCategory());
pstmt.setLong(7, product.getUnitsInStock());
pstmt.setString(9, product.getFilename());
}
// @@@ 여기부터 구현
public ArrayList<Product> getProductByCategory(String category) {
ArrayList<Product> listOfCategoryProducts = new ArrayList<Product>();
Product p = null;
try {
Connection con = ConnectionDb.getConnection();
String sql = "select * from product where category = ? " ;
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, category);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
p = setProductVo(rs);
listOfCategoryProducts.add(p);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return listOfCategoryProducts;
}
}
변경 후
// service로 db 부분은 넘긴 버전
서브쿼리 적용
String sql = " select * "
+ " from (select * from product where pname = '갤럭시 워치5') N "
+ " order by productId desc "
+ " limit ?, 6 " // 0, 6, 12 번째부터 6개 -> ? = (page-1)*6
;적용후
ProductsService
package service;
import java.util.ArrayList;
import java.util.List;
import dao.ConnectionDb;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import dto.Product;
public class ProductsService {
private Product setProductVo(ResultSet rs) throws SQLException {
Product p = new Product(rs.getString("productId"), rs.getString("pname"), rs.getInt("unitPrice"),
rs.getString("descriptions"), rs.getString("manufacturer"), rs.getString("category"),
rs.getLong("unitsInStock"), rs.getString("filename"));
return p;
}
public List<Product> getProductList(){
return getProductList("pname", "", 1);
}
public List<Product> getProductList(int page){
return getProductList("pname", "", page);
}
public List<Product> getProductList(String field/*pname, descriptions*/, String query/*갤럭시*/, int page){
List<Product> listOfProducts = new ArrayList<Product>();
String sql = " select * "
+ " from (select * from product where "+ field + " like ? ) N "
+ " order by unitPrice desc "
+ " limit ?, 6 " // 0, 6, 12 번째부터 6개 -> ? = (page-1)*6
;
try {
Connection con = ConnectionDb.getConnection();
PreparedStatement stmt = con.prepareStatement(sql); // 쿼리 실행
stmt.setString(1, "%" + query + "%");
stmt.setInt(2, (page-1)*6 );
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Product p = setProductVo(rs);
listOfProducts.add(p);
}
} catch (Exception e) {
System.out.println(e.getMessage());
System.out.println(e.getStackTrace());
}
return listOfProducts;
}
// 이름이 같은 유사한 코드들은 하나만 구현하고, 나머지는 그걸 호출하는 방식으로 해야 간편. 가장 파라미터가 많은 거 구현.
public int getProuductCount() {
return getProuductCount("pname", "");
}
public int getProuductCount(String field, String query) {
return 0;
}
public Product getProduct(String id) {
String sql = "select * from product where productId = ? ";
return null;
}
public Product getNextProduct(String id) {
return null;
}
public Product getPrivousProduct(String id) {
return null;
}
}
ProductRepository
package dao;
import java.util.ArrayList;
import java.util.List;
import dto.Product;
import service.ProductsService;
public class ProductRepository {
public List<Product> getAllProducts() {
ProductsService productsService = new ProductsService();
List<Product> listOfProducts = productsService.getProductList();
return listOfProducts;
}
public Product getProductById(String productId) {
Product p = null;
try {
Connection con = ConnectionDb.getConnection();
String sql = "select * from product where productId = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, productId);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
p = setProductVo(rs);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return p;
}
public void addProduct(Product product) {
try {
Connection con = ConnectionDb.getConnection();
String sql = "insert into product " + " values(" + " ?,?,?,?,?,?,?,?,?" + " );";
PreparedStatement pstmt = con.prepareStatement(sql);
setPstmt(pstmt, product);
pstmt.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
private void setPstmt(PreparedStatement pstmt, Product product) throws SQLException {
pstmt.setString(1, product.getProductId());
pstmt.setString(2, product.getPname());
pstmt.setInt(3, product.getUnitPrice());
pstmt.setString(4, product.getDescription());
pstmt.setString(5, product.getManufacturer());
pstmt.setString(6, product.getCategory());
pstmt.setLong(7, product.getUnitsInStock());
pstmt.setString(9, product.getFilename());
}
// @@@ 여기부터 구현
public ArrayList<Product> getProductByCategory(String category) {
ArrayList<Product> listOfCategoryProducts = new ArrayList<Product>();
Product p = null;
try {
Connection con = ConnectionDb.getConnection();
String sql = "select * from product where category = ? " ;
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, category);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
p = setProductVo(rs);
listOfCategoryProducts.add(p);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return listOfCategoryProducts;
}
}
-
unitPrice desc 가격 내림차순으로 6개씩 적용된 모습
-
정리
- 컨트롤러는 사용자의 입력, 출력 상호작용에 포커스를 맞추고
- 서비스는 데이터만 전문으로 하는 것
강좌 76 - NoticeService 클래스 완성하기
getProuductCount(String field, String query)- 게시글 목록 가져왔을 때 목록에서 페이지 되지 않은 레코드 개수가 몇 개인지 알 수 있는 함수
- 손 봐야함
public int getProuductCount(String field, String query) {
int count = 0 ;
String sql = " select COUNT(id) count "
+ " from (select * from product where "+ field + " like ? ) N "
+ " order by unitPrice desc "
;
try {
Connection con = ConnectionDb.getConnection();
PreparedStatement stmt = con.prepareStatement(sql); // 쿼리 실행
stmt.setString(1, "%" + query + "%");
ResultSet rs = stmt.executeQuery();
count = rs.getInt("count");
} catch (Exception e) {
System.out.println(e.getMessage());
System.out.println(e.getStackTrace());
}
return count;
}getProduct(String id)
public Product getProduct(String productId) {
Product p = null;
try {
Connection con = ConnectionDb.getConnection();
String sql = "select * from product where productId = ? ";
PreparedStatement pstmt = con.prepareStatement(sql); // 쿼리 실행
pstmt.setString(1, productId);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
p = setProductVo(rs);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return p;
}
비전공자 독학러. 일단 쌔린다. 개발 공부👊