티스토리 뷰
교육 7일
ibatis sequence no 예제
guestbook.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap namespace="guestbook"> <insert id="writeBook" parameterClass="model.GuestBookVO"> insert into guestbook(no,title,content) values(no_seq.nextval,#title#,#content#) </insert> <insert id="writeBookAndGetSeq" parameterClass="model.GuestBookVO"> <!-- dual로부터 next sequence 값을 얻어 GuestBookVO의 no에 setting --> <selectKey keyProperty="no" resultClass="int"> SELECT no_seq.nextval FROM DUAL </selectKey> INSERT INTO GUESTBOOK(no,title,content) values(#no#,#title#,#content#) </insert> <select id="getAllBookList" resultClass="model.GuestBookVO"> select no,title,content from guestbook </select> <!-- selectKey --> </sqlMap> |
SqlMapConfig.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <!-- 여러 sql 정의 xml 을 구분해서 사용하기 위해 네임 스페이스를 사용한다. --> <settings useStatementNamespaces="true"/> <!-- database 일반 정보 설정 --> <transactionManager type="JDBC" commitRequired="false"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver"/> <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:xe"/> <property name="JDBC.Username" value="spring"/> <property name="JDBC.Password" value="oracle"/> </dataSource> </transactionManager> <!-- SQL 정의 XML을 링크 --> <sqlMap resource="config/guestbook.xml"/> </sqlMapConfig> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | package factory; import java.io.IOException; import java.io.Reader; import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder; /** * ibatis 설정 파일(SqlMapConfig.xml)을 로딩하여 * SqlMapClient 를 생성하는 클래스 * @author admin * */ public class SqlMapFactory { private static SqlMapFactory factory=new SqlMapFactory(); private SqlMapClient sqlMapClient; private SqlMapFactory(){ // 설정 파일을 로딩해서 SqlMapClient 객체를 얻어 온다. try { Reader reader =Resources.getResourceAsReader("config/SqlMapConfig.xml"); this.sqlMapClient=SqlMapClientBuilder.buildSqlMapClient(reader); System.out.println("ibatis config loading.."+sqlMapClient); } catch (IOException e) { e.printStackTrace(); } } public static SqlMapFactory getInstance(){ return factory; } public SqlMapClient getSqlMapClient() { return sqlMapClient; } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | package model; import java.sql.SQLException; import java.util.List; import com.ibatis.sqlmap.client.SqlMapClient; import factory.SqlMapFactory; public class GuestBookDao { private static GuestBookDao dao=new GuestBookDao(); private SqlMapClient sqlMapClient; private GuestBookDao(){ this.sqlMapClient=SqlMapFactory.getInstance().getSqlMapClient(); } public static GuestBookDao getInstance(){ return dao; } public void writeBook(GuestBookVO vo) throws SQLException { sqlMapClient.insert("guestbook.writeBook", vo); } public List getAllBookList() throws SQLException { return sqlMapClient.queryForList("guestbook.getAllBookList"); } //writeBookAndGetSeq public int writeBookAndGetSeq(GuestBookVO vo) throws SQLException { return (Integer)sqlMapClient.insert("guestbook.writeBookAndGetSeq", vo); } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | package model; public class GuestBookVO { private int no; private String title; private String content; public GuestBookVO() { super(); // TODO Auto-generated constructor stub } public GuestBookVO(int no, String title, String content) { super(); this.no = no; this.title = title; this.content = content; } public int getNo() { return no; } public void setNo(int no) { this.no = no; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } @Override public String toString() { return "GuestBookVO [no=" + no + ", title=" + title + ", content=" + content + "]"; } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | package test; import java.sql.SQLException; import java.util.List; import model.GuestBookDao; import model.GuestBookVO; public class TestGuestBookDao { public static void main(String args[]) throws SQLException{ GuestBookDao dao=GuestBookDao.getInstance(); /*GuestBookVO vo=new GuestBookVO(); vo.setTitle("겨울남자"); vo.setContent("후끈후끈"); dao.writeBook(vo); System.out.println("insert ok..");*/ GuestBookVO vo = new GuestBookVO(); vo.setTitle("봄남자"); vo.setContent("두근두근"); int seq = dao.writeBookAndGetSeq(vo); System.out.println("현재 seq : "+seq); System.out.println(vo); /*List<GuestBookVO> list=dao.getAllBookList(); for(GuestBookVO rvo:list) System.out.println(rvo);*/ // } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | create table guestbook( no number primary key, title varchar2(50) not null, content varchar2(1000) not null ) drop sequence no_seq; create sequence no_seq nocache; -- title : 가을남자 content : 외로워 insert into guestbook(no,title,content) values(no_seq.nextval,'가을남자','외로워'); select * from guestbook; delete from guestbook |
**Transaction(iBatis)**
기존 JDBC 트랜잭션
connection.setAutoCommit(false); --> 기본 true 즉 오토 커밋
try{
.....
.....
con.commit(); // 모두 정상 수행시 db에 실제 적용
}catch(Exception e){
con.rollback(); // 수행시 하나라도 예외 발생하면
// rollback(원상복귀,작업취소)
}
iBatis 트랜잭션 처리
sqlMapClient.startTransaction(); //트랜잭션 처리 시작 autocommit -> false
...
...
sqlMapClient.commitTransaction(); // 커밋 수행
..
sqlMapClient.endTransaction(); // 트랜잭션 종료
// 트랜잭션 종료전 commitTransation() 이 수행되지 않으면
// rollback 된다.
TestTransaction --> ShopService --> CustomerDao
registerAll(){ --> MemberDao
customer register
member register
}
registerAll() 에서는
Customer 정보 등록 및
Member 정보 등록을 함께 한다.
등록시 하나라도 문제 발생하면
둘 다 rollback 되어야 한다.
customer.xml
1 2 3 4 5 6 7 8 | <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap namespace="customer"> <insert id="register" parameterClass="hashmap"> insert into customer values(#SSN#,#NAME#,#ADDRESS#) </insert> </sqlMap> |
member.xml
1 2 3 4 5 6 7 8 | <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap namespace="member"> <insert id="register" parameterClass="model.MemberVO"> insert into member values(#id#,#password#,#name#,#address#) </insert> </sqlMap> |
SqlMapConfig.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <!-- 여러 sql 정의 xml 을 구분해서 사용하기 위해 네임 스페이스를 사용한다. --> <settings useStatementNamespaces="true"/> <!-- database 일반 정보 설정 --> <transactionManager type="JDBC" commitRequired="false"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver"/> <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:xe"/> <property name="JDBC.Username" value="spring"/> <property name="JDBC.Password" value="oracle"/> </dataSource> </transactionManager> <!-- SQL 정의 XML을 링크 --> <sqlMap resource="config/customer.xml"/> <sqlMap resource="config/member.xml"/> </sqlMapConfig> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | package factory; import java.io.IOException; import java.io.Reader; import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder; /** * ibatis 설정 파일(SqlMapConfig.xml)을 로딩하여 * SqlMapClient 를 생성하는 클래스 * @author admin * */ public class SqlMapFactory { private static SqlMapFactory factory=new SqlMapFactory(); private SqlMapClient sqlMapClient; private SqlMapFactory(){ // 설정 파일을 로딩해서 SqlMapClient 객체를 얻어 온다. try { Reader reader =Resources.getResourceAsReader("config/SqlMapConfig.xml"); this.sqlMapClient=SqlMapClientBuilder.buildSqlMapClient(reader); System.out.println("ibatis config loading.."+sqlMapClient); } catch (IOException e) { e.printStackTrace(); } } public static SqlMapFactory getInstance(){ return factory; } public SqlMapClient getSqlMapClient() { return sqlMapClient; } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | package model; import java.sql.SQLException; import java.util.HashMap; import com.ibatis.sqlmap.client.SqlMapClient; import factory.SqlMapFactory; public class CustomerDao { private static CustomerDao dao=new CustomerDao(); private SqlMapClient sqlMapClient; private CustomerDao(){ sqlMapClient=SqlMapFactory.getInstance().getSqlMapClient(); } public static CustomerDao getInstance(){ return dao; } public void register(HashMap cMap) throws SQLException { sqlMapClient.insert("customer.register", cMap); } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | package model; import java.sql.SQLException; import com.ibatis.sqlmap.client.SqlMapClient; import factory.SqlMapFactory; public class MemberDao { private static MemberDao dao=new MemberDao(); private SqlMapClient sqlMapClient; private MemberDao(){ this.sqlMapClient=SqlMapFactory.getInstance().getSqlMapClient(); } public static MemberDao getInstance(){ return dao; } public void register(MemberVO vo) throws SQLException { sqlMapClient.insert("member.register", vo); } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | package model; public class MemberVO { private String id; private String password; private String name; private String address; public MemberVO() { super(); // TODO Auto-generated constructor stub } public MemberVO(String id, String password, String name, String address) { super(); this.id = id; this.password = password; this.name = name; this.address = address; } public String getId() { return id; } public void setId(String id) { this.id = id; } 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 getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "MemberVO [id=" + id + ", password=" + password + ", name=" + name + ", address=" + address + "]"; } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | package model; import java.sql.SQLException; import java.util.HashMap; import com.ibatis.sqlmap.client.SqlMapClient; import factory.SqlMapFactory; public class ShopService { private static ShopService service=new ShopService(); private SqlMapClient sqlMapClient; private ShopService(){ this.sqlMapClient=SqlMapFactory.getInstance().getSqlMapClient(); } public static ShopService getInstance(){ return service; } /* 트랜잭션 처리를 별도로 하지 않으면 * 오토 커밋 속성이므로 * 현재 고객정보 입력 후 * 회원 정보 입력시 문제 발생 해도 * 고객정보는 입력처리가 된다. * -> 둘 다 문제없을 때 입력 되고, * 하나라도 문제가 생기면 두 작업 모두 취소 시키기 위해 * 트랜잭션 처리가 필요하다. */ /*public void registerAll(HashMap cMap, MemberVO vo) throws SQLException { CustomerDao.getInstance().register(cMap); System.out.println("customer insert.."+cMap); MemberDao.getInstance().register(vo); System.out.println("member insert.."+vo); }*/ /** * 트랜잭션 처리 * sqlMapClient.startTransaction(); 트랜잭션 시작, autocommit false * sqlMapClient.commitTransaction(); 커밋 ( 커밋이 수행되어야 db 에 적용) * sqlMapClient.endTransaction(); 트랜잭션 종료 * (종료 이전에 커밋되지 않으면 rollback) * @param cMap * @param vo * @throws SQLException */ public void registerAll(HashMap cMap, MemberVO vo) throws SQLException { try{ sqlMapClient.startTransaction();// 트랜잭션 시작 CustomerDao.getInstance().register(cMap); System.out.println("customer insert.."+cMap); MemberDao.getInstance().register(vo); System.out.println("member insert.."+vo); sqlMapClient.commitTransaction();//커밋 수행되지 않고 종료하면 rollback System.out.println("registerall ok...commit"); }catch(SQLException e){ System.out.println("rollback 처리..."+e.getMessage()); throw e;// 예외 처리 후 다시 호출한 쪽으로 발생시켜 던진다. }finally{ sqlMapClient.endTransaction(); // 종료 } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | package test; import java.sql.SQLException; import java.util.HashMap; import model.MemberVO; import model.ShopService; public class TestTransaction { public static void main(String[] args) throws SQLException{ ShopService service=ShopService.getInstance(); HashMap cMap=new HashMap(); cMap.put("SSN", "88"); cMap.put("NAME", "류현진"); cMap.put("ADDRESS", "LA"); MemberVO vo =new MemberVO("java","abcd","소지섭","동대문시장"); service.registerAll(cMap,vo); System.out.println("insert ok!"); } } |
'Framework > Spring' 카테고리의 다른 글
[Spring & ibatis] 수업 8일-Spring MVC,ibatis (0) | 2013.05.01 |
---|---|
[Spring & ibatis] 수업 7일-2(web) (0) | 2013.04.30 |
[Spring & ibatis] 수업 6일 (0) | 2013.04.29 |
[Spring & ibatis] 수업 5일 (0) | 2013.04.26 |
[Spring & ibatis] 수업 4일 (0) | 2013.04.25 |