04_Spring结合数据库JDBC

张创琦

代码详见 Spring04_jdbc

实体类

package com.kkb.pojo;
public class Team {
    private Integer id;
    private String name;
    private String location;
    public Team() {
    }
    public Team(String name, String location) {
        this.name = name;
        this.location = location;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getLocation() {
        return location;
    }
    public void setLocation(String location) {
        this.location = location;
    }
    @Override
    public String toString() {
        return "Team{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", location='" + location + '\'' +
                '}';
    }
}

TeamDao.java

package com.kkb.dao;
import com.kkb.pojo.Team;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class TeamDao extends JdbcDaoSupport {
    /**
     * 处理我们的结果，自己封装结果的方法
     * @param resultSet
     * @return
     * @throws SQLException
     */
    public Team handleResult(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;
    }
    //插入
    public int insert(Team team) {
        String sql = " insert into team (tname, location) values (?, ?) ";
        return this.getJdbcTemplate().update(sql, team.getName(), team.getLocation());
        //return this.getJdbcTemplate().update(sql, "湖人", "洛杉矶");
        //update适用于增删改
    }
    //更新
    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());
    }
    //删除
    public int del(int id) {
        String sql = " delete from team where tid = ? ";
        return this.getJdbcTemplate().update(sql, id);
    }
    //查询，查询比上面其他三个特殊
    public Team findById(int id){
        String sql = " select * from team where tid=? ";
        return this.getJdbcTemplate().queryForObject(sql, new Object[]{id}, new RowMapper<Team>(){
            @Override
            public Team mapRow(ResultSet resultSet, int i) throws SQLException {
                return handleResult(resultSet);
            }
        });
    }
    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 handleResult(resultSet);
            }
        });
    }
    // getCount 查询受影响的行数
    public int getCount(){
        String sql = " select count(tid) from team ";
        return this.getJdbcTemplate().queryForObject(sql, Integer.class);
    }
    //求tid的最大值和最小值
    public Map<String, Object> getMany(){
        String sql = " select max(tid), min(tid) from team ";
        return this.getJdbcTemplate().queryForMap(sql);
    }
}

测试

package com.kkb.test;
import com.kkb.dao.TeamDao;
import com.kkb.pojo.Team;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import java.beans.PropertyVetoException;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class Test01 {
    ApplicationContext ac = new ClassPathXmlApplicationContext("application.xml");
    @Test
    public void test01() throws PropertyVetoException {
        //创建数据源
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");  //记得抛出异常
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/springJDBC? serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource.setUser("root");
        dataSource.setPassword("123456");
        //使用JDBCTemplate
        JdbcTemplate template = new JdbcTemplate(dataSource);
        String sql = " insert into team (tname, location) values (?, ?) ";
        int update = template.update(sql, "勇士", "金州");
        System.out.println("插入的结果"+ update);
    }
    //基于xml配置实现, insert
    @Test
    public void test02(){
        TeamDao dao = (TeamDao) ac.getBean("teamDao");
        Team team = new Team();
        team.setName("湖人");
        team.setLocation("洛杉矶");
        int res = dao.insert(team);
        System.out.println("插入数据的结果: "+res);
    }
    //基于xml配置实现, update
    @Test
    public void test03(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("application.xml");
        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);
    }
    //基于xml配置实现, delete
    @Test
    public void test04(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("application.xml");
        TeamDao dao = (TeamDao) ac.getBean("teamDao");
        int res = dao.del(4);
        System.out.println("删除数据的结果: "+res);
    }
    //findById 测试查询
    @Test
    public void test05(){
        TeamDao dao = (TeamDao) ac.getBean("teamDao");
        Team team = dao.findById(1);
        System.out.println(team);
    }
    //findAll 测试查询
    @Test
    public void test06(){
        TeamDao dao = (TeamDao) ac.getBean("teamDao");
        List<Team> team = dao.findAll();
        for(Team t: team){
            System.out.println(t);
        }
    }
    //getCount
    @Test
    public void test07(){
        TeamDao dao = (TeamDao) ac.getBean("teamDao");
        int count = dao.getCount();
        System.out.println("受影响的总行数："+count);
    }
    //getMany 获取最大值和最小值
    @Test
    public void test08(){
        TeamDao dao = (TeamDao) ac.getBean("teamDao");
        int count = dao.getCount();
        System.out.println("查询的总行数："+count);
        Map<String, Object> many = dao.getMany();
        Set<Map.Entry<String, Object>> entries = many.entrySet();
        for(Map.Entry<String, Object> entry: entries){
            System.out.println(entry.getKey()+"--------"+entry.getValue());
        }
    }
}

application.xml

注意：& 要写成 & a m p ;

<!-- 创建JdbcTemplate的数据源 -->
    <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>
    <!-- 创建JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <bean id="teamDao" class="com.kkb.dao.TeamDao">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

05_事务管理

张创琦

代码详见 Spring04_jdbc

方法1: 加入@Transactional

package com.kkb.service;
import com.kkb.dao.TeamDao;
import com.kkb.pojo.Team;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
@Service
public class TeamService {
    @Autowired
    private TeamDao teamDao;
    //事务
    //如果注解掉事务的话，需要注释掉以下注解内容，然后通过xml方式配置
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = {Exception.class})
    public int insert(Team team){
        //事务存在时，一条成功另一条失败则会发生回滚
        int num = teamDao.insert(team);
        System.out.println("第一条执行结果 num1: "+ num);
        //System.out.println(10/0);
        int num2 = teamDao.insert(team);
        System.out.println("第一条执行结果 num2: "+ num2);
        return num + num2; //只有1和2都成功才会返回结果
    }
}

方法2: xml注释

xml的优点： 可以统一管理，不用对每一个方法加入注解配置。

第一步：需要将上面一部分代码里的 @Transactional 注释掉

application.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       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/tx http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">
    <!-- context扫描 -->
    <context:component-scan base-package="com.kkb"/>
    <!-- 创建JdbcTemplate的数据源 -->
    <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>
    <!-- 创建JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <bean id="teamDao" class="com.kkb.dao.TeamDao">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>
    <!-- 启动事务的注解，最上方引入content -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!-- 跟注解相关 -->
    <tx:annotation-driven transaction-manager="transactionManager"/>
    <!-- 基于配置文件配置事务注解的话，transactionManager必须存在，tx:annotation-driven不需要存在 -->
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <!-- 默认进行回滚 rollback-for="" -->
            <tx:method name="insert*" propagation="REQUIRED" />
            <tx:method name="add*" propagation="REQUIRED" />
            <!-- find默认只读 read-only="true" -->
            <tx:method name="find*" propagation="SUPPORTS" read-only="true"/>
        </tx:attributes>
    </tx:advice>
    <aop:config>
        <aop:pointcut id="pt" expression="execution(* com.kkb.service..*.*(..))"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="pt"/>
    </aop:config>
</beans>

pom.xml配置相关依赖

<!-- 事务的依赖 -->
    <dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.9.6</version>
    </dependency>

Java 框架相关文档

04_Spring结合数据库JDBC以及事务管理

04_Spring结合数据库JDBC

05_事务管理

方法1: 加入@Transactional

方法2: xml注释