SQLite query // SQLite를 사용했다
CREATE TABLE "board" (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT,
"subject" TEXT,
"content" TEXT,
"writer" TEXT,
"hit" INTEGER DEFAULT 0,
"wdate" TEXT DEFAULT CURRENT_TIMESTAMP
)
BoardDTO.java // 데이터를 담을 클래스
public class BoardDTO {
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;
}
}
JDBCUtil.java // DB 접속, 종료와 관련된 클래스
db파일은 IDE와 EE 동일한 위치에 지정해주어야 한다
public class JDBCUtil {
public static Connection getConn() {
String DBFileName = "DB 위치";
Connection conn = null;
try {
Class.forName("org.sqlite.JDBC");
conn =DriverManager.getConnection("jdbc:sqlite:"+DBFileName);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn, PreparedStatement ps) {
if(ps!=null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
ps = null;
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
conn=null;
}
}
}
public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
rs=null;
}
}
close(conn, ps);
}
}
BoardDAO.java // DB에서 데이터를 INSERT DELETE UPDATE SELECT 해주는 컨트롤러이다
public class BoardDAO {
public int insert(BoardDTO dto) {
//db 연결
Connection conn = JDBCUtil.getConn();
PreparedStatement ps = null;
String sql = "INSERT INTO board(subject,content,writer) VALUES(?,?,?);";
try {
ps = conn.prepareStatement(sql);
ps.setString(1,dto.getSubject());
ps.setString(2,dto.getContent());
ps.setString(3,dto.getWriter());
int r = ps.executeUpdate();
return r;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil.close(conn, ps);
}
return 0;
}
public int delete(BoardDTO dto) {
//db 연결
Connection conn = JDBCUtil.getConn();
PreparedStatement ps = null;
String sql = "DELETE FROM board WHERE subject = ?;";
try {
ps = conn.prepareStatement(sql);
ps.setString(1,dto.getSubject());
int r = ps.executeUpdate();
return r;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil.close(conn, ps);
}
return 0;
}
public int update(BoardDTO dto) {
//db 연결
Connection conn = JDBCUtil.getConn();
PreparedStatement ps = null;
String sql = "UPDATE board SET content = ?,writer =? WHERE subject = ?;";
try {
ps = conn.prepareStatement(sql);
ps.setString(1,dto.getContent());
ps.setString(2, dto.getWriter());
ps.setString(3,dto.getSubject());
int r = ps.executeUpdate();
return r;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
JDBCUtil.close(conn, ps);
}
return 0;
}
public BoardDTO getOne(BoardDTO dto) {
Connection conn = JDBCUtil.getConn();
PreparedStatement ps = null;
ResultSet rs = null;
BoardDTO gdto = null;
String sql = "SELECT * FROM board WHERE subject = ?;";
try {
ps = conn.prepareStatement(sql);
ps.setString(1,dto.getSubject());
rs= ps.executeQuery();
if(rs.next()) {
gdto = new BoardDTO();
gdto.setSubject(rs.getString("subject"));
gdto.setContent(rs.getString("content"));
gdto.setWriter(rs.getString("writer"));
System.out.println(gdto.getSubject());
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
JDBCUtil.close(rs,ps,conn);
}
return gdto;
}
public List<BoardDTO> getAll() {
Connection conn = JDBCUtil.getConn();
PreparedStatement ps = null;
ResultSet rs = null;
BoardDTO gdto = null;
List<BoardDTO> list = new ArrayList<BoardDTO>();
String sql = "SELECT * FROM board;";
try {
ps = conn.prepareStatement(sql);
rs= ps.executeQuery();
while(rs.next()) {
gdto = new BoardDTO();
gdto.setSubject(rs.getString("subject"));
gdto.setContent(rs.getString("content"));
gdto.setWriter(rs.getString("writer"));
list.add(gdto);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
JDBCUtil.close(rs,ps,conn);
}
return list;
}
}
BoardUI.java // UI 클래스
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JButton;
import javax.swing.JFrame;
public class BoardUI extends JFrame {
BoardDAO dao = new BoardDAO();
BoardDTO dto = new BoardDTO();
public BoardUI() {
this.setTitle("게시판");
this.setSize(800, 600);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
this.setLayout(null);
JButton btnSave = new JButton("저장");
btnSave.setBounds(10, 10, 100, 30);
this.add(btnSave);
btnSave.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
dto.setSubject("반갑습니다.");
dto.setContent("안녕하세요 잘 지내고 있어요");
dto.setWriter("이평화");
dao.insert(dto);
}
});
JButton btnDelete = new JButton("삭제");
btnDelete.setBounds(10, 40, 100, 30);
this.add(btnDelete);
btnDelete.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
dao.delete(dto);
}
});
JButton btnUpdate = new JButton("수정");
btnUpdate.setBounds(10, 70, 100, 30);
this.add(btnUpdate);
btnUpdate.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
dto.setSubject("반갑습니다.");
dto.setContent("안녕하세요 잘 지내고 있나여");
dto.setWriter("홍길이");
dao.update(dto);
}
});
JButton btnSelect = new JButton("조회");
btnSelect.setBounds(10, 100, 100, 30);
this.add(btnSelect);
btnSelect.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
BoardDTO gdto = new BoardDTO();
gdto = dao.getOne(dto);
System.out.println(gdto.getSubject() + " " + gdto.getContent() + " " + gdto.getWriter() + "");
}
});
JButton btnSelectAll = new JButton("전체조회");
btnSelectAll.setBounds(10, 130, 100, 30);
this.add(btnSelectAll);
btnSelectAll.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
List<BoardDTO> list = dao.getAll();
for (int i = 0; i < list.size(); i++) {
BoardDTO gdto = list.get(i);
System.out.println(gdto.getSubject() + " " + gdto.getContent() + " " + gdto.getWriter() + "");
}
}
});
this.setVisible(true);
}
public static void main(String[] args) {
// TODO Auto-generated method stub
new BoardUI();
}
}