JDBC를 이용해 java oracle 기본 연결
JDBC 를 통해 Oracle DB SQLDeveloper의 자료를 가져와 java 콘솔창에 출력하기.
selet 문
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class OracleConnectTest_01 {
//나중에 프로젝트때 aws를 활용해서 공동의 url을 만들어야함
static final String URL="jdbc:oracle:thin:@localhost:1521:XE";
// Oracle에서 불러오는거면 URL 그냥 쓰기.
public void connectSawon() {
//싱글톤이라 한 메서드 안에서 사용해야함 //전역으로 못 뺌
//connection statement resultset 필요
Connection conn=null; //자바와 오라클 연결
Statement stmt=null; //crud 전제 sql 전달
ResultSet rs=null; //출력문을 읽어옴 리스트가 없을때 까지
String sql="Select * from sawon order by name"; //sawon 테이블 불러오기
try {
conn=DriverManager.getConnection(URL, "ssung2sin", "a1234"); //오라클 주소, 이름, 비밀번호
System.out.println("클라우드 오라클 서버 연결 성공!!");
//sql문 전달
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()) { //-> 데이터 없어서 false값 될때까지 반환//true값 반환
//DB로 부터 데이터 가져오기
int num=rs.getInt("num");//->오라클 테이블 컬럼명 or 열번호
String name=rs.getString("name");
String gender=rs.getString("gender");
String buseo=rs.getString("buseo");
int pay = rs.getInt("pay");
System.out.println(num+"\\t"+name+"\\t"+gender+"\\t"+buseo+"\\t"+pay);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("오라클 서버 연결 실패"+e.getMessage());
}
finally{
try { //null이면 비어있는 경우라서 파일을 열지도 않으니 null이 아니라면 닫아달라는말
if(rs!=null) rs.close();
if(stmt!=null) stmt.close();
if(conn!=null) conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void connectBamin() {
//주문번호 주문자 음식명 가격 상호명 가게위치 주문자주소
Connection cn=null;
Statement stmt=null;
ResultSet rs=null;
String sql="select f.fno,name,m_name,price,"
+ "shopname,loc,addr "
+ "from food f, jumin j "
+ "where f.fno=j.fno order by 2";
try {
cn=DriverManager.getConnection(URL, "scott", "tiger");
System.out.println("클라우드 오라클 서버 연결 성공!!");
stmt=cn.createStatement();
rs=stmt.executeQuery(sql);
System.out.println("***배민 주문 고객 리스트***");
System.out.println("주문번호\\t주문자명\\t음식명\\t가격\\t상호명\\t가게위치\\t주문자위치\\t");
while(rs.next()) {
int fno=rs.getInt("fno");
String name=rs.getString("name");
String m_name=rs.getString("m_name");
String price=rs.getString("price");
String sN=rs.getString("shopname");
String loc=rs.getString("loc");
String addr=rs.getString("addr");
System.out.println(fno+"\\t"+name+"\\t"+m_name+"\\t"+price+"\\t"+sN+"\\t"
+loc+"\\t"+addr);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("오라클 서버 연결 실패"+e.getMessage());
}
finally {
try {
rs.close();
stmt.close();
cn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void connectSawonGroup() {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String sql="select gender, count(*) cnt,to_char(avg(pay),'L999,999,999') avgpay from sawon group by gender";
try {
conn=DriverManager.getConnection(URL, "ssung2sin", "a1234");
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
System.out.println("**성별 인원 및 평균 급여**");
System.out.println("성별\\t인원수\\t평균급여");
System.out.println("---------------------------------------------");
while(rs.next()) {
String gd=rs.getString("gender");
int cnt=rs.getInt("cnt");
String pay=rs.getString("avgpay");
System.out.println(gd+"\\t"+cnt+"\\t"+pay);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
OracleConnectTest_01 oc1 = new OracleConnectTest_01();
//oc1.connectSawon();
//oc1.connectBamin();
oc1.connectSawonGroup();
}
}DBConnect 미리 설정하기
- CRUD 사용을 위해 DB를 만들어놓고 가져와서 사용하자.
- URL은 그대로 복붙으로 사용하는 것으로 DB에 추가해 놓자.
- Close의 경우 자주 사용하고, 하나하나 기입하기 귀찮으니까 모아놓자.
package dbtest;
import java.sql.*;
public class DBConnect {
// driver(OJDBC), url, 계정, 비밀번호를 반드시 connection 해줘야 한다.
// url
static final String ORACLE_URL="jdbc:oracle:thin:@localhost:1521:XE";
// driver : 딱 1번만 실행.. 생성자 단에서 실행
String driver="oracle.jdbc.driver.OracleDriver";
public DBConnect(){
try{
Class.forName(driver);
System.out.println("오라클 드라이버 성공!!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("오라클 드라이버 실패!! "+e.getMessage());
}
}
// Connection
public Connection getConnection(){
Connection conn=null;
try {
conn= DriverManager.getConnection(ORACLE_URL,"scott","tiger");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("오라클 연결실패: url, 계정, 비밀번호 확인 요함 "+e.getMessage());
}
return conn;
// void return이 없다. 해당 method에 void가 아니기 때문에 return이 없으면 아래 중괄호에 오류가 난다.
}
// close 메서드 생성.. 총 4개
public void dbClose(ResultSet rs, Statement stmt, Connection conn){
try {
if(rs!=null) rs.close();
if(stmt!=null) stmt.close();
if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void dbClose(Statement stmt, Connection conn){ // ResultSet이 없는 이유는 출력이 필요 없기 때문
try {
if(stmt!=null) stmt.close();
if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void dbClose(ResultSet rs, PreparedStatement pstmt, Connection conn){ //PreparedStatement : 미완의.. 뭐라고?
try {
if(rs!=null) rs.close();
if(pstmt!=null) pstmt.close();
if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void dbClose(PreparedStatement pstmt, Connection conn){
try {
if(pstmt!=null) pstmt.close();
if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}CRUD
package lesson;
import homework.DBConnect;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class SawonCRUD {
DBConnect db=new DBConnect();
Scanner sc=new Scanner(System.in);
// 1. insert
public void insertSawon(){
System.out.println("*** 사원 추가하기 ***");
System.out.println("사원명?");
String name=sc.nextLine();
System.out.println("성별은? (여자 or 남자)");
String gender=sc.nextLine();
System.out.println("부서는?");
String buseo=sc.nextLine();
System.out.println("급여는?");
int pay=Integer.parseInt(sc.nextLine());
String sql="insert into sawon values (seq_sawon.nextval,'"+name+"','"+gender+"','"+buseo+"',"+pay+")";
System.out.println(sql);
//오라클에 연결
Connection conn= db.getConnection();
Statement stmt=null;
try {
stmt=conn.createStatement();
stmt.execute(sql); // sql문 실행
System.out.println("DB에 추가 되었습니다.");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(stmt,conn);
}
}
// 2.select
public void selectSawon(){
String sql="select num,name,gender,buseo,to_char(pay,'L999,999,999') pay from sawon order by num";
System.out.println("\\t\\t [전체사원명단]");
System.out.println();
System.out.println("시퀀스\\t사원명\\t성별\\t부서명\\t\\t\\t월급여");
System.out.println("============================================");
// db연결
Connection conn=db.getConnection(); //연결
Statement stmt=null; // 전달
ResultSet rs=null; // 출력
try {
stmt= conn.createStatement();
rs=stmt.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getInt("num")+"\\t\\t"
+rs.getString("name")+"\\t"
+rs.getString("gender")+"\\t"
+rs.getString("buseo")
+rs.getString("pay"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(rs,stmt,conn);
}
}
// 3. delete
public void deleteSawon(){
System.out.println("삭제할 이름은?");
String name=sc.nextLine();
String sql="delete from sawon where name='"+name+"'";
Connection conn=db.getConnection();
Statement stmt=null;
try {
stmt=conn.createStatement();
stmt.execute(sql);
System.out.println("삭제가 완료되었습니다.");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(stmt,conn);
}
}
// 4.update .. 시퀀스 입력 후 부서, 급여만 수정
public boolean isDate(String num){
boolean flag=false;
String sql="select * from myinfo where num="+num;
Connection conn=db.getConnection();
Statement stmt=null;
ResultSet rs=null;
try {
stmt= conn.createStatement();
rs=stmt.executeQuery(sql);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(rs,stmt,conn);
}
return flag;
}
public void update(){
System.out.println("수정할 시퀀스는?");
String num=sc.nextLine();
if(!this.isDate(num)){
System.out.println("해당번호는 존재하지 않습니다.");
return;
}
System.out.println("수정할 부서는?");
String buseo=sc.nextLine();
System.out.println("수정할 급여는?");
int pay=Integer.parseInt(sc.nextLine());
String sql="update sawon set buseo='"+buseo+"',pay="+pay+" where num="+num;
System.out.println(sql);
Connection conn= db.getConnection();
Statement stmt=null;
try {
stmt= conn.createStatement();
stmt.execute(sql);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(stmt,conn);
}
}
//5. 사원검색
public void searchName(){
System.out.println("검색할 사원명을 검색해주세요 (일부 검색도 됨)");
String name=sc.nextLine();
String sql="select num,name,gender,buseo,to_char(pay,'L999,999,999') pay from sawon where name like '%"+name+"%'";
System.out.println("\\t\\t [검색사원명단]");
System.out.println();
System.out.println("시퀀스\\t사원명\\t성별\\t부서명\\t\\t\\t월급여");
System.out.println("============================================");
// db연결
Connection conn=db.getConnection(); //연결
Statement stmt=null; // 전달
ResultSet rs=null; // 출력
try {
stmt= conn.createStatement();
rs=stmt.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getInt("num")+"\\t\\t"
+rs.getString("name")+"\\t"
+rs.getString("gender")+"\\t"
+rs.getString("buseo")
+rs.getString("pay"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(rs,stmt,conn);
}
}
public static void main(String[] args) {
SawonCRUD sw=new SawonCRUD();
Scanner sc=new Scanner(System.in);
while (true){
System.out.println("Oracle DB Sawon 문제");
System.out.println("1.insert\\t2.select\\t3.delete\\t4.update\\t5.사원검색\\t9.종료");
int n=Integer.parseInt(sc.nextLine());
if (n==1){
sw.insertSawon();
} else if (n==9) {
System.out.println("종료합니다.");
break;
} else if (n==2) {
sw.selectSawon();
} else if (n==3) {
sw.deleteSawon();
} else if (n==4) {
sw.update();
} else if (n==5) {
sw.searchName();
}
}
}
}PreparedStatement
package dbtest;
import java.sql.*;
import java.util.Scanner;
public class PrepareMyShop {
DBConnect db=new DBConnect();
Scanner sc=new Scanner(System.in);
// insert
public void insertMyshop(){
// Connection
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
System.out.println("상품명은?");
String sang=sc.nextLine();
System.out.println("수량?");
int su=Integer.parseInt(sc.nextLine());
System.out.println("가격?");
int dan=Integer.parseInt(sc.nextLine());
// sql문에 변수값을 담아볼껀데 'PreparedStatement'는 미완의 sql문이다.
// sql.. PreparedStatement는 미완의 sql문을 작성
String sql="insert into myshop values (seq1.nextval,?,?,?,sysdate)"; // ?표 처리는 update 직전에 해줄께요
try {
pstmt= conn.prepareCall(sql);
// 업데이트 전에 ?표를 순서대로 바인딩 해야 함
pstmt.setString(1,sang); // 1은 '첫번째 물음표 라는 뜻이다.
pstmt.setInt(2,su); // 2는 '두번째 물음표 라는 뜻이다.
pstmt.setInt(3,dan);
// 업데이트
int a=pstmt.executeUpdate();
if(a==1){
System.out.println("인서트 성공!!");
} else {
System.out.println("인서트 실패ㅠㅠ");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(pstmt,conn);
}
}
// select
public void selectMyshop(){
System.out.println("번호\\t상품명\\t수량\\t숫자\\t입고일");
System.out.println("============================");
// 요청한 sql문을 String에 저장하기
String sql="select * from myshop order by shopnum asc";
Connection conn= db.getConnection();
Statement stmt=null;
ResultSet rs=null;
try {
stmt= conn.createStatement();
rs= stmt.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getInt("shopnum")
+"\\t"+rs.getString("sangpum")
+"\\t"+rs.getInt("su")
+"\\t"+rs.getInt("price")
+"\\t"+rs.getDate("ipgo"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(rs,stmt,conn);
}
}
// delete .. 삭제할 번호는 시퀀스 번호 입력 후 삭제
public void deleteMyshop(){
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
System.out.println("삭제할 시퀀스는?");
int num=Integer.parseInt(sc.nextLine());
String sql="delete from myshop where shopnum=?";
try {
pstmt=conn.prepareStatement(sql);
// ? 한거 바인딩
pstmt.setInt(1,num);
// pstmt.execute();
// System.out.println("***삭제성공***");
int a= pstmt.executeUpdate();
if(a==1){
System.out.println("***삭제성공***");
}
else {
System.out.println("삭제실패 ㅠㅠ");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(pstmt,conn);
}
}
// 수정할 시퀀스가 존재하는지에 대한 메서드
public boolean isdataMyshop(String shopnum){
boolean b=false;
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
String sql="select * from myshop where shopnum=?";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,shopnum);
rs= pstmt.executeQuery();
if(rs.next())
b=true;
else
b=false;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(rs,pstmt,conn);
}
return b;
}
// 수정 시퀀스가 존재할 시 update에 대한 메서드
public void updateMyshop(){
Connection conn= db.getConnection();
PreparedStatement pstmt=null;
System.out.println("수정할 시퀀스 번호?");
String num=sc.nextLine();
// 여기서 시퀀스가 없다면 종료
if(!isdataMyshop(num)){
System.out.println("해당번호는 존재하지 않습니다.");
return;
}
System.out.println("수정할 상품명?");
String sang=sc.nextLine();
System.out.println("수정할 수량은?");
int su=Integer.parseInt(sc.nextLine());
System.out.println("수정할 가격은?");
int price=Integer.parseInt(sc.nextLine());
// sql
String sql="update myshop set sangpum=?,su=?,price=? where shopnum=?";
try {
pstmt= conn.prepareStatement(sql);
// 4개 순서대로 바인딩
pstmt.setString(1,sang);
pstmt.setInt(2,su);
pstmt.setInt(3,price);
pstmt.setString(4,num);
// 최종적으로 업데이트!!
pstmt.execute();
System.out.println("*** 수정 성공 ***");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(pstmt,conn);
}
}
// 검색
public void searchSangpum(){
System.out.println("검색할 상품명(일부)");
String sang=sc.nextLine();
String sql="select * from myshop where sangpum like ?";
System.out.println(sql);
System.out.println("시퀀스\\t상품명\\t수량\\t가격\\t입고날짜");
System.out.println("------------------------------------------[");
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
pstmt=conn.prepareStatement(sql);
// ? 바인딩
pstmt.setString(1,"%"+sang+"%");
rs= pstmt.executeQuery();
while (rs.next()){
System.out.println(rs.getInt("shopnum")
+"\\t"+rs.getString("sangpum")
+"\\t"+rs.getInt("su")
+"\\t"+rs.getInt("price")
+"\\t"+rs.getDate("ipgo"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
db.dbClose(rs,pstmt,conn);
}
}
public static void main(String[] args) {
PrepareMyShop shop=new PrepareMyShop();
Scanner sc=new Scanner(System.in);
int n;
while (true){
System.out.println("***상품입고***");
System.out.println("1.상품추가\\t2.상품삭제\\t3.상품수정\\t4.상품전체출력\\t5.상품검색\\t9.종료");
n=Integer.parseInt(sc.nextLine());
if(n==1){
shop.insertMyshop();
} else if (n==9) {
System.out.println("종료합니다.");
break;
} else if (n==4) {
shop.selectMyshop();
} else if (n==2) {
shop.deleteMyshop();
} else if (n==3) {
shop.updateMyshop();
} else if (n==5) {
shop.searchSangpum();
}
}
}
}
limchard