Java 프로그래밍 - jdbc 연결

Today

Total

Recent Comments

Recent Posts

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

관리 메뉴

💻

Java 프로그래밍 - jdbc 연결 본문

KITRI/JAVA

Java 프로그래밍 - jdbc 연결

또효니 2020. 5. 25. 15:37

1. Window > Preferences > Java > Build Path > User Library

New 선택해서 라이브러리명 입력

Add External JARS 선택해서 C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6

2. 프로젝트 폴더에서 오른쪽 마우스 > Build Path > Add Libraries... > User Library >

아까 추가한 라이브러리 선택하고 Finish

예제 1) SELECT 부서 테이블 정보를 조회하기

package db;

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

public class Ex01 {
	public static void main(String[] args) {
		// 1단계 : 데이터 베이스 연결
		try {
			String className = "oracle.jdbc.driver.OracleDriver";
			Class.forName(className);
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		// 2단계 : 연결을 관리하는 객체 생성
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:xe"; 
					   /*"jdbc:oracle:thin:@"192.168.0.23":1521:xe"; 로 선택 가능*/
			String id = "java";
			String pass = "1234";
			
			Connection conn = DriverManager.getConnection(url, id, pass);
			
			// 3단계 : 작업관리(Query)
			PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM dept");
			
			// 4단계 : 결과처리
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				int deptNo = rs.getInt("deptno");
				String deptName = rs.getString("dname");
				String loc = rs.getString("loc");
				
				System.out.println(deptNo + "\t" + deptName + "\t" + loc);
			}
			
			// 5단계 : 종료
			rs.close();
			pstmt.close();
			conn.close();
			
		}catch(Exception e) {
			e.printStackTrace();
		}		
	}
}

[참고] PreparedStatement(동적쿼리) vs Statement(정적쿼리) 의 차이

정적 쿼리

- 고정된 SQL 형태를 만들어서 사용한다.

- 완전한 쿼리문일 때 사용

- 최근에는 잘 사용하지 않는다.

동적 쿼리

- 입력 값이나 변경 사항을 추가해 실행할 쿼리문을 문자열로 SQL변수에 작성해 담아 만든 후에 실행해주는 것.
- 불완전한 쿼리문에서 인자값을 계속 실어서 실행할 때 사용

예제 2) INSERT 사용자로부터 입력받아 person테이블에 데이터 추가하기

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Scanner;

public class Ex02 {
	public static void main(String[] args) {
		try {
			// 1단계 : 데이터 베이스 연결
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			// 2단계 : 연결을 관리하는 객체 생성
			String url = "jdbc:oracle:thin:@localhost:1521:xe";
			String id = "java";
			String pass = "1234";
			
			Connection conn = DriverManager.getConnection(url, id, pass);
			
			Scanner sc = new Scanner(System.in);
			System.out.println("번호:");
			int bunho = sc.nextInt();
			
			System.out.println("이름:");
			String name = sc.next();

			System.out.println("키:");
			float ki = sc.nextFloat();
			
			//정적쿼리
			
			/*String query = "insert into person values('"+ bunho +"','"+name+"'," +ki +"')";
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			*/
			
			//동적쿼리
			String sql = "insert into person values(?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, bunho);
			pstmt.setString(2, name);
			pstmt.setFloat(3, ki);
			
			int check = pstmt.executeUpdate();
			if(check > 0) System.out.println("데이터 추가 성공");
			
			pstmt.close();
			conn.close();
			sc.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

SELECT

=> excuteQuery() , ResultSet 사용

UPDATE, INSERT, DELETE

=> executeUpdate() 사용

https://docs.oracle.com/en/java/javase/14/docs/api/java.sql/java/sql/PreparedStatement.html#executeQuery()

PreparedStatement (Java SE 14 & JDK 14)

All Superinterfaces: AutoCloseable, Statement, Wrapper All Known Subinterfaces: CallableStatement public interface PreparedStatement extends Statement An object that represents a precompiled SQL statement. A SQL statement is precompiled and stored in a Pre

docs.oracle.com

예제 3) UPDATE 번호를 입력받아 해당 번호 Person의 키를 수정한다.

package db;

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

/**
 * @Author : 김소현
 * @Date   : 2020. 5. 26.
 * @Description : Person Table : Update
 */
public class Ex03 {
	public static void main(String[] args) {
		//데이터베이스 연결
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		
		//연결관리 객체 Connection 객체 생성
		Connection conn = null;
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:xe";
			String id = "java";
			String pass = "1234";
			
			conn = DriverManager.getConnection(url, id, pass);
		} catch(SQLException e) {
			e.printStackTrace();
		}
		
		//작업관리 - PreparedStatement, 결과처리 - ResultSet/int, 종료 - 각각의 close()
		try {
			Scanner sc = new Scanner(System.in);
			System.out.print("번호:");
			int bunho = sc.nextInt();
			
			String sql = "select * from person where bunho=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bunho);
			
			ResultSet rs = pstmt.executeQuery(); 
			
			if(rs.next()) { //두 줄이상면 while문  
				System.out.println(rs.getInt("bunho") + "\t" + rs.getString("name") +"\t" + rs.getFloat("ki"));
				System.out.println("키 수정:");
				float ki = sc.nextFloat();
				
				String sqlUp = "update person set ki=? where bunho=?";
				pstmt = conn.prepareStatement(sqlUp);
				pstmt.setFloat(1, ki);
				pstmt.setInt(2, bunho);
				
				int check = pstmt.executeUpdate();
				if(check > 0) 
					System.out.println("수정이 완료되었습니다.");
				else 
					System.out.println("수정에 실패하였습니다.");
			} else {
				System.out.println("해당 번호가 존재하지 않습니다.");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
}

예제 4) DELETE 번호를 입력받아 해당 번호 Person 데이터를 삭제한다.

package db;

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

public class Ex04 {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Scanner sc = null;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:xe";
			String id = "java";
			String pass = "1234";
			
			conn = DriverManager.getConnection(url, id, pass);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		
		try {
			sc = new Scanner(System.in);
			System.out.print("번호:");
			int bunho = sc.nextInt();
			
			String sql = "select * from person where bunho = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bunho);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				System.out.println("정말로 삭제하시겠습니까? (Yes):");
				String value = sc.next();
				if(value.equalsIgnoreCase("YES")) {
					String sqlDel = "delete from person where bunho = ?";
					
					if(pstmt!=null)pstmt.close();
					pstmt = conn.prepareStatement(sqlDel);
					pstmt.setInt(1, bunho);
					
					int check = pstmt.executeUpdate();
					System.out.println("삭제:" +check);
				}
			}else {
				System.out.println("해당 번호가 존재하지 않습니다.");
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(sc!=null) sc.close();
				if(pstmt!=null) pstmt.close();
				if(rs!=null) rs.close();
				if(conn!=null) conn.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	}
}

예제 5) MVC 패턴 이용 은행 프로그램 만들기

============Menu============
1. 계좌개설
2. 입금
3. 출금
4. 잔액조회
5. 전체출력
6. 프로그램종료

Oracle SQL Developer 에서 bank 테이블 생성해준다.

AccountDao.java

package bank;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

import dbUtil.ConnectionProvider;
import dbUtil.JdbcUtil;

public class AccountDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	private String sql = null;
	
	private Scanner sc = null;
	private AccountDto accountDto = null;
	
	public void makeAccount() { // 1.계좌개설
		sc = new Scanner(System.in);
		accountDto = new AccountDto();
		
		System.out.print("계좌번호:");
		accountDto.setId(sc.next());
		
		System.out.print("이름:");
		accountDto.setName(sc.next());
		
		System.out.print("입금액:");
		accountDto.setBalance(sc.nextLong());
		
		System.out.println(accountDto.toString()); //데이터 확인!
			
		try {
			Connection conn = ConnectionProvider.getConnection();
			sql = "insert into bank values(bank_num_seq.nextval,?,?,?)";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, accountDto.getId());
			pstmt.setString(2, accountDto.getName());
			pstmt.setLong(3, accountDto.getBalance());
			
			int check = pstmt.executeUpdate();
			System.out.println("insert result:" +check);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(pstmt);
			JdbcUtil.close(conn);
		}
	}
	
	public void deposit() {	// 2.입금
		accountDto = new AccountDto();
		sc = new Scanner(System.in);
		
		System.out.print("계좌번호:");
		accountDto.setId(sc.next());
		
		try {
			conn = ConnectionProvider.getConnection();
			sql = "select balance from bank where id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, accountDto.getId());
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				//System.out.println(rs.getLong("balance")); //확인
				System.out.print("입금액:");
				long money = sc.nextLong();
				
				accountDto.setBalance(rs.getLong("balance") + money);
				JdbcUtil.close(pstmt);
				
				sql = "update bank set balance = ? where id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setLong(1, accountDto.getBalance());
				pstmt.setString(2, accountDto.getId());
				
				int check = pstmt.executeUpdate();
				System.out.println("입금 확인:" + check);
				
			}else {
				System.out.println("계좌번호가 존재하지 않습니다.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtil.close(rs);
				JdbcUtil.close(pstmt);
				JdbcUtil.close(conn);
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	public void withdraw() { // 3.출금
		accountDto = new AccountDto();
		sc = new Scanner(System.in);
		
		System.out.print("계좌번호:");
		accountDto.setId(sc.next());
		
		System.out.print("출금액:");
		long money = sc.nextLong();
		accountDto.setBalance(money);
		
		try {
			conn = ConnectionProvider.getConnection();
			sql = "select balance from bank where id = ? and balance >= ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, accountDto.getId());
			pstmt.setLong(2, accountDto.getBalance());
			
			rs = pstmt.executeQuery();
			if(rs.next()) {
				accountDto.setBalance(rs.getLong("balance") - money);
				JdbcUtil.close(pstmt);
				
				sql = "update bank set balance=? where id=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setLong(1, accountDto.getBalance());
				pstmt.setString(2, accountDto.getId());

				int check = pstmt.executeUpdate();
				System.out.println("출금 확인:" + check);
				
			}else {
				System.out.println("계좌번호 또는 잔액을 확인하세요.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				JdbcUtil.close(rs);
				JdbcUtil.close(pstmt);
				JdbcUtil.close(conn);
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	public void inquire() {	// 4.잔액조회
		accountDto = new AccountDto();
		sc = new Scanner(System.in);
		
		System.out.print("계좌번호:");
		accountDto.setId(sc.next());
		
		try {
			conn = ConnectionProvider.getConnection();

			sql = "select * from bank where id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, accountDto.getId());
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				accountDto.setNum(rs.getInt("num"));
				accountDto.setId(rs.getString("id"));
				accountDto.setName(rs.getString("name"));
				accountDto.setBalance(rs.getLong("balance"));

				System.out.println(accountDto.getNum() +"\t" + accountDto.getId() + "\t"
							+ accountDto.getName() + "\t" + accountDto.getBalance());
			}else {
				System.out.println("계좌번호가 존재하지 않습니다.");
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				JdbcUtil.close(rs);
				JdbcUtil.close(pstmt);
				JdbcUtil.close(conn);
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	public void showData() {	// 5. 전체출력
		conn = ConnectionProvider.getConnection();
		ArrayList<AccountDto> list = new ArrayList<AccountDto>(); //데이터가 여러개면은 ArrayList활용
		
		try {
			sql = "select * from bank order by num asc";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				accountDto = new AccountDto();
				accountDto.setNum(rs.getInt("number"));
				accountDto.setId(rs.getString("id"));
				accountDto.setName(rs.getString("name"));
				accountDto.setBalance(rs.getLong("balance"));
				
				list.add(accountDto);
			}
			
			for(int i=0; i<list.size(); i++) {
				AccountDto dto = list.get(i);
				System.out.println(dto.getNum() + "\t" +dto.getId() +"\t"+ 
								dto.getName() +"\t"+dto.getBalance());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
			JdbcUtil.close(conn);
		}
		
	}
	
	
}

AccountDto.java

package bank;

/**
 * @Author : 김소현
 * @Date   : 2020. 5. 26.
 * @Description : 
 */

public class AccountDto {
	private int num;		//자동증가번호
	private String id;		//계좌번호
	private String name;	//이름
	private long balance;	//잔액
	
	public AccountDto() {}
	
	public AccountDto(int num, String id, String name, long balance) {
		this.num = num;
		this.id = id;
		this.name = name;
		this.balance = balance;
	}
	
	public int getNum() {
		return num;
	}
	
	public void setNum(int num) {
		this.num = num;
	}
	
	public String getId() {
		return id;
	}
	
	public void setId(String id) {
		this.id = id;
	}
	
	public String getName() {
		return name;
	}
	
	public void setName(String name) {
		this.name = name;
	}
	
	public long getBalance() {
		return balance;
	}
	
	public void setBalance(long balance) {
		this.balance = balance;
	}
	
	@Override
	public String toString() {
		return "AccountDto [num=" + num + ", id=" + id + ", name=" + name + ", balance=" + balance + "]";
	}
}

AccountView.java

package bank;

import java.util.Scanner;

import dbUtil.DriverProvider;

public class AccountView {
	public static void main(String[] args) {
		DriverProvider dp = new DriverProvider();
		dp.getDriver();
		
		AccountDao jbank = new AccountDao();
		Scanner sc = new Scanner(System.in);
		
		while(true) {
			printMenu();
			
			System.out.print("선택:");
			int choice = sc.nextInt();
			
			switch(choice) {
			case 1 :
				jbank.makeAccount();
				break;
			case 2 : 
				jbank.deposit();
				break;
			case 3 :
				jbank.withdraw();
				break;
			case 4 :
				jbank.inquire();
				break;
			case 5 : 
				jbank.showData();
				break;
			case 6 : 
				System.out.println("종료합니다.");
				if(sc!=null) sc.close();
				System.exit(0); //프로그램 종료
			}
		}
	}
	
	public static void printMenu() {
		System.out.println("============Menu============");
		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.println();
	}
}

ConnectionProvider.java

package dbUtil;

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

public class ConnectionProvider {
	
	public static Connection getConnection() {
		Connection conn = null;
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:xe";
			String id = "java";
			String pass = "1234";
			
			conn = DriverManager.getConnection(url, id, pass);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
}

DriverProvider.java

package dbUtil;

public class DriverProvider {
	public void getDriver() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
}

JdbcUtil.java

package dbUtil;

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

public class JdbcUtil {
	
	public static void close(Connection conn) {
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(PreparedStatement pstmt) {
		if(pstmt!=null) {
			try {
				pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void close(ResultSet rs) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

'KITRI > JAVA' 카테고리의 다른 글

Java 프로그래밍 - 채팅 프로그램 (1)	2020.05.25
Java 프로그래밍 - 네트워크 통신 (0)	2020.05.25
Java 프로그래밍 - 스레드(Thread) (0)	2020.05.20
Java 프로그래밍 - byte 입출력, char입출력 (0)	2020.05.20
Java 프로그래밍 - 예외처리(Exception) (0)	2020.05.18

'KITRI/JAVA' Related Articles

Comments

💻

Java 프로그래밍 - jdbc 연결 본문

Java 프로그래밍 - jdbc 연결

'KITRI > JAVA' 카테고리의 다른 글

티스토리툴바