Database
JDBC
- Java DataBase Connectivity
- 자바가 데이터베이스에 접속해서 SQL을 실행할 수 있도록 하는 표준이다.
- java.sql 패키지와 javax.sql 패키지에 관련 인터페이스와 클래스가 존재한다.
주요 API
- Connection 인터페이스
- 자바프로그램과 데이터베이스간의 연결을 담당하는 객체다.
- Statement createStatement()
- SQL전송을 담당하는 Statement객체를 반환한다.
- PreparedStatement prepareStatement(String sql)
- SQL전송을 담당하는 Statement객체를 반환한다.
- void commit()
- void rollback()
- void close()
- PreparedStatement 인터페이스
- SQL의 전송을 담당하는 객체다.
- int executeUpdate()
- INSERT, UPDATE, DELETE 쿼리를 DB로 전송하고, 실행결과를 반환한다.
- ResultSet executeQuery()
- SELECT 쿼리를 DB로 전송하고, 조회결과를 반환한다.
- void setXXX(int index, XXX value)
- ?위치에 실제값(파라미터값)을 설정한다.
- void setString(int index, String value)
- void setInt(int index, int value)
- void setLong(int index, long value)
- void setDouble(int index, double value)
- void setDate(int index, Date value)
- void close()
- ResultSet 인터페이스
- SELECT 쿼리문의 조회결과를 담당하는 객체다.
- boolean next()
- 커서를 다음행으로 이동시킨다. 데이터행이 존재하면 true를 반환한다.
- XXX getXXX(String columnName)
- void close()
- DriverManager 클래스
- JDCB 표준을 구현한 JDBC드라이버를 관리하는 클래스다.
- JDBC드라이버는 각 DBMS제작사가 자사의 DBMS에 엑세스할 수 있도록 위에 언급한 인터페이스를 구현한 클래스들의 모음이다.
- Connection getConnection(String url, String username, String password)
- url이 가르키는 데이터베이스에 지정된 계정정보로 연결을 시도한 후 연결을 담당하는 Connection객체를 반환한다.
JDBC API를 사용해서 자바와 데이터베이스 연동하기
- 자바와 데이터베이스 연동 절차
- JDBC 드라이브 JVM 메모리에 로딩하기
Class.forName("oracle.jdbc.OracleDriver");
- DBMS와 연결을 담당하는 Connection객체 획득하기
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "hta";
String password = "zxcv1234";
Connection connection = DriverManager.getConnection(url, username, password);
- DDBMS에 SQL 전송과 실행을 담당하는 PreparedStatement객체 획득하기
String sql = "insert into department(deptno, dname, part, build) values (?, ?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(slq);
- SQL의 ?에 값을 대응시키기
pstmt.setInt(1, 204);
pstmt.setString(2, '정밀기계공학과');
pstmt.setInt(3, 200);
pstmt.setString(4, '정밀기계실험관');
- SQL을 DBMS에 보내서 실행시키기
pstmt.executeUpdate();
- DBMS와 연결된 모든 자원을 해제하기
pstmt.close();
connection.close();
데이터 조회하기
public Department getDepartmentByNo(int deptNo) throws SQLException {
Department department = null;
String sql = "String * from department where deptno = ?";
String driverClassName = "oracle.jdbc.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "hta";
String password = "zxcv1234";
Class.forName(driverClassName);
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, deptNo);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
department = new Department();
department.setNo(rs.getInt("deptno"));
department.setName(rs.getString("dname"));
department.setPart(rs.getInt("part"));
department.setBuild(rs.getString("build"));
}
rs.close();
pstmt.close();
connection.close();
return department;
}
public List<Department> getAllDepartments(int deptNo) throws SQLException {
List<Department> departmentList = new ArrayList<>();
String sql = "String * from department";
String driverClassName = "oracle.jdbc.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "hta";
String password = "zxcv1234";
Class.forName(driverClassName);
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Department department = new Department();
department.setNo(rs.getInt("deptno"));
department.setName(rs.getString("dname"));
department.setPart(rs.getInt("part"));
department.setBuild(rs.getString("build"));
departmentList.add(department);
}
rs.close();
pstmt.close();
connection.close();
return departmentList;
}
public void insertDepartment(Department department) throws SQLException {
String sql = "insert into department(deptno, dname, part, build) values (?,?,?,?)";
String driverClassName = "oracle.jdbc.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "hta";
String password = "zxcv1234";
Class.forName(driverClassName);
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, department.getNo());
pstmt.setString(2, department.getName());
pstmt.setInt(3, department.getPart());
pstmt.setString(4, department.getBuild());
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
public void deleteDepartmentByNo(int deptNo) throws SQLException {
String sql = "delete from department where deptno = ?";
String driverClassName = "oracle.jdbc.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "hta";
String password = "zxcv1234";
Class.forName(driverClassName);
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, deptNo);
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
public void updateDepartment(Department department) throws SQLException {
String sql = "update department ";
sql += " set ";
sql += " dname = ?, ";
sql += " part = ?, ";
sql += " build = ? ";
sql += " where deptno = ? "
String driverClassName = "oracle.jdbc.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "hta";
String password = "zxcv1234";
Class.forName(driverClassName);
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, department.getName());
pstmt.setInt(2, department.getPart());
pstmt.setString(3, department.getBuild());
pstmt.setInt(4, department.getNo());
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
package demo;
import java.util.Date;
public class Book {
private int no;
private String title;
private String writer;
private int price;
private int discountPrice;
private int stock;
private Date createdDate;
public Book () {}
public int getNo() {
return no;
}
public void setNo(int no) {
this.no = no;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public int getDiscountPrice() {
return discountPrice;
}
public void setDiscountPrice(int discountPrice) {
this.discountPrice = discountPrice;
}
public int getStock() {
return stock;
}
public void setStock(int stock) {
this.stock = stock;
}
public Date getCreatedDate() {
return createdDate;
}
public void setCreatedDate(Date createdDate) {
this.createdDate = createdDate;
}
@Override
public String toString() {
return "Book [no=" + no + ", title=" + title + ", writer=" + writer + ", price=" + price + ", discountPrice="
+ discountPrice + ", stock=" + stock + ", createdDate=" + createdDate + "]";
}
}
package demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class BookApp {
static String driverClassName = "oracle.jdbc.OracleDriver";
static String url = "jdbc:oracle:thin:@localhost:1521:xe";
static String username = "hr";
static String password = "zxcv1234";
public static void insertBook(Book book) {
String sql = "INSERT INTO sample_books "
+ "(book_no, book_title, book_writer, book_price, book_discount_price, book_stock) "
+ "VALUES (?, ?, ?, ?, ?, ?)";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DriverManager.getConnection(BookApp.url, BookApp.username, BookApp.password);
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, book.getNo());
pstmt.setString(2, book.getTitle());
pstmt.setString(3, book.getWriter());
pstmt.setInt(4, book.getPrice());
pstmt.setInt(5, book.getDiscountPrice());
pstmt.setInt(6, book.getStock());
pstmt.executeUpdate();
} catch (SQLException e) {
System.out.println("[오류코드]" + e.getErrorCode());
System.out.println("[오류메시지]" + e.getMessage());
} finally {
try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
try { if (conn != null) conn.close(); } catch (SQLException e) {}
}
}
public static Book selectBookByNo(int bookNo) {
String sql = "SELECT book_no, book_title, book_writer, book_price, book_discount_price, book_stock, book_created_date "
+ "FROM sample_books "
+ "WHERE book_no = ? ";
Book book = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(BookApp.url, BookApp.username, BookApp.password);
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, bookNo);
rs = pstmt.executeQuery();
if (rs.next()) {
book = new Book();
book.setNo(rs.getInt("book_no"));
book.setTitle(rs.getString("book_title"));
book.setWriter(rs.getString("book_writer"));
book.setPrice(rs.getInt("book_price"));
book.setDiscountPrice(rs.getInt("book_discount_price"));
book.setStock(rs.getInt("book_stock"));
book.setCreatedDate(rs.getDate("book_created_date"));
}
} catch (SQLException e) {
System.out.println("[오류코드] " + e.getErrorCode());
System.out.println("[오류메시지] " + e.getMessage());
} finally {
try {if (rs != null) rs.close(); } catch (SQLException e) {}
try {if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
try {if (conn != null) conn.close(); } catch (SQLException e) {}
}
return book;
}
public static List<Book> selectAllBooks() {
String sql = "SELECT book_no, book_title, book_writer, book_price, book_discount_price, book_stock, book_created_date "
+ "FROM sample_books "
+ "ORDER BY book_no DESC ";
List<Book> books = new ArrayList<Book>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(BookApp.url, BookApp.username, BookApp.password);
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
Book book = new Book();
book.setNo(rs.getInt("book_no"));
book.setTitle(rs.getString("book_title"));
book.setWriter(rs.getString("book_writer"));
book.setPrice(rs.getInt("book_price"));
book.setDiscountPrice(rs.getInt("book_discount_price"));
book.setStock(rs.getInt("book_stock"));
book.setCreatedDate(rs.getDate("book_created_date"));
books.add(book);
}
} catch (SQLException e) {
System.out.println("[오류코드] " + e.getErrorCode());
System.out.println("[오류메시지] " + e.getMessage());
} finally {
try {if (rs != null) rs.close(); } catch (SQLException e) {}
try {if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
try {if (conn != null) conn.close(); } catch (SQLException e) {}
}
return books;
}
public static void updateBook(Book book) {
}
public static void deleteBook(int bookNo) {
String sql = "DELETE FROM sample_books "
+ "WHERE book_no = ?";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DriverManager.getConnection(BookApp.url, BookApp.username, BookApp.password);
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, bookNo);
pstmt.executeUpdate();
} catch (SQLException e) {
System.out.println("[오류코드]" + e.getErrorCode());
System.out.println("[오류메시지]" + e.getMessage());
} finally {
try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
try { if (conn != null) conn.close(); } catch (SQLException e) {}
}
}
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
while (true) {
System.out.println("---------------------------------------------------");
System.out.println("1.조회 2.등록 3.수정 4.삭제 0.종료");
System.out.println("---------------------------------------------------");
System.out.print("메뉴를 선택하세요 : ");
int menuNo = scanner.nextInt();
System.out.println();
scanner.nextLine();
if (menuNo == 1) {
System.out.println("[전체 도서 조회]");
List<Book> books = BookApp.selectAllBooks();
System.out.println(books);
if (books.isEmpty()) {
System.out.println("[안내] 조회된 책 정보가 존재하지 않습니다.");
} else {
System.out.println("------------------------------------------");
for (Book book : books) {
System.out.println("번호 : " + book.getNo());
System.out.println("제목 : " + book.getTitle());
System.out.println("가격 : " + book.getPrice());
System.out.println();
}
System.out.println("------------------------------------------");
}
} else if (menuNo == 2) {
System.out.println("[새 도서 등록]");
System.out.print("제목을 입력해주세요 : ");
String title = scanner.nextLine();
System.out.print("저자를 입력해주세요 : ");
String writer = scanner.nextLine();
System.out.print("번호를 입력해주세요 : ");
int no = scanner.nextInt();
System.out.print("가격을 입력해주세요 : ");
int price = scanner.nextInt();
System.out.print("할인가격을 입력해주세요 : ");
int discountPrice = scanner.nextInt();
System.out.print("재고량을 입력해주세요 : ");
int stock = scanner.nextInt();
Book book = new Book();
book.setTitle(title);
book.setWriter(writer);
book.setNo(no);
book.setPrice(price);
book.setDiscountPrice(discountPrice);
book.setStock(stock);
insertBook(book);
System.out.println("[안내] 새 책이 등록되었습니다.");
} else if (menuNo == 3) {
System.out.println("[도서 정보 수정]");
} else if (menuNo == 4) {
System.out.println("[도서 정보 삭제]");
} else if (menuNo == 0) {
System.out.println("[프로그램 종료]");
break;
}
System.out.println();
System.out.println();
System.out.println();
}
scanner.close();
}
}
