Spring数据访问之JdbcTemplate

Spring数据访问之JdbcTemplate

使用JdbcTemplate的基本操作步骤

1.引jar包

项目的基本架构

这里重点看实现类的内容

 package cn.hmy.dao.impl;

 import java.util.List;

 import org.springframework.jdbc.core.support.JdbcDaoSupport;

 import cn.hmy.beans.Emp;

 import cn.hmy.dao.IEmpDao;

 import cn.hmy.util.MyRowMapper;

 public class EmpDaoImpl extends JdbcDaoSupport implements IEmpDao {

     //获取员工总人数

     public int getTotalCount() throws Exception {

         String sql="select * from emp";

         Integer count=this.getJdbcTemplate().queryForObject(sql, Integer.class);

         return count;

     }

     //添加员工信息

     public int addEmp(Emp emp) throws Exception {

         String sql="insert into emp(empNo,empName,deptNo) values(?,?,?)";

         int count = this.getJdbcTemplate().update(sql, emp.getEmpNo(),emp.getEmpName(),emp.getDeptNo());

         return count;

     }

     //查询所有的员工信息

     public List<Emp> getAllEmps() throws Exception {

         String sql="select * from emp";

         List<Emp> list = this.getJdbcTemplate().query(sql,new MyRowMapper());

         return list;

     }

 }

EmpDaoImpl

 package cn.hmy.service.impl;

 import java.util.List;

 import cn.hmy.beans.Emp;

 import cn.hmy.dao.IEmpDao;

 import cn.hmy.service.IEmpService;

 public class EmpServiceImpl implements IEmpService{

     private IEmpDao empDao;

     //获取员工总记录数

     public int getTotalCount() throws Exception{

         return empDao.getTotalCount();

     }

     //添加员工

     public int addEmp(Emp emp) throws Exception {

         return empDao.addEmp(emp);

     }

     //查询员工的所有信息

     public List<Emp> getAllEmps() throws Exception {

         return empDao.getAllEmps();

     }

     public IEmpDao getEmpDao() {

         return empDao;

     }

     public void setEmpDao(IEmpDao empDao) {

         this.empDao = empDao;

     }

 }

EmpServiceImpl

在查询的时候我们为了能使查询到的结果集返回泛型集合，我们又为了减少代码量，就提出一个util类MyRowMapper类，来转换结果集

 package cn.hmy.util;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import org.springframework.jdbc.core.RowMapper;

 import cn.hmy.beans.Emp;

 public class MyRowMapper implements RowMapper{

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

         Emp emp=new Emp();

         emp.setDeptNo(rs.getInt("deptNo"));

         emp.setEmpName(rs.getString("empName"));

         emp.setEmpNo(rs.getInt("empNo"));

         return emp;

     }

 }

MyMapper

applicationContext.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"

    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd

        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">

<!-- 注册jdbc属性 -->

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">

 <property name="location" value="classpath:jdbc.properties"></property>

</bean>

<!-- Spring 内置配置数据源 -->

<!-- <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

  <property name="driverClassName" value="${jdbc.className}"/>

  <property name="url" value="${jdbc.url}"/>

  <property name="username" value="${jdbc.username}"/>

  <property name="password" value="${jdbc.password}"/>

</bean> -->

 <!--注册dbcp数据源  -->

 <!-- <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

 <property name="driverClassName" value="${jdbc.className}"/>

 <property name="url" value="${jdbc.url}"/>

 <property name="username" value="${jdbc.username}"/>

 <property name="password" value="${jdbc.password}"/>

 </bean> -->

 <!--注册c3p0数据源  -->

 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

  <property name="driverClass" value="${jdbc.className}"></property>

  <property name="jdbcUrl" value="${jdbc.url}"></property>

  <property name="user" value="${jdbc.username}"></property>

  <property name="password" value="${jdbc.password}"></property>

 </bean>

 <!--配置jdbcTemplate  -->

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

<property name="dataSource" ref="dataSource"></property>

</bean>

<!-- 管理empDao实现类 -->

<bean id="empDao" class="cn.hmy.dao.impl.EmpDaoImpl">

  <property name="jdbcTemplate" ref="jdbcTemplate"></property>

</bean>

<!-- 管理empService实现类 -->

<bean id="empService" class="cn.hmy.service.impl.EmpServiceImpl">

  <property name="empDao" ref="empDao"></property>

</bean>

</beans>

书写测试类

 package cn.hmy.test;

 import java.util.List;

 import org.junit.Test;

 import org.springframework.context.ApplicationContext;

 import org.springframework.context.support.ClassPathXmlApplicationContext;

 import cn.hmy.beans.Emp;

 import cn.hmy.service.IEmpService;

 public class MyTest {

     @Test

     public void getAdd() throws Exception{

         ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");

         IEmpService service=(IEmpService)context.getBean("empService");

         Emp emp=new Emp();

         emp.setDeptNo(1);

         emp.setEmpName("123");

         emp.setEmpNo(10);

         int addEmp;

         try {

             addEmp = service.addEmp(emp);

             System.out.println(addEmp);

         } catch (Exception e) {

             e.printStackTrace();

         }

     }

     @Test

     public void getAllEmps() throws Exception{

         ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");

         IEmpService service=(IEmpService)context.getBean("empService");

         List<Emp> allEmps = service.getAllEmps();

         for (Emp emp : allEmps) {

             System.out.println(emp.getEmpName());

         }

     }

 }

测试类