JDBC + Spring 整合
上一篇 我用到的Spring Jdbc Template 来优化sql
这次 使用 Spring 容器来整合 Jdbc
bean.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:mvc="http://www.springframework.org/schema/mvc"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
可以使用 可以不用
<context:property-placeholder location="db.properties"/>
因为我写了DataSourceUtil 所以这里使用了实例工厂来创建DataSource
采用了set方法注入
<bean id="dataSourceUtil" class="com.chen.jdbc.DataSourceUtil">
<property name="driver" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/boot_crm?serverTimezone=GMT"></property>
<property name="user" value="root"></property>
<property name="pwd" value="root"></property>
</bean>
设置实例工厂,以及实例工厂的方法
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
factory-bean="dataSourceUtil" factory-method="getDataSource"/>
创建 JdbcTemplate 类,用途简化执行sql
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
创建 数据库操作类
<bean class="com.chen.jdbc.dal.UserDal">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
</beans>
DataSourceUtil 类解析
实例工厂,
用途:创建DataSource
这个类有些多此一举,本着不浪费的原则和复习spring实例工厂的方法来画蛇添足
package com.chen.jdbc;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import javax.sql.DataSource;
/**
* @ClassName DataSourceUtil
* @Description TODO
* @Author newChenyingtao
* @Date 2020/6/20 10:51
* @Version 1.0
*/
public class DataSourceUtil {
// DataSourceUtil 类属性,我采用了set方法注入,所以将注解注入注释
// @Value("jdbc:mysql://localhost:3306/boot_crm?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC")
private String url;
// @Value("com.mysql.jdbc.Driver")
private String driver;
private String user;
private String pwd;
public void setUrl(String url) {
this.url = url;
}
public void setDriver(String driver) {
this.driver = driver;
}
public void setUser(String user) {
this.user = user;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
//实体工厂方法
public DataSource getDataSource(){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setPassword(pwd);
dataSource.setUsername(user);
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
return dataSource;
}
}
数据库操作层
一个依赖:private JdbcTemplate jdbcTemplate;
使用set方法注入
package com.chen.jdbc.dal;
import com.chen.jdbc.CloseHelper;
import com.chen.jdbc.JdbcHelper;
import com.chen.pojo.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @ClassName UserDal
* @Description 使用jdbc 实现的数据库操作层
* @Author newChenyingtao
* @Date 2020/6/19 16:08
* @Version 1.0
*/
public class UserDal {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public int count(){
String sql = "select count(sys_user.user_id) from sys_user";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
public Map selectOne(){
String sql = "select * from sys_user where user_id = ?";
Map map = jdbcTemplate.queryForMap(sql, 12);
System.out.println(map);
return map;
}
public List<User> selectAll3(){
String sql = "select * from sys_user";
List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
return list;
}
public int updateUser(){
String sql = "";
int x = jdbcTemplate.update(sql);
return x;
}
public int insertUser(){
String sql = "insert into sys_user values (?,?,?,?,?)";
int x = jdbcTemplate.update(sql, "12","2015","Jackset","1234","1");
return x;
}
}
测试用例 加载 bean.xml文件
从spring 容器中获取到 UserDal 进行操作
package test;
import com.chen.jdbc.dal.BaseDictDal;
import com.chen.jdbc.dal.UserDal;
import com.chen.pojo.User;
import org.junit.Before;
import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* @ClassName Test
* @Description TODO
* @Author newChenyingtao
* @Date 2020/6/19 16:20
* @Version 1.0
*/
public class JdbcTest {
ApplicationContext context;
/**
* 原生的JDBC + Spring 管理 Bean
*
*/
@Test
public void springTest(){
context = new ClassPathXmlApplicationContext("bean.xml");
UserDal userDal = context.getBean(UserDal.class);
List<User> list = userDal.selectAll3();
for (int i = 0; i < list.size(); i ++){
System.out.println(list.get(i).toString());
}
}
}
知识点整理:
JDBC:原生JDBC、DataSource 优化,
Spring: Spring Jdbc Template 、Spring 容器配置
借此机会,整理下自己的知识,代码有些不够完善,仅给大家提供思路
希望能够互相交流,
下次可以来整合 Mybatis