Spring 之 Spring JdbcTemplate的使用

1.准备

源码下载链接

https://share.weiyun.com/Iax0hI64

0. 数据表

create table user(
uid int  not null PRIMARY key AUTO_INCREMENT,
username VARCHAR(20) not null,
password VARCHAR(20) not null
)ENGINE=INNODB DEFAULT CHARSET=utf8;

insert into user values(1,"zhenqk","12345");
insert into user values(2,"sun","12345");
insert into user values(3,"小白","123");
insert into user values(4,"zhen","12345");

1.导入依赖

 <dependencies>
<!-- 测试 -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

<!-- spring ioc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.2.1.RELEASE</version>
    </dependency>
<!-- spring aop-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aspects</artifactId>
      <version>5.2.1.RELEASE</version>
    </dependency>
<!-spring jdbc-->
    <dependency>
      <groupId>.org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.2.1.RELEASE</version>
      <type>pom</type>
    </dependency>
<!-  数据库连接池-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.9</version>
    </dependency>
<!-  mysqljar  -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>
  </dependencies>

2.db.properties

druid.driver=com.mysql.jdbc.Driver
druid.url=jdbc:mysql://localhost:3306/kdb2
druid.username=root
druid.password=root

3.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: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
       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/aop
       https://www.springframework.org/schema/aop/spring-aop.xsd
         http://www.springframework.org/schema/tx
       https://www.springframework.org/schema/tx/spring-tx.xsd ">

   <context:component-scan base-package="com.ccut"></context:component-scan>
    <!--扫描aop注解-->
    <!--<aop:aspectj-autoproxy></aop:aspectj-autoproxy>-->
    <!--引入外部的资源文件-->
    <context:property-placeholder location="classpath:db.properties"></context:property-placeholder>

    <!--连接数据库的数据源信息 可以使用连接池进行连接
    数据源：可以使用该配置完成数据库的连接操作
    -->
    <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource" >
        <!--<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/bjpowernode"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>-->
          <property name="driverClassName" value="${druid.driver}"></property>
          <property name="url" value="${druid.url}"></property>
          <property name="username" value="${druid.username}"></property>
          <property name="password" value="${druid.password}"></property>
    </bean>
    <!--将数据源交给spring下jdbc模块中的核心处理类
    JdbcTemplate核心的类，操作的类
    -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="druidDataSource"></property>
    </bean>


    <bean class="org.springframework.jdbc.datasource.DataSourceTransactionManager" id="transactionManager">
        <property name="dataSource" ref="druidDataSource"></property>
    </bean>



<tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
</beans>

2.实体类User.java

main/java/com/ccut/pojo/User.java

public class User {
    private  int uid;
    private String username;
    private String password;

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    public User() {
    }

    public User(int uid, String username, String password) {
        this.uid = uid;
        this.username = username;
        this.password = password;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

3.测试方法使用

import java.util.List;
public class Test {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicatinContext.xml");
    JdbcTemplate jdbcTemplate1 = context.getBean("jdbcTemplate", JdbcTemplate.class);
    @org.junit.Test
    public  void testDB(){
        System.out.println(jdbcTemplate1);
    }
    /**
     * DML语句
     * 使用的是共同的update方法,只需要替换不同的sql即可
     */
    @org.junit.Test
    public  void testUpdate(){
        String sql = "update user set username=?,password=? where uid=?";
        //参数赋值按照占位符顺序
        jdbcTemplate1.update(sql,"jeck","abc",3);
    }
    /**
     * DQL语句
     */
    @org.junit.Test
    public  void testqueryObject(){//获取到一条记录通过一个Java中实体类对象进行接收
        String sql = "select uid,username,password from user where uid=?";
        //将数据库查询出来的条数据通过RowMapper对象进行封装为泛型类型
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
        //参数赋值按照占位符顺序
        User user = jdbcTemplate1.queryForObject(sql, rowMapper, 2);
        System.out.println(user);
    }
    @org.junit.Test
    public  void testqueryValue(){//获取到一个独立的数据
        String sql = "select count(uid) from user";
        //参数赋值按照占位符顺序
        int count = jdbcTemplate1.queryForObject(sql, Integer.class);
        System.out.println(count);
    }
    @org.junit.Test
    public  void testqueryList(){//获取到多条记录，通过list集合进行接收查询数据
        String sql = "select uid,username,password from user";
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
        //参数赋值按照占位符顺序
        List<User> userList = jdbcTemplate1.query(sql, rowMapper);
        System.out.println(userList);

    }
      // 测试 事务
    @org.junit.Test
    public  void testUserDao0(){
        UserDao userDaoImpl = context.getBean("userDaoImpl", UserDao.class);
        User u=new User(1,"王振强","12345");
        Boolean b=userDaoImpl.testUpdate(u);
        System.out.println(b);
    }


}

4. 与dao结合使用

1.dao层

IUserDao.java

public interface UserDao {
    /**
     * 获取一条记录
     */
    User testqueryObject(Integer uid);
Boolean  testUpdate(User user);
}

Impl.UserDaoImpl.java

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public User testqueryObject(Integer uid) {
        String sql = "select uid,username,password from user where uid=?";
        //将数据库查询出来的条数据通过RowMapper对象进行封装为泛型类型
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
        //参数赋值按照占位符顺序
        return jdbcTemplate.queryForObject(sql, rowMapper, uid);
    }

    @Override
    @Transactional(isolation=Isolation.REPEATABLE_READ,propagation = Propagation.REQUIRES_NEW)
    public  Boolean  testUpdate(User user){
        String sql = "update user set username=?,password=? where uid=?";
        //参数赋值按照占位符顺序
      //  异常代码  int o=3/0;
        int a=jdbcTemplate.update(sql,user.getUsername(),user.getPassword(),user.getUid());
        if(a>0){
            return true;
        }
        return false;
    }
}

2. test类

    /**
     * 测试在dao层使用spring中JDBC模块
     */
    @org.junit.Test
    public  void testUserDao(){
        UserDao userDaoImpl = context.getBean("userDaoImpl", UserDao.class);
        User user = userDaoImpl.testqueryObject(2);
        System.out.println(user);
    }