User.java
package com.fastcampus.ch3;
import java.util.Date;
import java.util.Objects;
public class User {
private String id;
private String pwd;
private String name;
private String email;
private Date birth;
private String sns;
private Date reg_date;
public User(){}
public User(String id, String pwd, String name, String email, Date birth, String sns, Date reg_date) {
this.id = id;
this.pwd = pwd;
this.name = name;
this.email = email;
this.birth = birth;
this.sns = sns;
this.reg_date = reg_date;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public String getSns() {
return sns;
}
public void setSns(String sns) {
this.sns = sns;
}
public Date getReg_date() {
return reg_date;
}
public void setReg_date(Date reg_date) {
this.reg_date = reg_date;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
User user = (User) o;
return id.equals(user.id) && Objects.equals(pwd, user.pwd) && Objects.equals(name, user.name) && Objects.equals(email, user.email) && Objects.equals(birth, user.birth) && Objects.equals(sns, user.sns);
}
@Override
public int hashCode() {
return Objects.hash(id, pwd, name, email, birth, sns);
}
@Override
public String toString() {
return "User{" +
"id='" + id + '\'' +
", pwd='" + pwd + '\'' +
", name='" + name + '\'' +
", email='" + email + '\'' +
", birth=" + birth +
", sns='" + sns + '\'' +
", reg_date=" + reg_date +
'}';
}
}DBConnectionTest2Test.java
package com.fastcampus.ch3;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.GenericXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import static org.junit.Assert.*;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/spring/**/root-context.xml"})
public class DBConnectionTest2Test {
@Autowired
DataSource ds;
@Test
public void insertUserTest() throws Exception {
User user = new User("asdf","1234","abc","aaaa@aaa.com",new Date(),"fb",new Date());
deleteAll();
int rowCnt = insertUser(user);
assertTrue(rowCnt==1);
}
@Test
public void selectUserTest() throws Exception {
deleteAll();
User user = new User("asdf2","1234","abc","aaaa@aaa.com",new Date(),"fb",new Date());
int rowCnt = insertUser(user);
User user2 = selectUser("asdf2");
assertTrue(user.getId().equals("asdf2"));
}
@Test
public void deleteUserTest() throws Exception {
deleteAll();
int rowCnt = deleteUser("asdf");
assertTrue(rowCnt==0);
User user = new User("asdf2","1234","abc","aaaa@aaa.com",new Date(),"fb",new Date());
rowCnt = insertUser(user);
assertTrue(rowCnt==1);
rowCnt = deleteUser(user.getId());
assertTrue(rowCnt==1);
assertTrue(selectUser(user.getId())==null);
}
//과제
// @Test
// public void updateUserTest() throws Exception {
// User user = new User("asdf3","1234","abc","aaaa@aaa.com",new Date(),"fb", "2021/01/01");
// deleteAll();
// int rowCnt = insertUser(user);
// assertTrue(rowCnt==1);
// User user2 = new User("asdf3", "1111", "ass", "bbbb@bbb.com", new Date(), "kakao", new Date());
// rowCnt = updateUser(user2);
// assertTrue(rowCnt==1);
//
// }
//과제
//매개변수로 받은 사용자 정보로 user_info테이블을 update하는 메서드
public int updateUser(User user) throws Exception {
Connection conn = ds.getConnection();
String sql = "update from user_info values(?,?,?,?,?,?,now())";
PreparedStatement pstmt = conn.prepareStatement(sql); //SQL Injection공격, 성능향상
pstmt.setString(1, user.getId());
pstmt.setString(2, user.getPwd());
pstmt.setString(3, user.getName());
pstmt.setString(4, user.getEmail());
pstmt.setDate(5, new java.sql.Date(user.getBirth().getTime()));
pstmt.setString(6, user.getSns());
int rowCnt = pstmt.executeUpdate(); //insert, delete, update
return rowCnt;
}
public int deleteUser(String id) throws Exception {
Connection conn = ds.getConnection();
String sql = "delete from user_info where id=? ";
PreparedStatement pstmt = conn.prepareStatement(sql); //SQL Injection공격, 성능향상
pstmt.setString(1, id);
// int rowCnt = pstmt.executeUpdate();
// return rowCnt;
return pstmt.executeUpdate();
}
public User selectUser(String id) throws Exception {
Connection conn = ds.getConnection();
String sql = "select * from user_info where id=? ";
PreparedStatement pstmt = conn.prepareStatement(sql); //SQL Injection공격, 성능향상
pstmt.setString(1,id);
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
User user = new User();
user.setId(rs.getString(1));
user.setPwd(rs.getString(2));
user.setName(rs.getString(3));
user.setEmail(rs.getString(4));
user.setBirth(new Date(rs.getDate(5).getTime()));
user.setSns(rs.getString(6));
user.setReg_date(new Date(rs.getTimestamp(7).getTime()));
return user;
}
return null;
}
private void deleteAll() throws Exception {
Connection conn = ds.getConnection();
String sql = "delete from user_info";
PreparedStatement pstmt = conn.prepareStatement(sql); //SQL Injection공격, 성능향상
pstmt.executeUpdate();
}
//사용자 정보를 user_info테이블에 저장하는 메서드
public int insertUser(User user) throws Exception {
Connection conn = ds.getConnection();
// insert into user_info (id, pwd, name, email, birth, sns, reg_date)
// values ('asdf22','1234','smith','aaa@aaa.com','2022-01-01','facebook',now());
String sql = "insert into user_info values(?,?,?,?,?,?,now())";
PreparedStatement pstmt = conn.prepareStatement(sql); //SQL Injection공격, 성능향상
pstmt.setString(1, user.getId());
pstmt.setString(2, user.getPwd());
pstmt.setString(3, user.getName());
pstmt.setString(4, user.getEmail());
pstmt.setDate(5, new java.sql.Date(user.getBirth().getTime()));
pstmt.setString(6, user.getSns());
int rowCnt = pstmt.executeUpdate(); //insert, delete, update
return rowCnt;
}
@Test
public void springJdbcConnectionTest() throws Exception {
// ApplicationContext ac = new GenericXmlApplicationContext("file:src/main/webapp/WEB-INF/spring/**/root-context.xml");
// DataSource ds = ac.getBean(DataSource.class);
Connection conn = ds.getConnection(); // 데이터베이스의 연결을 얻는다.
System.out.println("conn = " + conn);
assertTrue(conn!=null); //괄호 안의 조건식이 true면, 테스트 성공. 아니면 실패.
}
}
안녕하세요!!