Springboot + mybatis + postgreSQL

1. 配置: application.properties

spring.application.name=postgres server.port=8787 spring.jpa.hibernate.ddl-auto=update #spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.jpa.show-sql=true ds.blue.jdbcUrl=jdbc:postgresql://localhost:5432/blue?stringtype=unspecified ds.blue.username=postgres ds.blue.password=admin ds.blue.driverClassName=org.postgresql.Driver mybatis.type-aliases-package=pojo

pom.xml

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>3.0.6</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>demo-sql</artifactId> <version>0.0.1-SNAPSHOT</version> <name>postgreSimple</name> <description>Demo project for Spring Boot</description> <properties> <java.version>17</java.version> </properties> <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>3.0.0</version> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>2.0.31</version> </dependency> <!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind --> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-core</artifactId> </dependency> <!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-core --> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> </dependency> <!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-annotations --> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-annotations</artifactId> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>

2. app controller

package com.example.demo; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.annotation.Import; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.bind.annotation.RestController; import cfg.DataSrcConfig; import mapper.BlogMapper; import pojo.Blog; @RestController @MapperScan(basePackages = { "mapper", "pojo" }) @Import(DataSrcConfig.class) @SpringBootApplication public class PostgreSimpleApplication {     @Autowired     private BlogMapper mapper;     public static void main(String[] args) {         SpringApplication.run(PostgreSimpleApplication.class, args);     }     @PostMapping("/find/{id}")     public @ResponseBody Blog find(@PathVariable int id) {         Blog blog = mapper.selectBlog(id);         System.out.println(blog.getContent());         System.out.println(blog.getDetails());         return blog;     }     @PostMapping("/update/{id}")     public @ResponseBody Blog update(@RequestBody Blog blog) {          mapper.updateBlog(blog);          System.out.println(blog.getContent());         return blog;     }     @PostMapping("/add")     public String add(@RequestBody Blog blog) {             mapper.insertBlog(blog);         System.out.println(blog.getContent());         return blog.getContent();     } }

4. db config

package cfg; import javax.sql.DataSource; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; @Configuration public class DataSrcConfig {     @Bean     @ConfigurationProperties(prefix = "ds.blue")     public DataSource getDruid() {         return DataSourceBuilder.create().build();     } }

5. handler:ArrayHandler

package handler; import java.sql.Array; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Arrays; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.MappedTypes; import com.alibaba.fastjson.JSONArray; @MappedTypes(Object.class) public class ArrayHandler extends BaseTypeHandler { @Override public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException { String arrStr = JSONArray.toJSONString(parameter); String[] arr=new String[]{"dragonfly","butterfly","bird","eagle","swallio"}; Array sqlArr=ps.getConnection().createArrayOf("varchar", arr); ps.setArray(i, sqlArr); } @Override public Object getNullableResult(ResultSet rs, String columnName) throws SQLException { Array sqlArr = rs.getArray(columnName); if (sqlArr == null) return null; //{dragonfly,butterfly,bird,eagle,swallio} String[] strArr=(String[])sqlArr.getArray(); return strArr; } @Override public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException { Array sqlArr = rs.getArray(columnIndex); if (sqlArr == null) return null; //{dragonfly,butterfly,bird,eagle,swallio} String[] strArr=(String[])sqlArr.getArray(); return strArr; } @Override public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { Array sqlArr = cs.getArray(columnIndex); if (sqlArr == null) return null; //{dragonfly,butterfly,bird,eagle,swallio} String[] strArr=(String[])sqlArr.getArray(); return strArr; } }

6. handler:JsonHandler

package handler; import java.sql.CallableStatement; import java.sql.JDBCType; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import com.alibaba.fastjson.JSON; import pojo.PGTree; public class JsonHandler extends BaseTypeHandler {     @Override     public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType)             throws SQLException {                  Object objectJson = JSON.toJSONString(parameter);         ps.setObject(i, objectJson);         }     @Override     public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {         String ret=rs.getString(columnName);           return JSON.parseObject(ret, Object.class);                }     @Override     public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {         String ret=rs.getString(columnIndex);           return JSON.parseObject(ret, Object.class);     }     @Override     public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {         String ret=cs.getString(columnIndex);           return JSON.parseObject(ret, Object.class);     } }

7. repository:SQLMapper

package mapper; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.ResultType; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.SelectProvider; import org.apache.ibatis.jdbc.SQL; import org.apache.ibatis.type.JdbcType; import org.springframework.stereotype.Repository; import handler.ArrayHandler; import pojo.Blog; @Repository @Mapper public interface BlogMapper { // JDBCType.ARRAY // @Select("SELECT * FROM blog WHERE id = #{id}") public Blog selectBlog(int i); @SelectProvider(type = BlogSQLProvider.class, method = "selectBlogSql") public Blog selectBlogDy(int i); @SelectProvider(type = BlogSQLProvider.class, method = "selectBlogSqlPage") @ResultType(Blog.class) @Results(id = "blogList", value = { @Result(property = "id", column = "id"), //@Result(property = "pgtree", column = "pgtree", jdbcType = JdbcType.VARCHAR, typeHandler = JsonHandler.class) @Result(property = "categories", column = "categories", jdbcType = JdbcType.ARRAY, typeHandler = ArrayHandler.class) }) public List<Blog> selectBlogPage(int startrow, int size); @SelectProvider(type = BlogSQLProvider.class, method = "insertBlogSql") public void insertBlog(Blog blog); @SelectProvider(type = BlogSQLProvider.class, method = "updateBlogSql") public void updateBlog(Blog log); @SelectProvider(type = BlogSQLProvider.class, method = "updateBlogSql") public void updateBlogPara(Map<String, Object> hashMap); @Select("select * from blog order by id limit #{pageSize} OFFSET #{offset}") public List<Blog> selectBylimit(@Param("offset") int offset, @Param("pageSize") int pageSize); public class BlogSQLProvider { public String updateBlogSql() { return new SQL().UPDATE("Blog").SET("pgtree = #{pgtree,jdbcType = OTHER, typeHandler=handler.JsonHandler}") .SET("details = #{details}").SET("title = #{title}").SET("content = #{content}") .SET("categories = #{categories,jdbcType=ARRAY, typeHandler=handler.ArrayHandler}") .WHERE("ID = #{id}").toString(); } public String selectBlogSql() { return new SQL().SELECT("id,author, title, content,details,pgtree, categories").FROM("Blog") .WHERE("id = #{id}").toString(); } public String selectBlogSqlPage() { return new SQL().SELECT("id,author, title, content,details,pgtree, categories").FROM("Blog") .OFFSET("#{startrow}").LIMIT("#{size}").toString(); } public String insertBlogSql() { return new SQL().INSERT_INTO("Blog").INTO_COLUMNS("id,author", "title") .VALUES("content,details,pgtree,categories", "#{id},#{author},#{title},#{content},#{details}," + "#{pgtree,jdbcType = OTHER, typeHandler=handler.JsonHandler}," + "#{categories,jdbcType=ARRAY, typeHandler=handler.ArrayHandler}") .toString(); } } }

8.pojo: Blog

package pojo; public class Blog { private int id; private String title; private String author; private String content; private String details; private PGTree pgtree; private String[] categories; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getAuthor() { return author; } public void setAuthor(String author) { this.author = author; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public String getDetails() { return details; } public void setDetails(String details) { this.details = details; } public PGTree getPgtree() { return this.pgtree; } public void setPgtree(PGTree pgTree) { this.pgtree = pgTree; } public String[] getCategories() { return categories; } public void setCategories(String[] categories) { this.categories = categories; } }

8.pojo: PGTree

package pojo; import com.alibaba.fastjson.JSON; public class PGTree { String name; String color; public String getName() { return name; } public void setName(String name) { this.name = name; } public String getColor() { return color; } public void setColor(String color) { this.color = color; } public static PGTree fromJsonString(String treeStr) { PGTree pt = JSON.parseObject(treeStr, PGTree.class); return pt; } public String toJsonString() { String stringJSON = JSON.toJSONString(this); return stringJSON; } }

9.PostMan request Body

{"id":"4","title":"I'm butterfly","author":"flybird","content":"this is a beautiful story", "details":"{\"name\":\"grass\",\"hobby\":\"drink\"}", "pgtree":{"name":"leaf","color":"green"}, "categories":["hobby","technology","views"]} {     "id": 0,     "title": "good luck",     "author": "Jane",     "content": "This is for Jane!",     "details": "{\"entity\":\"tree\",\"color\":\"green\",\"location\":\"north\"}",     "pgtree":"{\"name\":\"tree\",\"color\":\"green\"}",     "categories": "['dragonfly','butterfly','bird','eagle','swallio']"