스프링
JDBCTemplate 연습 2
1). MySQL DB 사용자, 데이터베이스, 테이블 생성
사용자
생성
create user 'spring5'@'localhost' identified by 'spring5';
권한
grant all privileges on spring5fs.* to 'spring5'@'localhost';
데이터베이스
create database spring5fs default character set utf8;
테이블
create table spring5fs.MEMBER (
ID int auto_increment primary key,
EMAIL varchar(255),
PASSWORD varchar(100),
NAME varchar(100),
REGDATE datetime,
unique key (EMAIL)
) engine=InnoDB character set = utf8;데이터 추가해서 테스트
insert into member(email, password, name, regdate)
value ('asd@asd.com', '1234', '김복자', now());
2). DTO 생성
package spring;
import java.time.LocalDateTime;
public class Member {
private Long id;
private String email;
private String password;
private String name;
private LocalDateTime registerDateTime;
public Member(String email, String password,
String name, LocalDateTime regDateTime) {
this.email = email;
this.password = password;
this.name = name;
this.registerDateTime = regDateTime;
}
void setId(Long id) {
this.id = id;
}
public Long getId() {
return id;
}
public String getEmail() {
return email;
}
public String getPassword() {
return password;
}
public String getName() {
return name;
}
public LocalDateTime getRegisterDateTime() {
return registerDateTime;
}
public void changePassword(String oldPassword, String newPassword) {
if (!password.equals(oldPassword))
throw new WrongIdPasswordException();
this.password = newPassword;
}
}
3). DataSource 설정 ( DAO 만들고 주입 )
package config;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import spring.MemberDao;
@Configuration
public class AppCtx {
@Bean(destroyMethod = "close") // 빈이 소멸될 때 커넥션 풀을 닫도록 설정
public DataSource dataSource() {
DataSource ds = new DataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/spring5fs?"
+"useSSL=true&useUnicode=true&characterEncoding=utf8");
ds.setUsername("spring5");
ds.setPassword("spring5");
ds.setInitialSize(2); // 초기 커넥션 개수 지정
ds.setMaxActive(10); // 최대 커넥션 개수 지정
return ds;
}
// DAO 만들고 주입
@Bean
public MemberDao memberDao() {
return new MemberDao(dataSource());
}
}
4). DAO 설정
1. JdbcTemplate 필드 선언
2. DAO 생성자 만든 후 매개변수 DataSource 인자로 받음
3. 생성자 내부에서 JdbcTemplate 객체를 생성
이렇게 구현된 생성자를 통해 MemberDao 객체가 생성될 때마다 JdbcTemplate이 초기화되고, 이후 MemberDao 클래스 내에서 jdbcTemplate를 사용하여 데이터베이스와 상호작용하는 메서드들을 작성할 수 있다.
package spring;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
public class MemberDao {
private JdbcTemplate jdbcTemplate;
public MemberDao(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public Member selectByEmail(String email) {
String sql = "select * from MEMBER where EMAIL = ?";
List<Member> result = jdbcTemplate.query(sql, new MemberRowMapper(), email);
return result.isEmpty() ? null : result.get(0);
}
public List<Member> selectAll(){
String sql = "select * from member";
// List<Member> result = jdbcTemplate.query(sql,
// new MemberRowMapper());
// 람다식
List<Member> result = jdbcTemplate.query(sql,
(ResultSet rs, int rowNum)->{
Member member = new Member(
rs.getString("email"),
rs.getString("password"),
rs.getString("name"),
rs.getTimestamp("regdate").toLocalDateTime());
member.setId(rs.getLong("id"));
return member;
});
return result;
}
public int count1() {
String sql = "select count(*) from member";
List<Integer> result = jdbcTemplate.query(
sql,
new RowMapper<Integer>() {
@Override
public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
return rs.getInt(1);
}
});
return result.get(0);
}
// 람다식
public int count2() {
String sql = "select count(*) from member";
List<Integer> result = jdbcTemplate.query(sql,
(ResultSet rs, int rewNum)->{
return rs.getInt(1);
});
return result.get(0);
}
}
5). 실행 테스트
package main;
import java.util.List;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import config.AppCtx;
import spring.Member;
import spring.MemberDao;
public class TestSelEmail {
public static void main(String[] args) {
AnnotationConfigApplicationContext ctx =
new AnnotationConfigApplicationContext(AppCtx.class);
MemberDao memberDao = ctx.getBean("memberDao", MemberDao.class);
// Member member = memberDao.selectByEmail("madvirus@madvirus.com");
// System.out.println(member);
List<Member> list = memberDao.selectAll();
for(Member m1 : list) {
System.out.println(m1);
}
// System.out.println("count1 = " + memberDao.count1() );
// System.out.println("count2 = " + memberDao.count2() );
}
}
오라클
중복되지 않는 값 UNIQUE ( NULL은 여러 개 존재 가능 )
제약 조건 지정하기 ( 테이블 생성할 때 )
create table table_unique(
login_id VARCHAR2(20) UNIQUE,
login_pwd VARCHAR2(20) NOT NULL,
tel VARCHAR2(20)
);
테이블에 데이터 넣기
insert into table_unique(login_id, login_pwd, tel)
VALUES ('Test_ID_01', 'PWD01', '010-1234-5678')
LOGIN_ID 열에 중복되는 데이터 넣기 ( 무결성 제약조건 위배 )
insert into table_unique(login_id, login_pwd, tel)
VALUES ('Test_ID_01', 'PWD01', '010-1234-5678')
LOGIN_PWD 열에 중복되는 데이터 넣기 ( NOT NULL 조건만 지정되어 있어서 가능 )
insert into table_unique(login_id, login_pwd, tel)
VALUES ('Test_ID_02', 'PWD01', '010-1234-5678')
LOGIN_ID 열에 NULL값 입력 ( 유니크니까 NULL값 입력 가능 )
insert into table_unique(login_id, login_pwd, tel)
VALUES (NULL, 'PWD01', '010-1234-5678')
JDBC => JDBCTemplate 변경 실습
DataSource 설정
package config;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import guestbook.dao.MessageDao;
@Configuration
public class AppCtx {
@Bean(destroyMethod = "close") // 빈이 소멸될 때 커넥션 풀을 닫도록 설정
public DataSource dataSource() {
DataSource ds = new DataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/guestbook?"
+"useSSL=true&useUnicode=true&characterEncoding=utf8");
ds.setUsername("jspexam");
ds.setPassword("jsppw");
ds.setInitialSize(2); // 초기 커넥션 개수 지정
ds.setMaxActive(10); // 최대 커넥션 개수 지정
return ds;
}
@Bean
public MessageDao messageDao() {
return new MessageDao(dataSource());
}
}
DTO 동일
package guestbook.model;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
@Getter
@Setter
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class Message {
private int id;
private String guestName;
private String password;
private String message;
// 생성자 추가
public Message(String guestName, String password, String message) {
super();
this.guestName = guestName;
this.password = password;
this.message = message;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getGuestName() {
return guestName;
}
public void setGuestName(String guestName) {
this.guestName = guestName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
public boolean hasPassword() {
return password != null && !password.isEmpty();
}
public boolean matchPassword(String pwd) {
return password != null && password.equals(pwd);
}
}
DAO ( 주석으로 된게 기존 JDBC )
package guestbook.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import guestbook.model.Message;
public class MessageDao {
private JdbcTemplate jdbcTemplate;
public MessageDao(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
private Message makeMessageFromResultSet(ResultSet rs) throws SQLException {
Message message = new Message();
message.setId(rs.getInt("message_id"));
message.setGuestName(rs.getString("guest_name"));
message.setPassword(rs.getString("password"));
message.setMessage(rs.getString("message"));
return message;
}
public int insert(Message message) {
String sql = "insert into guestbook_message " +
"(guest_name, password, message) values (?, ?, ?)";
return jdbcTemplate.update(sql,
message.getGuestName(),
message.getPassword(),
message.getMessage()
);
}
// public int insert(Connection conn, Message message) throws SQLException {
// PreparedStatement pstmt = null;
// try {
// pstmt = conn.prepareStatement(
// "insert into guestbook_message " +
// "(guest_name, password, message) values (?, ?, ?)");
// pstmt.setString(1, message.getGuestName());
// pstmt.setString(2, message.getPassword());
// pstmt.setString(3, message.getMessage());
// return pstmt.executeUpdate();
// } finally {
// JdbcUtil.close(pstmt);
// }
// }
public Message select(int messageId) {
String sql = "select * from guestbook_message where message_id = ?";
return jdbcTemplate.queryForObject(sql,
(ResultSet rs, int rowNum) -> {
return makeMessageFromResultSet(rs);
} ,messageId);
}
// public Message select(Connection conn, int messageId) throws SQLException {
// PreparedStatement pstmt = null;
// ResultSet rs = null;
// try {
// pstmt = conn.prepareStatement(
// "select * from guestbook_message where message_id = ?");
// pstmt.setInt(1, messageId);
// rs = pstmt.executeQuery();
// if (rs.next()) {
// return makeMessageFromResultSet(rs);
// } else {
// return null;
// }
// } finally {
// JdbcUtil.close(rs);
// JdbcUtil.close(pstmt);
// }
// }
public int selectCount() {
String sql = "select count(*) from guestbook_message";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
// public int selectCount(Connection conn) throws SQLException {
// Statement stmt = null;
// ResultSet rs = null;
// try {
// stmt = conn.createStatement();
// rs = stmt.executeQuery("select count(*) from guestbook_message");
// rs.next();
// return rs.getInt(1);
// } finally {
// JdbcUtil.close(rs);
// JdbcUtil.close(stmt);
// }
// }
public List<Message> selectList(int firstRow, int endRow){
String sql = "select * from guestbook_message " +
"order by message_id desc limit ?, ?";
List<Message> result = jdbcTemplate.query(sql,
(rs, rowNum) -> makeMessageFromResultSet(rs),
firstRow - 1,
endRow - firstRow + 1);
return result;
}
// public List<Message> selectList(Connection conn, int firstRow, int endRow)
// throws SQLException {
// PreparedStatement pstmt = null;
// ResultSet rs = null;
// try {
// pstmt = conn.prepareStatement(
// "select * from guestbook_message " +
// "order by message_id desc limit ?, ?");
// pstmt.setInt(1, firstRow - 1);
// pstmt.setInt(2, endRow - firstRow + 1);
// rs = pstmt.executeQuery();
// if (rs.next()) {
// List<Message> messageList = new ArrayList<Message>();
// do {
// messageList.add(makeMessageFromResultSet(rs));
// } while (rs.next());
// return messageList;
// } else {
// return Collections.emptyList();
// }
// } finally {
// JdbcUtil.close(rs);
// JdbcUtil.close(pstmt);
// }
// }
public int delete1(int messageId) {
String sql = "delete from guestbook_message where message_id = ?";
return jdbcTemplate.update(sql, messageId);
}
// public int delete(Connection conn, int messageId) throws SQLException {
// PreparedStatement pstmt = null;
// try {
// pstmt = conn.prepareStatement(
// "delete from guestbook_message where message_id = ?");
// pstmt.setInt(1, messageId);
// return pstmt.executeUpdate();
// } finally {
// JdbcUtil.close(pstmt);
// }
// }
}'프로젝트 기반 자바(JAVA) 응용 SW개발자 취업과정' 카테고리의 다른 글
| 2023-08-07 57일차 (0) | 2023.08.07 |
|---|---|
| 2023-08-04 56일차 (0) | 2023.08.04 |
| 2023-08-02 54일차 (0) | 2023.08.02 |
| 2023-08-01 53일차 (0) | 2023.08.01 |
| 2023-07-31 52일차 (0) | 2023.07.31 |
