Ivan

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.getFirstname() +", lastname: "+user.getLastname());

    }

UPDATE:

第一步：Mybatis接口中使用@Select注解

    @Select("UPDATE users SET firstname = 'firstname', lastname = 'lastname' WHERE id = 2 RETURNING * ")

    Users update_user_return_all();

第二步：测试

    @Test

    public void f3() {

        Users user = userDao.update_user_return_all();

        System.out.println("user id: "+user.getId() +", firstname:"+user.getFirstname() +", lastname: "+user.getLastname() );

    }

3、 调用函数，返回数据

第一步：数据库创建函数

    CREATE OR REPLACE FUNCTION fun_return(v_param varchar)

    RETURNS varchar

    AS $$

        BEGIN

            return v_param||'-'||v_param;

        END;

    $$ LANGUAGE plpgsql;

第二步：Mybatis xml

    <select id="fun_return" parameterType="String" resultType="String" statementType="CALLABLE">

        {call fun_return( #{v_param,mode=IN} )}

    </select>

第三步：测试

    @Test

    public void fun_return() {

        String str = userDao.fun_return("20210101");

        System.out.println("str: " + str );

    }

plpgsql语言中，FUNCTION RETURNS属性无返回值时，使用void；有返回值时，填写返回值的数据类型；FUNCTION中调用无返回值的FUNCTION使用PERFORM，如果使用PERFORM调用有返回值的FUNCTION，则会抛弃其返回结果。

4、 返回多个游标

第一步：数据库操作

    CREATE TABLE users ( id serial, firstname text, lastname text, u_type int4 );

    INSERT INTO users (firstname, lastname, u_type) VALUES ('1-first', 'lastName', 1);

    INSERT INTO users (firstname, lastname, u_type) VALUES ('2-Joe', 'Cool', 2);

    CREATE OR REPLACE FUNCTION fun_cursor(IN v_p1 int, IN v_p2 int, OUT cursor_1 refcursor, OUT cursor_2 refcursor)

    RETURNS record AS $$

    BEGIN

        OPEN $3 FOR SELECT * FROM users where u_type = v_p1;

        OPEN $4 FOR SELECT * FROM users where u_type = v_p2;

    END;  $$

    LANGUAGE plpgsql;

第二步：Mybatis xml

    <resultMap type="Users" id="cursorMap">

        <result property="id" column="id" jdbcType="INTEGER"/>

        <result property="firstname" column="firstname" jdbcType="VARCHAR"/>

    <result property="lastname" column="lastname" jdbcType="VARCHAR"/>

    <result property="u_type" column="u_type" jdbcType="INTEGER"/>

    </resultMap>   

    <select id ="fun_cursor" parameterType= "map" statementType="CALLABLE" resultMap="cursorMap" resultType="Map"> 

        {call fun_cursor( #{type1, jdbcType=INTEGER, mode=IN},

                          #{type2, jdbcType=INTEGER, mode=IN},

                          #{cursor_1, mode=OUT,jdbcType=OTHER,javaType=ResultSet, resultMap=cursorMap},

                          #{cursor_2, mode=OUT,jdbcType=OTHER,javaType=ResultSet, resultMap=cursorMap}

                        )

        }

    </select >

第三步：Mapper接口定义

    @Transactional

    Map<String, Object> fun_cursor(Map<String, Object> map);

第四步：测试

    @Test

    public void fun_cursor() {

        Map<String,Object> map = new HashMap<String,Object>();

        map.put("type1", 1);

        map.put("type2", 2);

        userDao.fun_cursor(map);

        List<Users> list1 = (List)map.get("cursor_1");

        for(Users u : list1) {

            System.out.println("user id: "+u.getId()+ ", firstname: "+u.getFirstname()+ ", lastname: "+u.getLastname()+ ", u_type: "+u.getU_type());

        }

        List<Users> list2 = (List)map.get("cursor_2");

        for(Users u : list2) {

            System.out.println("user id: "+u.getId()+ ", firstname: "+u.getFirstname()+ ", lastname: "+u.getLastname()+ ", u_type: "+u.getU_type());

        }

    }

5、 调用存储过程，返回游标

第一步：数据库操作

    CREATE OR REPLACE PROCEDURE pro_one_refcursor(p1 integer, INOUT out_cur refcursor)

    LANGUAGE plpgsql

    AS $procedure$

    BEGIN

        if p1 = 1 THEN

            open out_cur for select * from t_user LIMIT 10;

        else   

            open out_cur for select name,age from t_user LIMIT 10;

        end if;

        EXCEPTION when OTHERS then

            raise exception 'sql exception--%',SQLERROR;

    END

    $procedure$;

第二步：JDBC URL配置escapeSyntaxCallMode参数

    jdbc.url=jdbc:postgresql://192.168.17.18:5432/postgres?escapeSyntaxCallMode=call

第三步：Mybatis xml

    <resultMap type="com.highgo.entity.User" id="cursorMap">

        <id property="id" column="id"/>

        <result property="name" column="name"/>

        <result property="age" column="age"/>

        <result property="bornDate" column="born_date"/>

        <result property="headImg" column="head_img"/>

    </resultMap>

    <select id="getOneCursorProcedure" parameterType="map" statementType="CALLABLE" resultType="Map">

        {

           #{result,mode=OUT, jdbcType=OTHER, javaType=ResultSet, resultMap=cursorMap}

             = call pro_one_refcursor(#{id,mode=IN,jdbcType=INTEGER},null)

        }

    </select>

第四步：Mapper接口定义

    @Transactional

    void getOneCursorProcedure(Map<String,Object> paramMap);

第五步：测试

    @Test

    public void pro_one_refcursor() {

        Map<String,Object> paramMap = new HashMap<>();

        paramMap.put("id",1);

        userMapper.getOneCursorProcedure(paramMap);

        List<User> list = (List<User>)paramMap.get("result");

        if(null != list && list.size() > 0){

            for (User u : list) {

                System.out.println(u.getId()+"--"+u.getName());

            }

        }

    }