MySQL – ページ 4 – Saoline Tips

Tomcat9 JNDIデータソースの設定

投稿日: 2019-01-11 投稿者: Hisa

プーリングコネクションするJNDIの設定を、server.xml に記述します。

[ 設定例 MySQL ]


      <Resource 
      driverClassName="com.mysql.jdbc.Driver" 
      maxActive="300" 
      maxIdle="100" 
      maxWait="5000" 
      name="jdbc/DATABASE_NAME" 
      password="??????" 
      type="javax.sql.DataSource" 
      url="jdbc:mysql://localhost:3306/DATABASE_NAME?characterEncoding=utf8&amp;autoReconnect=true&amp;useSSL=false" 
      username="??????" 
      
      validationQuery="select 1"
      testOnBorrow="true"
      testWhileIdle="true"
      timeBetweenEvictionRunsMillis="60000"
      />

5.7系にバージョンアップした時、はまったのですが、非リモートサーバーのデータベースの場合、urlに useSSL=false を入れる必要あります。(ないと劇遅)
Oracleの中の人に教えてもらったのですが、同一ホスト内の場合、127.0.0.1 より localhost が速いそうです。

[ 設定例 Firebird ]


    <Resource
      name="jdbc/DATABASE_NAME"
      type="javax.sql.DataSource"
      driverClassName="org.firebirdsql.jdbc.FBDriver"
      password="????????"
      maxIdle="40"
      maxWait="5000"
      username="????????"
      url="jdbc:firebirdsql:localhost/3050:DATABASE_NAME?lc_ctype=SJIS_0208"
      maxActive="100"
      
    />

[ 設定例 Oracle ]


    <Resource
      name="jdbc/SID_NAME"
      type="javax.sql.DataSource"
      driverClassName="oracle.jdbc.driver.OracleDriver"
      password="SID_NAME"
      maxIdle="40"
      maxWait="5000"
      username="????????"
      url="jdbc:oracle:thin:@???.???.???.???:1521:SID_NAME"
      maxActive="100"/>

[ 設定例 PostgreSQL ]


    <Resource
      name="jdbc/DBNAME"
      type="javax.sql.DataSource"
      driverClassName="org.postgresql.Driver"
      password="???????"
      maxIdle="40"
      maxWait="5000"
      username="??????"
      url="jdbc:postgresql://127.0.0.1/DBNAME"
      maxActive="100"/>

[ 設定例 SQLServer ]


     <Resource
      name="jdbc/DBNAME"
      type="javax.sql.DataSource"
      driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
      password="??????"
      maxIdle="40"
      maxWait="5000"
      username="??????"
      url="jdbc:sqlserver://???.????.???.???:1433;DatabaseName=DBNAME"
      maxActive="100" />

詳解Tomcat [ 藤野　圭一 ]

楽天で購入

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

独習Java サーバサイド編第2版【電子書籍】[ 山田祥寛 ]
価格：3960円 (2022/12/19時点)

楽天で購入

Tomcatハンドブック
価格：5170円（税込、送料無料) (2023/1/11時点)

楽天で購入

MySQL information_schema の活用テーブルステータス

投稿日: 2019-01-09 投稿者: Hisa

information_schema にある TABLES から現在の状態を取得できます。

下のようなビューを使って、わかりやすくしてます。


CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `vw_simple_table_schema` AS
    SELECT 
        `information_schema`.`tables`.`TABLE_NAME` AS `Name`,
        `information_schema`.`tables`.`TABLE_COMMENT` AS `Comment`,
        `information_schema`.`tables`.`TABLE_ROWS` AS `Rows`,
        (FLOOR((`information_schema`.`tables`.`DATA_LENGTH` / 1024)) + FLOOR((`information_schema`.`tables`.`INDEX_LENGTH` / 1024))) AS `total_length_k`,
        FLOOR((`information_schema`.`tables`.`DATA_LENGTH` / 1024)) AS `Data_length_k`,
        FLOOR((`information_schema`.`tables`.`INDEX_LENGTH` / 1024)) AS `index_length_k`,
        `information_schema`.`tables`.`CREATE_TIME` AS `Create_time`
    FROM
        `information_schema`.`tables`
    WHERE
        (`information_schema`.`tables`.`TABLE_SCHEMA` = 'ocean2')
    ORDER BY `information_schema`.`tables`.`TABLE_ROWS` DESC

各アプリケーションの管理人用ページに、下のような表を配置してます。

運航港地図と施設港地図

投稿日: 2019-01-08 投稿者: Hisa

googleMap利用の港地図です。

< リンク >

施設港地図

運航港地図

< 画面絵 >

運航港地図

施設港地図

< ご利用上の注意 >

運航港地図を船舶運航業務等で利用された結果生ずる、いかなる有形無形の損害については、開発者は一切の責任を負わないこととします。

データ内容につきましては、出来る限り最新の状況に追従させてますが、間に合わないこともあり、ご容赦下さい。

MySQL CSV文字列位置指定取得

投稿日: 2019-01-08 投稿者: Hisa

よく使うので、関数にしてます。


CREATE DEFINER=`root`@`localhost` FUNCTION `fc_csv_idxtxt_int`(
	`src` varchar(300),
	`idx` smallint

)
RETURNS int(11)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'CSV列指定位置取得'
begin

declare rtnvalcsv int;

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(src, ',', idx), ',', -1) INTO rtnvalcsv;

return rtnvalcsv;

end

15時間でわかるMySQL集中講座 [ 株式会社ハートビーツ　馬場俊彰 ]
価格：3168円（税込、送料無料) (2023/1/25時点)

楽天で購入

MySQL 記号除去関数

投稿日: 2019-01-08 投稿者: Hisa

各社から提供されるデータの船名を結合させて処理する時、船名内の記号、スペースの扱いがまちまちで、使えないので作ってみました。
正規表現使えば、すっきりして、新規出現にも対応できると思いますが、今のところ支障なく使えてるので、不都合に遭遇したら、作り変えます。


CREATE DEFINER=`root`@`localhost` FUNCTION `fc_extract_symbol`(
	`src` varchar(250)

)
RETURNS varchar(250) CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '記号除去関数'
begin

return
replace(replace(replace(replace(replace(replace(replace(src, ' ', ''), '*', ''), '.', ''), '-', ''), ',', ''), '(', ''), ')', '');

end

MySQL 言語指定曜日スカラー関数

投稿日: 2019-01-08 投稿者: Hisa

昔、MySQLで初めて作った、ユーザー定義関数です。
言語を付け足していけば、拡張できます。RETURNS の CHARSET は必要に応じて変更してください。


CREATE DEFINER=`root`@`localhost` FUNCTION `fc_dofwtxt`(
	`idx` tinyint,
	`langcd` varchar(4)

)
RETURNS varchar(20) CHARSET sjis
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '言語指定曜日文字列スカラー関数'
begin
  declare res varchar(12);
  
  if (langcd = 'jp') THEN
  	select 
  	  case (idx)
  	  when 2 then '月'
  	  when 3 then '火'
  	  when 4 then '水'
  	  when 5 then '木'
  	  when 6 then '金'
  	  when 7 then '土'
  	  when 1 then '日'
  		end as restxt into res;
  
  END IF;
  
  if (langcd = 'jpp') THEN
  	select 
  	  case (idx)
  	  when 2 then '(月)'
  	  when 3 then '(火)'
  	  when 4 then '(水)'
  	  when 5 then '(木)'
  	  when 6 then '(金)'
  	  when 7 then '(土)'
  	  when 1 then '(日)'
  		end as restxt into res;
  
  END IF; 
	
  if (langcd = 'ens') THEN
  	select 
  	  case (idx)
  	  when 2 then 'Mon'
  	  when 3 then 'Tue'
  	  when 4 then 'Wed'
  	  when 5 then 'Thu'
  	  when 6 then 'Fri'
  	  when 7 then 'Sat'
  	  when 1 then 'Sun'
  		end as restxt into res;
  
  END IF; 
	
  if (langcd = 'cnp') THEN
  	select 
  	  case (idx)
  	  when 2 then '(星期一)'
  	  when 3 then '(星期二)'
  	  when 4 then '(星期三)'
  	  when 5 then '(星期四)'
  	  when 6 then '(星期五)'
  	  when 7 then '(星期六)'
  	  when 1 then '(星期天)'
  		end as restxt into res;
  
  END IF;  		 	
	 
  
  return res;
end

MySQL の特徴 (他DBと比べて)

投稿日: 2019-01-05 投稿者: Hisa

経験上の羅列です。

1) || での連結は出来ない (CONCAT関数で同じことは出来る)

=> sql_modeの設定で出来ることがわかりました。http://wp.saoline.co/wordpress/?p=1305
2) WITHは5.7までないので、導出テーブルが入り組んだ場合、わかりにくい
3) 関数は普通に揃っている。(なければUDFを作ればよい)
4) インデックスが1テーブル1個しか効かない。(複合インデックスで対応、列の順番がパフォーマンスに影響)
5) 導出テーブルでLIMITが使えない
6) ストアドの結果を導出テーブルに出来ない
7) ストレージエンジンを選べるので。用途に合わせて最適に出来る
8) テーブル名の大文字小文字区別あり
9) 日付条件の問い合わせで数値が使える (20190101, ‘2019-01-01’ は同じ)
10) DUAL表の指定は不要
12) CONSTRAINTがない (値制約はトリガーで判断し、エラーを生成して処理しないことにすればもどきなことは出来る)
13) ユーザー認証に必ずホスト名(IP)が必要
14) 列毎にテーブル定義とは別のキャラクタセットが指定出来る
15) バルクインサートが超高速
16) SELECT検索結果をCSVに出力できる
17) JSONが格納できる(JSON型, 5.7から)
18) 日時の型が豊富で便利 (DATE, DATETIME, TIMESTAMP, TIME, YEAR)
19) innodb (デフォルト)の場合、パフォーマンス確保の為、データベースのサイズと同じ位のメモリをバッファプールで設定するのが望ましい
20) ダンプバックアップの種類が多く、便利

詳しくは、Oracleの公式ページを見て下さい。

http://otndnld.oracle.co.jp/document/products/sql/12/doc_cd/doc/appdev.120/E06020-01/oracle_mysql_compared.htm

これも詳しい : https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1197236_2274.html

MySQL カレンダーストアド応用編

投稿日: 2019-01-05 投稿者: Hisa

基本編で紹介したものに、データをUDFで拾ってきて、CONCATしてます。

[ 実行結果 ]

[ コード ]


CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sel_acalendar_vslccpp`(
	IN `ym` INT,
	IN `vslnm` VARCHAR(36),
	IN `idmultiple` SMALLINT

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '船寄港国単月カレンダー (使用)'
BEGIN	

DECLARE ymprev INT;
DECLARE ymnext INT;

SET ymprev = ym - 1;
SET ymnext = ym + 1;

IF (ymprev % 100 = 0) THEN
  SET ymprev = ymprev - 88;  
END IF;
IF (ymnext % 100 = 13) THEN
  SET ymnext = ymnext + 88;  
END IF;



SELECT
  YWK * idmultiple AS id,
  YWK, 
	
	
	MON, 
	TUE, WED, THU, FRI, SAT, 
	SUN,
	
	DTMIN, DTMAX,
	
	YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN) AS DTMINVAL,
	YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX) AS DTMAXVAL,
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) AS DTMINYM,
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) AS DTMAXYM
	
FROM
	(
	SELECT
	  
  	YWK,
  	MIN(YM) AS YMMIN,
  	MAX(YM) AS YMMAX,
  	MAX(SUN) AS SUN,
  	MAX(MON) AS MON,
  	MAX(TUE) AS TUE,
  	MAX(WED) AS WED,
  	MAX(THU) AS THU,
  	MAX(FRI) AS FRI,
  	MAX(SAT) AS SAT,
  	MIN(dt) AS DTMIN, MAX(dt) AS DTMAX
	FROM
		(
		SELECT
  		dt, 
  
  		CASE WHEN (WEEKOFYEAR(dt) = 53) THEN 
			  (YEAR(dt) - 1) * 100 + WEEKOFYEAR(dt)
	  	
	  		ELSE YEAR(dt) * 100 + WEEKOFYEAR(dt)  
	  	  
			END AS YWK,
	
			YEAR(dt) * 100 + MONTH(dt) AS YM,
  
  		CASE WHEN (DAYOFWEEK(dt) = 1) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt)) 
			ELSE '' END AS SUN,
			
			CASE WHEN (DAYOFWEEK(dt) = 2) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS MON,
			
			CASE WHEN (DAYOFWEEK(dt) = 3) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS TUE,
			
			CASE WHEN (DAYOFWEEK(dt) = 4) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS WED,
			
			CASE WHEN (DAYOFWEEK(dt) = 5) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS THU,
			
			CASE WHEN (DAYOFWEEK(dt) = 6) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS FRI,
			
			CASE WHEN (DAYOFWEEK(dt) = 7) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS SAT
			
		FROM
  		bimpsch.m_date
		WHERE
			YEAR(dt) * 100 + MONTH(dt) BETWEEN ymprev AND ymnext
			
		) a
	GROUP BY
  	YWK
	) b	
WHERE
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) = ym OR 
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) = ym
	
	
ORDER BY
  DTMIN;	
	
	
END

[ 利用例 ]

船の寄港国、ポップアップで港が現れる

右クリックコンテキストメニューを配置 => 列値、行IDとかで更新処理できる

[ jqGrid colModel例 ]


          colNames: ['月', '火', '水', '木', '金', '土', '日', '', '', ''],
          colModel: [
            {name: 'mon', width: '140px', classes: 'caltd calentd'},
            {name: 'tue', width: '140px', classes: 'caltd'},
            {name: 'wed', width: '140px', classes: 'caltd'},
            {name: 'thu', width: '140px', classes: 'caltd'},
            {name: 'fri', width: '140px', classes: 'caltd'},
            {name: 'sat', width: '140px', classes: 'caltd'},
            {name: 'sun', width: '140px', classes: 'caltd'},
            {name: 'id', hidden: true,
              editrules: {edithidden: true}
            },
            {name: 'dtmin', hidden: true,
              editrules: {edithidden: true}
            },
            {name: 'dtmax', hidden: true,
              editrules: {edithidden: true}
            },
          ],

MySQL カレンダーストアド基本編

投稿日: 2019-01-05 投稿者: Hisa

グリッドでカレンダーの配置が必要になり、いろいろ応用が効いて重宝してます。

[ 実行と結果 ]

[ コード ]


CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sel_acalendar`(
	IN `ym` INT


)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '単月カレンダー'
BEGIN	

DECLARE ymprev INT;
DECLARE ymnext INT;

SET ymprev = ym - 1;
SET ymnext = ym + 1;

IF (ymprev % 100 = 0) THEN
  SET ymprev = ymprev - 88;  
END IF;
IF (ymnext % 100 = 13) THEN
  SET ymnext = ymnext + 88;  
END IF;



SELECT
  YWK AS id,
  YWK, 
	
	
	MON, 
	TUE, WED, THU, FRI, SAT, 
	SUN,
	
	DTMIN, DTMAX,
	
	YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN) AS DTMINVAL,
	YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX) AS DTMAXVAL,
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) AS DTMINYM,
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) AS DTMAXYM
	
FROM
	(
	SELECT
	  
  	YWK,
  	MIN(YM) AS YMMIN,
  	MAX(YM) AS YMMAX,
  	MAX(SUN) AS SUN,
  	MAX(MON) AS MON,
  	MAX(TUE) AS TUE,
  	MAX(WED) AS WED,
  	MAX(THU) AS THU,
  	MAX(FRI) AS FRI,
  	MAX(SAT) AS SAT,
  	MIN(dt) AS DTMIN, MAX(dt) AS DTMAX
	FROM
		(
		SELECT
  		dt, 
  
  		CASE WHEN (WEEKOFYEAR(dt) = 53) THEN 
			  (YEAR(dt) - 1) * 100 + WEEKOFYEAR(dt)
	  	
	  		ELSE YEAR(dt) * 100 + WEEKOFYEAR(dt)  
	  	  
			END AS YWK,
	
			YEAR(dt) * 100 + MONTH(dt) AS YM,
  
  		CASE WHEN (DAYOFWEEK(dt) = 1) THEN 
			DATE_FORMAT(dt, '%e') 
			ELSE '' END AS SUN,
			
			CASE WHEN (DAYOFWEEK(dt) = 2) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS MON,
			
			CASE WHEN (DAYOFWEEK(dt) = 3) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS TUE,
			
			CASE WHEN (DAYOFWEEK(dt) = 4) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS WED,
			
			CASE WHEN (DAYOFWEEK(dt) = 5) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS THU,
			
			CASE WHEN (DAYOFWEEK(dt) = 6) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS FRI,
			
			CASE WHEN (DAYOFWEEK(dt) = 7) THEN 
			DATE_FORMAT(dt, '%e')
			ELSE '' END AS SAT
			
		FROM
  		m_date
		WHERE
			YEAR(dt) * 100 + MONTH(dt) BETWEEN ymprev AND ymnext
			
		) a
	GROUP BY
  	YWK
	) b	
WHERE
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) = ym OR 
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) = ym
	
	
ORDER BY
  DTMIN;	
	
	
END


CREATE TABLE `m_date` (
	`dt` DATE NOT NULL COMMENT '日付',
	`IS_HOLIDAY` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '祝日フラグ',
	PRIMARY KEY (`dt`)
)
COMMENT='連続日付マスタ'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

15時間でわかるMySQL集中講座 [ 株式会社ハートビーツ　馬場俊彰 ]

楽天で購入

カテゴリー: MySQL

Tomcat9 JNDIデータソースの設定

MySQL information_schema の活用テーブルステータス

運航港地図と施設港地図

MySQL CSV文字列位置指定取得

MySQL 日付指定年齢関数

MySQL 記号除去関数

MySQL 言語指定曜日スカラー関数

MySQL の特徴 (他DBと比べて)

MySQL カレンダーストアド応用編

MySQL カレンダーストアド基本編

2024年4月
日	月	火	水	木	金	土
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

2024年4月
日	月	火	水	木	金	土
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30