스프링JDBC, JdbcTemplate, 마리아DB, 오라클12C 실습,자바동영상/스프링동영상/자바교육/스프링교육/스프링프레임…

스프링JDBC, JdbcTemplate, 마리아DB, 오라클12C 실습,자바동영상/스프링동영상/자바교육/스프링교육/스프링프레임워크/스프링학원/자바학원/자바/JAVA 

 

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> 

 

 

Spring JDBC jdbcTemplate 예제

 

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 테이블 및 데이터를 생성하자.]

 27a404dadafe4694d2080f753034e5d3_1602665 

 

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

 

27a404dadafe4694d2080f753034e5d3_1602665
 

 

 

Next SQLJDBC 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강의, #스프링프레임워크, #스프링교육, #스프링학원, #스프링강좌, #스프링강의, #자바학원, #자바, #스프링동영상, #자바동영상, #스프링프레임워크교육, #스프링프레임워크강의, #스프링프레임워크학원 

, , , , , , , , , , , , , , , , ,

Comments