Spring的JdbcTemplate的使用

最新推荐文章于 2023-09-08 10:46:26 发布

Luck_ZZ

最新推荐文章于 2023-09-08 10:46:26 发布

阅读量307

点赞数

分类专栏： spring 文章标签： spring JdbcTemplate NamedParameterJdbcTemplate

本文链接：https://blog.csdn.net/Luck_ZZ/article/details/100052174

版权

spring 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、添加依赖

在maven中添加spring依赖、spring数据库模块依赖、c3p0连接池依赖、ojdbc8依赖、spring测试依赖

在maven远程仓库中没有ojdbc8，需要到oracle上下载jar包，添加到本地maven仓库

    <!--spring依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>5.1.9.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>5.1.9.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.1.9.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-expression</artifactId>
      <version>5.1.9.RELEASE</version>
    </dependency>

    <!--spring数据库模块 -->
    <!--spring jdbc-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.1.9.RELEASE</version>
    </dependency>

    <!--spring orm-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-orm</artifactId>
      <version>5.1.9.RELEASE</version>
    </dependency>

    <!--c3p0依赖-->
    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>

    <!--ojdbc8-->
    <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc8</artifactId>
      <version>11.2.0.1.0</version>
    </dependency>

    <!--spring test-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>5.1.9.RELEASE</version>
    </dependency>

    <!--junit依赖-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>

二、配置文件

dbconfig.properties文件保存连接数据库的信息

jdbc.user=scott
jdbc.password=tiger
jdbc.jdbcUrl=jdbc:oracle:thin:@//localhost:1521/orcl
jdbc.driverClass=oracle.jdbc.driver.OracleDriver

spring-conf.xml配置文件

配置c3p0数据库连接池，声明为一个bean，交给spring容器管理；把JdbcTemplate声明为一个bean，并设置c3p0为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">

    <!--引入外部文件-->
   <context:property-placeholder location="classpath:dbconfig.properties"/>

    <!--c3p0 dataSource-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
         <property name="user" value="${jdbc.user}" />
         <property name="password" value="${jdbc.password}" />
         <property name="jdbcUrl" value="${jdbc.jdbcUrl}" />
         <property name="driverClass" value="${jdbc.driverClass}" />
    </bean>

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

</beans>

三、编写实体类

studen表中，有字段sid、sname

实体类Student,java

public class Student {

    private Integer sid;
    private String sname;

    public Student() {
    }

    public Student(Integer sid, String sname) {
        this.sid = sid;
        this.sname = sname;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                '}';
    }

    //set、get方法
}

四、操作

1.修改

调用JdbcTemplate的update方法实现修改

@ContextConfiguration(locations = "classpath:spring-conf.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class MyTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 修改
     */
    @Test
    public void test01(){
        String sql = "update student set sname=? where sid=?";
        int update = jdbcTemplate.update(sql, "zhangsan", 1);
        System.out.println(update);
    }

}

2.批量插入

调用JdbcTemplate的batchUpdate方法实现批量插入

    /**
     * 批量插入
     */
    @Test
    public void test02(){
        String sql = "insert into student(sid, sname) values (?, ?)";
        List<Object[]> batchArgs = new ArrayList<>();
        batchArgs.add(new Object[]{7, "n7"});
        batchArgs.add(new Object[]{8, "n8"});
        batchArgs.add(new Object[]{9, "n9"});

        int[] is = jdbcTemplate.batchUpdate(sql, batchArgs);
        for (int i : is){
            System.out.println(i);
        }

    }

3.查询并返回Java对象

调用JdbcTemplate的queryForObject方法查询，返回Student对象

    /**
     * 返回java对象
     */
    @Test
    public void test03(){
        String sql = "select sid,sname from student where sid = ?";
//        RowMapper
        Student student = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Student.class), 1);
        System.out.println(student);
    }

4.查询并返回集合

调用JdbcTemplate的query方法查询，返回一个List集合

    /**
     * 返回集合
     */
    @Test
    public void test04(){
        String sql = "select sid,sname from student where sid > ?";
        List<Student> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class), 1);
        for(Student s : list){
            System.out.println(s);
        }
    }

5.查询并返回Java包装类

调用JdbcTemplate的queryForObject方法

    /**
     * 查询最大id
     */
    @Test
    public void test05(){
        String sql = "select max(sid) from student";
        Integer i = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(i);
    }

6.使用具名参数

占位符参数，？的顺序不能乱

具名参数，具体名字的参数，参数不是占位符，而是一个变量

要使用具名参数，需要使用NamedParameterJdbcTemplate类

   <!--配置一个具有具名参数功能的jdbcTemplate-->
    <bean id="nameParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"/>
    </bean>

调用NamedParameterJdbcTemplate对象的update进行修改

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Test
    public void test06(){
        String sql = "insert into student(sid, sname) values (:sid, :sname)";
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("sid",20);
        paramMap.put("sname","wang");
        int update = namedParameterJdbcTemplate.update(sql, paramMap);
        System.out.println(update);
    }

    @Test
    public void test07(){
        String sql = "insert into student(sid, sname) values (:sid, :sname)";
        Student student = new Student(30,"wang");
        int update = namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(student));
        System.out.println(update);
    }