[Java] DB 연동

DBUtil.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBUtil {
	// 접속
	public static Connection getConnection() throws Exception {
		Connection conn = null;

		Class.forName("com.mysql.cj.jdbc.Driver");

		String url = "jdbc:mysql://127.0.0.1:3306/skudb";
		String user = "sku";
		String password = "skupw";

		conn = DriverManager.getConnection(url, user, password);

		return conn;
	}

	// 종료
	public static void close(Connection conn, PreparedStatement ps) {
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		close(conn, ps);
	}
}

 

MemberDTO.java

public class MemberDTO {
	private String id;
	private String name;
	private String password;
	private String email;
	private String joinDate;

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPassword() {
		return password;
	}

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

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getJoinDate() {
		return joinDate;
	}

	public void setJoinDate(String joinDate) {
		this.joinDate = joinDate;
	}

	@Override
	public String toString() {
		return "MemberDTO [id=" + id + ", name=" + name + ", password=" + password + ", email=" + email + ", joinDate="
				+ joinDate + "]";
	}
}

 

MemberDAO.java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class MemberDAO {
	// 입력
	public boolean addMember(MemberDTO member) {
		boolean flag = false;

		// 1. 선언
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			// 3. 접속
			conn = DBUtil.getConnection();

			// 4. 쿼리 작성 -- PreparedStatement
			String sql = "insert into member values(?, ?, ?, ?, default)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, member.getId());
			ps.setString(2, member.getName());
			ps.setString(3, member.getPassword());
			ps.setString(4, member.getEmail());

			// 5. 쿼리 실행
			int count = ps.executeUpdate();

			if (count == 1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 2. 닫아라 !!
			DBUtil.close(conn, ps);
		}

		return flag;
	}

	// 수정
	public boolean updateMember(MemberDTO member) {
		boolean flag = false;

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBUtil.getConnection();

			String sql = "update member set name=?, password=?, email=? where id=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, member.getName());
			ps.setString(2, member.getPassword());
			ps.setString(3, member.getEmail());
			ps.setString(4, member.getId());

			if (ps.executeUpdate() == 1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps);
		}

		return flag;
	}

	// 삭제
	public boolean deleteMember(String id) {
		boolean flag = false;

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBUtil.getConnection();

			String sql = "delete from member where id=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, id);

			if (ps.executeUpdate() == 1)
				flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps);
		}

		return flag;
	}

	// 조회(id로 조회)
	public MemberDTO getMember(String id) {
		MemberDTO member = new MemberDTO();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.getConnection();

			String sql = "select name, password, email, join_date from member where id=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, id);

			rs = ps.executeQuery();
			rs.next();

			member.setId(id);
			member.setName(rs.getString(1));
			member.setPassword(rs.getString(2));
			member.setEmail(rs.getString(3));
			member.setJoinDate(rs.getString(4));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return member;
	}

	// 전체 조회
	public List<MemberDTO> getMembers() {
		List<MemberDTO> memberList = new ArrayList<>();

		// 1. 선언 !! -- 접속(Connection), 쿼리 작성(PreparedStatement), 결과(ResultSet)
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			// 3. 접속
			conn = DBUtil.getConnection();

			// 4. 쿼리 작성
			String sql = "select id, name, password, email, join_date from member";
			ps = conn.prepareStatement(sql);

			// 5. 쿼리 실행
			rs = ps.executeQuery();

			// 6. 결과값 얻어오기 !!
			while (rs.next()) {
				MemberDTO member = new MemberDTO();
				member.setId(rs.getString(1));
				member.setName(rs.getString(2));
				member.setPassword(rs.getString(3));
				member.setEmail(rs.getString(4));
				member.setJoinDate(rs.getString(5));
				memberList.add(member);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 2. 선언한 자원들을 닫아준다 !!!
			DBUtil.close(conn, ps, rs);
		}

		return memberList;
	}
}

 

메소드명(매개변수)	리턴 타입	기능
execute()	boolean	모든 sql문 실행. 반환값이 'true'이면, getResultSet 메소드를 사용해 결과를 얻을 수 있다. 반대로 반환값이 'false'이면 업데이트 개수 또는 결과가 없는 경우이다.
executeQuery()	ResultSet	SELECT 구문 실행.
executeUpdate()	int	SELECT 이외의 구문 실행. INSERT, UPDATE, DELETE 구문을 실행하고 영향을 받은 행의 개수를 반환한다. create, drop 관련 구문에서는 -1을 반환한다.
close()	void	PreparedStatement의 자원을 반납.