ㅇ 데이터베이스 연동 지원 - 템플릿 클래스를 통한 데이터 접근 지원 - 의미있는 예외 클래스 제공 - 트랜잭션 처리

JDBC ㅇ 데이터베이스 연동 지원 - 템플릿 클래스를 통한 데이터 접근 지원 - 의미있는 예외 클래스 제공 - 트랜잭션 처리 ㅇConnection / PreparedStatement / ResultSet / try-catch등 JDBC 중복 코드 제거

JDBC – DataSource 설정 ㅇ JNDI를 이용한 DataSource - Tomcat 등 WAS에서 제공하는 DataSource이용 - 사용 예1) <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:jee=“http://www.springframework.org/schema/jee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd"> <jee:jndi-lookup id=“dataSource” jndi-name=“jdbc/Oracle” resource-ref=“true” />  </beans>

JDBC – DataSource 설정 ㅇ JNDI를 이용한 DataSource - 사용 예2) <bean id=“dataSource” class=“org.springframework.jndi.JndiObjectFactoryBean”> <property name=“jndiName” value=“jdbc/Oracle” /> <property name=“resourceRef” value=“true” /> </bean>

JDBC – DataSource 설정 ㅇDriverManager를 이용한 DataSource - 커넥션 풀이나 JNDI를 사용할 수 없는 경우 - 사용 예) <bean id=“dataSource” class=“org.springframework.jdbc.datasource.DriverManagerDataSource” p:driverClassName=“oracle.jdbc.driver.OracleDriver” p:url=“jdbc:oracle:thin:@localhost:1521:orcl” p:username=“scott” p:password=“tiger” />

JDBC – DataSource 설정 ㅇConnection Pool을 이용한 DataSource - DBCP(Jakarta Commons Database Connection Pool) API 이용 - 사용 예) <bean id=“dataSource” class=“org.apache.commons.dbcp.BasicDataSource” p:driverClassName=“oracle.jdbc.driver.OracleDriver” p:url=“jdbc:oracle:thin:@localhost:1521:orcl” p:username=“scott” p:password=“tiger” /> ※ MySQL - driverClassName com.mysql.jdbc.Driver - url  jdbc:mysql://localhost:3306/DB

JDBC – DataSource 설정 ㅇ필요 라이브러리 - spring-jdbc.jar - spring-tx.jar - commons-dbcp.jar - commons-pool.jar - ojdbc.jar

JDBC – Template 사용 ㅇJdbcTemplate - queryForList() - query() - queryForObject() - queryForMap() - update() 조회 삽입, 수정, 삭제

JDBC – Template 사용 ㅇJdbcTemplate사용 예) @Autowired private JdbcTemplatejdbcTemplate; public void selectBoard() { String sql = "SELECT * FROM BOARD WHERE SEQ_NO = ?"; List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, “1”); for(int i = 0; i < list.size(); i++) { System.out.println(list.get(i).get("SEQ_NO") + "" + list.get(i).get("TITLE")); } } public void insertMember() { String sql = "INSERT INTO MEMBER VALUES (“ + “ ?, ?, ?, SYSDATE)"; jdbcTemplate.update(sql, “seorab”, “password”, “kim”); }

JDBC config.xml <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <context:annotation-config /> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" /> <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" /> <property name="username" value="scott" /> <property name="password" value="tiger" /> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg ref="dataSource" /> </bean> <bean id="boardDao" class="edu.seowon.jdbc.BoardDao" /> </beans>

JDBC Main.java (1 / 2) public class Main { public static void main(String[] args) { ClassPathXmlApplicationContextctx = new ClassPathXmlApplicationContext( "edu/seowon/jdbc/config.xml"); BoardDaodao = (BoardDao) ctx.getBean("boardDao"); List<Map<String, Object>> list = dao.getList(); for(int i = 0; i < list.size(); i++) { System.out.println(list.get(i).get("SEQ_NO") + " // " + (String)list.get(i).get("TITLE")); } List<BoardDto> list2 = dao.getList2(); for(int i = 0; i < list2.size(); i++) { System.out.println(list2.get(i).getSeqNo() + " // " + (String)list2.get(i).getTitle()); }

JDBC Main.java (2 / 2) BoardDto board = dao.get(); System.out.println(board.getSeqNo() + " // " + board.getTitle()); Map<String, String> map = dao.get2(); System.out.println(map.get("seqNo") + " // " + map.get("title")); Map<String, Object> map2 = dao.get3(); System.out.println(map2.get("SEQ_NO") + " // " + map2.get("TITLE")); int count = dao.getTotalCount(); System.out.println(count); dao.insertMember(“id", “password", ＂성명", "0108478"); } }

JDBC BoardDto.java public class BoardDto { private String seqNo; private String title; private String content; private String writeId; private String creDate; public String getSeqNo() { return seqNo; } public void setSeqNo(String seqNo) { this.seqNo = seqNo; } …. 생략 … }

JDBC BoardDao.java (1 / 6) public class BoardDao { @Autowired private JdbcTemplatejdbcTemplate; public List<Map<String, Object>> getList() { String sql = "SELECT * FROM BOARD_M1 WHERE SEQ_NO = ?"; List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, "21"); return list; }

JDBC BoardDao.java (2 / 6) public List<BoardDto> getList2() { String sql = "SELECT * FROM BOARD_M1 WHERE SEQ_NO = ?"; List<BoardDto> list = jdbcTemplate.query(sql, new Object[]{"21"}, new RowMapper<BoardDto>() { @Override public BoardDtomapRow(ResultSetrs, introwNum) throws SQLException { BoardDto board = new BoardDto(); board.setSeqNo(rs.getString("SEQ_NO")); board.setTitle(rs.getString("TITLE")); board.setContent(rs.getString("CONTENT")); board.setWriteId(rs.getString("WRITE_ID")); board.setCreDate(rs.getString("CRE_DATE")); return board; } }); return list; }

JDBC BoardDao.java (3 / 6) public BoardDto get() { String sql = "SELECT * FROM BOARD_M1 WHERE SEQ_NO = ?"; BoardDto board = jdbcTemplate.queryForObject(sql, new Object[]{"21"}, new RowMapper<BoardDto>() { @Override public BoardDtomapRow(ResultSetrs, introwNum) throws SQLException { BoardDto board = new BoardDto(); board.setSeqNo(rs.getString("SEQ_NO")); board.setTitle(rs.getString("TITLE")); board.setContent(rs.getString("CONTENT")); board.setWriteId(rs.getString("WRITE_ID")); board.setCreDate(rs.getString("CRE_DATE")); return board; } }); return board; }

JDBC BoardDao.java (4 / 6) public Map<String, String> get2() { String sql = "SELECT * FROM BOARD_M1 WHERE SEQ_NO = ?"; Map<String, String> map = jdbcTemplate.queryForObject(sql, new Object[]{"21"}, new RowMapper<Map<String, String>>() { @Override public Map<String, String> mapRow(ResultSetrs, introwNum) throws SQLException { Map<String, String> map = new HashMap<String, String>(); map.put("seqNo", rs.getString("SEQ_NO")); map.put("title", rs.getString("TITLE")); return map; } }); return map; }

JDBC BoardDao.java (5 / 6) public Map<String, Object> get3() { String sql = "SELECT * FROM BOARD_M1 WHERE SEQ_NO = ?"; Map<String, Object> map = jdbcTemplate.queryForMap(sql, new Object[]{"21"}); return map; } public intgetTotalCount() { String sql = "SELECT COUNT(*) FROM BOARD_M1 WHERE SEQ_NO = ?"; int count = jdbcTemplate.queryForObject(sql, new Object[]{"21"}, Integer.class); return count; }

JDBC BoardDao.java (6 / 6) public void insertMember(String id, String pw, String name, String phone) { String sql = "INSERT INTO MEMBER_M1 VALUES (?, ?, ?, ?, SYSDATE)"; jdbcTemplate.update(sql, id, pw, name, phone); } }

JDBC 실습 ㅇconfig.xml - dataSource빈 설정 - jdbcTemplate빈 설정 (JdbcTemplate) - ListController빈 선언 - BoardDao빈 선언 ㅇMain.java public class Main { public static void main(String[] args) { ClassPathXmlApplicationContextctx = new ClassPathXmlApplicationContext( "exam/spring/jdbc/config.xml"); Object obj = ctx.getBean("listController"); ((ListController)obj).process(); } }

JDBC 실습 ㅇListController.java - @Autowired를 사용하여 BoardDao주입 - process() 메소드 정의 ㆍboardDao의 selectBoard() 실행 후 List 형태로 결과값 받음 ㆍ위에서 받아온 List 데이터 출력 ㅇBoardDao.java - @Autowired를 사용하여 JdbcTemplate주입 - selectBoard() 메소드 정의 ㆍqueryForList() 메소드를 사용하여 BOARD_M1 테이블 조회

ㅇ 데이터베이스 연동 지원 - 템플릿 클래스를 통한 데이터 접근 지원 - 의미있는 예외 클래스 제공 - 트랜잭션 처리

ㅇ 데이터베이스 연동 지원 - 템플릿 클래스를 통한 데이터 접근 지원 - 의미있는 예외 클래스 제공 - 트랜잭션 처리

Presentation Transcript