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配置：

#DATASOURCE
spring.datasource.name=demo
spring.datasource.url=jdbc:derby:E:/test/db/demo_db;create=true
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=org.apache.derby.jdbc.EmbeddedDriver
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.filters=stat
spring.datasource.validationQuery=select 1 from sysibm.sysdummy1
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxOpenPreparedStatements=20
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#SETTINGS
page.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 {
	@Autowired
	private 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;

@Service
public class ConsumerService {
	@Autowired
	private 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;

@Mapper
public 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;

@Configuration
public 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;

@Component
public class ApplicationReadyEventListener implements ApplicationListener<ApplicationReadyEvent> {
	@Autowired
	private ConsumerService consumerService;

	@Override
	public 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
	 */
	@Override
	protected 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/dragonborn-lxc/springboot-mybatis-derby-demo