myBatis๋ฅผ ์ฌ์ฉํ๋ฉด ์๋์ ์ค๋ช ๋ ๋ฒ๊ฑฐ๋ก์ด ์์ ์ ๊ฐํธํํ ์ ์๋ค...
MemberDTO ํด๋์ค
๋ฐ์ดํฐ ์ ์ก ๊ฐ์ฒด
ํ ํ์์ ์ ๋ณด๋ฅผ ๋ด๊ณ ์์
getter, setter์ ํตํด ๋ค๋ฅธ ํด๋์ค์์ ์ ๊ทผ ๊ฐ๋ฅ
package db.member2;
public class MemberDTO {
private String id;
private String pwd;
private String name;
private String birth;
private String email;
private String tel;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getBirth() {
return birth;
}
public void setBirth(String birth) {
this.birth = birth;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
}
MemberDAO ์ธํฐํ์ด์ค
๋ฐ์ดํฐ๋ฒ ์ด์ค์ CRUD๋ฅผ ์ํ ์ธํฐํ์ด์ค
์ธํฐํ์ด์ค : ๋ชจ๋ ๊ธฐ๋ฅ์ ์ถ์ํ๋ก ์ ์ํ๊ณ ๊ตฌํ์ ํ์ง ์์ ๊ฒ
package db.member2;
import java.sql.SQLException;
import java.util.List;
public interface MemberDAO {
public int insertMember(MemberDTO dto) throws SQLException;
public int updateMember(MemberDTO dto) throws SQLException;
public int deleteMember(String id) throws SQLException;
public MemberDTO readMember(String id);
public List<MemberDTO> listMember();
public List<MemberDTO> listMember(String name);
}
MemberDAOImpl ํด๋์ค
MemberDAO ์ธํฐํ์ด์ค ๊ตฌํ ํด๋์ค (MemberDAO๋ฅผ ์์)
member1, member2 ํ ์ด๋ธ์ DML, SELECT ๋ฑ ๋ฐ์ดํฐ ์ฒ๋ฆฌ๋ฅผ ์ฃผ๋ชฉ์ ์ผ๋ก ํ๋ ํด๋์ค
์ฃผ์ ๊ธฐ๋ฅ์ด ๊ตฌํ๋๋ ํด๋์ค
package db.ch1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.ArrayList;
import java.util.List;
import db.util.DBConn;
public class MemberDAOImpl implements MemberDAO {
private Connection conn = DBConn.getConnection(); //DB ์ฐ๊ฒฐ
@Override
public int insertMember(MemberDTO dto) throws SQLException {
// ํ์ ์ฝ์
int result = 0;
PreparedStatement pstmt = null; //PreparedStatement ๊ฐ์ฒด ์ ์ธ
String sql;
try {
sql = "INSERT INTO member1(id, pwd, name) VALUES ( ?, ?, ? )";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dto.getId()); //1๋ฒ์งธ ?์ dto๊ฐ์ฒด์์ Id๋ฅผ ๊ฐ์ ธ์ค๊ฒ ๋ค๋ ์๋ฏธ
pstmt.setString(2, dto.getPwd());
pstmt.setString(3, dto.getName());
result = pstmt.executeUpdate(); //sql๋ฌธ ์คํ
pstmt.close();
sql = "INSERT INTO member2(id, birth, email, tel) VALUES ( ?, ?, ?, ? )";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dto.getId());
pstmt.setString(2, dto.getBirth());
pstmt.setString(3, dto.getEmail());
pstmt.setString(4, dto.getTel());
result += pstmt.executeUpdate();
} catch (SQLIntegrityConstraintViolationException e) {
// ๊ธฐ๋ณธํค ์ ์ฝ ์๋ฐ, NOT NULL ๋ฑ์ ์ ์ฝ ์๋ฐ - ๋ฌด๊ฒฐ์ฑ ์ ์ฝ ์๋ฐ์ ๋ฐ์
if (e.getErrorCode() == 1) { // ๊ธฐ๋ณธํค ์ค๋ณต
System.out.println("์์ด๋ ์ค๋ณต์ผ๋ก ๋ฑ๋ก์ด ๋ถ๊ฐ๋ฅํฉ๋๋ค.");
} else if (e.getErrorCode() == 1400) { // NOT NULL
System.out.println("ํ์ ์
๋ ฅ ์ฌํญ์ ์
๋ ฅ ํ์ง ์์์ต๋๋ค.");
} else {
System.out.println(e.toString());
}
throw e;
} catch (SQLDataException e) {
// ๋ ์ง๋ฑ์ ํ์ ์๋ชป์ผ๋ก ์ธํ ์์ธ
if (e.getErrorCode() == 1840 || e.getErrorCode() == 1861) {
System.out.println("๋ ์ง ์
๋ ฅ ํ์ ์ค๋ฅ ์
๋๋ค.");
} else {
System.out.println(e.toString());
}
throw e;
} catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e2) {
}
}
}
return result;
}
@Override
public int updateMember(MemberDTO dto) throws SQLException {
int result = 0;
PreparedStatement pstmt = null;
String sql;
try {
sql = "UPDATE member1 SET pwd = ?, name = ? WHERE id = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dto.getPwd());
pstmt.setString(2, dto.getName());
pstmt.setString(3, dto.getId());
result = pstmt.executeUpdate();
pstmt.close();
sql = "UPDATE member2 SET birth = ?, email = ?, tel = ? WHERE id = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dto.getBirth());
pstmt.setString(2, dto.getEmail());
pstmt.setString(3, dto.getTel());
pstmt.setString(4, dto.getId());
result += pstmt.executeUpdate();
} catch (SQLIntegrityConstraintViolationException e) {
if (e.getErrorCode() == 1400) { // NOT NULL
System.out.println("ํ์ ์
๋ ฅ ์ฌํญ์ ์
๋ ฅ ํ์ง ์์์ต๋๋ค.");
} else {
System.out.println(e.toString());
}
throw e;
} catch (SQLDataException e) {
// ๋ ์ง๋ฑ์ ํ์ ์๋ชป์ผ๋ก ์ธํ ์์ธ
if (e.getErrorCode() == 1840 || e.getErrorCode() == 1861) {
System.out.println("๋ ์ง ์
๋ ฅ ํ์ ์ค๋ฅ ์
๋๋ค.");
} else {
System.out.println(e.toString());
}
throw e;
} catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e2) {
}
}
}
return result;
}
@Override
public int deleteMember(String id) throws SQLException {
int result = 0;
PreparedStatement pstmt = null;
String sql;
try {
sql = "DELETE FROM member2 WHERE id = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
result = pstmt.executeUpdate();
pstmt.close();
sql = "DELETE FROM member1 WHERE id = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
result += pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e2) {
}
}
}
return result;
}
@Override
public MemberDTO readMember(String id) {
MemberDTO dto = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql;
try {
sql = "SELECT m1.id, pwd, name, TO_CHAR(birth, 'YYYY-MM-DD') birth, email, tel " + " FROM member1 m1 "
+ " LEFT OUTER JOIN member2 m2 ON m1.id = m2.id " + " WHERE m1.id = ? ";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if (rs.next()) {
dto = new MemberDTO();
dto.setId(rs.getString("id"));
dto.setPwd(rs.getString("pwd"));
dto.setName(rs.getString("name"));
dto.setBirth(rs.getString("birth"));
dto.setEmail(rs.getString("email"));
dto.setTel(rs.getString("tel"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e2) {
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e2) {
}
}
}
return dto;
}
@Override
public List<MemberDTO> listMember() {
List<MemberDTO> list = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql;
try {
sql = "SELECT m1.id, pwd, name, TO_CHAR(birth, 'YYYY-MM-DD') birth, email, tel " + " FROM member1 m1 "
+ " LEFT OUTER JOIN member2 m2 ON m1.id = m2.id ";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
MemberDTO dto = new MemberDTO();
dto.setId(rs.getString("id"));
dto.setPwd(rs.getString("pwd"));
dto.setName(rs.getString("name"));
dto.setBirth(rs.getString("birth"));
dto.setEmail(rs.getString("email"));
dto.setTel(rs.getString("tel"));
list.add(dto);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e2) {
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e2) {
}
}
}
return list;
}
@Override
public List<MemberDTO> listMember(String name) {
List<MemberDTO> list = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql;
try {
sql = "SELECT m1.id, pwd, name, TO_CHAR(birth, 'YYYY-MM-DD') birth, email, tel " + " FROM member1 m1 "
+ " LEFT OUTER JOIN member2 m2 ON m1.id = m2.id " + " WHERE INSER(name, ?) > 0";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
rs = pstmt.executeQuery();
while (rs.next()) {
MemberDTO dto = new MemberDTO();
dto.setId(rs.getString("id"));
dto.setPwd(rs.getString("pwd"));
dto.setName(rs.getString("name"));
dto.setBirth(rs.getString("birth"));
dto.setEmail(rs.getString("email"));
dto.setTel(rs.getString("tel"));
list.add(dto);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e2) {
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e2) {
}
}
}
return list;
}
}
INSERT ALL์ ์ด์ฉํ ๋ค์คํ ์ฝ์
sql = "INSERT ALL "
+ " INTO member1(id, pwd, name) VALUES ( ?, ?, ? )"
+ " INTO member2(id, birth, email, tel) VALUES ( ?, ?, ?, ? )"
+ " SELECT * FROM dual ";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dto.getId());
pstmt.setString(2, dto.getPwd());
pstmt.setString(3, dto.getName());
pstmt.setString(4, dto.getId());
pstmt.setString(5, dto.getBirth());
pstmt.setString(6, dto.getEmail());
pstmt.setString(7, dto.getTel());
result = pstmt.executeUpdate();
DAO(Data Access Object)
DB๋ฅผ ์ฌ์ฉํ์ฌ ๋ฐ์ดํฐ์ ์กฐํ ๋ฐ ์กฐ์ํ๋ ๊ธฐ๋ฅ์ ๋ด๋น
๋ฐ์ดํฐ์ ์ ๊ทผํ๊ธฐ ์ํด ์์ฑํ๋ ๊ฐ์ฒด
๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ ๊ทผํ๊ธฐ ์ํ ๋ก์ง๊ณผ ๋น์ฆ๋์ค ๋ก์ง์ ๋ถ๋ฆฌํ๊ธฐ ์ํด ์ฌ์ฉ๋๋ ๊ฐ๋
MemberUI ํด๋์ค
๋ฐ์ดํฐ ์ ์ถ๋ ฅ์ ์ํ ํด๋์ค
package db.member2;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.List;
import db.util.DBConn;
public class MemberUI {
private BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
private MemberDAO dao = new MemberDAOImpl();
public void menu() {
System.out.println("PreparedStatement - ํ์ ์์ ...");
int ch;
while (true) {
try {
System.out.print("1.๋ฑ๋ก 2.์์ 3.์ญ์ 4.์์ด๋๊ฒ์ 5.์ด๋ฆ๊ฒ์ 6.๋ฆฌ์คํธ 7.์ข
๋ฃ => ");
ch = Integer.parseInt(br.readLine());
if (ch == 7) {
DBConn.close();
return;
}
switch(ch) {
case 1: insert(); break;
case 2: update(); break;
case 3: delete(); break;
case 4: findById(); break;
case 5: findByName(); break;
case 6: listAll(); break;
}
} catch (Exception e) {
}
}
}
protected void insert() {
System.out.println("\nํ์ ๊ฐ์
!!!");
try {
MemberDTO dto = new MemberDTO();
System.out.print("์์ด๋ ? ");
dto.setId(br.readLine());
System.out.print("ํจ์ค์๋ ? ");
dto.setPwd(br.readLine());
System.out.print("์ด๋ฆ ? ");
dto.setName(br.readLine());
System.out.print("์๋
์์ผ ? ");
dto.setBirth(br.readLine());
System.out.print("์ด๋ฉ์ผ ? ");
dto.setEmail(br.readLine());
System.out.print("์ ํ๋ฒํธ ? ");
dto.setTel(br.readLine());
dao.insertMember(dto);
System.out.println("ํ์๊ฐ์
์ด ์ฑ๊ณต ํ์ต๋๋ค.");
} catch (Exception e) {
}
System.out.println();
}
protected void update() {
System.out.println("\nํ์ ์ ๋ณด ์์ !!!");
try {
MemberDTO dto = new MemberDTO();
System.out.print("์์ ํ ์์ด๋ ? ");
dto.setId(br.readLine());
System.out.print("ํจ์ค์๋ ? ");
dto.setPwd(br.readLine());
System.out.print("์ด๋ฆ ? ");
dto.setName(br.readLine());
System.out.print("์๋
์์ผ ? ");
dto.setBirth(br.readLine());
System.out.print("์ด๋ฉ์ผ ? ");
dto.setEmail(br.readLine());
System.out.print("์ ํ๋ฒํธ ? ");
dto.setTel(br.readLine());
int result = dao.updateMember(dto);
if (result == 0) {
System.out.println("๋ฑ๋ก๋ ์๋ฃ๊ฐ ์๋๋๋ค.");
} else {
System.out.println("ํ์์ ๋ณด๊ฐ ์์ ๋์์ต๋๋ค.");
}
} catch (Exception e) {
}
System.out.println();
}
protected void delete() {
System.out.println("\nํ์ ํํด !!!");
String id;
try {
System.out.print("์ญ์ ํ ์์ด๋ ? ");
id = br.readLine();
int result = dao.deleteMember(id);
if (result == 0) {
System.out.println("๋ฑ๋ก๋ ์๋ฃ๊ฐ ์๋๋๋ค.");
} else {
System.out.println("ํ์์ ๋ณด๊ฐ ์ญ์ ๋์์ต๋๋ค.");
}
} catch (Exception e) {
}
System.out.println();
}
protected void findById() {
System.out.println("\n์์ด๋ ๊ฒ์ !!!");
String id;
try {
System.out.print("๊ฒ์ํ ์์ด๋ ? ");
id = br.readLine();
MemberDTO dto = dao.readMember(id);
if (dto == null) {
System.out.println("๋ฑ๋ก๋ ์์ด๋๊ฐ ์๋๋๋ค.\n");
return;
}
System.out.print(dto.getId() + "\t");
// System.out.print(dto.getPwd()+"\t");
System.out.print(dto.getName() + "\t");
System.out.print(dto.getBirth() + "\t");
System.out.print(dto.getEmail() + "\t");
System.out.println(dto.getTel());
} catch (Exception e) {
}
System.out.println();
}
protected void findByName() {
System.out.println("\n์ด๋ฆ ๊ฒ์ !!!");
String name;
try {
System.out.print("๊ฒ์ํ ์ด๋ฆ ? ");
name = br.readLine();
List<MemberDTO> list = dao.listMember(name);
if (list.size() == 0) {
System.out.println("๋ฑ๋ก๋ ์ด๋ฆ์ด ์์ต๋๋ค.\n");
return;
}
for (MemberDTO dto : list) {
System.out.print(dto.getId() + "\t");
// System.out.print(dto.getPwd()+"\t");
System.out.print(dto.getName() + "\t");
System.out.print(dto.getBirth() + "\t");
System.out.print(dto.getEmail() + "\t");
System.out.println(dto.getTel());
}
} catch (Exception e) {
}
System.out.println();
}
protected void listAll() {
System.out.println("\n์ ์ฒด ๋ฆฌ์คํธ !!!");
List<MemberDTO> list = dao.listMember();
for (MemberDTO dto : list) {
System.out.print(dto.getId() + "\t");
// System.out.print(dto.getPwd()+"\t");
System.out.print(dto.getName() + "\t");
System.out.print(dto.getBirth() + "\t");
System.out.print(dto.getEmail() + "\t");
System.out.println(dto.getTel());
}
System.out.println();
}
}
App ํด๋์ค
main ๋ฉ์๋๋ฅผ ํฌํจํ๊ณ ์๋ ํด๋์ค
MemberUI์ menu() ๋ฉ์๋๋ฅผ ํธ์ถํด ํ๋ก๊ทธ๋จ์ ์คํ
public class App {
public static void main(String[] args) {
new MemberUI().menu();
}
}
์์ธ ์ฒ๋ฆฌ
SQLIntegrityConstraintViolationException
๋ฌด๊ฒฐ์ฑ ์ ์ฝ ์กฐ๊ฑด(์ธ๋ ํค, ๊ธฐ๋ณธ ํค ๋๋ ๊ณ ์ ํค)์ด ์๋ฐ
SQLDataException
ํ์ฉ๋์ง ์๋ ๋ณํ, 0์ผ๋ก ๋๋๊ธฐ ๋ฐ ํจ์์ ๋ํ ์ ํจํ์ง ์์ ์ธ์๋ฅผ ํฌํจํ๋ ์ด์ ๊ตญํ๋์ง ์๋ ๋ค์ํ ๋ฐ์ดํฐ ์ค๋ฅ
SQLException
๋ฐ์ดํฐ๋ฒ ์ด์ค ์ก์ธ์ค ์ค๋ฅ ๋๋ ๊ธฐํ ์ค๋ฅ์ ๋ํ ์ ๋ณด๋ฅผ ์ ๊ณตํ๋ ์์ธ
'๐ปProgramming > โ๏ธJava' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
CallableStatement ํ์ฉ(1) - ์ค๋ผํด ํ๋ก์์ (0) | 2022.08.23 |
---|---|
CallableStatement (0) | 2022.08.23 |
PreparedStatement (0) | 2022.08.22 |
Statement (0) | 2022.08.19 |
[JAVA] Collections Framework - Queue (0) | 2022.08.03 |