4일차 iBatis

action/ListAtion.java

package action;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import model.User;
import conn.Ibatis;

public class ListAction {
	List<User> list;  
	
	public String execute(){
		Ibatis ibatis=new Ibatis();
		try {
			//db에서 모든 글을 가져오기 
			list=ibatis.sqlMapper.queryForList("selectAllUsers");
			
			//select하기  ?			
			Map<String, String> map=new HashMap<String, String>();
			map.put("USER_ID", "^^");
			//list=ibatis.sqlMapper.queryForList("select", map);
			
			// 지우기 
			ibatis.sqlMapper.delete("delete", map);
			
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}   	
		return "success";
	}

	public List<User> getList() {
		return list;  // <s:iterater value="list">로 호출할 것이
	}	
}

conn/Ibatis.java

package conn;

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import model.User;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class Ibatis {
	public static SqlMapClient sqlMapper;	
	static {
		try {
			//sqlMapConfig.xml 파일의 설정내용을 가져온다.
			Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
			//sqlMapConfig.xml의 내용을 적용한 sqlMapper 객체 생성.
			sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
			reader.close(); 
		} catch (IOException e) {
			throw new RuntimeException(
					"SqlMapClient 인스턴스 생성시 예외발생." + e, e);
		}
	}   
}

model/User.java

package model;

public class User {
	private String userId;  
	private String userPW;
	private String userName;
	public String getUserId() {
		return userId;
	}
	public void setUserId(String userId) {
		this.userId = userId;
	}
	public String getUserPW() {
		return userPW;
	}
	public void setUserPW(String userPW) {
		this.userPW = userPW;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	@Override
	public String toString() {
		String result;
		result=String.format("[User:ID=%s,Password=%s,Name=%s]", 
				userId, userPW, userName);
		return result;
	}
}

test/UserTest.java

package test;

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import model.User;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class UserTest {
	private static SqlMapClient sqlMapper;
	static List<User> list;
	static {
		try {
			//sqlMapConfig.xml 파일의 설정내용을 가져온다.
			Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
			//sqlMapConfig.xml의 내용을 적용한 sqlMapper 객체 생성.
			sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
			reader.close(); 
		} catch (IOException e) {
			throw new RuntimeException(
					"SqlMapClient 인스턴스 생성시 예외발생." + e, e);
		}
	}   

	public static List<User> getList() {
		return list;   //${list}  <s:property value="list"> <s:iterator 로 화면에 출력
	}
}

13-1.sql

create table if not exists user(
  USER_ID varchar(20) primary key,
  USER_PW varchar(20),
  USER_NAME varchar(20)
);
insert into user value('^^', 'pppp1111', 'Hong');
insert into user value('-_-', '1111pppp', 'Min');
commit;
insert into user (USER_ID, USER_PW, USER_NAME )
 values ('pinksubean', 'pppp1111', 'park');
insert into user (USER_ID, USER_PW, USER_NAME )
 values ('pinkonejee', '1111pppp', 'kim');

SqlMapConfig.xml

<?xml version="1.0" encoding="euc-kr" ?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" 
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>

  <properties resource="dbconnect.properties" />

  <settings 
    useStatementNamespaces="false"
    cacheModelsEnabled="true" 
    enhancementEnabled="true"
    lazyLoadingEnabled="true" 
    maxRequests="32" 
    maxSessions="10" 
    maxTransactions="5" 
    />

  <transactionManager type="JDBC">
    <dataSource type="DBCP">
      <property name="JDBC.Driver" value="${driver}" />
      <property name="JDBC.ConnectionURL" value="${url}" />
      <property name="JDBC.Username" value="${username}" />
      <property name="JDBC.Password" value="${password}" />
      <property name="JDBC.DefaultAutoCommit" value="false" />
    </dataSource>
  </transactionManager>

   <sqlMap resource="User.xml" />
</sqlMapConfig> 

struts.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
    "-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
    "http://struts.apache.org/dtds/struts-2.0.dtd">

<struts>
  <constant name="struts.i18n.encoding" value="UTF-8" />
  <constant name="struts.devMode" value="true" />
  
  <package name="ch12"  extends="struts-default">

	
	<action name="List" class="action.ListAction">
		<result>list.jsp</result>
	</action>

   </package>    
</struts>

User.xml

<?xml version="1.0" encoding="euc-kr" ?>

<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="User">
  <!--  전화번호부 테이블 DATA0=(user_name) DATA1 DATA2 -->
  <!-- 리절트 맵 정의 -->
  <resultMap id="UserResult" class="model.User">
    <result property="userId" column="USER_ID"/>
    <result property="userPW" column="USER_PW"/>
    <result property="userName" column="USER_NAME"/>
  </resultMap>
  
  <!-- select 쿼리문 정의 -->
  <select id="selectAllUsers" resultMap="UserResult">
    select * from USER
  </select>
   <!-- 상세 내용 보기 -->
     <select id="select" parameterClass="java.util.Map"
             resultMap="UserResult">
             select * from USER where USER_ID=#USER_ID#
     </select>
  
    <!--  글 지우기  -->
  	<delete id="delete" parameterClass="java.util.Map">
  		delete  from USER where USER_ID=#USER_ID#
  	</delete>
  	
  	<!--  글 수정하기 -->
  	<update id="update" parameterClass="java.util.Map">
  		update USER (USER_PW, USER_NAME  ) 
  		set ( #USER_PW#, #USER_NAME#  ) where USER_ID=#USER_ID#
  	</update>
</sqlMap>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>아이바티스 공부</display-name>
 
    
  <filter>
  <filter-name>struts2</filter-name>
  <filter-class>
  org.apache.struts2.dispatcher.FilterDispatcher
  </filter-class>
   <init-param> 
     <param-name>struts.i18n.encoding</param-name>
     <param-value>euc-kr</param-value>
  </init-param> 
  </filter>
  <filter-mapping>
  <filter-name>struts2</filter-name>
  <url-pattern>/*</url-pattern>
  </filter-mapping>  
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
</web-app>

list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags" %>    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
	게시글 목록 <br/>
	<s:iterator value="list">
		<s:property value="userId"/> / 
		<s:property value="userPW"/> /
		<s:property value="userName" />		<hr/>  
	</s:iterator> <br/> 
	private String userId;  
	private String userPW;
	private String userName;
</body>
</html>