위 글은 김성박선생님의 자바 강의를 바탕으로 쓰여졌습니다.
더 자세한 내용은 아래 링크를 통해 알아보실 수 있습니다.
1) DBMS접속(Connection, DriverManager라는 클래스)
2) autocommit을 true, false로 할 것인지 결정
3) SQL 준비 (Connection, PreparedStatement)
4) SQL 실행 (PreparedStatement, ResultSet(Select))
5) DBMS에서 데이터를 읽어온다. (select문일 경우, ResultSet)
6) ResultSet close();
7) PreparedStatement close
8) DMBS 접속 close
public class DBUtil {
public static Connection getConnection() throws SQLException {
Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels","root","gkseogh1!23");
return conn;
}
// insert, update, delete - Connection, PreparedStatement
public static void close(PreparedStatement ps, Connection conn) throws SQLException{
ps.close();
conn.close();
}
// select - Connection, PreparedStatement, ResultSet
public static void close(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException{
rs.close();
close(ps,conn);
}
}
package com.example.jdbcexam01;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ConnectionTest02 {
public static void main(String[] args) {
try {
Connection conn = DBUtil.getConnection();
String sql = "select firstname, lastname from employees";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String firstname = rs.getString("firstname");
System.out.println(firstname);
}
DBUtil.close(rs, ps, conn);
}catch(SQLException ex){
System.out.println("DB error:" + ex.getMessage());
}
}
}
CREATE TABLE board(
id INT(11) AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
read_count INT(11) NOT NULL default 0,
created DATETIME default now(),
CONSTRAINT board_PK PRIMARY KEY(id)
);
글을 조회할때 read_count가 1씩 증가하는 쿼리문
SELECT id, name, title, count, read_count, created from where id = 1;
update board
set read_count = read_count + 1
where id = 1;
위와 같은 쿼리문을 실행시키면 read_count가 1씩 증가한다.
위와 같은 타입의 board클래스를 만들어보자.
package com.example.jdbcexam01;
import java.sql.Date;
public class Board {
private long id;
private String name;
private String title;
private String content;
private long readCount;
private Date created;
}
package com.example.jdbcexam01;
import java.sql.Date;
public class Board {
private long id;
private String name;
private String title;
private String content;
public Board(){
}
public Board(String name, String title, String content) {
this.name = name;
this.title = title;
this.content = content;
}
public Board(long id, String name, String title, String content, long readCount, Date created) {
this.id = id;
this.name = name;
this.title = title;
this.content = content;
this.readCount = readCount;
this.created = created;
}
private long readCount;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public long getReadCount() {
return readCount;
}
public void setReadCount(long readCount) {
this.readCount = readCount;
}
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
private Date created;
}
public class BoardDao List<Board> getBoards(int start, int size){
List<Board> list = new ArrayList<>();
try{
Connection conn = DBUtil.getConnection(); // DB연결
String sql = "select id, name, title, read_count, created from board order by created desc limit ?,?"; // 정해져있지 않은 값.
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, start);
ps.setInt(2,size);
ResultSet rs = ps.executeQuery(); // SQL실행(select문)
while(rs.next()){
// 한건의 데이터 로우를 읽어온다.
long id = rs.getLong("id");
String name = rs.getString("name");
String title = rs.getString("title");
int readcount = rs.getInt("read_count");
Date created = rs.getDate("created");
Board board = new Board(id, name, title, null, readcount, created);
list.add(board);
}
DBUtil.close(rs, ps , conn);
}catch (SQLException ex){
System.out.println("db error message"+ex.getMessage());
}
return list;
}
public int addBoard(Board board){
int updateCount = 0;
try{
Connection conn = DBUtil.getConnection(); // DB연결
String sql = "insert into board(name, title, content) values(?,?,?)"; // 정해져있지 않은 값.
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, board.getName());
ps.setString(2,board.getTitle());
ps.setString(3,board.getContent());
updateCount = ps.executeUpdate(); //executeU
DBUtil.close(ps , conn);
}catch (SQLException ex){
System.out.println("db error message"+ex.getMessage());
}
return updateCount;
}
public Board getBoard(long id){
Board board = null;
Connection conn = null;
try{
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
String sql = "select name, title, content, read_count, created from board where id = ?"; //
PreparedStatement ps = conn.prepareStatement(sql); //
ps.setLong(1, id);
ResultSet rs = ps.executeQuery(); // SQL(select)
while(rs.next()){
String name = rs.getString("name");
String title = rs.getString("title");
String content = rs.getString("content");
int readcount = rs.getInt("read_count");
Date created = rs.getDate("created");
board = new Board(id, name, title, content, readcount, created);
}
ps.close();
String sql2 = "update board\n" +
"set read_count = read_count+ 1\n" +
"where id = ?";
ps = conn.prepareStatement(sql2);
ps.setLong(1,id);
ps.executeUpdate();
conn.commit();
DBUtil.close(rs, ps , conn);
}catch (SQLException ex){
try {
conn.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
System.out.println("db error message"+ex.getMessage());
}
return board;
}
package com.example.jdbcexam01;
import java.util.List;
public class BoardTest {
public static void main(String[] args) {
BoardDao boardDao = new BoardDao();
List<Board> boards = boardDao.getBoards(0, 30);
for(Board board : boards){
System.out.println(board);
}
}
}
package com.example.jdbcexam01;
public class BoardTest3 {
public static void main(String[] args) {
BoardDao boardDao = new BoardDao();
Board board = boardDao.getBoard(3);
System.out.println(board);
}
}
package com.example.jdbcexam01;
public class BoardTest3 {
public static void main(String[] args) {
BoardDao boardDao = new BoardDao();
Board board = boardDao.getBoard(3);
System.out.println(board);
}
}