0620 JAVA JDBC를 통한 SQL 연동

 

JDBC
Java 와 DB를 합친 형태


0. jar 파일 가져오기
폴더-properties-java build path-libraries-classpath
-add jars(경로)
경로 C:\Oracle\product\12.2.0\dbhome_1\jdbc\lib\ojdbc8.jar


1. oracle 드라이버 로드
Class.forName("oracle.jdbc.driver.OracleDriver");
드라이버 로드에 실패할 경우 ClassNotFoundException에 걸린다.


2. 계정 접속
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:orcl","sqlid","sqlpw");
jdbc:oracle:thin(oracle 언어로 작성된):@localhost(내컴퓨터)
:1521(채널번호):orcl(전역 데이터베이스 이름)
접속 아이디
비밀번호 형식으로 작성해준다.

없는 계정에 접속할 경우 SQLExeption에 걸려 접속을 할 수 없다.

3.명령어 분석
String sql ="select * from dbtest";
PreparedStatement ps = conn.prepareStatement(sql);
ps: "select * from dbtest"를 분석한 내용.

4.실행
ResultSet rs =ps.executeQuery();
//rs: ps를 분석한 결과
while(rs.next()){ //rs.next 값을 갖고 있는지 묻는 불리언타입
int a= rs.getInt("num");//num 칼럼 1번째 줄
String b=rs.getString("name");
String c=rs.getString("addr");
System.out.println(a+b+c);
}; //반복문



에러
ClassNotFoundException :드라이버 로드에 실패했을 경우

SQLException: 접속 실패,분석 에러, 없는 칼럼 가져오기, .close에러등
너무 많아서 이유를 알기 힘들다.

SQL에서 작업한 (insert) 결과를 자바에서 볼 수 없다면
커밋을 했는지 확인해봐야 한다.
(커밋을 하기전까진 제대로 추가된게 아니다.)

실행 명령어
select : executeQuery()
insert,update,delete: executeUpdate()

 

int cnt = ps.executeUpdate(); //성공적으로 실행된 갯수를 리턴

 

기본 커넥트 확인:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DB_Connect {

	public static void main(String[] args) {
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("드라이버 로드 성공");
			
			Connection conn = DriverManager.getConnection
					("jdbc:oracle:thin:@localhost:1521:orcl","sqlid","sqlpw");
			System.out.println("접속 성공");
			
			conn.close();
			System.out.println("접속 끊기 성공");
		
		} catch (ClassNotFoundException e) {
			System.out.println("드라이버 로드 실패");
		} catch (SQLException e) {
			System.out.println("접속 실패");
		}
		
		
		
	}

}

 

 

JDBC를 통한 select

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Select {

	public static void main(String[] args) {
		String driver ="oracle.jdbc.driver.OracleDriver";
		String url="jdbc:oracle:thin:@localhost:1521:orcl";
		try {
			Class.forName(driver);
			System.out.println("로드 성공");
			Connection conn=DriverManager.getConnection
					(url,"sqlid","sqlpw");	
			System.out.println("접속 성공");
			String sql ="select * from dbtest order by num";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs =ps.executeQuery();
			while(rs.next()){
				int a= rs.getInt("num");
				String b=rs.getString("name");
				String c=rs.getString("addr");
				System.out.println(a+"/"+b+"/"+c);
			};
			
			conn.close();
		} catch (ClassNotFoundException e) {
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

 

 

JDBC를 통한 insert

 

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Insert {

	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection
					("jdbc:oracle:thin:@localhost:1521:orcl","sqlid","sqlpw");
			int in_num=6;
			String in_name="sim";
			String in_addr="강원";
//			String sql = "insert into dbtest(num,name,addr) values("
//						+in_num+",'"+in_name+"','"+in_addr+"')";
			String sql = "insert into dbtest(num,name,addr) values(?,?,?)";//'?'는 안된다.
			PreparedStatement ps = conn.prepareStatement(sql);//set전에 분석을 해야한다.
			ps.setInt(1, in_num); //1번째 물음표에 넣어라(칼럼의 순서가 아니다.)
			ps.setString(2,in_name);
			ps.setString(3,in_addr);
			int cnt = ps.executeUpdate();
			System.out.println("cnt:"+cnt);
			
			
			conn.close();
		} catch (ClassNotFoundException e) {
			System.out.println("로드 실패");
		} catch (SQLException e) {
			System.out.println("여러 이유로 실패");
		}
		
		
	}

}

 

 

JDBC를 통한 update

 

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Update {

	public static void main(String[] args) {
		String driver ="oracle.jdbc.driver.OracleDriver";
		String url="jdbc:oracle:thin:@localhost:1521:orcl";
		try {
			Class.forName(driver);
			Connection conn=DriverManager.getConnection
					(url,"sqlid","sqlpw");	
			int up_num=5;
			String up_name="jeon";
			String up_addr="남해";
//			String sql="update dbtest set name='"+up_name+"',addr='"
//			+up_addr+"' where num="+up_num;
			String sql ="update dbtest set name=? addr=? where num=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, up_name);
			ps.setString(2,up_addr);
			ps.setInt(3, up_num);
			int cnt= ps.executeUpdate();
			System.out.println("cnt:"+cnt);
			if(cnt>0) {
				System.out.println("수정 성공");
			}
			else if(cnt==0) {
				System.out.println("조건에 맞는 레코드 없음");
			}
			else {
				System.out.println("에러 발생"); //cnt=-1 즉 에러가 나도 여기에 도달하진 못한다.
			}
			
			conn.close();
		} catch (ClassNotFoundException e) {
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
	}

}

 

 

JDBC를 통한 delete

 

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Delete {

	public static void main(String[] args) {
//		3번 조건 삭제 sql문 작성 변수 연결 ? 작성
		String driver ="oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String id = "sqlid";
		String pw = "sqlpw";
		try {
			Class.forName(driver);
			Connection conn =DriverManager.getConnection(url,id,pw);
			int de_num=3;
//			String sql = "delete dbtest where num=3";
//			String sql = "delete dbtest where num="+de_num;
			
			String sql = "delete dbtest where num=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, de_num);
			int cnt = ps.executeUpdate();
			System.out.println("cnt:"+cnt);
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}