디렉터리 구조
dto/board/Board.java
public class Board {
private int _id;
private String subject;
private String content;
private String writer;
private int hit;
private String wdate;
public int get_id() {
return _id;
}
public void set_id(int _id) {
this._id = _id;
}
public String getSubject() {
return subject;
}
public void setSubject(String subject) {
this.subject = subject;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public int getHit() {
return hit;
}
public void setHit(int hit) {
this.hit = hit;
}
public String getWdate() {
return wdate;
}
public void setWdate(String wdate) {
this.wdate = wdate;
}
}
dao/util/JDBCUtil.java
public clas JDBCUtil {
public static Connection getConn() {
String DBFileName = "MyBoard.db";
Connection conn = null;
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:" + DBFileName);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn, PreparedStatement ps) {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ps = null;
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
}
public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
close(conn, ps);
}
}
dao/board/BoardDAOJDBC.java // 코딩 후 BoardDAO라는 interface를 만들것임
public class BoardDAOJDBC implements BoardDAO{
private final String BOARD_INSERT = "INSERT INTO board(subject,content,writer) VALUES(?,?,?);";
private final String BOARD_DELETE = "DELETE FROM board WHERE _id = ?;";
private final String BOARD_UPDATE = "UPDATE board SET subject=?, content = ?,writer =? WHERE _id = ?;";
private final String BOARD_SELECTE_ONE = "SELECT * FROM board WHERE _id = ?;";
private final String BOARD_SELECTE_ALL = "SELECT * FROM board ORDER BY _id DESC;";
public int insert(Board vo) {
Connection conn = null;
PreparedStatement ps = null;
int r = -1;
conn = JDBCUtil.getConn();
try {
ps = conn.prepareStatement(BOARD_INSERT);
ps.setString(1, vo.getSubject());
ps.setString(2, vo.getContent());
ps.setString(3, vo.getWriter());
r = ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return r;
}
public int delete(Board vo) {
Connection conn = null;
PreparedStatement ps = null;
conn = JDBCUtil.getConn();
try {
ps = conn.prepareStatement(BOARD_DELETE);
ps.setInt(1, vo.get_id());
int r = ps.executeUpdate();
return r;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return -1;
}
public int update(Board vo) {
Connection conn = null;
PreparedStatement ps = null;
conn = JDBCUtil.getConn();
try {
ps = conn.prepareStatement(BOARD_UPDATE);
ps.setString(1, vo.getSubject());
ps.setString(2, vo.getContent());
ps.setString(3, vo.getWriter());
ps.setInt(4, vo.get_id());
int r = ps.executeUpdate();
return r;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return -1;
}
public Board getOne(Board vo) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Board gvo = null;
conn = JDBCUtil.getConn();
try {
ps = conn.prepareStatement(BOARD_SELECTE_ONE);
ps.setInt(1, vo.get_id());
rs = ps.executeQuery();
if (rs.next()) {
gvo = new Board();
gvo.set_id(rs.getInt("_id"));
gvo.setSubject(rs.getString("subject"));
gvo.setContent(rs.getString("content"));
gvo.setWriter(rs.getString("writer"));
gvo.setHit(rs.getInt("hit"));
gvo.setWdate(rs.getString("wdate"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return gvo;
}
public List<Board> getAll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Board> list = new ArrayList<Board>();
try {
conn = JDBCUtil.getConn();
ps = conn.prepareStatement(BOARD_SELECTE_ALL);
rs = ps.executeQuery();
while (rs.next()) {
Board gvo = new Board();
gvo.set_id(rs.getInt("_id"));
gvo.setSubject(rs.getString("subject"));
gvo.setContent(rs.getString("content"));
gvo.setWriter(rs.getString("writer"));
gvo.setHit(rs.getInt("hit"));
gvo.setWdate(rs.getString("wdate"));
list.add(gvo);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return list;
}
}
DAOJDBC 클래스를 DAO interface로 만들기
BoardDAOJDBC.java 작업창에 오른족 마우스 – Refactor – Extract Interface
Interface name : BoardDAO
Members to declare in the interface – 전체 선택 – OK
서비스를 위해 메타태그를 달아야 한다
@Repository
클래스 상단에 @Repository
@Override
매서드 상단에 Override
@Repository
public class BoardDAOJDBC implements BoardDAO{
private final String BOARD_INSERT = "INSERT INTO board(subject,content,writer) VALUES(?,?,?);";
private final String BOARD_DELETE = "DELETE FROM board WHERE _id = ?;";
private final String BOARD_UPDATE = "UPDATE board SET subject=?, content = ?,writer =? WHERE _id = ?;";
private final String BOARD_SELECTE_ONE = "SELECT * FROM board WHERE _id = ?;";
private final String BOARD_SELECTE_ALL = "SELECT * FROM board ORDER BY _id DESC;";
@Override
public int insert(Board vo) {
Connection conn = null;
PreparedStatement ps = null;
int r = -1;
conn = JDBCUtil.getConn();
try {
ps = conn.prepareStatement(BOARD_INSERT);
ps.setString(1, vo.getSubject());
ps.setString(2, vo.getContent());
ps.setString(3, vo.getWriter());
r = ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return r;
}
@Override
public int delete(Board vo) {
Connection conn = null;
PreparedStatement ps = null;
conn = JDBCUtil.getConn();
try {
ps = conn.prepareStatement(BOARD_DELETE);
ps.setInt(1, vo.get_id());
int r = ps.executeUpdate();
return r;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return -1;
}
@Override
public int update(Board vo) {
Connection conn = null;
PreparedStatement ps = null;
conn = JDBCUtil.getConn();
try {
ps = conn.prepareStatement(BOARD_UPDATE);
ps.setString(1, vo.getSubject());
ps.setString(2, vo.getContent());
ps.setString(3, vo.getWriter());
ps.setInt(4, vo.get_id());
int r = ps.executeUpdate();
return r;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return -1;
}
@Override
public Board getOne(Board vo) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Board gvo = null;
conn = JDBCUtil.getConn();
try {
ps = conn.prepareStatement(BOARD_SELECTE_ONE);
ps.setInt(1, vo.get_id());
rs = ps.executeQuery();
if (rs.next()) {
gvo = new Board();
gvo.set_id(rs.getInt("_id"));
gvo.setSubject(rs.getString("subject"));
gvo.setContent(rs.getString("content"));
gvo.setWriter(rs.getString("writer"));
gvo.setHit(rs.getInt("hit"));
gvo.setWdate(rs.getString("wdate"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return gvo;
}
@Override
public List<Board> getAll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Board> list = new ArrayList<Board>();
try {
conn = JDBCUtil.getConn();
ps = conn.prepareStatement(BOARD_SELECTE_ALL);
rs = ps.executeQuery();
while (rs.next()) {
Board gvo = new Board();
gvo.set_id(rs.getInt("_id"));
gvo.setSubject(rs.getString("subject"));
gvo.setContent(rs.getString("content"));
gvo.setWriter(rs.getString("writer"));
gvo.setHit(rs.getInt("hit"));
gvo.setWdate(rs.getString("wdate"));
list.add(gvo);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.close(conn, ps);
}
return list;
}
}f