第八篇 Spring 集成JdbcTemplate

《Spring》篇章整体栏目
—————————————————————————————
【第一章】spring 概念与体系结构
【第二章】spring IoC 的工作原理
【第三章】spring IOC与Bean环境搭建与应用
【第四章】spring bean定义
【第五章】Spring 集合注入、作用域
【第六章】Spring 自动装配
【第七章】spring AOP
【第八章】Spring 集成JdbcTemplate
【第九章】Spring数据库事务管理
【第十章】Spring 集成Redis
【第十一章】Spring实战之打造新闻系统后端接口
—————————————————————————————

—————————————————————————————

前言

Spring 提供了一个 Spring JDBC 模块，它对 JDBC API 进行了封装，其的主要目的降低 JDBC API 的使用难度，以一种更直接、更简洁的方式使用 JDBC API。

1、导包

spring-beans-5.3.13.RELEASE.jar
spring-context-5.3.13.RELEASE.jar
spring-core-5.3.13.RELEASE.jar
spring-expression-5.3.13.RELEASE.jar
commons-logging-1.2.jar
spring-jdbc-5.3.13.RELEASE.jar
spring-tx-5.3.13.RELEASE.jar
spring-aop-5.3.13.jar
mysql-connector-java-8.0.23.jar

备注：自行去Maven资源库搜 -> https://mvnrepository.com/

JdbcTemplate 提供的方法以及说明

2、案例

2.1、创建表

CREATE TABLE
IF NOT EXISTS house_info (
id BIGINT PRIMARY KEY auto_increment COMMENT '主键',
NAME VARCHAR (50) COMMENT '房屋名称',
type VARCHAR (50) COMMENT '房屋类型',
address VARCHAR (50) COMMENT '房屋地址'
) COMMENT '房屋信息表';

2.2、创建连接数据库配置文件jdbc.properties(目标src)

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/spring
jdbc.username=root
jdbc.password=root

2.3、创建配置文件house.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"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.0.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd"><!--开启组建扫描--><context:component-scan base-package="com.xxxx.spring"/><!--引入jdbc.properties 配置--><context:property-placeholder location="classpath:jdbc.properties"/><bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/><property name="driverClassName" value="${jdbc.driver}"/></bean><bean id = "jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"/></bean></beans>

2.4、定义HouseInfo实体bean

package com.xxxx.spring.entity;/*** 房屋信息*/
public class HouseInfo {/*** 房屋名称*/private String name;/*** 房屋类型*/private String type;/*** 房屋地址*/private String address;public String getName() {return name;}public void setName(String name) {this.name = name;}public String getType() {return type;}public void setType(String type) {this.type = type;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}@Overridepublic String toString() {return "HouseInfo{" +"name='" + name + '\'' +", type='" + type + '\'' +", address='" + address + '\'' +'}';}
}

2.5、定义dao

package com.xxxx.spring.dao;import com.xxxx.spring.entity.HouseInfo;
import java.util.List;/*** 房屋信息DAO类*/
public interface HouseInfoDAO {/*** 新增房屋信息* @param houseInfo* @return*/public int insertHouseInfo(HouseInfo houseInfo);/*** 修改房屋信息* @param houseInfo* @return*/public int updateHouseInfo(HouseInfo houseInfo);/*** 删除房屋信息* @param houseInfo* @return*/public int deleteHouseInfo(HouseInfo houseInfo);/*** 查询房屋信息列表* @param houseInfo* @return*/public List<HouseInfo> selectHouseInfoList(HouseInfo houseInfo);/*** 获取房屋信息对象* @param houseInfo* @return*/public HouseInfo getHouseInfo(HouseInfo houseInfo);/*** 批量插入房屋信息* @param batchArgs*/public void batchInsertHouseInfo(List<Object[]> batchArgs);
}

package com.xxxx.spring.dao.impl;import com.xxxx.spring.dao.HouseInfoDAO;
import com.xxxx.spring.entity.HouseInfo;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.annotation.Resource;
import java.util.List;public class HouseInfoDAOImpl implements HouseInfoDAO {@Resourceprivate JdbcTemplate jdbcTemplate;@Overridepublic int insertHouseInfo(HouseInfo houseInfo) {String sql = " INSERT INTO house_info (NAME, type, address) " +" VALUES " +" (?,?,?) ";return this.jdbcTemplate.update(sql,houseInfo.getName(),houseInfo.getType(),houseInfo.getAddress());}@Overridepublic int updateHouseInfo(HouseInfo houseInfo) {String sql = "UPDATE house_info set address =? where name=?";return this.jdbcTemplate.update(sql,houseInfo.getAddress(),houseInfo.getName());}@Overridepublic int deleteHouseInfo(HouseInfo houseInfo) {String sql = "delete from  house_info where name=?";return this.jdbcTemplate.update(sql,houseInfo.getName());}@Overridepublic List<HouseInfo> selectHouseInfoList(HouseInfo houseInfo) {String sql = "select * from house_info where type=?";return this.jdbcTemplate.query(sql,new BeanPropertyRowMapper<HouseInfo>(HouseInfo.class),houseInfo.getType());}@Overridepublic HouseInfo getHouseInfo(HouseInfo houseInfo) {String  sql = "select * from house_info where id=?";return this.jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<HouseInfo>(HouseInfo.class),houseInfo.getId());}@Overridepublic void batchInsertHouseInfo(List<Object[]> batchArgs) {String sql = "INSERT INTO house_info (NAME, type, address) " +"VALUES " +" (?,?,?) ";this.jdbcTemplate.batchUpdate(sql,batchArgs);}
}

2.6、定义service

package com.xxx.spring.service;import com.xxx.spring.entity.HouseInfo;
import java.util.List;/*** 房屋信息DAO类*/
public interface HouseInfoService {/*** 新增房屋信息* @param houseInfo* @return*/public int insertHouseInfo(HouseInfo houseInfo);/*** 修改房屋信息* @param houseInfo* @return*/public int updateHouseInfo(HouseInfo houseInfo);/*** 删除房屋信息* @param houseInfo* @return*/public int deleteHouseInfo(HouseInfo houseInfo);/*** 查询房屋信息列表* @param houseInfo* @return*/public List<HouseInfo> selectHouseInfoList(HouseInfo houseInfo);/*** 获取房屋信息对象* @param houseInfo* @return*/public HouseInfo getHouseInfo(HouseInfo houseInfo);/*** 批量插入房屋信息* @param batchArgs*/public void batchInsertHouseInfo(List<Object[]> batchArgs);
}

package com.xxxx.spring.service.impl;import com.xxxx.spring.dao.HouseInfoDAO;
import com.xxxx.spring.entity.HouseInfo;
import com.xxxx.spring.service.HouseInfoService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;@Service("houseInfoService")
public class HouseInfoServiceImpl implements HouseInfoService {@Resourceprivate HouseInfoDAO houseInfoDAO;@Overridepublic int insertHouseInfo(HouseInfo houseInfo) {return this.houseInfoDAO.insertHouseInfo(houseInfo);}@Overridepublic int updateHouseInfo(HouseInfo houseInfo) {return this.houseInfoDAO.updateHouseInfo(houseInfo);}@Overridepublic int deleteHouseInfo(HouseInfo houseInfo) {return this.houseInfoDAO.deleteHouseInfo(houseInfo);}@Overridepublic List<HouseInfo> selectHouseInfoList(HouseInfo houseInfo) {return this.houseInfoDAO.selectHouseInfoList(houseInfo);}@Overridepublic HouseInfo getHouseInfo(HouseInfo houseInfo) {return this.houseInfoDAO.getHouseInfo(houseInfo);}@Overridepublic void batchInsertHouseInfo(List<Object[]> batchArgs) {this.houseInfoDAO.batchInsertHouseInfo(batchArgs);}
}

2.7、测试

public static void main(String[] args) {ClassPathXmlApplicationContext classPathXmlApplicationContext = new ClassPathXmlApplicationContext("house.xml");HouseInfoService houseInfoService = classPathXmlApplicationContext.getBean("houseInfoService", HouseInfoService.class);HouseInfo houseInfo = new HouseInfo();houseInfo.setName("雪山飞狐");houseInfo.setType("三房一厅");houseInfo.setAddress("深圳市");houseInfoService.insertHouseInfo(houseInfo);
}