- # mysql驱动配置
- spring.datasource.url = jdbc:mysql://localhost:3306/aslan?useUnicode=true&characterEncoding=utf-8
- spring.datasource.username = root
- spring.datasource.password = root
- spring.datasource.driverClassName = com.mysql.jdbc.Driver
- CREATE TABLE `learn_resource` (
- `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
- `author` varchar(20) DEFAULT NULL COMMENT '作者',
- `title` varchar(100) DEFAULT NULL COMMENT '描述',
- `url` varchar(100) DEFAULT NULL COMMENT '地址链接',
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM AUTO_INCREMENT=1035 DEFAULT CHARSET=utf8;
- -- ----------------------------
- -- Records of learn_resource
- -- ----------------------------
- INSERT INTO `learn_resource` VALUES ('999', '官方SpriongBoot例子', '官方SpriongBoot例子', 'https://github.com/spring-www.google.comboot/tree/master/spring-boot-samples');
- INSERT INTO `learn_resource` VALUES ('1000', '龙果学院', 'Spring Boot 教程系列学习', 'www.google.com');
- INSERT INTO `learn_resource` VALUES ('1001', '测试博客', 'Spring Boot干活', 'www.google.com');
- INSERT INTO `learn_resource` VALUES ('1002', '后端编程嘟', 'Spring Boot视频教程', 'www.google.com');
- INSERT INTO `learn_resource` VALUES ('1034', '后端编程嘟', 'Spring 屌屌的视频教程', 'www.google.com');
Spring Boot 默认使用 tomcat-jdbc 数据源,如果你想使用其他的数据源,比如这里使用了阿里巴巴的数据池管理, 除了在 application.properties 配置数据源之外,你应该额外添加以下依赖:
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>druid</artifactId>
- <version>1.0.19</version>
- </dependency>
在 resources 下面的 application.properties 配置文件中添加 druid 的相关参数
- #连接池的配置信息
- spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
- spring.datasource.initialSize=5
- spring.datasource.minIdle=5
- spring.datasource.maxActive=20
- spring.datasource.maxWait=60000
- spring.datasource.timeBetweenEvictionRunsMillis=60000
- spring.datasource.minEvictableIdleTimeMillis=300000
- spring.datasource.validationQuery=SELECT 1 FROM DUAL
- spring.datasource.testWhileIdle=true
- spring.datasource.testOnBorrow=false
- spring.datasource.testOnReturn=false
- spring.datasource.poolPreparedStatements=true
- spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
- spring.datasource.filters=stat,wall,log4j
- spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
- package com.example.demo.config;
- import com.alibaba.druid.pool.DruidDataSource;
- import org.slf4j.LoggerFactory;
- import org.slf4j.Logger;
- import org.springframework.beans.factory.annotation.Value;
- import org.springframework.context.annotation.Bean;
- import org.springframework.context.annotation.Configuration;
- import org.springframework.context.annotation.Primary;
- import javax.sql.DataSource;
- import java.sql.SQLException;
- /**
- * Created by Administrator on 2018/1/4 0004.
- */
- @Configuration
- public class DruidDBConfig {
- private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);
- @Value("${spring.datasource.url}")
- private String dbUrl;
- @Value("${spring.datasource.username}")
- private String username;
- @Value("${spring.datasource.password}")
- private String password;
- @Value("${spring.datasource.driverClassName}")
- private String driverClassName;
- @Value("${spring.datasource.initialSize}")
- private int initialSize;
- @Value("${spring.datasource.minIdle}")
- private int minIdle;
- @Value("${spring.datasource.maxActive}")
- private int maxActive;
- @Value("${spring.datasource.maxWait}")
- private int maxWait;
- @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
- private int timeBetweenEvictionRunsMillis;
- @Value("${spring.datasource.minEvictableIdleTimeMillis}")
- private int minEvictableIdleTimeMillis;
- @Value("${spring.datasource.validationQuery}")
- private String validationQuery;
- @Value("${spring.datasource.testWhileIdle}")
- private boolean testWhileIdle;
- @Value("${spring.datasource.testOnBorrow}")
- private boolean testOnBorrow;
- @Value("${spring.datasource.testOnReturn}")
- private boolean testOnReturn;
- @Value("${spring.datasource.poolPreparedStatements}")
- private boolean poolPreparedStatements;
- @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
- private int maxPoolPreparedStatementPerConnectionSize;
- @Value("${spring.datasource.filters}")
- private String filters;
- @Value("{spring.datasource.connectionProperties}")
- private String connectionProperties;
- @Bean //声明其为Bean实例
- @Primary //在同样的DataSource中,首先使用被标注的DataSource
- public DataSource dataSource(){
- DruidDataSource datasource = new DruidDataSource();
- datasource.setUrl(this.dbUrl);
- datasource.setUsername(username);
- datasource.setPassword(password);
- datasource.setDriverClassName(driverClassName);
- //configuration
- datasource.setInitialSize(initialSize);
- datasource.setMinIdle(minIdle);
- datasource.setMaxActive(maxActive);
- datasource.setMaxWait(maxWait);
- datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
- datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
- datasource.setValidationQuery(validationQuery);
- datasource.setTestWhileIdle(testWhileIdle);
- datasource.setTestOnBorrow(testOnBorrow);
- datasource.setTestOnReturn(testOnReturn);
- datasource.setPoolPreparedStatements(poolPreparedStatements);
- datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
- try {
- datasource.setFilters(filters);
- } catch (SQLException e) {
- logger.error("druid configuration initialization filter", e);
- }
- datasource.setConnectionProperties(connectionProperties);
- return datasource;
- }
- }
##### DruidDBConfig 类被 @Configuration 标注 ,用作配置信息;
##### DataSource 对象被 @Bean 声明 ,为 Spring 容器所管理,
##### @Primary 表示这里定义的 DataSource 将覆盖其他来源的 DataSource。
- public class LearnResouce {
- private Long id;
- private String author;
- private String title;
- private String url;
- ---get---set--方法
- }
- @Controller
- @RequestMapping("/learn")
- public class LearnController {
- private Logger logger = LoggerFactory.getLogger(LearnController.class);
- @Autowired
- LearnService learnService;
- //添加
- @RequestMapping("/add")
- public void addLearn(HttpServletRequest request, HttpServletResponse response){
- LearnResouce learnResouce = new LearnResouce();
- learnResouce.setAuthor("aslan");
- learnResouce.setTitle("test title");
- learnResouce.setUrl("www.baidu.com");
- learnService.add(learnResouce);
- }
- @GetMapping("/query/{id}")
- public void queryLearnResource(@PathVariable Long id){
- LearnResouce learnResouce = learnService.queryLearnResouceById(id);
- if (learnResouce == null){
- System.out.println("not find ");
- }else {
- System.out.println(learnResouce.getAuthor());
- logger.debug(learnResouce.getAuthor());
- logger.info("in here");
- logger.info(learnResouce.getAuthor());
- }
- }
- @GetMapping("/update/{id}")
- public void update(@PathVariable Long id){
- LearnResouce learnResouce = learnService.queryLearnResouceById(id);
- learnResouce.setUrl("www.google.com");
- learnService.update(learnResouce);
- System.out.println("update over!");
- }
- @GetMapping("/delete/{idstring}")
- public void deleteIds(@PathVariable String idstring){
- String[] ids = idstring.split(",");
- learnService.deleteByIds(ids);
- System.out.println("over");
- }
- }
- public interface LearnService {
- int add(LearnResouce learnResouce);
- LearnResouce queryLearnResouceById(Long id);
- int deleteByIds(String[] ids);
- int update(LearnResouce learnResouce);
- public List < LearnResouce > queryLearnResouceList(Map < String, Object > params);
- }
- @Service
- public class LearnServiceImpl implements LearnService {
- @Autowired
- LearnMapper learnMapper;
- @Override
- public int add(LearnResouce learnResouce) {
- return this.learnMapper.add(learnResouce);
- }
- @Override
- public LearnResouce queryLearnResouceById(Long id) {
- return this.learnMapper.queryLearnResourceById(id);
- }
- @Override
- public int deleteByIds(String[] ids) {
- return this.learnMapper.deleteByIds(ids);
- }
- @Override
- public int update(LearnResouce learnResouce) {
- return this.learnMapper.update(learnResouce);
- }
- @Override
- public List<LearnResouce> queryLearnResouceList(Map<String, Object> params) {
- return this.learnMapper.queryLearnResouceList(params);
- }
- }
- package com.example.demo.dao;
- import com.example.demo.domain.LearnResouce;
- import org.apache.ibatis.annotations.*;
- import org.springframework.stereotype.Component;
- /**
- * Created by Administrator on 2018/1/3 0003.
- */
- @Component
- @Mapper
- public interface LearnMapper {
- @Insert("insert into learn_resource(author,title,url) values(#{author},#{title},#{url})")
- int add(LearnResouce learnResouce);
- @Select("select * from learn_resource where id = #{id}")
- LearnResouce queryLearnResourceById(@Param("id") Long id);
- @Update("update learn_resource set author=#{author},title=#{title},url=#{url} where id = #{id}")
- int update(LearnResouce learnResouce);
- @DeleteProvider(type = LearnSqlBuilder.class,method = "deleteByids")
- int deleteByIds(@Param("ids") String[] ids);
- @SelectProvider(type = LearnSqlBuilder.class, method = "queryLearnResouceByParams")
- List<LearnResouce> queryLearnResouceList(Map<String, Object> params);
- class LearnSqlBuilder {
- public String queryLearnResouceByParams(final Map<String, Object> params) {
- StringBuffer sql =new StringBuffer();
- sql.append("select * from learn_resource where 1=1");
- return sql.toString();
- }
- public String deleteByids(@Param("ids") final String[] ids){
- StringBuilder sql = new StringBuilder();
- sql.append("DELETE FROM learn_resource WHERE id in(");
- for (int i = 0 ; i < ids.length; i++){
- if (i == ids.length - 1){
- sql.append(ids[i]);
- }else {
- sql.append(ids[i]).append(",");
- }
- }
- sql.append(")");
- return sql.toString();
- }
- }
- }
暂无
在 pom.xml 中添加依赖
- <dependency>
- <groupId>com.github.pagehelper</groupId>
- <artifactId>pagehelper-spring-boot-starter</artifactId>
- <version>1.1.0</version>
- </dependency>
- @Override
- public List<LearnResouce> queryLearnResouceList(Map<String, Object> params) {
- PageHelper.startPage(Integer.parseInt(params.get("page").toString()), Integer.parseInt(params.get("rows").toString()));
- return this.learnMapper.queryLearnResouceList(params);
- }
Controller 中的用法:
- @GetMapping("/qlist/{page}/{rows}") public void qlist(@PathVariable Integer page, @PathVariable Integer rows) {
- Map < String,
- Object > params = new HashMap < >();
- params.put("page", page);
- params.put("rows", rows);
- List < LearnResouce > list = learnService.queryLearnResouceList(params);
- for (LearnResouce learn: list) {
- System.out.println(learn.getTitle());
- }
- }
- 到此为止,Spring Boot与Mybatis的初步整合就完成了
同 @select 功能类似的还有 @insert,@delete,@update,对应于数据库语句的 CRUD。 使用 @select 很方便 ,不用写配置文件
优点,可以实现动态 SQL 语句。
来源: http://www.bubuko.com/infodetail-2450035.html