JdbcTemplate与Hibernate类似同样也可以成为数据库与界面之间的桥梁,通过数据库连接池dataSource创建JdbcTemplate模板可以将使业务层与具体数据库分开。调用JdbcTemplate中的update方法对数据库进行相应的增,删,改,更新等操作。
搭建环境
1.创建表
create database ee19_spring_day02;
use ee19_spring_day02;
create table t_user(
id int primary key auto_increment,
username varchar(50),
password varchar(32) );
insert into t_user(username,password) values('jack','1234');
insert into t_user(username,password) values('rose','5678');
2.导入jar包
创建maven项目导入依赖包代码:
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-jdbc</artifactId>
<version>1.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.5.3</version>
</dependency>
</dependencies>
3.API测试
提供javaBean
测试代码(使用的是DBCP连接池)
public static void main(String[] args) {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/ee19_spring_day02");
dataSource.setUsername("root");
dataSource.setPassword("admin");
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.update("insert into t_user(username,password) value(?,?);","zhangsan","1564");
}
数据库成功添加数据:
4.Spring配置DBCP
spring配置:
<!--创建连接池,为模板jdbcTemplate注入作准备-->
<bean id="dateSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ee19_spring_day02"/>
<property name="username" value="root"/>
<property name="password" value="admin"/>
</bean>
<!--创建jdbcTemplate,为注入service作准备-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dateSource"></property>
</bean>
<!--创建bean(service)然后注入jdbcTemplate,
这里的jdbcTemplate相当于dao层。
-->
<bean id="userservice" class="com.jack.DBCP.UserService">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
开始测试
public static void main(String[] args) {
User user = new User();
user.setUsername("tom");
user.setPassword("6789");
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(new String[]{"Spring_Jdbc.xml"});
UserService userService = (UserService) applicationContext.getBean("userservice");
userService.update(user);
}
成功向数据库添加数据
5.Spring配置c3p0
- 与DBCP唯一不同的是,c3p0数据库连接池是ComboPooledDataSource类型。其他配置都是一样。
<bean id="dateSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/ee19_spring_day02"/>
<property name="user" value="root"/>
<property name="password" value="admin"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dateSource"/>
</bean>
<bean id="userservice" class="com.jack.DBCP.UserService">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
开始测试
public static void main(String[] args) {
User user = new User();
user.setUsername("hei");
user.setPassword("1597");
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(new String[]{"Spring_c3p0.xml"});
UserService userService = (UserService) applicationContext.getBean("userservice");
userService.update(user);
}
成功向数据库添加数据
利用JdbcDaoSupport,让底层自动创建JdbcTemplate模板。
将dao层继承JdbcDaoSupport类,可以不用在创建模板JdbcTemplate,因为JdbcDaoSupport已经通过createJdbcTemplate()方法创建好一个模板,所以继承了JdbcDaoSupport的dao层,只需要注入数据源就能对数据库进行相应的操作。
UserDao在spring配置中注入一个数据源即可。