0. 복습
+오류메모+
Exception in thread "main" java.sql.SQLRecoverableException<수정 전>
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDateTime;
public class MemberSelect {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "c##human";
String pw = "human";
Connection conn = DriverManager.getConnection(url,user,pw);
String sql = "select * from web_member";
System.out.println("sql 에러시 여기서 문자열 확인" + sql);
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
String id = rs.getString("customerId");
String Pw = rs.getString("customerPw");
String name = rs.getString("name");
int age = rs.getInt("age");
String gender = rs.getString("gender");
String tel = rs.getString("tel");
LocalDateTime birthday = rs.getTimestamp("birth").toLocalDateTime();
String job = rs.getString("job");
String email = rs.getString("email");
String address = rs.getString("address");
System.out.println("id: " + id);
System.out.println("pw: " + Pw);
System.out.println("name: " + name);
System.out.println("age: " + age);
System.out.println("gender: " + gender);
System.out.println("tel: " + tel);
System.out.println("birth: " + birthday);
System.out.println("job: " + job);
System.out.println("email: " + email);
System.out.println("address: " + address);
System.out.println("======================================================");
if(rs != null) rs.close();
if(ps != null) ps.close();
if(conn != null) conn.close();
}
}
}
- while문을 닫지 않고 실행하여 오류 발생
<수정 후>
- while문 괄호 닫고 아래 중괄호 하나 지움.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDateTime;
public class MemberSelect {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "c##human";
String pw = "human";
Connection conn = DriverManager.getConnection(url,user,pw);
String sql = "select * from web_member";
System.out.println("sql 에러시 여기서 문자열 확인" + sql);
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
String id = rs.getString("customerId");
String Pw = rs.getString("customerPw");
String name = rs.getString("name");
int age = rs.getInt("age");
String gender = rs.getString("gender");
String tel = rs.getString("tel");
LocalDateTime birthday = rs.getTimestamp("birth").toLocalDateTime();
String job = rs.getString("job");
String email = rs.getString("email");
String address = rs.getString("address");
System.out.println("id: " + id);
System.out.println("pw: " + Pw);
System.out.println("name: " + name);
System.out.println("age: " + age);
System.out.println("gender: " + gender);
System.out.println("tel: " + tel);
System.out.println("birth: " + birthday);
System.out.println("job: " + job);
System.out.println("email: " + email);
System.out.println("address: " + address);
System.out.println("======================================================");
}
if(rs != null) rs.close();
if(ps != null) ps.close();
if(conn != null) conn.close();
}
}1. model1을 이용한 dto, dao
(1) 용어
- model: 사용자 입력 처리하여 사용자가 원하는 비즈니스 로직을 사용하여 원하는 데이터를 만들 수 있음.
- dto(database transfer object)
- 데이터베이스의 테이블에 들어있는 하나의 row 데이터를 java에서 사용할 수 있도록 만드는 클래스
- dao(database access object)
- 자바에서 데이터베이스를 조작하는데 필요한 작업을 모아놓은 클래스
- model1은 데이터를 처리할 때 DAO를 통해 DB의 데이터를 DTO에 담아서 바로 처리.
- 단점
1) 사용자 입력이나 출력을 보여주는 view 화면 프로그램
2) 사용자의 요청에 따라 처리하여 데이터를 만드는 model코드 섞임.
1. 관리자 메뉴
import java.util.ArrayList;
import com.human.dao.HumanDao;
import com.human.dto.HumanDto;
import com.human.util.UserInput;
import com.human.util.DBConn;
import java.time.format.DateTimeFormatter;
import java.sql.*;
public class DBConnEx {
public static void main(String[] args) {
HumanDao dao = new HumanDao();
int input = 0;
String strMainMenu = "------HumanTable 관리 프로그램------\n";
strMainMenu += "| 1. 출력 | 2. 입력 | 3. 삭제 | 4. 수정 | 5. 종료 | \n";
strMainMenu += "메뉴 번호 ";
while(input != 5) {
input = UserInput.inputInt(strMainMenu);
switch(input) {
case 1:
System.out.println("--------------------------------");
ArrayList<HumanDto> resultDtos = new ArrayList<HumanDto>();
ResultSet rs = DBConn.statementQuery("select * from human");
try {
while (rs.next()) {
resultDtos.add(new HumanDto(
rs.getString("name"),
rs.getInt("age"),
rs.getDouble("height"),
rs.getTimestamp("birthday").toLocalDateTime()
));
}
} catch (SQLException e) {
e.printStackTrace();
}
for(HumanDto dto : resultDtos) {
System.out.println(dto);
}
break;
case 2:
System.out.println("--------------------------------");
System.out.println("입력할 Human 데이터 입력>");
HumanDto dto = new HumanDto();
dto.setName(UserInput.inputString("이름 입력> "));
dto.setAge(UserInput.inputInt("나이 입력> "));
dto.setHeight(UserInput.inputDouble("키 입력> "));
dto.setBirthday(UserInput.inputLocalDateTime("생일 입력> "));
dao.insert(dto);
break;
case 3:
System.out.println("--------------------------------");
System.out.println("삭제할 Human 데이터 입력>");
String name = UserInput.inputString("이름 입력> ");
dao.delete(name);
break;
case 4:
System.out.println("--------------------------------");
String updateName = UserInput.inputString("수정할 이름 입력> ");
int updateAge = UserInput.inputInt("수정할 나이 입력> ");
dao.update(updateAge, updateName);
break;
case 5:
System.out.println("--------------------------------");
System.out.println("프로그램을 종료합니다. 이용해주셔서 감사합니다");
input = 5;
break;
default:
System.out.println("보기에 있는 숫자를 입력하세요");
break;
}
}
}
}
2. dto
import java.time.LocalDateTime;
import java.util.Objects;
public class HumanDto {
private String name;
private Integer age;
private Double height;
private LocalDateTime birthday;
@Override
public String toString() {
return "HumanDto [name=" + name + ", age=" + age + ", height=" + height + ", birthday=" + birthday + "]";
}
@Override
public int hashCode() {
return Objects.hash(name);
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
HumanDto other = (HumanDto) obj;
return Objects.equals(name, other.name);
}
public HumanDto() {}
public HumanDto(String name, Integer age, Double height, LocalDateTime birthday) {
super();
this.name = name;
this.age = age;
this.height = height;
this.birthday = birthday;
}
public HumanDto(HumanDto dto) {
super();
this.name = dto.name;
this.age = dto.age;
this.height = dto.height;
this.birthday = dto.birthday;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Double getHeight() {
return height;
}
public void setHeight(Double height) {
this.height = height;
}
public LocalDateTime getBirthday() {
return birthday;
}
public void setBirthday(LocalDateTime birthday) {
this.birthday = birthday;
}
}
3. DBConn
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBConn {
// 생성자를 private로 설정하여 외부에서 객체 생성하지 못하도록 함.
private DBConn() {}
// DB연결을 위한 Connection 객체를 static으로 선언하여 클래스 변수로 사용
private static Connection dbConn = null;
// 쿼리 실행을 위한 Statement 객체를 static으로 선언하여 클래스 변수로 사용
private static Statement st = null;
// 쿼리 실행 결과를 저장하기 위한 Resultset 객체를 static으로 선언하여 클래스 변수로 사용
private static ResultSet rs = null;
// DB연결을 위한 메소드. 싱글폰 패턴을 사용하여 이미 연결되어 있는 경우 연결 객체 반환. 연결x: 새로운 연결객체 사용
public static Connection getInstance() {
if(dbConn == null) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String id = "c##human";
String pw = "human";
dbConn = DriverManager.getConnection(url,id,pw);
System.out.println("DBConnection...");
} catch (Exception e) {
e.printStackTrace();
}
}
return dbConn;
}
// DB연결을 종료하는 메소드
public static void dbClose() {
try {
if(rs != null) rs.close();
if(st != null) st.close();
if(dbConn != null) dbConn.close();
} catch(Exception e) {
e.printStackTrace();
} finally {
rs = null;
st = null;
dbConn = null;
}
}
// insert, update, delete 등 쿼리 실행결과를 반환하지 않는 쿼리문 실행
// 쿼리문을 매개변수로 받아 Statement 객체를 생성하여 쿼리문 실행
public static int statementUpdate(String sql) {
DBConn.getInstance();
int rValue = -1;
if(dbConn != null) {
try {
if(st == null) st = dbConn.createStatement();
rValue = st.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println("not connected....");
}
return rValue;
}
// select 등 쿼리 실행결과를 반환하는 쿼리문을 실행하는 메소드
// 자바에서 오라클 db 사용가능
public static ResultSet statementQuery(String sql) {
DBConn.getInstance();
if(DBConn.dbConn != null) {
try {
if(st == null) st = dbConn.createStatement();
rs = st.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println("not connected...");
}
return rs;
}
}
4. UserInput
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Scanner;
public class UserInput {
private static Scanner sc = new Scanner(System.in);
public static int inputInt(String st) {
System.out.println(st + " 정수입력>>");
return Integer.parseInt(sc.nextLine());
}
public static double inputDouble(String st) {
System.out.println(st + " 실수입력>>");
return Double.parseDouble(sc.nextLine());
}
public static String inputString(String st) {
System.out.println(st + " 문자입력>>");
return sc.nextLine();
}
public static LocalDateTime inputLocalDateTime(String str) {
System.out.println(str + " 시간입력(yyyy-MM-dd HH:mm:ss)>>");
return LocalDateTime.parse(sc.nextLine(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
public static String dataToString(LocalDateTime date) {
return date.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
}
5. DAO
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import com.human.dto.HumanDto;
import com.human.util.DBConn;
public class HumanDao {
public ArrayList<HumanDto> select() {
ArrayList<HumanDto> resultDtos = new ArrayList<HumanDto>();
ResultSet rs = DBConn.statementQuery(String.format("select * from human"));
try {
while(rs.next()) {
resultDtos.add(new HumanDto(rs.getString("name"), rs.getInt("age"), rs.getDouble("height"), rs.getTimestamp("birthday").toLocalDateTime()));
}
} catch(SQLException e) {
e.printStackTrace();
}
return resultDtos;
}
public void insert(HumanDto dto) {
String sql = String.format("insert into human values('" + "%s', %d, %f, to_date('%s', 'YYYY:MM:DD HH24:MI:SS'))", dto.getName(), dto.getAge(), dto.getHeight(), dto.getBirthday().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:SS")));
DBConn.statementUpdate(sql);
System.out.println("[입력 완료]");
}
public void delete(String name) {
String sql = String.format("delete human where name = '%s'", name);
DBConn.statementUpdate(sql);
System.out.println("[삭제 완료]");
}
public void update(int updateAge, String updateName) {
String sql = String.format("update human set age = %d where name = '%s'", updateAge, updateName);
DBConn.statementUpdate(sql);
System.out.println("[수정 완료]");
}
}(2) 응용: 프로그램 구현연습
- 현재 입력 insert 프로그램이 구현되지 않음.
- sql예외적인 문법오류로 인해 구현실패함.import java.time.LocalDateTime;
import java.util.Objects;
public class MemberDTO {
private String id = "";
private String pw = "";
private String name = "";
private int age;
private String gender = "";
private String tel = "";
private LocalDateTime birth;
// 추가: getter/setter, toString, hashCode/equals, 생성자
@Override
public String toString() {
return "MemberDTO [id=" + id + ", pw=" + pw + ", name=" + name + ", age=" + age + ", gender=" + gender
+ ", tel=" + tel + ", birth=" + birth + "]";
}
@Override
public int hashCode() {
return Objects.hash(id);
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
MemberDTO other = (MemberDTO) obj;
return Objects.equals(id, other.id);
}
public MemberDTO() {}
public MemberDTO(String id, String pw, String name, int age, String gender, String tel, LocalDateTime birth) {
super();
this.id = id;
this.pw = pw;
this.name = name;
this.age = age;
this.gender = gender;
this.tel = tel;
this.birth = birth;
}
public MemberDTO(MemberDTO dto) {
super();
this.id = dto.id;
this.pw = dto.pw;
this.name = dto.name;
this.age = dto.age;
this.gender = dto.gender;
this.tel = dto.tel;
this.birth = dto.birth;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPw() {
return pw;
}
public void setPw(String pw) {
this.pw = pw;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public LocalDateTime getBirth() {
return birth;
}
public void setBirth(LocalDateTime birth) {
this.birth = birth;
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBConn {
private DBConn() {}
private static Connection dbConn = null;
private static Statement st = null;
private static ResultSet rs = null;
private static Connection getInstance() {
if(dbConn == null) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String id = "c##human";
String pw = "human";
dbConn = DriverManager.getConnection(url,id,pw);
System.out.println("DBConnection....");
} catch(Exception e) {
e.printStackTrace();
}
}
return dbConn;
}
public static void dbClose() {
try {
if(rs != null) rs.close();
if(st != null) st.close();
if(dbConn != null) dbConn.close();
} catch(Exception e) {
e.printStackTrace();
} finally {
rs = null;
st = null;
dbConn = null;
}
}
public static int statementUpdate(String sql) {
Connection conn = getInstance();
int rValue = -1;
if(conn != null) {
try {
if(st == null) st = dbConn.createStatement();
rValue = st.executeUpdate(sql);
} catch(SQLException e) {
e.printStackTrace();
}
} else {
System.out.println("not connected....");
}
return rValue;
}
public static ResultSet statementQuery(String sql) {
Connection conn = getInstance();
if(conn != null) {
try {
if(st == null) st = dbConn.createStatement();
rs = st.executeQuery(sql);
} catch(SQLException e) {
e.printStackTrace();
}
} else {
System.out.println("not connected...");
}
return rs;
}
}
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Scanner;
public class UserInput {
private static Scanner sc = new Scanner(System.in);
public static int inputInt(String st) {
System.out.println(st + " 정수입력>>");
return Integer.parseInt(sc.nextLine());
}
public static String inputString(String st) {
System.out.println(st + " 문자입력>>");
return sc.nextLine();
}
public static LocalDateTime inputLocalDateTime(String str) {
System.out.println(str + " 시간입력(yyyy-MM-dd HH:mm:ss)>>");
return LocalDateTime.parse(sc.nextLine(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
public static String dataToString(LocalDateTime date) {
return date.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
}
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import com.human.dto.MemberDTO;
import com.human.util.DBConn;
public class MemberDao {
public ArrayList<MemberDTO> select() {
ArrayList<MemberDTO> resultDtos = new ArrayList<>();
ResultSet rs = DBConn.statementQuery(String.format("select * from web_member"));
try {
while (rs.next()) {
resultDtos.add(new MemberDTO(rs.getString("customerId"), rs.getString("customerPw"),
rs.getString("name"), rs.getInt("age"), rs.getString("gender"), rs.getString("tel"),
rs.getTimestamp("birth").toLocalDateTime()));
}
} catch (SQLException e) {
e.printStackTrace();
}
return resultDtos;
}
public void insert(MemberDTO dto) {
// 오류 해결: String.format 작성x
String sql = String.format("insert into web_member values('" + "%s','%s','%s',%d,'%s','%s,to_date('%s', 'YYYY:MM:DD HH24:MI:SS'))",
dto.getId(), dto.getPw(), dto.getName(), dto.getAge(), dto.getGender(), dto.getTel(),dto.getBirth().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:SS")));
DBConn.statementUpdate(sql);
System.out.println("[입력 완료]");
}
public void delete(String name) {
String sql = String.format("delete web_member where name = '%s'", name);
DBConn.statementUpdate(sql);
System.out.println("[삭제 완료]");
}
public void update(int updateAge, String updateName) {
String sql = String.format("update web_member set age = %d where name = '%s'", updateAge, updateName);
DBConn.statementUpdate(sql);
System.out.println("[수정 완료]");
}
}
import java.util.ArrayList;
import com.human.dao.MemberDao;
import com.human.dto.MemberDTO;
import com.human.util.UserInput;
public class DBConnEx {
public static void main(String[] args) {
MemberDao dao = new MemberDao();
int input = 0;
String strMainMenu = "--------------------Member 관리 프로그램 ---------------------\n";
strMainMenu += "| 1. 출력 | 2. 입력 | 3. 삭제 | 4. 수정 | 5. 종료 |\n";
strMainMenu += "메뉴 번호 ";
while(input != 5) {
input = UserInput.inputInt(strMainMenu);
switch(input) {
case 1:
ArrayList<MemberDTO> resultDtos = dao.select();
for(MemberDTO dto:resultDtos) {
System.out.println(dto);
}
break;
case 2:
System.out.println("--------------------------------------------------------");
System.out.println("입력할 Member 데이터 입력>>");
MemberDTO dto = new MemberDTO();
dto.setId(UserInput.inputString("아이디 입력> "));
dto.setPw(UserInput.inputString("비밀번호 입력> "));
dto.setName(UserInput.inputString("이름 입력> "));
dto.setAge(UserInput.inputInt("나이 입력> "));
dto.setGender(UserInput.inputString("성별 입력> "));
dto.setTel(UserInput.inputString("전화번호 입력> "));
dto.setBirth(UserInput.inputLocalDateTime("생일 입력> "));
dao.insert(dto);
break;
case 3:
System.out.println("--------------------------------------------------------");
System.out.println("삭제할 Member 데이터 입력>>");
String name = UserInput.inputString("이름 입력> ");
dao.delete(name);
break;
case 4:
System.out.println("--------------------------------------------------------");
String updateName = UserInput.inputString("수정할 이름 입력> ");
int updateAge = UserInput.inputInt("수정할 나이 입력> ");
dao.update(updateAge, updateName);
break;
case 5:
System.out.println("--------------------------------------------------------");
System.out.println("프로그램을 종료합니다.");
input = 5;
break;
default:
System.out.println("보기에 있는 숫자를 입력하세요");
break;
}
}
}
}
IT 개발자가 되기 위한 기록