SpringBoot集成sqlite，并用集成tk做baseservice封装

最新推荐文章于 2024-02-29 09:06:19 发布

置顶 cdchen2017

最新推荐文章于 2024-02-29 09:06:19 发布

阅读量1.4k

点赞数 1

分类专栏：快速开发框架文章标签： SpringBoot sqllite mybatis tkmapper

本文链接：https://blog.csdn.net/justhavetry/article/details/90041633

版权

快速开发框架专栏收录该内容

0 篇文章 0 订阅

订阅专栏

项目是用来做爬虫的，springboot项目，集成了mybatis、sqllite已经tkmapper，并简单封装了basemapper和baseservice，方便快速。

1. pom

<?xml version="1.0" encoding="UTF-8"?>
<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.cdchen.crawler</groupId>
	<artifactId>crawler</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.6.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<name>crawler</name>
	<description>crawler</description>

	<properties>
		<selenium.version>3.4.0</selenium.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
		<commons-io.version>2.4</commons-io.version>
		<poi.version>3.10-FINAL</poi.version>
		<mybatis.springboot.starter>1.3.0</mybatis.springboot.starter>
		<druid.version>1.1.2</druid.version>
		<mybatis.generator.version>1.3.5</mybatis.generator.version>
		<pagehelper.version>1.1.1</pagehelper.version>
		<swagger.ui.version>2.7.0</swagger.ui.version>
		<easy-poi-version>3.0.1</easy-poi-version>
	</properties>

	<dependencies>
		<!-- 使用sqllite作为数据库 -->
		<dependency>
			<groupId>org.xerial</groupId>
			<artifactId>sqlite-jdbc</artifactId>
			<version>3.21.0.1</version>
		</dependency>
		<!-- 爬虫jar -->
		<dependency>
			<groupId>org.seleniumhq.selenium</groupId>
			<artifactId>selenium-java</artifactId>
		</dependency>
		<!-- 验证码 -->
		<dependency>
			<groupId>net.java.dev.jna</groupId>
			<artifactId>jna</artifactId>
			<version>4.1.0</version>
		</dependency>

		<dependency>
			<groupId>net.sourceforge.tess4j</groupId>
			<artifactId>tess4j</artifactId>
			<version>3.4.0</version>
			<exclusions>
				<exclusion>
					<groupId>com.sun.jna</groupId>
					<artifactId>jna</artifactId>
				</exclusion>
			</exclusions>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-actuator</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>${commons-io.version}</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>${poi.version}</version>
		</dependency>

		<!--分页插件-->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>${pagehelper.version}</version>
		</dependency>
		<!--Swagger2-->
		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger2</artifactId>
			<version>${swagger.ui.version}</version>
		</dependency>
		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger-ui</artifactId>
			<version>${swagger.ui.version}</version>
		</dependency>
		<!--第三方 swagger2 ui-->
		<dependency>
			<groupId>com.drore.cloud</groupId>
			<artifactId>swagger-bootstrap-ui</artifactId>
			<version>1.0</version>
		</dependency>
		<!-- mybatis-plus begin -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>tk.mybatis</groupId>
			<artifactId>mapper-spring-boot-starter</artifactId>
			<version>1.2.4</version>
		</dependency>
		<!-- mybatis-plus end -->
		<!--Gson-->
		<dependency>
			<groupId>com.google.code.gson</groupId>
			<artifactId>gson</artifactId>
			<version>2.8.1</version>
		</dependency>
		<!--Gson end-->
		<!--Excel导入导出相关START-->
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-base</artifactId>
			<version>${easy-poi-version}</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-web</artifactId>
			<version>${easy-poi-version}</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-annotation</artifactId>
			<version>${easy-poi-version}</version>
		</dependency>
		<dependency>
			<groupId>com.itextpdf</groupId>
			<artifactId>itextpdf</artifactId>
			<version>5.5.6</version>
		</dependency>

		<dependency>
			<groupId>com.itextpdf</groupId>
			<artifactId>itext-asian</artifactId>
			<version>5.2.0</version>
		</dependency>
		<!--Excel导入导出相关END-->
		<!--工具类-->
		<dependency>
			<groupId>com.xiaoleilu</groupId>
			<artifactId>hutool-all</artifactId>
			<version>3.1.1</version>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.16.18</version>
		</dependency>
		<dependency>
			<groupId>com.github.nintha</groupId>
			<artifactId>webp-imageio-core</artifactId>
			<version>0.4.2</version>
			<scope>system</scope>
			<systemPath>${project.basedir}/lib/webp-imageio-0.4.2.jar</systemPath>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.33</version>
		</dependency>
		<dependency>
			<groupId>javax.persistence</groupId>
			<artifactId>persistence-api</artifactId>
			<version>1.0</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
			<!--mybatis 逆向工程插件-->
			<plugin>
				<groupId>org.mybatis.generator</groupId>
				<artifactId>mybatis-generator-maven-plugin</artifactId>
				<version>${mybatis.generator.version}</version>
				<configuration>
					<verbose>true</verbose>
					<overwrite>true</overwrite>
					<configurationFile>src/main/resources/generateConfig.xml</configurationFile>
				</configuration>
			</plugin>
		</plugins>
	</build>


</project>

2. 配置文件

## 数据源
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.datasource.url=jdbc:sqlite:D:/crawler-plugin/my.s3db
spring.datasource.username=root
spring.datasource.password=root

##mybatis xml路径
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis-plus.mapper-locations=classpath:mapper/*.xml

3. 配置类


import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.sqlite.SQLiteDataSource;

import javax.sql.DataSource;

@Configuration
public class MyBatisConfig {
    @Autowired
    private DataSourceProperties dataSourceProperties;
    @Bean(name="dataSource")
    public DataSource dataSource() {
        SQLiteDataSource dataSource = new SQLiteDataSource();
        dataSource.setUrl(dataSourceProperties.getUrl());
        return dataSource;
    }

    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        return sqlSessionFactoryBean.getObject();
    }

}

3. 主类启动时配置mapperscan

这里的MapperScan 需要使用tkmapper jar包的，不要用错了

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
import tk.mybatis.spring.annotation.MapperScan;

@SpringBootApplication
@MapperScan("com.cdchen.crawler.mapper")
public class Application extends WebMvcConfigurerAdapter{

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

	@Override
	public void addResourceHandlers(ResourceHandlerRegistry registry) {
		registry.addResourceHandler("doc.html").addResourceLocations("classpath:/META-INF/resources/");
		registry.addResourceHandler("swagger-ui.html").addResourceLocations("classpath:/META-INF/resources/");
		registry.addResourceHandler("/webjars/**").addResourceLocations("classpath:/META-INF/resources/webjars/");
		super.addResourceHandlers(registry);
	}
}

4. BaseService

import com.cdchen.crawler.param.Query;
import com.cdchen.crawler.response.TableResultResponse;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.entity.Example;

import java.lang.reflect.ParameterizedType;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

public abstract class BaseService<M extends Mapper<T>, T> {
    @Autowired
    protected M mapper;

    public void setMapper(M mapper) {
        this.mapper = mapper;
    }

    /**
     * 返回单个对象
     * @param entity
     * @return
     */
    public T selectOne(T entity) {
        return mapper.selectOne(entity);
    }

    /**
     * 根据主键进行查询,必须保证结果唯一
     * 单个字段做主键时,可以直接写主键的值
     * 联合主键时,key可以是实体类,也可以是Map
     * @param id
     * @return
     */
    public T selectById(Object id) {
        return mapper.selectByPrimaryKey(id);
    }


    public List<T> selectList(T entity) {
        return mapper.select(entity);
    }

    /**
     * 查询所有记录
     * @return
     */
    public List<T> selectListAll() {
        return mapper.selectAll();
    }


    public Long selectCount(T entity) {
        return new Long(mapper.selectCount(entity));
    }

    /*插入一条数据 null的属性也会保存
       *支持Oracle序列,UUID,类似Mysql的INDENTITY自动增长(自动回写)
       *优先使用传入的参数值,参数值空时,才会使用序列、UUID,自动增长
       *
       **/
    public void insert(T entity) {
        // EntityUtils.setCreatAndUpdatInfo(entity);
        mapper.insert(entity);
    }

    /**
     *插入一条数据 null的属性不会保存
     *只插入不为null的字段,不会影响有默认值的字段
     *支持Oracle序列,UUID,类似Mysql的INDENTITY自动增长(自动回写)
     *优先使用传入的参数值,参数值空时,才会使用序列、UUID,自动增长
     */
    public T insertSelective(T entity) {
        // EntityUtils.setCreatAndUpdatInfo(entity);
        mapper.insertSelective(entity);
        return entity;
    }

    /**
     * 根据实体类中字段不为null的条件进行删除,条件全部使用=号and条件
     * @param entity
     * @return
     */
    public void delete(T entity) {
        mapper.delete(entity);
    }

    /**
     *通过主键进行删除,这里最多只会删除一条数据
     *单个字段做主键时,可以直接写主键的值
     *联合主键时,key可以是实体类,也可以是Map
     *
     */
    public void deleteById(Object id) {
        mapper.deleteByPrimaryKey(id);
    }

    /**
     * 根据主键id批量删除
     * @param ids
     * @return
     */
    public void deleteBatchIds(String[] ids){
        if(ids!=null && ids.length>0){
            List<String> idList= Arrays.asList(ids);
            for(String id:idList){
                mapper.deleteByPrimaryKey(Long.valueOf(id));
            }
        }
    }

    /**
     * 根据主键进行更新,这里最多只会更新一条数据
     * 参数为实体类
     */
    public int updateById(T entity) {
        // EntityUtils.setUpdatedInfo(entity);
        return mapper.updateByPrimaryKey(entity);
    }

    /**
     * 根据主键进行更新
     * 只会更新不是null的数据
     */
    public int updateSelectiveById(T entity) {
        // EntityUtils.setUpdatedInfo(entity);
        return mapper.updateByPrimaryKeySelective(entity);
    }


    public List<T> selectByExample(Object example) {
        return mapper.selectByExample(example);
    }

    public int selectCountByExample(Object example) {
        return mapper.selectCountByExample(example);
    }

    public TableResultResponse<T> selectByQuery(Query query) {
        Class<T> clazz = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[1];
        Example example = new Example(clazz);
        if(query.entrySet().size()>0) {
            Example.Criteria criteria = example.createCriteria();
            for (Map.Entry<String, Object> entry : query.entrySet()) {
                criteria.andLike(entry.getKey(), "%" + entry.getValue().toString() + "%");
            }
        }
        Page<Object> result = PageHelper.startPage(query.getPage(), query.getLimit());
        List<T> list = mapper.selectByExample(example);
        return new TableResultResponse<T>(result.getTotal(), list);
    }
}

5. 设置实体类自增Id key generator

因为sqllite插入插入的Id的语法不同，所以在需要插入后获取Id时，需要配置Id的generator


import lombok.Data;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 *
 * @description: 目标网站
 *
 * @author: cdchen
 *
 * @create: 2019-05-08 10:48
 **/
@Table(name="target")
@Data
public class Target {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY,generator = "SELECT LAST_INSERT_ROWID()")
    private Integer id;
    private String name;
    private String indexUrl;
}

6. 使用示例

service

@Component
@Slf4j
public class TargetService extends BaseService<TargetMapper,Target>{
    @Autowired
    private  TargetMapper targetMapper;

    public Target getTarget(){
        Target target = targetMapper.selectByName(Constant.qiushibaikeName);
        if(target == null){
            log.info("=====> 初始化target");
            target = new Target();
            target.setIndexUrl(Constant.qiushibaikeIndexUrl);
            target.setName(Constant.qiushibaikeName);
            this.insertSelective(target);
        }
        return target;
    }
}

mapper

import com.cdchen.crawler.entity.Target;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;

@Repository
public interface TargetMapper  extends Mapper<Target> {
    // 根据 ID 查询
    Target selectByName(@Param("name") String name);

}

项目放在了gitee上，地址：https://gitee.com/passionday/crawler

cdchen2017

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot集成sqlite，并用集成tk做baseservice封装

项目是用来做爬虫的，springboot项目，集成了mybatis、sqllite已经tkmapper，并简单封装了basemapper和baseservice，方便快速。1. pom<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:x...
复制链接

扫一扫