Hibernate使用PostgreSQL序列生成主键。
第一步：数据库创建表、序列
CREATE TABLE users ( id int, firstname text ) ;
CREATE SEQUENCE SEQ_USER_ID;
第二步：实体类配置
@Entity
@Table(name = "users")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_gen")
@SequenceGenerator(name = "seq_gen", sequenceName = "seq_user_id", allocationSize = 1)
private Long id;
private String firstname;
}
第三步：测试
@Test
public void save(){
User u = new User();
u.setFirstname("name");
userRepository.save(u);
}
注意：使用@SequenceGenerator时，由于allocationSize值默认是50，如果序列INCREMENT属性值为1（不设置默认是1），则需要设置allocationSize属性为1；或者设置序列属性INCREMENT的值为50。
CREATE TABLE users ( id serial, firstname text, lastname text );
1、 Mybatis返回postgresql自增主键值
第一步：java对象
public class Users {
private int id;
private String firstname;
}
第二步：Mybatis xml
<insert id="insert_user_return_id" parameterType="Users" useGeneratedKeys="true" keyProperty="id">
INSERT INTO users (firstname, lastname) VALUES (#{firstname},#{lastname})
</insert>
第三步：测试
@Test
public void f2() {
Users u = new Users();
u.setFirstname("firstName");
u.setLastname("lastName");
userDao.insert_user_return_id(u);
System.out.println("key>>>>>>:" +u.getId());
}
注意：这里获取自增的主键值是通过传输的对象获取；建表时自增字段必须是第一个字段。
2、 使用returning返回数据
INSERT:
第一步：Mybatis接口中使用@Select注解
@Select("INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING * ")
Users insert_user_return_all();
第二步：测试
@Test
public void f1() {
Users user = userDao.insert_user_return_all();
System.out.println( "user id: "+user.getId() +", firstname: "+user.get
1、按月产生连续数据
highgo=# SELECT to_char( T, 'yyyy-mm' ) AS TIME FROM generate_series(to_timestamp('2021-01', 'yyyy-mm'), to_timestamp('2021-10', 'yyyy-mm'), '1 months') AS T;
time
---------
2020-01
2020-02
2020-03
2020-04
2、按天产生连续数据
highgo=# SELECT to_char( T, 'yyyy-mm-dd' ) AS TIME FROM generate_series(to_timestamp('2021-01-01', 'yyyy-mm-dd'), to_timestamp('2021-01-03', 'yyyy-mm-dd'), '1 days') AS T;
time
------------
2020-01-01
2020-01-02
2020-01-03
3、按小时产生连续数据
highgo=# SELECT to_char( t, 'yyyy-mm-dd hh24:mi:ss' ) AS TIME FROM generate_series ( to_timestamp( '2021-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), to_timestamp( '2021-01-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), '1 hours' ) AS t;
time
---------------------
2020-01-01 00:00:00
2020-01-01 01:00:00
. . .
2020-01-01 06:00:00
CREATE OR REPLACE FUNCTION db2_timestampdiff(i_type int, tp1 timestamp with time zone, tp2 timestamp with time zone)
RETURNS numeric
LANGUAGE plpgsql
AS $function$
DECLARE
l_result number;
t_result number;
begin
l_result:=null;
--秒
if ( i_type = 2 ) then
select floor(extract(epoch from (tp1-tp2))) into t_result;
l_result := t_result;
end if;
--分钟
if (i_type = 4) then
select floor(extract(epoch from (tp1-tp2))/60) into t_result;
l_result := t_result;
end if;
--小时
if (i_type = 8) then
select floor(extract(epoch from (tp1-tp2))/60/60) into t_result;
l_result := t_result;
end if;
--天
if (i_type = 16) then
select floor(extract(day from (tp1-tp2))) into t_result;
l_result := t_result;
END IF;
--周
if (i_type = 32) then
select floor(extract(WEEKDAY from (tp1-tp2))) into t_resu
创建函数返回游标：
CREATE or REPLACE FUNCTION fun_cursor( in v_p1 int,in v_p2 int, refcursor, refcursor)
RETURNS SETOF refcursor AS $$
BEGIN
OPEN $3 FOR SELECT * FROM users where u_type = v_p1;
RETURN NEXT $3;
OPEN $4 FOR SELECT * FROM users where u_type = v_p2;
RETURN NEXT $4;
END;
$$ LANGUAGE plpgsql;
客户端调用：
BEGIN;
SELECT * FROM fun_cursor(1,2,'a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
MySQL函数功能：
按照关键字截取字符串，substring_index（被截取字段，关键字，关键字出现的次数）
PG建兼容函数：
create or replace function substring_index(text,text,int)
returns text as $$
declare
a text;
b text :='';
var int;
begin
for var in 1..$3 loop
a=split_part($1,$2,var);
b=b || $2 || a;
end loop;
return substring(b from 2 for length(b));
end
$$ language plpgsql
例子：
MySQL：
mysql> select substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3);
+--------------------------------------------------------------+
| substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3) |
+--------------------------------------------------------------+
| fhafjkdalsfhiad,fjadsoj;f,ad |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
PG：
postgres=# select substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3);
substring_index
------------------------------
fhafjkdalsfhiad,fjadsoj;f,ad
(1 行记录)
mysqlcompat-0.0.7是网上开源的postgresql兼容包；
以下是写sql兼容的方式，兼容mysql函数，此种方式效率会是瓶颈；
因此尽量改成postgresql原生的函数或者底层源码兼容；
1 -- BIT_XOR
-- Note: only works for integers and bigints
CREATE OR REPLACE FUNCTION _bit_xor(bigint, bigint)
RETURNS bigint AS $$
SELECT $1 # COALESCE($2, 0)
$$ IMMUTABLE LANGUAGE SQL;
CREATE AGGREGATE bit_xor (
BASETYPE = bigint,
SFUNC = _bit_xor,
STYPE = bigint,
INITCOND = 0
);
2 -- GROUP_CONCAT()
-- Note: only supports the comma separator
-- Note: For DISTINCT and ORDER BY a subquery is required
CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
SELECT CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
END
$$ IMMUTABLE LANGUAGE SQL;
CREATE AGGREGATE group_concat (
BASETYPE = text,
SFUNC = _group_concat,
STYPE = text
);
mysqlcompat-0.0.7是网上开源的postgresql兼容包；
以下是写sql兼容的方式，兼容mysql函数，此种方式效率会是瓶颈；
因此尽量改成postgresql原生的函数或者底层源码兼容；
1 -- BIT_COUNT()
CREATE OR REPLACE FUNCTION bit_count(bigint)
RETURNS integer AS $$
SELECT pg_catalog.length(pg_catalog.replace(pg_catalog.ltrim(pg_catalog.textin(pg_catalog.bit_out($1::bit(64))), '0'), '0', ''));
$$ IMMUTABLE STRICT LANGUAGE SQL;
mysqlcompat-0.0.7是网上开源的postgresql兼容包；
以下是写sql兼容的方式，兼容mysql函数，此种方式效率会是瓶颈；
因此尽量改成postgresql原生的函数或者底层源码兼容；
1 -- TIME TO INTEGER
CREATE OR REPLACE FUNCTION _time_to_integer(time with time zone)
RETURNS integer AS $$
SELECT
EXTRACT(HOUR FROM $1)::integer * 10000
+ EXTRACT(MINUTE FROM $1)::integer * 100
+ EXTRACT(SECONDS FROM $1)::integer
$$ IMMUTABLE STRICT LANGUAGE SQL;
CREATE CAST (time with time zone AS integer)
WITH FUNCTION _time_to_integer (time with time zone)
AS IMPLICIT;
CREATE OR REPLACE FUNCTION _time_to_integer(time without time zone)
RETURNS integer AS $$
SELECT
EXTRACT(HOUR FROM $1)::integer * 10000
+ EXTRACT(MINUTE FROM $1)::integer * 100
+ EXTRACT(SECONDS FROM $1)::integer
$$ IMMUTABLE STRICT LANGUAGE SQL;
CREATE CAST (time without time zone AS integer)
WITH FUNCTION _time_to_integer (time without time zone)
AS IMPLICIT;
2 -- DATE TO INTEGER
-- Note: requires explicit casts in some cases
CREATE OR REPLACE FUNCTION _date_to_integer(date)
RETURNS integer AS $$
SELE
mysqlcompat-0.0.7是网上开源的postgresql兼容包；
以下是写sql兼容的方式，兼容mysql函数，此种方式效率会是瓶颈；
因此尽量改成postgresql原生的函数或者底层源码兼容；
1 -- IF
-- Warning: still requires casts in some instances
CREATE OR REPLACE FUNCTION if(boolean, anyelement, anyelement)
RETURNS anyelement AS '
SELECT CASE WHEN $1 THEN $2 ELSE $3 END
' IMMUTABLE LANGUAGE SQL;
2 -- IFNULL
-- Warning: still requires casts in some instances
CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement)
RETURNS anyelement AS '
SELECT COALESCE($1, $2)
' IMMUTABLE LANGUAGE SQL;