Spring使用JDBC Template进行增，改，删，查

最新推荐文章于 2024-05-16 00:05:47 发布

久许

最新推荐文章于 2024-05-16 00:05:47 发布

阅读量397

点赞数

分类专栏： spring 从入门到进阶

本文链接：https://blog.csdn.net/jiuweideqixu/article/details/89157902

版权

spring 从入门到进阶专栏收录该内容

13 篇文章 0 订阅

订阅专栏

sql.sql

drop database if exists selection_course;

create database selection_course default charset utf8mb4;
use selection_course;

create table course
(
   id                   int not null auto_increment,
   name                 char(20),
   score                int,
   primary key (id)
);

create table selection
(
   student              int not null,
   course               int not null,
   selection_time       datetime,
   score                int,
   primary key (student, course)
);

create table student
(
   id                   int not null auto_increment,
   name                 varchar(20),
   sex                  char(2),
   born                 date,
   primary key (id)
);

alter table selection add constraint FK_Reference_1 foreign key (course)
      references course (id) on delete restrict on update restrict;

alter table selection add constraint FK_Reference_2 foreign key (student)
      references student (id) on delete restrict on update restrict;

insert into course(id,name,score) values(1001,'英语',5);
insert into course(id,name,score) values(1002,'操作系统',5);
insert into course(id,name,score) values(1003,'数据结构',3);

commit;

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: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
    http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/aop
    http://www.springframework.org/schema/aop/spring-aop.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx.xsd">
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/selection_course?useUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <context:component-scan base-package="com.imooc.sc"/>
</beans>

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one
  or more contributor license agreements.  See the NOTICE file
  distributed with this work for additional information
  regarding copyright ownership.  The ASF licenses this file
  to you under the Apache License, Version 2.0 (the
  "License"); you may not use this file except in compliance
  with the License.  You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing,
  software distributed under the License is distributed on an
  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  KIND, either express or implied.  See the License for the
  specific language governing permissions and limitations
  under the License.
-->
<!-- $Id: pom.xml 642118 2008-03-28 08:04:16Z reinhard $ -->
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

  <modelVersion>4.0.0</modelVersion>
  <packaging>war</packaging>

  <name>sc</name>
  <groupId>com.imooc</groupId>
  <artifactId>sc</artifactId>
  <version>1.0-SNAPSHOT</version>

  <build>
    <plugins>
      <plugin>
        <groupId>org.mortbay.jetty</groupId>
        <artifactId>maven-jetty-plugin</artifactId>
        <version>6.1.7</version>
        <configuration>
          <connectors>
            <connector implementation="org.mortbay.jetty.nio.SelectChannelConnector">
              <port>8888</port>
              <maxIdleTime>30000</maxIdleTime>
            </connector>
          </connectors>
          <webAppSourceDirectory>${project.build.directory}/${pom.artifactId}-${pom.version}</webAppSourceDirectory>
          <contextPath>/</contextPath>
        </configuration>
      </plugin>
    </plugins>
  </build>

  <dependencies>
    <!--spring核心jar包-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>4.2.4.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>4.2.4.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>4.2.4.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-expression</artifactId>
      <version>4.2.4.RELEASE</version>
    </dependency>
    <!--spring进行传统AOP开发相关jar包-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aop</artifactId>
      <version>4.2.4.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>aopalliance</groupId>
      <artifactId>aopalliance</artifactId>
      <version>1.0</version>
    </dependency>
    <!--和数据库相关的，驱动，spring整合，事务-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.44</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.2.4.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>4.2.4.RELEASE</version>
    </dependency>
    <!--和测试相关的jar包-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>4.2.4.RELEASE</version>
    </dependency>
    <!--dependency>
      <groupId>com.imooc</groupId>
      <artifactId>[the artifact id of the block to be mounted]</artifactId>
      <version>1.0-SNAPSHOT</version>
    </dependency-->
  </dependencies>

</project>

进行测试

execute方法(可执行DDL语句)

update方法

执行插入操作：（参数放在Object数组内部）

但是可能不能插入中文，解决办法：

alter table student character set utf8;
alter table student change name name varchar(20) character set utf8;
alter table student change sex sex char(2) character set utf8;

执行更新操作：（参数直接在形式参数中写出）

batchUpdate

批量更新

查询简单数据项（需要指定记录的数据类型）

查询一个，使用queryForObject

查询多个，使用queryForList

查询复杂数据类型（不指定数据类型，记录封装到Map中，多条记录时则将每个map添加到List当中）

查询一个

查询多个

查询复杂数据类型（查询结果封装成实体对象，而不是封装成Map）

关键是将实体的每个属性，比如Student类的每个属性和数据库表student中的各个字段相对应。jdbcTemplate通过RowMapper接口来解决这个问题。

查询一个

定义Student类

package com.imooc.sc.entity;

import java.util.Date;

public class Student {
    private int id;
    private String name;
    private String sex;
    private Date born;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBorn() {
        return born;
    }

    public void setBorn(Date born) {
        this.born = born;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", born=" + born +
                '}';
    }
}

查询多个

但是使用匿名内部类的方式会另代码产生duplicate现象，所以最好定义一个私有的内部类来实现RowMapper接口。

    @Test
    public void testQuerySimple5(){
        String sql = "select * from student where id=?";
        Student stu = jdbcTemplate.queryForObject(sql, new StudentRowMapper(),1001);
        System.out.println(stu);
    }

    @Test
    public void testQuerySimple6(){
        String sql = "select * from student where sex=?";
        List<Student> list = jdbcTemplate.query(sql,new Object[]{"男"}, new StudentRowMapper());
        System.out.println(list);
    }

    private class StudentRowMapper implements RowMapper<Student>{
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student stu = new Student();
            stu.setId(resultSet.getInt("id"));
            stu.setName(resultSet.getString("name"));
            stu.setSex(resultSet.getString("sex"));
            stu.setBorn(resultSet.getDate("born"));
            return stu;
        }
    }

持久层实现

目录结构

Course类

package com.imooc.sc.entity;

public class Course {
    private int id;
    private String name;
    private int score;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }
}

Student类

package com.imooc.sc.entity;

import java.util.Date;

public class Student {
    private int id;
    private String name;
    private String sex;
    private Date born;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBorn() {
        return born;
    }

    public void setBorn(Date born) {
        this.born = born;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", born=" + born +
                '}';
    }
}

Selection类

package com.imooc.sc.entity;

import java.util.Date;

public class Selection {
    private int sid;
    private int cid;
    private Date selection_time;
    private int score;

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    public Date getSelection_time() {
        return selection_time;
    }

    public void setSelection_time(Date selection_time) {
        this.selection_time = selection_time;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }
}

CourseDao类

package com.imooc.sc.dao;

import com.imooc.sc.entity.Course;

import java.util.List;

public interface CourseDao {
    void insert(Course stu);
    void update(Course stu);
    void delete(int id);
    Course select(int id);
    List<Course> findAll();
}

StudentDao类

package com.imooc.sc.dao;

import com.imooc.sc.entity.Student;

import java.util.List;

public interface StudentDao {
    void insert(Student stu);
    void update(Student stu);
    void delete(int id);
    Student select(int id);
    List<Student> findAll();
}

SelectionDao类

package com.imooc.sc.dao;

import com.imooc.sc.entity.Selection;

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

public interface SelectionDao {
    void insert(List<Selection> list);
    void delete(int sid, int cid);
    List<Map<String,Object>> selectByStudent(int sid);
    List<Map<String,Object>> selectByCourse(int cid);
}

CourseDaoImpl类

package com.imooc.sc.dao.impl;

import com.imooc.sc.dao.CourseDao;
import com.imooc.sc.entity.Course;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

//把CourseDaoImpl定义为一个Bean，在其他地方可以通过@Resource(name="courseDaoImpl")和@Autowired的方式引入
@Repository//它对应的Bean的默认的id是 courseDaoImpl,即第一个字母是小写的。
public class CourseDaoImpl implements CourseDao {
    
    
    @Resource(name="jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    
    public void insert(Course cou) {
        String sql = "insert into Course(name,score) values(?,?)";
        //如果直接使用jdbc来操作，则需要将java.util.data转换为java.sql.data才能插入到数据库中，但是JdbcTemplate已经封装好了。
        jdbcTemplate.update(sql,cou.getName(),cou.getScore());
    }

    public void update(Course cou) {
        String sql = "update Course set name=?, score=? where id=?";
        jdbcTemplate.update(sql,cou.getName(),cou.getScore(),cou.getId());
    }

    public void delete(int id) {
        String sql = "delete from Course where id=?";
        jdbcTemplate.update(sql,id);
    }

    public Course select(int id) {
        Course cou;
        String sql = "select * from Course where id=?";
        cou = jdbcTemplate.queryForObject(sql,new CourseDaoImpl.CourseRowMapper(),id);
        return cou;
    }

    public List<Course> findAll() {
        String sql = "select * from Course";
        List<Course> list = jdbcTemplate.query(sql,new CourseDaoImpl.CourseRowMapper());
        return list;
    }

    private class CourseRowMapper implements RowMapper<Course> {
        public Course mapRow(ResultSet resultSet, int i) throws SQLException {
            Course cou = new Course();
            cou.setId(resultSet.getInt("id"));
            cou.setName(resultSet.getString("name"));
            cou.setScore(resultSet.getInt("score"));
            return cou;
        }
    }
}

StudentDaoImpl类

package com.imooc.sc.dao.impl;

import com.imooc.sc.dao.StudentDao;
import com.imooc.sc.entity.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class StudentDaoImpl implements StudentDao {

    @Resource(name="jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    public void insert(Student stu) {
        String sql = "insert into student(name,sex,born) values(?,?,?)";
        //如果直接使用jdbc来操作，则需要将java.util.data转换为java.sql.data才能插入到数据库中，但是JdbcTemplate已经封装好了。
        jdbcTemplate.update(sql,stu.getName(),stu.getSex(),stu.getBorn());
    }

    public void update(Student stu) {
        String sql = "update student set name=?,sex=?,born=? where id=?";
        jdbcTemplate.update(sql,stu.getName(),stu.getSex(),stu.getBorn(),stu.getId());
    }

    public void delete(int id) {
        String sql = "delete from student where id=?";
        jdbcTemplate.update(sql,id);
    }

    public Student select(int id) {
        Student stu;
        String sql = "select * from student where id=?";
        stu = jdbcTemplate.queryForObject(sql,new StudentRowMapper(),id);
        return stu;
    }

    public List<Student> findAll() {
        String sql = "select * from student";
        List<Student> list = jdbcTemplate.query(sql,new StudentRowMapper());
        return list;
    }

    //可将查询的结果封装为一个对应的实体
    private class StudentRowMapper implements RowMapper<Student>{
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student stu = new Student();
            stu.setId(resultSet.getInt("id"));
            stu.setName(resultSet.getString("name"));
            stu.setSex(resultSet.getString("sex"));
            stu.setBorn(resultSet.getDate("born"));
            return stu;
        }
    }
}

SelectionDaoImpl类

package com.imooc.sc.dao.impl;

import com.imooc.sc.dao.SelectionDao;
import com.imooc.sc.entity.Selection;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@Repository
public class SelectionDaoImpl implements SelectionDao {

    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;


    //批量插入数据时还可以用batchUpdate方法，只不过List内部的数据类型是Object，不是Selecton。
    public void insert(List<Selection> list) {
        String sql = "insert into selection(student,course,selection_time,score) values(?,?,?,?)";
        int len = list.size();
        Selection sel;
        for(int i=0;i<len;i++){
            sel = list.get(i);
            System.out.println(sel);
            jdbcTemplate.update(sql,sel.getSid(),sel.getCid(),sel.getSelection_time(),sel.getScore());
        }
    }

    public void delete(int sid,int cid) {
        String sql = "delete from selection where student=? and course=?";
        jdbcTemplate.update(sql,sid,cid);
    }

    //这里建议使用连接查询，因为直接查找出来的学生号和课程号人们看不懂
    public List<Map<String, Object>> selectByStudent(int sid) {
        String sql = "select se.*,s.name sname,c.name cname from selection se " +
                "left join student s on se.student = s.id " +
                "left join course c on se.course = c.id " +
                "where s.id = ?";
        return jdbcTemplate.queryForList(sql,sid);
    }

    public List<Map<String, Object>> selectByCourse(int cid) {
        String sql = "select se.*,s.name sname,c.name cname from selection se " +
                "left join student s on se.student = s.id " +
                "left join course c on se.course = c.id " +
                "where c.id = ?";
        return jdbcTemplate.queryForList(sql,cid);
    }
}

SpringDemo01类

package com.imooc.sc;

import com.imooc.sc.dao.CourseDao;
import com.imooc.sc.dao.SelectionDao;
import com.imooc.sc.entity.Course;
import com.imooc.sc.entity.Selection;
import com.imooc.sc.entity.Student;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring.xml")
public class SpringDemo01 {

    @Resource(name="jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void demo01(){
        jdbcTemplate.execute("create table user1(id int,name varchar(20))");
    }

    @Test
    public void testUpdate01(){
        String sql = "insert into student(name,sex) values(?,?)";
        jdbcTemplate.update(sql,new Object[]{"张飞","男"});
    }

    @Test
    public void testUpdate02(){
        String sql = "update student set sex=? where id=?";
        jdbcTemplate.update(sql,"女","1001");
    }

    @Test
    public void testBatchUpdate(){
        String[] sqls = {
                "insert into student(name,sex) values('关羽','男')",
                "insert into student(name,sex) values('刘备','男')",
                "update student set name='貂蝉' where id=1001"
        };
        jdbcTemplate.batchUpdate(sqls);
    }

    //让学生选课，批量进行选课操作
    @Test
    public void testBatchUpdate02(){
        String sql = "insert into selection(student,course) values(?,?)";
        List<Object[]> list = new ArrayList<Object[]>();
        list.add(new Object[]{1001,1002});
        list.add(new Object[]{1009,1002});
        jdbcTemplate.batchUpdate(sql,list);
    }

    @Test
    public void testQuerySimple1(){
        String sql = "select count(*) from student";
        int count = jdbcTemplate.queryForObject(sql,Integer.class);
        System.out.println(count);
    }

    @Test
    public void testQuerySimple2(){
        String sql = "select name from student where sex=?";
        List<String> list = jdbcTemplate.queryForList(sql,String.class,"男");
        System.out.println(list);
    }

    @Test
    public void testQuerySimple3(){
        String sql = "select * from student where id=?";
        Map<String,Object> map = jdbcTemplate.queryForMap(sql,1001);
        System.out.println(map);
    }

    @Test
    public void testQuerySimple4(){
        String sql = "select * from student";
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
        System.out.println(list);
    }

    @Test
    public void testQuerySimple5(){
        String sql = "select * from student where id=?";
        Student stu = jdbcTemplate.queryForObject(sql, new StudentRowMapper(),1001);
        System.out.println(stu);
    }

    @Test
    public void testQuerySimple6(){
        String sql = "select * from student where sex=?";
        List<Student> list = jdbcTemplate.query(sql,new Object[]{"男"}, new StudentRowMapper());
        System.out.println(list);
    }

    private class StudentRowMapper implements RowMapper<Student>{
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student stu = new Student();
            stu.setId(resultSet.getInt("id"));
            stu.setName(resultSet.getString("name"));
            stu.setSex(resultSet.getString("sex"));
            stu.setBorn(resultSet.getDate("born"));
            return stu;
        }
    }

    @Resource(name = "courseDaoImpl")
    private CourseDao courseDao;

/*    @Test
    public void testDelete(){
        courseDao.delete(1004);
    }

    @Test
    public void testInsert(){
        Course cou = new Course();
        cou.setId(1001);
        cou.setName("英语");
        cou.setScore(5);
        courseDao.insert(cou);
    }*/

/*    @Resource(name="selectionDaoImpl")
    SelectionDao selectionDao;*/

    /*
    @Test
    public void test01(){
        System.out.println(selectionDao.selectByCourse(1002));;
    }*/

/*    @Test
    public void test02(){
        List<Selection> list = new ArrayList<Selection>();
        Selection sel1 = new Selection();
        Selection sel2 = new Selection();


        sel1.setSid(1011);
        sel1.setCid(1002);
        sel1.setSelection_time(new Date());
        sel1.setScore(7);
        list.add(sel1);

        sel2.setSid(1011);
        sel2.setCid(1003);
        sel2.setSelection_time(new Date());
        sel2.setScore(7);
        list.add(sel2);
        System.out.println(list);

        selectionDao.insert(list);
    }*/
}

久许

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Spring使用JDBC Template进行增，改，删，查

sql.sqldrop database if exists selection_course;create database selection_course default charset utf8mb4;use selection_course;create table course( id int not null auto_...
复制链接

扫一扫