Spring JDBC
Spring 是个一站式框架:Spring 自身提供了控制层的 SpringMVC 和 持久层的 Spring JdbcTemplate。
开发步骤
1. 下载 Spring JdbcTemplate 的 jar 包
<!-- spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<!-- 阿里数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
2. 导入属性文件
<context:property-placeholder location="config.properties"/>
3. 管理数据源对象
- spring 管理与数据库链接 (数据源)
<bean id="dataSource"class="com.alibaba.druid.pool.DruidDataSource">
<propertyname="driverClassName" value="${driverClassName}">
</property> <property name="url" value="${url}"></property>
<property name="username" value="${user}"></property>
<property name="password" value="${password}"></property>
<property name="initialSize" value="10"></property>
<property name="minIdle" value="5"></property>
<property name="maxActive" value="20"></property>
</bean>
- 在配置文件中创建 JdbcTemplate
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
- 在类中获得 JdbcTemplate 对象,就可以直接使用(此处以使用注解的方式为例)。
@Autowired
JdbcTemplate jdbcTemplate;
JdbcTemplate 中常用的方法
execute:无返回值,可执行 ddl,增删改语句
update:执行新增、修改、删除语句;
queryForXXX:执行查询相关语句;
案例:JdbcTemplate 中常用的方法
新建Dept类
package com.ff.spring.bean;
public class Dept {
private int id;
private String name;
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;
}
@Override
public String toString() {
return "Dept{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
新建UserDao
package com.ff.spring.Dao;
import com.ff.spring.bean.Dept;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLOutput;
import java.util.List;
import java.util.Map;
/*
连接点:类中可以被增强的方法(可以额外添加功能)
切入点:实际被增强的方法
通知:在连接点上要做的事情
目标类:实际执行者
*/
@Repository
public class UserDao {
@Autowired
JdbcTemplate jdbcTemplate;
public void save() {
// jdbcTemplate.update("insert into dept(name) values ('后勤部')");
// jdbcTemplate.update("insert into dept (name) values(?)","教育部");
// jdbcTemplate.execute("create table test(id int ,name varchar (15))");
//返回的是修改数据的条数
/*int count=jdbcTemplate.update("delete from dept where id>?",2);
System.out.println(count);*/
/* Dept dept = jdbcTemplate.queryForObject("select id,name from dept where id=?", new RowMapper<Dept>() {
@Override
public Dept mapRow(ResultSet resultSet, int i) throws SQLException {
Dept dept1 = new Dept();
dept1.setId(resultSet.getInt("id"));
dept1.setName(resultSet.getString("name"));
return dept1;
}
}, 1);
System.out.println(dept);*/
/* Map dept=jdbcTemplate.queryForMap("select id,name from dept where id=?",1);
System.out.println(dept);
}
}*/
List<Dept> list = jdbcTemplate.query("select * from dept", new RowMapper<Dept>() {
@Override
public Dept mapRow(ResultSet resultSet, int i) throws SQLException {
System.out.println("执行了一次");
Dept dept = new Dept();
dept.setId(resultSet.getInt("id"));
dept.setName(resultSet.getString("name"));
return dept;
}
});
System.out.println(list);
}
}
新建service
package com.ff.spring.service;
import com.ff.spring.Dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service(value="userService")
public class UserService {
@Autowired
UserDao userDao;
public void save(){
userDao.save();
}
}
新建JdbcUtil
package com.ff.spring.test;
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class JdbcUtil {
public Connection getConnection() throws SQLException {
DruidDataSource druidDataSource= new DruidDataSource();
druidDataSource.setUrl("");
druidDataSource.setUsername("");
druidDataSource.setPassword("");
druidDataSource.setDriverClassName("");
return druidDataSource.getConnection();//从连接池中获取的
}
}
config.properties
drivername=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybaits?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
uname=root
password=13579
db.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/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
https://www.springframework.org/schema/context/spring-context.xsd">
<!--读取属性文件-->
<context:property-placeholder location="config.properties"></context:property-placeholder>
<!--spring生产数据库连接管理对象-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${drivername}"></property>
<property name="url" value="${url}"></property>
<property name="username" value="${uname}"></property>
<property name="password" value="${password}"></property>
<property name="initialSize" value="5"></property>
<property name="minIdle" value="5"></property>
<property name="maxActive" value="20"></property>
</bean>
<!--spring提供的jdbc支持-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
spring.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/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
https://www.springframework.org/schema/context/spring-context.xsd">
<!--spring开启注解扫描-->
<context:component-scan base-package="com.ff.spring"></context:component-scan>
<import resource="db.xml"></import>
</beans>
pom.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>spring2</artifactId>
<version>1.0-SNAPSHOT</version>
<name>spring3</name>
<properties>
<maven.compiler.target>1.8</maven.compiler.target>
<maven.compiler.source>1.8</maven.compiler.source>
<junit.version>5.6.2</junit.version>
</properties>
<dependencies>
<!--junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>provided</scope>
</dependency>
<!-- mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!-- spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<!-- 阿里数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
<!--spring-mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.1</version>
</dependency>
<!-- spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
</plugins>
</build>
</project>
新建测试类
package com.ff.spring.test;
import com.ff.spring.bean.User;
import com.ff.spring.service.UserService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Test1 {
public static void main(String[] args) {
ApplicationContext context= new ClassPathXmlApplicationContext("spring.xml");
UserService userService = (UserService) context.getBean("userService");
userService.save();
}
}