- SpringFramework
- JSP / JAVA
- EL/JSTL
- Mybatis
- ORACLE SQL
- HTML/CSS/JavaScript
[코드보기]
<insert id="memberInsert">
INSERT INTO MEMBER_TBL_02
(CUSTNO, CUSTNAME, PHONE, ADDRESS, JOINDATE, GRADE, CITY)
VALUES
((SELECT MAX(CUSTNO) FROM MEMBER_TBL_02)+1, #{custname},#{phone},#{address},#{joindate},#{grade},#{city})
</insert>
@RequestMapping(value="/insertMember.do")
public String insertMember(MemberVO vo) {
s.memberInsert(vo);
return "memberList.do";
}
@Repository
public class MemberDAOImpl implements MemberDAO{
@Autowired
SqlSessionTemplate mybatis;
@Override
public void memberInsert(MemberVO vo) {
mybatis.insert("member-mapper.memberInsert", vo);
}
[코드보기]
<form action="memberList.do">
<select name="ch1">
<option value="custname">이름</option>
<option value="address">주소</option>
</select>
<input type="text" name="ch2" >
<input type="submit" value="검색하기">
</form>
<select id="memberSelectAll" resultType="MemberVO">
SELECT *
FROM MEMBER_TBL_02
<where>
<if test="ch1=='custname'">
AND CUSTNAME LIKE '%'||#{ch2}||'%'
</if>
<if test="ch1=='address'">
AND ADDRESS LIKE '%'||#{ch2}||'%'
</if>
</where>
ORDER BY CUSTNO DESC
</select>
@Override
public List<MemberVO> memberSelectAll(MemberVO vo) {
return mybatis.selectList("member-mapper.memberSelectAll", vo);
}
@RequestMapping(value="/memberList.do")
public String selectMemberAll(MemberVO vo ,Model m) {
m.addAttribute("list", s.memberSelectAll(vo));
return "member_list.jsp";
}
[코드보기]
@Override
public MemberVO memberSelectOne(int idx) {
return mybatis.selectOne("member-mapper.memberSelectOne", idx);
}
@Override
public void updateMember(MemberVO vo) {
mybatis.update("member-mapper.memberUpdate", vo);
}
<select id="memberSelectOne" resultType="MemberVO">
SELECT *
FROM MEMBER_TBL_02
WHERE CUSTNO=#{custno}
</select>
<update id="memberUpdate">
UPDATE MEMBER_TBL_02
SET
CUSTNAME=#{custname},
PHONE=#{phone},
ADDRESS=#{address},
GRADE=#{grade},
CITY=#{city}
WHERE CUSTNO=#{custno}
</update>
@RequestMapping(value="/memberedit.do")
public String selectMemberOne(MemberVO vo ,Model m) {
m.addAttribute("m", s.memberSelectOne(vo.getCustno()));
return "edit.jsp";
}
@RequestMapping(value="/editOk.do")
public String editMember(MemberVO vo) {
s.updateMember(vo);
return "memberList.do";
}
[코드보기]
@Override
public void deleteMember(int idx) {
mybatis.delete("member-mapper.memberDelete", idx);
}
<delete id="memberDelete">
DELETE FROM MEMBER_TBL_02
WHERE CUSTNO=#{custno}
</delete>
@RequestMapping(value="/memberdelete.do")
public String deleteMember(MemberVO vo) {
s.deleteMember(vo.getCustno());;
return "memberList.do";
}
<select id="moneyList" resultType="MoneyVO">
SELECT M1.CUSTNO AS CUSTNO, M1.CUSTNAME AS CUSTNAME, M1.GRADE AS GRADE, SUM(M2.PRICE) AS PRICE
FROM MEMBER_TBL_02 M1
JOIN MONEY_TBL_02 M2
ON M1.CUSTNO = M2.CUSTNO
GROUP BY M1.CUSTNO, M1.CUSTNAME, M1.GRADE
ORDER BY SUM(M2.PRICE) DESC
</select>
package com.batis.biz.project;
import java.util.List;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.batis.biz.common.MoneyVO;
@Repository
public class MoneyDAOImpl implements MoneyDAO {
@Autowired
SqlSessionTemplate mybatis;
@Override
public List<MoneyVO> moneylist(MoneyVO vo) {
return mybatis.selectList("money-mapper.moneyList", vo);
}
}
package com.batis.biz.project;
import java.util.List;
import com.batis.biz.common.MoneyVO;
public interface MoneyService {
List<MoneyVO> moneylist(MoneyVO vo);
}
@RequestMapping(value="/money_list.do")
public String selectMoney(MoneyVO vo ,Model m) {
m.addAttribute("list", ms.moneylist(vo));
return "money_list.jsp";
}