MySQL 8系 でのJDBC設定

そろそろ、本番環境のMySQLを8系に上げないといけないと思い、開発環境を8系にして、MyBatisで試してたところ、いきなり最初の connect でエラーが出て使えないので、調べてましたところ、

JDBCの設定記述を変えないといけないことがわかり、無事解決しました。

ここで教えて頂く : https://qiita.com/syoki/items/3d82ef00300868353572

< MyBatis Config UNPOOLED 設定例 >


  <properties>     
    
    <!-- 5.7 -->
    <!--<property name="msdriver" value="com.mysql.jdbc.Driver" />--> 
    <!-- 8 -->
    <property name="msdriver" value="com.mysql.cj.jdbc.Driver" />   
    <!-- 5.7 -->  
    <!--<property name="msurl" value="jdbc:mysql://localhost:3306/dbname?characterEncoding=utf8&amp;autoReconnect=true&amp;useSSL=false" />-->
    <!-- 8 -->
    <property name="msurl" value="jdbc:mysql://localhost/dnksg?characterEncoding=utf8&amp;autoReconnect=true&amp;useSSL=false&amp;serverTimezone=GMT%2B9&amp;rewriteBatchedStatements=true" />     
    
    <property name="msusername" value="???????" />     
    <property name="mspassword" value="??????????" />   
    

    
  </properties>

[商品価格に関しましては、リンクが作成された時点と現時点で情報が変更されている場合がございます。]

Java逆引きレシピ 第2版 [ 竹添 直樹 ]
価格:3080円(税込、送料無料) (2023/1/25時点)

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>