Java学习小结——Spring整合JDBC、Spring整合mybatis

本文详细介绍了如何进行Spring与JDBC的整合,包括编码前的准备工作,如建表、创建对象类和配置数据源,以及两种不同的配置方式。接着,文章转向了Spring整合Mybatis的步骤,包括XML配置和注解配置,涵盖了从导入jar包、建立表到编写测试代码的全过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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.jarspring-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对象有两种方式:

  1. DirverManager:每次打开到数据源的物理连接,资源消耗大,效率低不利于分层架构中的事务管理

  2. javax.sql.DataSource:对象代表了一个提供获取到物理数据源连接的工厂。相比较DriverManager,DataSource是获取Connection的首选方式。DataSource可能来自数据库提供商提供的实现,也可能是其他第三方的实现。

一、基于xml配置:

  1. 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>
    
  2. 阿里提供的实现: DruidDataSource

    
    

二、基于java_config:

  1. Spring提供的实现org.springframework.jdbc.datasource.DriverManagerDataSource

    
    
  2. 阿里提供的实现: 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);
	}
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值