오늘은 Spring 기반에서 AJAX를 활용하여 DB를 불러오고 로그인하는 예제입니다.
파일 구조는 아래와 같이 되어 있습니다. 그리고 아래의 Github 주소에서 관련 파일 확인 가능합니다.
Github : https://github.com/espriter/espriter_blog_practice/tree/master/sweb24_ajax_ex
pack.controller 패키지
** LoginController.java **
package pack.controller;
import javax.servlet.http.HttpSession;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
@Controller
public class LoginController {
@RequestMapping(value="login", method=RequestMethod.GET)
public String login(HttpSession session) {
if(session.getAttribute("id") == null) {
return "loginform";
}else {
return "redirect:/main.jsp";
}
}
@RequestMapping(value="login", method=RequestMethod.POST)
public String submit(HttpSession session,
@RequestParam("id")String id,
@RequestParam("pwd")String pwd) {
if(id.equals("kor") && pwd.equals("111")) {
session.setAttribute("id", id);
return "redirect:/main.jsp";
}else {
return "redirect:/err.jsp";
}
}
@RequestMapping()
public String logout(HttpSession session) {
session.invalidate();
return "redirect:/index.jsp";
}
}
** SangpumController.java **
package pack.controller;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import pack.model.DataInter;
import pack.model.GogekDto;
import pack.model.SawonDto;
@Controller
public class SangpumController {
@Autowired
private DataInter inter;
@RequestMapping("sawon")
@ResponseBody
public Map<String, Object> sawonList(@RequestParam("buser_name")String buser_name){
List<Map<String,String>> dataList = new ArrayList<Map<String,String>>();
Map<String, String> data = null;
List<SawonDto> sawonList = inter.sawonList(buser_name);
if(sawonList.size()==0) {
return null;
}
for(SawonDto d:sawonList) {
data = new HashMap<String,String>();
data.put("sawon_no", d.getSawon_no());
data.put("sawon_name", d.getSawon_name());
data.put("buser_tel", d.getBuser_tel());
data.put("sawon_jik", d.getSawon_jik());
data.put("gogek_ct", d.getGogek_ct());
dataList.add(data);
}
Map<String, Object> sawonDatas = new HashMap<String,Object>();
sawonDatas.put("datas", dataList);
return sawonDatas;
}
@RequestMapping("gogek")
@ResponseBody
public Map<String, Object> gogekList(@RequestParam("sawon_no")String sawon_no){
List<Map<String,String>> dataList = new ArrayList<Map<String,String>>();
Map<String, String> data = null;
List<GogekDto> gogekList = inter.gogekList(sawon_no);
if(gogekList.size()==0) {
return null;
}
for(GogekDto d:gogekList) {
data = new HashMap<String,String>();
data.put("gogek_no", d.getGogek_no());
data.put("gogek_name", d.getGogek_name());
data.put("gogek_tel", d.getGogek_tel());
data.put("gogek_jumin", d.getGogek_jumin());
dataList.add(data);
}
Map<String, Object> gogekDatas = new HashMap<String,Object>();
gogekDatas.put("datas", dataList);
return gogekDatas;
}
}
pack.model 패키지
** DataDao.java **
package pack.model;
import java.util.List;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
@Repository
public class DataDao extends SqlSessionDaoSupport implements DataInter{
@Autowired
public DataDao(SqlSessionFactory factory) {
this.setSqlSessionFactory(factory);
}
public List<SawonDto> sawonList(String buser_name) {
List<SawonDto> list = getSqlSession().selectList("sawonList", buser_name);
return list;
}
public List<GogekDto> gogekList(String sawon_no){
List<GogekDto> list = getSqlSession().selectList("gogekList", sawon_no);
return list;
}
}
** DataInter.java **
package pack.model;
import java.util.List;
public interface DataInter {
public List<SawonDto> sawonList(String buser_name);
public List<GogekDto> gogekList(String sawon_no);
}
** GogekDto.java **
package pack.model;
public class GogekDto {
private String gogek_no, gogek_name, gogek_tel, gogek_jumin;
public String getGogek_no() {
return gogek_no;
}
public String getGogek_jumin() {
return gogek_jumin;
}
public void setGogek_jumin(String gogek_jumin) {
this.gogek_jumin = gogek_jumin;
}
public void setGogek_no(String gogek_no) {
this.gogek_no = gogek_no;
}
public String getGogek_name() {
return gogek_name;
}
public void setGogek_name(String gogek_name) {
this.gogek_name = gogek_name;
}
public String getGogek_tel() {
return gogek_tel;
}
public void setGogek_tel(String gogek_tel) {
this.gogek_tel = gogek_tel;
}
}
** SawonDto.java **
package pack.model;
public class SawonDto {
private String sawon_no, sawon_name, buser_tel, sawon_jik, gogek_ct;
public String getSawon_no() {
return sawon_no;
}
public void setSawon_no(String sawon_no) {
this.sawon_no = sawon_no;
}
public String getSawon_name() {
return sawon_name;
}
public void setSawon_name(String sawon_name) {
this.sawon_name = sawon_name;
}
public String getBuser_tel() {
return buser_tel;
}
public void setBuser_tel(String buser_tel) {
this.buser_tel = buser_tel;
}
public String getSawon_jik() {
return sawon_jik;
}
public void setSawon_jik(String sawon_jik) {
this.sawon_jik = sawon_jik;
}
public String getGogek_ct() {
return gogek_ct;
}
public void setGogek_ct(String gogek_ct) {
this.gogek_ct = gogek_ct;
}
}
pack.resource 폴더
** Configuration.xml **
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
<configuration>
<properties resource="pack/resource/db.properties" />
<typeAliases>
<typeAlias type="pack.model.SawonDto" alias="sdto"/>
<typeAlias type="pack.model.GogekDto" alias="gdto"/>
</typeAliases>
<mappers>
<mapper resource="pack/resource/DataMapper.xml" />
</mappers>
</configuration>
** DataMapper.xml **
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
<mapper namespace="dev">
<select id="sawonList" resultType="sdto" parameterType="string">
select s.sawon_no sawon_no, s.sawon_name sawon_name,
b.buser_tel buser_tel, s.sawon_jik sawon_jik,
(select count(*) from gogek where gogek_damsano = s.sawon_no) gogek_ct
from buser b inner join sawon s on b.buser_no = s.buser_num where b.buser_name = #{buser_name}
</select>
<select id="gogekList" resultType="gdto" parameterType="string">
select * from gogek where gogek_damsano = #{sawon_no};
</select>
</mapper>
** db.properties(예제입니다 / 각자의 환경에 맞게 고치세요) **
driver=org.mariadb.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=123
src 폴더
** loginform.jsp **
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
** 로그인 ** <br>
<form action="login" method="post">
아이디 : <input type="text" name="id"><br>
비밀번호 : <input type="text" name="pwd"><br>
<br>
<input type="submit" value="로그인">
</form>
</body>
</html>
** mvc-config.xml **
<?xml version="1.0" encoding="UTF-8"?>
<!-- Uncomment and your base-package here:
<context:component-scan
base-package="org.springframework.samples.web"/> -->
<context:component-scan base-package="pack.controller"/>
<context:component-scan base-package="pack.model"/>
<mvc:annotation-driven />
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<!-- Example: a logical view name of 'showMessage' is mapped to '/WEB-INF/jsp/showMessage.jsp' -->
<property name="prefix" value="/WEB-INF/view/"/>
<property name="suffix" value=".jsp"/>
</bean>
</beans>
** web.xml **
<?xml version="1.0" encoding="ISO-8859-1"?>
id="WebApp_ID" version="2.5">
<display-name>sweb24_ajax_ex</display-name>
<!--
- Location of the XML file that defines the root application context.
- Applied by ContextLoaderListener.
-->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring/application-config.xml</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!--
- Servlet that dispatches request to registered handlers (Controller implementations).
-->
<servlet>
<servlet-name>dispatcherServlet</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/mvc-config.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>dispatcherServlet</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
</web-app>
<?xml version="1.0" encoding="ISO-8859-1"?>
id="WebApp_ID" version="2.5">
<display-name>sweb24_ajax_ex</display-name>
<!--
- Location of the XML file that defines the root application context.
- Applied by ContextLoaderListener.
-->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring/application-config.xml</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!--
- Servlet that dispatches request to registered handlers (Controller implementations).
-->
<servlet>
<servlet-name>dispatcherServlet</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/mvc-config.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>dispatcherServlet</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
</web-app>
** err.jsp **
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
에러! 아이디와 비밀번호를 입력하세요!!
<br>
<a href="login">로그인</a>
</body>
</html>
** index.jsp **
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<a href="login">로그인</a>
</body>
</html>
** main.jsp **
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script
<script>
$(document).ready(function(){
$("#buserSel").click(function() {
$("#showGogek").empty();
$("#showSawon").empty();
$.ajax({
type:"get",
url:"sawon?buser_name="+$("#buserName").val(),
dataType:"json",
success:function(data){
var list = data.datas;
var str = "<table border='1'><tr><th>직원번호</th><th>직원명</th><th>부서전화</th><th>직급</th><th>관리고객</th></tr>";
$(list).each(function(index, obj){
str += "<tr>";
str += "<td>" + obj["sawon_no"] + "</td>";
if(obj["gogek_ct"] > 0){
str += "<td>" + "<a href='javascript:showGogek("+ obj["sawon_no"] +")'>" +obj["sawon_name"] + "</a></td>";
}else{
str += "<td>" + obj["sawon_name"] + "</td>";
}
str += "<td>" + obj["buser_tel"] + "</td>";
str += "<td>" + obj["sawon_jik"] + "</td>";
str += "<td>" + obj["gogek_ct"] + "</td>";
str += "</tr>"
});
str += "</table>";
$("#showSawon").append(str);
},
error:function(){
$("#showSawon").text("부서명이 잘못되었습니다. 다시 입력해주세요");
}
});
});
});
$(document).on("mouseover", ".gogek", function(){
$("#jumin"+this.id).show();
});
$(document).on("mouseout", ".gogek", function(){
$("#jumin"+this.id).hide();
});
function showGogek(sawon_no){
$("#showGogek").empty();
$.ajax({
type:"get",
url:"gogek?sawon_no="+sawon_no,
dataType:"json",
success:function(data){
var list = data.datas;
var str = "<table border='1'><tr><th>고객번호</th><th>고객명</th><th>고객전화</th></tr>";
$(list).each(function(index, obj){
str += "<tr>";
str += "<td>" + obj["gogek_no"] + "</td>";
str += "<td id='"+obj["gogek_no"] +"' class='gogek'>" + obj["gogek_name"] + "</td>";
str += "<td>" + obj["gogek_tel"] + "</td>";
str += "<td style='display:none;' id='jumin"+obj["gogek_no"] +"'>" + obj["gogek_jumin"] + "</td>";
str += "</tr>"
});
str += "<td colspan='3'>고객수 :"+ list.length +"명</td>"
str += "</table>";
$("#showGogek").append(str);
},
error:function(){
$("#showGogek").text("데이터 접근 오류입니다.");
}
});
}
</script>
</head>
<body>
<%
String id = (String)session.getAttribute("id");
if(id == null) {
response.sendRedirect("login");
return;
}
%>
<%=id %>님 환영합니다.<br>
<a href="logout">로그아웃</a><br>
Json 처리문제 2<p/>
부서명:<input type="text" id="buserName"><input type="button" id="buserSel" value="확인">
<div id="showSawon">
</div>
<div id="showGogek">
</div>
</body>
</html>
그 외
** application-config.xml **
<?xml version="1.0" encoding="UTF-8"?>
<!-- Uncomment and add your base-package here: <context:component-scan base-package="org.springframework.samples.service"/> -->
<bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<value>classpath:pack/resource/db.properties</value>
</property>
</bean>
<bean id="dataSource"
class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
<property name="driverClass" value="${driver}" /><!-- driver는
db.properties의 속성값을 의미 -->
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</bean>
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:pack/resource/Configuration.xml" />
</bean>
</beans>
** pom.xml **
<modelVersion>4.0.0</modelVersion>
<groupId>org.springframework.samples.service.service</groupId>
<artifactId>sweb24_ajax_ex</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<properties>
<!-- Generic properties -->
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<!-- Web -->
<jsp.version>2.2</jsp.version>
<jstl.version>1.2</jstl.version>
<servlet.version>2.5</servlet.version>
<!-- Spring -->
<spring-framework.version>4.3.3.RELEASE</spring-framework.version>
<!-- Logging -->
<logback.version>1.0.13</logback.version>
<slf4j.version>1.7.5</slf4j.version>
</properties>
<dependencies>
<!-- Spring MVC -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring-framework.version}</version>
</dependency>
<!-- Other Web dependencies -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>${jstl.version}</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>${servlet.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>${jsp.version}</version>
<scope>provided</scope>
</dependency>
<!-- Spring and Transactions -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring-framework.version}</version>
</dependency>
<!-- Logging with SLF4J & LogBack -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>${logback.version}</version>
<scope>runtime</scope>
</dependency>
<!--DB -->
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.2.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.0.0.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<!--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>
<!-- json -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.9.5</version>
</dependency>
</dependencies>
</project>
결과 이미지
본 포스팅은 KIC 캠퍼스에서 박영권 강사의 지도하에 공부하며 작성한 리포트입니다.
혹시 잘못된 내용이 있거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.
댓글 영역
SqlSessionFactory 부분이 없으니까 List<SawonDto> List<GogekDto>부분에서 오류가 납니다 ㅠㅠ 살려주세요 ㅠㅠ