[Spring Boot] DB 쿼리 작성하기

우리가 java 언어를 사용하게 위해 사용하는 이클립스가 자바가 아니듯이 DB에버도 DB가 아니라 DB에 접속을 해주고 명령어를 전달해주는 역할을 한다.

우리가 DB를 설치할 때에도 JDBC를 다운받아야 했기 때문에 JDBC 라이브러리를 사용해 다운을 실행했다.
※ 스프링에서도 pom.xmal 에 동일한 라이브러리를 넣어주었다.

---

✨Statement 와 Prepared Statement

Data Base 의 데이터를 처리한는 Query문을 실행하기 위해 Connection 은 두 가지 방법을 제공한다.

• Statement : 쿼리문 실행 (한번 실행하고 다시 실행할 일이 없을 때)
  → 생성을 한 후에 깔끔하게 지워버린다.
  ex) 유저생성, 권한 넣기, 테이블 생성 등...

• Prepared Statement : 쿼리문 실행 (?을 이용해 다양한 쿼리문 대응)
  → 문장을 컴파일 한 후 재사용 한다. = 문장을 저장하고 있다가 재사용한다. (가지고 있다가 바뀌는 부분만 변경해 사용)
  → 때문에 문장이 여러 번 반복될 때 유리하며 성능과 속도 면에서 우수하다.
  ex) select, insert, update, delete..

---

Statment

• 생성 : Connection.cteateStatment()
• 실행 쿼리 : INSERT INTO member VALUES(‘kim’,’java’,95);
• 사용 메서드 1 : executeQuret() : ResultSet
• 사용 메서드 2 : executeUpdate() : int

Prepared Statement

• 생성 : Connection.Prepared Statement() (java.sql)
• 실행 쿼리 : INSERT INTO member VALUES90(?, ?, ?);
• 사용 메서드 1 : executeQurey():ResultSet (SELECT)
• 사용 메서드 2 : executeUpdate() : int (UPDATE, DELECT, INSERT)

→ executeQurey() 는 반환되는 데이터가 있을 경우 사용 한다.(SELECT)
→ executeUpdate() 는 DB 에 변화를 줄 경우 사용 한다.(UPDATE, DELETE, INSERT)
→ executeUpdate() 사용시 반환되는 int 는 적용에 성공한 데이터 행(row) 개수 이다.

---

📌 테이블 생성 예제 (create)

이러한 테이블을 만드는 쿼리문을 작성해보자

controller 작성

• view 에서 stmt 를 실행하면 아래 메서드가 실행된다.
• 사용자에게 받은 요청을 service 로 전달하기 위해 service 객체를 생성한다.
• DB 관련 Exception 을 throws로 넘겼기 때문에 controller에서 처리
  아무도 처리하지 않으면 tomcat으로 넘어가 예외를 뿌려버린다!

@RequestMapping(value="/stmt")
public String stmt(Model model) {
	logger.info("테이블 생성 요청");
	MainService service = new MainService();
	String msg = "";
	try {
		msg = service.stmt();
	} catch (SQLException e) {
		e.printStackTrace();
		msg = "데이터 처리 중 문제가 발생 했습니다.";
	}
	model.addAttribute("msg", msg);
	
	return "main";
}

service 작성

• DB관련 작업을 해야하기 때문에 DAO 객체를 생성하고, DAO에서 넘어오는 값이 true면 "테이블 생성에 성공했습니다" 라는 문구를 return

	public String stmt() throws SQLException {
		MainDAO dao = new MainDAO();
		String msg = "테이블 생성에 실패했습니다.";
		if(dao.stmt()) {
			msg = "테이블 생성에 성공했습니다.";
		}
		return msg;
	}

❗ DB 생성자 만들기

❗ 먼저 데이터베이스를 접속할 때마다 메서드에 접속 코드를 작성할 경우 코드의 중복이 발생하기 때문에 생성자로 만들어 DAO 객체를 생성하면 DB에 접속이 되도록 생성자를 만들어준다. (객체화를 하면 생성자가 가장 먼저 실행되고, 생성자는 초기화를 해준다!)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class MainDAO {
	Logger logger = LoggerFactory.getLogger(getClass());
	private Connection conn = null; 
    
	public MainDAO() {
		String id = "web_user";
		String pw = "pass";
		String url = "jdbc:mariadb://localhost:3306/mydb";
		String dirver = "org.mariadb.jdbc.Driver";
		
		try {
			Class.forName(dirver); // DB랑 java랑 연결
			conn = DriverManager.getConnection(url, id, pw); 
			logger.info("Connection : {}", conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

DAO 작성
생성자를 만들었으니 이제는 쿼리문을 작성해보자!

public boolean stmt() throws SQLException {
	String sql = "create table member("
			+ "	ID varchar(50) primary key,"
			+ "	PW varchar (100),"
			+ "	NAME varchar(20),"
			+ "	AGE int(4),"
			+ "	GNDER varchar(4),"
			+ "	EMAIL varchar(100)"
			+ ")";
		
	boolean success = false;
	try {
		Statement stmt = conn.createStatement();
		int row = stmt.executeUpdate(sql);
		logger.info("이 쿼리문을 실행해서 영향받은 데이터 갯수 : " + row); 
		success = true;
	} catch (Exception e) { 
		e.printStackTrace();
	}finally {
		conn.close();
	}
	return success;
}

테이블을 만들 경우 row는 만들어지지 않기 때문에 "이 쿼리문을 실행해서 영향받은 데이터 갯수" 는 0 이 나와야 한다.
(insert, update, delete 는 0개 이상이 나와야 한다.)

만약 테이블 성공에 완료했을 경우 true 를 반환해 그 값을 sercive → controller → view 로 보내 적절한 값을 출력해준다.

❌ 주의사항
만약 DB에서 쿼리문을 작성한 후 복사/붙여넣기를 할 때에는 오류 방지를 위해 ; 은 빼고 복사해주는게 좋다!

---

📌 정보 넣기 예제 (insert)

위에서 만들어 준 member 라는 테이블에 데이터를 넣는 코드를 작성해보자

controller

@RequestMapping(value="/pstmt")
public String patmt(Model model) {
	logger.info("insert 요청");
	MainService service = new MainService();
	String msg = "";
	try {
		msg = service.pstmt();
	} catch (Exception e) {
		e.printStackTrace();
		msg = "데이터 처리 중 문제가 발생했습니다.";
		msg = e.toString(); // 이거 쓰면 안됨!! 외부에 노출되니까
	}
	model.addAttribute("msg", msg);
	return "main";
}

service

• insert가 성공했을 경우 1개 이상의 행이 반환되기 때문에 row가 0 이상일 경우 insert가 완료되었다는 것으로 판단하여 msg에 성공 문구를 넣어 return을 해준다.

public String pstmt() throws SQLException{
	MainDAO dao = new MainDAO();
	
	String msg = "insert에 실패 했습니다.";
	int row = dao.pstmt();
	
	if(row > 0) {
		msg = "insert에" + row + "성공 했습니다.";
	}
	
	return msg;
}

DAO

• 쿼리문을 작성한다.

• insert를 하기 위해서 PreparedStatement 로 실행 객체를 준비한다.

• ? 에 대응을 해주기 위해 .setString() , .setint() 를 이용해 컬럼 순서에 맞춰 데이터를 넣어준다.
  ※ 컬럼의 데이터타입을 모르겠으면 일단 String로 넣어보자! → String으로 넣으면 되는 경우가 있다.
  .setString(인덱스번호/컬럼명, 넣어줄 데이터) 를 작성해주면 된다. (컬럼명의 경우 정확하게 알아야 하기 때문에 보통 인덱스 넘버를 사용한다.

• 다 사용한 자원은 반납해준다.

public int pstmt() throws SQLException {
	int row = 0;
	String sql = "INSERT INTO member(ID,PW,NAME,AGE,GENDER,EMAIL)VALUES(?,?,?,?,?,?)"; 
	PreparedStatement ps = conn.prepareStatement(sql);
	
	ps.setString(1, "admin"); 
	ps.setString(2, "pass");
	ps.setString(3, "홍길동");
	ps.setInt(4, 20); 
	ps.setString(5, "남자");
	ps.setString(6, "abc@naver.com");
	
	row = ps.executeUpdate();
	ps.close(); 
	conn.close(); 
       
	return row;
}

---

📌 데이터 조회 예제 (select)

member 테이블에 위에서 넣은 정보를 조회하는 코드를 작성해보자!

controller

@RequestMapping(value="/resultSet")
public String resultSet(Model model) {
	logger.info("slelect 요청!");
    
	MainService service = new MainService();
	try {
		String msg = service.resultSet();
	} catch (SQLException e) {
		e.printStackTrace();
		msg = "데이터 처리 중 문제가 발생 했습니다.";
	}
	model.addAttribute("msg", msg);
	return "main";
}

service

• DB를 건드려야 하지 때문에 DAO 객체를 생성해 메서드를 호출해주고,
• 조회된 행이 1개 이상일 경우 "데이터 호출에 성공했습니다" 문자열을 msg에 담아 ruturn해준다.

public String resultSet() throws SQLException {
	
	logger.info("slelect service 요청!");
	String msg = "데이터 호출에 실패 했습니다.";
	
	MainDAO dao = new MainDAO();
	int cnt = dao.resultSet();
	if(cnt > 0) {
		msg = "데이터 호출에 성공 했습니다.";
	}
	return msg;
}

DAO

• sql 쿼리를 작성해 변수에 담아주고, PreparedStatement 로 실행 객체를 준비한다.
• ResultSet : 안에 덩어리로 데이터가 들어가 있다.
  ※ Set 은 java의 컬렉션프레임워크에 나온 그 set과 같아서 순서가 없고, 중복을 허용하지 않는다.
• ResultSet 에 있는 데이터를 뽑아내기 위해 .next() 사용
• 다 사용했다면 자원을 반납하고 행의 수를 return 한다.

	public int resultSet() throws SQLException {
		
		logger.info("select DAO 요청");
		int row = 0;
		String sql = "SELECT * FROM member";
        
		PreparedStatement ps = conn.prepareStatement(sql);
		
		ResultSet rs = ps.executeQuery(); 
		
		while(rs.next()) {
			String id = rs.getString("ID");
			String pw = rs.getString(2); 
			String name = rs.getNString("NAME");
			int age = rs.getInt("AGE");
			String gender = rs.getString("GENDER");
			String email = rs.getString("EMAIL");
			logger.info(id + "/" + pw + "/" + name + "/" + age + "/" + gender + "/" + email);
			row++;
		}
		
		rs.close();
		ps.close();
		conn.close();
		
		return row;
	}

---

🚫 예외와 에러

이렇게 DB와 연결해서 값을 넣고 하다보면 예외와 에러를 많이 만나게 된다.
그럴때는 당황하지 말고 로그 를 보면 된다!!

아래로 긴 로그는 가장 위에 힌트가 있으니 위를 보고
옆으로 긴 로그는 가장 왼쪽(끝)에 힌트가 있으니 끝을 보면 된다.

예를들어 아래와 같은 예외가 발생했을 경우 가장 끝에서부터 앞으로 오면서 어디가 문제인지 찾아보면 된다.

2024-09-05 11:00:18.779 ERROR 16228 --- [nio-8080-exec-5] o.s.b.w.servlet.support.ErrorPageFilter : Cannot forward to error page for request [/stmt] as the response has already been committed. As a result, the response may have the wrong status code. If your application is running on WebSphere Application Server you may be able to resolve this problem by setting com.ibm.ws.webcontainer.invokeFlushAfterService to false

→ 테이블은 만들어 졌는데 404 예외 발생
return 경로를 잘못 설정해 최종 페이지를 찾지 못한 경우이다.

그리고, 로그 찍는걸 습관처럼 해야한다.
(에러가 나면 어디서 에러가 발생했는지 로그를 통해 볼 수 있다.)

---

✨ 회원가입 페이지 만들기 ✨

Controller

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import ko.co.gudi.service.JoinService;

@Controller
public class JoinController {

	Logger logger = LoggerFactory.getLogger(getClass());
	
	
	@RequestMapping(value="/")
	public String home() {
		return "join";
	}
	
	
	@RequestMapping(value="/join", method = RequestMethod.POST)
	public String join(HttpServletRequest req, Model model) {
		logger.info("회원가입 controller");
		
		JoinService service = new JoinService();
		
		try {
			String result = service.join(req); // controller 에 들어온 request 정보를 service 에 넘겨준다.
			model.addAttribute("result", result);
		} catch (Exception e) {
			e.printStackTrace();
			logger.info("회원가입이 실패했습니다. controller");
		}		
		
		return "resultSet";
	}
}

service

import java.sql.SQLException;
import javax.servlet.http.HttpServletRequest;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import ko.co.gudi.dao.JoinDAO;

public class JoinService {
	
	Logger logger = LoggerFactory.getLogger(getClass());
	public String join(HttpServletRequest req) throws SQLException {
		logger.info("회원가입 service");	
		
		JoinDAO dao = new JoinDAO();
		int row = dao.join(req); 
		String msg = "회원가입에 실패했습니다.";
		
		if(row >0) {
			msg = "회원가입에 성공했습니다.";
		}
		return msg;
	}
}

DAO

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.http.HttpServletRequest;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class JoinDAO {
	
	Logger logger = LoggerFactory.getLogger(getClass());
	private Connection conn = null;
	
	public JoinDAO() { // DB 연결 생성자
		String id = "web_user";
		String pw = "pass";
		String url = "jdbc:mariadb://localhost:3306/mydb";
		String dirver = "org.mariadb.jdbc.Driver";
		
		try {
			Class.forName(dirver); // DB랑 java랑 연결
			conn = DriverManager.getConnection(url, id, pw); 
			logger.info("Connection : {}", conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public int join(HttpServletRequest req) throws SQLException {
		logger.info("insert DAO 요청");
		int row = 0;
		
		String id = req.getParameter("id");
		String pw = req.getParameter("pw");
		String name = req.getParameter("name");
		String age = req.getParameter("age");
		String gender = req.getParameter("gender");
		String email = req.getParameter("email");		
		
		int su = Integer.parseInt(age);

		String sql = "INSERT INTO member(ID,PW,NAME,AGE,GENDER,EMAIL)VALUES(?,?,?,?,?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		
		ps.setString(1, id);
		ps.setString(2, pw);
		ps.setString(3, name);
		ps.setInt(4, su);
		ps.setString(5, gender);
		ps.setString(6, email);
		
		row = ps.executeUpdate();
		
		ps.close();
		conn.close();
		
		return row;
	}
}