스프링 tutorial (7)

DB persistence의 구현

 

1.     DB startup script의 생성

DB는 jar파일을 복사하므로써 사용할 수 있는 오픈소스 DB인 HSQL을 사용한다.

hsqldb.jar를 아래와 같이 복사한다.

spring-framework-2.5/lib/hsqldb => war/WEB-INF/lib

 

DB start를 위한 batch 파일을 필요로 한다.

springapp 디렉토리 아래에 db 디렉토리를 생성한다.

 

운영체제에 따라 아래와 같이 생성한다.

 

Linux/Mac OS X 일 경우 :

/db/server.sh

java -classpath ../war/WEB-INF/lib/hsqldb.jar org.hsqldb.Server -database test

이 경우에는 권한을 잊지 말자 'chmod +x server.sh'.

Windows일 경우 :

db/server.bat

java -classpath ..\war\WEB-INF\lib\hsqldb.jar org.hsqldb.Server -database test

 

운영체제에 따른 스크립트를 실행하여 DB를 start한다.

 

2.     Table과 test data를 생성

 

DB디렉토리 아래에 create_products.sql 을 생성한다.

 

db/create_products.sql

CREATE TABLE products (
  id INTEGER NOT NULL PRIMARY KEY,
  description varchar(255),
  price decimal(15,2)
);
CREATE INDEX products_description ON products(description); 

테스트 데이터는 DB 디렉토리 아래에 load_data.sql 로 생성한다.

 

db/load_data.sql

INSERT INTO products (id, description, price) values(1, 'Lamp', 5.78);
INSERT INTO products (id, description, price) values(2, 'Table', 75.29);
INSERT INTO products (id, description, price) values(3, 'Chair', 22.81);
 

3.     스크립트 실행과 테스트 데이터 로드를 위한 ant target를 추가

 

Ant에 sql task를 사용하여 추가하고 DB properties는 build.properties에 추가한다.

 

build.properties

# Ant properties for building the springapp
 
appserver.home=${user.home}/apache-tomcat-6.0.14
# for Tomcat 5 use $appserver.home}/server/lib
# for Tomcat 6 use $appserver.home}/lib
appserver.lib=${appserver.home}/lib
 
deploy.path=${appserver.home}/webapps
 
tomcat.manager.url=http://localhost:8080/manager
tomcat.manager.username=tomcat
tomcat.manager.password=s3cret
 
db.driver=org.hsqldb.jdbcDriver
db.url=jdbc:hsqldb:hsql://localhost
db.user=sa
db.pw= 

build.xml

 

<target name="createTables">
        <echo message="CREATE TABLES USING: ${db.driver} ${db.url}"/>
        <sql driver="${db.driver}"
             url="${db.url}"
             userid="${db.user}"
             password="${db.pw}"
             onerror="continue"
             src="db/create_products.sql">  
            <classpath refid="master-classpath"/>
        </sql> 
    </target>
 
    <target name="dropTables">
        <echo message="DROP TABLES USING: ${db.driver} ${db.url}"/>
        <sql driver="${db.driver}"
             url="${db.url}"
             userid="${db.user}"
             password="${db.pw}"
             onerror="continue">  
            <classpath refid="master-classpath"/>
 
        DROP TABLE products;
 
        </sql> 
    </target>
 
    <target name="loadData">
        <echo message="LOAD DATA USING: ${db.driver} ${db.url}"/>
        <sql driver="${db.driver}"
             url="${db.url}"
             userid="${db.user}"
             password="${db.pw}"
             onerror="continue"
             src="db/load_data.sql">  
            <classpath refid="master-classpath"/>
        </sql> 
    </target>
 
    <target name="printData">
        <echo message="PRINT DATA USING: ${db.driver} ${db.url}"/>
        <sql driver="${db.driver}"
             url="${db.url}"
             userid="${db.user}"
             password="${db.pw}"
             onerror="continue"
             print="true">  
            <classpath refid="master-classpath"/>
 
        SELECT * FROM products;
 
        </sql> 
    </target>
 
    <target name="clearData">
        <echo message="CLEAR DATA USING: ${db.driver} ${db.url}"/>
        <sql driver="${db.driver}"
             url="${db.url}"
             userid="${db.user}"
             password="${db.pw}"
             onerror="continue">  
            <classpath refid="master-classpath"/>
 
        DELETE FROM products;
 
        </sql> 
    </target>
 
    <target name="shutdownDb">
        <echo message="SHUT DOWN DATABASE USING: ${db.driver} ${db.url}"/>
        <sql driver="${db.driver}"
             url="${db.url}"
             userid="${db.user}"
             password="${db.pw}"
             onerror="continue">  
            <classpath refid="master-classpath"/>
 
        SHUTDOWN;
 
        </sql> 
    </target> 

 

ant createTables loadData printData를 실행함으로써 테이블과 데이터를 준비한다.

 

4.     JDBC 구현을 위한 Data Access Object (DAO) 생성

 

DB access를 위한 클래스들을 가지는 디렉토리를 생성한다.

src/repository

 

ProductDAO라는 이름을 가지는 interface를 생성한다.

 

src/springapp/repository/ProductDao.java

package springapp.repository;
 
import java.util.List;
 
import springapp.domain.Product;
 
public interface ProductDao {
 
    public List<Product> getProductList();
 
    public void saveProduct(Product prod);
 
} 

인터페이스를 구현한 클래스를 생성한다.

 

src/springapp/repository/JdbcProductDao.java

 

package springapp.repository;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
 
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
 
import springapp.domain.Product;
 
public class JdbcProductDao extends SimpleJdbcDaoSupport implements ProductDao {
 
    /** Logger for this class and subclasses */
    protected final Log logger = LogFactory.getLog(getClass());
 
 
    public List<Product> getProductList() {
        logger.info("Getting products!");
        List<Product> products = getSimpleJdbcTemplate().query(
                "select id, description, price from products", 
                new ProductMapper());
        return products;
    }
 
    public void saveProduct(Product prod) {
        logger.info("Saving product: " + prod.getDescription());
        int count = getSimpleJdbcTemplate().update(
            "update products set description = :description, price = :price where id = :id",
            new MapSqlParameterSource().addValue("description", prod.getDescription())
                .addValue("price", prod.getPrice())
                .addValue("id", prod.getId()));
        logger.info("Rows affected: " + count);
    }
    
    private static class ProductMapper implements ParameterizedRowMapper<Product> {
 
        public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
            Product prod = new Product();
            prod.setId(rs.getInt("id"));
            prod.setDescription(rs.getString("description"));
            prod.setPrice(new Double(rs.getDouble("price")));
            return prod;
        }
 
    }
 
} 

domain/Product.java

 

package springapp.domain;
 
import java.io.Serializable;
 
public class Product implements Serializable {
 
    private int id;
    private String description;
    private Double price;
    
    public void setId(int i) {
        id = i;
    }
 
    public int getId() {
        return id;
    }
 
    public String getDescription() {
        return description;
    }
    
    public void setDescription(String description) {
        this.description = description;
    }
    
    public Double getPrice() {
        return price;
    }
    
    public void setPrice(Double price) {
        this.price = price;
    }
    
    public String toString() {
        StringBuffer buffer = new StringBuffer();
        buffer.append("Description: " + description + ";");
        buffer.append("Price: " + price);
        return buffer.toString();
    }
} 

5.     JDBC DAO 구현의 test

 

spring-framework-2.5/dist/modules/spring-test.jar => war/WEB-INF/lib

 

test/springapp/domain/JdbcProductDaoTests.java

 

package springapp.repository;
 
import java.util.List;
 
public class JdbcProductDaoTests extends AbstractTransactionalDataSourceSpringContextTests {
 
    private ProductDao productDao;
 
    
    public void setProductDao(ProductDao productDao) {
        this.productDao = productDao;
    }
 
    @Override
    protected String[] getConfigLocations() {
        return new String[] {"classpath:test-context.xml"};
    }
 
    @Override
    protected void onSetUpInTransaction() throws Exception {
        super.deleteFromTables(new String[] {"products"});
        super.executeSqlScript("file:db/load_data.sql", true);
    }
 
    public void testGetProductList() {
        
        List<Product> products = productDao.getProductList();
        
        assertEquals("wrong number of products?", 3, products.size());
        
    }
    
    public void testSaveProduct() {
        
        List<Product> products = productDao.getProductList();
        
        for (Product p : products) {
            p.setPrice(200.12);
            productDao.saveProduct(p);
        }
        
        List<Product> updatedProducts = productDao.getProductList();
        for (Product p : updatedProducts) {
            assertEquals("wrong price of product?", 200.12, p.getPrice());
        }
 
    }
 
} 

test/test-context.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-2.5.xsd"> 
 
    <!-- the test application context definition for the jdbc based tests --> 
 
    <bean id="productDao" class="springapp.repository.JdbcProductDao">
        <property name="dataSource" ref="dataSource" />
    </bean>
 
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="${jdbc.driverClassName}"/>
      <property name="url" value="${jdbc.url}"/>
      <property name="username"  value="${jdbc.username}"/>
      <property name="password" value="${jdbc.password}"/>
    </bean>
    <bean id="propertyConfigurer" 
          class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
            </list>
        </property>
    </bean>
    
    <bean id="transactionManager" 
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>
 
</beans> 

war/WEB-INF/classes/jdbc.properties

 

jdbc.driverClassName=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost
jdbc.username=sa
jdbc.password= 

build.xml

<property name="test.dir" value="test"/>

    <path id="test-classpath">

        <fileset dir="${web.dir}/WEB-INF/lib">

            <include name="*.jar"/>

        </fileset>

        <pathelement path="${build.dir}"/>

        <pathelement path="${test.dir}"/>

        <pathelement path="${web.dir}/WEB-INF/classes"/>

    </path>

  

build.xml

  

    
<target name="tests" depends="build, buildtests" description="Run tests">
        <junit printsummary="on"
            fork="false"
            haltonfailure="false"
            failureproperty="tests.failed"
            showoutput="true">
            <classpath refid="test-classpath"/>
            <formatter type="brief" usefile="false"/>
            
            <batchtest>
                <fileset dir="${build.dir}">
                    <include name="**/*Tests.*"/>
                    <exclude name="**/Jdbc*Tests.*"/>
                </fileset>
            </batchtest>
            
        </junit>
        
        <fail if="tests.failed">
            tests.failed=${tests.failed}
            ***********************************************************
            ***********************************************************
            ****  One or more tests failed!  Check the output ...  ****
            ***********************************************************
            ***********************************************************
        </fail>
    </target>
 
    <target name="dbTests" depends="build, buildtests,dropTables,createTables,loadData" 
            description="Run db tests">
        <junit printsummary="on"
            fork="false"
            haltonfailure="false"
            failureproperty="tests.failed"
            showoutput="true">
            <classpath refid="test-classpath"/>
            <formatter type="brief" usefile="false"/>
            
            <batchtest>
                <fileset dir="${build.dir}">
                    <include name="**/Jdbc*Tests.*"/>
                </fileset>
            </batchtest>
            
        </junit>
        
        <fail if="tests.failed">
            tests.failed=${tests.failed}
            ***********************************************************
            ***********************************************************
            ****  One or more tests failed!  Check the output ...  ****
            ***********************************************************
            ***********************************************************
        </fail>
    </target>  

ant dbTests를 실행하여 테스트 통과를 확인한다.