[Spring] 스프링 정리4 - DB연동

[목차]

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.maven.org/접속

② 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"/>