MyBatis中如何获得PostgreSQL数据库错误码

Posted on 2017-02-28 21:17:41 by osdba

熟悉PostgreSQL数据库的人知道,在psql中可以通过设置\set VERBOSITY verbose显示执行SQL出错时的详细错误码,如下所示:


mydb=> \set VERBOSITY verbose
mydb=> insert into tangtbl values(1, 'xxxxxx');
ERROR:  23505: duplicate key value violates unique constraint "pk_id"
DETAIL:  Key (id)=(1) already exists.
SCHEMA NAME:  public
TABLE NAME:  tangtbl
CONSTRAINT NAME:  pk_id
LOCATION:  _bt_check_unique, nbtinsert.c:406

上面的“23505”,就是错误码,在PostgreSQL官方文档中详细列出了所有的错误码:Appendix A. PostgreSQL Error Codes
然后如何在Java的Mybatis中获得这个错误码呢?请见下面的代码:


package mybatistest.mybatistest01;

import java.sql.SQLException;

import mybatis.db.DBSessionFactory;
import mybatistest.dao.TangTblMapper;
import mybatistest.model.TangTbl;

import org.apache.ibatis.session.SqlSession;

/**
 * Hello world!
 *
 */
public class App 
{
    public static void main( String[] args )
    {
        SqlSession session = DBSessionFactory.openSession();
        
        TangTblMapper tangMapper = session.getMapper(TangTblMapper.class);
        TangTbl row = new TangTbl();
        row.setNote("xxxxxx");
        row.setId(1);
        try {
            Integer ret = tangMapper.insertAll(row);
            System.out.printf("ret=%d\n", ret);            
        }catch(Exception e) {
            if (e.getCause() instanceof SQLException) {
                SQLException sqlException = (SQLException) e.getCause();
                System.out.printf("sqlSQLState=%s\n", sqlException.getSQLState());
            }
        }
        session.commit();
    }
}

从上面的代码中可以知道,是调用异常SQLException中的函数getSQLState()得到的。
下面贴出MyBatis中的mapper代码: TangTblMapper.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="mybatistest.dao.TangTblMapper">
  <resultMap id="BaseResultMap" type="mybatistest.model.TangTbl">
    <result column="id" jdbcType="INTEGER" property="id" />
    <result column="note" jdbcType="VARCHAR" property="note" />
  </resultMap>
  
  <insert id="insertAll" parameterType="mybatistest.model.TangTbl">
    insert into tangtbl (id,note)
    values (#{id}, #{note})
  </insert>
  
</mapper>

TangTblMapper.java的内容如下:


package mybatistest.dao;

import mybatistest.model.TangTbl;

public interface TangTblMapper {
    int insertAll(TangTbl record);
}

建立的测试表的表结构如下:


evd=> \d tangtbl;
                         Table "public.tangtbl"
 Column |  Type   |                      Modifiers
--------+---------+------------------------------------------------------
 id     | integer | not null default nextval('tangtbl_id_seq'::regclass)
 note   | text    |
Indexes:
    "pk_id" PRIMARY KEY, btree (id)