eclipse中spring的Spring JdbcTemplate访问access的简易实现-阿里云开发者社区

修改上一篇博文中的配置文件和代码，让我们来看看如何访问一个本地access的mdb文件数据库。
首先下图为被访问的access文件的ip地址和存储路径及文件名。

下图为access文件中的某一个表的概要内容

下图表示eclipse所在的就是同一台电脑

文档结构与前一篇博文完全相同，只是修改了spring配置文件以及sql语句
1.spring配置文件

<?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:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        classpath:/org/springframework/beans/factory/xml/spring-beans-4.0.xsd     
        http://www.springframework.org/schema/context
        classpath:/org/springframework/context/config/spring-context-4.0.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">     
        
    <!-- 扫描类包，将标注Spring注解的类自动转化Bean，同时完成Bean的注入 -->
    <context:component-scan base-package="com.infotech.access.dao"/>
        
    <!-- 配置ucanaccess数据源 -->
    <bean id="dsmysql" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close" 
        p:driverClassName="net.ucanaccess.jdbc.UcanaccessDriver"
        p:url="jdbc:ucanaccess://D:\share\data_be.mdb"
        p:username="admin"
        p:password="" />
        
    <!-- 配置事务管理器 -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
        p:dataSource-ref="dsmysql" />

    <!-- 配置JdbcTemplate  -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
        p:dataSource-ref="dsmysql" />
     
</beans>

2.spring源文件

package com.infotech.access.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import com.infotech.access.domain.CheckedList;

@Repository
public class CheckedlistDao {
    
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    /**
     * @param skostl
     * @return
     */
    public String getData(String smname) {
        
        final String sr =null;
        
        jdbcTemplate.query("select * from CheckedList where ID =?", new Object[] { smname }, new RowCallbackHandler() {

            public void processRow(ResultSet rs) throws SQLException {
              String sr2 = rs.getString("CL_name");
              System.out.println(sr2);
            }
            
        });
        
        return sr;
    }
    

}

3.测试TestNG文件

package com.infotech.access.dao;

import java.util.Date;
import org.springframework.test.context.testng.AbstractTransactionalTestNGSpringContextTests;
import org.testng.annotations.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;

import com.infotech.access.domain.CheckedList;
import static org.testng.Assert.*;

@ContextConfiguration("classpath*:/ruku-context.xml")
public class CheckedlistDaoTest extends AbstractTransactionalTestNGSpringContextTests{
    
    private CheckedlistDao cld;
    
    @Autowired
    public void setCheckedlistDao(CheckedlistDao cldlocal){
        this.cld = cldlocal;
    }

    
    @Test
    public void testAcess() {
        
        String sb1 = this.cld.getData("5");
        System.out.println(sb1);
        
    }
    

}

下面我们将access复制到同一局域网的另一台电脑上，进行远程访问

只需要修改spring配置文件

<?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:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        classpath:/org/springframework/beans/factory/xml/spring-beans-4.0.xsd     
        http://www.springframework.org/schema/context
        classpath:/org/springframework/context/config/spring-context-4.0.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">     
        
    <!-- 扫描类包，将标注Spring注解的类自动转化Bean，同时完成Bean的注入 -->
    <context:component-scan base-package="com.infotech.access.dao"/>
        
    <!-- 配置ucanaccess数据源 -->
    <bean id="dsmysql" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close" 
        p:driverClassName="net.ucanaccess.jdbc.UcanaccessDriver"
        p:url="jdbc:ucanaccess://\\\\172.16.30.106\\share\\data_be.mdb"
        p:username="admin"
        p:password="" />
        
    <!-- 配置事务管理器 -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
        p:dataSource-ref="dsmysql" />

    <!-- 配置JdbcTemplate  -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
        p:dataSource-ref="dsmysql" />
     
</beans>

下面基于上述内容扩充我们代码的内容，java代码和测试TestNG代码如下所示：

package com.infotech.access.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.infotech.access.domain.CheckedList;

@Repository
public class CheckedlistDao {

    /**
     * 声明JdbcTemplate的一个变量
     */
    private JdbcTemplate jdbcTemplate;

    /**
     * @param 注入JdbcTemplate的变量的实例
     */
    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    

    /**
     * @param sCname 输入参数为id
     * @return 返回CheckedList对象
     */
    public CheckedList getCL(final String sid) {

        final CheckedList cl1 = new CheckedList();

        jdbcTemplate.query("select * from CheckedList where ID =?", new Object[] { sid }, new RowCallbackHandler() {

            public void processRow(ResultSet rs) throws SQLException {
                cl1.setSid(sid);
                cl1.setClName(rs.getString("CL_name"));
                cl1.setClHandler(rs.getString("CL_handler"));
            }

        });
        return cl1;
    }
    
    /**
     * @param fromId
     * @param toId
     * @return 返回值是List容器的实例
     */
    public List<CheckedList> getCLs(final String fromId, final String toId) {

        String sql2 = "select * from CheckedList where ID between ? and ?";
        final List<CheckedList> cls = new ArrayList<CheckedList>();
        jdbcTemplate.query(sql2, new Object[] { fromId, toId }, new RowCallbackHandler() {

            public void processRow(ResultSet rs) throws SQLException {
                CheckedList cl1 = new CheckedList();
                cl1.setSid(rs.getString("ID"));
                cl1.setClName(rs.getString("CL_name"));
                cl1.setClHandler(rs.getString("CL_handler"));
                cls.add(cl1);
            }
        });

        return cls;
    }
    
    /**
     * 重构 
     * @param sHname 输入参数为CL_handler 模糊查询
     * @return
     */
    public List<CheckedList> getCLs(String sHname) {

        String sql = "select * from CheckedList where CL_handler like ?";
        return jdbcTemplate.query(sql, new Object[] { sHname }, new RowMapper<CheckedList>() {

            public CheckedList mapRow(ResultSet rs, int rowNum) throws SQLException {

                CheckedList cl1 = new CheckedList();
                cl1.setSid(rs.getString("ID"));
                cl1.setClName(rs.getString("CL_name"));
                cl1.setClHandler(rs.getString("CL_handler"));
                return cl1;
            }
        });

    }
    
    /**
     * @param sid  返回值是数据库中一条记录中的一个字段值
     * @return
     */
    public String getCLone(String sid){
        
        String sql = "select * from CheckedList where ID =?";
        String sr = jdbcTemplate.queryForObject(sql, new Object[] {sid}, new RowMapper<String>(){

            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                // TODO Auto-generated method stub
                return rs.getString("CL_name");
            }
            
        });
        
        return sr;
    }
    
    
    
    /**
     * @param 
     * @return
     */
    public String getData(String smname) {
        
        final String sr =null;
        jdbcTemplate.query("select * from CheckedList where ID =?", new Object[] { smname }, new RowCallbackHandler() {

            public void processRow(ResultSet rs) throws SQLException {
              String sr2 = rs.getString("CL_name");
              System.out.println(sr2);
            }
            
        });
        return sr;
    }
    

}

测试代码

package com.infotech.access.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.test.context.testng.AbstractTransactionalTestNGSpringContextTests;
import org.testng.annotations.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;

import com.infotech.access.domain.CheckedList;
import static org.testng.Assert.*;

@ContextConfiguration("classpath*:/ruku-context.xml")
public class CheckedlistDaoTest extends AbstractTransactionalTestNGSpringContextTests{
    
    private CheckedlistDao cld;
    
    @Autowired
    public void setCheckedlistDao(CheckedlistDao cldlocal){
        this.cld = cldlocal;
    }

    
    @Test
    public void testAcess() {
        
/*        String sb1 = this.cld.getData("5");
        System.out.println(sb1);                    */
        
/*        CheckedList cltemp = cld.getCL("5");
        String sHname = cltemp.getClHandler();
        String sName = cltemp.getClName();
        assertEquals(cltemp.getClHandler(),"许强");
        System.out.println(sHname+"--"+sName);      */
        
/*        List<CheckedList> rlist = cld.getCLs("5", "10");
        
        boolean b1 = rlist.isEmpty();   //是否为空
        int i1 = rlist.size();          //结果集大小        
        System.out.println(b1);
        System.out.println(i1);
        
        for (int i = 0; i < i1; i++) {   //循环显示ArrayList的内容
            CheckedList cltemp = rlist.get(i);
            String sName = cltemp.getClName();
            String sHname = cltemp.getClHandler();
            System.out.println(sHname+"  "+sName);
        }*/
        
/*        List<CheckedList> rlist = cld.getCLs("许强");
        boolean b1 = rlist.isEmpty();   //是否为空
        int i1 = rlist.size();          //结果集大小        
        System.out.println(b1);
        System.out.println(i1);
        
        for (int i = 0; i < i1; i++) {   //循环显示ArrayList的内容
            CheckedList cltemp = rlist.get(i);
            String sName = cltemp.getClName();
            String sHname = cltemp.getClHandler();
            System.out.println(sHname+"  "+sName);
        }*/
        
        String s2 = cld.getCLone("5");   //获取一个String
        System.out.println(s2);
        
        
    }
    

}

eclipse中spring的Spring JdbcTemplate访问access的简易实现

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像