使用embedded database

这篇文章没有上下文，只是我自己的备忘，等以后我搞清楚了，再把文章的前后文加上吧。

首先新建  embeddedDataSource.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:aop="http://www.springframework.org/schema/aop"
        xmlns:context="http://www.springframework.org/schema/context"
        xmlns:tx="http://www.springframework.org/schema/tx"
        xmlns:jdbc="http://www.springframework.org/schema/jdbc"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-3.0.xsd
    http://www.springframework.org/schema/jdbc
    http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

    <!-- 使用内存数据库 -->
    <jdbc:embedded-database id="dataSource">
        <jdbc:script location="classpath:schema.sql"/>
        <jdbc:script location="classpath:data.sql"/>
    </jdbc:embedded-database>

</beans>

--------------------------------------------------------------------------------------------------------------

其次，在web.xml中加入以下内容：

    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>

    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:embeddedDataSource.xml</param-value>
    </context-param>

--------------------------------------------------------------------------------------------------------------

然后，在classpath目录下，新建 schema.sql 和 data.sql

schema.sql 内容如下：

drop table expenseRecords if exists;
drop table employeeInfo if exists;
create table expenseRecords(uid varchar(5), type varchar(20), cost decimal(6,2));
create table employeeInfo(uid varchar(5), uname varchar(20), exp decimal(3,1));

data.sql 内容如下：

insert into expenseRecords(uid, type, cost) values('00001', 'Books', 150);
insert into expenseRecords(uid, type, cost) values('00002', 'Training/Education', 800);
insert into expenseRecords(uid, type, cost) values('00003', 'Books', 40);
insert into expenseRecords(uid, type, cost) values('00004', 'Books', 70.5);
insert into expenseRecords(uid, type, cost) values('00005', 'Books', 900);
insert into expenseRecords(uid, type, cost) values('00006', 'Training/Education', 60.5);
insert into expenseRecords(uid, type, cost) values('00002', 'Books', 160.5);
insert into expenseRecords(uid, type, cost) values('00001', 'other', 300);
insert into expenseRecords(uid, type, cost) values('00001', 'Training/Education', 1000);


insert into employeeInfo(uid, uname, exp) values('00001', 'guanyuan', 1.1);
insert into employeeInfo(uid, uname, exp) values('00002', 'lihongjing', 0.2);
insert into employeeInfo(uid, uname, exp) values('00003', 'zhaolan', 0.7);
insert into employeeInfo(uid, uname, exp) values('00004', 'chendan', 1.5);
insert into employeeInfo(uid, uname, exp) values('00005', 'xiefang', 2.1);
insert into employeeInfo(uid, uname, exp) values('00006', 'pangmin', 0.9);

--------------------------------------------------------------------------------------------------------------

最后，修改DataManager.java 文件内容全部替换为：

    private static EmbeddedDatabase db;

    static {

        db = new EmbeddedDatabaseBuilder().addDefaultScripts().build();    

//这里其实应该不需要的把，因为已经将datasource注册为bean了呀，可是不加测试不能通过，不知道是为什么？

    }


    public static ResultSet getResultSet(String sql) throws SQLException {
        Connection conn = db.getConnection();
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        return resultSet;
    }

--------------------------------------------------------------------------------------------------------------

最后的最后，写测试：

    @Test
    public void test() throws SQLException {
        String sql = "select * from employeeInfo where uid = '00001';"; //查询用户信息
        ResultSet resultSet = DataManager.getResultSet(sql);
        String result = null;
        while (resultSet.next()) {
            result = resultSet.getString("uname");
        }
        assertEquals("guanyuan", result);
    }