一,Spring JDBC 中配置jdbc Template对象实现增删改查操作:
一,数据准备:
在 Navicat 新建数据库,注意:字符集–>utf8mb4(4字节utf-8编码)
二,创建Maven工程,引入Spring 和 SpringJDBC、MySQL JDBC驱动的相关依赖:
-
Spring 框架的依赖
-
Spring-JDBC–>对数据库进行 增删改查操作(版本号和Spring框架依赖保持一致)
-
引入MySQL-JDBC驱动-版本号与数据库版本号保持一致(因为底层是采用JDBC 操作数据库)
<repositories>
<repository>
<id>aliyun</id>
<name>aliyun</name>
<url>https://maven.aliyun.com/repository/public</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
</dependencies>
三,创建applicationContext.xml配置文件,引入xml文件标志和Schema,配置Spring-jdbc
<?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:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://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
https://www.springframework.org/schema/aop/spring-aop.xsd">
<!--数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/imooc?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
<!--jdbcTemplete提供数据crud的api-->
<bean id="jdbcTemplete" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
四,创建实体类和Dao类:将 java实体bean 与数据库中的的列,按照排列的顺序一一对应
public class Employee {
private int eno;
private String ename;
private int salary;
private String dname;
private Date hiredate;
public int getEno() {
return eno;
}
public void setEno(int eno) {
this.eno = eno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
@Override
public String toString() {
return "Employee{" +
"eno=" + eno +
", ename='" + ename + '\'' +
", salary=" + salary +
", dname='" + dname + '\'' +
", hiredate=" + hiredate +
'}';
}
}
public class EmployeeDao {
private JdbcTemplate jdbcTemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public Employee findByEno(int eno) {
String sql = "select * from employee where eno=?";
// jdbcTemplate.queryForObject:查询唯一的返回数据并封装成实体对象
// new Object[]{eno}:查询的依据或给定的条件
// new BeanPropertyRowMapper:将java实体bean与数据库中的列,按照排序的顺序一一对应,返回具体的对象
Employee employee = jdbcTemplate.queryForObject(sql, new Object[]{eno}, new BeanPropertyRowMapper<Employee>(Employee.class));
return employee;
}
}
第五:在xml配置文件中,配置Dao类
<?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:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://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
https://www.springframework.org/schema/aop/spring-aop.xsd">
<!--数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/imooc?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
<!--jdbcTemplete提供数据crud的api-->
<bean id="jdbcTemplete" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<!--为dao注入jdbcTemplete对象-->
<bean id="employeeDao" class="com.imooc.spring.jdbc.dao.EmployeeDao">
<property name="jdbcTemplate" ref="jdbcTemplete"/>
</bean>
</beans>
六,创建SpringApplication类,完成员工查询工作:
public class SpringApplication {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
EmployeeDao dao = context.getBean("employeeDao", EmployeeDao.class);
Employee employee = dao.findByEno(3308);
System.out.println(employee);
}
}
二,数据查询的方法
// 查询结果中单条记录转换为对应对象,我们可以使用queryForObjcet()进行查询,
public Employee findByEno(int eno) {
String sql = "select * from employee where eno=?";
// jdbcTemplate.queryForObject:查询唯一的返回数据并封装成实体对象
// new Object[]{eno}:查询的依据或给定的条件
// new BeanPropertyRowMapper:将java实体bean与数据库中的列,按照排序的顺序一一对应,返回具体的对象
// 查询单条数据
Employee employee = jdbcTemplate.queryForObject(sql, new Object[]{eno}, new BeanPropertyRowMapper<Employee>(Employee.class));
return employee;
}
// 查询结果中多条记录转换为对应对象,我们可以使用query()进行查询,
public List<Employee> findByDname(String dname) {
String sql = "select * from employee where dname=?";
// 查询复合数据
List<Employee> employees = jdbcTemplate.query(sql, new Object[]{dname}, new BeanPropertyRowMapper<Employee>(Employee.class));
return employees;
}
// 很多字段名是没有实体属性的,无法进行实体类映射,我们可以使用queryForList()进行查询,这个结果会被封装成map对象
public List<Map<String, Object>> findMapByDname(String dname) {
String sql = "select dname as dn from employee where dname=?";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, new Object[]{dname});
return maps;
}
三,增删改操作
// 新增
public int insert(Employee employee) {
String sql = "insert into employee(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
int update = jdbcTemplate.update(sql, new Object[]{
employee.getEno(), employee.getEname(), employee.getSalary(), employee.getDname(), employee.getHiredate()
});
return update;
}
// 修改
public int update(int salary) {
String sql = "update employee set salary=? where eno=3308";
int update = jdbcTemplate.update(sql, new Object[]{salary});
return update;
}
//删除
public int delete(int eno) {
String sql = "delete from employee where eno=?";
int update = jdbcTemplate.update(sql, new Object[]{eno});
return update;
}