質問編集履歴
4
コード修正
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -89,7 +89,7 @@
|
|
|
89
89
|
|
|
90
90
|
最終的にSQLを組み立てているXMLを書きます。大体こういう感じです：
|
|
91
91
|
```XML
|
|
92
|
-
<select id="search" resultType="
|
|
92
|
+
<select id="search" resultType="package.DTOclass">
|
|
93
93
|
SELECT
|
|
94
94
|
* FROM
|
|
95
95
|
(
|
3
参考先追加
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -109,6 +109,6 @@
|
|
|
109
109
|
ORDER BY tables.access_time DESC LIMIT 100 OFFSET #{counter}
|
|
110
110
|
</select>
|
|
111
111
|
```
|
|
112
|
-
最初はまったのは、テーブル名を普通にバインド（```#{rotate}```みたいに）しようとしたらException（```org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"```）を出されてしまったことです。テーブル名はバインドできなさそうです。
|
|
112
|
+
最初はまったのは、テーブル名を普通にバインド（```#{rotate}```みたいに）しようとしたらException（```org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"```）を出されてしまったことです。テーブル名はバインドできなさそうです。参考：[mybatis/old-google-code-issues - github](https://github.com/mybatis/old-google-code-issues/blob/master/wiki/FaqJapanese.wiki)、[iBatis奮闘記-0013 ($を使用した動的パラメータ) - いろいろ備忘録日記](http://devlights.hatenablog.com/entry/20060908/p1)
|
|
113
113
|
|
|
114
114
|
検索のために複数テーブルを含めて抽出という目的が達成されたのでクローズします。
|
2
解決策備忘録として追記
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -1,3 +1,6 @@
|
|
|
1
|
+
**最終的な解決？を最下部に追記しています。ご参考まで**
|
|
2
|
+
ご回答いただいた[iwamoto_takaaki](https://teratail.com/users/iwamoto_takaaki)さん、[masuda_yuya](https://teratail.com/users/masuda_yuya)さん、ありがとうございます。
|
|
3
|
+
|
|
1
4
|
DB接続をするWEBアプリケーションを作っているのですが、掲題について悩んでいるので質問させてください。
|
|
2
5
|
|
|
3
6
|
具体的には、条件検索用のフォームとその結果の件数と表が表示されているWEBページです。MyBatisを使って動的SQLを生成しています。
|
|
@@ -5,7 +8,7 @@
|
|
|
5
8
|
|
|
6
9
|
ログテーブル名自体は、「table_yyyyMMdd」のように普段のテーブル名の末尾に日付情報がつくだけなので、ロジックでどうにかしようと思っているのですが、実際のSQLについて考えてみると、この書き方でよいのかと疑問に思ったため、よりよい書き方があればと思い質問に至りました。自分が考え付いたのは以下です：
|
|
7
10
|
```SQL
|
|
8
|
-
|
|
11
|
+
SELECT * FROM
|
|
9
12
|
(SELECT * FROM table)AS table UNION ALL SELECT * FROM
|
|
10
13
|
(SELECT * FROM table_yyyyMMdd)AS past01 UNION ALL SELECT * FROM
|
|
11
14
|
(SELECT * FROM table_yyyyMMdd)AS past02 UNION ALL SELECT * FROM
|
|
@@ -44,4 +47,68 @@
|
|
|
44
47
|
where access_time >= to_timestamp('2016/06/27 00:00','YYYY/MM/DD HH24:MI') and access_time <= to_timestamp('2016/06/27 07:00','YYYY/MM/DD HH24:MI')
|
|
45
48
|
order by access_time desc limit 100 offset 1
|
|
46
49
|
```
|
|
47
|
-
↑6/27の0時から7時まで……の条件のつもりだが、普通に現在(17時とか)から最新順で100件になってしまう。条件を別の項目に変え(```where hoge='piyo'```みたいな)ても効いておらず、普通に全データの最新100件が返される。
|
|
50
|
+
↑6/27の0時から7時まで……の条件のつもりだが、普通に現在(17時とか)から最新順で100件になってしまう。条件を別の項目に変え(```where hoge='piyo'```みたいな)ても効いておらず、普通に全データの最新100件が返される。
|
|
51
|
+
|
|
52
|
+
---
|
|
53
|
+
06/28
|
|
54
|
+
[masuda_yuya](https://teratail.com/users/masuda_yuya)さんのご指摘にてSQLの文法の誤りを認識。改修して、ベタSQLとしてはこういう感じになりました：
|
|
55
|
+
```SQL
|
|
56
|
+
select*from (
|
|
57
|
+
select*from table
|
|
58
|
+
union all select*from table_yyyyMMdd
|
|
59
|
+
union all select*from table_yyyyMMdd
|
|
60
|
+
union all select*from table_yyyyMMdd
|
|
61
|
+
) tables
|
|
62
|
+
where tables.access_time <= to_timestamp('2016/06/27 06:00','YYYY/MM/DD HH24:MI')
|
|
63
|
+
and tables.access_time >= to_timestamp('2016/06/27 01:00','YYYY/MM/DD HH24:MI')
|
|
64
|
+
order by tables.access_date desc limit 100 offset 1
|
|
65
|
+
```
|
|
66
|
+
WHERE句の条件は適当でよく、正常に結果が抽出できるかどうかの確認となります。問題なさそうだったので、動的SQLにするためにjavaをいじります。
|
|
67
|
+
- yyyyMMdd部分の生成
|
|
68
|
+
メインのテーブルが「table」で、本日分のデータのみが載っている。日次でローテートされ、ローテートされるとテーブル名の末尾に「_yyyyMMdd」が付与される。ここでは過去10日分のみ保持。
|
|
69
|
+
とりあえず、SQL組み立てでバインドのため利用している変数を宣言しているクラス内に、過去10日間のテーブル名を返すgetメソッドを作りました。
|
|
70
|
+
```java
|
|
71
|
+
public List<String> getRotateTables(){
|
|
72
|
+
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyyMMdd");
|
|
73
|
+
Calendar t =Calendar.getInstance();
|
|
74
|
+
List<String> list=new ArrayList<>();
|
|
75
|
+
String[] tmp = new String[10];
|
|
76
|
+
for(int i=0;i<=9;i++){
|
|
77
|
+
t.add(Calendar.DAY_OF_YEAR, -1);
|
|
78
|
+
tmp[i]="table_"+dateFormat.format(t.getTime()).toString();
|
|
79
|
+
}
|
|
80
|
+
list.addAll(Arrays.asList(tmp));
|
|
81
|
+
return list;
|
|
82
|
+
}
|
|
83
|
+
```
|
|
84
|
+
きたないっぽいですが動作すること優先で進みます。
|
|
85
|
+
|
|
86
|
+
- Mapperインタフェースに上記のパラメータ追加
|
|
87
|
+
- Mapper使っているDAOに上記パラメータ追加
|
|
88
|
+
- 検索やページングのActionクラスにも追加
|
|
89
|
+
|
|
90
|
+
最終的にSQLを組み立てているXMLを書きます。大体こういう感じです：
|
|
91
|
+
```XML
|
|
92
|
+
<select id="search" resultType="sso.repository.SSODTO">
|
|
93
|
+
SELECT
|
|
94
|
+
* FROM
|
|
95
|
+
(
|
|
96
|
+
SELECT * FROM table
|
|
97
|
+
<foreach item="rotate" collection="rotateList" separator=" ">
|
|
98
|
+
UNION ALL SELECT * FROM ${rotate}
|
|
99
|
+
</foreach>
|
|
100
|
+
) tables
|
|
101
|
+
<where>
|
|
102
|
+
<if test="from!=null and from!=''">
|
|
103
|
+
AND tables.access_time >= to_timestamp(#{from},'YYYY/MM/DD HH24:MI')
|
|
104
|
+
</if>
|
|
105
|
+
<if test="to!=null and to!=''">
|
|
106
|
+
AND tables.access_time <= to_timestamp(#{to},'YYYY/MM/DD HH24:MI')
|
|
107
|
+
</if>
|
|
108
|
+
</where>
|
|
109
|
+
ORDER BY tables.access_time DESC LIMIT 100 OFFSET #{counter}
|
|
110
|
+
</select>
|
|
111
|
+
```
|
|
112
|
+
最初はまったのは、テーブル名を普通にバインド（```#{rotate}```みたいに）しようとしたらException（```org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"```）を出されてしまったことです。テーブル名はバインドできなさそうです。MyBatisじゃなくてiBatisですが参考：[iBatis奮闘記-0013 ($を使用した動的パラメータ) - いろいろ備忘録日記](http://devlights.hatenablog.com/entry/20060908/p1)
|
|
113
|
+
|
|
114
|
+
検索のために複数テーブルを含めて抽出という目的が達成されたのでクローズします。
|
1
検証して浮上した別問題について追記
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -18,10 +18,10 @@
|
|
|
18
18
|
SELECT * FROM table
|
|
19
19
|
<where>
|
|
20
20
|
<if test="startTime!=null and startTime!=''">
|
|
21
|
-
AND
|
|
21
|
+
AND access_time >= to_timestamp(#{start}, 'YYYY/MM/DD HH24:MI')
|
|
22
22
|
</if>
|
|
23
23
|
<if test="endTime!=null and endTime=''">
|
|
24
|
-
AND
|
|
24
|
+
AND access_time <= to_timestamp(#{end}, 'YYYY/MM/DD HH24:MI')
|
|
25
25
|
</if>
|
|
26
26
|
</where>
|
|
27
27
|
ORDER BY time DESC LIMIT 100 OFFSET #{counter}
|
|
@@ -29,4 +29,19 @@
|
|
|
29
29
|
```
|
|
30
30
|
上記の自分が思いついたSQLであてはめようとすると、```<where>```までの間に記述を追加することになると思っています。ですが、ローテートされて残っているテーブルは10件になるため、なんだかあまりきれいじゃないような気がしています。また、速度的にもどうなのかな？ と思うところがあります。
|
|
31
31
|
|
|
32
|
-
postgresqlですが、Oracleだったらこう書けるよ、とかでもよいので、何かよりよくするためのご助言を頂ければと思います。
|
|
32
|
+
postgresqlですが、Oracleだったらこう書けるよ、とかでもよいので、何かよりよくするためのご助言を頂ければと思います。
|
|
33
|
+
|
|
34
|
+
---
|
|
35
|
+
ひとまずプログラムの中に組み込む前にベタSQLで結果を試してみたところ、当初質問を立てた意図とは変わりますが、うまくいかない部分があったので解決するまでこの質問をあけておきます（解決後質問文に顛末を追記します）。
|
|
36
|
+
なお、明示的に回答を求めず自己解決の心づもりでおりますが、下記に目を通してお気づきの点ございましたらご助言頂けますと幸甚です。
|
|
37
|
+
|
|
38
|
+
**問題：where句が効いてない**
|
|
39
|
+
```SQL
|
|
40
|
+
select*from table
|
|
41
|
+
union all select*from table_20160626
|
|
42
|
+
union all select*from table_20160625
|
|
43
|
+
union all select*from table_20160624
|
|
44
|
+
where access_time >= to_timestamp('2016/06/27 00:00','YYYY/MM/DD HH24:MI') and access_time <= to_timestamp('2016/06/27 07:00','YYYY/MM/DD HH24:MI')
|
|
45
|
+
order by access_time desc limit 100 offset 1
|
|
46
|
+
```
|
|
47
|
+
↑6/27の0時から7時まで……の条件のつもりだが、普通に現在(17時とか)から最新順で100件になってしまう。条件を別の項目に変え(```where hoge='piyo'```みたいな)ても効いておらず、普通に全データの最新100件が返される。
|