springboot学习11之mybatis+derby增删改查模糊分页查询

最新推荐文章于 2024-03-05 17:49:48 发布

夢_殤

最新推荐文章于 2024-03-05 17:49:48 发布

阅读量3.3k

点赞数

分类专栏： spring boot 文章标签： springboot mybatis derby

本文链接：https://blog.csdn.net/dream_broken/article/details/86300340

版权

spring boot 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

准备开发一个小应用程序，数据要能保存起来，数据量不大，所以不想使用常用的mysql/oracle数据库，毕竟要搭建mysql/oracle数据库感觉挺麻烦的，就想到了用内存数据库derby，迁移部署的时候方便，只需把derby指定的库文件夹复制过去，修改下配置文件的路径就行了。但之前没搞过springboot+mybatis+derby，网上找了下资料，折腾了两小时，简单的增删改查跑通了，记录一下。

在之前折腾过springboot+mybatis+mysql(看之前的文章），想着只是把mysql替换为derby而已，所以直接在之前的基础上修改。

JDK使用1.8。derby的10.13.1.1版本支持分页查询了.

项目文件结构

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.fei</groupId>
  <artifactId>springboot-mybatis-derby</artifactId>
  <version>0.0.1-SNAPSHOT</version>
       <!-- 依赖仓库 设置从aliyun仓库下载-->
	<repositories>
		<repository>
			<id>alimaven</id>
			<url>http://maven.aliyun.com/nexus/content/repositories/central/</url>
			<snapshots>
				<enabled>true</enabled>
			</snapshots>
			<releases>
				<enabled>true</enabled>
			</releases>
		</repository>
	</repositories>
	<!-- 插件依赖仓库 -->
	<pluginRepositories>
		<pluginRepository>
			<id>alimaven</id>
			<url>http://maven.aliyun.com/nexus/content/repositories/central/</url>
			<snapshots>
				<enabled>true</enabled>
			</snapshots>
			<releases>
				<enabled>true</enabled>
			</releases>
		</pluginRepository>
	</pluginRepositories>
  
  <properties>
  <!-- 文件拷贝时的编码 -->
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<!-- 编译时的编码 -->
		<maven.compiler.encoding>UTF-8</maven.compiler.encoding>
		
  </properties>
  
   <parent>
	    <groupId>org.springframework.boot</groupId>
	    <artifactId>spring-boot-starter-parent</artifactId>
	    <version>1.5.2.RELEASE</version>
	</parent>   
  
	<dependencies>
	    <dependency>
	        <groupId>org.springframework.boot</groupId>
	        <artifactId>spring-boot-starter-web</artifactId>

	    </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.1.10</version>
		</dependency>
		
		<dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>fastjson</artifactId>
		    <version>1.2.47</version>
		</dependency>

		</dependencies>
		
		<build>
		    <sourceDirectory>src</sourceDirectory>
		    <plugins>
		      <plugin>
		        <artifactId>maven-compiler-plugin</artifactId>
		       <!--  <version>3.1</version> -->
		        <configuration>
		          <source>1.8</source>
		          <target>1.8</target>
		          <encoding>UTF-8</encoding>
		        </configuration>
		      </plugin>
		    </plugins>
		  </build>
</project>

springboot的1.5.2.RELEASE版本下，默认使用的derby是10.13.1.1。为了便于直观操作derby，可以在官网上下载derby，使用里面的ij工具。官网下载地址http://db.apache.org/derby/derby_downloads.html，选择10.13.1.1版本下载

解压后，bin文件夹里有ij.bat工具

配置环境变量：

DERBY_HOME:D:\db-derby-10.13.1.1-bin

path:加上%DERBY_HOME%\bin

classpath:加上%DERBY_HOME%\lib\derby.jar;%DERBY_HOME%\lib\derbytools.jar

有整个demo代码都放在github上了，这里就不全部一一贴出来了，只贴一部分。

application.yml

logging:
  config: classpath:logback.xml
  path: d:/logs
server:
  port: 80
  session-timeout: 60

dbBaseDir: e:/derbyData  #数据库的根路径

mybatis:
     mapperLocations: classpath:/com/fei/dao/*.xml
     typeAliasesPackage: com.fei.dao    
     mapperScanPackage: com.fei.dao
     configLocation: classpath:/mybatis-config.xml

spring:
    datasource:
        name: fabric
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:derby:${dbBaseDir}/fabric;create=true
        username: root
        password: root
        driver-class-name: org.apache.derby.jdbc.EmbeddedDriver
        minIdle: 5
        maxActive: 100
        initialSize: 10
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: select 1 from sysibm.sysdummy1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 50
        removeAbandoned: true
        filters: stat # ,wall,log4j # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        connectionProperties: druid.stat.slowSqlMillis=5000 # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        useGlobalDataSourceStat: true # 合并多个DruidDataSource的监控数据
        druidLoginName: wjf # 登录druid的账号
        druidPassword: wjf # 登录druid的密码

配置url: jdbc:derby:${dbBaseDir}/fabric;create=true中的create=true是说，如果库fabric不存在，则自动创建。

注意connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000中不要有druid.stat.mergeSql=true，否则druid的State会打印错误信息，但是又能操作derby。所以干脆去掉了druid.stat.mergeSql=true。

DatasouceConfig.java

package com.fei.db;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;

@Configuration
public class DatasourceConfig {
	private Logger logger = LoggerFactory.getLogger(DatasourceConfig.class);
	
	@Value("${spring.datasource.name}")
    private String dbName;
	
    @Value("${spring.datasource.url}")
    private String dbUrl;
    
    @Value("${spring.datasource.type}")
    private String dbType;
    
    @Value("${spring.datasource.username}")
    private String username;
    
    @Value("${spring.datasource.password}")
    private String password;
    
    @Value("${spring.datasource.driver-class-name}")
    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.filters}")
    private String filters;
    
    @Value("${spring.datasource.connectionProperties}")
    private String connectionProperties;
    
    @Value("${spring.datasource.useGlobalDataSourceStat}")
    private boolean useGlobalDataSourceStat;
    
    @Value("${spring.datasource.druidLoginName}")
    private String druidLoginName;
	
    @Value("${spring.datasource.druidPassword}")
    private String druidPassword;
    
    @Bean(name="dataSource",destroyMethod = "close", initMethod="init")
    @Primary //不要漏了这
    public DataSource dataSource(){  
        DruidDataSource datasource = new DruidDataSource();  
        try {  
        	datasource.setName(dbName);
	        datasource.setUrl(this.dbUrl);  
	        datasource.setDbType(dbType);
	     //   datasource.setUsername(username);  
	    //    datasource.setPassword(password);  
	        datasource.setDriverClassName(driverClassName);  
	        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.setFilters(filters);  
            datasource.setConnectionProperties(connectionProperties);
            datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
        } catch (SQLException e) {  
            logger.error("druid configuration initialization filter", e);  
        }  
        return datasource;  
    }
    
    /  下面是druid 监控访问的设置  /
    @Bean
    public ServletRegistrationBean druidServlet() {
	    ServletRegistrationBean reg = new ServletRegistrationBean();
	    reg.setServlet(new StatViewServlet());
	    reg.addUrlMappings("/druid/*");  //url 匹配
	    reg.addInitParameter("allow", "192.168.16.110,127.0.0.1"); // IP白名单 (没有配置或者为空，则允许所有访问)
	    reg.addInitParameter("deny", "192.168.16.111"); //IP黑名单 (存在共同时，deny优先于allow)
	    reg.addInitParameter("loginUsername", this.druidLoginName);//登录名
	    reg.addInitParameter("loginPassword", this.druidPassword);//登录密码
	    reg.addInitParameter("resetEnable", "false"); // 禁用HTML页面上的“Reset All”功能
	    return reg;
    }

    @Bean(name="druidWebStatFilter")
    public FilterRegistrationBean filterRegistrationBean() {
	    FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
	    filterRegistrationBean.setFilter(new WebStatFilter());
	    filterRegistrationBean.addUrlPatterns("/*");
	    filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); //忽略资源
	    filterRegistrationBean.addInitParameter("profileEnable", "true");
	    filterRegistrationBean.addInitParameter("principalCookieName", "USER_COOKIE");
	    filterRegistrationBean.addInitParameter("principalSessionName", "USER_SESSION");
	    return filterRegistrationBean;
    }
   
}

MybatisConfiguration.java

package com.fei.db;

import java.io.IOException;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
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.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

/**
 * mybatis的相关配置设置
 * @author Jfei
 *
 */
@Configuration
@AutoConfigureAfter(DatasourceConfig.class)
@ConfigurationProperties
@EnableTransactionManagement
//@MapperScan("com.fei.springboot.dao")
public class MybatisConfiguration implements TransactionManagementConfigurer{

	private static Log logger = LogFactory.getLog(MybatisConfiguration.class);

    //  配置类型别名
        @Value("${mybatis.typeAliasesPackage}")
        private String typeAliasesPackage;

    //  配置mapper的扫描，找到所有的mapper.xml映射文件
//        @Value("${mybatis.mapperLocations : classpath:com/fei/springboot/dao/*.xml}")
        @Value("${mybatis.mapperLocations}")
        private String mapperLocations;

    //  加载全局的配置文件
        @Value("${mybatis.configLocation}")
        private String configLocation;

        @Autowired
        private DataSource dataSource;

        // 提供SqlSeesion
        @Bean(name = "sqlSessionFactory")
        @Primary
        public SqlSessionFactory sqlSessionFactory() {
            try {
                SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
                sessionFactoryBean.setDataSource(dataSource);
                // 读取配置 
                sessionFactoryBean.setTypeAliasesPackage(typeAliasesPackage);
                
                //设置mapper.xml文件所在位置 
                Resource[] resources = new PathMatchingResourcePatternResolver().getResources(mapperLocations);
                sessionFactoryBean.setMapperLocations(resources);
             //设置mybatis-config.xml配置文件位置
                sessionFactoryBean.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));

                //添加分页插件、打印sql插件
                Interceptor[] plugins = new Interceptor[]{sqlPrintInterceptor()};
                sessionFactoryBean.setPlugins(plugins);
                
                return sessionFactoryBean.getObject();
            } catch (IOException e) {
                logger.error("mybatis resolver mapper*xml is error",e);
                return null;
            } catch (Exception e) {
                logger.error("mybatis sqlSessionFactoryBean create error",e);
                return null;
            }
        }

        @Bean
        public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        	return new SqlSessionTemplate(sqlSessionFactory);
        }
        
        //事务管理
        @Bean
        public PlatformTransactionManager annotationDrivenTransactionManager() {
            return new DataSourceTransactionManager(dataSource);
        }

        //将要执行的sql进行日志打印(不想拦截，就把这方法注释掉)
        @Bean
        public SqlPrintInterceptor sqlPrintInterceptor(){
        	return new SqlPrintInterceptor();
        }

   
}

SqlPrintInterceptor.java

package com.fei.db;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;

/**
 * MyBatis 将mybatis要执行的sql拦截打印出来
 *
 * @since 1.0.0
 */
@Intercepts
({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
public class SqlPrintInterceptor implements Interceptor {

	private static Log logger = LogFactory.getLog(SqlPrintInterceptor.class);
	
    private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameterObject = null;
        if (invocation.getArgs().length > 1) {
            parameterObject = invocation.getArgs()[1];
        }

        long start = System.currentTimeMillis();

        Object result = invocation.proceed();
        
        String statementId = mappedStatement.getId();
        BoundSql boundSql = mappedStatement.getBoundSql(parameterObject);
        Configuration configuration = mappedStatement.getConfiguration();
        String sql = getSql(boundSql, parameterObject, configuration);

        long end = System.currentTimeMillis();
        long timing = end - start;
        if(logger.isInfoEnabled()){
        	logger.info("执行sql耗时:" + timing + " ms" + " - id:" + statementId + " - Sql:" );
        	logger.info("   "+sql);
        }
       
        return result;
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof Executor) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    @Override
    public void setProperties(Properties properties) {
    }

    private String getSql(BoundSql boundSql, Object parameterObject, Configuration configuration) {
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
        if (parameterMappings != null) {
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else if (parameterObject == null) {
                        value = null;
                    } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else {
                        MetaObject metaObject = configuration.newMetaObject(parameterObject);
                        value = metaObject.getValue(propertyName);
                    }
                    sql = replacePlaceholder(sql, value);
                }
            }
        }
        return sql;
    }

    private String replacePlaceholder(String sql, Object propertyValue) {
        String result;
        if (propertyValue != null) {
            if (propertyValue instanceof String) {
                result = "'" + propertyValue + "'";
            } else if (propertyValue instanceof Date) {
                result = "'" + DATE_FORMAT.format(propertyValue) + "'";
            } else {
                result = propertyValue.toString();
            }
        } else {
            result = "null";
        }
        return sql.replaceFirst("\\?", Matcher.quoteReplacement(result));
    }
}

UserMapper.java

package com.fei.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.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.fei.domain.User;


@Mapper
public interface UserMapper {

	@Update("create table sys_user(id varchar(50) not null primary key,user_name varchar(30) )")
	void createTable();
	
	@Select("select count(1) from sys_user")
	int count();
	
	@Insert("insert into sys_user(id,user_name) values(#{id},#{userName})")
	void insert(User u);
	
	@Select("<script> select * from sys_user "
			+ " where 1=1 "
			+ " <if test=\"userName != null \"> and user_name like '%'||#{userName}||'%' </if> "
			+ " offset #{startIndex} rows fetch next #{pageSize} rows only </script>")
	List<User> queryByPage(@Param("userName")String userName, @Param("startIndex")int startIndex,@Param("pageSize")int pageSize);
	
	//注：方法名和要UserMapper.xml中的id一致
	List<User> query(@Param("userName")String userName);
	
	@Delete("delete from sys_user")
	void deleteAll();
}

如果不习惯把sql写在接口上面的，可以写在xml文件里，直接在接口类的包下创建xml文件

UserMapper.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="com.fei.dao.UserMapper">
 
<select id="query"  resultType="com.fei.domain.User">
    select id ,user_name 
    from sys_user 
    where 1=1
    <if test="userName != null">
      and user_name like '%'||#{userName}||'%'
    </if> 
</select>
</mapper>

UserService.java

package com.fei.service;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.stereotype.Service;

import com.fei.dao.UserMapper;
import com.fei.domain.User;

@Service
public class UserService{

	private static Logger LOG = LoggerFactory.getLogger(UserService.class);
	
	@Autowired
	private UserMapper userMapper;
	
	/**
	 * 如果表不存在，则创建表
	 */
	public void createTableIfNotExist(){
		try {
			userMapper.count();
		} catch (BadSqlGrammarException  e) {
			//42X05 表示表或试图不存在
			if(e.getSQLException().getSQLState().equals("42X05")){
				try {
					userMapper.createTable();
				} catch (Exception e2) {
					LOG.error("创建表异常",e2);
				}
				
			}else{
				LOG.error("创建表未知异常",e);
			}
			
		}catch(Exception e){
			LOG.error("创建表未知异常",e);
		}
	}
	
	public void insert(User u){
		this.userMapper.insert(u);
	}
	
	public List<User> queryByPage(String userName,int pageNum,int pageSize){
		int startIndex = (pageNum - 1)*pageSize;
		return userMapper.queryByPage(userName,startIndex, pageSize);
	}
	
	public List<User> query(String userName){
		return userMapper.query(userName);
	}
	
}

UserController.java

package com.fei.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.fastjson.JSONObject;
import com.fei.domain.User;
import com.fei.service.UserService;

@Controller
@RequestMapping("/user")
public class UserController {

	
	@Autowired
	private UserService userService;
	
	@RequestMapping("/hello")
	@ResponseBody
	public String hello(){
		return "hello";
	}
	/**
	 * 测试插入
	 * @return
	 */
	@RequestMapping("/add")
	@ResponseBody
	public String add(){
		User u = new User();
		int i = (int)Math.random()*100+100;
		u.setId(String.valueOf(i));
		u.setUserName("test哈哈哈"+i);
		this.userService.insert(u);
		return "success";
	}
	/**
	 * 测试分页
	 * @return
	 */
	@RequestMapping("/queryPage")
	@ResponseBody
	public String queryPage(Integer pageNum,String userName){
		List<User> list = this.userService.queryByPage(userName != null ?userName:null,
				pageNum == null ? 1 : pageNum, 2);
		return JSONObject.toJSONString(list);
	}
	
	/**
	 * 测试mapper.xml
	 * @return
	 */
	@RequestMapping("/query")
	@ResponseBody
	public String query(String userName){
		List<User> list = this.userService.query(userName != null ?userName:null);
		return JSONObject.toJSONString(list);
	}
}

可以事先通过derby的ij工具先创建数据库和表，也可以代码创建表。比如写个监听类，springboot启动的时候，就检查表是否已创建了，如果没有就先创建表。

ApplicationReadyEventListener.java

package com.fei.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.fei.service.UserService;

/**
 * 监听spring boot的启动
 * @author Jfei
 *
 */
@Component
public class ApplicationReadyEventListener implements ApplicationListener<ApplicationReadyEvent>{

	@Autowired
	private UserService userService;
	
	@Override
	public void onApplicationEvent(ApplicationReadyEvent event) {
		initCreateDbTable();
		
	}
	/**
	 * 如果数据库表不存在，则创建表
	 */
	private void initCreateDbTable(){
		userService.createTableIfNotExist();
	}

}

启动类

Application.java

package com.fei;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.context.embedded.ConfigurableEmbeddedServletContainer;
import org.springframework.boot.context.embedded.EmbeddedServletContainerCustomizer;
import org.springframework.boot.web.support.SpringBootServletInitializer;
import org.springframework.context.annotation.ComponentScan;

@EnableAutoConfiguration
@ComponentScan(basePackages={"com.fei"})
@SpringBootApplication
public class Application extends SpringBootServletInitializer implements EmbeddedServletContainerCustomizer{

	 @Override  
    protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {  
        return application.sources(Application.class);  
    }  

	 
	 public static void main(String[] args) throws Exception {
	        SpringApplication.run(Application.class, args);
	    }

	public void customize(ConfigurableEmbeddedServletContainer configurableEmbeddedServletContainer) {
	//	configurableEmbeddedServletContainer.setPort(9090);
	}
}

运行起来后，可以登录127.0.0.1/druid的监控，查看sql监控

可以看到有2个sql，一个是application.yml里配置的validationQuery: select 1 from sysibm.sysdummy1；一个是监听类检查表是否存在时执行的查询count(1)（因为不是第一次执行，表已经创建了，所以没看到create table）。

可以调用UserController里的/add和/queryPage进行测试。

derby数据，还可使用ij.bat进行操作。运行ij.bat(derby的数据库只允许一个客户端连接，如果程序连接了，ij就不能连接了，所以要先停止代码）

derby的语法和常规的sql一样，只是某些函数不一样，自行百度。

demo代码。

夢_殤

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
springboot学习11之mybatis+derby增删改查模糊分页查询

准备开发一个小应用程序，数据要能保存起来，数据量不大，所以不想使用常用的mysql/oracle数据库，毕竟要搭建mysql/oracle数据库感觉挺麻烦的，就想到了用内存数据库derby，迁移部署的时候方便，只需把derby指定的库文件夹复制过去，修改下配置文件的路径就行了。但之前没搞过springboot+mybatis+derby，网上找了下资料，折腾了两小时，简单的增删改查跑通了...
复制链接

扫一扫