내용

글번호 699
작성자 heojk
작성일 2017-06-30 15:50:09
제목 [Re]회원관리기능 구현하기(숙제) 1
내용 ----------- 설정 1. Maven pom.xml 파일 설정을 할 수 있습니까? - spring-jdbc, commons-dbcp, odjbc, aspectjweaver pom.xml
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
		<dependency>
		    <groupId>com.github.noraui</groupId>
		    <artifactId>ojdbc7</artifactId>
		    <version>12.1.0.2</version>
		</dependency>
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjweaver</artifactId>
			<version>1.8.10</version>
		</dependency>
2. web.xml 파일에 공통 컴포넌트 설정파일(application-config.xml)과 - 웹 컴포넌트 설정파일(mvc-config.xml)을 설정할 수 있습니까? 파일 작성하고 위치를 지정하면 됩니다. WEB-INF/web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

	<!-- The definition of the Root Spring Container shared by all Servlets and Filters -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>classpath:spring/application-config.xml</param-value>
	</context-param>
	
	<!-- Creates the Spring Container shared by all Servlets and Filters -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<!-- Processes application requests -->
	<servlet>
		<servlet-name>appServlet</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>/WEB-INF/spring/mvc-config.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
		
	<servlet-mapping>
		<servlet-name>appServlet</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>

</web-app>
3. application-config.xml 파일에 DataSource, JDBCTemplate, TransactionManager 설정을 할 수 있습니까? application-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">

	<context:component-scan base-package="com.coderby.myapp"/>
	
	<bean id="dataSoure" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
		<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
		<property name="username" value="hr"/>
		<property name="password" value="hr"/>
	</bean>
	
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSoure"/>
	</bean>

	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSoure"/>
	</bean>
	
	<tx:annotation-driven/>
</beans>
----------- biz() 구현 4. 데이터베이스에 테이블을 생성하고 샘플 데이터를 insert할 수 있습니까? - SQL Developer 에서 user_ddl.sql
desc users;
drop table users;
create table users (
    user_id varchar2(50) primary key,
    user_name varchar2(30),
    user_password varchar2(30),
    user_role varchar2(50)
);
select * from users;
desc users;
4-1. 테이터베이스 테이블 데이터를 매핑시킬 수 있는 VO 클래스를 만들 수 있습니까? UserVO.java
package com.coderby.myapp.user.model;

public class UserVO {
	private String userId;
	private String userName;
	private String userPassword;
	private String userRole;
	public String getUserId() {
		return userId;
	}
	public void setUserId(String userId) {
		this.userId = userId;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPassword() {
		return userPassword;
	}
	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}
	public String getUserRole() {
		return userRole;
	}
	public void setUserRole(String userRole) {
		this.userRole = userRole;
	}
	@Override
	public String toString() {
		return "UserVO [userId=" + userId + ", userName=" + userName + ", userPassword=" + userPassword + ", userRole="
				+ userRole + "]";
	}
	
}
5. Repository 인터페이스와 클래스를 생성하고 데이터베이스 CRUD 메서드를 추가할 수 있습니까? IUserRepository.java
package com.coderby.myapp.user.dao;

import java.util.List;

import com.coderby.myapp.user.model.UserVO;

public interface IUserRepository {
	void insertUser(UserVO user);

	void updateUser(UserVO user);

	void deleteUser(String userId, String userPassword);
	UserVO selectUser(String userId);
	List<UserVO> selectAllUser();

	String getPassword(String userId);
}

UserRepository.java
package com.coderby.myapp.user.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.coderby.myapp.user.model.UserVO;

@Repository
public class UserRepository implements IUserRepository {

	@Autowired
	JdbcTemplate jdbcTemplate;
	
	@Override
	public void insertUser(UserVO user) {
		String sql = "insert into users (user_id, user_name, user_password, user_role) "
					+"values (?, ?, ?, ?)";
		jdbcTemplate.update(sql, user.getUserId(), user.getUserName(), user.getUserPassword(), user.getUserRole());
	}

	@Override
	public void updateUser(UserVO user) {
		String sql = "update users set user_name=?, user_password=?, user_role=? "
					+"where user_id=?";
		jdbcTemplate.update(sql, user.getUserName(), user.getUserPassword(), user.getUserRole(), user.getUserId());		
	}

	@Override
	public void deleteUser(String userId, String userPassword) {
		String sql = "delete users where user_id=? and user_password=?";
		jdbcTemplate.update(sql, userId, userPassword);
	}

	private class UserMapper implements RowMapper<UserVO> {
		@Override
		public UserVO mapRow(ResultSet rs, int count) throws SQLException {
			UserVO user = new UserVO();
			user.setUserId(rs.getString("user_id"));
			user.setUserName(rs.getString("user_name"));
			user.setUserPassword(rs.getString("user_password"));
			user.setUserRole(rs.getString("user_role"));
			return user;
		}		
	}
	
	@Override
	public UserVO selectUser(String userId) {
		String sql = "select user_id, user_name, user_password, user_role "
					+"from users where user_id=?";
		return jdbcTemplate.queryForObject(sql, new UserMapper(), userId);
	}

	@Override
	public List<UserVO> selectAllUser() {
		String sql = "select user_id, user_name, user_password, user_role "
					+"from users";
		return jdbcTemplate.query(sql, new UserMapper());
	}

	@Override
	public String getPassword(String userId) {
		String sql = "select user_password from users where user_id=?";
		return jdbcTemplate.queryForObject(sql, String.class, userId);
	}

}
6. Service 인터페이스와 클래스를 생성하고 Repository 의존성주입을 통해 클래스를 구현할 수 있습니까? IUserService.java
package com.coderby.myapp.user.service;

import java.util.List;

import com.coderby.myapp.user.model.UserVO;

public interface IUserService {
	void insertUser(UserVO user);

	void updateUser(UserVO user);

	void deleteUser(String userId, String userPassword);
	UserVO selectUser(String userId);
	List<UserVO> selectAllUser();

	boolean checkPassword(String userId, String userPassword);
}
UserService.java
package com.coderby.myapp.user.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.coderby.myapp.user.dao.IUserRepository;
import com.coderby.myapp.user.model.UserVO;

@Service
public class UserService implements IUserService {

	@Autowired
	IUserRepository userRepository;

	@Override
	public void insertUser(UserVO user) {
		userRepository.insertUser(user);
	}

	@Override
	public void updateUser(UserVO user) {
		userRepository.updateUser(user);
	}

	@Override
	public void deleteUser(String userId, String userPassword) {
		userRepository.deleteUser(userId, userPassword);
	}

	@Override
	public UserVO selectUser(String userId) {
		return userRepository.selectUser(userId);
	}

	@Override
	public List<UserVO> selectAllUser() {
		return userRepository.selectAllUser();
	}

	@Override
	public boolean checkPassword(String userId, String userPassword) {
		String password = userRepository.getPassword(userId);
		if(password.equals(userPassword)) {
			return true;
		}else {
			return false;
		}
	}
}
7. 트랜잭션 처리를 할 수 있습니까? <tx:annotation-config/> 서비스 메서드 선언부 위에 @Transactional("txManager") UserService.java
@Service
@Transactional
public class UserService implements IUserService {
----------- 컨트롤러와 뷰 구현 8. 컨트롤러를 작성할 수 있습니까? 그리고 서비스 빈을 의존성 주입할 수 있습니까? UserController.java
@Controller
@RequestMapping("/user")
public class UserController {
	
	private static final Logger logger = LoggerFactory.getLogger(UserController.class);
	
	@Autowired
	IUserService userService;

}
mvc-config.java
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">

	<context:component-scan base-package="com.coderby.myapp.user.controller"/>
	<mvc:annotation-driven/>
	
	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/views/"/>
		<property name="suffix" value=".jsp"/>
	</bean>
	
	<mvc:view-controller path="/" view-name="home"/>
</beans>
9. 시스템의 기능을 URL로 매핑시킬 수 있습니까? - 엑셀 이용