JDBC 연결하기

전체코드

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <groupId>co.jw.sol</groupId>
   <artifactId>AOP</artifactId>
   <version>0.0.1-SNAPSHOT</version>

   <!-- xml에서 사용할 속성들 -->
   <properties>
      <!-- 자바 버전 -->
      <java-version>1.8</java-version>
      <!-- 스프링 버전 -->
      <org.springframework-version>5.1.9.RELEASE</org.springframework-version>
      <!--<org.springframework-version>4.3.25.RELEASE</org.springframework-version> -->
      <org.slf4j-version>1.7.26</org.slf4j-version>
      <ch.qos.logback-version>1.2.3</ch.qos.logback-version>
      <javax.annotation-version>1.3.2</javax.annotation-version>
      <org.aspectj-version>1.9.4</org.aspectj-version>
      <com.oracle-version>11.2.0.3</com.oracle-version>
      <org.apache.commons-version>2.7.0</org.apache.commons-version>
   </properties>

   <!-- Repository 정보 -->
   <repositories>
        <repository>
            <id>oracle</id>
            <name>ORACLE JDBC Repository</name>
            <url>http://maven.jahia.org/maven2</url>
        </repository>
    </repositories>

   <!-- 프로젝트에서 사용할 라이브러리 정보 -->
   <dependencies>
      <!-- spring context -->
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-context</artifactId>
         <version>${org.springframework-version}</version>
      </dependency>
      <!-- slf4j -->
      <dependency>
         <groupId>org.slf4j</groupId>
         <artifactId>slf4j-api</artifactId>
         <version>${org.slf4j-version}</version>
      </dependency>
      <!-- logback -->
      <dependency>
         <groupId>ch.qos.logback</groupId>
         <artifactId>logback-classic</artifactId>
         <version>${ch.qos.logback-version}</version>
         <exclusions>
            <exclusion>
               <groupId>org.slf4j</groupId>
               <artifactId>slf4j-api</artifactId>
            </exclusion>
         </exclusions>
         <scope>runtime</scope>
      </dependency>
      <!-- https://mvnrepository.com/artifact/javax.annotation/javax.annotation-api -->
      <dependency>
         <groupId>javax.annotation</groupId>
         <artifactId>javax.annotation-api</artifactId>
         <version>${javax.annotation-version}</version>
      </dependency>
      <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
      <dependency>
         <groupId>org.aspectj</groupId>
         <artifactId>aspectjweaver</artifactId>
         <version>${org.aspectj-version}</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-jdbc</artifactId>
         <version>${org.springframework-version}</version>
      </dependency>

      <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>12.1.0.2</version>
        </dependency>


      <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
      <dependency>
         <groupId>org.apache.commons</groupId>
         <artifactId>commons-dbcp2</artifactId>
         <version>${org.apache.commons-version}</version>
      </dependency>
   </dependencies>
</project>

 

메인클래스

public class MjdbcClass {
	public static void main(String[] args) {
		
		AnnotationConfigApplicationContext ctx2 = 
				new AnnotationConfigApplicationContext(BBean.class);
                //BBean에 등록된 객체를 생성한다
		
		JdbcDAO dao = ctx2.getBean(JdbcDAO.class);
        //BBean에서 정의한 빈 중에 JdbcDAO타입의 객체를 가저와 dao에 저장한다
		
		JBean b1 = new JBean();		//JBean 객체를 생성한다
		b1.setNum1(2);
		b1.setStr1("새 데이터");	//JBean의 값을 set메서드로 저장한다
		
		dao.in_sert(b1);
        //값을 저장한 JBean을 매개변수로 JdbcDAO의 in_sert(저장)메서드를 호출한다.
		
		JBean b2 = new JBean();
		b2.setNum1(2);
		b2.setStr1("뭐야 데이터");
        //수정메서드
		dao.up_date(b2);
		
		//삭제메서드
		dao.de_lete(2);
		
		//출력메서드
		List<JBean> li = dao.sel_ect();
		for(JBean b3 : li) {
			System.out.println(b3.getNum1());
			System.out.println(b3.getStr1());
		}
		
		ctx2.close();
	}
}

 

BBean.java

@Configuration
@ComponentScan(basePackages = {"co.bm.jdbc.beans", "co.bm.jdbc.db"})
/*  빈으로 사용하겠다고 명시하고, 해당 패키지에서 @Component를 스캔한다
	beans → JBean.java
	db → JdbcDAO.java, MapperClass.java */

public class BBean {

	//함수단위 빈 등록
	@Bean
	public BasicDataSource source() {
    //jdbc 접속을 위한 driverClassName, url, username, password를 source에 담아 반환한다
		
		BasicDataSource source = new BasicDataSource();
		source.setDriverClassName("oracle.jdbc.OracleDriver");
		source.setUrl("jdbc:oracle:thin:@localhost:1521/xepdb1");
		source.setUsername("BM");
		source.setPassword("bm0301");
		
		return source;
	}
	
	//DB에 접속해 쿼리를 전달하는 빈을 등록한다
	@Bean
	public JdbcTemplate db(BasicDataSource source) {
    //source 를 메개변수로 DB에 접속, 쿼리를 전달하는 빈을 등록한다
		JdbcTemplate db = new JdbcTemplate(source);
		return db;
	}
}

 

JBean.java

: DB의 데이터와 동일. DB의 값을 담음

  private int num1;       private String str1; 

 

 

JdbcDAO.java

@Component
public class JdbcDAO {
	
	//JDBC 객체를 주입
	//BBean의 (Jdbc 관리해주는 객체의 주소값)을 JdbcDAO에 (타입으로)자동 주입
	@Autowired
	private JdbcTemplate db;
    /* → JdbcTemplate과 같은 타입으로 등록된 bean인 public JdbcTemplate db(BasicDataSource source) {} 
      을 같은 타입인 db 에 가져다가 주입한다 */
	
	//MapperClass 자동주입
	@Autowired
	private MapperClass mapper;
    /* → MapperClass과 같은 타입으로 등록된 bean(public class MapperClass implements RowMapper<JBean>{})
    	을 같은 타입인 mapper 가져다가 주입한다 */
	
	
	public void in_sert(JBean bean) {
		String sql ="insert into spr_table(num1, str1) values (?, ?) ";
		db.update(sql, bean.getNum1(), bean.getStr1());
	}
	
    
    //DB 출력 메서드
	public List<JBean> sel_ect(){
    //결과 값을 List<>로 반환받는다
    
		String sql ="select num1, str1 from spr_table ";
        //실행할 쿼리문
        
		List<JBean> li = db.query(sql, mapper);
		//아까 테이블로부터 값을 꺼내서 bean에 저장한 것 반환받음
        //쿼리문을 실행해서, MapperClass를 주입받은 mapper bean을 반환받아 li에 저장한다
		return li;
	}
    
    
	public void up_date(JBean bean) {
		String sql ="update spr_table set str1=? where num1=?";
		db.update(sql, bean.getStr1(), bean.getNum1());
	}
	
	public void de_lete(int n) {
		String sql ="delete from spr_table where num1=?";
		db.update(sql, n);
	}
}

 

MapperClass.java

@Component
public class MapperClass implements RowMapper<JBean> {
//RowMapper<JBean>를 상속받아 구현하고, JBean 타입으로 반환한다.

	@Override
	public JBean mapRow(ResultSet rs, int rowNum) throws SQLException {
		
		JBean bean = new JBean();	//JBean 객체를 생성한다
		
		bean.setNum1(rs.getInt("num1"));
		bean.setStr1(rs.getString("str1"));
        //쿼리 결과값 num1과 str1을 JBean의 num1값과 str1에 set메서드로 저장한다
		
		return bean;
        //쿼리문 실행결과를 저장한 JBean을 반환한다
	}
}