[2022-08-11] TIL - 12일차
2022. 8. 11. 13:27ㆍGolfzon tech/TIL
😁 Today I Learned
- PostgreSql
- Member, Board 테이블 생성
- CRUD 작성 (insert, update, delete, select
개인적으로 Comment 테이블을 따로 생성하여 데이터를 넣어보았다.
JDBC를 사용하여 PostgreSql과 연결하고 insert, select 순서대로 코드를 작성하였다.
1. VO 작성
//////// CommentVO ///////
package test.comment;
import java.io.Serializable;
import java.sql.Timestamp;
public class CommentVO implements Serializable{
private int num; // 인덱스
private String content; // 댓글 내용
private String writer; // 작성자
private Timestamp wdate; // 작성 날짜.
private int likeCount; // 좋아요 개수
public CommentVO() {
System.out.println("CommentVO 생성자..");
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
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 Timestamp getWdate() {
return wdate;
}
public void setWdate(Timestamp wdate) {
this.wdate = wdate;
}
public int getLikeCount() {
return likeCount;
}
public void setLikeCount(int likeCount) {
this.likeCount = likeCount;
}
@Override
public String toString() {
return "CommentVO [num=" + num + ", content=" + content + ", writer=" + writer + ", wdate=" + wdate
+ ", likeCount=" + likeCount + "]";
}
}
2. Interface DAO 작성
/////// CommentDAO //////
package test.comment;
import java.util.ArrayList;
public interface CommentDAO {
// insert
int insert(CommentVO vo);
// update
int update(CommentVO vo);
// delete
int delete(CommentVO vo);
// selectOne
CommentVO selectOne(CommentVO vo);
// selectAll
ArrayList<CommentVO> selectAll();
// searchList
ArrayList<CommentVO> searchList(String searchKey, String searchWord);
}
3. DAO 를 상속받은 DAOimpl 클래스를 생성 (오버 라이딩하기 전)
4. psql 을 접속하여 Comment 테이블 생성
CREATE TABLE comment
(
num integer NOT NULL,
content character varying(1000) NOT NULL,
writer character varying(50) NOT NULL,
wdate timestamp with time zone NOT NULL DEFAULT now(),
likeCount integer NOT NULL,
PRIMARY KEY (num)
);
5. 사용할 query 문과 jdbc 연결에 필요한 드라이버, URL, User, Password 저장할 Interface 생성
////////// CommentDB_postgres //////////
package test.comment;
public interface CommentDB_postgres {
// Connection
String DRIVER_NAME = "org.postgresql.Driver";
String URL = "jdbc:postgresql://localhost:5432/golfzon";
String USER = "kosta";
String PASSWORD = "hi123456";
// Query문
String SQL_INSERT = "insert into comment (num,content,writer,likeCount) "
+ "values (nextval('seq_comment'),?,?,?)";
String SQL_UPDATE = "update comment set content=?, writer=?, wdate=now(), likeCount=? where num = ?";
String SQL_DELETE = "delete from comment where num = ?";
String SQL_SELECT_ONE = "select * from comment where num = ?";
String SQL_SELECT_ALL = "select * from comment order by num asc";
String SQL_SEARCH_LIST_CONTENT = "select * from comment where content like ?";
String SQL_SEARCH_LIST_WRITER = "select * from comment where writer like ?";
}
6. DAOimpl 작성
////////// CommentDAOimpl ////////
package test.comment;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class CommentDAOimpl implements CommentDAO {
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
public CommentDAOimpl() {
try {
Class.forName(CommentDB_postgres.DRIVER_NAME); // 드라이버가 먼저 연결해야 밑에가 동작한다.
System.out.println("Driver success! ");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
@Override
public int insert(CommentVO vo) {
System.out.println("insert()...");
int flag = 0;
try {
conn = DriverManager.getConnection(CommentDB_postgres.URL, CommentDB_postgres.USER,
CommentDB_postgres.PASSWORD);
System.out.println("connection success");
pstmt = conn.prepareStatement(CommentDB_postgres.SQL_INSERT);
pstmt.setString(1, vo.getContent());
pstmt.setString(2, vo.getWriter());
pstmt.setInt(3, vo.getLikeCount());
flag = pstmt.executeUpdate(); // ?? 머지 이거
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} // end finally
System.out.println(vo);
return flag;
}
@Override
public int update(CommentVO vo) {
System.out.println("update()....");
int flag = 0;
try {
conn = DriverManager.getConnection(CommentDB_postgres.URL, CommentDB_postgres.USER,
CommentDB_postgres.PASSWORD);
pstmt = conn.prepareStatement(CommentDB_postgres.SQL_UPDATE);
pstmt.setString(1, vo.getContent());
pstmt.setString(2, vo.getWriter());
pstmt.setInt(3, vo.getLikeCount());
pstmt.setInt(4, vo.getNum());
flag = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} // end finally
return flag;
}
@Override
public int delete(CommentVO vo) {
int flag = 0;
try {
conn = DriverManager.getConnection(CommentDB_postgres.URL,CommentDB_postgres.USER,CommentDB_postgres.PASSWORD);
pstmt = conn.prepareStatement(CommentDB_postgres.SQL_DELETE);
pstmt.setInt(1, vo.getNum());
flag = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return flag;
}
@Override
public CommentVO selectOne(CommentVO vo) {
System.out.println("selectOne()...");
try {
conn = DriverManager.getConnection(CommentDB_postgres.URL, CommentDB_postgres.USER,
CommentDB_postgres.PASSWORD);
pstmt = conn.prepareStatement(CommentDB_postgres.SQL_SELECT_ONE);
pstmt.setInt(1,vo.getNum());
rs = pstmt.executeQuery();
// rs.next() 읽어올 것이 있으면
while (rs.next()) {
vo.setNum(rs.getInt("num"));
vo.setContent(rs.getString("content"));
vo.setWriter(rs.getString("writer"));
vo.setWdate(rs.getTimestamp("wdate"));
vo.setLikeCount(rs.getInt("likeCount"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally { // close가 있어서 finally 해줘야ㅕ 됨.
if (rs != null) {
try {
rs.close(); // 나중에 쓴걸 먼저 닫는다.
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} // end finally
return vo;
}
@Override
public ArrayList<CommentVO> selectAll() {
System.out.println("selectAll()...");
ArrayList<CommentVO> list = new ArrayList<CommentVO>();
try {
conn = DriverManager.getConnection(CommentDB_postgres.URL, CommentDB_postgres.USER,
CommentDB_postgres.PASSWORD);
pstmt = conn.prepareStatement(CommentDB_postgres.SQL_SELECT_ALL);
rs = pstmt.executeQuery();
// rs.next() 읽어올 것이 있으면
while (rs.next()) {
CommentVO vo = new CommentVO();
vo.setNum(rs.getInt("num"));
vo.setContent(rs.getString("content"));
vo.setWriter(rs.getString("writer"));
vo.setWdate(rs.getTimestamp("wdate"));
vo.setLikeCount(rs.getInt("likeCount"));
list.add(vo);
}
} catch (SQLException e) {
e.printStackTrace();
} finally { // close가 있어서 finally 해줘야ㅕ 됨.
if (rs != null) {
try {
rs.close(); // 나중에 쓴걸 먼저 닫는다.
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} // end finally
return list;
}
@Override
public ArrayList<CommentVO> searchList(String searchKey, String searchWord) {
System.out.println("searchList()...");
System.out.println(searchKey);
System.out.println(searchWord);
ArrayList<CommentVO> list = new ArrayList<CommentVO>();
try {
conn = DriverManager.getConnection(CommentDB_postgres.URL, CommentDB_postgres.USER,
CommentDB_postgres.PASSWORD);
if(searchKey.equals("content")) {
pstmt = conn.prepareStatement(CommentDB_postgres.SQL_SEARCH_LIST_CONTENT);
}else if(searchKey.equals("writer")) {
pstmt = conn.prepareStatement(CommentDB_postgres.SQL_SEARCH_LIST_WRITER);
}
pstmt.setString(1, "%"+searchWord+"%");
rs = pstmt.executeQuery();
// rs.next() 읽어올 것이 있으면
while (rs.next()) {
CommentVO vo = new CommentVO();
vo.setNum(rs.getInt("num"));
vo.setContent(rs.getString("content"));
vo.setWriter(rs.getString("writer"));
vo.setWdate(rs.getTimestamp("wdate"));
vo.setLikeCount(rs.getInt("likeCount"));
list.add(vo);
}
} catch (SQLException e) {
e.printStackTrace();
} finally { // close가 있어서 finally 해줘야ㅕ 됨.
if (rs != null) {
try {
rs.close(); // 나중에 쓴걸 먼저 닫는다.
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} // end finally
return list;
}
}
7. 실행할 main 생성
/////////// main //////////////
package test.comment;
import java.util.ArrayList;
public class CommentMain {
public static void main(String[] args) {
CommentDAO dao = new CommentDAOimpl();
CommentVO vo = new CommentVO();
vo.setContent("댓글 내용2");
vo.setWriter("댓글 작성자2");
vo.setLikeCount(2);
// insert
// int result = dao.insert(vo);
// System.out.println(result);
// vo.setContent("변경!");
// vo.setWriter("댓글 변경!");
// vo.setLikeCount(55);
// vo.setNum(5);
// // update
// int result = dao.update(vo);
// System.out.println(result);
System.out.println("===============");
// delete
// int result = dao.delete(vo);
// System.out.println(result);
// selectAll
ArrayList<CommentVO> list = new ArrayList<CommentVO>(dao.selectAll());
for (CommentVO data : list) {
System.out.println(data);
}
System.out.println("===============");
// searchList
ArrayList<CommentVO> list2 = dao.searchList("content","변경") ;
for (CommentVO data : list2) {
System.out.println(data);
}
}
}
메인에서는 올바른 테스트를 위해 selectAll 메서드를 제외하고 주석처리해가면서 다른 메서드들을 테스트하였다.
+) JDBC 는 자바에서 데이터베이스에 접근하기 위해 사용되는 인터페이스다.
728x90
'Golfzon tech > TIL' 카테고리의 다른 글
| [2022-08-16] TIL - 14일차 (0) | 2022.08.16 |
|---|---|
| [2022-08-12] TIL - 13일차 (0) | 2022.08.16 |
| [2022-08-10] TIL - 11일차 (0) | 2022.08.10 |
| [2022-08-08] TIL - 10일차 (0) | 2022.08.08 |
| [2022-08-05] TIL - 9일차 (0) | 2022.08.05 |