스프링JDBC, JdbcTemplate, 마리아DB, 오라클12C 실습,자바동영상/스프링동영상/자바교육/스프링교육/스프링프레임워크/스프링학원/자바학원/자바/JAVA
ojc.asia/bbs/board.php?bo_table=LecSpring&wr_id=878
ojc.asia
4-3-1. Spring JDBC에서 DB 접근하는 방법 – jdbcTemplate
n Spring JDBC의 핵심 클래스로 모든 형태의 SQL 구문을 실행해서 원하는 결과 타입을 반환한다.
n 스프링의 모든 데이터 접근 프레임워크는 템플릿 클래스를 포함하는데 이 경우 템플릿 클래스는 JdbcTemplate 클래스이다.
n JdbcTemplate 클래스가 작업하기 위해 필요한 것은 DataSource 이며 스프링의 모든 DAO 템플릿 클래스는 스레드에 안전하기 때문에 애플리케이션 내의 각각의 DataSource에 대해서 하나의 JdbcTemplate 인스턴스만을 필요로 한다.
n Connection 객체의 생성 및 닫기 등 자원의 생성과 해제를 처리하므로 연결을 끊는 것을 잊었을 때 문제가 되지 않는다.
n Exception을 처리하고 org.springframework.dao 패키지에 정의 된 excepion 클래스의 도움으로 예외 메시지를 제공한다.
n INSERT, UPDATE, DELETE 및 SELECT과 같은 작업을 데이터베이스에서 수행 할 수 있다.
메소드 | 내용 |
public int update(String query) | 레코드의 입력, 수정, 삭제처리 |
public int update(String query,Object... args) | PreparedStatement를 이용하여 주어진 파라미터로 레코드의 입력, 수정, 삭제처리 |
public void execute(String query) | DDL쿼리를 실행 |
public T execute(String sql, PreparedStatementCallback action) | PreparedStatement callback을 이용하여 SQL을 실행 |
public T query(String sql, ResultSetExtractor rse) | ResultSetExtractor를 사용하여 레코드를 추출 |
public List query(String sql, RowMapper rse) | RowMapper를 사용하여 레코드를 추출 |
public class StudentDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTeamplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}//:
}///~
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" >
<property name="driverClassName“><value>oracle.jdbc.driver.OracleDriver</value></property>
<property name="url“><value>jdbc:oracle:thin:@localhost:1521:ORCL</value></property>
<property name="username“><value>scott</value></property>
<property name="password“><value>tiger</value></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource">
<ref bean="dataSource"/>
</property>
</bean>
<bean id=“studentDao"
class="oraclejava.training.jdbc.StudentDao">
<property name="jdbcTemplate">
<ref bean="jdbcTemplate"/>
</property>
</bean>
n 마리아DB에서 EMP 테이블의 데이터를 Spring JDBC의 JdbcTemplate을 이용하여 화면에 출력하는 간단한 예제를 작성해 보자.
STS에서
File -> New -> Project -> Maven Project
GroupId : jdbc1
ArtifactId : jdbc1
Name : jdbc1
Description : jdbc1
Finish 클릭
[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>jdbc1</groupId>
<artifactId>jdbc1</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>jdbc1</name>
<description>jdbc1</description>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
</dependencies>
</project>
[HeidiSQL에서 jdbc1 이라는 데이터베이스와 EMP 테이블 및 데이터를 생성하자.]
create table emp
(
empno int(4) not null auto_increment,
ename varchar(50),
sal int(4),
primary key (empno)
);
insert into emp(ename, sal) values ('1길동', 1000);
insert into emp(ename, sal) values ('2길동', 2000);
insert into emp(ename, sal) values ('3길동', 3000);
[EmpDao.java]
package jdbc1;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class EmpDao {
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
List getNames() {
String sql = "select * from emp";
return jdbcTemplate.queryForList(sql);
}
}
[src/main/resources/ jdbc1.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean id="EmpDao" class="jdbc1.EmpDao">
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost/jdbc1</value>
</property>
<property name="username">
<value>root</value>
</property>
<property name="password">
<value>1111</value>
</property>
</bean>
</beans>
[JdbcClient.java]
package jdbc1;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class JdbcClient {
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbc1.xml");
EmpDao e = (EmpDao)ctx.getBean("EmpDao");
System.out.println(e.getNames());
}
}
실습2 : 오라클12C, 스프링부트 기반 예제
STS 에서 File , New, Spring Stater Project
Name : JdbcTemplateExam
Type : Maven
Package : com.example.jdbctemplate
Next 후 SQL의 JDBC API 및 Oracle Driver 선택 후 Finish
오라클의 Connection Pool을 사용하기 위해 pom.xml에 디펜던시 추가
[pom.xml]
<?xml version="1.0" encoding="UTF-8"?>
<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>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.4.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>jdbcTemplateExam</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>jdbcTemplateExam</name>
<description>Demo project for Spring Boot, JdbcTemplate</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
</dependency>
<dependency>
<groupId>com.oracle.database.ha</groupId>
<artifactId>ons</artifactId>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ucp</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
[Customer.java]
package com.example.jdbctemplate;
public class Customer {
private long id;
private String firstName;
private String lastName;
public Customer(long id, String firstName, String lastName) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
}
@Override
public String toString() {
return String.format("Customer[id=%d, firstName='%s', lastName='%s']", id, firstName, lastName);
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
}
[OracleConfig.java]
package com.example.jdbctemplate;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;
@Configuration
public class OracleConfig {
@Bean
public DataSource dataSource() throws SQLException {
PoolDataSource dataSource = PoolDataSourceFactory.getPoolDataSource();
dataSource.setUser("scott");
dataSource.setPassword("tiger");
dataSource.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
dataSource.setURL("jdbc:oracle:thin:@//localhost:1521/ORCL");
dataSource.setFastConnectionFailoverEnabled(true);
dataSource.setInitialPoolSize(5);
dataSource.setMinPoolSize(5);
dataSource.setMaxPoolSize(10);
return dataSource;
}
}
[JdbcTemplateExamApplication.java]
package com.example.jdbctemplate;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
@ConfigurationProperties("oracle")
@SpringBootApplication
public class JdbcTemplateExamApplication implements CommandLineRunner {
private static final Logger logger = LoggerFactory.getLogger(JdbcTemplateExamApplication.class);
public static void main(String[] args) {
SpringApplication.run(JdbcTemplateExamApplication.class, args);
}
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public void run(String... args) throws Exception {
jdbcTemplate.execute("DROP TABLE customers");
jdbcTemplate.execute("CREATE TABLE customers(id number GENERATED AS IDENTITY, firstName VARCHAR(100), lastName VARCHAR2(100))");
logger.info(">>>>> customers table created.");
List<Object[]> splitUpNames = Arrays.asList("John Woo", "Jeff Dean", "Josh Bloch", "Josh Long").stream()
.map(name -> name.split(" "))
.collect(Collectors.toList());
// Uses JdbcTemplate's batchUpdate operation to bulk load data
jdbcTemplate.batchUpdate("INSERT INTO customers(firstName, lastName) VALUES (?, ?)", splitUpNames);
logger.info(">>>>> insert ok.");
jdbcTemplate.query("SELECT id, firstName, lastName FROM customers",
(rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("firstName"), rs.getNString("lastName")))
.forEach(customer -> logger.info(customer.toString()));
logger.info(">>>>> the end.");
}
}
#스프링jdbc, #JdbcTemplate, #Springjdbc, #자바스프링, #Spring동영상, #Spring강의, #스프링프레임워크, #스프링교육, #스프링학원, #스프링강좌, #스프링강의, #자바학원, #자바, #스프링동영상, #자바동영상, #스프링프레임워크교육, #스프링프레임워크강의, #스프링프레임워크학원
댓글 없음:
댓글 쓰기