JAVA, MySQL DB연동 순서
-
String sql = "sql문";
- select : executeQuery()
- insert, update, delete : executeUpdate()
-
Connection conn = DBConnection.getConnection();
-
ps = conn.prepareStatement(sql); //택배차
ps.setString(1, userid); //물음표에 넘겨진 값을 문자열로 셋팅
rs = ps.executeQuery() or executUpdate();
ㄴ select문일 경우만 ResultSet(rs) 타입의 객체로 받음 -
다 작성 후 try~catch문
UserDAO
package model.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import model.DBConnection;
import model.Session;
import model.dto.UserDTO;
public class UserDAO {
private Connection conn; //DAO내부에서만 쓸거니까 private 선언
private PreparedStatement ps;
private ResultSet rs;
public UserDAO() {
conn = DBConnection.getConnection(); //connection은 각 메소드마다 만들어야되니까 UserDAO생성자에서 연결
}
public boolean checkId(String userid) {
String sql = "select * from user where userid = ?"; //데이터 넘겨주는 부분은 물음표
try {
ps = conn.prepareStatement(sql); //택배차
ps.setString(1, userid); //물음표에 넘겨진 값을 문자열로 셋팅
rs = ps.executeQuery(); //결과 (select : executeQuery)
return !rs.next(); //rs 리턴 / 중복체크 로직 -> 똑같은 아이디를 찾았을때 결과가 없어야함. 다음으로 넘어가면 안됨(중복) => false가 리턴되어야 하니까 !로 리턴(검색된 결과가 없으면 참)
} catch (SQLException e) {
e.printStackTrace();
}
// finally {
// //실제로는 finally 블럭을 이용해 사용한 rs, ps, conn 들을 close() 해주어야 한다.
// try {
// rs.close();
// ps.close();
// conn.close();
// } catch (SQLException e) {
// }
// }
return false;
}
public boolean join(UserDTO user) {
String sql = "insert into user (userid,userpw,username,userage,userphone,useraddr)"
+ " values(?,?,?,?,?,?)"; //입력받을 컬럼 갯수만큼 물음표
int result = 0;
try {
ps = conn.prepareStatement(sql);
ps.setString(1, user.getUserid());
ps.setString(2, user.getUserpw());
ps.setString(3, user.getUsername());
ps.setInt(4, user.getUserage());
ps.setString(5, user.getUserphone());
ps.setString(6, user.getUseraddr());
result = ps.executeUpdate(); //insert, update, delete : executeUpdate() <= 수정된게 있으면 행의 갯수를 리턴 (행은 한개 추가했으니까 result == 1
} catch (SQLException e) {
e.printStackTrace();
System.out.println(e);
}
return result == 1; //리턴은 boolean타입
}
public boolean login(String userid, String userpw) {
String sql = "select * from user where userid = ? and userpw = ?";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, userid);
ps.setString(2, userpw);
rs = ps.executeQuery(); //select : executeQuery()
if(rs.next()) { //rs.next() = true면 if문 실행
UserDTO loginUser = new UserDTO( //로그인유저 객체 생성
rs.getString("userid"),
rs.getString("userpw"),
rs.getString("username"),
rs.getInt("userage"),
rs.getString("userphone"),
rs.getString("useraddr")
);
Session.setData("loginUser", loginUser); //로그인유저 객체 세션에 저장
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public boolean modifyUser(String userid, int choice, String newData) {
String[] columns = {"","userpw","userphone","useraddr"};
String sql = "update user set "+columns[choice]+"=? where userid=?";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, newData);
ps.setString(2, userid);
return ps.executeUpdate() == 1;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
//회원탈퇴
public boolean leaveId(String userid) {
String sql = "delete from user where userid = ?";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, userid);
return ps.executeUpdate() == 1;
} catch (SQLException e) {
}
return false;
}
}ProductDAO
package model.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import model.DBConnection;
import model.Session;
import model.dto.ProductDTO;
import model.dto.UserDTO;
public class ProductDAO {
private Connection conn;
private PreparedStatement ps;
private ResultSet rs;
public ProductDAO() {
conn = DBConnection.getConnection(); //여러 메소드에 공통으로 사용해야되는 conn은 ProductDAO 생성자에 생성
}
public boolean addProduct(ProductDTO newProduct) {
String sql = "insert into product (prodname,prodprice,prodamount,prodinfo,userid)"
+ " values(?,?,?,?,?)";
String userid = ((UserDTO)Session.getData("loginUser")).getUserid();
try {
ps = conn.prepareStatement(sql);
//setString(물음표자리, 가져올거)
ps.setString(1, newProduct.getProdname());
ps.setInt(2, newProduct.getProdprice());
ps.setInt(3, newProduct.getProdamount());
ps.setString(4, newProduct.getProdinfo());
ps.setString(5, userid);
return ps.executeUpdate() == 1; //insert = executeUpdate()
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public ArrayList<ProductDTO> getList() {
String sql = "select * from product where userid = ?";
String userid = ((UserDTO)Session.getData("loginUser")).getUserid();
ArrayList<ProductDTO> result = new ArrayList<ProductDTO>();
try {
ps = conn.prepareStatement(sql);
ps.setString(1, userid);
rs = ps.executeQuery(); //select : executeQuery()
while(rs.next()) {
ProductDTO product = new ProductDTO(
rs.getInt("prodnum"),
rs.getString("prodname"),
rs.getInt("prodprice"),
rs.getInt("prodamount"),
rs.getString("prodinfo"),
rs.getInt("likecnt"),
userid
);
result.add(product); //result 객체에 검색된 정보 리스트로 추가
}
} catch (SQLException e) {
}
return result;
}
public boolean removeProduct(int prodnum) {
String sql = "delete from product where prodnum = "+prodnum;
try {
ps = conn.prepareStatement(sql);
return ps.executeUpdate() == 1;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public boolean modifyProduct(int prodnum, int choice, String newData) {
String[] columns = {"", "prodprice", "prodamount", "prodinfo"}; //배열로 규칙성부여 / 0번방은 비워둠(choice에 0이 없어서)
String sql = "update product set " + columns[choice] + " = ?"
+ " where prodnum = "+prodnum; //choice마다 컬럼명이 바뀜 => 물음표로 셋팅 불가 => 동적쿼리
try {
ps = conn.prepareStatement(sql);
ps.setString(1, newData);
return ps.executeUpdate() == 1;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public boolean removeAll(String userid) {
String sql = "select from product where userid = ?";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, userid);
rs = ps.executeQuery();
if(!rs.next()) {
return true;
}
sql = "delete from product where userid = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, userid);
return ps.executeUpdate() != 0;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public ArrayList<ProductDTO> search(String keyword) {
//select * from product where prodname like('%10000%')
//or prodinfo like('%10000%') or prodprice between 0.9*10000 and 1.1*10000
String sql = "select * from product where prodname like('%"+keyword+"%') or"
+ " prodinfo like ('%"+keyword+"%')";
if(isDigit(keyword)) {
sql += " or prodprice between 0.9*"+keyword+" and 1.1*"+keyword;
}
ArrayList<ProductDTO> result = new ArrayList<ProductDTO>();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
ProductDTO product = new ProductDTO(
rs.getInt("prodnum"),
rs.getString("prodname"),
rs.getInt("prodprice"),
rs.getInt("prodamount"),
rs.getString("prodinfo"),
rs.getInt("likecnt"),
rs.getString("userid")
);
result.add(product);
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
private boolean isDigit(String msg) {
for (int i = 0; i < msg.length(); i++) {
char ch = msg.charAt(i);
if(ch < '0' || ch > '9') {
return false;
}
}
return true;
}
public ProductDTO getDetail(int prodnum) {
String sql = "select * from product where prodnum="+prodnum;
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
if(rs.next()) {
ProductDTO product = new ProductDTO(
rs.getInt("prodnum"),
rs.getString("prodname"),
rs.getInt("prodprice"),
rs.getInt("prodamount"),
rs.getString("prodinfo"),
rs.getInt("likecnt"),
rs.getString("userid")
);
return product;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public boolean updateLikeCnt(int prodnum) {
String sql = "update product set likecnt = likecnt+1 where prodnum="+prodnum;
try {
ps = conn.prepareStatement(sql);
return ps.executeUpdate() == 1;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
}
