springboot整合postgresql

依赖

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-maven-plugin</artifactId>
            <version>1.3.7</version>
        </dependency>

配置application.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=123456
spring.datasource.driver-class-name=org.postgresql.Driver

controller

package com.example.demo.controller;


import com.example.demo.entity.Dome;
import com.example.demo.page.PageRequest;
import com.example.demo.service.DomeService;
import com.github.pagehelper.PageInfo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @creator wangli66
 * @create-time 11:06 2019/12/3
 **/
@Api(value = "Dome工程")
@RestController
@RequestMapping("/dome")
public class DomeController {

    @Autowired
    private DomeService domeService;

    @ApiOperation(value = "testInterceptor")
    @RequestMapping("/testInterceptor")
    public String testInterceptor(String param) {
        return domeService.testInterceptor(param);
    }

    @ApiOperation(value = "findDome")
    @RequestMapping("/findDome")
    public String findDome(Dome dome) {
        return domeService.findById(dome).toString();
    }

    @ApiOperation(value = "findAll")
    @RequestMapping("/findAll")
    public List<Dome> findAll() {
        return domeService.findAll();
    }

    @ApiOperation(value = "insert")
    @RequestMapping("/insert")
    public void insert(Dome dome) {
        domeService.insertDome(dome);
    }
    
    @ApiOperation(value = "findAllPage")
    @RequestMapping("/findAllPage")
    public PageInfo<Dome> findAllPage(PageRequest pageRequest) {
        return domeService.findAllPage(pageRequest);
    }
}

service

package com.example.demo.service;

import com.example.demo.entity.Dome;
import com.example.demo.page.PageRequest;
import com.github.pagehelper.PageInfo;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @creator wangli66
 * @create-time 17:14 2019/12/26
 **/
@Service
public interface DomeService {

    String testInterceptor(String param);
    Dome findById(Dome dome);
    List<Dome> findAll();
    void insertDome(Dome dome);

    PageInfo<Dome> findAllPage(PageRequest pageRequest);
}

impl

package com.example.demo.service;

import com.example.demo.dao.DomeDaoMapper;
import com.example.demo.entity.Dome;
import com.example.demo.page.PageRequest;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @creator wangli66
 * @create-time 17:17 2019/12/26
 **/
@Component
@Transactional
public class DomeServiceImpl implements DomeService {
    @Autowired
    private DomeDaoMapper domeDaoMapper;

    @Override
    public String testInterceptor(String param) {
        return "测试拦截器：" + param;
    }

    @Override
    public Dome findById(Dome dome) {
        return domeDaoMapper.findById(dome);
    }

    @Override
    public List<Dome> findAll() {
        return domeDaoMapper.findAll();
    }

    @Override
    public void insertDome(Dome dome) {
        domeDaoMapper.insertDome(dome);
    }

    /**
     * 分页查询，使用pageHelper隔离sql的变化
     *
     * @param pageRequest
     * @return
     */
    @Override
    public PageInfo<Dome> findAllPage(PageRequest pageRequest) {
        int pageNum = pageRequest.getPageNum() == null ? 1 : pageRequest.getPageNum();
        int pageSize = pageRequest.getPageSize() == null ? 10 : pageRequest.getPageSize();
        PageHelper.startPage(pageNum, pageSize);
        List<Dome> domes = domeDaoMapper.findAll();
        return new PageInfo<Dome>(domes);
    }

    // 测试物理分页，手动计算pagenum，pagesize
    public void findAllPage1() {
        int pageNum = 2;
        int pageSize = 2;
        List<Dome> page1 = domeDaoMapper.findPage1(pageNum, pageSize);
        System.out.println(page1);
    }

    public void insertTest(int count) {
        // 插入十条数据
        for (int i = 0; i < count; i++) {
            Dome dome = new Dome();
            dome.setName("wangli9" + i);
            dome.setCompanyId("3" + i);
            dome.setCompanyName("qqq" + i);
            dome.setNickName("nike" + i);
            dome.setPassword("123456" + i);
            insertDome(dome);
        }
    }
}

mapper

package com.example.demo.dao;

import com.example.demo.entity.Dome;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @creator wangli66
 * @create-time 9:58 2019/12/27
 **/
public interface DomeDaoMapper {
    void insertDome(@Param("dome")Dome dome);
    Dome findById(@Param("dome")Dome dome);
    List<Dome> findAll();
    List<Dome> findPage1(@Param("pageNum")int pageNum, @Param("pageSize")int pageSize);
}

mapper.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.example.demo.dao.DomeDaoMapper">

	<sql id="base_word">
		id,name,nick_name,company_name,company_id,password,status
	</sql>
    <select id="findById" parameterType="com.example.demo.entity.Dome" resultType="com.example.demo.entity.Dome" >
		select <include refid="base_word"/> from t_user where status = 0 and id = #{dome.id}
	</select>

    <select id="findAll" resultType="com.example.demo.entity.Dome" >
		select <include refid="base_word"/> from t_user where status = 0
	</select>
	<insert id="insertDome" parameterType="com.example.demo.entity.Dome">
		insert into t_user(name,nick_name,company_name,company_id,password,status)values (
		#{dome.name,jdbcType=VARCHAR},
		#{dome.nickName,jdbcType=VARCHAR},
		#{dome.companyName,jdbcType=VARCHAR},
		#{dome.companyId,jdbcType=VARCHAR},
		#{dome.password,jdbcType=VARCHAR},
		0
		)
	</insert>
	<select id="findPage1" resultType="com.example.demo.entity.Dome">
		select * from t_user limit #{pageNum} offset #{pageSize}
	</select>
</mapper>

entity

package com.example.demo.entity;

import lombok.Data;

/**
 * @creator wangli66
 * @create-time 9:59 2019/12/27
 **/
@Data
public class Dome {
    private Integer id;
    private String name;
    private String nickName;
    private String companyName;
    private String companyId;
    private String password;
    private Integer status;
}

包结构