Spring整合JDBC
1.编码前准备
-
建表s_user
create table s_user( id number primary key, name varchar2(20), age number(2), birth date );
-
创建序列,用来生成主键
create sequence user_id_seq increment by 1 start with 1;
-
创建对象类
package db.bean; import java.util.Date; public class SUser { private Integer id; private String name; private int age; private Date birth; public Integer getId() { return id; } public void setId(Integer 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; } public Date getBirth() { return birth; } public void setBirth(Date birth) { this.birth = birth; } }
-
持久层接口
package db.dao; import java.util.List; import db.bean.SUser; public interface ISUserDao { int saveUser(SUser user); int updateUser(SUser user); int deleteUserById(int id); SUser selectById(int id); List<SUser> selectByCondition(String name, int minAge, int maxAge); }
-
引入jar包
spring-jdbc-5.1.9.RELEASE.jar
和spring-tx-5.1.9.RELEASE.jar
是Spring jdbc编程的依赖包ojdbc6.jar
是jdbc编程的依赖包druid-1.1.23.jar
是阿里提供的第三方数据源实现的依赖jar包。 -
配置db.properties
url=jdbc:oracle:thin:@localhost:1521:xe user=root password=root driver=oracle.jdbc.OracleDriver maxWait=2000 initSize=3
-
配置log4j.properties,查看日志信息
log4j.rootLogger=INFO, stdout log4j.logger.org.mybatis.example=TRACE log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%d [%-5p] %c - %m%n log4j.logger.org.springframework.jdbc.core.JdbcTemplate=DEBUG
2.引入数据源,数据源配置
获取Connection对象有两种方式:
DirverManager:每次打开到数据源的物理连接,资源消耗大,效率低不利于分层架构中的事务管理
javax.sql.DataSource:对象代表了一个提供获取到物理数据源连接的工厂。相比较DriverManager,DataSource是获取Connection的首选方式。DataSource可能来自数据库提供商提供的实现,也可能是其他第三方的实现。
一、基于xml配置:
-
Spring提供的实现
org.springframework.jdbc.datasource.DriverManagerDataSource
jdbc_dao.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 https://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <context:component-scan base-package="db.dao"></context:component-scan> <context:property-placeholder location="db.properties"/> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="url" value="${url}"></property> <property name="username" value="${user}"></property> <property name="password" value="${password}"></property> </bean> <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
-
阿里提供的实现:
DruidDataSource
二、基于java_config:
-
Spring提供的实现
org.springframework.jdbc.datasource.DriverManagerDataSource
-
阿里提供的实现:
DruidDataSource
package db.config; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.ComponentScan; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.PropertySource; import org.springframework.jdbc.core.JdbcTemplate; import com.alibaba.druid.pool.DruidDataSource; @Configuration//表明这是一个配置类 @ComponentScan(basePackages = "db.dao")//为了能搜索到@Component/@Service/@Repository/@Controller注解 @PropertySource(value = "classpath:db.properties") public class AppConfig { @Value("${url}") private String jdbcUrl; @Value("${driver}") private String driver; @Value("${user}") private String user; @Value("${password}") private String password; @Value("${initSize}") private int initSize; @Value("${maxWait}") private long maxWait; @Bean(name = "jdbcTemplate") public JdbcTemplate jdbcTemplate(DataSource dataSource) { return new JdbcTemplate(dataSource); } @Bean(name = "dataSource")//这里不写默认为方法名 public DataSource createDataSource() { DruidDataSource ds = new DruidDataSource(); ds.setUrl(jdbcUrl); ds.setDriverClassName(driver); ds.setUsername(user); ds.setPassword(password); ds.setInitialSize(initSize); ds.setMaxWait(maxWait); return ds; } }
3.代码实现
package db.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
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 db.bean.SUser;
import db.dao.ISUserDao;
@Repository
public class SUserDaoImpl implements ISUserDao {
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public int saveUser(SUser user) {
String sql = "insert into s_user(id,name,age,birth) values(user_id_seq.nextval,?,?,?)";
return jdbcTemplate.update(sql, user.getName(), user.getAge(), user.getBirth());
}
@Override
public int updateUser(SUser user) {
String sql = "update s_user set name=?,age=?,birth=? where id=?";
return jdbcTemplate.update(sql, user.getName(), user.getAge(), user.getBirth(), user.getId());
}
@Override
public int deleteUserById(int id) {
// TODO Auto-generated method stub
return 0;
}
@Override
public SUser selectById(int id) {
String sql="select id,name,age,birth from s_user where id=?";
return jdbcTemplate.queryForObject(sql, new RowMapper<SUser>() {
@Override
public SUser mapRow(ResultSet rs, int rowNum) throws SQLException {
SUser user = new SUser();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setAge(rs.getInt("age"));
user.setBirth(rs.getDate("birth"));
return user;
}
},id);
}
@Override
public List<SUser> selectByCondition(String name, int minAge, int maxAge) {
String sql="select id,name,age,birth from s_user where name like ? and age between ? and ?";
return jdbcTemplate.query(sql,(rs,rowNum)-> {
SUser user = new SUser();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setAge(rs.getInt("age"));
user.setBirth(rs.getDate("birth"));
return user;
},"%"+name+"%",maxAge,minAge);
/*return jdbcTemplate.query(sql, new RowMapper<SUser>() {
@Override
public SUser mapRow(ResultSet rs, int rowNum) throws SQLException {
SUser user = new SUser();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setAge(rs.getInt("age"));
user.setBirth(rs.getDate("birth"));
return user;
}
},"%"+name+"%",maxAge,minAge);*/
}
}
测试:
package db;
import java.util.Date;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import db.bean.SUser;
import db.dao.ISUserDao;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:db/jdbc_dao.xml")//基于xml配置
//@ContextConfiguration(classes = AppConfig.class)//基于java_config配置
public class DB_test {
@Autowired
private ISUserDao userDao;
@Test
public void test() {
SUser user = new SUser();
user.setName("zhangsan");
user.setAge(16);
user.setBirth(new Date());
userDao.saveUser(user);
}
}
Spring整合Mybatis
1.编码前准备
-
导入jar包
mymbatis-3.5.6.jar
mybatis-spring-2.0.2.jar
-
建表,插入数据
create table account( id number primary key, name varchar2(20), balance number ); insert into account values(1,'tom',1400.00); insert into account values(2,'jerry',1500.00); commit;
-
实体类
package db.bean; public class Account { private long id; private String name; private double balance; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public double getBalance() { return balance; } public void setBalance(double balance) { this.balance = balance; } @Override public String toString() { return "Account [id=" + id + ", name=" + name + ", balance=" + balance + "]"; } }
-
mybatis-config.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> <settings> <setting name="jdbcTypeForNull" value="VARCHAR"/> </settings> <mappers> <mapper resource="db/IAccountDao.xml"/> </mappers> </configuration>
-
持久层接口
package db.dao; import db.bean.Account; public interface IAccountDao { Account findById(long id); void updateBalanceId(double balance, long id); }
-
持久层接口映射文件
IAccountDao.xml
<?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="db.dao.IAccountDao"> <!-- public Account findById(long id) --> <select id="findById" resultType="db.bean.Account"> select id,name,balance from account where id = #{id} </select> <!-- void updateBalanceId(double balance, long id); --> <update id="updateBalanceId"> update account set balance = #{param1} where id = #{param2} </update> </mapper>
-
业务层接口
package db.service; public interface IAccountService { void transferTo(Long fromId,long toId,double money); }
-
业务层实现类
package db.service.impl; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; import db.bean.Account; import db.dao.IAccountDao; import db.service.IAccountService; @Service public class AccountServiceImpl implements IAccountService { @Autowired private IAccountDao dao; @Override @Transactional(propagation = Propagation.REQUIRED,rollbackFor = Exception.class) public void transferTo(Long fromId, long toId, double money) { Account from = dao.findById(fromId); if(from.getBalance()<money) { throw new RuntimeException("余额不足!"); } dao.updateBalanceId(from.getBalance()-money, fromId); Account to = dao.findById(toId); dao.updateBalanceId(to.getBalance()+money, toId); } }
2.配置
2.1 基于xml配置:
mybatis_dao.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"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<context:component-scan base-package="db.service"></context:component-scan>
<context:property-placeholder location="db.properties"/>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="url" value="${url}"></property>
<property name="username" value="${user}"></property>
<property name="password" value="${password}"></property>
</bean>
<!-- 配置mybatis SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<!-- 读配置文件 -->
<!-- <property name="configLocation" value="db/mybatis-config.xml"></property> -->
<!-- 不读配置文件 -->
<property name="mapperLocations">
<array>
<value>db/IAccountDao.xml</value>
</array>
</property>
</bean>
<!-- Mapper -->
<bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
<property name="basePackage" value="db.dao"></property>
</bean>
</beans>
测试:
package db;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import db.service.IAccountService;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:db/mybatis_dao.xml")
public class Mybatis_Spring_Test {
@Autowired
private IAccountService accountService;
@Test
public void test() {
accountService.transferTo(1L, 2L, 100);
}
}
2.2 基于注解配置
MybatisConfig
package db.config;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.alibaba.druid.pool.DruidDataSource;
@Configuration
@ComponentScan(basePackages = "db.service")
@PropertySource(value = "classpath:db.properties")
@MapperScan(basePackages = "db.dao") //MapperScannerConfigurer
public class MybatisConfig {
@Value("${url}")
private String jdbcUrl;
@Value("${driver}")
private String driver;
@Value("${user}")
private String user;
@Value("${password}")
private String password;
@Value("${initSize}")
private int initSize;
@Value("${maxWait}")
private long maxWait;
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean sfb = new SqlSessionFactoryBean();
sfb.setDataSource(dataSource);
sfb.setMapperLocations(new ClassPathResource("db/IAccountDao.xml"));
return sfb.getObject();
}
@Bean
public DataSourceTransactionManager transactionManager(DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "jdbcTemplate")
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "dataSource")//这里不写默认为方法名
public DataSource createDataSource() {
DruidDataSource ds = new DruidDataSource();
ds.setUrl(jdbcUrl);
ds.setDriverClassName(driver);
ds.setUsername(user);
ds.setPassword(password);
ds.setInitialSize(initSize);
ds.setMaxWait(maxWait);
return ds;
}
}
测试:
package db;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import db.config.MybatisConfig;
import db.service.IAccountService;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = MybatisConfig.class)
public class Mybatis_Spring_Test {
@Autowired
private IAccountService accountService;
@Test
public void test() {
accountService.transferTo(1l, 2l, 100);
}
}