Search

๊ฒŒ์‹œํŒ ํ”„๋กœ๊ทธ๋žจ - Oracle

๊ฒŒ์‹œํŒ ํ”„๋กœ๊ทธ๋žจ ๋งŒ๋“ค๊ธฐ

1.
์ž๋ฐ” ํ”„๋กœ์ ํŠธ ์ƒ์„ฑ
2.
OJDBC ๋“œ๋ผ์ด๋ฒ„ ์ถ”๊ฐ€ํ•˜๊ธฐ
3.
Java ํ”„๋กœ๊ทธ๋žจ์—์„œ Oracle DB ์—ฐ๊ฒฐ
a.
JDBConnection.java
4.
๊ฒŒ์‹œํŒ ํ”„๋กœ๊ทธ๋žจ ์ž‘์„ฑ
a.
DTO
i.
Board.java
b.
Service
i.
BoardService.java
ii.
BoardServiceImpl.java
c.
DAO
i.
BoardDAO.java JDBConnection.java
d.
Main.java

์ž๋ฐ” ํ”„๋กœ์ ํŠธ ์ƒ์„ฑ

OJDBC ๋“œ๋ผ์ด๋ฒ„ ์ถ”๊ฐ€ํ•˜๊ธฐ

Java ํ”„๋กœ๊ทธ๋žจ์—์„œ Oracle DB ์—ฐ๊ฒฐ

1.
JDBConnection.java
2.
module-info.java

JDBConnection.java

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class JDBConnection { public Connection con; // ์—ฐ๊ฒฐ๋œ ๋“œ๋ผ์ด๋ฒ„์— SQL์„ ์š”์ฒญํ•  ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜๋Š” ํด๋ž˜์Šค public Statement stmt; // SQL ์‹คํ–‰ ์š”์ฒญ์„ ํ•˜๋Š” ํด๋ž˜์Šค public PreparedStatement psmt; // Statement ์—์„œ ? ํŒŒ๋ผ๋ฏธํ„ฐ ํ™•์žฅ๊ธฐ๋Šฅ์„ ์ถ”๊ฐ€๋กœ ์ œ๊ณตํ•˜๋Š” ํด๋ž˜์Šค public ResultSet rs; // SQL ์‹คํ–‰ ๊ฒฐ๊ณผ๋ฅผ ๋ฐ›์•„์˜ค๋Š” ํด๋ž˜์Šค // ๊ธฐ๋ณธ ์ƒ์„ฑ์ž public JDBConnection() { // JDBC ๋“œ๋ผ์ด๋ฒ„ ๋กœ๋“œ // Oracle try { // ojdbc8.jar ๋“œ๋ผ์ด๋ฒ„์˜ ํด๋ž˜์Šค๋ฅผ ๋กœ๋“œํ•œ๋‹ค. Class.forName("oracle.jdbc.OracleDriver"); // DB์— ์—ฐ๊ฒฐ // - ์—ฐ๊ฒฐ์— ํ•„์š”ํ•œ ์ •๋ณด : URL, id, pw // URL : jdbc:mysql://๋„๋ฉ”์ธ:[PORT]/[์Šคํ‚ค๋งˆ]?์˜ต์…˜ํŒŒ๋ผ๋ฏธํ„ฐ // * ๋‚ด PC์˜ IP์ฃผ์†Œ : localhost : 127.0.0.1 // * 1521 : Oracle ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ๊ธฐ๋ณธ ํฌํŠธ // * SID // * 11g : xe // * 12c~ : orcl String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String id = "aloha"; String pw = "123456"; // ์ž๋ฐ” ํ”„๋กœ๊ทธ๋žจ์—์„œ JDBC ๋“œ๋ผ์ด๋ฒ„๋ฅผ ์—ฐ๊ฒฐ์‹œ์ผœ์ฃผ๋Š” ํด๋ž˜์Šค // getConnection() ๋ฉ”์†Œ๋“œ๋กœ DB์— ์—ฐ๊ฒฐ ์š”์ฒญํ•˜๊ณ  ์ƒ์„ฑ๋œ Connection ๊ฐ์ฒด๋ฅผ ๋ฐ˜ํ™˜๋ฐ›๋Š”๋‹ค. con = DriverManager.getConnection(url, id, pw); System.out.println("DB ์—ฐ๊ฒฐ ์„ฑ๊ณต"); } catch (Exception e) { System.err.println("DB ์—ฐ๊ฒฐ ์‹คํŒจ"); e.printStackTrace(); } } }
Java
๋ณต์‚ฌ

module-info.java

module ๊ฒŒ์‹œํŒ { // ํ”„๋กœ์ ํŠธ๊ฐ€ ์˜์กดํ•˜๊ณ  ์žˆ๋Š” ๋ชจ๋“ˆ ์„ค์ • - ์™ธ๋ถ€์— ๊ณต๊ฐœํ•  ํŒจํ‚ค์ง€ ์ง€์ • // * ํŒจํ‚ค์ง€๋ฅผ ์ง€์ •ํ•ด์ฃผ์–ด์•ผ, ํ•ด๋‹น ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ์˜ ํŒจํ‚ค์ง€ ํด๋ž˜์Šค๋“ค์„ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Œ. requires java.sql; }
Java
๋ณต์‚ฌ

๊ฒŒ์‹œํŒ ํ”„๋กœ๊ทธ๋žจ ์ž‘์„ฑ

ํ”„๋กœ์ ํŠธ ๊ตฌ์กฐ

๐Ÿ“ฆ DTO ๐Ÿ“„ Board.java ๐Ÿ“ฆ Service ๐Ÿ“„ BoardService.java ๐Ÿ“„ BoardServiceImpl.java ๐Ÿ“ฆ DAO ๐Ÿ“„ BoardDAO.java ๐Ÿ“„ JDBConnection.java ๐Ÿ“„ Main.java
Java
๋ณต์‚ฌ

์•„ํ‚คํ…์ฒ˜ ํŒจํ„ด

3๊ณ„์ธต ํŒจํ„ด (3-Tier Architecture)

3๊ฐœ์˜ ๊ณ„์ธต์œผ๋กœ ๊ตฌ์กฐ๋ฅผ ๋ถ„๋ฆฌํ•˜์—ฌ ์—ญํ• ์„ ๋ช…ํ™•ํ•˜๊ฒŒ ํ•˜์—ฌ SW ๋ฅผ ์„ค๊ณ„ํ•˜๋Š” ํŒจํ„ด
+------------------------+ | Presentation Layer | <- ์‚ฌ์šฉ์ž ์ธํ„ฐํŽ˜์ด์Šค +------------------------+ | v +------------------------+ | Business Logic Layer | <- ์• ํ”Œ๋ฆฌ์ผ€์ด์…˜ ๋กœ์ง +------------------------+ | v +------------------------+ | Data Access Layer | <- ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์™€์˜ ์ƒํ˜ธ์ž‘์šฉ +------------------------+
Java
๋ณต์‚ฌ

๊ณ„์ธต

โ€ข
ํ”„๋ฆฌ์  ํ…Œ์ด์…˜ ๊ณ„์ธต (Presentation Layer)
โ€ข
๋น„์ฆˆ๋‹ˆ์Šค ๋กœ์ง ๊ณ„์ธต (Business Logic Layer)
โ€ข
๋ฐ์ดํ„ฐ ๊ณ„์ธต (Data Access Layer)

ํ”„๋ฆฌ์  ํ…Œ์ด์…˜ ๊ณ„์ธต (Presentation Layer)

์‚ฌ์šฉ์ž์™€ ์ง์ ‘ ์ƒํ˜ธ์ž‘์šฉํ•˜๋Š” ๊ณ„์ธต์œผ๋กœ, UI ์—ญํ• ์„ ํ•œ๋‹ค.

๋น„์ฆˆ๋‹ˆ์Šค ๋กœ์ง ๊ณ„์ธต (Business Logic Layer)

ํ•ต์‹ฌ ๋กœ์ง์„ ์ฒ˜๋ฆฌํ•˜๋Š” ๊ณ„์ธต - ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌ ๋กœ์ง - ์—…๋ฌด ํ”„๋กœ์„ธ์Šค(๋น„์ฆˆ๋‹ˆ์Šค ๊ทœ์น™) ๋กœ์ง

๋ฐ์ดํ„ฐ ๊ณ„์ธต (Data Access Layer)

๋ฐ์ดํ„ฐ์™€ ์ƒํ˜ธ์ž‘์šฉํ•˜๋Š” ๊ณ„์ธต
โ€ข
๋ฐ์ดํ„ฐ ๊ณ„์ธต ๊ตฌ์„ฑ์š”์†Œ
โ—ฆ
DTO (Data Transfer Object)
โ—ฆ
DAO (Data Access Object)

DTO (Data Transfer Object)

๋ฐ์ดํ„ฐ ์ „์†ก ๊ฐ์ฒด
โ€ข
์„œ๋กœ ๋‹ค๋ฅธ ํด๋ž˜์Šค ๊ฐ„์—, ์„œ๋กœ ๋‹ค๋ฅธ ๊ณ„์ธต ์‚ฌ์ด์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์ „์†กํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ
โ€ข
์ฃผ๋กœ, ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋กœ๋ถ€ํ„ฐ ์ฝ์–ด์˜จ ๋ฐ์ดํ„ฐ ๋‹จ์œ„๋ฅผ ๊ฐ์ฒด๋กœ ๊ฐ€์ ธ์™€์„œ ์‚ฌ์šฉ
โ€ข
DB( ํ…Œ์ด๋ธ” ) ---> ์ž๋ฐ” ( ๊ฐ์ฒด )
โ€ข
ex) ๊ฒŒ์‹œํŒ ์ •๋ณด ํด๋ž˜์Šค Board

DAO (Data Access Object)

๋ฐ์ดํ„ฐ ์ ‘๊ทผ ๊ฐ์ฒด
โ€ข
๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์ ‘๊ทผ์„ ํ•˜์—ฌ ์ƒํ˜ธ์ž‘์šฉ์„ ํ•˜๋Š” ๊ฐ์ฒด
โ€ข
์—…๋ฌด ๋กœ์ง๊ณผ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—ฐ๋™ ๋กœ์ง์„ ๋ถ„๋ฆฌํ•˜๊ธฐ ์œ„ํ•ด์„œ ์‚ฌ์šฉ
โ€ข
ex) ๊ฒŒ์‹œ๊ธ€ ๋ฐ์ดํ„ฐ ์ ‘๊ทผ ํด๋ž˜์Šค BoardAccess

์†Œ์Šค์ฝ”๋“œ

1.
DTO
a.
Board.java
2.
Service
a.
BoardService.java
b.
BoardServiceImpl.java
3.
DAO
a.
BoardDAO.java
b.
JDBConnection.java
4.
Main.java

DTO

โ€ข
Board.java

Board.java

package ๊ฒŒ์‹œํŒ.DTO; import java.util.Date; import lombok.AllArgsConstructor; import lombok.Data; /** * (DTO) * ๊ฒŒ์‹œ๊ธ€ ์ •๋ณด * Java DB * - ๊ฒŒ์‹œ๊ธ€ ๋ฒˆํ˜ธ no no * - ์ œ๋ชฉ title title * - ์ž‘์„ฑ์ž writer writer * - ๋‚ด์šฉ content content * - ๋“ฑ๋ก์ผ์ž regDate red_date * - ์ˆ˜์ •์ผ์ž updDate upd_date */ @Data @AllArgsConstructor public class Board { private int no; private String title; private String writer; private String content; private Date regDate; private Date updDate; public Board() { this("(์ œ๋ชฉ์—†์Œ)", "(์ž‘์„ฑ์ž์—†์Œ)", ""); } public Board(String title, String writer, String content) { this.title = title; this.writer = writer; this.content = content; } }
Java
๋ณต์‚ฌ

Service

โ€ข
BoardService.java
โ€ข
BoardServiceImpl.java

BoardService.java

package ๊ฒŒ์‹œํŒ.Service; import java.util.List; import ๊ฒŒ์‹œํŒ.DTO.Board; /** * ๊ฒŒ์‹œํŒ ํ”„๋กœ๊ทธ๋žจ - ๊ธฐ๋Šฅ ๋ฉ”์†Œ๋“œ * - ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก * - ๊ฒŒ์‹œ๊ธ€ ์กฐํšŒ * - ๊ฒŒ์‹œ๊ธ€ ๋“ฑ๋ก * - ๊ฒŒ์‹œ๊ธ€ ์ˆ˜์ • * - ๊ฒŒ์‹œ๊ธ€ ์‚ญ์ œ */ public interface BoardService { // ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก List<Board> list(); // ๊ฒŒ์‹œ๊ธ€ ์กฐํšŒ Board select(int no); // ๊ฒŒ์‹œ๊ธ€ ๋“ฑ๋ก int insert(Board board); // ๊ฒŒ์‹œ๊ธ€ ์ˆ˜์ • int update(Board board); // ๊ฒŒ์‹œ๊ธ€ ์‚ญ์ œ int delete(int no); }
Java
๋ณต์‚ฌ

BoardServiceImpl.java

package ๊ฒŒ์‹œํŒ.Service; import java.util.List; import ๊ฒŒ์‹œํŒ.DAO.BoardDAO; import ๊ฒŒ์‹œํŒ.DTO.Board; /** * ๊ฒŒ์‹œํŒ ๊ธฐ๋Šฅ - ๋น„์ฆˆ๋‹ˆ์Šค ๋กœ์ง ํด๋ž˜์Šค */ public class BoardServiceImpl implements BoardService { private BoardDAO boardDAO = new BoardDAO(); @Override public List<Board> list() { // DAO ๊ฐ์ฒด๋กœ ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์š”์ฒญํ•˜๊ณ  List<Board> List<Board> boardList = boardDAO.list(); // ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ๋ฐ์ดํ„ฐ ๋ฐ˜ํ™˜ return boardList; } @Override public Board select(int no) { // ๊ฒŒ์‹œ๊ธ€ ๋ฒˆํ˜ธ no ๋ฅผ DB ๋กœ ๋„˜๊ฒจ์ฃผ๊ณ  ๊ฒŒ์‹œ๊ธ€ ์ •๋ณด ์š”์ฒญ Board board = boardDAO.select(no); // ๊ฒŒ์‹œ๊ธ€ ์ •๋ณด ๋ฐ˜ํ™˜ return board; } @Override public int insert(Board board) { // ๊ฒŒ์‹œ๊ธ€ ์ •๋ณด๋ฅผ ์ „๋‹ฌํ•˜์—ฌ DB ์— ๋ฐ์ดํ„ฐ ๋“ฑ๋ก ์š”์ฒญ int result = boardDAO.insert(board); // ์ ์šฉ๋œ ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜๋ฅผ ๋ฐ˜ํ™˜ // - result(๊ฒฐ๊ณผ) : 0 --> ๋ฐ์ดํ„ฐ ๋“ฑ๋ก ์‹คํŒจ // 1 --> ๋ฐ์ดํ„ฐ ๋“ฑ๋ก ์„ฑ๊ณต if( result > 0 ) System.out.println("๋ฐ์ดํ„ฐ ๋“ฑ๋ก ์„ฑ๊ณต!"); else System.err.println("๋ฐ์ดํ„ฐ ๋“ฑ๋ก ์‹คํŒจ!"); return result; } @Override public int update(Board board) { int result = boardDAO.update(board); // ์ ์šฉ๋œ ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜๋ฅผ ๋ฐ˜ํ™˜ // - result(๊ฒฐ๊ณผ) : 0 --> ๋ฐ์ดํ„ฐ ์ˆ˜์ • ์‹คํŒจ // 1 --> ๋ฐ์ดํ„ฐ ์ˆ˜์ • ์„ฑ๊ณต if( result > 0 ) System.out.println("๋ฐ์ดํ„ฐ ์ˆ˜์ • ์„ฑ๊ณต!"); else System.err.println("๋ฐ์ดํ„ฐ ์ˆ˜์ • ์‹คํŒจ!"); return result; } @Override public int delete(int no) { int result = boardDAO.delete(no); // ์ ์šฉ๋œ ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜๋ฅผ ๋ฐ˜ํ™˜ // - result(๊ฒฐ๊ณผ) : 0 --> ๋ฐ์ดํ„ฐ ์‚ญ์ œ ์‹คํŒจ // 1 --> ์‚ญ์ œ ์ˆ˜์ • ์„ฑ๊ณต if( result > 0 ) System.out.println("๋ฐ์ดํ„ฐ ์‚ญ์ œ ์„ฑ๊ณต!"); else System.err.println("๋ฐ์ดํ„ฐ ์‚ญ์ œ ์‹คํŒจ!"); return result; } }
Java
๋ณต์‚ฌ

DAO

โ€ข
BoardDAO.java
โ€ข
JDBConnection.java

BoardDAO.java

package ๊ฒŒ์‹œํŒ.DAO; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import ๊ฒŒ์‹œํŒ.DTO.Board; /** * ๋ฐ์ดํ„ฐ ์ ‘๊ทผ ๊ฐ์ฒด * - ๊ฒŒ์‹œ๊ธ€ ๋ฐ์ดํ„ฐ๋ฅผ ์ ‘๊ทผ */ public class BoardDAO extends JDBConnection { /** * ๋ฐ์ดํ„ฐ ๋ชฉ๋ก * @return */ public List<Board> list() { // ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก์„ ๋‹ด์„ ์ปฌ๋ ‰์…˜ ๊ฐ์ฒด ์ƒ์„ฑ List<Board> boardList = new ArrayList<Board>(); // SQL ์ž‘์„ฑ String sql = " SELECT * " + " FROM board "; try { // 1. SQL ์‹คํ–‰ ๊ฐ์ฒด ์ƒ์„ฑ - Statement (stmt) stmt = con.createStatement(); // 2. SQL ์‹คํ–‰ ์š”์ฒญ -> ๊ฒฐ๊ณผ ResultSet (rs) rs = stmt.executeQuery(sql); // 3. ์กฐํšŒ๋œ ๊ฒฐ๊ณผ๋ฅผ ๋ฆฌ์ŠคํŠธ(boardList)์— ์ถ”๊ฐ€ while( rs.next() ) { // next() : ์กฐํšŒ ๊ฒฐ๊ณผ์˜ ๋‹ค์Œ ๋ฐ์ดํ„ฐ๋กœ ์ด๋™ Board board = new Board(); // ๊ฒฐ๊ณผ ๋ฐ์ดํ„ฐ ๊ฐ€์ ธ์˜ค๊ธฐ // rs.getXXX("์ปฌ๋Ÿผ๋ช…") : ํ•ด๋‹น ์ปฌ๋Ÿผ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๋ฐ˜ํ™˜ board.setNo( rs.getInt("no") ); board.setTitle( rs.getString("title") ); board.setWriter( rs.getString("writer") ); board.setContent( rs.getString("content") ); board.setRegDate( rs.getTimestamp("reg_date") ); board.setUpdDate( rs.getTimestamp("upd_date") ); // ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์ถ”๊ฐ€ boardList.add(board); } } catch (SQLException e) { System.err.println("๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์กฐํšŒ ์‹œ, ์˜ˆ์™ธ ๋ฐœ์ƒ"); e.printStackTrace(); } // 4. ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ๋ฐ˜ํ™˜ return boardList; } /** * ๋ฐ์ดํ„ฐ ์กฐํšŒ * @param no * @return */ public Board select(int no) { // ๊ฒŒ์‹œ๊ธ€ ์ •๋ณด ๊ฐ์ฒด ์ƒ์„ฑ Board board = new Board(); // SQL ์ž‘์„ฑ String sql = " SELECT * " + " FROM board " + " WHERE no = ? "; // no ๊ฐ€ ? ์ธ ๋ฐ์ดํ„ฐ๋งŒ ์กฐํšŒ // ๋ฐ์ดํ„ฐ ์กฐํšŒ : SQL ์‹คํ–‰ ๊ฐ์ฒด ์ƒ์„ฑ -> SQL ์‹คํ–‰ ์š”์ฒญ -> ์กฐํšŒ ๊ฒฐ๊ณผ -> ๋ฐ˜ํ™˜ try { // SQL ์‹คํ–‰ ๊ฐ์ฒด ์ƒ์„ฑ - PreparedStatement (psmt) psmt = con.prepareStatement(sql); // ? ๋™์  ํŒŒ๋ผ๋ฏธํ„ฐ ๋ฐ”์ธ๋”ฉ // * psmt.setXXX( ์ˆœ์„œ๋ฒˆํ˜ธ, ๋งคํ•‘ํ•  ๊ฐ’ ); psmt.setInt( 1, no ); // 1๋ฒˆ์งธ ? ํŒŒ๋ผ๋ฏธํ„ฐ์— ๋งคํ•‘ // SQL ์‹คํ–‰ ์š”์ฒญ rs = psmt.executeQuery(); // ์กฐํšŒ ๊ฒฐ๊ณผ 1๊ฑด ๊ฐ€์ ธ์˜ค๊ธฐ if( rs.next() ) { // ๊ฒฐ๊ณผ ๋ฐ์ดํ„ฐ ๊ฐ€์ ธ์˜ค๊ธฐ // rs.getXXX("์ปฌ๋Ÿผ๋ช…") : ํ•ด๋‹น ์ปฌ๋Ÿผ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๋ฐ˜ํ™˜ board.setNo( rs.getInt("no") ); board.setTitle( rs.getString("title") ); board.setWriter( rs.getString("writer") ); board.setContent( rs.getString("content") ); board.setRegDate( rs.getTimestamp("reg_date") ); board.setUpdDate( rs.getTimestamp("upd_date") ); } } catch (SQLException e) { System.err.println("๊ฒŒ์‹œ๊ธ€ ์กฐํšŒ ์‹œ, ์˜ˆ์™ธ ๋ฐœ์ƒ"); e.printStackTrace(); } // ๊ฒŒ์‹œ๊ธ€ ์ •๋ณด 1๊ฑด ๋ฐ˜ํ™˜ return board; } /** * ๋ฐ์ดํ„ฐ ๋“ฑ๋ก * @param board * @return */ public int insert(Board board) { int result = 0; // ๊ฒฐ๊ณผ : ์ ์šฉ๋œ ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜ String sql = " INSERT INTO board ( no, title, writer, content ) " + " VALUES( SEQ_BOARD.nextval, ?, ?, ? ) "; try { psmt = con.prepareStatement(sql); // ์ฟผ๋ฆฌ ์‹คํ–‰ ๊ฐ์ฒด ์ƒ์„ฑ psmt.setString( 1, board.getTitle() ); // 1๋ฒˆ ? ์— title(์ œ๋ชฉ) ๋งคํ•‘ psmt.setString( 2, board.getWriter() ); // 2๋ฒˆ ? ์— writer(์ž‘์„ฑ์ž) ๋งคํ•‘ psmt.setString( 3, board.getContent() ); // 3๋ฒˆ ? ์— content(๋‚ด์šฉ) ๋งคํ•‘ result = psmt.executeUpdate(); // SQL ์‹คํ–‰ ์š”์ฒญ // * executeUpdate() // SQL(INSERT, UPDATE, DELETE) ์‹คํ–‰ ์‹œ ์ ์šฉ๋œ ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜๋ฅผ int ํƒ€์ž…์œผ๋กœ ๋ฐ›์•„์˜จ๋‹ค. // ex) ๊ฒŒ์‹œ๊ธ€ 1๊ฐœ ์ ์šฉ ์„ฑ๊ณต ์‹œ, result : 1 // ์‹คํŒจ ์‹œ, result : 0 } catch (SQLException e) { System.err.println("๊ฒŒ์‹œ๊ธ€ ๋“ฑ๋ก ์‹œ, ์˜ˆ์™ธ ๋ฐœ์ƒ"); e.printStackTrace(); } return result; } /** * ๋ฐ์ดํ„ฐ ์ˆ˜์ • * @param board * @return */ public int update(Board board) { int result = 0; // ๊ฒฐ๊ณผ : ์ ์šฉ๋œ ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜ String sql = " UPDATE board " + " SET title = ? " + " ,writer = ? " + " ,content = ?" + " ,upd_date = sysdate " + " WHERE no = ? "; try { psmt = con.prepareStatement(sql); // ์ฟผ๋ฆฌ ์‹คํ–‰ ๊ฐ์ฒด ์ƒ์„ฑ psmt.setString( 1, board.getTitle() ); // 1๋ฒˆ ? ์— title(์ œ๋ชฉ) ๋งคํ•‘ psmt.setString( 2, board.getWriter() ); // 2๋ฒˆ ? ์— writer(์ž‘์„ฑ์ž) ๋งคํ•‘ psmt.setString( 3, board.getContent() ); // 3๋ฒˆ ? ์— content(๋‚ด์šฉ) ๋งคํ•‘ psmt.setInt( 4, board.getNo() ); // 4๋ฒˆ ? ์— no(๊ธ€๋ฒˆํ˜ธ) ๋งคํ•‘ result = psmt.executeUpdate(); // SQL ์‹คํ–‰ ์š”์ฒญ // * executeUpdate() // SQL(INSERT, UPDATE, DELETE) ์‹คํ–‰ ์‹œ ์ ์šฉ๋œ ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜๋ฅผ int ํƒ€์ž…์œผ๋กœ ๋ฐ›์•„์˜จ๋‹ค. // ex) ๊ฒŒ์‹œ๊ธ€ 1๊ฐœ ์ ์šฉ ์„ฑ๊ณต ์‹œ, result : 1 // ์‹คํŒจ ์‹œ, result : 0 } catch (SQLException e) { System.err.println("๊ฒŒ์‹œ๊ธ€ ์ˆ˜์ • ์‹œ, ์˜ˆ์™ธ ๋ฐœ์ƒ"); e.printStackTrace(); } return result; } /** * ๋ฐ์ดํ„ฐ ์‚ญ์ œ * @param no * @return */ public int delete(int no) { int result = 0; // ๊ฒฐ๊ณผ : ์ ์šฉ๋œ ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜ String sql = " DELETE FROM board " + " WHERE no = ? "; try { psmt = con.prepareStatement(sql); // ์ฟผ๋ฆฌ ์‹คํ–‰ ๊ฐ์ฒด ์ƒ์„ฑ psmt.setInt( 1, no ); // 1๋ฒˆ ? ์— no(๊ธ€๋ฒˆํ˜ธ) ๋งคํ•‘ result = psmt.executeUpdate(); // SQL ์‹คํ–‰ ์š”์ฒญ // * executeUpdate() // SQL(INSERT, UPDATE, DELETE) ์‹คํ–‰ ์‹œ ์ ์šฉ๋œ ๋ฐ์ดํ„ฐ ๊ฐœ์ˆ˜๋ฅผ int ํƒ€์ž…์œผ๋กœ ๋ฐ›์•„์˜จ๋‹ค. // ex) ๊ฒŒ์‹œ๊ธ€ 1๊ฐœ ์ ์šฉ ์„ฑ๊ณต ์‹œ, result : 1 // ์‹คํŒจ ์‹œ, result : 0 } catch (SQLException e) { System.err.println("๊ฒŒ์‹œ๊ธ€ ์‚ญ์ œ ์‹œ, ์˜ˆ์™ธ ๋ฐœ์ƒ"); e.printStackTrace(); } return result; } }
Java
๋ณต์‚ฌ

JDBConnection.java

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class JDBConnection { public Connection con; // ์—ฐ๊ฒฐ๋œ ๋“œ๋ผ์ด๋ฒ„์— SQL์„ ์š”์ฒญํ•  ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜๋Š” ํด๋ž˜์Šค public Statement stmt; // SQL ์‹คํ–‰ ์š”์ฒญ์„ ํ•˜๋Š” ํด๋ž˜์Šค public PreparedStatement psmt; // Statement ์—์„œ ? ํŒŒ๋ผ๋ฏธํ„ฐ ํ™•์žฅ๊ธฐ๋Šฅ์„ ์ถ”๊ฐ€๋กœ ์ œ๊ณตํ•˜๋Š” ํด๋ž˜์Šค public ResultSet rs; // SQL ์‹คํ–‰ ๊ฒฐ๊ณผ๋ฅผ ๋ฐ›์•„์˜ค๋Š” ํด๋ž˜์Šค // ๊ธฐ๋ณธ ์ƒ์„ฑ์ž public JDBConnection() { // JDBC ๋“œ๋ผ์ด๋ฒ„ ๋กœ๋“œ // Oracle try { // ojdbc8.jar ๋“œ๋ผ์ด๋ฒ„์˜ ํด๋ž˜์Šค๋ฅผ ๋กœ๋“œํ•œ๋‹ค. Class.forName("oracle.jdbc.OracleDriver"); // DB์— ์—ฐ๊ฒฐ // - ์—ฐ๊ฒฐ์— ํ•„์š”ํ•œ ์ •๋ณด : URL, id, pw // URL : jdbc:mysql://๋„๋ฉ”์ธ:[PORT]/[์Šคํ‚ค๋งˆ]?์˜ต์…˜ํŒŒ๋ผ๋ฏธํ„ฐ // * ๋‚ด PC์˜ IP์ฃผ์†Œ : localhost : 127.0.0.1 // * 1521 : Oracle ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ๊ธฐ๋ณธ ํฌํŠธ // * SID // * 11g : xe // * 12c~ : orcl String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String id = "aloha"; String pw = "123456"; // ์ž๋ฐ” ํ”„๋กœ๊ทธ๋žจ์—์„œ JDBC ๋“œ๋ผ์ด๋ฒ„๋ฅผ ์—ฐ๊ฒฐ์‹œ์ผœ์ฃผ๋Š” ํด๋ž˜์Šค // getConnection() ๋ฉ”์†Œ๋“œ๋กœ DB์— ์—ฐ๊ฒฐ ์š”์ฒญํ•˜๊ณ  ์ƒ์„ฑ๋œ Connection ๊ฐ์ฒด๋ฅผ ๋ฐ˜ํ™˜๋ฐ›๋Š”๋‹ค. con = DriverManager.getConnection(url, id, pw); System.out.println("DB ์—ฐ๊ฒฐ ์„ฑ๊ณต"); } catch (Exception e) { System.err.println("DB ์—ฐ๊ฒฐ ์‹คํŒจ"); e.printStackTrace(); } } }
Java
๋ณต์‚ฌ

Main.java

package ๊ฒŒ์‹œํŒ; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.Scanner; import ๊ฒŒ์‹œํŒ.DTO.Board; import ๊ฒŒ์‹œํŒ.Service.BoardService; import ๊ฒŒ์‹œํŒ.Service.BoardServiceImpl; public class Main { static Scanner sc = new Scanner(System.in); // ์ž…๋ ฅ ๊ฐ์ฒด static List<Board> boardList = null; // ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก static BoardService boardService = new BoardServiceImpl(); // ๋น„์ฆˆ๋‹ˆ์Šค ๋กœ์ง ๊ฐ์ฒด /** * ๋ฉ”๋‰ดํŒ */ public static void menu() { System.out.println(":::::::::: ๊ฒŒ์‹œํŒ ::::::::::"); System.out.println("1. ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก"); System.out.println("2. ๊ฒŒ์‹œ๊ธ€ ์กฐํšŒ"); System.out.println("3. ๊ฒŒ์‹œ๊ธ€ ๋“ฑ๋ก"); System.out.println("4. ๊ฒŒ์‹œ๊ธ€ ์ˆ˜์ •"); System.out.println("5. ๊ฒŒ์‹œ๊ธ€ ์‚ญ์ œ"); System.out.println("0. ํ”„๋กœ๊ทธ๋žจ ์ข…๋ฃŒ"); System.out.print(":::::::::: ๋ฒˆํ˜ธ ์ž…๋ ฅ : "); } /** * ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก */ public static void list() { System.out.println(":::::::::: ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ::::::::::"); // ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ๋ฐ์ดํ„ฐ ์š”์ฒญ boardList = boardService.list(); printAll(boardList); } /** * ๊ธ€ ๋ชฉ๋ก ์ „์ฒด ์ถœ๋ ฅ * @param list */ private static void printAll(List<Board> list) { // ๊ธ€ ๋ชฉ๋ก์ด ์กด์žฌํ•˜๋Š”์ง€ ํ™•์ธ if( list == null || list.isEmpty() ) { System.err.println("์กฐํšŒ๋œ ๊ธ€์ด ์—†์Šต๋‹ˆ๋‹ค."); return; } // ๊ธ€ ๋ชฉ๋ก ์ถœ๋ ฅ for (Board board : list) { print(board); } } /** * ๊ฒŒ์‹œ๊ธ€ ์ถœ๋ ฅ * @param board */ private static void print(Board board) { if( board == null ) { System.err.println("์กฐํšŒํ•  ์ˆ˜ ์—†๋Š” ๊ฒŒ์‹œ๊ธ€์ž…๋‹ˆ๋‹ค."); return; } int no = board.getNo(); String title = board.getTitle(); String writer = board.getWriter(); String content = board.getContent(); Date regDate = board.getRegDate(); Date updDate = board.getUpdDate(); // ๋‚ ์งœ ํฌ๋งท SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); String reg = sdf.format(regDate); String upd = sdf.format(updDate); System.out.println("::::::::::::::::::::::::::::::::::::::::"); System.out.println("โ˜… ๊ธ€๋ฒˆํ˜ธ : " + no); System.out.println("โ˜… ์ œ๋ชฉ : " + title); System.out.println("โ˜… ์ž‘์„ฑ์ž : " + writer); System.out.println("----------------------------------------"); System.out.println(" " + content); System.out.println("โ˜… ๋“ฑ๋ก์ผ์ž : " + reg); System.out.println("โ˜… ์ˆ˜์ •์ผ์ž : " + upd); System.out.println("::::::::::::::::::::::::::::::::::::::::"); System.out.println(); } /** * ๊ฒŒ์‹œ๊ธ€ ์กฐํšŒ */ public static void select() { System.out.println(":::::::::: ๊ฒŒ์‹œ๊ธ€ ์กฐํšŒ :::::::::::"); System.out.print("๊ธ€ ๋ฒˆํ˜ธ : "); int no = sc.nextInt(); sc.nextLine(); // ๊ธ€๋ฒˆํ˜ธ(no)๋ฅผ ์ „๋‹ฌํ•˜์—ฌ ๊ฒŒ์‹œ๊ธ€ ์ •๋ณด ๋ฐ์ดํ„ฐ ์š”์ฒญ Board board = boardService.select(no); // ๊ฒŒ์‹œ๊ธ€ ์ •๋ณด ์ถœ๋ ฅ print(board); } /** * ๊ฒŒ์‹œ๊ธ€ ๋“ฑ๋ก */ public static void insert() { System.out.println(":::::::::: ๊ฒŒ์‹œ๊ธ€ ๋“ฑ๋ก ::::::::::"); Board board = input(); // ๊ฒŒ์‹œ๊ธ€ ๋“ฑ๋ก ์š”์ฒญ int result = boardService.insert(board); if( result > 0 ) { System.out.println("โ˜… ๊ฒŒ์‹œ๊ธ€์ด ๋“ฑ๋ก๋˜์—ˆ์Šต๋‹ˆ๋‹ค."); } else { System.err.println("โ˜… ๊ฒŒ์‹œ๊ธ€ ๋“ฑ๋ก์— ์‹คํŒจํ•˜์˜€์Šต๋‹ˆ๋‹ค."); } } /** * ๊ฒŒ์‹œ๊ธ€ ์ •๋ณด ์ž…๋ ฅ * @return */ private static Board input() { System.out.print("โ˜… ์ œ๋ชฉ : "); String title = sc.nextLine(); System.out.print("โ˜… ์ž‘์„ฑ์ž : "); String writer = sc.nextLine(); System.out.print("โ˜… ๋‚ด์šฉ : "); String content = sc.nextLine(); Board board = new Board(title, writer, content); return board; } /** * ๊ฒŒ์‹œ๊ธ€ ์ˆ˜์ • */ public static void update() { System.out.println(":::::::::: ๊ฒŒ์‹œ๊ธ€ ์ˆ˜์ • ::::::::::"); System.out.print("๊ฒŒ์‹œ๊ธ€ ๋ฒˆํ˜ธ : "); int no = sc.nextInt(); sc.nextLine(); Board board = input(); board.setNo(no); // ๊ฒŒ์‹œ๊ธ€ ์ˆ˜์ • ์š”์ฒญ int result = boardService.update(board); if( result > 0 ) { System.out.println("โ˜… ๊ฒŒ์‹œ๊ธ€์ด ์ˆ˜์ •๋˜์—ˆ์Šต๋‹ˆ๋‹ค."); } else { System.err.println("โ˜… ๊ฒŒ์‹œ๊ธ€ ์ˆ˜์ •์— ์‹คํŒจํ•˜์˜€์Šต๋‹ˆ๋‹ค."); } } /** * ๊ฒŒ์‹œ๊ธ€ ์‚ญ์ œ */ public static void delete() { System.out.println(":::::::::: ๊ฒŒ์‹œ๊ธ€ ์‚ญ์ œ :::::::::::"); System.out.print("๊ฒŒ์‹œ๊ธ€ ๋ฒˆํ˜ธ : "); int no = sc.nextInt(); sc.nextLine(); // ๊ฒŒ์‹œ๊ธ€ ์‚ญ์ œ ์š”์ฒญ int result = boardService.delete(no); if( result > 0 ) { System.out.println("โ˜… ๊ฒŒ์‹œ๊ธ€์„ ์‚ญ์ œํ•˜์˜€์Šต๋‹ˆ๋‹ค."); } else { System.err.println("โ˜… ๊ฒŒ์‹œ๊ธ€ ์‚ญ์ œ์— ์‹คํŒจํ•˜์˜€์Šต๋‹ˆ๋‹ค."); } } public static void main(String[] args) { int menuNo = 0; do { // ๋ฉ”๋‰ดํŒ ์ถœ๋ ฅ menu(); // ๋ฉ”๋‰ด ๋ฒˆํ˜ธ ์ž…๋ ฅ menuNo = sc.nextInt(); sc.nextLine(); // 0 -> ํ”„๋กœ๊ทธ๋žจ ์ข…๋ฃŒ if( menuNo == 0 ) break; // ๋ฉ”๋‰ด ์„ ํƒ switch (menuNo) { case 1: list(); // ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก break; case 2: select(); // ๊ฒŒ์‹œ๊ธ€ ์กฐํšŒ break; case 3: insert(); // ๊ฒŒ์‹œ๊ธ€ ๋“ฑ๋ก break; case 4: update(); // ๊ฒŒ์‹œ๊ธ€ ์ˆ˜์ • break; case 5: delete(); // ๊ฒŒ์‹œ๊ธ€ ์‚ญ์ œ break; } } while (menuNo != 0); System.out.println("ํ”„๋กœ๊ทธ๋žจ์„ ์ข…๋ฃŒํ•ฉ๋‹ˆ๋‹ค..."); } }
Java
๋ณต์‚ฌ