在项目工程中,我们一定会使用程序去连接数据库,Spring 中给我们提供了两个特别方便连接数据库的操作: JdbcTemplate 与 NamedParameterJdbcTemplate,如果不用Spring 提供的 JdbcTemplate,必须创建大量的冗余代码(创建连接,关闭连接,处理异常)中的所有DAO数据库的操作方法 - 插入,更新和删除。下面就介绍如果使用。
工程项目结构
数据库操作
CREATE TABLE `customer` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(100) NOT NULL,
`AGE` int(10) unsigned NOT NULL,
PRIMARY KEY (`CUST_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
程序代码
Customer.java 创建一个 model,用于与数据库中的表对应上
package com.wq.spring.customer.model;
/**
* 创建一个 Customer的model
* 用来对应上数据库中的表
* @author wuq
*
*/
public class Customer {
private int id;
private String name;
private int age;
public Customer(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
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 getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Customer [age=" + age + ", id=" + id + ", name=" + name+ "]";
}
}
CustomerDao.java 数据的访问层 dao
package com.wq.spring.customer.dao;
import com.wq.spring.customer.model.Customer;
// 数据的访问层 dao
public interface CustomerDao {
public void insert(Customer customer);
public Customer findByCustomerById(int id);
}
JdbcCustomerDAO.java 通过 JdbcTemplate 的方式连接数据库。
使用 JdbcCustomerDAO 操作数据库,需要继承与 JdbcDaoSupport,通过 JdbcDaoSupport 来获取数据源操作类。使用JdbcTemplate 可节省大量的冗余代码,因为 JdbcTemplate类 会自动处理它。
package com.wq.spring.customer.dao.impl;
import java.util.Map;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.wq.spring.customer.dao.CustomerDao;
import com.wq.spring.customer.model.Customer;
public class JdbcCustomerDAO extends JdbcDaoSupport implements CustomerDao {
@Override
public void insert(Customer customer) {
String sql = "INSERT INTO CUSTOMER (ID, NAME, AGE) VALUES (?, ?, ?)";
getJdbcTemplate().update( sql, new Object[] { customer.getId(), customer.getName(),
customer.getAge() });
}
@Override
public Customer findByCustomerById(int id) {
String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";
Map<String, Object> customerMap = getJdbcTemplate().queryForMap(sql, id);
Customer customer = new Customer(
Integer.parseInt(customerMap.get("ID").toString()),
customerMap.get("NAME").toString(),
Integer.valueOf(customerMap.get("AGE").toString()));
return customer;
}
}
SimpleJdbcCustomerDAO.java 使用 NamedParameterJdbcTemplate 操作数据库
package com.wq.spring.customer.dao.impl;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import com.wq.spring.customer.dao.CustomerDao;
import com.wq.spring.customer.model.Customer;
public class SimpleJdbcCustomerDAO extends NamedParameterJdbcDaoSupport implements CustomerDao{
@Override
public void insert(Customer customer) {
// :name 需要为小写的,大写的会报错
String sql = "UPDATE CUSTOMER SET NAME = :name WHERE ID = :id";
SqlParameterSource ps=new BeanPropertySqlParameterSource(customer);
int i = getNamedParameterJdbcTemplate().update(sql, ps); // 传入的参数也可以使用 map
System.out.println("更新影响行数:" + i);
}
@Override
public Customer findByCustomerById(int id) {
// :ID 需要和 map 中的 key 大小写保持一致
String sql = "SELECT * FROM CUSTOMER WHERE ID = :ID";
// 拼接条件
Map<String, Object> map = new HashMap();
map.put("ID", id);
// 查询返回一个 list
List<Map<String, Object>> list = getNamedParameterJdbcTemplate().queryForList(sql, map);
System.out.println( list.toString() );
// 查询返回 map
Map<String, Object> customerMap = getNamedParameterJdbcTemplate().queryForMap(sql, map);
Customer customer = new Customer(
Integer.parseInt(customerMap.get("ID").toString()),
customerMap.get("NAME").toString(),
Integer.valueOf(customerMap.get("AGE").toString()));
return customer;
}
}
Test.java 测试类
package com.wq.spring.customer.app;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.wq.spring.customer.dao.CustomerDao;
import com.wq.spring.customer.model.Customer;
public class Test {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbc-connect.xml");
CustomerDao customerDAO = (CustomerDao) context.getBean("customerSimpleDAO");
/* Customer customer = new Customer(1, "Wayfreem", 29);
customerDAO.insert(customer);*/
Customer customer1 = customerDAO.findByCustomerById(1);
System.out.println(customer1);
}
}
jdbc.properties 数据源相关配置
jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc\:mysql\://localhost\:3306/study_db?serverTimezone\=GMT%2B8
jdbc.username=root
jdbc.password=admin
spring-jdbc-connect.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:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<!-- 引入外部的属性文件 -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!-- 配置数据源 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClass}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<!-- 通过 spring ioc 容器自动注入 JdbcCustomerDAO -->
<bean id="customerDAO" class="com.wq.spring.customer.dao.impl.JdbcCustomerDAO">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="customerSimpleDAO" class="com.wq.spring.customer.dao.impl.SimpleJdbcCustomerDAO">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
启动测试类,查询结果如下
附
如果不继承于 JdbcDaoSupport 来获取数据源,可以使用下面的操作代码
package com.wq.spring.customer.dao.impl;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import com.wq.spring.customer.dao.CustomerDao;
import com.wq.spring.customer.model.Customer;
public class JdbcCustomerDAO implements CustomerDao {
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
@Override
public void insert(Customer customer) {
String sql = "INSERT INTO CUSTOMER (ID, NAME, AGE) VALUES (?, ?, ?)";
jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.update( sql, new Object[] { customer.getId(), customer.getName(),
customer.getAge() });
}
@Override
public Customer findByCustomerById(int id) {
String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";
jdbcTemplate = new JdbcTemplate(dataSource);
Map<String, Object> customerMap = jdbcTemplate.queryForMap(sql, id);
Customer customer = new Customer(
Integer.parseInt(customerMap.get("ID").toString()),
customerMap.get("NAME").toString(),
Integer.valueOf(customerMap.get("AGE").toString()));
return customer;
}
}
源码地址:
链接:https://pan.baidu.com/s/1y8RHwHw2rPc8NUIysC2m-A
提取码:e7fr