MyBatis MySQL 開発環境でのプーリング接続で、Too many connections の解消

本番環境では普通に JNDI のプーリングが効いて、接続数過多のエラーはまずは発生しないですが、NetBeansの開発環境で JNDI が使えないので (管理人が知らないだけなのかも)、MyBatis の設定は、MyBatis のPOOL を使ってます。


    <environment id="?????">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${msdriver}"/>
        <property name="url" value="${msurl}"/>
        <property name="username" value="${msusername}"/>
        <property name="password" value="${mspassword}"/>
        
        <!-- maybe not affect --> 
        <property name="poolMaximumActiveConnections" value="1000" /> 
        <property name="poolMaximumIdleConnections" value="1000" />
        <!--<property name="maxActive" value="300" />-->
        <!--<property name="maxWait" value="5000" />-->
              
      </dataSource>
    </environment>

正常にプーリングが効くはずなんですが、よく MySQL の JDBC が Too many connections でエラーを起こします。


Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1110)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2465)
	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2306)
	... 55 more

MySQL の my.ini (Windowsなので) の最大 300 にしていたのを、試しに 1000 に変更して、エラーの発生する処理 (連続1200レコードほどの INSERT) を行ったところ、

何故か、エラーなしに正常に処理が完了出来ました。

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=1000

処理中、プロセス数を確認してみると、500位まで接続数が上昇し続け、アイドルなのが捨てられ、1桁に減りました。
MyBatis の問題か、connecter/J の問題かはわかりませんが、長年の??が解消しました。

MultipleSelect 使い方 (4) Java

大まかな流れとしては、

  1. サーブレットで各マルチセレクト条件値CSVテキストをリクエストパラメータで受信
  2. サーブレットで、1.がnullであるか判別してフラグにして、操作用クラスにセットする
  3. マルチセレクト条件値CSVテキストは、リストにして操作用クラスにセットする
  4. 操作用クラスの検索処理メソッドで、最初にセットされた項目別フラグがtrueの場合、一時テーブルの削除追加を行う
  5. MyBatisの指定idの検索SQLを実行する際に、フラグがtrueの場合、一時テーブルをジョインしたSQL、falseの場合ジョインしないSQLを実行

となります。

MultipleSelect 使い方 (3) MyBatis SQL

検索条件を各項目の一時テーブルに入れて、ジョインするかしないかは、パラメータで指示してます。

[ SQL例 ]


 <!-- 船スペックマスタリスト --> 
 <!-- 使用 -->
  <select id="selVesselSpecList2" parameterType="ParamVesselSpec" resultType="VesselSpec">
    select * from (
		select
			v.ukfld as id, v.ukfld as imo,
			
			v.Name as vessel, 
      coalesce(fc_acc6m(v.Name), '') as acc6m,
    
			coalesce(en.cnt_en, 0) as cnt_en, 
      coalesce(`in`.cnt_in, 0) as cnt_in,     
			coalesce(eo.cnt_eo, 0) as cnt_eo, 			
			coalesce(io.cnt_io, 0) as cnt_io, 
      coalesce(ua.cnt_ua, 0) as cnt_ua, 
			
			v.TEU, v.`Year`, v.Speed, v.GrossT, v.DeadW, v.`call`, v.Cntry,  
		
			v.tm_add, v.tm_updt, 
			greatest(v.tm_add, v.tm_updt, coalesce(mt.TM_ADD, '2000-01-01 00:00:00')) as tm_lastupdt,
		
			v.is_manadd,
    
      coalesce(mt.LENGTH_OVERALL, 0) as `length`,
			coalesce(mt.BREADTH_EXTREME, 0) as width,
			coalesce(mt.DRAUGHT, 0) as draught,
    
      coalesce(round(mt.LENGTH_OVERALL, 0), 0) as `length_int`,
			coalesce(round(mt.BREADTH_EXTREME, 0), 0) as width_int,
			coalesce(round(mt.DRAUGHT, 0), 0) as draught_int,
    
			COALESCE(mt.LIQUID_OIL, 0) AS liquid_oil,
			COALESCE(mt.FUEL_CONSUMPTION, '') AS fuel_consumption,
			COALESCE(mt.OWNER, '') AS owner,			
			COALESCE(mt.MANAGER, '') AS manager,
			COALESCE(mt.MANAGER_OWNER, '') AS manager_owner    
			
			
		from 
			vessel2 v
    
      left outer join vessel_mt mt on (v.ukfld = mt.IMO) 
    
      <if test="!isTEUselAll">
        inner join tmp_imo_teu tt on (
        v.ukfld = tt.IMO and tt.ID_SESSION = #{idSession} and tt.NM_GRID = 'specs' 
        )
      </if>
      <if test="!isYearselAll">
        inner join tmp_imo_year yt on (
        v.ukfld = yt.IMO and yt.ID_SESSION = #{idSession} and yt.NM_GRID = 'specs' 
        )        
      </if>
      <if test="!isSpeedselAll">
        inner join tmp_imo_speed st on (
        v.ukfld = st.IMO and st.ID_SESSION = #{idSession} and st.NM_GRID = 'specs' 
        )        
      </if>
      
      <if test="!isLengthselAll">
        inner join tmp_imo_length lg on (
        mt.IMO = lg.IMO and lg.ID_SESSION = #{idSession} and lg.NM_GRID = 'specs' 
        )        
      </if>
      <if test="!isWidthselAll">
        inner join tmp_imo_width wd on (
        mt.IMO = wd.IMO and wd.ID_SESSION = #{idSession} and wd.NM_GRID = 'specs' 
        )        
      </if>
      <if test="!isDraughtselAll">
        inner join tmp_imo_draught dr on (
        mt.IMO = dr.IMO and dr.ID_SESSION = #{idSession} and dr.NM_GRID = 'specs' 
        )        
      </if>
      
    
			left outer join (
			select imo, count(*) as cnt_en from sch_export_source group by imo
			) en 
			on (v.ukfld = en.imo)
			
			left outer join (
			select imo, count(*) as cnt_in from sch_import_source group by imo
			) `in` 
			on (v.ukfld = `in`.imo)
		      
      left outer join (
			select imo, count(*) as cnt_eo from sch_export_source_old group by imo
			) eo 
			on (v.ukfld = eo.imo)
      
      left outer join (
			select imo, count(*) as cnt_io from sch_import_source_old group by imo
			) io 
			on (v.ukfld = io.imo)    
      
			left outer join (
			select u.vessel, count(u.id) as cnt_ua from sch_useradd u group by vessel
			) ua on (v.Name = ua.vessel)             
      
    where 
      1 = 1 and v.Name like concat('%', #{vsllk}, '%') and
      ukfld like concat('%', #{imolk}, '%')
      <if test="isModOnly">
        and is_manadd = 1
      </if>
    ) a
    order by
      ${sidx} ${sord}  
    limit ${skip}, ${pgrec}  
  </select>

[ 使っているテーブル ]

船マスタ


CREATE TABLE `vessel2` (
	`Name` VARCHAR(36) NOT NULL DEFAULT '' COMMENT '船名',
	`call` VARCHAR(9) NOT NULL DEFAULT '' COMMENT '無線局呼出符号',
	`Year` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '建造年',
	`Speed` FLOAT NOT NULL DEFAULT '0' COMMENT '速力',
	`TEU` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '積載TEU',
	`GrossT` INT(11) NOT NULL DEFAULT '0' COMMENT 'グロス重量',
	`DeadW` INT(11) NOT NULL DEFAULT '0' COMMENT '重量重量',
	`Cntry` VARCHAR(2) NOT NULL DEFAULT '' COMMENT '船籍国コード',
	`ukfld` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
	`tm_add` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	`tm_updt` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '最終更新時刻',
	`is_manadd` TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'ユーザー更新フラグ',
	`ChgName` VARCHAR(36) NOT NULL DEFAULT '0' COMMENT '参照結合用変換船名',
	PRIMARY KEY (`ukfld`),
	INDEX `vessel2_Name` (`Name`),
	INDEX `vessel2_ChgName` (`ChgName`)
)
COMMENT='船マスタ (利用)'
COLLATE='utf8_general_ci'
ENGINE=MyISAM

一時テーブル (一部)

・ID_SESSION はTomcatのセッションIDでユーザー別
・ほっておくと肥大するので、夜中に全レコード削除


CREATE TABLE `tmp_imo_teu` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
	`ID_SESSION` VARCHAR(70) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID',
	`NM_GRID` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'jqGridグリッドID',
	`IMO` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	PRIMARY KEY (`ID`),
	INDEX `tmp_imo_ID_SESSION` (`ID_SESSION`),
	INDEX `tmp_imo_NM_GRID` (`NM_GRID`),
	INDEX `tmp_imo_IMO` (`IMO`)
)
COMMENT='船スペック検索用IMO一時テーブル'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5627
;

CREATE TABLE `tmp_imo_year` (
	`ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
	`ID_SESSION` VARCHAR(70) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID',
	`NM_GRID` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'jqGridグリッドID',
	`IMO` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	PRIMARY KEY (`ID`),
	INDEX `tmp_imo_ID_SESSION` (`ID_SESSION`),
	INDEX `tmp_imo_NM_GRID` (`NM_GRID`),
	INDEX `tmp_imo_IMO` (`IMO`)
)
COMMENT='船スペック検索用IMO一時テーブル'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

[ 一時テーブル削除追加用SQL ]

・テーブル名を ${tmptbl} のパラメータで渡すことで、まとめてます。


 <!-- マルチセレクト用一時テーブル更新 -->
 <!-- 削除 -->
 <delete id="delIMOTmpAll" parameterType="MyBatisParent">
   delete from ${tmptbl} where ID_SESSION = #{idSession} and NM_GRID = #{nmGrid}
 </delete>
 <!-- 追加 -->
 <insert id="insAIMOTmp" parameterType="MyBatisParent">
   insert into ${tmptbl}
   (ID_SESSION, NM_GRID, IMO)
   select #{idSession}, #{nmGrid}, 
   ukfld
   from ${vslmsttbl}
   where ${vslColumn} between #{insMinVal} and #{insMaxVal}
 </insert>
 <insert id="insAIMOTmpmt" parameterType="MyBatisParent">
   insert into ${tmptbl}
   (ID_SESSION, NM_GRID, IMO)
   select #{idSession}, #{nmGrid}, IMO
   from ${vslmsttbl}
   where ${vslColumn} between #{insMinVal} and #{insMaxVal}
 </insert>

MyBatis 大量連続更新時のパフォーマンス向上策

sqlSessionFactory.openSession の引数に ExecutorType.BATCH を入れてます。

[ コードサンプル ]


  /**
   * MyBatisセッションファクトリー
   */
  protected SqlSessionFactory sqlSessionFactory = null;

  /**
   * マルチ選択用ファイル分類一時テーブル削除追加
   */
  protected void delinsFileTmpTable() {

    try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
      // -- ファイル分類一時テーブル更新 -- //
      // 削除 //
      session.delete("delTmpFileList", this);
      // 追加 //
      for (String nmf : this.fileextList) {
        this.nmFile = nmf;
        session.insert("insATmpFileList", this);
      }
      session.commit();
    }
    catch (Exception e) {
      e.printStackTrace();
    }

  }

JAVA 本番環境リリース時変更をなくす (MyBatis Datasource)

開発環境がWindows, 実行環境がLinuxの場合、そのままでは動かないので、違う設定する必要ありますが、最小限に済ます方法です。

1)  環境設定用xmlは、UNPOOLED (Windows開発環境) と JNDI (Linux本番環境)の2種類を用意。

開発用


    <environment id="ocean">
      <transactionManager type="JDBC"/>
      <dataSource type="UNPOOLED">
        <property name="driver" value="${msdriver}"/>
        <property name="url" value="${msurl}"/>
        <property name="username" value="${msusername}"/>
        <property name="password" value="${mspassword}"/>
      </dataSource>
    </environment>

本番用


    <environment id="ocean">
      <transactionManager type="JDBC"/>
      <dataSource type="JNDI">
        <property name="data_source" value="java:comp/env/jdbc/ocean2"/>
      </dataSource>
    </environment>

2) web.xml の context に判別できる記述をしておく


  <!-- Datasource JNDI pooled or Unpooled -->
  <context-param>
    <param-name>IsDSJNDI</param-name>
    <param-value>0</param-value>
  </context-param>

3) Startupのサーブレットで、2)を読み、public static な変数に入れておく
静的設定クラス


  /**
   * Mybatis設定
   */
  public static final String MB_RESOURCE = "vesselsch/mybatis/SqlMapConfigLocal.xml";       // 開発用、DRバックアップ用
  public static final String MB_RESOURCE_JNDI = "vesselsch/mybatis/SqlMapConfigJNDI.xml";   // サービス用
  public static boolean isDSJNDI = false;

Startup


ServletContext cntxt = getServletContext();
// データソースがJNDIかどうか //
DBini.isDSJNDI = cntxt.getInitParameter("IsDSJNDI").equals("1");

4) sqlSessionFactory の生成にあたって、リソース読込で 3)を使う


 /**
   * MyBatis SQLセッションファクトリ
   */
  protected SqlSessionFactory sqlSessionFactory = null;
  /*
  コンストラクタ
  */
  public MyBatisParent() {

    // MyBatis //
    try {
      // 開発環境 or サービス環境で違える UNPOOLED or JNDI //
      InputStream inputStream = Resources.getResourceAsStream(DBini.isDSJNDI ? DBini.MB_RESOURCE_JNDI : DBini.MB_RESOURCE);
      sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    catch (IOException ie) {
      ie.printStackTrace();
    }

  }

5) 4) は親クラスのコンストラクタで行い、継承された子クラスでの個別設定はなくす
利用例


  public ArrayList<ExRate> selectExRateSheet() {

    List list = null;
    try (SqlSession session = sqlSessionFactory.openSession()) {
      list = session.selectList("selExRate", this);
      System.out.println("selectExRateSheet:" + list.size());
    }
    catch (Exception se) {
      se.printStackTrace();
    }
    return new ArrayList(list);

  }

6) 結果、2)のフラグ記述の変更だけで本番で動く

MyBatis 使って何がうれしいか

業務システムでデータベースは必須と思います。

データベースといえば、SQLが必須と思います。

昔、Java始めた頃は、iBatisもMyBatisもなかったので、Javaに標準についてるjavax.sql使ってJDBC介して操作してましたので、SQLを直接ソースコードにべた書きしたり、プロパティファイルから読んだり、独自テキストファイルに記述されたのを読んだりしていて、

かなり統一性に欠ける、メンテナンス性の悪い、後で苦労することしてました。

iBatis (MyBatisの前身)を知ってから、

コードが大変短くなり、SQLをまとめて分類して置いておけ、やりたい事がJavaコードに制約されず、簡潔に速くできるようになりました。

MyBatisのxml内で、条件分岐、パラメータ以外のメタ文字も指定できるので、プログラム側で行うか、静的に行うかの選択肢が増えて、ケースバイケースな事がしやすくなります。

xml内if利用例


  <!-- 商品マスタアップロードエクセル列マッピングリスト -->
  <select id="selExcelMapList" parameterType="ParamMstMnt" resultMap="ExcelMapMap">
    
    SELECT
      ID, m.IDX_EXCEL, 
      fc_sel_columntitle('goodslist', m.NM_TBLFLD, #{cdLang}) AS NM_EXCELTXT,
      m.NM_TBLFLD, 1 AS FLG_CHECKED, 
      fc_sel_columnfld('goodslist', m.NM_TBLFLD) AS NM_JAVAFLD,
      c.NM_FORMATTER
    FROM
      m_defupldclms m
      INNER JOIN m_jqcolumn c ON (m.NM_TBLFLD = c.NM_TBLFIELD AND c.ID_GRID = 'goodslist')
    WHERE
      1 = 1 
    <if test="!isSK">
      AND c.IS_EDITABLE_CUST = 1 OR c.NM_TBLFIELD = 'CD_JAN'
    </if>    
    
    ORDER BY ID
  </select>