SPRING #1

김형우·2022년 3월 30일

Spring #2

목록 보기

1/8

// 프로젝트 생성 : boot_20220328
// 프로젝트 생성 위치 : d:/java-workspace/boot_20220328

// 라이브러리 다운로드 pom.xml

		<!-- oracle -->
		<dependency>
			<groupId>com.oracle.database.jdbc</groupId>
			<artifactId>ojdbc8</artifactId>
		</dependency>

		<!-- mybatis -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.2.0</version>
		</dependency>

		<!-- thymeleaf -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>

		<!-- tomcat -->
		<dependency>
			<groupId>org.apache.tomcat.embed</groupId>
			<artifactId>tomcat-embed-jasper</artifactId>
			<scope>provided</scope>
		</dependency>

		<!-- web -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<!-- devtools -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>

        <!-- lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		
		
		<!-- 아래는 자동생성되어 있음 추가할 필요없음 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

// 파일명 : application.properties

# 포트번호
server.port=9090

server.servlet.context-path=/ROOT

# 소스 변경시 서버 자동으로 구동가능
spring.devtools.livereload.enabled=true

# view는 프레임워크 thymeleaf 나중에는 vue.js react.js로 연동
spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html

# 오라클 연동
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@1.234.5.158:11521/xe
spring.datasource.username=ds200
spring.datasource.password=pw200

// 파일명 : HomeController.java

package com.example.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class HomeController {
    
    // 127.0.0.1:9090/ROOT/
    // 127.0.0.1:9090/ROOT/home
    @GetMapping(value = {"/","/home"})
    public String homeGET(){
        return "home";
    }
}

// 파일명 : home.jsp

<!DOCTYPE html>
<html lang="ko" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>home</title>
</head>

<body>
    home화면입니다.
</body>
</html>

// 파일명 : Boot20220328Application.java

package com.example.boot_20220328;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication

// 컨트롤러, 환경설정, 서비스 패키지 등록
@ComponentScan(basePackages = {
	"com.example.controller", 
	"com.example.service",
	"com.example.config"
})
public class Boot20220328Application {

	public static void main(String[] args) {
		SpringApplication.run(Boot20220328Application.class, args);
	}
}

==========================================

// 파일명 : MybatisConfig.java

package com.example.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
public class MybatisConfig {

	@Bean
	public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
		System.out.println("datasource configuration");
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(dataSource);
		
		//mappers 위치 설정
		Resource[] arrResource = new PathMatchingResourcePatternResolver().getResources("classpath:/mappers/*Mapper.xml");
		sqlSessionFactoryBean.setMapperLocations(arrResource);
		return sqlSessionFactoryBean.getObject();
	}
}

// 파일명 : MemberDTO.java

package com.example.dto;

import java.util.Date;
import lombok.Data;

@Data
public class MemberDTO {
    // 이메일
    private String uemail;
    // 암호
    private String upw;
    // 이름
    private String uname;
    // 연락처
    private String uphone;
    // 권한
    private String urole;
    // 등록일
    private Date uregdate;
    
    
    // 날짜포멧을 바꿔서 보관하기 위해
    private String uregdate1;
}

//파일명 : seller/insert.html

<!DOCTYPE html>
<html lang="ko" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>판매자등록</title>
</head>

<body>
    <div style="padding:20px">
        <h3>판매자등록</h3>
        <hr />

        <form th:action="@{/seller/insert}" method="post">
            이메일 : <input type="text" name="uemail" /><br />
            암호 : <input type="password" name="upw" /><br />
            암호확인 : <input type="password" /><br />
            이름 : <input type="text" name="uname" /><br />
            연락처 : <input type="text" name="uphone" /><br />
            권한 : 
            <select name="urole">
                <option value="SELLER">판매자</option>
            </select>
            <br />
            <input type="submit" value="판매자등록" />
        </form>

    </div>
</body>
</html>

// 파일명 : SellerController.java

package com.example.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import com.example.dto.MemberDTO;
import com.example.service.MemberService;

@Controller
@RequestMapping(value="/seller")
public class SellerController {
    @Autowired
    MemberService mService;
    
    // 127.0.0.1:9090/ROOT/seller/insert
    @GetMapping(value="/insert")
    public String insertGET(){
        // templates폴더 seller폴더 insert.html 표시 렌더링
        return "/seller/insert";
    }

    @PostMapping(value="/insert")
    public String insertPOST(@ModelAttribute MemberDTO member){
        System.out.println(member.toString());
        mService.insertMember(member);
        return "redirect:/home"; // 주소를 바꾼다음 엔터키
    }
    
    //127.0.0.1:9090/ROOT/seller/selectlist
    @GetMapping(value="/selectlist")
    public String selectlistGET( Model model ) {
    	List<MemberDTO> list = mService.selectMemberList();
    	model.addAttribute("list", list);
    	return "/seller/selectlist";
    }
    
    // 127.0.0.1:9090/ROOT/seller/delete?email=a
    @RequestMapping(value="/delete", 
    		method = {RequestMethod.GET, RequestMethod.POST })
    public String deleteGETPOST(@RequestParam(name="email") String em) {
    	int ret = mService.deleteMemberOne(em);
    	if(ret == 1) {
    		// 성공시
    	}
    	else {
    		// 실패 시
    	}
    	return "redirect:/seller/selectlist";
    }
    
    // 127.0.0.1:9090/ROOT/seller/update?email=a
    @GetMapping(value = "/update")
    public String updateGET(
    		Model model,
    		@RequestParam(name="email") String em ) {
    	MemberDTO member = mService.selectMemberOne(em);
    	model.addAttribute("obj", member);
    	return "/seller/update";
    }
    
    @PostMapping(value = "/update")
    public String updatePOST(@ModelAttribute MemberDTO member) {
    	System.out.println(member.toString());
    	int ret = mService.updateMemberOne(member);
    	if(ret == 1) {
    		return "redirect:/seller/selectlist";
    	}
    	return "redirect:/seller/update?email=" + member.getUemail();
    }
}

// 파일명 : MemberService.java

package com.example.service;

import java.util.List;

import org.springframework.stereotype.Service;

import com.example.dto.MemberDTO;

@Service
public interface MemberService {
    
}

// 파일명 : MemberServiceImpl.java

package com.example.service;

import com.example.dto.MemberDTO;

import java.util.List;

import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class MemberServiceImpl implements MemberService{

	//xml로 되어 있는 mapper을 호출함.
	@Autowired
	SqlSessionFactory sqlFactory;

    @Override
    public int insertMember(MemberDTO member) {
    	// Member이고 id가 insertMemberOne인 항목을 호출함.
        return sqlFactory.openSession()
        		.insert("Member.insertMemberOne", member);
    }

	@Override
	public List<MemberDTO> selectMemberList() {
		// 네임스페이스명.id명으로 호출함.
		return sqlFactory.openSession()
				.selectList("Member.selectMemberList");
	}

	@Override
	public int deleteMemberOne(String uemail) {
		return sqlFactory.openSession()
				.delete("Member.deleteMemberOne", uemail);
	}

	@Override
	public MemberDTO selectMemberOne(String uemail) {
		return sqlFactory.openSession()
				.selectOne("Member.selectMemberOne", uemail);
	}

	@Override
	public int updateMemberOne(MemberDTO member) {
		return sqlFactory.openSession()
				.update("Member.updateMemberOne", member);
	}
    
}

// 파일명 : memberMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="Member">

	<select id="selectMemberOne" 
		parameterType="String" 
		resultType="com.example.dto.MemberDTO" >
		SELECT M.* FROM MEMBER M WHERE M.UEMAIL=#{uemail}
	</select>

	<select id="selectMemberList" 
		resultType="com.example.dto.MemberDTO">
		SELECT M.*, 
		TO_CHAR(UREGDATE, 'YYYY-MM-DD') UREGDATE1 
		FROM MEMBER M
	</select>

    <insert id="insertMemberOne" 
        parameterType="com.example.dto.MemberDTO">
        INSERT INTO MEMBER( UEMAIL, UPW, UNAME, UPHONE, UROLE)
        VALUES( #{uemail}, #{upw}, #{uname}, #{uphone}, #{urole} )
    </insert>

	<delete id="deleteMemberOne" parameterType="String" >
		DELETE FROM MEMBER WHERE UEMAIL=#{uemail}
	</delete>

	<update id="updateMemberOne" 
		parameterType="com.example.dto.MemberDTO">
		UPDATE MEMBER SET UNAME=#{uname}, UPHONE=#{uphone}
			WHERE UEMAIL=#{uemail}	
	</update>	

</mapper>

// 파일명 : /seller/selectlist.html

<!DOCTYPE html>
<html lang="ko" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>판매자등록</title>
</head>

<body>
    <div style="padding:20px">
        <h3>판매자목록</h3>
        <hr />
        <table border="1">
        	<tr>
        		<th>번호</th>
        		<th>이메일</th>
        		<th>이름</th>
        		<th>연락처</th>
        		<th>권한</th>
        		<th>등록일</th>
        		<th>버튼</th>
        	</tr>		
        	<tr th:each="tmp, idx : ${list}">
        		<td th:text="${idx.count}"></td>
        		<td th:text="${tmp.uemail}"></td>
        		<td th:text="${tmp.uname}"></td>
        		<td th:text="${tmp.uphone}"></td>
        		<td th:text="${tmp.urole}"></td>
        		<td th:text="${tmp.uregdate1}"></td>
        		<td>
        			<form th:action="@{/seller/delete}" method="post">
        				<input type="hidden" name="email" th:value="${tmp.uemail}" />
        				<input type="submit" value="post삭제" />
        			</form>
        			
        			<a th:href="@{/seller/delete(email=${tmp.uemail})}">삭제</a>
        			<a th:href="@{/seller/update(email=${tmp.uemail})}">수정</a>
        		</td>
        	</tr> 
        </table>

    </div>
</body>
</html>

// 파일명 : /seller/update.html

<!DOCTYPE html>
<html lang="ko" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>판매자등록</title>
</head>

<body>
    <div style="padding:20px">
        <h3>판매자등록</h3>
        <hr />

        <form th:action="@{/seller/update}" method="post">
            이메일 : <input type="text" name="uemail" th:value="${obj.uemail}" readonly /><br />
            이름 : <input type="text" name="uname" th:value="${obj.uname}" /><br />
            연락처 : <input type="text" name="uphone" th:value="${obj.uphone}" /><br />
            <br />
            <input type="submit" value="판매자수정" />
        </form>

    </div>
</body>
</html>

// 오류발생 시

Caused by: java.sql.SQLTransientConnectionException: HikariPool-11 - Connection is not available, request timed out after 30000ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
... 105 more

//-- application.properties에 추가

spring.datasource.hikari.connection-test-query=SELECT 1 FROM DUAL
spring.datasource.hikari.connection-timeout=600000
spring.datasource.hikari.maximum-pool-size=500
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.minimum-idle=20
spring.datasource.hikari.validation-timeout=3000
spring.datasource.hikari.idle-timeout=60000

-- 요구사항 분석 참고
http://ihongss.com/pdf/sample1.pdf

김형우

The best

다음 포스트

SPRING #1

Spring #2

SPRING #2

0개의 댓글