[목차]
6. Spring에서 DB 연동
* Maria DB lib 등록
① JAVA의 JDBC를 사용
② DaoSupport 클래스 사용 - spring이 지원
③ MyBatis 사용
[내용]
- Spring api 문서
docs.spring.io/spring/docs/5.0.x/javadoc-api/
* Maria DB lib 등록
② Search창에 mariadb-java-client 검색
③ 버전선택 (2.6.2)
④ Apache Maven 내용 pom.xml에 copy & paste
<!-- MariaDB driver -->
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.6.2</version>
</dependency>⑤ Search창에 spring-boot-starter-jdbc 검색
⑥ 버전선택 (2.3.6.RELEASE)
⑦ Apache Maven 내용 pom.xml에 copy & paste
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.3.6.RELEASE</version>
</dependency>
① JAVA의 JDBC를 사용
- SangpumDto
public class SangpumDto {
private String code, sang, su, dan;
//getter 추가
//setter 추가
}
- SangpumInter
public interface SangpumInter {
ArrayList<SangpumDto> selectList();
}
- SangpumImpl : DB 처리
@Repository // @Componet와 동일. db와 연동할 코드
public class SangpumImpl implements SangpumInter{
private Connection conn;
private PreparedStatement ptmt;
private ResultSet rs;
public SangpumImpl() {
try {
Class.forName("org.mariadb.jdbc.Driver");
} catch (Exception e) {
System.out.println("SangpumImpl err"+e);
}
}
public ArrayList<SangpumDto> selectList() {
ArrayList<SangpumDto> list = new ArrayList<SangpumDto>();
String sql="select * from sangdata";
try {
conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123");
ptmt = conn.prepareStatement(sql);
rs = ptmt.executeQuery();
while(rs.next()) {
SangpumDto dto = new SangpumDto();
dto.setCode(rs.getString("code"));
dto.setSang(rs.getString("sang"));
dto.setSu(rs.getString("su"));
dto.setDan(rs.getString("dan"));
list.add(dto);
}
} catch (Exception e) {
System.out.println("selectList err"+e);
}finally {
try {
if(conn != null) conn.close();
if(ptmt != null) ptmt.close();
if(rs != null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
}@Repository : @Componet와 동일. db와 연동할 코드
- BusinessInter
public interface BusinessInter {
public void dataList();
}
- BusinessImpl
@Service // business logic에 사용
public class BusinessImpl implements BusinessInter{
@Autowired // setter 주입 사용
private SangpumInter sangpumInter;
/* 생성자 주입 사용가능
* @Autowired public BusinessImpl(SangpumInter sangpumInter) {
* this.sangpumInter=sangpumInter; }
*/
public void dataList() {
ArrayList<SangpumDto> list = sangpumInter.selectList();
for(SangpumDto s:list) {
System.out.println(s.getCode()+" "+
s.getSang()+" "+
s.getSu()+" "+
s.getDan());
}
}
}
- main
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("dbinit.xml");
BusinessInter businessInter = (BusinessInter)context.getBean("businessImpl");
businessInter.dataList();
}
- dbinit.xml
<!-- 어노테이션 사용하도록 설정 -->
<context:component-scan base-package="pack.business"/>
<context:component-scan base-package="pack.model"/>
② DaoSupport 클래스 사용
- SangpumDto
- BusinessImpl
- main
동일
- SangpumInter
public interface SangpumInter {
List<SangpumDto> selectList() throws DataAccessException;
}
- SangpumImpl
// Spring이 제공하는 DB 연결처리. JdbcDaoSupport 상속.
public class SangpumImpl extends JdbcDaoSupport implements SangpumInter {
// 생성자 주입으로 처리 가능.
// public SangpumImpl(DataSource dataSource) {
// super.setDataSource(dataSource);
// }
public List<SangpumDto> selectList(/* String code */) throws DataAccessException {
// member : Datasource, JdbcTemplate
RowMapper rowMapper = new SangRowMapper();
return getJdbcTemplate().query("select * from sangdata", rowMapper);
// record수 만큼 mapRow() 호출.
//return getJdbcTemplate().query("select * from sangdata where code=code", rowMapper);
// 매개변수 사용시
}
// 내부 클래스
class SangRowMapper implements RowMapper{ // RowMapper 상속
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
// mapRow는 레코드 수 만큼 호출. rs.next()가 내부적으로 처리됨.
System.out.println("rowNum" + rowNum);
SangpumDto dto = new SangpumDto();
dto.setCode(rs.getString("code"));
dto.setSang(rs.getString("sang"));
dto.setSu(rs.getString("su"));
dto.setDan(rs.getString("dan"));
return dto;
}
}
}
- BusinessInter
public class BusinessImpl implements BusinessInter{
private SangpumInter sangpumInter;
public void setSangpumInter(SangpumInter sangpumInter) {
this.sangpumInter = sangpumInter;
}
public void dataList() {
ArrayList<SangpumDto> list = (ArrayList<SangpumDto>)sangpumInter.selectList();
for(SangpumDto s:list) {
System.out.println(s.getCode()+" "+
s.getSang()+" "+
s.getSu()+" "+
s.getDan());
}
}
}
- dbinit.xml
<!-- db연결 -->
<!-- xml파일이 아니 별도 class로 작성 가능. -->
<bean id="dataSorce" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName">
<value>org.mariadb.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost:3306/test</value>
</property>
<property name="username">
<value>root</value>
</property>
<property name="password">
<value>123</value>
</property>
</bean>
<!-- db연결 클래스 객체 생성 -->
<bean id="sangpumImpl" class="pack.model.SangpumImpl">
<!--JdbcDaoSupport을 상속받았기 때문에 별도로 dataSource setter를 정의하지않아도 setter주입을 할 수 있다.-->
<property name="dataSource" ref="dataSorce"/>
</bean>
<!-- 객체생성 -->
<bean id="businessImpl" class="pack.business.BusinessImpl">
<property name="sangpumInter" ref="sangpumImpl"/>
</bean>
- dbinit.xml
<!-- DriverManagerDataSource를 상속받은 db 연결 class 객체 생성. -->
<bean id="dataSource" class="pack.model.DataSourceMaria"></bean>
<!-- db연결 클래스 객체 생성 -->
<bean id="sangpumImpl" class="pack.model.SangpumImpl">
<!-- JdbcDaoSupport을 상속받았기 때문에 별도로 dataSource setter를 정의하지않아도 setter주입을 할 수 있다. -->
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 객체생성 -->
<bean id="businessImpl" class="pack.business.BusinessImpl">
<property name="sangpumInter" ref="sangpumImpl"/>
</bean>
③ MyBatis 사용
- db.properties
- DataMapper.xml
<mapper namespace="dev">
<select id="selectAll" resultType="dto">
select * from sangdata
</select>
</mapper>
- Configuration.xml
<configuration>
<properties resource="pack/mybatis/db.properties" />
<typeAliases>
<typeAlias type="pack.model.SangpumDto" alias="dto"/>
</typeAliases>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="pack/mybatis/DataMapper.xml" />
</mappers>
</configuration>
- SqlMapConfig
public class SqlMapConfig { // 메뉴얼 확인하여 작성.
public static SqlSessionFactory sqlSession; // DB의 SQL 명령 실행을 위한 메소드를 가진 객체.
static {
String resource = "pack/mybatis/Configuration.xml";
try {
Reader reader = Resources.getResourceAsReader(resource);
// Configuration.xml을 읽는다.
sqlSession = new SqlSessionFactoryBuilder().build(reader);
// sqlSession 객체를 생성한다.
reader.close();
} catch (Exception e) {
System.out.println("SqlMapConfig err"+e);
}
}
public static SqlSessionFactory getSqlSession() {
return sqlSession;
}
}
- SangpumInter
public interface SangpumInter {
List<SangpumDto> selectDataAll();
}
- SangpumImpl
@Repository
public class SangpumImpl implements SangpumInter{
private SqlSessionFactory factory = SqlMapConfig.getSqlSession();
public List<SangpumDto> selectDataAll() {
SqlSession sqlSession = factory.openSession();
List<SangpumDto> list = null;
try {
list=sqlSession.selectList("selectAll");
} catch (Exception e) {
System.out.println("selectDataAll err"+e);
}finally {
if(sqlSession!=null) sqlSession.close();
}
return list;
}
}
- BusinessInter
public interface BusinessInter {
void dataList();
}
- BusinessImpl
@Service
public class BusinessImpl implements BusinessInter{
@Autowired
private SangpumInter inter;
public void dataList() {
List<SangpumDto> list =inter.selectDataAll();
for(SangpumDto s: list) {
System.out.println(s.getCode()+" "+s.getSang()+" "+s.getSu()+" "+s.getDan());
}
}
}
- main
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("dbinit.xml");
BusinessInter businessInter = (BusinessInter)context.getBean("businessImpl");
businessInter.dataList();
}
- dbinit
<context:component-scan base-package="pack.business"/>
<context:component-scan base-package="pack.model"/>
'BACK END > Spring' 카테고리의 다른 글
| [Spring] 스프링 정리6 - Controller 처리 (0) | 2021.01.13 |
|---|---|
| [Spring] 스프링 정리5 - MVC Pattern (0) | 2021.01.13 |
| [Spring] 스프링 정리3 - AOP (Aspect Oriented Program) (0) | 2021.01.12 |
| [Spring] 스프링 정리2 - 생성자 주입(Constructor Injection), setter 주입(setter injection) (0) | 2021.01.12 |
| [Spring] 스프링 정리1 - spring 환경구축 (0) | 2021.01.08 |