ABOUT ME

    -

    Today
    -
    Yesterday
    -
    Total
    -
    • [Spring Security] DB정보로 로그인/로그아웃하기 (1)
      DEV/Spring 2020. 8. 13. 16:49

      1. 테이블생성/데이터 추가하기 (mysql기준)

      -- -----------------------------------------------------
-- Table `member`
-- -----------------------------------------------------
CREATE TABLE `member` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'member id',
  `name` VARCHAR(255) NOT NULL COMMENT 'member name',
  `password` VARCHAR(255) NOT NULL COMMENT '암호화된 password',
  `email` VARCHAR(255) NOT NULL UNIQUE COMMENT 'login id, email',
  `create_date` DATETIME NULL DEFAULT NULL COMMENT '등록일',
  `modify_date` DATETIME NULL DEFAULT NULL COMMENT '수정일',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- -----------------------------------------------------
-- Table `member_role`
-- -----------------------------------------------------
CREATE TABLE `member_role` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'role id',
  `member_id` INT(11) NOT NULL COMMENT 'member id fk',
  `role_name` VARCHAR(100) NOT NULL COMMENT 'role 이름 ROLE_ 로 시작하는 값이어야 한다.',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`member_id`)
  REFERENCES `member` (`id`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;
      insert into member (id, name, password, email, create_date, modify_date) values ( 1, '고문영', '$2a$10$G/ADAGLU3vKBd62E6GbrgetQpEKu2ukKgiDR5TWHYwrem0cSv6Z8m', 'munyoung@example.com', now(), now());
insert into member (id, name, password, email, create_date, modify_date) values ( 2, '문강태', '$2a$10$G/ADAGLU3vKBd62E6GbrgetQpEKu2ukKgiDR5TWHYwrem0cSv6Z8m', 'gangtae@example.com', now(), now());

insert into member_role (id, member_id, role_name) values (1, 1, 'ROLE_USER');
insert into member_role (id, member_id, role_name) values (2, 1, 'ROLE_ADMIN');
insert into member_role (id, member_id, role_name) values (3, 2, 'ROLE_USER');

       

      2. pom.xml에 데이터베이스 사용을 위한 라이브러리 추가

      <!-- mysql jdbc driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
        </dependency>

       

      pom.xml

      더보기 
      <project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>org.edwith.webbe</groupId>
	<artifactId>securityexam</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>securityexam Maven Webapp</name>
	<url>http://maven.apache.org</url>

	<properties>
		<!-- eclipse에서 웹 어플리케이션 프로젝트 작성시 web.xml파일을 작성하지 않고 java-config로 설정할 경우 
			아래의 설정이 있어야 합니다. -->
		<failOnMissingWebXml>false</failOnMissingWebXml>
		<!-- spring 5.2.3이 나오는 시점에 spring security는 5.2.2가 최신버전이라서 5.2.2.RELEASE로 
			설정함 -->
		<spring.version>5.2.2.RELEASE</spring.version>
	</properties>

	<dependencies>
		<!-- servlet-api이다. tomcat에 배포될 경우엔 사용되지 않도록 하기 위해서 scope를 provided로 설정하였다. -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>3.1.0</version>
			<scope>provided</scope>
		</dependency>

		<!-- jsp-api이다. tomcat에 배포될 경우엔 사용되지 않도록 하기 위해서 scope를 provided로 설정하였다. -->
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>javax.servlet.jsp-api</artifactId>
			<version>2.3.2-b02</version>
			<scope>provided</scope>
		</dependency>

		<!-- jstl은 tomcat이 기본 지원하지 않는다. 그렇기 때문에 tomcat에도 배포가 되야 한다. -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>

		<!-- spring webmvc에 대한 의존성을 추가한다. spring webmvc에 대한 의존성을 추가하게 되면 spring-web, 
			spring-core등이 자동으로 의존성이 추가된다. -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		
		<!-- mysql jdbc driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
        </dependency>

        <!-- 커넥션 풀 라이브러리를 추가한다. spring boot 2의 경우는 hikariCP가 기본으로 사용된다.-->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.6.0</version>
        </dependency>

        <!-- DataSource, Transaction등을 사용하려면 추가한다. spring-tx를 자동으로 포함시킨다.-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${spring.version}</version>
        </dependency>

		<!-- java 9 이상에서 추가해줘야 합니다. @PostConstruct 등을 사용하려면 필요함 -->
		<dependency>
			<groupId>javax.annotation</groupId>
			<artifactId>javax.annotation-api</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- Spring Security Core -->
		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-core</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- Spring Security Config -->
		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-config</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- Spring Security Web -->
		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-web</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- Spring Security JSP Custom Tags -->
		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-taglibs</artifactId>
			<version>${spring.version}</version>
		</dependency>

	</dependencies>
	<build>
		<finalName>securityexam</finalName>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.7.0</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
					<encoding>utf-8</encoding>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

       

      3. ApplicationConfig에 데이터베이스 관련설정 추가

      package securityexam.config;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

//Spring 설정파일
//레이어드 아키텍처에서 Controller가 사용하는 Bean들에 대해 설정을 한다.
//dao, service를 컴포넌트 스캔하여 찾도록 한다.
//어노테이션으로 트랜잭션을 관리하기 위해 @EnableTransactionManagement를 설정하였다.

@Configuration
@EnableTransactionManagement
@ComponentScan(basePackages= {"securityexam.dao","securityexam.service"})
public class ApplicationConfig implements TransactionManagementConfigurer {
	
	// mysql 드라이버 클래스 이름은 "com.mysql.jdbc.Driver"에서 버전 6이후에는 "com.mysql.cj.jdbc.Driver"로 변경되었다.
	private String driverClassName = "com.mysql.cj.jdbc.Driver";
	
	// java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value 'KST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.)
    // DB연결시 위와 같은 오류가 발생한다면 &serverTimezone=UTC 를 url에 붙여줘야 합니다.
	private String url = "jdbc:mysql://localhost:3306/데이터베이스명?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC";
	private String username="데이터베이스유저아이디";
	private String password="데이터베이스비밀번호";
	
	/**
     * 커넥션 풀과 관련된 Bean을 생성한다.
     * @return
     */
    @Bean
    public DataSource dataSource(){
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);

        return dataSource;
    }
    
    /**
     * 트랜잭션 관리자를 생성한다.
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
    	return new DataSourceTransactionManager(dataSource());
    }
    
	@Override
	public PlatformTransactionManager annotationDrivenTransactionManager() {
		return transactionManager();
	}

}

      ApplicationConfig.java

       

      4. DTO, DAO 작성

      package securityexam.dto;

import java.util.Date;

public class Member {
	private Long id;
	private String name;
	private String password;
	private String email;
	private Date createDate;
	private Date modifyDate;
	
	public Member() {
		createDate = new Date();
		modifyDate = new Date();
	}

	public Member(Long id, String name, String password, String email) {
		this();
		this.name = name;
		this.password = password;
		this.email = email;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getCreateDate() {
		return createDate;
	}

	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}

	public Date getModifyDate() {
		return modifyDate;
	}

	public void setModifyDate(Date modifyDate) {
		this.modifyDate = modifyDate;
	}
	
	@Override
	public String toString() {
		return "Member [id=" + id + ", name=" + name + ", password=" + password + ", email=" + email + ", createDate="
				+ createDate + ", modifyDate=" + modifyDate + "]";
	}

}

      Member.java

       

      package securityexam.dto;

public class MemberRole {
	private Long id;
	private Long memberId;
	private String roleName;
	
	public MemberRole() {
		
	}

	public MemberRole(Long memberId, String roleName) {
		this.memberId = memberId;
		this.roleName = roleName;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Long getMemberId() {
		return memberId;
	}

	public void setMemberId(Long memberId) {
		this.memberId = memberId;
	}

	public String getRoleName() {
		return roleName;
	}

	public void setRoleName(String roleName) {
		this.roleName = roleName;
	}
	
}

      MemberRole.java

       

       

      package securityexam.dao;

public class MemberDaoSqls {
	public static final String SELECT_ALL_BY_EMAIL = "SELECT id,name,password,email,create_date,modify_date FROM member WHERE email = :email";
}

      MemberDaoSqls.java

       

       

      @Repository
public class MemberDao {
	private NamedParameterJdbcTemplate jdbc;
	// BeanPropertyRowMapper는 Role클래스의 프로퍼티를 보고 자동으로 칼럼과 맵핑해주는 RowMapper객체를 생성한다.
	// roleId 프로퍼티는 role_id 칼럼과 맵핑이 된다.
	private RowMapper<Member> rowMapper = BeanPropertyRowMapper.newInstance(Member.class);

	public MemberDao(DataSource dataSource){
		this.jdbc = new NamedParameterJdbcTemplate(dataSource);
	}

	public Member getMemberByEmail(String email){
		Map<String, Object> map = new HashMap<>();
		map.put("email", email);

		return jdbc.queryForObject(MemberDaoSqls.SELECT_ALL_BY_EMAIL, map, rowMapper);
	}
}

      MemberDao.java

       

       

      package securityexam.dao;

public class MemberRoleDaoSqls {
	public static final String SELECT_ALL_BY_EMAIL = "SELECT mr.id,mr.member_id,mr.role_name FROM member_role mr JOIN member m ON mr.member_id = m.id WHERE m.email = :email";
}

      MemberRoleDaoSqls.java

       

       

      @Repository
public class MemberRoleDao {
	private NamedParameterJdbcTemplate jdbc;
	// BeanPropertyRowMapper는 Role클래스의 프로퍼티를 보고 자동으로 칼럼과 맵핑해주는 RowMapper객체를 생성한다.
	// roleId 프로퍼티는 role_id 칼럼과 맵핑이 된다.
	private RowMapper<MemberRole> rowMapper = BeanPropertyRowMapper.newInstance(MemberRole.class);

	public MemberRoleDao(DataSource dataSource){
		this.jdbc = new NamedParameterJdbcTemplate(dataSource);
	}

	public List<MemberRole> getRolesByEmail(String email){
		Map<String, Object> map = new HashMap<>();
		map.put("email", email);

		return jdbc.query(MemberRoleDaoSqls.SELECT_ALL_BY_EMAIL, map, rowMapper);
	}
}

      MemberRoleDao.java

      'DEV > Spring' 카테고리의 다른 글

      전자정부프레임워크기반 게시판 만들기 (1) 시작  (1) 2020.12.28
      [Spring Security] 회원가입하기  (0) 2020.08.20
      [Spring Security] DB정보로 로그인/로그아웃하기 (2)  (0) 2020.08.14
      [Spring Security] 스프링 시큐리티 설정  (0) 2020.07.15
      [Spring Security] 스프링 시큐리티 개념  (0) 2020.07.15

      관련글 관련글 더보기

      댓글

    Designed by Tistory.

    티스토리툴바