一、jdbcTemplate的入门程序
spring框架也集成了对JDBC数据库的操作,但是要明白的是spring对JDBC的支持只是一个简单的封装(JdbcTemplate来操作数据库),而不是真正意义上的ORM框架,所以一般情况下我们都会在spring容器管理的基础上集成Hibernate框架或者Mybatis框架.
1.创建maven工程(修改jdk版本和配置web.xml文件)
2.导入相应的Jar包
<!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-expression -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/aopalliance/aopalliance -->
<dependency>
<groupId>aopalliance</groupId>
<artifactId>aopalliance</artifactId>
<version>1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.6.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>4.2.4.RELEASE</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.9</version>
<scope>test</scope>
</dependency>
3.在spring核心配置文件中将jdbcTemplate添加到spring容器中
<?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-4.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd">
<!-- 配置数据库连接池 -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/spring_day03?characterEncoding=utf-8" />
<property name="username" value="root" />
<property name="password" value="123" />
</bean>
<!-- 配置JdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" ></property>
</bean>
</beans>
4.进行测试
package cn.itcast.spring03;
import static org.junit.Assert.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import spring03.Account;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:springshujulianjie.xml")
public class Springtest {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 增加用户
*/
@Test
public void testName() throws Exception {
String sql = "insert into account values(?,?,?)";
jdbcTemplate.update(sql, null, "李四", 500000);
}
/**
* 删除用户
*/
@Test
public void testName2() throws Exception {
String sql = "delete from account where id =?";
jdbcTemplate.update(sql, 1);
}
/**
* 查询
*/
@Test
public void find() throws Exception {
String sql = "select name from account where id =?";
String queryForObject = jdbcTemplate.queryForObject(sql, String.class, 2);
System.out.println(queryForObject);
}
/**
* 查询一行记录,返回javabean
*/
@Test
public void find1() throws Exception {
String sql = "select * from account where id =?";
Account queryForObject = jdbcTemplate.queryForObject(sql, new myromappers(), 2);
System.out.println(queryForObject);
}
/**
* 查询多行记录,返回javabean
*/
@Test
public void find2() throws Exception {
String sql = "select * from account";
List<Account> query = jdbcTemplate.query(sql, new myromappers());
for (Account account1 : query) {
System.out.println(account1);
}
}
// roemapper实现类
class myromappers implements RowMapper<Account> {
@Override
public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
Account account = new Account();
account.setId(rs.getInt("id"));
return account;
}
}
}
二、spring配置文件中数据库连接池的配置
1.C3P0数据库连接池的配置
1.1导入依赖的jar包
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
1.2在spring配置文件中配置C3P0数据库连接池
<bean id="dateSourse" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/spring_day03"></property>
<property name="user" value="root"></property>
<property name="password" value="123"></property>
</bean>
2.DBCP数据库连接池的配置
2.1导入DBCP数据库连接池依赖的jar包
<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>
2.2 配置DBCP数据库连接池
<bean id="dateSourse" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/spring_day03"></property>
<property name="username" value="root"></property>
<property name="password" value="123"></property>
</bean>
3.Druid数据库连接池的配置
3.1导入Druid数据库连接池依赖的jar包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.18</version>
</dependency>
3.2配置Druid数据库连接池
<bean id="dateSourse" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
三、jdbc中事务的回顾及spring中事务的管理
3.2没有spring之前,进行事务管理需要,conn
3.3spring提供了一个类:平台事务管理器,来管理实务
这个类是一个接口,有不同的实现类
四、Spring中的事务管理
1 没有添加事物的数据库修改
1.1创建service接口及其实现类
public interface AccountService {
//转账
public void transferMoney(int from,int to,double money);
}
@Service
public class AccountServiceImpl implements AccountService {
@Autowired
private AccountDao accountDao;
@Override
public void transferMoney(final int from, final int to, final double money) {
accountDao.moneyOut(from,money);
//准备抛异常
// System.out.println(1/0);
accountDao.moneyIn(to,money);
}
1.2.创建dao接口及其实现类
public interface AccountDao {
void moneyOut(int from, double money);
void moneyIn(int to, double money);
}
@Repository
public class AccountDaoImpl implements AccountDao {
//jdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void moneyOut(int from, double money) {
String sql = "update account set money = money - ? where id = ?";
jdbcTemplate.update(sql, money,from);
}
@Override
public void moneyIn(int to, double money) {
String sql = "update account set money = money + ? where id = ?";
jdbcTemplate.update(sql, money,to);
}
}
1.3在spring配置文件中配置
<!-- 开启注解扫描 -->
<context:component-scan base-package="cn.itcast.spring"></context:component-scan>
<!-- 加载外部属性 -->
<context:property-placeholder location="db.properties" />
<!-- druid数据库连接池 -->
<bean id="dateSourse" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<!-- 配置jdbcTemplate -->
<bean id="jdbctemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dateSourse"></property>
</bean>
1.4进行测试
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext2.xml")
public class shiwu {
@Autowired
private AccountService accountService;
@Test
public void testName() throws Exception {
accountService.transferMoney(2, 4, 1000);
}
}
2.使用编程的方式进行事务管理
2.1修改service实现类的接口,在其中添加事物
@Override
public void transferMoney(final int from, final int to, final double money) {
transactionTemplate.execute(new TransactionCallbackWithoutResult() {
@Override
protected void doInTransactionWithoutResult(TransactionStatus arg0) {
accountDao.moneyOut(from,money);
//准备抛异常
System.out.println(1/0);
accountDao.moneyIn(to,money);
}
}) ;
}
2.2spring配置文件中创建事物管理,并在切面中加入此方法
<!-- 配置事物管理器 管理实务的工具 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!-- 在连接池中获取事物管理器 -->
<property name="dataSource" ref="dateSourse"></property>
</bean>
<!-- 配置模板 -->
<bean id="transactionTemplate"
class="org.springframework.transaction.support.TransactionTemplate">
<!-- 把事物管理器给模板,创建出事务管理 -->
<property name="transactionManager" ref="transactionManager"></property>
</bean>
2.3进行测试
@Test
public void testName() throws Exception {
accountService.transferMoney(2, 4, 1000);
}
五、Spring与mybatis的整合
1.传统Dao开发方式
1.1创建maven工程
解决jdk版本问题和缺少web.xml的问题
1.2添加依赖的java包
spring包,mybatis包,spring整合mybatis的包,mysql的驱动包,druid数据库连接池的包,日志包,junit测试包
1.3配置文件
1.3.1 sqlMapConfig.xml
起别名.加载mapper.xml映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置别名 -->
<typeAliases>
<package name="cn.itcast.spring.domain"/>
</typeAliases>
<!-- 加载mapper映射文件 -->
<mappers>
<mapper resource="mapper.xml" />
</mappers>
</configuration>
1.3.2mapper.xml
书写sql语句
<mapper namespace="test">
<select id="abc" resultType="account">
select * from account
</select>
1.3.3applicationContext.xml
包扫描;加载配置文件配置数据库连接池;配置事物管理器,开启事务注解驱动;配置sqlsessionFactory;加载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:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd">
<!-- 包扫描 -->
<!-- <context:component-scan base-package="cn.itcast.dao"></context:component-scan> -->
<!-- 加载属性文件 -->
<context:property-placeholder location="classpath:db.properties" />
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 开启事务注解驱动 -->
<tx:annotation-driven transaction-manager="transactionManager" />
<!-- sqlSessionFactory的设置 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<!-- mybatis的核心配置文件的路径 -->
<property name="configLocation" value="classpath:sqlMapConfig.xml"></property>
</bean>
<!-- dao实现类放到容器中 -->
<bean id="accountDao" class="cn.itcast.dao.AccountDaoImpl">
<property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
</bean>
</beans>
1.4创建接口及其实现类
public interface AccountDao {
//查询所有账号信息
public List<Account> findAll();
}
public class AccountDaoImpl extends SqlSessionDaoSupport implements AccountDao {
@Override
public List<Account> findAll() {
SqlSession session = getSqlSession();
List<Account> list = session.selectList("test.abc");
return list;
}
}
1.5进行测试
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Ceshi {
@Autowired
private AccountDao accountDao;
@Test
public void testName() throws Exception {
List<Account> findAll = accountDao.findAll();
for (Account account : findAll) {
System.out.println(account);
}
}
}
2.1创建maven工程
解决jdk版本问题和缺少web.xml的问题
2.2添加依赖的java包
spring包,mybatis包,spring整合mybatis的包,mysql的驱动包,druid数据库连接池的包,日志包,junit测试包
2.3配置文件
2.3.1 sqlMapConfig.xml
起别名.加载mapper.xml映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置别名 -->
<typeAliases>
<package name="cn.itcast.spring.domain"/>
</typeAliases>
<!-- 加载mapper映射文件 -->
<mappers>
<mapper resource="mapper.xml" />
</mappers>
</configuration>
2.3.2mapper.xml要和接口的名字一样
书写sql语句
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.dao.AccountDao">
<select id="findAll" resultType="account">
select * from account
</select>
</mapper>
2.3.3applicationContext.xml
包扫描;加载配置文件配置数据库连接池;配置事物管理器,开启事务注解驱动;配置sqlsessionFactory;加载接口实现类到spring容器中(两种方式)。
<?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:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd">
<!-- 包扫描 -->
<!-- <context:component-scan base-package="cn.itcast.dao"></context:component-scan> -->
<!-- 加载属性文件 -->
<context:property-placeholder location="classpath:db.properties" />
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 开启事务注解驱动 -->
<tx:annotation-driven transaction-manager="transactionManager" />
<!-- sqlSessionFactory的设置 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<!-- mybatis的核心配置文件的路径 -->
<property name="configLocation" value="classpath:sqlMapConfig.xml"></property>
</bean>
<!-- 把接口的代理对象放在spring容器中 -->
<!-- <bean id="accountMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> -->
<!-- <property name="sqlSessionFactory" ref="sqlSessionFactory"></property> -->
<!-- <property name="mapperInterface" value="cn.itcast.dao.AccountDao"></property> -->
<!-- </bean> -->
<!-- 通过包扫描将接口的实现类自动放入容器中 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="cn.itcast.dao" ></property>
</bean>
</beans>
2.4创建接口
public interface AccountDao {
//查询所有账号信息
public List<Account> findAll();
}
2.4进行测试
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Ceshi2 {
@Autowired
private AccountDao accountDao;
@Test
public void testName() throws Exception {
List<Account> findAll = accountDao.findAll();
for (Account account : findAll) {
System.out.println(account);
}
}
}