2021. 05. 04(火)TIL

103832 ワード

TIL Java db テキストリンク

Database

JDBC

Java DataBase Connectivity

Javaをデータベースに接続してSQLを実行できるようにする基準です.

java.sqlパッケージとjavax.sqlパッケージには関連インタフェースとクラスが存在します.

プライマリAPI

接続コネクタ

Javaプログラムとデータベースの接続を担当するオブジェクトです.

Statement createStatement()

SQL転送を担当するStatementオブジェクトを返します.

PreparedStatement prepareStatement(String sql)

SQL転送を担当するStatementオブジェクトを返します.

void commit()

SQLの実行結果をデータベースに反映します.

void rollback()

SQL実行結果のDB反映をキャンセルします.

void close()

DBへの接続を解除します.

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()

DB接続リソースを解除します.

ResultSetインタフェース

SELECT検索結果の対象を担当します.

boolean next()

カーソルを次の行に移動します.データ行が存在する場合はtrueを返します.

XXX getXXX(String columnName)

カラム名に相当する値を返します.

void close()

DB接続リソースを解除します.

DriverManagerクラス

JDCB規格を実装するJDBCドライバを管理するクラスです.

JDBCドライバは、各DBMSプロダクションが独自のDBMSにアクセスできるように上記インタフェースを実現するクラスの集合である.

Connection getConnection(String url, String username, String password)

urlで指定したデータベース・アカウント情報を使用して接続を試み、接続を担当する接続オブジェクトを返します.

JDBC APIによるJavaとデータベースのバインド

データベースと

Javaのバインドプロセス

JDBCドライブJVMメモリ

にロード

  Class.forName("oracle.jdbc.OracleDriver");

DBMSに接続する接続オブジェクト

を取得する.

  String url = "jdbc:oracle:thin:@localhost:1521:xe";
  String username = "hta";
  String password = "zxcv1234";
  Connection connection = DriverManager.getConnection(url, username, password);

DDBMSへのSQLの送信および実行のためのPrepared Statementオブジェクト

を取得する

  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) {}
		}
	}
	
	// 반환값이 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

2013.10.9 Difference between Activity and FragmentActivity, getMetrics

[React] 12. react-slickの使用