Maven构建Spring Boot+Mybatis+derby的配置

最近项目用到了spring boot和derby的内嵌模式组合，感觉derby作为内嵌数据库的话，使用极其小巧方便，不需要再去配置什么server了，所以整理了一下，写了个简短的demo，因为习惯用mybatis了，所以使用的是mybatis，嫌xml配置麻烦故没有使用xml配置的模式，使用的是注解模式，然后数据库连接池用的是阿里的druid，spring boot的话默认也配置有自己的数据库连接池。

项目结构：

pom.xml配置：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.endnesswaltz</groupId><artifactId>demo</artifactId><version>1.0.0</version><packaging>war</packaging><name>demo</name><description>Spring Boot+Mybatis+derby</description><properties><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>1.5.3.RELEASE</version></parent><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId><exclusions><exclusion><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-tomcat</artifactId></exclusion></exclusions></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>1.3.0</version></dependency><dependency><groupId>org.apache.derby</groupId><artifactId>derby</artifactId><version>10.13.1.1</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.0.31</version></dependency><dependency><groupId>javax.servlet</groupId><artifactId>javax.servlet-api</artifactId><version>3.1.0</version><scope>provided</scope></dependency></dependencies><build><finalName>demo</finalName><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-war-plugin</artifactId><configuration><warName>demo</warName></configuration></plugin><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><configuration><source>1.8</source><target>1.8</target></configuration></plugin><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

application.properties配置：

#DATASOURCEspring.datasource.name=demospring.datasource.url=jdbc:derby:E:/test/db/demo_db;create=truespring.datasource.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.driver-class-name=org.apache.derby.jdbc.EmbeddedDriverspring.datasource.initialSize=5spring.datasource.minIdle=5spring.datasource.maxActive=20spring.datasource.maxWait=60000spring.datasource.timeBetweenEvictionRunsMillis=60000spring.datasource.minEvictableIdleTimeMillis=300000spring.datasource.filters=statspring.datasource.validationQuery=select 1 from sysibm.sysdummy1spring.datasource.testWhileIdle=truespring.datasource.testOnBorrow=falsespring.datasource.testOnReturn=falsespring.datasource.poolPreparedStatements=truespring.datasource.maxOpenPreparedStatements=20spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000#SETTINGSpage.limit=10

ConsumerController.java：

package com.endnesswaltz.demo.controller;import java.util.Arrays;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Value;import org.springframework.dao.DuplicateKeyException;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.RestController;import com.endnesswaltz.demo.entity.Consumer;import com.endnesswaltz.demo.entity.json.Response;import com.endnesswaltz.demo.service.ConsumerService;@RestController@RequestMapping("/consumer")public class ConsumerController {@Autowiredprivate ConsumerService consumerService;@Value("${page.limit}")    private String limit;@RequestMapping(value = "/create", method = RequestMethod.POST)public Response create(@RequestParam String id, @RequestParam String name, @RequestParam String age, @RequestParam String sex) {Response response = new Response();try {consumerService.create(id, name, age, sex);} catch (DuplicateKeyException e) {e.printStackTrace();return response.failure("data exists");} catch (Exception e) {e.printStackTrace();return response.failure("server is busy,try again later");}return response.success("create successfully");}@RequestMapping(value = "/retrieve", method = RequestMethod.GET)public Response retrieve(@RequestParam String offset) {Response response = new Response();List<Consumer> list = null;try {if ("-1".equals(offset)) {list =  consumerService.retrieveAll();} else {list =  consumerService.retrieveSeveral(limit, offset);}} catch (Exception e) {e.printStackTrace();return response.failure("server is busy,try again later");}return response.success(list);}@RequestMapping(value = "/update", method = RequestMethod.PUT)public Response update(@RequestParam String id, @RequestParam String name, @RequestParam String age, @RequestParam String sex) {Response response = new Response();try {consumerService.update(id, name, age, sex);} catch (Exception e) {e.printStackTrace();return response.failure("server is busy,try again later");}return response.success("update successfully");}@RequestMapping(value = "/delete", method = RequestMethod.DELETE)public Response delete(@RequestParam String[] ids) {Response response = new Response();try {consumerService.delete(Arrays.asList(ids));} catch (Exception e) {e.printStackTrace();return response.failure("server is busy,try again later");}return response.success("delete successfully");}}

ConsumerService.java：

package com.endnesswaltz.demo.service;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.BadSqlGrammarException;import org.springframework.stereotype.Service;import com.endnesswaltz.demo.entity.Consumer;import com.endnesswaltz.demo.mapper.ConsumerMapper;@Servicepublic class ConsumerService {@Autowiredprivate ConsumerMapper dao;public Integer count() throws Exception {try {return dao.count();} catch (BadSqlGrammarException e) {if (e.getSQLException().getSQLState().equals("42X05")) {dao.create();}}return 0;}public void create(String id, String name, String age, String sex) throws Exception {Consumer obj = new Consumer();obj.setId(Long.parseLong(id));obj.setName(name);obj.setAge(age);obj.setSex(sex);dao.insert(obj);}public List<Consumer> retrieveAll() throws Exception {return dao.selectAll();}public List<Consumer> retrieveSeveral(String limit, String offset) throws Exception {return dao.selectSeveral(Integer.parseInt(limit), Integer.parseInt(offset));}public void update(String id, String name, String age, String sex) throws Exception {Consumer obj = new Consumer();obj.setId(Long.parseLong(id));obj.setName(name);obj.setAge(age);obj.setSex(sex);dao.update(obj);}public void delete(List<String> ids) throws Exception {dao.delete(ids);}}

Consumer.java：

package com.endnesswaltz.demo.entity;public class Consumer {private Long id;private String name;private String age;private String sex;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 String getAge() {return age;}public void setAge(String age) {this.age = age;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}}

ConsumerMapper.java：

package com.endnesswaltz.demo.mapper;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.Param;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.annotations.Update;import com.endnesswaltz.demo.entity.Consumer;@Mapperpublic interface ConsumerMapper {@Update("create table consumer(id bigint,name varchar(255),age smallint,sex varchar(1),primary key (id))")void create() throws Exception;@Select("select count(1) from consumer")Integer count() throws Exception;@Select("select * from consumer")List<Consumer> selectAll() throws Exception;@Select("select * from consumer order by id asc {limit ${limit} offset ${offset}}")List<Consumer> selectSeveral(@Param("limit")int limit, @Param("offset")int offset) throws Exception;@Insert("insert into consumer(id,name,age,sex) values(#{o.id}, #{o.name}, #{o.age}, #{o.sex})")void insert(@Param("o")Consumer obj) throws Exception;@Delete("<script>delete from consumer where id in <foreach item='item' index='index' collection='ids' open='(' separator=',' close=')'>#{item}</foreach></script>")void delete(@Param("ids")List<String> ids) throws Exception;@Update("update consumer set name = #{o.name}, age = #{o.age}, sex = #{o.sex} where id = #{o.id}")void update(@Param("o")Consumer obj) throws Exception;}

Response.java（用于统一返回固定格式的json类型）：

package com.endnesswaltz.demo.entity.json;public class Response {private String code;private String message;private Object data;public Response success() {this.code = "1";return this;}public Response success(String message) {this.code = "1";this.message = message;return this;}public Response success(Object data) {this.code = "1";this.data = data;return this;}public Response success(String message, Object data) {this.code = "1";this.message = message;this.data = data;return this;}public Response failure() {this.code = "0";return this;}public Response failure(String message) {this.code = "0";this.message = message;return this;}public String getCode() {return code;}public void setCode(String code) {this.code = code;}public String getMessage() {return message;}public void setMessage(String message) {this.message = message;}public Object getData() {return data;}public void setData(Object data) {this.data = data;}}

DruidDataSourceConfiguration.java：

package com.endnesswaltz.demo.config;import javax.sql.DataSource;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import com.alibaba.druid.pool.DruidDataSource;@Configurationpublic class DruidDataSourceConfiguration {@Bean@ConfigurationProperties(prefix = "spring.datasource")public DataSource druidDataSource() {DruidDataSource druidDataSource = new DruidDataSource();return druidDataSource;}}

DruidStatViewServlet.java（druid的监控分析页面配置）：

package com.endnesswaltz.demo.config;import javax.servlet.annotation.WebInitParam;import javax.servlet.annotation.WebServlet;import com.alibaba.druid.support.http.StatViewServlet;@WebServlet(urlPatterns = "/druid/*", initParams = {@WebInitParam(name = "allow",value="127.0.0.1"),@WebInitParam(name = "deny", value = ""),@WebInitParam(name = "loginUsername", value = "test"),@WebInitParam(name = "loginPassword", value = "test"),@WebInitParam(name = "resetEnable", value = "false")})public class DruidStatViewServlet extends StatViewServlet {/** *  */private static final long serialVersionUID = 1L;}

DruidStatFilter.java（druid的过滤器配置）：

package com.endnesswaltz.demo.filter;import javax.servlet.annotation.WebFilter;import javax.servlet.annotation.WebInitParam;import com.alibaba.druid.support.http.WebStatFilter;@WebFilter(filterName = "druidWebStatFilter", urlPatterns = "/*", initParams = {@WebInitParam(name = "exclusions", value = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")})public class DruidStatFilter extends WebStatFilter {}

ApplicationReadyEventListener.java（自定义的spring boot启动监听，这里是当表不存在时就建表）：

package com.endnesswaltz.demo.listener;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.context.event.ApplicationReadyEvent;import org.springframework.context.ApplicationListener;import org.springframework.stereotype.Component;import com.endnesswaltz.demo.service.ConsumerService;@Componentpublic class ApplicationReadyEventListener implements ApplicationListener<ApplicationReadyEvent> {@Autowiredprivate ConsumerService consumerService;@Overridepublic void onApplicationEvent(ApplicationReadyEvent event) {try {consumerService.count();} catch (Exception e) {e.printStackTrace();}}}

Application.java（spring boot启动主程序）：

package com.endnesswaltz.demo;import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;import org.springframework.boot.builder.SpringApplicationBuilder;import org.springframework.boot.web.support.SpringBootServletInitializer;import org.springframework.context.annotation.ComponentScan;@SpringBootApplication@ComponentScan("com.endnesswaltz.demo")@MapperScan("com.endnesswaltz.demo.mapper")public class Application extends SpringBootServletInitializer {/** * use container to start application */@Overrideprotected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {return builder.sources(Application.class);}/** * @param args */public static void main(String[] args) {SpringApplication.run(Application.class, args);}}

至此，基本上所有的核心内容就配置好了，然后启动tomcat，可以看到crud的结果，这里我用的是postman来测试

你还可以进入htto://yourhost/yourproject/druid/index.html进入查看druid监听到的sql状态，这里就不截图了

如果还有什么疑问或者发现错误，望各位大神指教，谢谢~~~

---

参考：
derby:

http://db.apache.org/derby/docs/10.13/ref/index.html

spring boot:

http://projects.spring.io/spring-boot/

代码：

https://github.com/EndnessWaltz/springboot-mybatis-derby-demo