spring- jdbc

2024. 2. 8. 18:18ㆍDaily Codig Reminder

기존방법

4가지 정보 ⇒ connection 연결 ⇒ prepare

스프링 db연결

4가지 propertity 파일저장 ⇒ 4가지 정보를 저장한 datasource

connection pool (커넥션 관리해주는 클래스 ): 커넥선 풀에서 커넥션을 얻어서 사용,

커넥션 사용 후 반납, 다른 커넥션에서 재사용할 수 있게 해줌

 

jdbc dependency 추가하고 maven확인 alt + f5

<!-- https://mvnrepository.com/artifact/com.jslsolucoes/ojdbc6 -->
<dependency>
    <groupId>com.jslsolucoes</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.1.0</version>
</dependency>

 

sql table 만들기

 create table test
( num number(4) primary key,
  username varchar2(10),
  address varchar2(10) );

  insert into test values ( 1, '홍길동' , '서울');
insert into test values ( 2, '이순신' , '전라');
insert into test values ( 3, '유관순' , '서울');
insert into test values ( 4, '강감찬' , '서울');
insert into test values ( 5, '유재석' , '서울');
   commit;

 

 

dao

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import java.sql.Connection;

public class TestDAO {
	//jdbc
	String driver="oracle.jdbc.driver.OracleDriver";
    String url ="jdbc:oracle:thin:@localhost:1521:xe";
    String userid = "scott";
    String passwd = "tiger";
    
    public TestDAO() {
    	try {
			Class.forName(driver);
			System.out.println("드라이버 로딩성공~~~");
		} catch (Exception e) {
			e.printStackTrace();
		}
    }
public ArrayList<TestDTO> select(){
	ArrayList<TestDTO> list = new ArrayList<TestDTO>();
	Connection con = null;
	PreparedStatement pstmt= null;
	ResultSet rs =null;
	try {
		con = DriverManager.getConnection(url, userid, passwd);
		String sql = "select * from test";
		pstmt= con.prepareStatement(sql);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			int n = rs.getInt("num");
			String n2 = rs.getString("username");
			String n3 = rs.getString("address");
			TestDTO dto = new TestDTO(n, n2, n3);
			list.add(dto);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		try {
			if(rs!=null)rs.close();
			if(pstmt!=null)pstmt.close();
			if(con!=null)con.close();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
	}
	return list;
}
}

 

 

main

TestDAO dao = new TestDAO();
		ArrayList<TestDTO> list =dao.select();
		for (TestDTO testDTO : list) {
			System.out.println(list);
		}

 

 

main2

ApplicationContext ctx = new
				GenericXmlApplicationContext("classpath:Test.xml");
		TestDAO p3 =
				(TestDAO)ctx.getBean("testDAO");
		System.out.println(p3.select());

 

 

jdbc dataSource

dependency

<!-- https://mvnrepository.com/artifact/com.jslsolucoes/ojdbc6 -->
<dependency>
    <groupId>com.jslsolucoes</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.1.0</version>
</dependency>

<!--DataSource\uCEE8\uB125\uC158 \uD480 \uCD94\uAC00\uBD80\uBD84 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1</version>
</dependency>

 

 

properties

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
jdbc.userid=scott     
jdbc.passwd=tiger

 

 

dao

public class TestDAO {
//	//jdbc
//	String driver="oracle.jdbc.driver.OracleDriver";
//    String url ="jdbc:oracle:thin:@localhost:1521:xe";
//    String userid = "scott";
//    String passwd = "tiger";
//    
	@Autowired
	DataSource dataSource;
	//드라이버 로딩, 커넥션 연결후 받아서 사용하게 함
    public TestDAO() {
    	
    }
public ArrayList<TestDTO> select(){
	System.out.println("in dao ===="+dataSource);
	ArrayList<TestDTO> list = new ArrayList<TestDTO>();
	Connection con = null;
	PreparedStatement pstmt= null;
	ResultSet rs =null;
	try {
		con = dataSource.getConnection();/////////
		String sql = "select * from test";
		pstmt= con.prepareStatement(sql);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			int n = rs.getInt("num");
			String n2 = rs.getString("username");
			String n3 = rs.getString("address");
			TestDTO dto = new TestDTO(n, n2, n3);
			list.add(dto);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		try {
			if(rs!=null)rs.close();
			if(pstmt!=null)pstmt.close();
			//if(con!=null)con.close();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
	}
	return list;
}
public void delete(int num){
	System.out.println("in dao ===="+dataSource);
	ArrayList<TestDTO> list = new ArrayList<TestDTO>();
	Connection con = null;
	PreparedStatement pstmt= null;
	try {
		con = dataSource.getConnection();/////////
		String sql = "delete  from test where num =?";
		pstmt= con.prepareStatement(sql);
		pstmt.setInt(1, num);
		int n = pstmt.executeUpdate();
		
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		try {
			if(pstmt!=null)pstmt.close();
			//if(con!=null)con.close();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
	}
}
}

auto commit 이 되어 commit 코드가 필요하지 않음

 

bean

<context:annotation-config></context:annotation-config>	
<context:property-placeholder location="classpath:com/config/jdbc.properties"/>
<bean id="myDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.userid}"></property>
<property name="password" value="${jdbc.passwd}"></property>
</bean>
<bean id="testDAO" class="com.dao.TestDAO"></bean>

 

 

main

ApplicationContext ctx = new
				GenericXmlApplicationContext("classpath:com/config/config.xml");
		TestDAO dao =
				(TestDAO)ctx.getBean("testDAO");
		System.out.println(dao.select());
		dao.delete(5);
		ArrayList<TestDTO> list = dao.select();
		for(TestDTO string : list) {
			System.out.println(string);
		}

번호: 1 이름은: 홍길동 주소는: 서울

번호: 2 이름은: 이순신 주소는: 전라

번호: 3 이름은: 유관순 주소는: 서울

번호: 4 이름은: 강감찬 주소는: 서울

 

 

 

jdbc template

dependency

 

<!-- https://mvnrepository.com/artifact/com.jslsolucoes/ojdbc6 -->
<dependency>
    <groupId>com.jslsolucoes</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.1.0</version>
</dependency>

<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1</version>
</dependency>
<!-- 추가 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.22.RELEASE</version>
</dependency>

 

 

dao

public class TestDAO {
   
//	@Autowired
//	DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	//드라이버 로딩, 커넥션 연결후 받아서 사용하게 함
    public TestDAO() {
    	
    }
    public TestDAO(DataSource dataSource){//생성자를 통해 DataSourcre 주입, JdbcTemplate 에 설정
    	this.jdbcTemplate = new JdbcTemplate(dataSource);//DataSource 에서 JdbcTemplate생성
   //데이터베이스 주소 출럭
    	//jdbc Templete 주소 출력
    }
    
    
public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	public void setJdbcTemplate(DataSource dataSource) {
		this.jdbcTemplate =new JdbcTemplate(dataSource);
	}
	
public List<TestDTO> select(){
	System.out.println(jdbcTemplate);
	List<TestDTO>list = jdbcTemplate.query("select * from test",
			new RowMapper<TestDTO>() {
		public TestDTO mapRow(ResultSet  rs , int roNum) throws SQLException{
			TestDTO dto = new TestDTO();
			dto.setNum(rs.getInt(1));
			dto.setUsername(rs.getString(2));
			dto.setAddress(rs.getString(3));
			return dto;
		}
	});
	return list;
			}//end select

}

 

 

selectByName

public List<TestDTO> selectByName(String name){
	System.out.println(jdbcTemplate);
	List<TestDTO>list = jdbcTemplate.query("select * from test where username=?",
			new RowMapper<TestDTO>() {
		public TestDTO mapRow(ResultSet  rs , int roNum) throws SQLException{
			TestDTO dto = new TestDTO();
			dto.setNum(rs.getInt(1));
			dto.setUsername(rs.getString(2));
			dto.setAddress(rs.getString(3));
			return dto;
		}
	}, name);
	return list;
			}//end select

 

 

count

public int selectCount(){
		System.out.println(jdbcTemplate);
		Integer count = jdbcTemplate.queryForObject("select count(*) from test",
				
				
				Integer.class);
		return count;
				}//end select

⇒ 4

 

 

select One

public TestDTO selectOne(int num){
		System.out.println(jdbcTemplate);
		TestDTO dto = jdbcTemplate.queryForObject("select * from test where num =?",
				new RowMapper<TestDTO>() {
			public TestDTO mapRow(ResultSet  rs , int roNum) throws SQLException{
				TestDTO dto = new TestDTO();
				dto.setNum(rs.getInt(1));
				dto.setUsername(rs.getString(2));
				dto.setAddress(rs.getString(3));
				return dto;
			}
		}, num);
		return dto;
				}//end selectOne

System.out.println(dao.selectOne(1));

⇒ 번호: 1 이름은: 홍길동 주소는: 서울

 

insert delete

public void insert(int num, String name, String address){
int n = jdbcTemplate.update("insert into test (num, username, address)values (?,?,?)",
		num, name, address);
System.out.println("insert 갯수: "+ n);
				
				}//end insert
	public void delete(int num){
		int n = jdbcTemplate.update("delete from test where num =?",
				num);
		System.out.println("insert 갯수: "+ n);
						
						}//end insert

⇒ insert 갯수: 1

delete 갯수 : 1

 

insert 하고 rollback

public void insert(int num, String name, String address){
	Connection con = null;
	PreparedStatement pstmt= null;
	try {
		con = DriverManager.getConnection(url, userid, passwd);
		String sql = "insert into test (num, username, address)"+ " values (?,?,?)";
		pstmt= con.prepareStatement(sql);
		pstmt.setInt(1, num);
		pstmt.setString(2, name);
		pstmt.setString(3, address);
		
		con.setAutoCommit(false); //insert 후
		int n = pstmt.executeUpdate();
		System.out.println("insert 갯수 ==== " + n);
		int x= 5/0; //강제 exception
		if (n==1)con.commit();
	} catch (Exception e) {
		try {
			System.out.println("rollback====");
			con.rollback();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		finally {
			try {
				if(pstmt!=null)pstmt.close();
				if(con!=null)con.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}}}}}

dao.insert(7, "zhemfmf", "Wkwnj");

⇒ insert 갯수 ==== 1

rollback====

 

dataresource

tx 처리 ⇒ xml : xml 설정이 어려움

tx처리 ⇒ @ 이용 :tx이 필요한 함수위에 선언하면 끝임

main ⇒ service ⇒ dao ⇒ jdbcTemplate ⇒ dataSource ⇒ propertites

  1. jdbc.propertites 로딩
  2. —> DataSource 생성
  3. tx 터리 dataSource 주입 txManager생성 @Transactional 사용 <tx:annotation-driven txManaer 등록>
  4. DataSource 주입으로 JdbcTemplate 생성
  5. JdbcTemplate 을 Dao 에 주입 생성
  6. dao를 service 에 주입 생성
  7. main 에서 service 를 get 해서 사용
  8. tx 처리 필요함수에 @Transactional 처리

dependency

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>3.1.4.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.jslsolucoes/ojdbc6 -->
<dependency>
    <groupId>com.jslsolucoes</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.1.0</version>
</dependency>

 

 

bean

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location">
<value>classpath:jdbc.propertites</value>
</property>
</bean>
<!--  dataSource 생성 -->
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${driver}"></property>
<property name="url" value="${url}"></property>
<property name="username" value="${username}"></property>
<property name="password" value="${password}"></property>
</bean>
<!--  3. DatatransactionManager  설정 : @Transaction 으로 tx 자릴,ㄹ 위임받아 자동 commit ,rollback 함-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 4. Tx 자리에 annotation 활성화 필요함수에서 @Transactional 처리 -->
<tx:annotation-driven transaction-manager="transactionManager"/>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>

</bean>
<bean id="deptDAO" class="com.dao.ProductDAO">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<bean id= "deptService" class="com.service.ProductService">
<property name="dao" ref="deptDAO"/>
</bean>
</beans>

 

 

dao

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.entity.ProductDTO;
import com.entity.OrderDTO;

public class ProductDAO {

	private JdbcTemplate jdbcTemplate;// new JdbcTemplate(DataSource)//xml에서 작업 
	//properties=> DataSoucre => jdbcTemplate에 주입되어야 사용 가능 
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		//DataSource주입완료된 template set 이용 주입 사용
		this.jdbcTemplate = jdbcTemplate;
	}



    // 전체 상품 목록
	public List<ProductDTO> selectProduct() {
			//template 주소확인 
		String query = "select * from t_product order by pcode";
		return jdbcTemplate.query(query, new RowMapper<ProductDTO>() {

			@Override
			public ProductDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
				ProductDTO dto = new ProductDTO();
				dto.setPcode(rs.getString("pcode"));
				dto.setPname(rs.getString("pname"));
				dto.setPrice(rs.getInt("price"));
				dto.setQuantity(rs.getInt("quantity"));
				return dto;
			}
		});

	}// end select

	 // 전체 주문 목록
		public List<OrderDTO> selectOrder() {

			String query = "select * from t_order order by pcode";
			return jdbcTemplate.query(query, new RowMapper<OrderDTO>() {
				@Override
				public OrderDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
					OrderDTO dto = new OrderDTO();
					 dto.setNum(rs.getInt("num"));
					 dto.setPcode(rs.getString("pcode"));
					 dto.setQnantity(rs.getInt("quantity"));
					return dto;
				}
			});
		}// end select
	
	// 주문
	public void addOrder(String pcode, int quantity) throws Exception{// p01, 3

		String sql = "insert into t_order ( num, pcode, quantity ) values "
				+ "( t_order_seq.nextval, ? , ? )";

		int n = jdbcTemplate.update(sql, pcode, quantity);
		System.out.println("insert 갯수 : "+n);   //ordre테이블 
		//sysout n 
//오더데이블에 insert후 
	//	String sql2 = "update t_product set  quantity = 5  where pcode = ?";
		//String sql2 = "update t_product set  quantity = quantity-?  where pcode = ?";
		String sql2 = "up t_product set  quantity = quantity - ?  where pcode = ?";
		//update쿼리를 잘못 작성하여 에러를 발생시킴  수량감소
		int n2= jdbcTemplate.update(sql2 , quantity, pcode );	
		System.out.println("update 갯수 : "+n2);

	}// end insert


	public void update(int deptno, String dname, String loc) {

		String sql = "update dept set dname = ? , loc = ? where deptno = ?";

		int n = jdbcTemplate.update(sql,  dname, loc, deptno);

	}// end update

	
	public void delete(int deptno) {

		String sql = "delete from dept where deptno = ?";
		int n = jdbcTemplate.update(sql, deptno);
	}// end delete


}

 

 

service

public class ProductService {

	ProductDAO dao;
	public void setDao(ProductDAO dao) {
		this.dao = dao;
	}
	
	@Transactional   //tx처리함  필요함수 위에 붙여줌 //자동 commit, rollback 
	public void addOrder(String pcode, int quantity) throws Exception{
		dao.addOrder(pcode, quantity);
	}
	
	//t_product목록읽기 
	public List<ProductDTO> selectProduct(){
		//sysout dao 주소 확인 
		return dao.selectProduct();
	}


	public ProductDAO getDao() {
		return dao;
	}


	public List<OrderDTO> selectOrder() {
		return dao.selectOrder();
	}


	
	public void update(int deptno, String dname, String loc){
		dao.update(deptno, dname, loc);
	}
	
	public void delete(int deptno){
		dao.delete(deptno);
	}
	
}

 

 

main

public class ProductTest {

	public static void main(String[] args) {
		
		GenericXmlApplicationContext ctx =
				new GenericXmlApplicationContext("Dept.xml");
		
		
		ProductService service = ctx.getBean("deptService", ProductService.class);
		System.out.println("주문전  데이터 =============================================");
		List<ProductDTO> list = service.selectProduct();  // t_product 테이블 전체 데이터 출력
		for (ProductDTO dto : list) {
			System.out.println(dto);
		}
	
		
		// 상품 주문
	System.out.println("p01 상품 3개 주문합니다");
		try {
			service.addOrder("p01", 3);//add시 문제 발생  
			//T_ORDER에  정상  INSERT, T_product update   p01 수량 10-5개 변경 에러 발생 =>에러 발생 롤백
			//T_order에 인서트 확인 할 것 
		//
		} catch (Exception e) {
			System.out.println("에러가 발생하여 롤백처리.");
		}
		System.out.println("주문 후 product table  데이터 =============================================");
		list = service.selectProduct();
		for (ProductDTO dto : list) {
			System.out.println(dto);
		}
		System.out.println("주문 후 OrderTable 데이터 =============================================");
		List<OrderDTO> oList = service.selectOrder();
		for (OrderDTO dto : oList) {
			System.out.println(dto);
		}
	
	}//end main

}//end class

주문전 데이터 =============================================

ProductDTO [pcode=p01, pname=TV, price=1000, quantity=10]

ProductDTO [pcode=p02, pname=iPhone, price=500, quantity=5]

ProductDTO [pcode=p03, pname=G3, price=600, quantity=20]

p01 상품 3개 주문합니다

insert 갯수 : 1

에러가 발생하여 롤백처리.

주문 후 product table 데이터 =============================================

ProductDTO [pcode=p01, pname=TV, price=1000, quantity=10]

ProductDTO [pcode=p02, pname=iPhone, price=500, quantity=5]

ProductDTO [pcode=p03, pname=G3, price=600, quantity=20]

주문 후 OrderTable 데이터 =============================================

 

 

 

 

bean 더 간단히 하기

  1. prtoperty 에 jdbcTemplate 지우기 dao 도 생성 변수 위에 @Autowired 붙이기
  2. dao , service bean 생성 자동 <context:component-scan base-package="com.*"></context:component-scan> @Repository처리: dao 맨 위에 service 맨 위에

 

 

 

'Daily Codig Reminder' 카테고리의 다른 글

parameter  (1) 2024.02.12
mybatis  (1) 2024.02.12
@Autowired, CoC ,@Qualifier ,lifecycle  (1) 2024.02.08
props, autowired  (1) 2024.02.07
spring - property  (1) 2024.02.06

관련글

티스토리툴바