DAY 63 - 스프링 프레임워크 - JDBC / HOMEWORK (2024.10.04) (2024.10.07)

스프링과 JDBC

스프링은 JDBC를 비롯하여 ORM프레임워크(MyBatis, hibernate, JPA(Java Persistence API))를 지원한다

스프링의 목표는 인터페이스에 의한 개발인데 DAO는 데이터베이스에서 데이터를 읽거나 쓰는 수단을 제공하기 위해

존재하며, 반드시 인터페이스를 통해 외부에 제공돼야 한다.

서비스 객체는 인터페이스를 통해서 DAO에 접근한다

서비스 객체를 특정 데이터 액세스 구현체에 결합시키지 않음으로써 테스트를 용이하게 한다

DAO인터페이스는 DAO구현과 서비스 객체 사이에서 느슨한 결합이 유지될 수 있게 한다

 

서비스 객체                    → DAO 인터페이스

UserInsertService                       UserDAO

                                                          ↑

                                            DAO 구현 객체

                                                    UserDAOImpl

---

JDBC

1. driver loading

=> 1번 *.jar

 

2. Connection (접속)

=> url, username, pssword

 

1.2 ---> Connection Pool ( 접속자 수 많을 때 - 서버 안에 만들어놓겠다. )

 

3. PreparedStatment

=> SQL

자바와 웹을 구분하자!!

 

서버 안에 Connection Pool

이거 이름을 jdbc/oracle이라고 붙여놓음  jdbc - namespace

이 connection을 20개가량 만들어놨다.

중간에 datasource가 있어야한다.

실행하자마자 모든 것들에대한 Connecion Pool을 datasource가 쥐고있게된다.

반환을 통해 주고받고한다. (close)

 

자바는 소스를 고치게되면 컴파일 동작을 한다. 컴파일하게되며 서버를 재부팅해야한다.(서버를 껐다 켜야한다.)

그래서 자바에서 sql 문장을 분리시키려고했다 ---> 마이바티스

모든 sql문장은 mapper에 있습니다 하는 것

---

스프링은 데이터베이스 연동을 위한 템플릿 클래스를 제공함으로써

Connection, Statement(PreparedStatement), ResultSet등을 생성하고 처리한 다음

close(반환)하는 JDBC의 중복된 코드를 줄일 수 있다

 

JDBC는 무조건 SQLException의 예외만 발생하므로 정확히 Connection에서 발생했는지 아니면 Statement에서 발생했는지 따져봐야한다. JdbcTemplate클래스는 SQLException이 발생하면 스프링이 제공하는 예외 클래스중 알맞는 것으로 변환해서 발생한다.

스프링은 JDBC보다 다양한 예외 계층을 제공하고 어떤 퍼시스턴스 솔루션과도 연관성을 갖지 않는다

스프링을 사용하면 퍼시스턴스 기술과 관계없이 일관성있게 예외를 발생시킬 수 있다

 

스프링의 DataAccessException는 비검사형 예외(try~catch블럭을 사용하지 않아도 컴파일이 되는 예외)로서 반드시 잡아서 처리할 필요가 없다

 

---

[실습]

Project : Chapter04_ANNO

Chapter04_XML

 

Project : Chapter04_XML

Package : user.main

Class : HelloSpring.java - main 메소드

 

Package : user.service

Interface : UserService.java

public void execute(); - 추상메소드

Class : UserInsertService.java

UserSelectService.java

UserUpdateService.java

UserDeleteService.java

 

Package : user.dao

Interface : UserDAO.java

Class : UserDAOImpl.java

 

Package : user.bean

Class : UserDTO.java

 

src/main/resources

Folder : spring

File : applicationContext.xml

db.properties

---

[ Chapter04_XML ] 

 

https://mvnrepository.com/

 

spring jdbc

 

---

commons-collections4

---

common-dbcp2

---

mysql

 

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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>Chapter04_XML</groupId>
  <artifactId>Chapter04_XML</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <build>
    <sourceDirectory>src</sourceDirectory>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.1</version>
        <configuration>
          <release>17</release>
        </configuration>
      </plugin>
    </plugins>
  </build>
  <dependencies>
  	<!-- Spring -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>5.3.37</version>
	</dependency>
	
	<dependency>
	    <groupId>org.projectlombok</groupId>
	    <artifactId>lombok</artifactId>
	    <version>1.18.30</version>
	    <scope>provided</scope>
	</dependency>
	
	<!-- Connection Pool -->
	<dependency>
	    <groupId>org.springframework</groupId>
	    <artifactId>spring-jdbc</artifactId>
	    <version>5.3.37</version>
	</dependency>
	
	<dependency>
	    <groupId>org.apache.commons</groupId>
	    <artifactId>commons-collections4</artifactId>
	    <version>4.4</version>
	</dependency>
	
	<dependency>
	    <groupId>org.apache.commons</groupId>
	    <artifactId>commons-dbcp2</artifactId>
	    <version>2.11.0</version>
	</dependency>
	
	<!-- MySQL -->
	<dependency>
	    <groupId>com.mysql</groupId>
	    <artifactId>mysql-connector-j</artifactId>
	    <version>8.3.0</version>
	</dependency>
  </dependencies>
</project>

---

[ Chapter04_ANNO ]

 

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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>Chapter04_ANNO</groupId>
  <artifactId>Chapter04_ANNO</artifactId>
  <version>0.0.1-SNAPSHOT</version>
    <dependencies>
  	<!-- Spring -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>5.3.37</version>
	</dependency>
	
	<!-- AOP -->
	<dependency>
	    <groupId>org.aspectj</groupId>
	    <artifactId>aspectjweaver</artifactId>
	    <version>1.9.22.1</version>
	    <!--<scope>runtime</scope>  -->
	</dependency>
	<dependency>
	    <groupId>org.aspectj</groupId>
	    <artifactId>aspectjrt</artifactId>
	    <version>1.9.22.1</version>
	    <scope>runtime</scope>
	</dependency>

	<dependency>
	    <groupId>org.projectlombok</groupId>
	    <artifactId>lombok</artifactId>
	    <version>1.18.30</version>
	    <scope>provided</scope>
	</dependency>
  </dependencies>
</project>

---

Project : Chapter04_XML

src/main/resources

Folder : spring

File : applicationContext.xml

          db.properties

총 5개 !!

---

db.properties

##Oracle
#jdbc.driver=oracle.jdbc.driver.OracleDriver
#jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
#jdbc.username=C##JAVA
#jdbc.password=1234

##MySQL
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Seoul
jdbc.username=root
jdbc.password=1234

## MySQL - Naver Cloud
#jdbc.driver=com.mysql.cj.jdbc.Driver
#jdbc.url=jdbc:mysql://db-pf2ak-kr.vpc-pub-cdb.ntruss.com:3306/studydb?serverTimezone=Asia/Seoul
#jdbc.username=study
#jdbc.password=bitcamp123!@#

 

---

Folder: sql

File: usertable.sql

##Oracle
create table usertable(
name varchar2(30) not null,
id varchar2(30) primary key,
pwd varchar2(30) not null);

##MySQL
create table usertable(
name varchar(30) not null,
id varchar(30) primary key,
pwd varchar(30) not null);

SELECT * FROM USERTABLE;

---

applicationContext.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"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

	<bean id="helloSpring" class="user.main.HelloSpring"></bean>

</beans>

---

인터페이스

Package : user.service

Interface : UserService.java

public void execute(); - 추상메소드

package user.service;

public interface UserService {
	public void execute();
}

---

메인함수

Package : user.main

Class : HelloSpring.java - main 메소드

spring/applicationContext.xml ---> spring 꼭 써주기

package user.main;

import java.util.Scanner;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import user.service.UserService;

public class HelloSpring {

	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext("spring/applicationContext.xml");
		HelloSpring helloSpring = (HelloSpring) context.getBean("helloSpring");
		helloSpring.menu(context);
		System.out.println("프로그램 종료합니다.");
	}
	
	public void menu(ApplicationContext context) {
		Scanner scan = new Scanner(System.in);	
		UserService userService = null;
		int num;
		while (true) { 
			System.out.println();
            System.out.println("**************");
            System.out.println("1. 입력");
            System.out.println("2. 출력");
            System.out.println("3. 수정");
            System.out.println("4. 삭제");
            System.out.println("5. 끝");
            System.out.println("**************");
            System.out.print("번호 : ");
            num = scan.nextInt();
            
            if (num == 5) {                
                break;
            } else if (num == 1)
            	userService = (UserService) context.getBean("userInsertService");
            else if (num == 2)
            	userService = (UserService) context.getBean("userSelectService");               
            else if (num == 3)
            	userService = (UserService) context.getBean("userUpdateService");             
            else if (num == 4)
            	userService = (UserService) context.getBean("userDeleteService");  

            userService.execute();
        }
		
	}
}

---

applicationContext.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"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

	<bean id="helloSpring" class="user.main.HelloSpring"></bean>
	<bean id="userInsert" class="user.service.UserInsertService"></bean>
	<bean id="userSelect" class="user.service.UserSelectService"></bean>
	<bean id="userUpdate" class="user.service.UserUpdateService"></bean>
	<bean id="userDelete" class="user.service.UserDeleteService"></bean>
</beans>

---

DTO

Package : user.bean

Class : UserDTO.java

package user.bean;

import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
public class UserDTO {
	private String name;
	private String id;
	private String pwd;
	
	public String toString() {
		return name + "\t" + id + "\t" + pwd;
	}
}

---

입력

Class : UserInsertService.java

package user.service;

import java.util.Scanner;

import user.bean.UserDTO;

public class UserInsertService implements UserService {
	private UserDTO userDTO;
	
	public void setUserDTO(UserDTO userDTO) {
		this.userDTO = userDTO;
	}

	@Override
	public void execute() {
		System.out.println();
		Scanner scan = new Scanner(System.in);	
		
		System.out.print("이름 입력 : ");
		String name= scan.next();
		System.out.print("아이디 입력 : ");
		String id= scan.next();
		System.out.print("비밀번호 입력 : ");
		String pwd = scan.next();
				
		userDTO.setName(name);
		userDTO.setId(id);
		userDTO.setPwd(pwd);
	}

}

---

applicationContext.xml

<bean id="userDTO" class="user.bean.UserDTO" scope="prototype"></bean>

	<bean id="userInsert" class="user.service.UserInsertService" scope="prototype">
		<property name="userDTO" ref="userDTO"></property>
	</bean>

---

UserInsertService.java

UserDAO userDAO = new UserDAOImpl(); <-- 빈으로 등록, 싱글톤

public class UserInsertService implements UserService {
	private UserDTO userDTO;
	private UserDAO userDAO;
	
	public void setUserDTO(UserDTO userDTO) {
		this.userDTO = userDTO;
	}

	public void setUserDAO(UserDAO userDAO) {
		this.userDAO = userDAO;
	}

---

applicationContext.xml

<bean id="userDAOImpl" class="user.dao.impl.UserDAOImpl"></bean>

	<bean id="userInsertService" class="user.service.UserInsertService" scope="prototype">
		<property name="userDTO" ref="userDTO"></property>
		<property name="userDAO" ref="userDAOImpl"></property>
	</bean>

---

UserInsertService.java

		//DB
		userDAO.write(userDTO);
		
		System.out.println(name + "님의 데이터를 저장하였습니다.");

---

Package: user.dao

Interface: UserDAO.java

package user.dao;

import user.bean.UserDTO;

public interface UserDAO {

	public void write(UserDTO userDTO);

}

---

Package: user.dao.impl

Class: UserDAOImpl.java

package user.dao.impl;

import user.bean.UserDTO;
import user.dao.UserDAO;

public class UserDAOImpl implements UserDAO {

	@Override
	public void write(UserDTO userDTO) {
		// TODO Auto-generated method stub
		
	}
	
}

---

먼저 applicationContext.xml에서 Connection Pool부터 해줘야한다 !!!

Connection Pool   ------------   DataSource ------------ 클라이언트

 

 

1. 커넥션풀을 이용한 DataSource 설정

스프링이 직접적으로 커넥션풀을 제공하진 않지만 DBCP(Jakarta Commons Database Connection Pool) API와 같은 커넥션 풀 라이브러리를 이용

DBCP에는 풀링 기능을 제공하는 다양한 데이터 소스가 포함되 있지만 BasicDataSource가 가장 많이 사용된다

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />

        <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />

        <property name="username" value="" />

        <property name="password" value="" />

        <property name="initialSize" value="5" />

         → 해당 풀이 시작될 때 생성할 커넥션 수, 0은 무제한

         <property name="maxActive" value="10" />

         → 해당 풀에서 동시에 제공할 수 있는 최대 커넥션 수, 0은 무제한

</bean>

 

applicationContext.xml

<!-- Connection Pool & DataSource -->

• 방법1

	<!-- Connection Pool & DataSource -->
	<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
		<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Seoul" />
		<property name="username" value="root" />
		<property name="password" value="1234"></property>
	</bean>

---

• 방법2

	<bean id="dataSource" 
	      class="org.apache.commons.dbcp2.BasicDataSource"
	      p:driverClassName="com.mysql.cj.jdbc.Driver"
	      p:url="jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Seoul"
	      p:username="root"
	      p:password="1234" />

---

• 방법3

감추는 방법 !

	<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
		<property name="driverClassName" value="${jdbc.driver}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}"></property>
	</bean>

 

properties가 어디있는지 알려줘야한다 !!

<context:property-placeholder location="classpath:spring/db.properties"/>

---

db.properties

##MySQL
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Seoul
jdbc.username=root
jdbc.password=1234

 

방법3을 가장 선호한다 !!

---

스프링은 데이터베이스 연동을 위한 템플릿 클래스를 제공함으로써

Connection, Statement(PreparedStatement), ResultSet등을 생성하고 처리한 다음 close(반환)하는 JDBC의 중복된 코드를 줄일 수 있다

 

위에 모든 작어들을 템플릿클래스가 해준다 !!

---> Connection, Statement(PreparedStatement), ResultSet 이거 할 필요 X

 

UserDAOImpl.java

package user.dao.impl;

import org.springframework.jdbc.core.JdbcTemplate;

import user.bean.UserDTO;
import user.dao.UserDAO;

public class UserDAOImpl implements UserDAO {
	private JdbcTemplate jdbcTemplate = null;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public void write(UserDTO userDTO) {
		// TODO Auto-generated method stub
		
	}
	
}

---

applicationContext.xml

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"></bean>

내가 만든 커넥션 풀을 줘야 연결을 하므로

 

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg ref="dataSource"></constructor-arg>
	</bean>

위에처럼 코드를 추가해줘야한다.

jdbcTemplate을 UserDAO에게 줘야한다.

 

---

public class UserDAOImpl implements UserDAO {
           private JdbcTemplate jdbcTemplate = null;

           public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
                    this.jdbcTemplate = jdbcTemplate;
           }

	<bean id="userDAOImpl" class="user.dao.impl.UserDAOImpl">
		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>

---

UserDAOImpl.java

package user.dao.impl;

import org.springframework.jdbc.core.JdbcTemplate;

import user.bean.UserDTO;
import user.dao.UserDAO;

public class UserDAOImpl implements UserDAO {
	private JdbcTemplate jdbcTemplate = null;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public void write(UserDTO userDTO) {
		String sql = "insert into usertable values(?,?,?)";
		jdbcTemplate.update(sql, userDTO.getName(), userDTO.getId(), userDTO.getPwd()); //insert, update, delete
		
	}
	
}

 

close도 다 jdbcTemplate이 해준다.

 

SELECT * FROM USERTABLE;

---

출력

applicationContext.xml

	<bean id="userSelectService" class="user.service.UserSelectService">
		<property name="userDAO" ref="userDAOImpl" />
	</bean>

---

UserSelectService.java

package user.service;

import java.util.List;

import lombok.Setter;
import user.bean.UserDTO;
import user.dao.UserDAO;

public class UserSelectService implements UserService {
	@Setter
	private UserDAO userDAO;
	@Override
	public void execute() {
		//DB
		List<UserDTO> list = userDAO.getUserList();

		System.out.println("이름\t아이디\t비밀번호\t");
		for(UserDTO userDTO : list) {
			System.out.println(userDTO);
		}
	}

}

---

UserDAOImpl.java

이제는 하나하나 꺼내서 DTO에 넣지 않아도 된다.

BeanPropertyRowMapper 이걸 통해서 컬럼에서 꺼내온걸  DTO에 넣어라

	@Override
	public List<UserDTO> getUserList() {
		String sql = "select * from usertable";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<UserDTO>(UserDTO.class));
	}

Generic을 안 걸면 Object로 가기 때문에 꼭 해줘야한다. --- <UserDTO>

---

수정

applicationContext.xml

	<bean id="userUpdateService" class="user.service.UserUpdateService">
		<property name="userDTO" ref="userDTO" />
		<property name="userDAO" ref="userDAOImpl" />
	</bean>

---

UserUpdateService.java

package user.service;

import java.util.List;
import java.util.Scanner;

import lombok.Setter;
import user.bean.UserDTO;
import user.dao.UserDAO;

public class UserUpdateService implements UserService {
	@Setter
	private UserDTO userDTO;
	@Setter
	private UserDAO userDAO;
	
	@Override
	public void execute() {
		System.out.println();
		Scanner scan = new Scanner(System.in);	
		
		System.out.print("수정할 아이디 입력 : ");
		String id = scan.next();
		
		UserDTO userDTO = userDAO.getExistId(id);

아이디가 존재하는지 먼저 확인 !!

---

UserDAO.java

package user.dao;

import java.util.List;

import user.bean.UserDTO;

public interface UserDAO {

	public void write(UserDTO userDTO);

	public List<UserDTO> getUserList();

	public void update(UserDTO userDTO);

	public void delete(UserDTO userDTO);

	public UserDTO getExistId(String id);

}

---

UserDAOImpl.java

return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<UserDTO>(UserDTO.class));

이런식으로 query로 가져오면 list값을 가지고 와서 다르게 해야한다.

	@Override
	public UserDTO getExistId(String id) {
		String sql = "select * from usertable where id = ?";
		return getJdbcTemplate().queryForObject(
				sql, 
				new BeanPropertyRowMapper<UserDTO>(UserDTO.class), 
				id);
	}

 

수정할 아이디가 없을 때는 error가 발생하게 된다 !!

반환할 값이 없으면 null값을 return하는 try-catch문을 거는 것이다 !!

	@Override
	public UserDTO getExistId(String id) {
		String sql = "select * from usertable where id = ?";
		try {
		return getJdbcTemplate().queryForObject(
				sql, 
				new BeanPropertyRowMapper<UserDTO>(UserDTO.class), 
				id);
		}catch(EmptyResultDataAccessException e) {
			return null;
		}
	}

---

UserUpdateService.java

	@Override
	public void execute() {
		System.out.println();
		Scanner scan = new Scanner(System.in);	
		
		System.out.print("수정할 아이디 입력 : ");
		String id = scan.next();
		
		UserDTO userDTO = userDAO.getExistId(id);
		
		if(userDTO == null) {
			System.out.println("해당 아이디가 존재하지 않습니다.");
			return;
		}

		System.out.println("이름\t아이디\t비밀번호\t");
		System.out.println(userDTO);
		
		System.out.print("수정할 이름 입력: ");
		String name = scan.next();
		
		System.out.print("수정할 비밀번호 입력: ");
		String pwd = scan.next();
		
		userDTO.setName(name);
		userDTO.setPwd(pwd);
		
		userDAO.update(userDTO);
		System.out.println(name + "님의 데이터를 수정하였습니다.");

---

UserDAO.java

package user.dao;

import java.util.List;

import user.bean.UserDTO;

public interface UserDAO {

	public void write(UserDTO userDTO);

	public List<UserDTO> getUserList();

	public UserDTO getExistId(String id);
    
	public void update(UserDTO userDTO);

}

---

UserDAOImpl.java

	@Override
	public void update(UserDTO userDTO) {
		String sql = "update usertable set name=?, pwd=? where id=?";
		jdbcTemplate.update(sql, userDTO.getName(), userDTO.getPwd(), userDTO.getId());
		
	}

---

삭제

	<bean id="userDeleteService" class="user.service.UserDeleteService">
		<property name="userDAO" ref="userDAOImpl" />
	</bean>

 

---

UserDeleteService.java

package user.service;

import java.util.Scanner;

import lombok.Setter;
import user.bean.UserDTO;
import user.dao.UserDAO;

public class UserDeleteService implements UserService {
	@Setter
	private UserDAO userDAO;
	@Override
	public void execute() {
		System.out.println();
		Scanner scan = new Scanner(System.in);	
		
		System.out.print("삭제할 아이디 입력 : ");
		String id = scan.next();

		UserDTO userDTO = userDAO.getExistId(id);
		
		if(userDTO == null) {
			System.out.println("해당 아이디가 존재하지 않습니다.");
			return;
		}
       
		userDAO.delete(id);
		System.out.println(userDTO.getName() + "님의 데이터를 삭제하였습니다.");    
	}

}

---

UserDAO.java

package user.dao;

import java.util.List;

import user.bean.UserDTO;

public interface UserDAO {

	public void write(UserDTO userDTO);

	public List<UserDTO> getUserList();
	
	public UserDTO getExistId(String id);

	public void update(UserDTO userDTO);

	public void delete(String id);

}

---

UserDAOImpl.java

	@Override
	public void delete(String id) {
		String sql = "delete from usertable where id=?";
		getJdbcTemplate().update(sql, id);
	}

---

[ JdbcDaoSupport ]

class MemberDAOImpl {

        @Setter

         private JdbcTemplate jdbcTemplate = null;

}

 

class BoardDAOImpl {

        @Setter

         private JdbcTemplate jdbcTemplate = null;

}

 

class ImageBoardDAOImpl {

        @Setter

         private JdbcTemplate jdbcTemplate = null;

}

 

이런식으로 하면 JdbcTemplate을 여러번 쓰게돼서

이제 JdbcTemplate을 직접적으로 만들지 않게 하는 것이다.

 

* 스프링이 제공하는 기반클래스

JdbcTemplate - JdbcDaoSupport

                       - JdbcDaoSupport 상속 받으면 JdbcTemplate 빈으로 생성할 필요가 없다

                       - JdbcDaoSupport 에서는 getJdbcTemplate() 메소드를 제공하므로 JdbcTemplate을

                         편리하게 사용할 수 있다.

 

JdbcTemplate 을 직접 만들지 말고 JdbcDaoSupport을 상속받아라.

 

extends implements가 있을 때는 extends 먼저 처리 !!

DaoSupport

        ↑

JdbcDaoSupport {

               private JdbcTemplate ~~~;

        ↑

UserDAOImpl

자식은 JdbcTemplate 이용은되지만 부모가 private이라 접근할 수 없다.

메서드로 접근하면 된다. -------> getJdbcTemplate으로 접근하면 된다.

---

applicationContext.xml (주석걸기)

	<!-- 
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg ref="dataSource"></constructor-arg>
	</bean>
	 -->

	<bean id="userDAOImpl" class="user.dao.impl.UserDAOImpl">
		<!--<property name="jdbcTemplate" ref="jdbcTemplate"></property>  -->
		<property name="dataSource" ref="dataSource"></property>
	</bean>

setDataSource(DataSource) ~~~~

이런 메서드가 UserDAOImpl에 있다는 것 근데 나한테 없다는건 부모에 있다는 것!

public class UserDAOImpl extends JdbcDaoSupport implements UserDAO {

 

 

UserDAOImpl.java

private JdbcTemplate jdbcTemplate = null;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
       this.jdbcTemplate = jdbcTemplate;
}

지우기

 

getJdbcTemplate()로 바꾸기

public class UserDAOImpl extends JdbcDaoSupport implements UserDAO {

	@Override
	public void write(UserDTO userDTO) {
		String sql = "insert into usertable values(?,?,?)";
		getJdbcTemplate().update(sql, userDTO.getName(), userDTO.getId(), userDTO.getPwd()); //insert, update, delete
		
	}

	@Override
	public List<UserDTO> getUserList() {
		String sql = "select * from usertable";
		return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<UserDTO>(UserDTO.class));
	}

	@Override
	public void update(UserDTO userDTO) {
		String sql = "update usertable set name=?, pwd=? where id=?";
		getJdbcTemplate().update(sql, userDTO.getName(), userDTO.getPwd(), userDTO.getId());
		
	}

	@Override
	public void delete(UserDTO userDTO) {
		String sql = "delete from usertable where id=?";
		getJdbcTemplate().update(sql, userDTO.getId());
	}
	
}

---

[ NamedParameterJdbcDaoSupport  ]

SQL문에서 ?를 넣게되면 뭔지 잘 모른다.

그래서  마이바티스에서는 #{name} 이런식으로 적어줬다.

#이 들어오면 물음표의 역할이었다.

이렇게되면 이건 누가봐도 이름, 아이디 이런식으로 알 수 있다.

 

그래서 스프링에서도 이런식으로 이름으로 하겠다는게

NamedParameterJdbcTemplate 

얘네를 만들어주는 부모클래스가 - NamedParameterJdbcDaoSupport

 

UserDAOImpl.java

public class UserDAOImpl extends NamedParameterJdbcDaoSupport implements UserDAO {

 

@Override
public void write(UserDTO userDTO) {
           String sql = "insert into usertable values(:name, :id, :pwd)";
           getJdbcTemplate().update(sql, userDTO.getName(), userDTO.getId(), userDTO.getPwd()); //insert, update, delete
}

name, id, pwd 이렇게 이름으로 들어오려면 map으로 잡혀야되는데 dto로 들고왔기 때문에 map으로 변경해야한다.

public class UserDAOImpl extends NamedParameterJdbcDaoSupport implements UserDAO {

	@Override
	public void write(UserDTO userDTO) {
		Map<String, String> map = new HashMap<>();
		map.put("name", userDTO.getName());
		map.put("id", userDTO.getId());
		map.put("pwd", userDTO.getPwd());
		
		String sql = "insert into usertable values(:name, :id, :pwd)";
		getNamedParameterJdbcTemplate().update(sql, map);		
	}

---

[ Chapter04_ANNO ]

---

applicationContext.xml

<bean id="helloSpring" class="user.main.HelloSpring"></bean> 제거

 

HelloSpring.java

@Component
public class HelloSpring {

---

applicationContext.xml

<bean id="userInsertService" class="user.service.UserInsertService" scope="prototype">
            <property name="userDTO" ref="userDTO" /> 제거
             <property name="userDAO" ref="userDAOImpl" />
</bean> 

 

 

<bean id="userDTO" class="user.bean.UserDTO" scope="prototype"></bean> 제거

 

UserDTO.java

@Component
@Scope("prototype")
@Setter
@Getter
public class UserDTO {

 

UserInsertService.java

@Component
@Scope("prototype")
public class UserInsertService implements UserService {
	@Autowired
	private UserDTO userDTO;
	@Autowired
	private UserDAO userDAO;

 

서비스역할을 하는 파일이다. - @Service 이렇게 해도된다.

//@Component
@Service
@Scope("prototype")
public class UserInsertService implements UserService {
	@Autowired
	private UserDTO userDTO;
	@Autowired
	private UserDAO userDAO;

---

applicationContext.xml

<bean id="userInsertService" class="user.service.UserInsertService" scope="prototype">
            <property name="userDTO" ref="userDTO" /> 
             <property name="userDAO" ref="userDAOImpl" /> 제거
</bean> 

 

<bean id="userDAOImpl" class="user.dao.impl.UserDAOImpl"> 
         <property name="dataSource" ref="dataSource"></property>
</bean>

얘도 제거하려하는데 dataSource가 걸린다.

 

@Component
public class UserDAOImpl extends NamedParameterJdbcDaoSupport implements UserDAO {

 

UserDAOImpl.java

DB와 연결된 애들이다 -- @Repository 이렇게 해도된다.

//@Component
@Repository
public class UserDAOImpl extends NamedParameterJdbcDaoSupport implements UserDAO {

 

 

setDataSource를 NamedParameterJdbcDaoSupport 얘가 쥐고있다.

 

<property name="dataSource" ref="dataSource"></property> 

final로 걸려있기 때문에 자식으로 override가 안된다.

이름을 setDataSource이렇게 잡으면 안되므로 setDS로 한다.

UserDAOImpl.java

//@Component
@Repository
public class UserDAOImpl extends NamedParameterJdbcDaoSupport implements UserDAO {

	@Autowired
	public void setDS(DataSource dataSource) {
		setDataSource(dataSource);
	}

 

---

UserSelectService.java

package user.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import user.bean.UserDTO;
import user.dao.UserDAO;

@Service
public class UserSelectService implements UserService {
	@Autowired
	private UserDAO userDAO;
	@Override
	public void execute() {
		//DB
		List<UserDTO> list = userDAO.getUserList();

		System.out.println("이름\t아이디\t비밀번호\t");
		for(UserDTO userDTO : list) {
			System.out.println(userDTO);
		}
	}

}

---

UserUpdateService.java

package user.service;

import java.util.Scanner;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import user.bean.UserDTO;
import user.dao.UserDAO;

@Service
public class UserUpdateService implements UserService {
	@Autowired
	private UserDAO userDAO;
	
	@Override
	public void execute() {
		System.out.println();
		Scanner scan = new Scanner(System.in);	
		
		System.out.print("수정할 아이디 입력 : ");
		String id = scan.next();
		
		UserDTO userDTO = userDAO.getExistId(id);
		
		if(userDTO == null) {
			System.out.println("해당 아이디가 존재하지 않습니다.");
			return;
		}

		System.out.println("이름\t아이디\t비밀번호\t");
		System.out.println(userDTO);
		
		System.out.print("수정할 이름 입력: ");
		String name = scan.next();
		
		System.out.print("수정할 비밀번호 입력: ");
		String pwd = scan.next();
		
		userDTO.setName(name);
		userDTO.setPwd(pwd);
		
		userDAO.update(userDTO);
		System.out.println(name + "님의 데이터를 수정하였습니다.");				
	}

}

---

UserDeleteService.java

 

package user.service;


import java.util.Scanner;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import lombok.Setter;
import user.bean.UserDTO;
import user.dao.UserDAO;

@Service
public class UserDeleteService implements UserService {
	@Autowired
	private UserDAO userDAO;
	
	public void execute() {
		System.out.println();
		Scanner scan = new Scanner(System.in);	
		
		System.out.print("삭제할 아이디 입력 : ");
		String id = scan.next();

		UserDTO userDTO = userDAO.getExistId(id);
		
		if(userDTO == null) {
			System.out.println("해당 아이디가 존재하지 않습니다.");
			return;
		}
       
		userDAO.delete(id);
		System.out.println(userDTO.getName() + "님의 데이터를 삭제하였습니다.");    
	}

}

---

applicationContext.xml

	<context:component-scan base-package="user.service" />
	<context:component-scan base-package="user.dao" />
	<context:component-scan base-package="user.bean" />
	<context:component-scan base-package="user.main" />

 

밑에처럼 해도되지만 비추천 !!

<context:component-scan base-package="user.*"/>

---

<context:property-placeholder location="classpath:spring/db.properties"/>
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
          <property name="driverClassName" value="${jdbc.driver}" />
          <property name="url" value="${jdbc.url}" />
          <property name="username" value="${jdbc.username}" />
          <property name="password" value="${jdbc.password}"></property>
</bean>

얘도 잘라버리자 !!

 

Package: spring.conf

Class: SpringConfiguration.java

package spring.conf;

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.context.annotation.Configuration;

@Configuration
public class SpringConfiguration {
	public BasicDataSource dataSource() {
		BasicDataSource basicDataSource =  new BasicDataSource();
		
		return basicDataSource;
	}
}

---

package spring.conf;

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

@Configuration
@PropertySource("classpath:spring/db.properties")
public class SpringConfiguration {
	private @Value("${jdbc.driver}") String driver;
	private @Value("${jdbc.url}") String url;
	private @Value("${jdbc.username}") String username;
	private @Value("${jdbc.password}") String password;
	
	@Bean
	public BasicDataSource dataSource() {
		BasicDataSource basicDataSource =  new BasicDataSource();
		basicDataSource.setDriverClassName(driver);
		basicDataSource.setUrl(url);
		basicDataSource.setUsername(username);
		basicDataSource.setPassword(password);
		
		return basicDataSource;
	}
}

 

applicationContext.xml

<context:component-scan base-package="spring.conf" />

---