Spring
230203 Spring Service, DAO 예제 DB연결
주영재
2023. 2. 3. 19:56
이전의 예제를 DB와 연결
인터페이스와 VO는 당연히 구축해놓았으나 가독성의 편의를 위해 본문에서 기술하지 않는다.
Controller.java
package com.simple.controller;
import java.util.ArrayList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import com.simple.command.ScoreVO;
import com.simple.score.service.ScoreService;
@Controller
@RequestMapping("service")
public class ServiceController {
@Autowired
@Qualifier("service")
private ScoreService service; //인터페이스 타입 선언
//화면출력
@RequestMapping("/scoreRegist")
public void rigistView() {}
//폼요청
@PostMapping("/regist")
public String regist(ScoreVO vo) {
//System.out.println(vo.toString()) 넘어오는지 확인용;
service.regist(vo);
return "service/scoreResult";
}
//목록화면
@RequestMapping("/scoreList")
public String scoreList(Model model) {
//데이터조회
ArrayList<ScoreVO> list=service.getList();
model.addAttribute("list",list);
return "service/scoreList";
}
//삭제요청
@RequestMapping("/delete")
public String delete(@RequestParam("num")int num) {
service.delete(num);
return "redirect:/service/scoreList";//삭제후에 목록으로
}
//수정
@RequestMapping("/modify")
public String modify(@ModelAttribute("num")int num,
@ModelAttribute("name")String name,
@ModelAttribute("kor")String kor,
@ModelAttribute("eng")String eng) {
return "service/scoreModify";
}
@RequestMapping("/modifyThis")
public String modifyThis(ScoreVO vo) {
service.modify(vo.getNum(),vo.getName(),vo.getKor(),vo.getEng());
return "redirect:/service/scoreList";
}
}ScoreServiceImpl.java
package com.simple.score.service;
import java.util.ArrayList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import com.simple.command.ScoreVO;
import com.simple.score.dao.ScoreDAO;
//@Controller
//@Repository
//@Component
@Service("service") //빈의 이름명시
public class ScoreServiceImpl implements ScoreService {
@Autowired
@Qualifier("scoreDAO")
private ScoreDAO scoreDAO;
public void regist(ScoreVO vo) {
scoreDAO.regist(vo);
}
public ArrayList<ScoreVO> getList() {
//ArrayList<ScoreVO> list=scoreDAO.getList();
return scoreDAO.getList();
}
@Override
public void delete(int num) {
scoreDAO.delete(num);
}
@Override
public void modify(int num, String name, String kor, String eng) {
scoreDAO.modify(num,name,kor,eng);
}
}ScoreDAO.java
package com.simple.score.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.simple.command.ScoreVO;
@Repository("scoreDAO")
public class ScoreDAOImpl implements ScoreDAO{
@Autowired
private DataSource dataSource;
@Override
public void regist(ScoreVO vo) {
String sql="insert into score(name, kor, eng) values(?,?,?)";
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn=dataSource.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, vo.getName());
pstmt.setString(2, vo.getKor());
pstmt.setString(3, vo.getEng());
pstmt.executeUpdate(); //insert, update, delete
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(conn !=null) conn.close();
if(pstmt !=null) pstmt.close();
} catch (Exception e2) {
}
}
}
@Override
public ArrayList<ScoreVO> getList() {
ArrayList<ScoreVO> list=new ArrayList<>();
String sql="select * from score order by num desc";
Connection conn = null;
PreparedStatement pstmt=null;
ResultSet rs =null;
try {
conn=dataSource.getConnection();
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
//ORM->Object Relational Mapping 객체와 데이터에서 나온 것들을 붙여주는 작업
while(rs.next()) {
ScoreVO vo=new ScoreVO();
//자동형변환된다
vo.setNum(rs.getInt("num")); //column명. 대소문자를 구별한다면 하나하나 중요해진다.
vo.setName(rs.getString("name"));
vo.setKor(rs.getString("kor"));
vo.setEng(rs.getString("eng"));
list.add(vo);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(conn !=null) conn.close();
if(pstmt !=null) pstmt.close();
if(rs !=null) rs.close();
} catch (Exception e2) {
}
}
return list;
}
@Override
public void delete(int num) {
//삭제기능-num는 key가 아니라 index가 넘어옵니다. (화면에서 key를 넘기도록 변경)
//delete기능 구현.
String sql="delete from score where num=?";
Connection conn = null;
PreparedStatement pstmt=null;
try {
conn=dataSource.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,num);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(conn !=null) conn.close();
if(pstmt !=null) pstmt.close();
} catch (Exception e2) {
}
}
}
@Override
public void modify(int num, String name, String kor, String eng) {
String sql="update score set name=?, kor=?, eng=? where num=?";
Connection conn = null;
PreparedStatement pstmt=null;
try {
conn=dataSource.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, name );
pstmt.setString(2, kor);
pstmt.setString(3, eng);
pstmt.setInt(4,num );
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(conn !=null) conn.close();
if(pstmt !=null) pstmt.close();
} catch (Exception e2) {
}
}
}
}db와 연동하였기 때문에
@Autowired
private DataSource dataSource;를 사용할 수 있음.
