Spring Boot中使用JdbcTemplate

一、前言在Spring Boot开启JdbcTemplate很简单只需要引入spring-boot-starter-jdbc依赖即可。JdbcTemplate封装了许多SQL操作具体可查阅官方文档https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html。二、引入依赖spring-boot-starter-jdbcdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-jdbc/artifactId/dependency数据库驱动为mysql数据源采用Druid。具体可参考《Spring Boot整合MyBatis》。三、代码编写3.1 数据准备drop table if exists student; create table student ( sno int(11) not null auto_increment comment 学号, sname varchar(50) character set utf8 collate utf8_general_ci not null comment 姓名, ssex varchar(2) character set utf8 collate utf8_general_ci not null comment 性别, primary key (sno) using btree ) engine innodb auto_increment 1 character set utf8 collate utf8_general_ci row_format dynamic; insert into student values (1, KangKang, M); insert into student values (2, Mike, M); insert into student values (3, Jane, F);这里主要演示在Dao的实现类里使用JdbcTemplate所以其它模块代码的编写就不展示了具体可参考文末的源码。3.2 Dao层StudentDao接口代码ComponentpublicinterfaceStudentDao{intadd(Studentstudent);intupdate(Studentstudent);intdeleteBysno(intsno);ListMapString,ObjectqueryStudentsListMap();StudentqueryStudentBySno(intsno);}StudentDaoImpl类代码Repository(studentDao)publicclassStudentDaoImplimplementsStudentDao{AutowiredprivateJdbcTemplatejdbcTemplate;Overridepublicintadd(Studentstudent){// String sql insert into student(sno,sname,ssex) values(?,?,?);// Object[] args { student.getSno(), student.getName(), student.getSex() };// int[] argTypes { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR };// return this.jdbcTemplate.update(sql, args, argTypes);Stringsqlinsert into student(sno,sname,ssex) values(:sno,:name,:sex);NamedParameterJdbcTemplatenpjtnewNamedParameterJdbcTemplate(this.jdbcTemplate.getDataSource());returnnpjt.update(sql,newBeanPropertySqlParameterSource(student));}Overridepublicintupdate(Studentstudent){Stringsqlupdate student set sname ?,ssex ? where sno ?;Object[]args{student.getName(),student.getSex(),student.getSno()};int[]argTypes{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR};returnthis.jdbcTemplate.update(sql,args,argTypes);}OverridepublicintdeleteBysno(intsno){Stringsqldelete from student where sno ?;Object[]args{sno};int[]argTypes{Types.VARCHAR};returnthis.jdbcTemplate.update(sql,args,argTypes);}OverridepublicListMapString,ObjectqueryStudentsListMap(){Stringsqlselect * from student;returnthis.jdbcTemplate.queryForList(sql);}OverridepublicStudentqueryStudentBySno(intsno){Stringsqlselect * from student where sno ?;Object[]args{sno};int[]argTypes{Types.VARCHAR};ListStudentstudentListthis.jdbcTemplate.query(sql,args,argTypes,newStudentMapper());if(studentList!nullstudentList.size()0){returnstudentList.get(0);}else{returnnull;}}}在引入spring-boot-starter-jdbc驱动后可直接在类中注入JdbcTemplate。由上面代码可发现对于保存操作有两种不同的方法当插入的表字段较多的情况下推荐使用NamedParameterJdbcTemplate。3.3 Mapper对于返回结果可以直接使用ListMapString, Object来接收这也是个人比较推荐使用的方式毕竟比较简单方便也可以使用库表对应的实体对象来接收不过这时候我们就需要手动创建一个实现了org.springframework.jdbc.core.RowMapper的对象用于将实体对象属性和库表字段一一对应publicclassStudentMapperimplementsRowMapperStudent{OverridepublicStudentmapRow(ResultSetrs,introwNum)throwsSQLException{StudentstudentnewStudent();student.setSno(rs.getInt(sno));student.setName(rs.getString(sname));student.setSex(rs.getString(ssex));returnstudent;}}3.4 控制层TestControllerRestControllerpublicclassTestController{AutowiredprivateStudentServicestudentService;RequestMapping(value/querystudent,methodRequestMethod.GET)publicStudentqueryStudentBySno(intsno){returnthis.studentService.queryStudentBySno(sno);}RequestMapping(value/addstudent,methodRequestMethod.GET)publicintaddstudent(Studentstudent){returnthis.studentService.add(student);}RequestMapping(value/queryallstudent,methodRequestMethod.GET)publicListMapString,Objectqueryallstudent(){returnthis.studentService.queryStudentsListMap();}RequestMapping(value/deletestudent,methodRequestMethod.GET)publicintdeletestudent(intsno){returnthis.studentService.deleteBysno(sno);}}四、测试最终项目目录如下图所示启动项目测试插入数据 http://localhost:8080/addstudent?sno4nameMariasexF查询所有学生数据http://localhost:8080/queryallstudent测试删除http://localhost:8080/deletestudent?sno4