配置

引入依赖

MyBatis使用JDBC接口连接瀚高数据库，在数据库安装目录 interfaces/jdbc 下，可以获得。驱动包名称：hgdb-jdbc-x.xx.jar。该驱动包与PostgreSQL保持兼容，其中类名、类结构与 PostgreSQL 驱动完全一致，曾经运行于 PostgreSQL 的应用程序可以直接移植到当前系统使用。

如果是maven构建的项目，可以自行导入本地仓库添加依赖，示例：

#导入本地仓库
mvn install:install-file -Dfile=C:\Users\Lihq\Desktop\hgdb-jdbc-v9.0.jar
                     -DgroupId=com.highgo
                     -DartifactId=highgo-9.0.1-jdbc
                     -Dversion=1.0
                     -Dpackaging=Jar

<!--引入本地仓库依赖-->
<dependency>
  <groupId>org.mybatis</groupId>

  <artifactId>mybatis</artifactId>

  <version>3.5.4</version>

</dependency>

<dependency>
  <groupId>com.highgo</groupId>

  <artifactId>highgo-9.0.1-jdbc</artifactId>

  <version>1.0</version>

</dependency>

配置文件

mybatis-config.xml

<?xml version = "1.0" encoding = "UTF-8"?>

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <typeAliases>
        <typeAlias alias = "Pet" type = "net.pets.model.Pet"/>
    </typeAliases>

    <environments default = "development">
        <environment id = "development">
            <transactionManager type = "JDBC"/>
            <dataSource type = "POOLED">
                <property name = "driver" value = "org.postgresql.Driver"/>
                <property name = "url" value = "jdbc:postgresql://192.168.33.8:5866/highgo"/>
                <property name = "username" value = "highgo"/>
                <property name = "password" value = "highgo"/>
            </dataSource>

        </environment>

    </environments>

    <mappers>
        <mapper resource = "mybatis-mappers/Pet.xml"/>
    </mappers>

</configuration>

mapper.xml

<?xml version = "1.0" encoding = "UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace = "Pet">
    <insert id = "insert" parameterType = "Pet" useGeneratedKeys="true" keyProperty="public.pet.id" keyColumn="id">
        INSERT INTO pet (name, species, owner ) VALUES (#{name}, #{species}, #{owner});
            <selectKey keyProperty = "id" resultType = "int" order = "AFTER">
                SELECT currval(pg_get_serial_sequence('pet','id')) as id
            </selectKey>

    </insert>

    <resultMap id = "result" type = "Pet">
         <result property = "id" column = "id"/>
         <result property = "name" column = "name"/>
         <result property = "species" column = "species"/>
         <result property = "owner" column = "owner"/>
    </resultMap>

    <select id = "getAll" resultMap = "result">
        SELECT * FROM pet ;
    </select>

    <update id = "update" parameterType = "Pet">
        UPDATE pet SET name = #{name}, species = #{species}, owner = #{owner} WHERE id = #{id};
    </update>

    <delete id = "deleteById" parameterType = "int">
        DELETE FROM pet WHERE id = #{id};
    </delete>

</mapper>

数据分页 PageHelper

配置插件

<plugins>
    <!-- 配置 PageHelper 分页插件 -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <!--数据库方言可以不指定，可以从连接传中自动获取-->
        <property name="helperDialect" value="postgresql" />
        <!-- 其他可选配置项 -->
        <property name="reasonable" value="true"/> <!-- 合理化分页 -->
        <property name="supportMethodsArguments" value="true"/> <!-- 支持通过方法参数传递分页参数 -->
    </plugin>

</plugins>

mapper文件

<select id = "getAll" resultMap = "result">
    SELECT * FROM pet
</select>

调用

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import java.util.List;

public List<Pet> getAll(int pageNum, int pageSize) {
    // 设置分页参数
    PageHelper.startPage(pageNum, pageSize); 
    List<Pet> pets = petMapper.getAll();
    PageInfo<Pet> pageInfo = new PageInfo<>(pets);
    return pets;
}

存储过程和函数的调用

--示例存储过程
CREATE OR REPLACE PROCEDURE update_pet_owner(petid INT, new_owner varchar)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE pet
    SET owner = $2
    WHERE id = $1;
END;
$$;

--示例函数
CREATE OR REPLACE FUNCTION get_pet_by_id(petid INT)
RETURNS TABLE(id INT, name varchar, owner varchar) AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.name, u.owner
    FROM pet u
    WHERE u.id = petid;
END;
$$ LANGUAGE plpgsql;

mapper文件

<update id="callUpdatePetOwner"  parameterType="com.example.springbootmybatisdemo.entity.Pet" statementType="CALLABLE">
      { CALL update_pet_owner(
            #{id, mode=IN, jdbcType=INTEGER},
            #{owner, mode=IN, jdbcType=VARCHAR}
       ) }
</update>

        
        
<select id="callGetPetById" resultType="Pet">
     SELECT * FROM get_pet_by_id(#{petId})
</select>

注意：

• 在调用存储过程时，必须设置 statementType="CALLABLE"。
• { CALL procedure_name(...) } 是标准的 JDBC 调用语法。
• jdbc 需要指定 escapeSyntaxCallMode=callIfNoReturn 。
• 如果存储过程有输出参数，需要在 MyBatis 中显式声明 mode=OUT。
• 存储过程使用CALL调用，通常用于执行操作（如更新数据）。
• 函数使用SELECT调用，通常用于返回数据。

另外，也可以结合 <![CDATA[ 标签来使用，此时，不需强制指定 escapeSyntaxCallMode参数。

<!--存储过程调用-->
<update id="callUpdatePetOwner"  parameterType="com.example.springbootmybatisdemo.entity.Pet" statementType="CALLABLE">
    <![CDATA[
        CALL update_pet_owner(
        #{id, mode=IN, jdbcType=INTEGER},
        #{owner, mode=IN, jdbcType=VARCHAR}
        )
    ]]>
</update>

<!--函数调用-->
<select id="callGetPetById" resultType="Pet">
    <![CDATA[
        SELECT * FROM get_pet_by_id(#{petId})
    ]]>
</select>