2023-08-03 55일차

2023. 8. 3. 20:48

스프링

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

CO_JUN 개발일지