一.相关准备
1. 数据库表准备
CREATE TABLE "public"."bank_discount_config" (
"id" int4 NOT NULL DEFAULT nextval('bank_discount_config_id_seq'::regclass),
"name" varchar(255) COLLATE "pg_catalog"."default",
"bank_id" int4,
"model_type" varchar(255) COLLATE "pg_catalog"."default",
"discount_code" varchar(255) COLLATE "pg_catalog"."default",
"discount_amount" varchar(255) COLLATE "pg_catalog"."default",
"periods" int4,
"discount_rate" numeric(5,2),
"status" varchar(255) COLLATE "pg_catalog"."default",
"loan_amount" numeric(10,2),
"commission_fee_rate" numeric(5,2),
"discount_type" int2,
"create_time" date,
"create_user" int2,
"is_delete" char(1) COLLATE "pg_catalog"."default",
"info" json,
CONSTRAINT "bank_discount_config_pkey1" PRIMARY KEY ("id")
)
;
ALTER TABLE "public"."bank_discount_config"
OWNER TO "postgres";
2. 项目结构
3. 引入依赖
<!--引入mybatis-springboot-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<!--引入数据源-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
<!--引入springboot测试-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--引入postgreSql-->
<!--引入lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.14</version>
</dependency>
<dependency>
<groupId>org.json4s</groupId>
<artifactId>json4s-core_2.11</artifactId>
<version>3.5.3</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.13</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.71</version>
<scope>compile</scope>
</dependency>
</dependencies>
4.引入配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://localhost:5432/runoobdb
username: postgres
password: root
jpa:
hibernate:
ddl-auto: update
dialect: org.hibernate.dialect.PostgreSQL9Dialect
properties:
hibernate:
temp:
use_jdbc_metadata_defaults: false
mybatis:
mapper-locations: classpath:cn/kingcar/mapper/*.xml
type-aliases-package: cn.kingcar.entity
type-handlers-package: cn.kingcar.util.JsonbTypeHandler
5.工具类引入
使用mybatis中的自定义TypeHandler处理PostgreSQL中的Json类型, 参考文章
package cn.kingcar.util;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;
@MappedTypes({ JSONObject.class })
public class JsonbTypeHandler extends BaseTypeHandler<JSONObject> {
//引入PGSQL提供的工具类PGobject
private static final PGobject jsonObject = new PGobject();
@Override
public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {
jsonObject.setType("json");
jsonObject.setValue(parameter.toString());
ps.setObject(i, jsonObject);
}
@Override
public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
String sqlJson = rs.getString(columnName);
if (null != sqlJson){
return JSONObject.parseObject(sqlJson);
}
return null;
}
//根据列索引,获取可以为空的结果
@Override
public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String sqlJson = rs.getString(columnIndex);
if (null != sqlJson){
return JSONObject.parseObject(sqlJson);
}
return null;
}
@Override
public JSONObject getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String sqlJson = cs.getString(columnIndex);
if (null != sqlJson) {
return JSONObject.parseObject(sqlJson);
}
return null;
}
}
二 接口开发
1.查询接口
-
Service接口
/** * 查询所有数据 * @return */ List<BankDiscountConfig> queryAll();
-
Service实现类
@Override public List<BankDiscountConfig> queryAll() { List<BankDiscountConfig> bankDiscountConfigs = bankDao.queryAll(); return bankDiscountConfigs; }
-
DAO接口
/** * 查询所有数据 * @return */ List<BankDiscountConfig> queryAll();
-
Mapper文件
<mapper namespace="cn.kingcar.dao.BankDao"> <resultMap id="BaseResultMap" type="cn.kingcar.entity.BankDiscountConfig"> <id column="id" jdbcType="INTEGER" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> <result column="bank_id" jdbcType="INTEGER" property="bankId" /> <result column="model_type" jdbcType="VARCHAR" property="modelType" /> <result column="discount_code" jdbcType="VARCHAR" property="discountCode" /> <result column="discount_amount" jdbcType="DECIMAL" property="discountAmount" /> <result column="periods" jdbcType="INTEGER" property="periods" /> <result column="discount_rate" jdbcType="DECIMAL" property="discountRate" /> <result column="status" jdbcType="VARCHAR" property="status" /> <result column="loan_amount" jdbcType="DECIMAL" property="loanAmount" /> <result column="commission_fee_rate" jdbcType="DECIMAL" property="commissionFeeRate" /> <result column="discount_type" jdbcType="INTEGER" property="discountType" /> <result column="create_time" jdbcType="TIMESTAMP" property="createTime" /> <result column="create_user" jdbcType="INTEGER" property="createUser" /> <result column="is_delete" jdbcType="CHAR" property="isDelete" /> <result column="info" jdbcType="OTHER" typeHandler="cn.kingcar.util.JsonbTypeHandler" property="info" /> </resultMap> <sql id="BaseSql"> id, name, bank_id, model_type, discount_code, discount_amount, periods, discount_rate, status, loan_amount, commission_fee_rate, discount_type, create_time, create_user, is_delete,info </sql> <select id="queryAll" resultMap="BaseResultMap"> select <include refid="BaseSql"/> from bank_discount_config </select>
2. 删除接口
-
Service接口
/** * 依据主键删除 * @param id */ void deletByKey(Integer id);
-
Service实现类
@Override public void deletByKey(Integer id) { bankDao.deleteByPrimaryKey(id); }
-
DAO接口
/** * 依据主键删除 * @param id */ void deleteByPrimaryKey(Integer id);
-
Mapper文件
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer"> delete from bank_discount_config where id = #{id,jdbcType=INTEGER} </delete>
3. 修改接口
-
Service接口
/** * 更新操作 * @param bankDiscountConfig */ void updateByPrimaryKey(BankDiscountConfig bankDiscountConfig);
-
Service实现类
@Override public void updateByPrimaryKey(BankDiscountConfig bankDiscountConfig) { bankDao.updateByPrimaryKey(bankDiscountConfig); }
-
DAO接口
/** * 更新操作 * @param bankDiscountConfig */ void updateByPrimaryKey(BankDiscountConfig bankDiscountConfig);
-
Mapper文件
<update id="updateByPrimaryKey" parameterType="cn.kingcar.entity.BankDiscountConfig">
update bank_discount_config
<set>
<if test="name != null">
name = #{name,jdbcType=VARCHAR},
</if>
<if test="bankId != null">
bank_id = #{bankId,jdbcType=INTEGER},
</if>
<if test="modelType != null">
model_type = #{modelType,jdbcType=VARCHAR},
</if>
<if test="discountCode != null">
discount_code = #{discountCode,jdbcType=VARCHAR},
</if>
<if test="discountAmount != null">
discount_amount = #{discountAmount,jdbcType=DECIMAL},
</if>
<if test="periods != null">
periods = #{periods,jdbcType=INTEGER},
</if>
<if test="discountRate != null">
discount_rate = #{discountRate,jdbcType=DECIMAL},
</if>
<if test="status != null">
status = #{status,jdbcType=VARCHAR},
</if>
<if test="loanAmount != null">
loan_amount = #{loanAmount,jdbcType=DECIMAL},
</if>
<if test="commissionFeeRate != null">
commission_fee_rate = #{commissionFeeRate,jdbcType=DECIMAL},
</if>
<if test="discountType != null">
discount_type = #{discountType,jdbcType=INTEGER},
</if>
<if test="createTime != null">
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
<if test="createUser != null">
create_user = #{createUser,jdbcType=INTEGER},
</if>
<if test="isDelete != null">
is_delete = #{isDelete,jdbcType=CHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
4. 插入接口
-
Service接口
/** * 新增操作 * @param bankDiscountConfig */ void insertInto(BankDiscountConfig bankDiscountConfig);
-
Service实现类
@Override public void insertInto(BankDiscountConfig bankDiscountConfig) { bankDao.insert(bankDiscountConfig); }
-
DAO接口
/** * 新增操作 * @param bankDiscountConfig */ void insert(BankDiscountConfig bankDiscountConfig);
-
Mapper文件
<insert id="insert" parameterType="cn.kingcar.entity.BankDiscountConfig"> <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer"> SELECT currval('bank_discount_config_id_seq') as id </selectKey> insert into bank_discount_config (name, bank_id, model_type, discount_code, discount_amount, periods, discount_rate, status, loan_amount, commission_fee_rate, discount_type, create_time, create_user, is_delete,info) values (#{name,jdbcType=VARCHAR}, #{bankId,jdbcType=INTEGER}, #{modelType,jdbcType=VARCHAR}, #{discountCode,jdbcType=VARCHAR}, #{discountAmount,jdbcType=DECIMAL}, #{periods,jdbcType=INTEGER}, #{discountRate,jdbcType=DECIMAL}, #{status,jdbcType=VARCHAR}, #{loanAmount,jdbcType=DECIMAL}, #{commissionFeeRate,jdbcType=DECIMAL}, #{discountType,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP}, #{createUser,jdbcType=INTEGER}, #{isDelete,jdbcType=CHAR},#{info,jdbcType=OTHER,typeHandler=cn.kingcar.util.JsonbTypeHandler}) </insert>