210706_1(iBatis)

예제2

1) blog_user 테이블 생성

2) ibatis config

3) ibatis mapper

4) ibatis client helper

5) 실행 프로그램 작성 실행

 

 

테이블생성

create table blog_user(
user_id varchar2(10) primary key,
user_name varchar2(20),
role varchar2(5),
password varchar2(10)
);

drop table blog_user;

DTO 생성

package ex2.blog;

import java.io.Serializable;

public class BlogUser implements Serializable{
	
	private String userID;
	private String userName;
	private String role;
	private String password;
	
	public String getUserID() {
		return userID;
	}
	public void setUserID(String userId) {
		this.userID = userId;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getRole() {
		return role;
	}
	public void setRole(String role) {
		this.role = role;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	
	

}

database.properties -- 드라이버연동 

#####################
#ORACLE
#####################
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:XE
username=jspuser
password=1234

sqlMapClientManager.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config2.0//EN" 
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
	<properties resource="ex2/blog/database.properties" />

	<settings cacheModelsEnabled="false"
		useStatementNamespaces="true" />
		
	<transactionManager type="JDBC" commitRequired="false">
		<dataSource type="SIMPLE">
			<property name="JDBC.Driver" value="${driver}" />
			<property name="JDBC.ConnectionURL" value="${url}" />
			<property name="JDBC.Username" value="${username}" />
			<property name="JDBC.Password" value="${password}" />

		</dataSource>
	</transactionManager>
	<sqlMap resource="ex2/blog/BlogUser.xml" />

</sqlMapConfig>

BlogUser.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "//ibatis.apache.org//DTD SQL Map 2.0//EN"
 "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="BlogUser">
	<typeAlias alias="user" type="ex2.blog.BlogUser" />
	<!-- bean의 속성과 테이블의 컬럼을 매핑한다. -->
	<resultMap class="user" id="getBlogUserAll">
		<result property="userID" column="USER_ID" />
		<result property="userName" column="USER_NAME" />
		<result property="role" column="ROLE" />
		<result property="password" column="PASSWORD" />

	</resultMap>
	<!-- 여기서부터 입력 선택 수정 삭제 sql 작성합니다 -->




</sqlMap>

헬퍼클래스작성

SqlMapClientManager.java

package ex2.blog;

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

public class SqlMapClientManager {

	private static SqlMapClient sqlMap;

	static {
		try {
			String resource = "ex2/blog/SqlMapClientManager.xml";
			Reader reader = Resources.getResourceAsReader(resource);
			sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
			// TODO: handle exception
		}
	}

	private SqlMapClientManager() {
	}

	public static SqlMapClient getSqlMapClient() {
		return sqlMap;
	}
}

---

BlogUser.xml --Insert 추가

	<insert id="regUser" parameterClass="user">
		INSERT INTO BLOG_USER (USER_ID, USER_NAME, ROLE, PASSWORD)
		VALUES (#userID#, #userName#, #role#, #password#)
	</insert>

IBatisTestInsert.java

package ex2.blog;

import com.ibatis.sqlmap.client.SqlMapClient;

public class IBatisTestInsert {
	
	private static SqlMapClient sqlMap = SqlMapClientManager.getSqlMapClient();
	

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		try {
			
			//새로운 사용자 입력
			BlogUser paramBlogUser = new BlogUser();
		//	BlogUser returnBlogUser = new BlogUser();
			
			paramBlogUser.setUserID("4");
			paramBlogUser.setUserName("김길재");
			paramBlogUser.setRole("user");
			paramBlogUser.setPassword("kkjae");
			// 네임스페이스.아이디 , 파라미터
			sqlMap.insert("BlogUser.regUser", paramBlogUser);
			
			System.out.println("----------------------------");
			System.out.println("데이터 입력완료.");
			System.out.println("----------------------------");


		} catch (Exception e) {
			// TODO: handle exception
		} //catch

	}

}

---

목록가져오기

BlogUser.xml 추가

	<select id="getBlogUserAll" resultMap="getBlogUserAll">
		SELECT USER_ID,
		USER_NAME,
		ROLE,
		PASSWORD
		FROM BLOG_USER
	</select>

IBatisTestList.java

 

package ex2.blog;

import java.util.Iterator;
import java.util.List;

import com.ibatis.sqlmap.client.SqlMapClient;

public class IBatisTestList {
	
	private static SqlMapClient sqlMap = SqlMapClientManager.getSqlMapClient();

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		try {
			System.out.println("-----------------------");
			System.out.println("initial");
			System.out.println("-----------------------");
			
			BlogUser paramBlogUser = new BlogUser();
			BlogUser returnBlogUser = new BlogUser();
			
			List list = sqlMap.queryForList("BlogUser.getBlogUserAll", paramBlogUser);
			Iterator it = list.iterator();
			
			while(it.hasNext()) {
				returnBlogUser = (BlogUser)it.next();
				
				System.out.println("UserID =" + returnBlogUser.getUserID());
				System.out.println("UserName =" + returnBlogUser.getUserName());
				System.out.println("Role =" + returnBlogUser.getRole());
				
				System.out.println("======================\n");

			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}//catch

	}

}

 

 

목록 수정하기 + 출력하기

BlogUser.xml 추가

	<update id="updUser" parameterClass="user">
		UPDATE BLOG_USER SET
		USER_NAME = #userName#,
		ROLE = #role#,
		PASSWORD = #password#
		WHERE
		USER_ID = #userID#
	</update>

IBatisTestEdit.java

package ex2.blog;
import java.util.Iterator;
import java.util.List;

import com.ibatis.sqlmap.client.SqlMapClient;

public class IBatisTestEdit {
	
	private static SqlMapClient sqlMap = SqlMapClientManager.getSqlMapClient();

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		try {
			BlogUser paramBlogUser = new BlogUser();
			BlogUser returnBlogUser = new BlogUser();
			//사용자 수정
			paramBlogUser.setUserID("1");
			paramBlogUser.setUserName("김정수");
			paramBlogUser.setRole("user");
			paramBlogUser.setPassword("kkjae");
			
			sqlMap.update("BlogUser.updUser", paramBlogUser);
			
			System.out.println("--------------------------");
			System.out.println("데이터가 수정 되었습니다.");
			System.out.println("--------------------------");
			
			List list = sqlMap.queryForList("BlogUser.getBlogUserAll", paramBlogUser);
			Iterator it = list.iterator();
			
			while(it.hasNext()) {
				returnBlogUser = (BlogUser)it.next();
				
				System.out.println("UserID =" + returnBlogUser.getUserID());
				System.out.println("UserName =" + returnBlogUser.getUserName());
				System.out.println("Role =" + returnBlogUser.getRole());
				
				System.out.println("======================\n");
			}

		}catch(Exception e) {
			e.printStackTrace();
		}//catch

	}

}

---

삭제하고 출력하기

BlogUser.xml

 

	<!-- 삭제하기 -->
	<delete id="delUser" parameterClass="user">
	DELETE FROM BLOG_USER WHERE USER_ID=#userID#
	</delete>

IBatisTestDelete.java

package ex2.blog;

import java.util.Iterator;
import java.util.List;

import com.ibatis.sqlmap.client.SqlMapClient;

public class IBatisTestDelete {
	
	private static SqlMapClient sqlMap = SqlMapClientManager.getSqlMapClient();

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			BlogUser paramBlogUser = new BlogUser();
			BlogUser returnBlogUser = new BlogUser();
			
			//사용자 삭제
			paramBlogUser.setUserID("1");
			sqlMap.update("BlogUser.delUser", paramBlogUser);
			
			System.out.println("--------------------------");
			System.out.println("삭제되었습니다.");
			System.out.println("--------------------------");

			List list = sqlMap.queryForList("BlogUser.getBlogUserAll", paramBlogUser);
			Iterator it = list.iterator();
			
			while(it.hasNext()) {
				returnBlogUser = (BlogUser)it.next();
				
				System.out.println("UserID =" + returnBlogUser.getUserID());
				System.out.println("UserName =" + returnBlogUser.getUserName());
				System.out.println("Role =" + returnBlogUser.getRole());
				
				System.out.println("======================\n");
			}
		} catch (Exception e) {
			// TODO: handle exception
		}

	}

}

 

 

---

웹어플로된 예제

Spring mvc + iBatis2 미니 방명록

pom.xml 디펜던시 추가

	<!-- ojdbc를 위한 repositories세팅 -->
	<repositories>
		<repository>
			<id>ojdbc14</id>
			<name>oracle.com</name>
			<url>http://maven.geomajas.org/</url>
		</repository>
	</repositories>

	<dependencies>

		<!-- https://mvnrepository.com/artifact/com.oracle.jdbc/ojdbc14 -->
		<dependency>
			<groupId>com.oracle.jdbc</groupId>
			<artifactId>ojdbc14</artifactId>
			<version>10.2.0.1.0</version>
		</dependency>
	
    
    
    
    <dependency>
			<groupId>commons-collections</groupId>
			<artifactId>commons-collections</artifactId>
			<version>3.2.2</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>

 

~~~예제 스프링매핑까지 730

 

create table guestbook(
idx number constraints pk_guestbook_idx primary key,
password varchar2(10),
name varchar2(20),
subject varchar2(100),
content varchar2(500),
write_date varchar2(20)
);

drop sequence guest_idx_seq;
create sequence guestbook_idx_seq start with 1 increment by 1;

insert into guestbook values(
 guestbook_idx_seq.nextval, '1111','홍길동','연습','연습입니다.',to_char(sysdate,'yyyymmdd'));
 
 
 commit -- 웹어플이 아닌 커맨드 모드에서는 오토커밋이 아니므로 commit 해준다.
 
 select * from guestbook;

DTO

package guestbook.vo;

public class GuestbookVo {

	private int idx;
	private String password, name, subject, content, write_date;
	
	public int getIdx() {
		return idx;
	}
	public void setIdx(int idx) {
		this.idx = idx;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSubject() {
		return subject;
	}
	public void setSubject(String subject) {
		this.subject = subject;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getWrite_date() {
		return write_date;
	}
	public void setWrite_date(String write_date) {
		this.write_date = write_date;
	}

}

web.xml 리스너, 서블릿,매핑 등등

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
	id="WebApp_ID" version="4.0">

	<!-- 컨텍스트 로더 리스너 지정 -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>/WEB-INF/applicationContext.*xml</param-value>
	</context-param>

	<servlet>
		<servlet-name>guestbook</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>

	<servlet-mapping>
		<servlet-name>guestbook</servlet-name>
		<url-pattern>*.guest</url-pattern>
	</servlet-mapping>


	<display-name>spring_ibatis</display-name>
	<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>

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
 "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
	<bean id="dataSource"
		class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName">
			<value>oracle.jdbc.driver.OracleDriver</value>
		</property>
		<property name="url">
			<value>jdbc:oracle:thin:@localhost:1521:XE</value>
		</property>
		<property name="username">
			<value>jspuser</value>
		</property>
		<property name="password">
			<value>1234</value>
		</property>
	</bean>

	<bean id="sqlMapClient"
		class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation" value="/WEB-INF/SqlMapConfig" />
	</bean>


</beans>