Spring的JdbcTemplate的基本操作
Spring的JdbcTemplate的基本操作入门
JdbcTemplate概述 :
JdbcTemplate是spring框架中提供的一个对象，是对原始繁琐的Jdbc API对象的简单封装。spring框架为我们提供了很多的操作模板类。例如：操作关系型数据的JdbcTemplate和，操作nosql数据库的RedisTemplate，操作消息队列的JmsTemplate等等。
1 创建项目，导入jar包，准备相关配置文件
pom.xml:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns=".0.0"xmlns:xsi=""xsi:schemaLocation=".0.0 .0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>org.example</groupId><artifactId>spring_jdbcTemplate_01</artifactId><version>1.0-SNAPSHOT</version><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.1</version><configuration><source>8</source><target>8</target></configuration></plugin></plugins></build><properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target></properties><!--spring核心--><dependencies><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>5.3.21</version></dependency><!--切面--><dependency><groupId>org.springframework</groupId><artifactId>spring-aspects</artifactId><version>5.3.16</version></dependency><!--spring整合事务--><dependency><groupId>org.springframework</groupId><artifactId>spring-tx</artifactId><version>5.3.21</version></dependency><!--orm对象关系映射--><dependency><groupId>org.springframework</groupId><artifactId>spring-orm</artifactId><version>5.3.16</version></dependency><!--方便开发pojo类--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.22</version></dependency><!--spring与jdbc整合--><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>5.3.21</version></dependency><!--Spring日志--><dependency><groupId>commons-logging</groupId><artifactId>commons-logging</artifactId><version>1.2</version></dependency><!--数据库驱动--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.28</version></dependency><!--连接池--><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.6</version></dependency><!--测试--><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.2</version><scope>test</scope></dependency></dependencies>
</project>
jdbc.properties:
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
jdbc.username=root
jdbc.password=123456
applicationContext.xml:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns=""xmlns:xsi=""xmlns:context=""xsi:schemaLocation="://www.springframework.org/schema/beans/spring-beans.xsd://www.springframework.org/schema/context/spring-context.xsd"><context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder><context:component-scan base-package="com.zi"></context:component-scan><bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"><property name="driverClassName" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></bean><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"/></bean>
</beans>
2 创建实体类及相关dao、service
Emp.class
package com.zi.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.io.Serializable;
import java.util.Date;@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp implements Serializable {private Integer empno;private String ename;private String job;private Integer mgr;private Date hiredate;private Double sal;private Double comm;private Integer deptno;
}EmpDao:
package com.zi.dao;import com.zi.pojo.Emp;public interface EmpDao {int findEmpCount();Emp findEmpByEmpno(int empno);int addEmp(Emp emp);int updateEmp(Emp emp);int deleteEmpByEmpno(int empno);
}EmpDaoImpl:
package com.zi.dao.impl;import com.zi.dao.EmpDao;
import com.zi.pojo.Emp;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;@Repository
public class EmpDaoImpl implements EmpDao {@Autowiredprivate JdbcTemplate jdbcTemplate;/*** 查询员工个数* @return*/@Overridepublic int findEmpCount() {String sql = "select count(1) from emp";Integer count = jdbcTemplate.queryForObject(sql, Integer.class);return count;}/*** 根据员工编号查询员工* @param empno 编号* @return*/@Overridepublic Emp findEmpByEmpno(int empno) {String sql = "select * from emp where empno = ?";BeanPropertyRowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);Emp emp = jdbcTemplate.queryForObject(sql, rowMapper, empno);return emp;}/*** 增加员工* @param emp* @return*/@Overridepublic int addEmp(Emp emp) {String sql = "insert into emp values(DEFAULT,?,?,?,?,?,?,?)";Object[] args = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno()};return jdbcTemplate.update(sql, args);}/*** 更新员工信息* @param emp* @return*/@Overridepublic int updateEmp(Emp emp) {String sql = "update emp set ename = ?, job = ?, mgr = ?, hiredate = ?, sal = ?, comm = ?, deptno = ? where empno = ?";Object[] args = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno(), emp.getEmpno()};int row = jdbcTemplate.update(sql, args);return row;}@Overridepublic int deleteEmpByEmpno(int empno) {String sql = "delete from emp where empno = ?";int i = jdbcTemplate.update(sql, empno);return i;}
}EmpService:
package com.zi.service;import com.zi.pojo.Emp;public interface EmpService {int findEmpCount();Emp findEmpByEmpno(int empno);int addEmp(Emp emp);int updateEmp(Emp emp);int deleteEmpByEmpno(int empno);
}EmpServiceImpl:
package com.zi.service.impl;import com.zi.dao.EmpDao;
import com.zi.pojo.Emp;
import com.zi.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;@Service
public class EmpServiceImpl implements EmpService {@Autowiredprivate EmpDao empDao;@Overridepublic int findEmpCount() {return empDao.findEmpCount();}@Overridepublic Emp findEmpByEmpno(int empno) {return empDao.findEmpByEmpno(empno);}@Overridepublic int addEmp(Emp emp) {return empDao.addEmp(emp);}@Overridepublic int updateEmp(Emp emp) {return empDao.updateEmp(emp);}@Overridepublic int deleteEmpByEmpno(int empno) {return empDao.deleteEmpByEmpno(empno);}
}3 测试
package com.zi.test;import com.zi.pojo.Emp;
import com.zi.service.EmpService;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;import java.util.Date;public class Test01 {private ApplicationContext applicationContext = null;private EmpService empService = null;@Beforepublic void init(){applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");empService = applicationContext.getBean(EmpService.class);}@Testpublic void testFindEmpCount(){System.out.println(empService.findEmpCount());}@Testpublic void testFindEmpByEmpno(){Emp emp = empService.findEmpByEmpno(7936);System.out.println(emp);//Emp(empno=7936, ename=curry, job=SG, mgr=7839, hiredate=2022-07-07 00:00:00.0, sal=6666.0, comm=300.0, deptno=20)}@Testpublic void testAddEmp(){Emp emp = new Emp(null, "Rose", "SmallBoss", 7902, new Date(), 4000.0, 500.0, 20);int row = empService.addEmp(emp);System.out.println(row);}@Testpublic void updateEmp(){Emp emp = new Emp(7938, "大漂亮郭艾伦", "PG", 7902, new Date(), 6000.0, 700.0, 10);int i = empService.updateEmp(emp);System.out.println(i);}@Testpublic void deleteEmpByEmpno(){int i = empService.deleteEmpByEmpno(7938);System.out.println(i);}
}4 拓展【JdbcTemplate的批处理操作】
EmpServiceImpl:
/*** 批量添加员工* @param emps* @return*/
@Override
public int[] empBathAdd(List<Emp> emps) {String sql = "insert into emp values(DEFAULT,?,?,?,?,?,?,?)";List<Object[]> args = new ArrayList<>();for (Emp emp : emps) {Object[] arg = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno()};args.add(arg);}return jdbcTemplate.batchUpdate(sql, args);
}
测试类：
@Test
public void empBatchAdd(){List<Emp> emps = new ArrayList<>();for (int i = 0; i < 15; i++) {Emp emp = new Emp(null, "" + i, "" + i, 7902, new Date(), 6000.0, 700.0, 20);emps.add(emp);}int[] row = empService.empBathAdd(emps);System.out.println(Arrays.toString(row));//[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
}
Spring的JdbcTemplate的基本操作
Spring的JdbcTemplate的基本操作入门
JdbcTemplate概述 :
JdbcTemplate是spring框架中提供的一个对象，是对原始繁琐的Jdbc API对象的简单封装。spring框架为我们提供了很多的操作模板类。例如：操作关系型数据的JdbcTemplate和，操作nosql数据库的RedisTemplate，操作消息队列的JmsTemplate等等。
1 创建项目，导入jar包，准备相关配置文件
pom.xml:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns=".0.0"xmlns:xsi=""xsi:schemaLocation=".0.0 .0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>org.example</groupId><artifactId>spring_jdbcTemplate_01</artifactId><version>1.0-SNAPSHOT</version><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.1</version><configuration><source>8</source><target>8</target></configuration></plugin></plugins></build><properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target></properties><!--spring核心--><dependencies><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>5.3.21</version></dependency><!--切面--><dependency><groupId>org.springframework</groupId><artifactId>spring-aspects</artifactId><version>5.3.16</version></dependency><!--spring整合事务--><dependency><groupId>org.springframework</groupId><artifactId>spring-tx</artifactId><version>5.3.21</version></dependency><!--orm对象关系映射--><dependency><groupId>org.springframework</groupId><artifactId>spring-orm</artifactId><version>5.3.16</version></dependency><!--方便开发pojo类--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.22</version></dependency><!--spring与jdbc整合--><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>5.3.21</version></dependency><!--Spring日志--><dependency><groupId>commons-logging</groupId><artifactId>commons-logging</artifactId><version>1.2</version></dependency><!--数据库驱动--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.28</version></dependency><!--连接池--><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.6</version></dependency><!--测试--><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.2</version><scope>test</scope></dependency></dependencies>
</project>
jdbc.properties:
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
jdbc.username=root
jdbc.password=123456
applicationContext.xml:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns=""xmlns:xsi=""xmlns:context=""xsi:schemaLocation="://www.springframework.org/schema/beans/spring-beans.xsd://www.springframework.org/schema/context/spring-context.xsd"><context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder><context:component-scan base-package="com.zi"></context:component-scan><bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"><property name="driverClassName" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></bean><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"/></bean>
</beans>
2 创建实体类及相关dao、service
Emp.class
package com.zi.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.io.Serializable;
import java.util.Date;@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp implements Serializable {private Integer empno;private String ename;private String job;private Integer mgr;private Date hiredate;private Double sal;private Double comm;private Integer deptno;
}EmpDao:
package com.zi.dao;import com.zi.pojo.Emp;public interface EmpDao {int findEmpCount();Emp findEmpByEmpno(int empno);int addEmp(Emp emp);int updateEmp(Emp emp);int deleteEmpByEmpno(int empno);
}EmpDaoImpl:
package com.zi.dao.impl;import com.zi.dao.EmpDao;
import com.zi.pojo.Emp;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;@Repository
public class EmpDaoImpl implements EmpDao {@Autowiredprivate JdbcTemplate jdbcTemplate;/*** 查询员工个数* @return*/@Overridepublic int findEmpCount() {String sql = "select count(1) from emp";Integer count = jdbcTemplate.queryForObject(sql, Integer.class);return count;}/*** 根据员工编号查询员工* @param empno 编号* @return*/@Overridepublic Emp findEmpByEmpno(int empno) {String sql = "select * from emp where empno = ?";BeanPropertyRowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);Emp emp = jdbcTemplate.queryForObject(sql, rowMapper, empno);return emp;}/*** 增加员工* @param emp* @return*/@Overridepublic int addEmp(Emp emp) {String sql = "insert into emp values(DEFAULT,?,?,?,?,?,?,?)";Object[] args = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno()};return jdbcTemplate.update(sql, args);}/*** 更新员工信息* @param emp* @return*/@Overridepublic int updateEmp(Emp emp) {String sql = "update emp set ename = ?, job = ?, mgr = ?, hiredate = ?, sal = ?, comm = ?, deptno = ? where empno = ?";Object[] args = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno(), emp.getEmpno()};int row = jdbcTemplate.update(sql, args);return row;}@Overridepublic int deleteEmpByEmpno(int empno) {String sql = "delete from emp where empno = ?";int i = jdbcTemplate.update(sql, empno);return i;}
}EmpService:
package com.zi.service;import com.zi.pojo.Emp;public interface EmpService {int findEmpCount();Emp findEmpByEmpno(int empno);int addEmp(Emp emp);int updateEmp(Emp emp);int deleteEmpByEmpno(int empno);
}EmpServiceImpl:
package com.zi.service.impl;import com.zi.dao.EmpDao;
import com.zi.pojo.Emp;
import com.zi.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;@Service
public class EmpServiceImpl implements EmpService {@Autowiredprivate EmpDao empDao;@Overridepublic int findEmpCount() {return empDao.findEmpCount();}@Overridepublic Emp findEmpByEmpno(int empno) {return empDao.findEmpByEmpno(empno);}@Overridepublic int addEmp(Emp emp) {return empDao.addEmp(emp);}@Overridepublic int updateEmp(Emp emp) {return empDao.updateEmp(emp);}@Overridepublic int deleteEmpByEmpno(int empno) {return empDao.deleteEmpByEmpno(empno);}
}3 测试
package com.zi.test;import com.zi.pojo.Emp;
import com.zi.service.EmpService;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;import java.util.Date;public class Test01 {private ApplicationContext applicationContext = null;private EmpService empService = null;@Beforepublic void init(){applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");empService = applicationContext.getBean(EmpService.class);}@Testpublic void testFindEmpCount(){System.out.println(empService.findEmpCount());}@Testpublic void testFindEmpByEmpno(){Emp emp = empService.findEmpByEmpno(7936);System.out.println(emp);//Emp(empno=7936, ename=curry, job=SG, mgr=7839, hiredate=2022-07-07 00:00:00.0, sal=6666.0, comm=300.0, deptno=20)}@Testpublic void testAddEmp(){Emp emp = new Emp(null, "Rose", "SmallBoss", 7902, new Date(), 4000.0, 500.0, 20);int row = empService.addEmp(emp);System.out.println(row);}@Testpublic void updateEmp(){Emp emp = new Emp(7938, "大漂亮郭艾伦", "PG", 7902, new Date(), 6000.0, 700.0, 10);int i = empService.updateEmp(emp);System.out.println(i);}@Testpublic void deleteEmpByEmpno(){int i = empService.deleteEmpByEmpno(7938);System.out.println(i);}
}4 拓展【JdbcTemplate的批处理操作】
EmpServiceImpl:
/*** 批量添加员工* @param emps* @return*/
@Override
public int[] empBathAdd(List<Emp> emps) {String sql = "insert into emp values(DEFAULT,?,?,?,?,?,?,?)";List<Object[]> args = new ArrayList<>();for (Emp emp : emps) {Object[] arg = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno()};args.add(arg);}return jdbcTemplate.batchUpdate(sql, args);
}
测试类：
@Test
public void empBatchAdd(){List<Emp> emps = new ArrayList<>();for (int i = 0; i < 15; i++) {Emp emp = new Emp(null, "" + i, "" + i, 7902, new Date(), 6000.0, 700.0, 20);emps.add(emp);}int[] row = empService.empBathAdd(emps);System.out.println(Arrays.toString(row));//[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
}