SpringBoot的dao层、JdbcTemplate多数据源访问实例

2017-11-02 12:52:10来源:oschina作者:wangxuwei人点击

分享

一、项目目录结构



二、新建MVN项目


创建过程需要填写groupId和artifactId,这里都是springboottest。


三、pom.xml把依赖的库都填进去。内容如下:


xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0
springboottest
springboottest
0.0.1-SNAPSHOT
jar
springboot-mybatis
Demo project for Spring Boot
org.springframework.boot
spring-boot-starter-parent
1.5.8.RELEASE



UTF-8
UTF-8
1.8



org.mybatis.spring.boot
mybatis-spring-boot-starter
1.3.0


org.springframework.boot
spring-boot-starter-web


mysql
mysql-connector-java
runtime


org.springframework.boot
spring-boot-starter-test
test





org.springframework.boot
spring-boot-maven-plugin



四、创建数据库表user及配置:


见Spring Mybatis实例SqlSessionDaoSupport混用xml配置和注解。为了演示多数据源,分别在yiibai和test2中建user表,插入的数据内容不一样(随便写、只要两个不也一样就行)。


-- Database: `yiibai`
INSERT INTO `user` (`id`, `name`, `dept`, `website`, `phone`, `birthday`) VALUES
(1, 'yiibai1', 'Tech', 'http://www.yiibai.com', '13800009911', '1973-11-05'),
(2, '依依2', 'Tech', 'http://www.zjptcc.com', '13800009922', '1973-11-05'),
(3, 'yiibai3', 'Tech', 'http://www.zjptcc.com', '13800009900', '1973-11-05'),
(4, 'yiibai4', 'Tech', 'http://www.zjuni.com', '13800009900', '1973-11-05');--
-- Database: `test2`
--
INSERT INTO `user` (`id`, `name`, `dept`, `website`, `phone`, `birthday`) VALUES
(1, 'yiibai1', 'Tech', 'http://www.yiibai.com', '13800009911', '1973-11-05'),
(2, '依蓓2', 'Tech', 'http://www.zjptcc.com', '13800009900', '1973-11-05'),
(3, '依蓓3', 'Tech', 'http://www.zjptcc.com', '13800009900', '1973-11-05'),
(4, '依依4', 'Tech', 'http://www.zjuni.com', '13800009900', '1973-11-05');

数据源的配置在MVN项目的application.yml中


spring:
datasource:
url : jdbc:mysql://localhost:3306/yiibai?useSSL=false
username : your-user
password : your-passwd
driver-class-name : com.mysql.jdbc.Driver
test2ds:
url : jdbc:mysql://localhost:3306/test2?useSSL=false
username :your-user
password : your-passwd
driver-class-name : com.mysql.jdbc.Driver

修改username和password以适应你的数据库环境


五、DAO层接口和实体类


和Spring Mybatis实例SqlSessionDaoSupport混用xml配置和注解中的例子类似。其中IUser加@Mapper 注释并去掉public User oneByID(int id);方法。修改后内容如下:


package com.yiibai.mybatis.dao;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.yiibai.mybatis.models.User;
@Mapper
public interface IUser {
@Select("select * from user where id= #{id}")
public User getUserByID(int id);@Insert("insert into user(id,name, dept,phone,website,birthday) values(#{id},#{name}, #{dept},#{phone}, #{website}, #{birthday})")
public int insertUser(User user);@Delete("delete from user where id=#{id}")
public int deleteUserById(int id);@Update("update user set name=#{name},dept=#{dept},phone=#{phone},website=#{website},birthday=#{birthday} where id=#{id}")
public int updateUser(User user);@Select("select * from user")
public List getAllUser();
}

User类内容不变。加一个UserRowMapper类JdbcTemplate函数中使用,具体作用见Spring 的持久化实例中的三第2点中有详细说明。内容如下:


package com.yiibai.mybatis.models;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class UserRowMapper implements RowMapper {
public User mapRow(ResultSet rs, int arg1) throws SQLException {
// TODO 自动生成的方法存根
return new User(rs.getInt("id"),rs.getString("name"),rs.getString("dept")
,rs.getString("phone"),rs.getString("website"),rs.getDate("birthday"));
}
}

六、Server类


package com.yiibai.mybatis.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.yiibai.mybatis.dao.IUser;
import com.yiibai.mybatis.models.User;
@Service(value="userService")
public class UserService {
@Autowired
private IUser iUser;
public List getAllUser() {
// TODO 自动生成的方法存根
return iUser.getAllUser();
}

public User getUserByID(int id) {
// TODO 自动生成的方法存根
return iUser.getUserByID(id);
}

public int deleteUserById(int id) {
// TODO 自动生成的方法存根
return iUser.deleteUserById(id);
}
public int updateUser(User user) {
// TODO 自动生成的方法存根
return iUser.updateUser(user);
}
}

七、多数据源配置


package com.yiibai.mybatis.datasource;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
@Configuration
public class DataSourceConfig {
@Bean(name = "primaryDataSource")
@Qualifier("primaryDataSource")
@ConfigurationProperties(prefix="spring.datasource")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondaryDataSource")
@Qualifier("secondaryDataSource")
@Primary
@ConfigurationProperties(prefix="spring.test2ds")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "primaryJdbcTemplate")
public JdbcTemplate primaryJdbcTemplate(
@Qualifier("primaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "secondaryJdbcTemplate")
public JdbcTemplate secondaryJdbcTemplate(
@Qualifier("secondaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}

七、web控制类


这里实现了两个controller类,UserController注入了Service类实例、Ucontroller注入了JdbcTemplate类实例。代码如下:


package com.yiibai.mybatis.web;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import com.yiibai.mybatis.models.User;
import com.yiibai.mybatis.service.UserService;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
@Qualifier("userService")// @Resource(name = "userService")
private UserService userService;@RequestMapping(value = "/list", method = RequestMethod.GET)
public List getUsers() {
return userService.getAllUser();
}@RequestMapping(value = "/{id}", method = RequestMethod.GET)
public User getUserByID(@PathVariable("id") int id) {
return userService.getUserByID(id);
}
}

其中userService会使用默认数据源,也就是在DataSourceConfig中有@Primary注释的那个DataSource


也就是在application.yml中spring.test2ds(数据库test2)那个。


package com.yiibai.mybatis.web;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import com.yiibai.mybatis.models.User;
import com.yiibai.mybatis.models.UserRowMapper;
@RestController
@RequestMapping("/u")
public class Ucontroller {
@Autowired
@Qualifier("primaryJdbcTemplate")
protected JdbcTemplate primaryJdbcTemplate;
@Autowired
@Qualifier("secondaryJdbcTemplate")
protected JdbcTemplate secondaryJdbcTemplate;
@RequestMapping(value = "/list", method = RequestMethod.GET)
public List getUsers() {
String sql = "SELECT * FROM user";
return primaryJdbcTemplate.query(sql, new UserRowMapper());
}
@RequestMapping(value = "/{id}", method = RequestMethod.GET)
public User getUserByID(@PathVariable("id") int id) {
String sql= "SELECT * FROM user WHERE id ='"+id+"' ";
return secondaryJdbcTemplate.queryForObject(sql,new UserRowMapper());
}
}

八、主类:


package com.yiibai.mybatis;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.embedded.ConfigurableEmbeddedServletContainer;
import org.springframework.boot.context.embedded.EmbeddedServletContainerCustomizer;
import org.springframework.web.bind.annotation.RestController;@SpringBootApplication
@RestController
public class SpringbootMutiDsAppimplements EmbeddedServletContainerCustomizer{
public static void main(String[] args) {
SpringApplication.run(SpringbootMutiDsApp.class, args);
}
public void customize(ConfigurableEmbeddedServletContainer container) {
// TODO 自动生成的方法存根
container.setPort(8080);
}
}

九、运行:


. ____ _ __ _ _
/// / ___'_ __ _ _(_)_ ____ _ / / / /
( ( )/___ | '_ | '_| | '_ // _` | / / / /
///___)| |_)| | | | | || (_| |) ) ) )
'|____| .__|_| |_|_| |_/__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v1.5.8.RELEASE)
2017-11-01 09:02:22.233INFO 5972 --- [main] com.yiibai.mybatis.SpringbootMutiDsApp : Starting SpringbootMutiDsApp on mymotif-Vostro-14-5480 with PID 5972 (/home/mymotif/workspace/SpringBootTest/target/classes started by mymotif in /home/mymotif/workspace/SpringBootTest)
2017-11-01 09:02:22.314INFO 5972 --- [main] com.yiibai.mybatis.SpringbootMutiDsApp : No active profile set, falling back to default profiles: default
2017-11-01 09:02:22.458INFO 5972 --- [main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@56de5251: startup date [Wed Nov 01 09:02:22 CST 2017]; root of context hierarchy
2017-11-01 09:02:26.092INFO 5972 --- [main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2017-11-01 09:02:26.140INFO 5972 --- [main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2017-11-01 09:02:26.141INFO 5972 --- [main] org.apache.catalina.core.StandardEngine: Starting Servlet Engine: Apache Tomcat/8.5.23
2017-11-01 09:02:26.341INFO 5972 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]: Initializing Spring embedded WebApplicationContext
2017-11-01 09:02:26.341INFO 5972 --- [ost-startStop-1] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 3895 ms
2017-11-01 09:02:26.509INFO 5972 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean: Mapping servlet: 'dispatcherServlet' to [/]
2017-11-01 09:02:26.514INFO 5972 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'characterEncodingFilter' to: [/*]
2017-11-01 09:02:26.514INFO 5972 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2017-11-01 09:02:26.515INFO 5972 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'httpPutFormContentFilter' to: [/*]
2017-11-01 09:02:26.515INFO 5972 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'requestContextFilter' to: [/*]
2017-11-01 09:02:27.587INFO 5972 --- [main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@56de5251: startup date [Wed Nov 01 09:02:22 CST 2017]; root of context hierarchy
2017-11-01 09:02:27.676INFO 5972 --- [main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/u/{id}],methods=[GET]}" onto public com.yiibai.mybatis.models.User com.yiibai.mybatis.web.Ucontroller.getUserByID(int)
2017-11-01 09:02:27.678INFO 5972 --- [main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/u/list],methods=[GET]}" onto public java.util.List com.yiibai.mybatis.web.Ucontroller.getUsers()
2017-11-01 09:02:27.679INFO 5972 --- [main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user/{id}],methods=[GET]}" onto public com.yiibai.mybatis.models.User com.yiibai.mybatis.web.UserController.getUserByID(int)
2017-11-01 09:02:27.680INFO 5972 --- [main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user/list],methods=[GET]}" onto public java.util.List com.yiibai.mybatis.web.UserController.getUsers()
2017-11-01 09:02:27.685INFO 5972 --- [main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servlet.ModelAndView org.springframework.boot.autoconfigure.web.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
2017-11-01 09:02:27.685INFO 5972 --- [main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity> org.springframework.boot.autoconfigure.web.BasicErrorController.error(javax.servlet.http.HttpServletRequest)
2017-11-01 09:02:27.718INFO 5972 --- [main] o.s.w.s.handler.SimpleUrlHandlerMapping: Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2017-11-01 09:02:27.719INFO 5972 --- [main] o.s.w.s.handler.SimpleUrlHandlerMapping: Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2017-11-01 09:02:27.759INFO 5972 --- [main] o.s.w.s.handler.SimpleUrlHandlerMapping: Mapped URL path [/**/favicon.ico] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2017-11-01 09:02:28.045INFO 5972 --- [main] o.s.j.e.a.AnnotationMBeanExporter : Registering beans for JMX exposure on startup
2017-11-01 09:02:28.230INFO 5972 --- [main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2017-11-01 09:02:28.235INFO 5972 --- [main] com.yiibai.mybatis.SpringbootMutiDsApp : Started SpringbootMutiDsApp in 7.301 seconds (JVM running for 9.627)

http://localhost:8080/user/list



http://localhost:8080/u/list



这里可以看出http://localhost:8080/user/list获得的是test2库中的user表数据


而http://localhost:8080/u/list获得的是yiibai库中(通过primaryJdbcTemplate)的user表数据


参考:


SpringBoot多数据源的配置(SpringBoot+MyBatis)


Spring Mybatis实例SqlSessionDaoSupport混用xml配置和注解


Spring 的持久化实例

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台