create sequence per_seq
increment by 1
start with 1
minvalue 1;
drop table person cascade constraints;
create table person(
num number primary key,
name varchar2(20),
age number,
gender varchar2(10),
birth date default sysdate
);
insert into person values(per_seq.nextval,'슈가',23,'남','1992/3/4');
insert into person values(per_seq.nextval,'아이유',33,'여',default);
insert into person values(per_seq.nextval,'제니',44,'여','1971/09/22');
insert into person values(per_seq.nextval,'싸이',20,'남',default);
DAO(Data Access Objects)
-DB를 사용해 데이터를 조회하거나 조작을 전담하는 오브젝트
-DB와의 연결을 담당한다.
-저장소(테이블)에 데이터를 입력,조회,수정,삭제 등의 처리를 한다.
DTO(Data Transfer Object)
-데이터의 전달을 위해 만드는 오브젝트
-데이터를 담을 private 변수와
그 변수를 조작할 수 있는 getter,setter 메서드로 구성한다.
-bean 또는 VO(Value Object)라고도 한다.
PersonDao 객체를 생성하면
1. 드라이버 로드 된다.
pdao.getAllPerson() 호출하면
getAllPerson() 정의로 가서
connect() 호출 2. 계정에 접속
while문에서 4줄의 레코드 칼럼별로
가져오기
Main
import java.util.ArrayList;
import java.util.Scanner;
public class PersonMain {
static Scanner sc = new Scanner(System.in);
// static을 붙이는 이유 : main과 메소드가 static으로 설정되어 있기 때문에
static PersonDao pdao = new PersonDao();
public static void main(String[] args) {
init();
}
static void init(){
while(true) {
System.out.println("===메뉴 선택하기====");
System.out.println("1.전체 정보 조회");
System.out.println("2.성별로 조회");
System.out.println("3.정보수정");
System.out.println("4.정보삭제");
System.out.println("5.정보추가");
System.out.println("6.프로그램 종료");
System.out.print(">> 메뉴 번호 입력 : ");
int menu =sc.nextInt();
switch(menu) {
case 1 :
ArrayList<PersonBean> lists =pdao.getAllPerson();
// System.out.println(lists.size());
for(int i=0;i<lists.size();i++) {
PersonBean pb =lists.get(i);
System.out.println(pb.getNum()+","+pb.getName()+
","+pb.getAge()+","+pb.getGender()+","+pb.getBirth());
}
System.out.println();
// for(PersonBean x: lists) {
// System.out.println(x);
// }
break;
case 2 :
getPersonByGender();
break;
case 3 :
updateData();
break;
case 4 :
deleteData();
break;
case 5 :
insertData();
break;
case 6 :
System.out.println("프로그램을 종료합니다.");
System.exit(0);
break;
default : System.out.println("1~6사이의 번호만 입력 가능");
}//switch
}//while
}//init
public static void getPersonByGender(){
String gender;
System.out.print("검색할 성별 입력:");
gender = sc.next();
ArrayList<PersonBean> lists=pdao.findGender(gender);
System.out.println("검색한 성별은 "+lists.size()+"줄 있음");
for(int i=0;i<lists.size();i++) {
PersonBean pb =lists.get(i);
System.out.println(pb.getNum()+","+pb.getName()+
","+pb.getAge()+","+pb.getGender()+","+pb.getBirth());
}
}
public static void insertData() {
System.out.println("번호는 시퀀스로 입력됩니다.");
System.out.print("이름입력:");
String name = sc.next();
System.out.print("나이입력:");
int age = sc.nextInt();
System.out.print("성별입력:");
String gender = sc.next();
System.out.print("생일입력:");
String birth = sc.next();
PersonBean pb= new PersonBean();
pb.setName(name);
pb.setAge(age);
pb.setGender(gender);
pb.setBirth(birth);
pdao.insertPerson(pb);
}
public static void updateData() {
System.out.print("번호입력:");
int num= sc.nextInt();
System.out.print("이름입력:");
String name = sc.next();
System.out.print("나이입력:");
int age = sc.nextInt();
System.out.print("성별입력:");
String gender = sc.next();
System.out.print("생일입력:");
String birth = sc.next();
PersonBean pb= new PersonBean();
pb.setNum(num);
pb.setName(name);
pb.setAge(age);
pb.setGender(gender);
pb.setBirth(birth);
pdao.updatePerson(pb);
}
public static void deleteData() {
System.out.print("삭제할 번호입력:");
int num= sc.nextInt();
pdao.deletePerson(num);
}
}Bean
public class PersonBean {
private int num;
private String name;
private int age;
private String gender;
private String birth;
public void setNum(int num) {
this.num=num;
}
public int getNum() {
return num;
}
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 getBirth() {
return birth;
}
public void setBirth(String birth) {
this.birth = birth;
}
public String toString() {
return this.num+","+this.name+","+this.age+","+this.gender+","+this.birth;
}
}Dao
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class PersonDao {
String driver= "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String id = "sqlid";
String pw = "sqlpw";
Connection conn= null;
PersonDao(){
System.out.println("PersonDao 생성자");
try {//1. 드라이버 로드
Class.forName(driver);
} catch (ClassNotFoundException e) {
System.out.println("error");
}
}
public void connect(){
try {//2. 계정 접속
conn = DriverManager.getConnection(url,id,pw);
} catch (SQLException e) {
System.out.println("error 2");
}
}
public ArrayList<PersonBean> getAllPerson() {
connect();
ArrayList<PersonBean> lists = new ArrayList<PersonBean>();
//3. SQL문 분석
String sql="select * from person order by num";
try {
PreparedStatement ps= conn.prepareStatement(sql);
//4. SQL문 실행
ResultSet rs = ps.executeQuery();
while(rs.next()) {
int num=rs.getInt("num");
String name=rs.getString("name");
int age = rs.getInt("age");
String gender = rs.getString("gender");
Date date = rs.getDate("birth");
String dates = String.valueOf(date);
// String date = rs.getString("birth");
// Date date = rs.getDate("birth");
// String dates = String.valueOf(rs.getDate("birth"));
// System.out.println(num+","+name+","+age+","+gender+","+dates);
PersonBean pb= new PersonBean();
pb.setNum(num);
pb.setName(name);
pb.setAge(age);
pb.setGender(gender);
pb.setBirth(dates);
lists.add(pb);
}
conn.close();
} catch (SQLException e) {
System.out.println("error 3");
}
return lists;
}//getAll
public ArrayList<PersonBean> findGender(String genders) {
connect();
ArrayList<PersonBean> lists = new ArrayList<PersonBean>();
try {
String sql="select * from person where gender=? order by num";
PreparedStatement ps= conn.prepareStatement(sql);
ps.setString(1, genders);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
int num=rs.getInt("num");
String name=rs.getString("name");
String gender = rs.getString("gender");
int age = rs.getInt("age");
Date date = rs.getDate("birth");
String dates = String.valueOf(date);
PersonBean pb= new PersonBean();
pb.setNum(num);
pb.setName(name);
pb.setAge(age);
pb.setGender(gender);
pb.setBirth(dates);
lists.add(pb);
}
conn.close();
} catch (SQLException e) {
System.out.println("error 3");
}
return lists;
}
public void insertPerson(PersonBean pb) {//5 정보추가
connect();
String sql ="insert into person values"
+ "(per_seq.nextval,?,?,?,?)";
try {
PreparedStatement ps= conn.prepareStatement(sql);
ps.setString(1, pb.getName());
ps.setInt(2, pb.getAge());
ps.setString(3, pb.getGender());
ps.setString(4, pb.getBirth());
int result =ps.executeUpdate();
System.out.println("result:"+result);
conn.close();
} catch (SQLException e) {
System.out.println("error 3");
}
}
public void updatePerson(PersonBean pb) {
connect();
String sql ="update person set "
+ "name=?,age=?,gender=?,birth=? where num=?";
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, pb.getName());
ps.setInt(2, pb.getAge());
ps.setString(3, pb.getGender());
ps.setString(4, pb.getBirth());
ps.setInt(5, pb.getNum());
int result=ps.executeUpdate();
System.out.println("result:"+result);
conn.close();
} catch (SQLException e) {
System.out.println("error 3");
}
}
public void deletePerson(int num) {
connect();
String sql="delete person where num="+num;
try {
PreparedStatement ps = conn.prepareStatement(sql);
int result=ps.executeUpdate();
System.out.println("result:"+result);
conn.close();
} catch (SQLException e) {
System.out.println("error 3");
}
}
}'Develop > JAVA' 카테고리의 다른 글
| 0623 JDBC 응용 2일차 (0) | 2022.06.24 |
|---|---|
| 0622 JDBC 응용 (0) | 2022.06.24 |
| 0620 JAVA JDBC를 통한 SQL 연동 (0) | 2022.06.20 |
| 0609 JAVA 열여섯번째... (0) | 2022.06.10 |
| 0607 JAVA 열네번째 공부 정리~ (0) | 2022.06.10 |