在pgsql中带有操作符@>可实现其左边的包含右边的对象。在日常开发中经常使用到,本文主要讲,当字段为VARCHAR类型,数据格式为以','分隔形式,如何利用@>操作符在mybatis中实现该功能。
定义 课程表 tbl_class,定义学生表tbl_student
<-- 课程表-->
CREATE TABLE public.tbl_class (
id serial NOT NULL,
class_code varchar NOT NULL,
class_name varchar NOT NULL,
CONSTRAINT tbl_class_pk PRIMARY KEY (id)
);
-- Column comments
COMMENT ON COLUMN public.tbl_class.id IS '主键';
COMMENT ON COLUMN public.tbl_class.class_code IS '课程代号';
COMMENT ON COLUMN public.tbl_class.class_name IS '课程名称';
<--初始化数据-->
INSERT INTO public.tbl_class
(id, class_code, class_name)
VALUES(1, '0001', '语文');
INSERT INTO public.tbl_class
(id, class_code, class_name)
VALUES(2, '0002', '数学');
INSERT INTO public.tbl_class
(id, class_code, class_name)
VALUES(3, '003', '外语');
INSERT INTO public.tbl_class
(id, class_code, class_name)
VALUES(4, '004', '物理');
INSERT INTO public.tbl_class
(id, class_code, class_name)
VALUES(5, '005', '化学');
<-- 学生表-->
CREATE TABLE public.tbl_student (
id serial NOT NULL,
"name" varchar NOT NULL,
student_class varchar NULL,
code varchar NULL,
CONSTRAINT tbl_student_pk PRIMARY KEY (id)
);
-- Column comments
COMMENT ON COLUMN public.tbl_student.id IS '主键';
COMMENT ON COLUMN public.tbl_student."name" IS '学生姓名';
COMMENT ON COLUMN public.tbl_student.student_class IS '学生所选的课程代号';
COMMENT ON COLUMN public.tbl_student.code IS '学号';
INSERT INTO public.tbl_student
(id, "name", student_class, code)
VALUES(1, '小刘', '001,002,004', 'x001');
INSERT INTO public.tbl_student
(id, "name", student_class, code)
VALUES(2, '小毛', '002,003', 'm002');
INSERT INTO public.tbl_student
(id, "name", student_class, code)
VALUES(3, '小孙', '001,002,003,004,005', 's003');
INSERT INTO public.tbl_student
(id, "name", student_class, code)
VALUES(4, '小张', '001,005', 'z004');
INSERT INTO public.tbl_student
(id, "name", student_class, code)
VALUES(5, '小闫', '004,005', 'y005');
即在学生表(tbl_student)字段student_class中,表示为每个学生所学的课程,以','(逗号)形式隔开。
现在需求为,筛选出哪些人选择了语文课(代号是001)
利用pgsql的@>如下:
select * from tbl_student ts where string_to_array(student_class,',')@>array['001']
结果如下:
可以看出@>可以筛选出左边包含右边的数据。
下面介绍在mybatis中的使用,在mybatis中,mybatis将string_to_array(student_class,',')识别为一text[]类型,故需在最后将其强转为text[]即可。
select
*
from
tbl_student ts
<where>
string_to_array(student_class, ',')<![CDATA[@>]]>array[#{classCode,jdbcType=VARCHAR }]::text[]
</where>
即:
<select id ="getStudentByClass" resultMap="BaseMap">
select
*
from
tbl_student ts
<where>
string_to_array(student_class, ',')<![CDATA[@>]]>array[#{classCode,jdbcType=VARCHAR }]::text[]
</where>
</select>
其中@>操作符需要在mybatis中需要使用<![CDATA[@>]]>来修饰以免不能被mybatis识别。
以上就是PostgreSql整合mybatis实现@>包含功能的说明