[24] Spring JDBC 예제
>>>>>>>>>>>>>>>>> Spring JDBC >>>>>>>>>>>>>>>>
- DAO statement 처리 방식
1) JdbcTemplate
2) namedParameterJdbcTemplate
3) SimpleJdbcTemplate
- JdbcTemplate 메소드
1) update() : insert, update, delete 쿼리문
2) queryForInt() : 여러가지 수치함수(sum, count등)에 의해 int type컬럼 값 하나를 가져올때
3) queryForObject() : 하나의 오브젝트 리턴 타입(BbsDTO 클래스등)을 검색해서 가져올때
4) query() : 여러개의 오브젝트 타입(List등)을 가져올때
- RowMapper : 스프링이 호출. List타입으로 결과를 반환하고 싶을때
- Dynamic Web Project : springJDBC 생성
프로젝트 오른쪽 버튼 -> Configure -> Convert to AspectJ Project
- 오라클라이브러리, 스프링라이브러리 /lib/폴더에 복사
-----------------------------------------------[테이블생성]
create table board2(
idx int primary key,
name varchar(10),
email varchar(50),
homepage varchar(50),
title varchar(50),
content varchar(2000),
pwd varchar(10),
wdate date,
hit int
);
create sequence board2_idx_seq;
-----------------------------------------------BbsDTO.java
package part1;
public class BbsDTO {
private int idx;
private String name;
private String email;
private String homepage;
private String title;
private String content;
private String pwd;
private String wdate;
private int hit;
public BbsDTO() {}
//idx,wdate 인자없는 생성자
public BbsDTO(String name, String email, String homepage,
String title, String content, String pwd, int hit) {
this.name = name;
this.email = email;
this.homepage = homepage;
this.title = title;
this.content = content;
this.pwd = pwd;
this.hit = hit;
}
getter와 setter생성
}
-----------------------------------------------Dao.java
package part1;
import java.util.List;
public interface Dao {
public List<BbsDTO> list();
public BbsDTO read();
public int rowCount();
public void insert(BbsDTO dto);
public void update(BbsDTO dto);
public void delete(int num);
}
-----------------------------------------------DaoImp.java
package part1;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
public class DaoImp implements Dao {
private JdbcTemplate jt;
public void setJt(JdbcTemplate jt) {
this.jt = jt;
}
@Override
public List<BbsDTO> list() {
String sql="SELECT * FROM board2 WHERE idx>=? AND idx<=? ORDER BY idx DESC";
Integer it[]=new Integer[2];
it[0]=1;
it[1]=10;
List<BbsDTO> list=jt.query(sql, it,new RowMapper<BbsDTO>() {
@Override
public BbsDTO mapRow(ResultSet rs, int arg1) throws SQLException {
BbsDTO dto=new BbsDTO();
dto.setIdx(rs.getInt("idx"));
dto.setName(rs.getString("name"));
dto.setEmail(rs.getString("email"));
dto.setHomepage(rs.getString("homepage"));
dto.setTitle(rs.getString("title"));
dto.setContent(rs.getString("content"));
dto.setPwd(rs.getString("pwd"));
dto.setWdate(rs.getString("wdate"));
dto.setHit(rs.getInt("hit"));
return dto;
}
});
return list;
}//목록 페이지
@Override
public BbsDTO read() {
String sql="SELECT * FROM board2 WHERE idx=?";
Integer it[]=new Integer[1];
it[0]=1;
BbsDTO bbsdto=jt.queryForObject(sql, it,new RowMapper<BbsDTO>() {
@Override
//new RowMapper<BbsDTO>() anonymous 클래스
public BbsDTO mapRow(ResultSet rs, int arg1)
throws SQLException {
BbsDTO dto=new BbsDTO();
dto.setIdx(rs.getInt("idx"));
dto.setName(rs.getString("name"));
dto.setEmail(rs.getString("email"));
dto.setHomepage(rs.getString("homepage"));
dto.setTitle(rs.getString("title"));
dto.setContent(rs.getString("content"));
dto.setPwd(rs.getString("pwd"));
dto.setWdate(rs.getString("wdate"));
dto.setHit(rs.getInt("hit"));
return dto;
}
});
return bbsdto;
}//상세보기
@Override
public int rowCount() {
String sql="SELECT count(*) FROM board2";
return jt.queryForInt(sql);
}
@Override
public void insert(BbsDTO dto) {
String sql="INSERT INTO board2(idx,name,email,homepage,title,content,pwd,wdate,hit) ";
sql+=" VALUES(board2_idx_seq.nextval,?,?,?,?,?,?,sysdate,?)";
Object args[]=new Object[]{dto.getName(),dto.getEmail(),dto.getHomepage(),dto.getTitle()
,dto.getContent(),dto.getPwd(),dto.getHit()};
jt.update(sql,args);
}
@Override
public void update(BbsDTO dto) {
String sql="UPDATE board2 SET name=? WHERE idx=?";
Object args[]=new Object[]{dto.getName(),dto.getIdx()};
jt.update(sql,args);
}
@Override
public void delete(int num) {
String sql="DELETE FROM board2 WHERE idx=?";
Object args[]=new Object[]{ num };
jt.update(sql,args);
}
}
-----------------------------------------------jdbc.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
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-2.0.xsd">
<!-- 1. dataSource선언 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin://@127.0.0.1:1521:xe"/>
<property name="username" value="hr"/>
<property name="password" value="hr"/>
</bean>
<!-- 2. JdbcTemplate선언 -->
<bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 3. DaoImp선언 -->
<bean id="dao" class="part1.DaoImp">
<property name="jt" ref="jt"/>
</bean>
</beans>
-----------------------------------------------JdbcMain.java
package part1;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
public class JdbcMain {
public static void main(String[] args) {
ApplicationContext context=new FileSystemXmlApplicationContext("src/part1/jdbc.xml");
Dao dao=(Dao)context.getBean("dao");
//갯수
System.out.println(dao.rowCount());
//추가
dao.insert(new BbsDTO("진달래","email","homepate","title","content","pwd",1));
//수정
BbsDTO dto=new BbsDTO();
dto.setIdx(1);
dto.setName("무궁화");
dao.update(dto);
//삭제
dao.delete(3);
//목록
List<BbsDTO> list=dao.list();
for(BbsDTO dto:list)
{
System.out.printf("%d %s %s",dto.getIdx(),dto.getName(),dto.getEmail());
System.out.printf("%s %s %s",dto.getHomepage(),dto.getTitle(),dto.getContent());
System.out.printf("%s %s %d\n",dto.getPwd(),dto.getWdate(),dto.getHit());
}
//상세보기
BbsDTO dto=dao.read();
System.out.printf("%d %s %s",dto.getIdx(),dto.getName(),dto.getEmail());
System.out.printf("%s %s %s",dto.getHomepage(),dto.getTitle(),dto.getContent());
System.out.printf("%s %s %d\n",dto.getPwd(),dto.getWdate(),dto.getHit());
}
}