MyBatis 整合 PostgreSQL 完整搭建指南：從資料庫安裝到 tk.mybatis 通用 Mapper 應用

環境準備與 PostgreSQL 安裝

在 Ubuntu/Debian 系統上安裝 PostgreSQL：

apt-get update && apt-get install postgresql

安裝完成後預設建立系統用戶與資料庫用戶 postgres（密碼同帳號）。圖形化管理工具推薦 pgAdmin，可至官網下載對應版本。

遠端連線配置

修改兩個關鍵設定檔（路徑視版本而異，如 /etc/postgresql/9.5/main/）：

1. postgresql.conf：設定 listen_addresses = '*' 允許所有 IP 連線
2. pg_hba.conf：新增 host all all 0.0.0.0/0 md5 允許遠端密碼驗證

修改完成後重啟服務：systemctl restart postgresql

MyBatis 代碼生成器 (MBG) 整合

Maven 依賴配置

<dependencies>
  <!-- MyBatis 核心 -->
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>${mybatis.version}</version>
  </dependency>
  <!-- Spring 整合 -->
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>${mybatis.spring.version}</version>
  </dependency>
  <!-- 代碼生成器 -->
  <dependency>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-core</artifactId>
    <version>${MBG.version}</version>
  </dependency>
  <!-- 分頁插件 -->
  <dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>${pagehelper.version}</version>
  </dependency>
  <!-- tk.mybatis 通用 Mapper -->
  <dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper</artifactId>
    <version>${mapper.version}</version>
  </dependency>
  <!-- PostgreSQL 驅動 -->
  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.3-1102-jdbc41</version>
  </dependency>
</dependencies>

MBG Maven Plugin 配置

<plugin>
  <groupId>org.mybatis.generator</groupId>
  <artifactId>mybatis-generator-maven-plugin</artifactId>
  <version>${MBG.version}</version>
  <configuration>
    <configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
    <overwrite>true</overwrite>
    <verbose>true</verbose>
  </configuration>
  <dependencies>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>9.3-1102-jdbc41</version>
    </dependency>
    <dependency>
      <groupId>tk.mybatis</groupId>
      <artifactId>mapper</artifactId>
      <version>${mapper.version}</version>
    </dependency>
  </dependencies>
</plugin>

generatorConfig.xml 關鍵設定

<context targetRuntime="MyBatis3Simple" defaultModelType="flat">
  <property name="beginningDelimiter" value="`"/>
  <property name="endingDelimiter" value="`"/>
  <plugin type="${mapper.plugin}">
    <property name="mappers" value="${mapper.Mapper}"/>
  </plugin>
  <jdbcConnection driverClass="${jdbc.driverClass}"
    connectionURL="${jdbc.url}"
    userId="${jdbc.username}"
    password="${jdbc.password}"/>
  <javaModelGenerator targetPackage="${targetModelPackage}" targetProject="${targetJavaProject}"/>
  <sqlMapGenerator targetPackage="mapper" targetProject="${targetResourcesProject}"/>
  <javaClientGenerator targetPackage="${targetMapperPackage}" targetProject="${targetJavaProject}" type="XMLMAPPER"/>
  <table tableName="product" domainObjectName="Product"/>
</context>

關鍵點：targetRuntime="MyBatis3Simple" 生成精簡 Mapper，配合通用 Mapper 使用；若用 MyBatis3 易產生重複 Mapped Statement 錯誤。

Spring 整合配置

<bean id="jimDataSource" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="${jdbc.driverClass}"/>
  <property name="url" value="${jdbc.url}"/>
  <property name="username" value="${jdbc.username}"/>
  <property name="password" value="${jdbc.password}"/>
  <!-- 連線池調優參數 -->
  <property name="initialSize" value="5"/>
  <property name="maxWait" value="60000"/>
  <property name="validationQuery" value="SELECT 1"/>
  <property name="testWhileIdle" value="true"/>
</bean>

<bean id="jimSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="jimDataSource"/>
  <property name="mapperLocations" value="classpath:mapper/*.xml"/>
  <property name="typeAliasesPackage" value="com.jim.logstashmvc.dao.generated.entity"/>
  <property name="plugins">
    <array>
      <bean class="com.github.pagehelper.PageHelper">
        <property name="properties">
          <value>dialect=postgresql reasonable=true supportMethodsArguments=true returnPageInfo=check params=count=countSql</value>
        </property>
      </bean>
    </array>
  </property>
</bean>

<bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
  <property name="sqlSessionFactoryBeanName" value="jimSqlSessionFactory"/>
  <property name="basePackage" value="com.jim.logstashmvc.dao.generated.mapper"/>
</bean>

通用 Mapper 使用範例

Mapper 介面

public interface ProductMapper extends Mapper<Product> {}

Service 層封裝

public interface IService<T> {
    T selectByKey(Object key);
    int save(T entity);
    int delete(Object key);
    int updateAll(T entity);
    int updateNotNull(T entity);
    List<T> selectByExample(Object example);
}

public abstract class BaseService<T> implements IService<T> {
    @Autowired protected Mapper<T> mapper;
    // 實作 CRUD 方法...
}

@Service
public class ProductServiceImpl extends BaseService<Product> implements ProductService {
    @Override
    public List<Product> selectByProduct(Product product, int page, int rows) {
        Example example = new Example(Product.class);
        Example.Criteria criteria = example.createCriteria();
        if (StringUtils.isNotBlank(product.getName())) {
            criteria.andEqualTo("name", product.getName());
        }
        if (product.getId() != null) {
            criteria.andEqualTo("id", product.getId());
        }
        PageHelper.startPage(page, rows);
        return selectByExample(example);
    }
}

生成檔案結構對比

常見問題排查

• Mapped Statements 重複：確認 targetRuntime 設為 MyBatis3Simple，且 MapperScannerConfigurer 掃描路徑正確
• 分頁失效：檢查 PageHelper 插件註冊順序，需在 SqlSessionFactoryBean 中配置
• 欄位名稱衝突：PostgreSQL 區分大小寫，建議資料表欄位統一小寫，或在 generatorConfig 指定分隔符