Spring Security (3) - JDBC 설정 (xml)

1. security-context.xml 수정

<!-- <security:authentication-manager>
		<security:authentication-provider>
			<security:user-service>
				<security:user name="user" password="password" authorities="ROLE_USER" />
				<security:user name="admin" password="password" authorities="ROLE_ADMIN" />
			</security:user-service>
		</security:authentication-provider>
	</security:authentication-manager> -->
	<security:authentication-manager>
		<security:authentication-provider>
			<security:jdbc-user-service data-source-ref="dataSource"
						role-prefix=""
						users-by-username-query="SELECT username, password, enabled FROM users WHERE username = ?"
						authorities-by-username-query="SELECT username, authority FROM authorities WHERE username = ?"
			/>
		</security:authentication-provider>
	</security:authentication-manager>

 - 기존에 임시로 지정해줬던 user, admin 계정을 주석처리 후 jdbc 연결설정 코드 추가

 - (dataSource 추가)

 - users 테이블 추가 Column = username, password, enabled

  - 테이블 값 insert

  - username : admin00 , password : pw00, enabled : 1

  - ex) insert into users(username, password, enabled) values('admin00', 'pw00', '1');

        insert into users(username, password, enabled) values('user00', 'pw00', '1');

 - authorities 테이블 추가 Column = username, authority

  - username : admin00, authority

  - ex) insert into authorities(username, authority) values('admin00', 'ROLE_ADMIN');

        insert into authorities(username, authority) values('user00', 'ROLE_USER');

 

2. pom.xml 추가

<!-- MyBatis -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis</artifactId>
		    <version>3.4.6</version>
		</dependency>
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis-spring</artifactId>
		    <version>1.3.2</version>
		</dependency>
		
		<!-- MySQL -->
		<dependency>
		    <groupId>mysql</groupId>
		    <artifactId>mysql-connector-java</artifactId>
		    <version>8.0.16</version>
		</dependency>
			
		
		<!-- spring-jdbc -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
		

 

3. root-context.xml 추가

<!-- mysql 설정  -->
		<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property> 
		    <property name="url" value="jdbc:mysql://127.0.0.1:3306/'디비명'?useSSL=false&amp;serverTimezone=UTC"></property>
		    <property name="username" value="MYSQL Id"></property>
		    <property name="password" value="MYSQL Pw"></property>
		</bean>
		
		<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	        <property name="dataSource" ref="dataSource"></property>
	    </bean>
	    <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate" destroy-method="clearCache">
		    <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg>
		</bean>

- dataSource 설정 후 테스트 실행

 

4. src/text/java MySqlConnectionTest.class 생성 

public class MySqlConnectionText {
	
	private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	private static final String URL = "jdbc:mysql://127.0.0.1:3306/디비명?useSSL=false&serverTimezone=UTC";
	private static final String USER = "id";
	private static final String PASSWORD = "password";
	
	@Test
	public void testConnection() throws Exception {
		Class.forName(DRIVER);
		
		
		try(Connection con = DriverManager.getConnection(URL, USER, PASSWORD)){
			System.out.println(con);
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

}

- 접속 성공 시 콘솔창

- 테스트까지 성공했으면 접속 테스트

 

- MySQL 테이블에 있는 접속 계정정보로 로그인 성공