2021. 05. 04(火)TIL
Database
JDBC
プライマリAPI
JDBC APIによるJavaとデータベースのバインド
データベースと Class.forName("oracle.jdbc.OracleDriver"); String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "hta";
String password = "zxcv1234";
Connection connection = DriverManager.getConnection(url, username, password); String sql = "insert into department(deptno, dname, part, build) values (?, ?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(slq); pstmt.setInt(1, 204);
pstmt.setString(2, '정밀기계공학과');
pstmt.setInt(3, 200);
pstmt.setString(4, '정밀기계실험관'); pstmt.executeUpdate(); 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) {}
}
}
// 반환값이 null 일 수도 있다. 조회된 데이터가 없으면 null을 반환한다.
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();
// 한 행이 조회되거나 조회된 행이 없거나 2가지만 가능
// rs.next() 실행결과가 true면 조회된 행이 있는경우
// rs.next() 실행결과가 false면 조회된 행이 없는경우
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;
}
// 반환값은 언제나 null이 아니다. 조회된 데이터가 없으면 List객체가 비어있을 뿐이다.
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"));
// Book객체를 ArrayList객체에 추가한다.
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();
}
}
Reference
この問題について(2021. 05. 04(火)TIL), 我々は、より多くの情報をここで見つけました https://velog.io/@eastgun_/2021.-05.-04화-TILテキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol