spring整合jdbc

AUTO_K

已于 2022-03-15 17:41:36 修改

阅读量838

点赞数

文章标签： spring java template method

于 2022-03-11 09:29:18 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_44722237/article/details/123413455

版权

1.使用spring-jdbc操作数据库

主要内容:学习使用JdbcTemplate API和如何使用Spring管理 JdbcTemplate

创建项目引入依赖

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.2.13.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.2.13.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.23</version>
    </dependency>
    <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.5.2</version>
    </dependency>
    <!--测试依赖-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>
</dependencies>

Spring管理JdbcTemplate

spring整合jdbc的时候我们都是直接让我的dao继承Spring提供的JdbcDaoSupport类，该类中提供了JdbcTemplate模板可用。

内置了jdbcTemplate 对象。

package com.kkb.TeamDao;

import com.kkb.pojo.Team;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;


@Repository
@ComponentScan
public class TeamDao extends JdbcDaoSupport {
    public  Integer  insert(Team team){
        String sql="insert into team(tname,location) value (?,?)";
        return this.getJdbcTemplate().update(sql,team.getName(),team.getLocation());
    }
}

spring的配置文件application.xml（springjdbc.xml）中需要创建数据源和给TeamDao中的jdbcTemplate赋值。

teamDao需要 jdbcTemplate，jdbcTemplate对象需要dataSource 。

<?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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd">
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springJDBC?
    serverTimezone=UTC&amp;characterEncoding=utf8&amp;useUnicode=true&amp;useSSL=false"></property>
    <property name="user" value="root"></property>
    <property name="password" value="123456"></property>
</bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <bean id="teamDao" class="com.kkb.TeamDao.TeamDao">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

<!--    <context:component-scan base-package="com.kkb.TeamDao"></context:component-scan>-->
</beans>

测试类

package com.kkb.test01;

import com.kkb.TeamDao.TeamDao;
import com.kkb.pojo.Team;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class test1 {
    ApplicationContext ac = new ClassPathXmlApplicationContext("springjdbc.xml");
    @Test
    public void  insert(){
        TeamDao teamDao = (TeamDao)ac.getBean("teamDao");
        Team team =new Team();
        team.setLocation("河南");
        team.setName("少林队");
        teamDao.insert(team);
    }
}

2.下面说一下其他方法使用

package com.kkb.TeamDao;

import com.kkb.pojo.Team;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

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


@Repository
@ComponentScan
public class TeamDao extends JdbcDaoSupport {
    /**
     * 查询结果如果是一个字段（列的一个值）直接使用queryForObject(sql,Integer.class);   （sql语句，类.class）
     * @return
     */
    public int getCount(){
        String sql="select count(tid) from team";
        return this.getJdbcTemplate().queryForObject(sql,Integer.class);
    }

    /**
     * 查询结果如果有多个字段   使用queryForMap(sql)    返回的结果类型为  @return
     *   key  max(tid)  value   数值
     *   key  min(tid)  value   数值
     * @return    Map<String, Object>
     */
    public Map<String, Object> getMany(){
        String sql="select max(tid),min(tid) from team";
        return this.getJdbcTemplate().queryForMap(sql);
    }


    /**
     * 封装的结果集映射函数    方便多个查询函数调用  这里只有findAll调用了
     * @param resultSet
     * @return
     * @throws SQLException
     */
    private Team handlResult(ResultSet resultSet) throws SQLException {
        Team team=new Team();
        team.setId(resultSet.getInt("tid"));
        team.setName(resultSet.getString("tname"));
        team.setLocation(resultSet.getString("location"));
        return team;
    }

    /**
     * 查询所有  方法名称里面没有参数，  query里面就不用new Object[]{ 参数1，参数2 }接受
     * @return
     */
    public List<Team> findAll(){
        String sql="select * from team";
        return this.getJdbcTemplate().query(sql, new RowMapper<Team>() {
            @Override
            public Team mapRow(ResultSet resultSet, int i) throws SQLException {
                return handlResult(resultSet);
            }
        });
    }

    /**
     * 查询单个用queryForObject
     * @return
     */
    public Team findById(int tid){
        String sql = "select * from team where tid=?";
          return this.getJdbcTemplate().queryForObject(sql,new Object[]{tid}, new RowMapper<Team>() {
            @Override
            public Team mapRow(ResultSet resultSet, int i) throws SQLException {
                Team team=new Team();
                team.setId(resultSet.getInt("tid"));
                team.setName(resultSet.getString("tname"));
                team.setLocation(resultSet.getString("location"));
                return team;
            }
        });
    }
    /**
     * 增加
     * @param team
     * @return
     */
    public  Integer  insert(Team team){
        String sql="insert into team(tname,location) value (?,?)";
        return this.getJdbcTemplate().update(sql,team.getName(),team.getLocation());
    }

    /**
     * 修改
     * @param team
     * @return
     */
    public int update(Team team){
        String sql="update team set tname=? ,location=? where tid=?";
        return
                this.getJdbcTemplate().update(sql,team.getName(),team.getLocation(),team.getId());
    }

    /**
     * 删除
     * @param id
     * @return
     */
    public int del(int id){
        String sql="delete from team where tid=?";
        return this.getJdbcTemplate().update(sql,id);
    }




}

对应的测试类：

package com.kkb.test01;

import com.kkb.TeamDao.TeamDao;
import com.kkb.pojo.Team;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;
import java.util.Map;
import java.util.Set;

public class test1 {
    ApplicationContext ac = new ClassPathXmlApplicationContext("springjdbc.xml");
    @Test
    public void  insert(){
        TeamDao teamDao = (TeamDao)ac.getBean("teamDao");
        Team team =new Team();
        team.setLocation("河南");
        team.setName("少林队");
        teamDao.insert(team);
    }

    @Test
    public void testFindAll(){
        TeamDao dao= (TeamDao) ac.getBean("teamDao");
        List<Team> all = dao.findAll();
        for (Team team : all) {
            System.out.println(team);
        }
    }
    @Test
    public void testFindById(){
        TeamDao dao= (TeamDao) ac.getBean("teamDao");
        Team t = dao.findById(2);
        System.out.println(t);
    }


    @Test
    public void testDel(){
        TeamDao dao= (TeamDao) ac.getBean("teamDao");
        int res=dao.del(6);
        System.out.println("删除数据的结果："+res);
    }
    @Test
    public void testUpdate(){
        TeamDao dao= (TeamDao) ac.getBean("teamDao");
        Team team=new Team();
        team.setName("小牛");
        team.setLocation("达拉斯");
        team.setId(4);
        int res=dao.update(team);
        System.out.println("更新数据的结果："+res);
    }

    @Test
    public void testGet(){
        TeamDao dao= (TeamDao) ac.getBean("teamDao");
        int count = dao.getCount();
        System.out.println("查询的总行数："+count);
        Map<String, Object> many = dao.getMany();
         //快捷键   many.en    再换行输入iter   enter
        Set<Map.Entry<String, Object>> entries = many.entrySet();
        for (Map.Entry<String, Object> entry : entries) {
            System.out.println(entry.getKey()+"----"+entry.getValue());
        }
    }

}

这里只列举testGet方法的测试结果