1.
๋ฐ์ดํฐ๋ฒ ์ด์ค ๊ตฌ์ถ
2.
ํ
์ด๋ธ ์์ฑ
3.
์๋ฐ ํ๋ก์ ํธ ์์ฑ
4.
MySQL ๋๋ผ์ด๋ฒ ๋ค์ด๋ก๋
5.
MySQL ๋๋ผ์ด๋ฒ ์ถ๊ฐํ๊ธฐ
6.
Java ํ๋ก๊ทธ๋จ์์ MySQL DB ์ฐ๊ฒฐ
a.
JDBConnection.java
7.
๊ฒ์ํ ํ๋ก๊ทธ๋จ ์์ฑ
a.
DTO
i.
Board.java
b.
Service
i.
BoardService.java
ii.
BoardServiceImpl.java
c.
DAO
i.
BoardDAO.java
JDBConnection.java
d.
Main.java
๋ฐ์ดํฐ๋ฒ ์ด์ค ๊ตฌ์ถ
ํ ์ด๋ธ ์์ฑ
-- ๊ฒ์ํ ํ
์ด๋ธ ์์ฑ
CREATE TABLE `board` (
`no` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '๊ฒ์๊ธ ๋ฒํธ',
`title` VARCHAR(100) NOT NULL COMMENT '์ ๋ชฉ',
`writer` VARCHAR(100) NOT NULL COMMENT '์์ฑ์',
`content` TEXT NULL COMMENT '๋ด์ฉ',
`created_at` TIMESTAMP NOT NULL DEFAULT now() COMMENT '๋ฑ๋ก์ผ์',
`updated_at` TIMESTAMP NOT NULL DEFAULT now() COMMENT '์์ ์ผ์'
) COMMENT = '๊ฒ์ํ';
SQL
๋ณต์ฌ
SQL CRUD (๋ฑ๋ก, ์กฐํ, ์์ , ์ญ์ ) ์์
-- ๋ฑ๋ก
INSERT INTO board (title, writer, content)
VALUES ('๊ฒ์๊ธ ์ ๋ชฉ', 'ALOHA', '๋ฐ์ดํฐ๋ฒ ์ด์ค');
-- ์ ์ฒด ์กฐํ (๋ชฉ๋ก)
SELECT *
FROM board;
-- ์กฐํ
SELECT *
FROM board
WHERE no = 1;
;
-- ์์
UPDATE board
SET title = '์์ '
,writer = '์์ '
,content = '์์ '
,updated_at = now()
WHERE no = 1;
-- ์ญ์
DELETE FROM board WHERE no = 1;
SQL
๋ณต์ฌ
์๋ฐ ํ๋ก์ ํธ ์์ฑ
์ดํด๋ฆฝ์ค์์ ์๋ฐ ํ๋ก์ ํธ๋ฅผ ์์ฑํฉ๋๋ค.
1.
File
2.
New
3.
Java Project
ํ๋ก์ ํธ ์ด๋ฆ์ ์ง์ ํฉ๋๋ค.
MySQL ๋๋ผ์ด๋ฒ ๋ค์ด๋ก๋
MySQL ๋ค์ด๋ก๋ ์ฌ์ดํธ์์ JDBC (Connector/J) ๋๋ผ์ด๋ฒ๋ฅผ ๋ค์ด๋ก๋ ํฉ๋๋ค.
Connector/J ๋งํฌ๋ฅผ ํด๋ฆญํฉ๋๋ค.
Platform Independent (์ด์์ฒด์ ๋
๋ฆฝํ) ์ ํํฉ๋๋ค.
MySQL ๋๋ผ์ด๋ฒ ์ถ๊ฐํ๊ธฐ
์ดํด๋ฆฝ์ค์์ ํ๋ก์ ํธ์ JDBC ๋๋ผ์ด๋ฒ๋ฅผ ์ถ๊ฐํฉ๋๋ค.
1.
ํ๋ก์ ํธ ์ฐํด๋ฆญ
2.
Build Path
3.
Configure Build Pathโฆ
Modulepath ๋ฅผ ์ ํํ ์ํ์์, [Add Library] ๋ฒํผ์ ํด๋ฆญํ์ฌ ๋ผ์ด๋ธ๋ฌ๋ฆฌ๋ฅผ ์ถ๊ฐํฉ๋๋ค.
User Library ๋ฅผ ์ ํํ๊ณ Next ๋ฅผ ๋๋ฌ, ์ฌ์ฉ์ ์ง์ ๋ผ์ด๋ธ๋ฌ๋ฆฌ ์ค์ ์ ํฉ๋๋ค.
[User Librariesโฆ] ๋ฅผ ํด๋ฆญํ์ฌ ์๋ก์ด ๋ผ์ด๋ธ๋ฌ๋ฆฌ๋ฅผ ์ถ๊ฐํ๊ฑฐ๋, ๊ธฐ์กดํด ์ง์ ํ ๋ผ์ด๋ธ๋ฌ๋ฆฌ๋ฅด ๊ฐ์ ธ์ต๋๋ค.
๊ธฐ์กด์ ์ถ๊ฐ๋ ๋ผ์ด๋ธ๋ฌ๋ฆฌ๊ฐ ์์ผ๋ฉด, [New] ๋ฒํผ์ ๋๋ฌ ๋ผ์ด๋ธ๋ฌ๋ฆฌ๋ฅผ ์ถ๊ฐํด์ค๋๋ค.
User Libaray ์ด๋ฆ์ ์ง์ ํ์ฌ ๋ผ์ด๋ธ๋ฌ๋ฆฌ๋ฅผ ์์ฑํฉ๋๋ค.
[Add JARsโฆ] ๋ฒํผ์ ๋๋ฌ์, ์์ฑํ ๋ผ์ด๋ธ๋ฌ๋ฆฌ ์์ JDBC ๋๋ผ์ด๋ฒ ๋ผ์ด๋ธ๋ฌ๋ฆฌ๋ฅผ ์ถ๊ฐํฉ๋๋ค.
ํ๋ก์ ํธ ๋ด์ ๋ผ์ด๋ธ๋ฌ๋ฆฌ ํ์ผ์ ๋ณด๊ดํ์ฌ ์ฐธ์กฐํ๋๋ก ํ๋ฉด, ๋ค๋ฅธ ํ๊ฒฝ์ผ๋ก ํ๋ก์ ํธ๋ฅผ ๊ฐ์ ธ๊ฐ์ ๋, ์ง์ ๊ฒฝ๋ก๊ฐ ๋ฌ๋ผ์ ธ ๋ผ์ด๋ธ๋ฌ๋ฆฌ๋ฅผ ์ฐธ์กฐํ์ง ๋ชปํ๋ ํ์์ ๋ฐฉ์งํ ์ ์์ต๋๋ค.
ํ๋ก์ ํธ ์์ lib ํด๋์ ์ ์ฅํ JDBC ๋๋ผ์ด๋ฒ๋ฅผ ์ ํํ๊ณ OK ๋ฅผ ๋๋ฆ
๋๋ค.
[Apply and Close] ๋ฒํผ์ ํด๋ฆญํ์ฌ ์ ์ฉํฉ๋๋ค.
[Finish] ํด๋ฆญํฉ๋๋ค.
[Apply and Close] ๋ฒํผ์ ํด๋ฆญํ์ฌ ์ ์ฉํฉ๋๋ค.
Java ํ๋ก๊ทธ๋จ์์ MySQL 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 ๋๋ผ์ด๋ฒ ๋ก๋
// MySQL
try {
// mysql-connector-j.xxx.jar ๋๋ผ์ด๋ฒ์ ํด๋์ค๋ฅผ ๋ก๋ํ๋ค.
Class.forName("com.mysql.cj.jdbc.Driver");
// DB์ ์ฐ๊ฒฐ
// - ์ฐ๊ฒฐ์ ํ์ํ ์ ๋ณด : URL, id, pw
// URL : jdbc:mysql://๋๋ฉ์ธ:[PORT]/[์คํค๋ง]?์ต์
ํ๋ผ๋ฏธํฐ
// * ๋ด PC์ IP์ฃผ์ : localhost : 127.0.0.1
// * 3306 : MySQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๊ธฐ๋ณธ ํฌํธ
String url = "jdbc:mysql://127.0.0.1:3306/aloha?serverTimezone=Asia/Seoul&allowPublicKeyRetrieval=true&useSSL=false";
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();
}
}
public static void main(String[] args) {
JDBConnection jdbc = new JDBConnection();
}
}
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
* - ๋ฑ๋ก์ผ์ createdAt red_date
* - ์์ ์ผ์ updatedAt updated_at
*/
@Data
@AllArgsConstructor
public class Board {
private int no;
private String title;
private String writer;
private String content;
private Date createdAt;
private Date updatedAt;
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.setCreatedAt( rs.getTimestamp("created_at") );
board.setUpdatedAt( rs.getTimestamp("updated_at") );
// ๊ฒ์๊ธ ๋ชฉ๋ก ์ถ๊ฐ
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.setCreatedAt( rs.getTimestamp("created_at") );
board.setUpdatedAt( rs.getTimestamp("updated_at") );
}
} 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 (title, writer, content) "
+ " VALUES( ?, ?, ? ) ";
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 = ?"
+ " ,updated_at = now() "
+ " 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
package ๊ฒ์ํ.DAO;
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 ๋๋ผ์ด๋ฒ ๋ก๋
// MySQL
try {
// mysql-connector-j.xxx.jar ๋๋ผ์ด๋ฒ์ ํด๋์ค๋ฅผ ๋ก๋ํ๋ค.
Class.forName("com.mysql.cj.jdbc.Driver");
// DB์ ์ฐ๊ฒฐ
// - ์ฐ๊ฒฐ์ ํ์ํ ์ ๋ณด : URL, id, pw
// URL : jdbc:mysql://๋๋ฉ์ธ:[PORT]/[์คํค๋ง]?์ต์
ํ๋ผ๋ฏธํฐ
// * ๋ด PC์ IP์ฃผ์ : localhost : 127.0.0.1
// * 3306 : MySQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๊ธฐ๋ณธ ํฌํธ
// * ~/aloha : ์คํค๋ง
String url = "jdbc:mysql://127.0.0.1:3306/aloha?serverTimezone=Asia/Seoul&allowPublicKeyRetrieval=true&useSSL=false";
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 createdAt = board.getCreatedAt();
Date updatedAt = board.getUpdatedAt();
// ๋ ์ง ํฌ๋งท
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
String reg = sdf.format(createdAt);
String upd = sdf.format(updatedAt);
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
๋ณต์ฌ