[JAVA] SQL_3

2017.05.31

SQL_3

- DB에 값 저장해 놓고 GUI로 구연한 프로그램에 값을 집어 넣어서 출력하기

1. SQL에 student_score 테이블 생성

create table student_score (

num number,

name varchar(10), 

stu_number varchar(20), 

stu_major varchar(50), 

java_score number, 

iot_score number, 

android_score number, 

db_score number, 

all_score number, 

avg_score number, 

day date 

)

select * from STUDENT_SCORE;

create sequence stu_num

start with 1

increment by 1

2. GUI 구현

4. Stu_Insert 클래스에 연결하고 구연

public class Stu_Insert {

public void insert(String name, String stu_number, String stu_major, String java_score, String iot_score, String android_score, String db_score) throws Exception{

System.out.println(name);

System.out.println(stu_number);

System.out.println(stu_major);

System.out.println(java_score);

System.out.println(iot_score);

System.out.println(android_score);

System.out.println(db_score);

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

String user = "system";

String pass = "1111";

int all_score = Integer.parseInt(java_score) + Integer.parseInt(iot_score) + Integer.parseInt(android_score) + Integer.parseInt(db_score);

int avg_score = all_score/4;

Connection con = DriverManager.getConnection(url, user, pass);

String sql;

sql = "insert into student_score values(stu_num.nextval, ?,?,?,?,?,?,?,?,?,sysdate)";

PreparedStatement pst = con.prepareStatement(sql);

pst.setString(1, name);

pst.setString(2, stu_number);

pst.setString(3, stu_major);

pst.setString(4, java_score);

pst.setString(5, iot_score);

pst.setString(6, android_score);

pst.setString(7, db_score);

pst.setInt(8, all_score);

pst.setInt(9, avg_score);

int cnt = pst.executeUpdate();

if (cnt > 0) {

System.out.println("성공");

} else {

System.out.println("실패");

}

}

5. GUI에 값 입력하고 테이블에 값 들어갔는지 확인

6. 예제

* 테이블 생성

- 테이블 명 : bookinfo

  num number,

code varchar(20), 

title varchar(50), 

price varchar(20), 

author varchar(20), 

publisher varchar(20), 

insert_date date 

- Book_insert 클래스 만들기

public void insert() throws Exception {

Scanner sc = new Scanner(System.in);

System.out.print("책 정보 입력");

System.out.print("코드 : ");

String code = sc.nextLine();

System.out.print("제목 : ");

String title = sc.nextLine();

System.out.print("가격 : ");

String price = sc.nextLine();

System.out.print("저자 : ");

String author = sc.nextLine();

System.out.print("출판사 : ");

String publisher = sc.nextLine();

System.out.println("책등록 성공!");

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

String user = "system";

String pass = "1111";

Connection con = DriverManager.getConnection(url, user, pass);

String sql;

sql = "insert into bookinfo values(book_num.nextval,?,?,?,?,?,sysdate)";

PreparedStatement pst = con.prepareStatement(sql);

pst.setString(1, code);

pst.setString(2, title);

pst.setString(3, price);

pst.setString(4, author);

pst.setString(5, publisher);

pst.executeUpdate();

int cnt = pst.executeUpdate();

if (cnt > 0) {

return "성공";

} else {

return "실패";

}

}