SpringBoot+jpa/mybatis+postgrepsql 实现 Postgresql数据查询_mybatis jpa dialect postgresql 配置

1.加入jar包

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
 
<!--postgrepsql依赖-->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

2’配置application.yml文件

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/gupaodb
    username: root
    password: 123456
    driverClassName: org.postgresql.Driver
    jpa:
      show_sql: true
      hibernate:
        ddl-auto: none  # none: 关闭hibernate的自动创建表结构的机制 DDL 级别 (create, create-drop, validate, update)
      properties:
        hibernate:
          dialect: org.hibernate.dialect.PostgreSQLDialect
          format_sql: true
          temp:
            # 兼容SpringBoot2.X, 关闭 Hibernate尝试验证PostgreSQL的CLOB特性
            use_jdbc_metadata_defaults: false

3、创建entity类

package com.gupao.springkafkademo.entity;

import javax.persistence.*;

@Entity(name = "Company")//名字
@Table(name = "Company")//对应数据库表中名字
public class Company {

    @Id//数据库中主键标识
    @GeneratedValue//自增标识
    private int id;

    @Column(name = "name")//name属性为表的字段别名
    private String name;

    @Column(name = "age")
    private int age;

    @Column(name = "address")
    private String address;

    @Column(name = "salary")
    private float  salary;

    public Company() {
    }

    public Company(int id, String name, int age, String address, float salary) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.address = address;
        this.salary = salary;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public float getSalary() {
        return salary;
    }

    public void setSalary(float salary) {
        this.salary = salary;
    }
}

 

 

4、创建Respository

package com.gupao.springkafkademo.repository;

import com.gupao.springkafkademo.entity.Company;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;

@Repository
public interface CompanyRepository
        extends PagingAndSortingRepository<Company, Integer>
{


    @Query(value = "select * from company",nativeQuery = true)
    List<Company> GetAllCompanyData();

    //根据id查询用户
    @Query(value = "SELECT * FROM company WHERE id=?",
            nativeQuery = true)
    Company findCompanyById(int id);

    //根据name查询用户
    @Query(value = "SELECT * FROM company WHERE name=?",
            nativeQuery = true)
    public List<Company> getCompanyByName(String name);

    //修改用户
    @Query(value = "UPDATE company  SET address=:#{#company.address} WHERE id=:#{#company.id}"
            , nativeQuery = true)
    @Modifying
    @Transactional
    int updateCompanyById(Company company);

    //删除用户
    @Modifying
    @Transactional
    @Query(value = "delete from company where id=?", nativeQuery =
            true)
    int deleteCompanyById(int id);

    //增加用户
    @Query(value = "insert into company(name,address)" +
            " values(:#{#company.name},:#{#company.address})",
            nativeQuery =
                    true)
    int addCompany(Company company);

    //分页查询
    @Query(value = "select id,name,address from " +
            "company limit  ?1  offset  ?2", nativeQuery = true)
    List<Company> getPage(int pageSize, int pageNumber);

    //获取记录总数
    @Query(value = "SELECT \"count\"(*) from company",
            nativeQuery = true)
    int getAccount();
}

@Service
public class CompanyService {

    @Autowired
    private CompanyRepository companyRepository;

    public List<Company> GetAllCompanyData(){
        return companyRepository.GetAllCompanyData();
    }

}

5、我所遇到的坑，一直报这个错，虽然不影响使用，但是膈应人，难受

PgSQL-JDBC:"org.postgresql.jdbc.PgConnection.createClob() 方法尚未被实作"

网上搜索设置方法，全都不好使，

#    jpa:
#      properties:
#        hibernate:
#          temp:
#            # 兼容SpringBoot2.X, 关闭 Hibernate尝试验证PostgreSQL的CLOB特性
#            use_jdbc_metadata_defaults: false
#          jdbc:
#            lob:
#              non_contextual_creation: true
 

改投 springboot+mybatis+postgresql

1、加jar包

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.1.1</version>
</dependency>

<!--postgrepsql依赖-->
<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.11</version>
    <scope>runtime</scope>
</dependency>

 

2、配置yml

spring:
  datasource:
    platform: postgres
    url: jdbc:postgresql://127.0.0.1:5432/gupaodb
    username: postgres
    password: 123456
    driverClassName: org.postgresql.Driver

mybatis: # 坑死我点之一 mybatis是跟spring一个级别的         
  mapper-locations: classpath:mapper/*Mapper.xml  # 坑死我点之二 Mapper.xml 必须在classpath之下，不想讲理论，
  type-aliases-package: com.gupao.springkafkademo.entity  # 坑死我点之三 这是实体entity对应的文件夹

#坑死我点四：

* mapper.java文件，要用@Mapper注解，不需要在在启动时候写加入包扫描了，用@Resposity的，要扫描

3 、实体

package com.gupao.springkafkademo.entity;

import java.io.Serializable;

public class Employee implements Serializable {

    private int id;

    private String name;

    private int age;

    public Employee() {
    }

    public Employee(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

4、javamapper

package com.gupao.springkafkademo.mapper;

import com.gupao.springkafkademo.entity.Employee;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;


@Mapper
public interface EmployeeMapper{

    public List<Employee> findALL();

    public int getSize();

    public int insertEmployee(Employee employee);

    public int updateEmployee(Employee employee);

    public Employee queryById(int id);
}

5、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.gupao.springkafkademo.mapper.EmployeeMapper">
<!--    <resultMap id="authorMap" type="com.gupao.springkafkademo.entity.Employee">-->
<!--        <id column="id" property="id" jdbcType="INTEGER" />-->
<!--        <result column="name" property="name" jdbcType="VARCHAR" />-->
<!--        <result column="age" property="age" jdbcType="INTEGER" />-->
<!--    </resultMap>-->

    <sql id="base_column">
        id,name,age
    </sql>
    
    <select id="findALL" resultType="Employee">
        select id,name,age from employee
    </select>

    <select id="getSize" resultType="integer">
        select count(*) from employee
    </select>

    <select id="queryById" resultType="Employee" parameterType="integer">
        select id,name,age from employee where id = #{id}
    </select>
    
    <update id="updateEmployee" parameterType="Employee">
        update employee set name = #{name} where id = #{id}
    </update>

    <insert id="insertEmployee" parameterType="Employee">
        INSERT INTO
        employee(id,name,age)
        VALUES
        (#{id},#{name},#{age})
    </insert>

</mapper>

烦死，破玩意，看了简单，一堆堆细节地方