小试SpringJdbcTemplate

最新推荐文章于 2021-11-22 14:32:22 发布

原创最新推荐文章于 2021-11-22 14:32:22 发布 · 155 阅读

0 ·

CC 4.0 BY-SA版权

SpringMVC 同时被 2 个专栏收录

4 篇文章

订阅专栏

JdbcTemplate

1 篇文章

订阅专栏

本文介绍如何在Spring框架中使用JdbcTemplate进行数据库操作，包括配置、基本CRUD操作、批量操作及事务管理等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

小试SpringJdbcTemplate

maven下的jdbcTemplate

spring.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:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
    <context:property-placeholder location="classpath:dbconfig.properties"/>
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="${driverClassName}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${user}"/>
        <property name="password" value="${password}"/>
        <property name="initialSize" value="${initialSize}"/>
        <property name="minIdle" value="${minIdle}"/>
        <property name="maxActive" value="${maxActive}"/>
        <property name="maxWait" value="${maxWait}"/>
        <property name="filters" value="${filters}"/>
    </bean>

    <!--注入灵魂-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"/>
    </bean>

    <!--注入大法师灵魂,当你有多个参数需要传递时,请注入他-->
    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"/>
    </bean>

    <!--注入事务管理-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!--加入注解方式调味-->
    <tx:annotation-driven transaction-manager="transactionManager"/>

    <!--别忘了加点包的扫描-->
    <context:component-scan base-package="com.wanmait.template.dao"/>
</beans>``

DAO

package com.wanmait.template.dao;

import com.wanmait.template.pojo.Department;
import com.wanmait.template.pojo.Doctor;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;

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

/*spring管理数据访问层*/
@Repository
public class DoctorDAO {

    /*注入灵魂*/
    @Resource
    private JdbcTemplate jdbcTemplate;
    /*注入大法师灵魂,参数比较多的话则注入此灵魂*/
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /*查全部*/
    public List<Doctor> findAll()
    {
        String sql = "select * from doctor";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Doctor>(Doctor.class));
    }

    /*模糊查询*/
    public List<Doctor> findAll(String info)
    {
        String sql = "select * from doctor where visible=1 and info like ?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Doctor>(Doctor.class),"%"+info+"%");
    }

    /*根据Id查询单条记录并返回此对象*/
    //BeanPropertyRowMapper 只要列名能和属性名一致，就能自动设置属性
    //如果根据给定的id查不到数据，会报异常EmptyResultDataAccessException
    public Doctor findById(Integer id)
    {
        Doctor doctor = null;
        String sql = "select * from doctor where visible=1 and id=?";
        try {
            doctor = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Doctor>(Doctor.class),id);
        } catch (DataAccessException e) {

        }
        return doctor;
    }

    /*假删除*/
    public void deleteById(Integer id)
    {
        String sql = "update doctor set visible=0 where id=?";
        jdbcTemplate.update(sql,id);
    }

    /*根据Id更新单条数据*/
    public void updateById(Doctor doctor)
    {
        String sql = "update doctor set name=?,info=? where id=?";
        jdbcTemplate.update(sql,doctor.getName(),doctor.getInfo(),doctor.getId());
    }

    /*添加一条新数据*/
    public void insert(Doctor doctor)
    {
        String sql = "insert into doctor(name,scope) values(?,?)";
        jdbcTemplate.update(sql,doctor.getName(),doctor.getScope());
    }

    /*如果参数比较多,使用命名参数这种方式比较方便*/
    public void insert2(Doctor doctor)
    {
        //:title 命名参数 参数名一定要和javabean类的属性名一致
        String sql = "insert into doctor(name,info) values(:name,:info)";
        SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(doctor);
        namedParameterJdbcTemplate.update(sql,sqlParameterSource);
    }

    /*添加新数据后马上获取该条数据的主键Id*/
    public void testInsert3(Doctor doctor)
    {
        String sql = "insert into doctor(name,info) values(:name,:info)";
        SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(doctor);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql,sqlParameterSource,keyHolder);
        doctor.setId(keyHolder.getKey().intValue());
    }

    /*获取总数*/
    public Integer getCount()
    {
        String sql = "select count(*) from doctor where visible=1";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    /*多表联合查询*/
    public List<Doctor> findAllMessage()
    {
        List<Doctor>doctors = null;
        StringBuilder stringBuilder = new StringBuilder("select doctor.name,doctor.info,doctor.department_id,department.title")
                .append(" from doctor")
                .append(" join department on department.id=doctor.department_id")
                .append(" where doctor.visible=1");
        doctors = jdbcTemplate.query(stringBuilder.toString(),new DoctorRowMapper());
        return doctors;
    }

    class DoctorRowMapper implements RowMapper<Doctor>
    {
        @Override
        public Doctor mapRow(ResultSet resultSet, int i) throws SQLException {
           Doctor doctor = new Doctor();
           doctor.setName(resultSet.getString("name"));
           doctor.setInfo(resultSet.getString("info"));
            Department department = new Department();
            department.setId(resultSet.getInt("department_id"));
            department.setTitle(resultSet.getString("title"));
            doctor.setDepartment(department);
            return doctor;
        }
    }
}

test测试一下

package com.wanmait.template.dao.test;

import com.wanmait.template.dao.DoctorDAO;
import com.wanmait.template.pojo.Doctor;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class DoctorDAOTest {

    @Resource
    private DoctorDAO doctorDAO;

    /*查全部*/
    @Test
    public void testFindAll()
    {
        Assert.assertNotNull(doctorDAO.findAll());
    }

    /*模糊查询*/
    @Test
    public void testFindByInfo()
    {
        String info = "病";
        Assert.assertNotNull(doctorDAO.findAll(info));
    }

    /*根据Id查询单条记录并返回此对象*/
    @Test
    public void testFindById()
    {
        Integer id = 18;
        Assert.assertNotNull(doctorDAO.findById(id));
    }

    /*假删除*/
    @Test
    public void testDelete()
    {
        Integer id = 1;
        doctorDAO.deleteById(id);
    }

    /*根据Id更新单条数据*/
    @Test
    public void testUpdateById()
    {
        Doctor doctor = new Doctor();
        doctor.setId(2);
        doctor.setName("江老哥");
        doctor.setInfo("B阶段个人项目奋力中");
        doctorDAO.updateById(doctor);
    }

    /*添加一条新数据*/
    @Test
    public void testInsert()
    {
        Doctor doctor = new Doctor();
        doctor.setName("萧萧");
        doctor.setScope("你长阿德");
        doctorDAO.insert(doctor);
    }

    /*添加一条新数据,多个参数*/
    @Test
    public void testInsert2()
    {
        Doctor doctor = new Doctor();
        doctor.setName("刘老狗");
        doctor.setInfo("哈哈哈武清");
        doctorDAO.insert2(doctor);
    }

    /*添加新数据后马上获取该条数据的主键Id*/
    @Test
    public void testInsert3()
    {
        Doctor doctor = new Doctor();
        doctor.setName("刘老狗");
        doctor.setInfo("哈哈哈武清");
        doctorDAO.testInsert3(doctor);
        System.out.println(doctor.getId());
    }

    /*获取总数*/
    @Test
    public void testGetCount()
    {
        System.out.println(doctorDAO.getCount());
    }

    /*多表联合查询*/
    @Test
    public void testFindAllMessage()
    {
        System.out.println(doctorDAO.findAllMessage().get(0).getDepartment().getTitle());
    }

}