[69일] spring (8) - mymelon media 목록

media 테이블 sql

--media_sql.txt

-- 미디어 테이블 생성
CREATE TABLE media (
  mediano  NUMBER        NOT NULL PRIMARY KEY,          -- 미디어 번호
  title    VARCHAR2(255) NOT NULL,                      -- 제목
  poster   VARCHAR2(255) DEFAULT 'poster.jpg' NOT NULL, -- 포스터 이미지
  filename VARCHAR2(255) NOT NULL,                      -- 음원 파일명
  filesize NUMBER        DEFAULT 0 NOT NULL,
  mview    CHAR(1)       DEFAULT 'Y' NOT NULL,          -- 출력모드 y이면 목록에 출력
  rdate    DATE          NOT NULL,                      -- 등록일
  mediagroupno NUMBER    NULL                           -- 부모테이블 PK
);


-- 시퀀스 생성
create sequence media_seq;


commit;


-- 행목록
select * from media
where mview='Y' and mediagroupno=? 
order by mediano desc;

 

패키지와 클래스 생성

 

MediaCont.java

package kr.co.itwill.media;

import org.springframework.stereotype.Controller;

@Controller
public class MediaCont {

	public MediaCont() {
		System.out.println("-----MediaCont() 객체 생성");
	}//end
	
}//class end

확인

 

MediaDAO.java

package kr.co.itwill.media;

import org.springframework.stereotype.Repository;

@Repository
public class MediaDAO {

	public MediaDAO() {
		System.out.println("-----MediaDAO() 객체 생성");
	}//end
	
}//class end

 

MediaCont.java

package kr.co.itwill.media;

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

@Controller
public class MediaCont {

	@Autowired
	private MediaDAO dao;
	
	public MediaCont() {
		System.out.println("-----MediaCont() 객체 생성");
	}//end
	
}//class end

 

MediaDAO.java

package kr.co.itwill.media;

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

@Repository
public class MediaDAO {

	@Autowired
	private JdbcTemplate jt;
	
	public MediaDAO() {
		System.out.println("-----MediaDAO() 객체 생성");
	}//end
	
}//class end

 

MediaDTO.java

package kr.co.itwill.media;

public class MediaDTO {

	private int mediano;
    private String title;
    private String poster;
    private String filename;
    private long filesize;
    private String mview;
    private String rdate;
    private int mediagroupno;
	
    //기본생성자, getter, setter, toString -> lombok.jar
    public MediaDTO() {}

	public int getMediano() {
		return mediano;
	}

	public void setMediano(int mediano) {
		this.mediano = mediano;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getPoster() {
		return poster;
	}

	public void setPoster(String poster) {
		this.poster = poster;
	}

	public String getFilename() {
		return filename;
	}

	public void setFilename(String filename) {
		this.filename = filename;
	}

	public long getFilesize() {
		return filesize;
	}

	public void setFilesize(long filesize) {
		this.filesize = filesize;
	}

	public String getMview() {
		return mview;
	}

	public void setMview(String mview) {
		this.mview = mview;
	}

	public String getRdate() {
		return rdate;
	}

	public void setRdate(String rdate) {
		this.rdate = rdate;
	}

	public int getMediagroupno() {
		return mediagroupno;
	}

	public void setMediagroupno(int mediagroupno) {
		this.mediagroupno = mediagroupno;
	}

	@Override
	public String toString() {
		return "MediaDTO [mediano=" + mediano + ", title=" + title + ", poster=" + poster + ", filename=" + filename
				+ ", filesize=" + filesize + ", mview=" + mview + ", rdate=" + rdate + ", mediagroupno=" + mediagroupno
				+ "]";
	}
    
}//class end

 

list.jsp

추가한 부분

 

 

목록

MediaCont.java

package kr.co.itwill.media;

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.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class MediaCont {

	@Autowired
	private MediaDAO dao;
	
	public MediaCont() {
		System.out.println("-----MediaCont() 객체 생성");
	}//end
	
	@RequestMapping("/media/list.do")
	public ModelAndView list(int mediagroupno) {
		ModelAndView mav = new ModelAndView();
		mav.setViewName("media/list");
		mav.addObject("list", dao.list(mediagroupno));
		mav.addObject("mediagroupno", mediagroupno);
		return mav;		
	}//list() end
	
}//class end

 

MediaDAO.java

package kr.co.itwill.media;

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;

@Repository
public class MediaDAO {

	@Autowired
	private JdbcTemplate jt;
	
	StringBuilder sql = null;
	
	public MediaDAO() {
		System.out.println("-----MediaDAO() 객체 생성");
	}//end
	
	public List<MediaDTO> list(int mediagroupno) {
		List<MediaDTO> list = null;
		try {
			sql = new StringBuilder();
			sql.append(" SELECT mediano, title, poster, filename, filesize, mview, rdate, mediagroupno ");
			sql.append(" FROM media ");
			sql.append(" WHERE mview='Y' and mediagroupno = " + mediagroupno);
			sql.append(" ORDER BY mediano DESC ");
			
			RowMapper<MediaDTO> rowMapper = new RowMapper<MediaDTO>() {
			
				@Override
				public MediaDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
					MediaDTO dto = new MediaDTO();
					dto.setMediano(rs.getInt("mediano"));
					dto.setTitle(rs.getString("title"));
					dto.setPoster(rs.getString("poster"));
					dto.setFilename(rs.getString("filename"));
					dto.setFilesize(rs.getLong("filesize"));
					dto.setMview(rs.getString("mview"));
					dto.setRdate(rs.getString("rdate"));
					dto.setMediagroupno(rs.getInt("mediagroupno"));
					return dto;
				}//mapRow() end
			};//rowMapper end
			
			list = jt.query(sql.toString(), rowMapper);
			
		} catch (Exception e) {
			System.out.println("media 목록 실패 : " + e);
		}//end
		return list;
	}//list() end
	
}//class end

 

list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>list.jsp</title>
	<style>
		*{font-family: gulim; font-size: 24px;}
	</style>
	<link href="../css/style.css" rel="stylesheet" type="text/css">
</head>
<body>
	<div class="title">음원 목록</div>
	<div class="content">
		<input type="button" value="음원등록" onclick="location.href='create.do?mediagroupno=${mediagroupno}'">
		<input type="button" value="HOME" onclick="location.href='/home.do'">
	</div>
	
		<table>
		<tr>
			<th>번호</th>
			<th>제목</th>
			<th>등록일</th>
			<th>음원파일명</th>
			<th>수정/삭제</th>
		</tr>
		
		<c:forEach var="dto" items="${list}">
			<tr>
				<td>${dto.mediano}</td>
				<td>${dto.title}</td>
				<td>${dto.rdate}</td>
				<td>
					${dto.filename}<br>
					${dto.filesize}<br>
					<c:set var="filesize" value="${fn:substringBefore(dto.filesize/1024, '.') }"></c:set>
					${filesize}KB
				</td>
				<td>
					<input type="button" value="수정">
					<input type="button" value="삭제">
				</td>
			</tr>
		</c:forEach>
		
		</table>
	
</body>
</html>

 

 

 

쓰기

MediaCont.java

	@RequestMapping(value = "/media/create.do", method = RequestMethod.GET)
	public ModelAndView createForm(int mediagroupno) {
		ModelAndView mav = new ModelAndView();
		mav.setViewName("media/createForm");
		mav.addObject("mediagroupno", mediagroupno);
		return mav;
	}//createForm() end

 

createForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>createForm.jsp</title>
	<style>
	*{font-family: gulim; font-size: 24px;}
	</style>
	<link href="../css/style.css" rel="stylesheet" type="text/css">
</head>
<body>
	<div class="title">음원 등록</div>
	<form name="frm" method="post" action="create.do" enctype="multipart/form-data">
		<input type="hidden" name="mediagroupno" value="${mediagroupno}"> <!-- 부모글번호 -->
		<table class='table'>
        <tr>
          <th>제목</th>
          <td><input type='text' name='title' size='50'></td>
        </tr>
        <tr>
          <th>포스터</th>
          <td><input type='file' name='posterMF' size='50'></td>    
        </tr>
        <tr>
          <th>미디어 파일</th>
          <td><input type='file' name='filenameMF' size='50'></td>    
        </tr>  
        </table>
        
        <div class="bottom">
			<input type="submit" value="음원등록">
			<input type="button" value="음원목록" onclick="location.href='list.do?mediagroupno=${mediagroupno}'">
			<input type="button" value="HOME" onclick="location.href='/home.do'">
		</div>
        
	</form>

</body>
</html>

 

MediaDAO.java

	public int create(MediaDTO dto) {
		int cnt = 0;
		try {
			sql = new StringBuilder();
			sql.append(" INSERT INTO media(mediano, title, poster, filename, filesize, mediagroupno, rdate ");
			sql.append(" VALUES (media_seq.nextval, ?, ?, ?, ?, ?, sysdate) ");
			cnt = jt.update(sql.toString(), dto.getTitle(), dto.getPoster(), dto.getFilename(), dto.getFilesize(), dto.getMediagroupno()); 
		} catch (Exception e) {
			System.out.println("음원 등록 실패 : " + e);
		}//end
		return cnt;
	}//create() end

 

 

02_스프링부트 JdbcTemplate기반 mymelon프로젝트_media.pdf

1.44MB

pw : itwill