Java/MySQL COUNT関数を使った際に取得が0になる

Question

###前提・実現したいこと
Javaで住所録を作っています。
名字の最初の一文字であ行～わ行に分け、さらに「あ行○件、か行○件～」と件数の表示をしたいです。

###発生している問題
取得件数が0件となってしまいます。
どのようにしたら件数が正しく取得できるのか、お手数ですがご教示をお願いいたします。

###追記
どうやらSQLは正しい値をとれているようです。
問題は変数countKanaに格納できていないところにあるようです。
わかる方、どうかご回答をお願いいたします。

###情報
言語：Java
データベース：MySQL
userテーブル：
簡単ですがこんな感じに入っています。
+------------+---------+
| name | initial |
+------------+---------+
| 山田花子 | や |
| 鈴木一郎 | さ |
| | |
+------------+---------+

###ソースコード

public int countKana(String initial) throws SQLException, ClassNotFoundException {

 Connection con = null;
 PreparedStatement pStm = null;
 int countKana = -1;
 
 try {

  /* DB接続 */
  Class.forName(bDAO.getDRIVER_NAME());
  con = DriverManager.getConnection(bDAO.getJDBC_URL(), bDAO.getDB_USER(), bDAO.getDB_PASS());

  /* SQL */
  String sql = "SELECT COUNT(*) AS countKana FROM user WHERE user.initial = '" + initial + "'";
  pStm = con.prepareStatement(sql);
  ResultSet rs = pStm.executeQuery();

  while (rs.next()) {
   countKana = rs.getInt("countKana");
  }

 } catch (Exception e) {
  e.printStackTrace();
  throw new SQLException(e);
 } finally {
  if (pStm != null) {
   try {
    pStm.close();
   } catch (Exception e) {
    e.printStackTrace();
    throw new SQLException(e);
   }
  }
  if (con != null) {
   try {
    con.close();
   } catch (Exception e) {
    e.printStackTrace();
    throw new SQLException(e);
   }
  }
 }

 return countKana;
}

Answer 1

Javaは詳しくないのですが、

while (rs.next())

これってひょっとして一行目スキップしちゃったりはしませんか？

---

元のSQLでも求める値を返していると思います。

SELECT COUNT(*) AS countKana FROM user WHERE user.initial = 'あ'

文字コードに問題なければ、直接DBで実行すれば「あ」の数が出力されるのでは無いでしょうか？

Comments

[Richiko]

ご回答ありがとうございます。

確かに元のSQL文でも値は返っているようです。
なのに変数に格納されないということは、一行目の値がとれていないということでしょうか･･･

[hirohiro]

while (rs.next())　は問題ないみたいです。
初期値は「最初の値の前」になってるそうで、いきなりnextを実行して問題ないようです。
ただ、javaは詳しくないので(２回目)間違ってるかも知れませんが、SQL実行周りのコードが多分変です。

まず、prepareStatementですが、これはSQLを先にコンパイルしておいて、変数を後から与える手法です。(Javaに限らず他の言語でも同じ概念を使います)
にもかかわらず、SQL中に変数を入れちゃっています。
別にこれでも動作しますが、prepareStatementにしている意味がありません。

次に、コンパイル済みのステートメントを実行するのは「pStm.executeQuery();」ではなく「pStm.executeUpdate();」のようです。
提示されたコードでは、空のSQLを実行してしまっているのでは？

参考：http://www.javadrive.jp/servlet/database/index10.html

「executeQuery();」ならこう
sql = "SELECT COUNT(*) AS countKana FROM user WHERE user.initial = 'あ'";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);

「executeUpdate()」ならこう
String sql = "SELECT COUNT(*) AS countKana FROM user WHERE user.initial = ？”;
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, "あ");
ResultSet rs = pstmt.executeUpdate();

[Richiko]

ありがとうございます。
確認して、いただいた通りのソースに訂正してみました。

```
String sql = "SELECT COUNT(*) AS countKana FROM user WHERE user.initial = 'あ'";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
countKana = rs.getInt("countKana");
}
```
しかしながらやはり変数の値は0になります…。
列名ではなくインデックスナンバーでも取得できるらしいので、
rs.getInt(1)にもしてみましたが変わりません。
せっかく調べてくださったのに申し訳ないです…。

[hirohiro]

>変数の値は0になります
-1で初期化してるようですので、0を代入する処理はどこかで実行された？
でもDBで直接実行する「SELECT COUNT(*) AS countKana FROM user WHERE user.initial = 'あ'」の結果はゼロではないのですよね？
謎ですね。whileの中で何かデバックメッセージ「System.out.println(countKana);とか？この辺りはjavaが分からないのでなんとも...」を出力するとちゃんど出力されますか？その際、「rs.getInt("countKana")」の値もついでに出力したら、やっぱり0が出力されるのでしょうか？

[Richiko]

> -1で初期化してるようですので、0を代入する処理はどこかで実行された？
調べたところ、intの場合値が取れない（＝null）の場合、0でかえってくるようです。
データベースに直接SQL文を書くときちんと件数が返ってくるので、変数に代入できていないんだと思います。

>whileの中で何かデバックメッセージ「System.out.println(countKana);とか？この辺りはjavaが分からないのでなんとも...」を出力するとちゃんど出力されますか？
>その際、「rs.getInt("countKana")」の値もついでに出力したら、やっぱり0が出力されるのでしょうか？

while(rs.next()){
countKana = rs.getInt("countKana");
System.out.print(countKana);
}

と記載して動かしてみると、コンソールには0が表示されます。

[hirohiro]

なるほど、ということはrsに何かResultSet型のオブジェクトが入っていることは確かなようですね。
そうなると、getIntで取得できないのか、rsにSQLの戻り値がちゃんと入っていないかのどちらかということになりそうです。

「SELECT COUNT(*) AS countKana, 1 AS n FROM user WHERE user.initial = 'あ'」
こんなSQLに変えて、getInt('n')を出力して「1」が表示されれば、rsにSQLのリターン値が入っている事が期待できます。(逆にこれも0だとrsが空オブジェクトの可能性が...）

もしデータは正常なのに0が取得されているだけの場合、
「java　件数　count(*)」でグーグル検索するとgetString(1)で取得している例文がありました。(しかしgetInt()で取得している例文もありました)

[Richiko]

ありがとうございます。
遅い時間になってしまったので、本日は休ませていただきます。質問をしている身でありながら申し訳ございません。
また明日の夜、結果をご報告させていただきますね。

[Richiko]

こんばんは。お返事が遅くなりまして申し訳ありません。

いただいたSQLで確認をしてみたところ、コンソールに「1」と表示されました。
値は返ってきているのかな、と思います。

またgetString(1),getString("countKana")どちらも試してみましたが、やはり取れる値は0でした；
せっかく調べてくださったのに申し訳ありません。

[hirohiro]

あらー？これもうちょっと解らないですね。
スミマセン後思いつくのはこのくらいです。
・キー名のスペルミスとか
・count(*)の値が本当にゼロだとか

前のSQLでn=1が取れたなら、次のだとどうなのかは興味あります。
1.　「SELECT 1 AS countKana FROM user WHERE user.initial = 'あ'」
　　※count(*)が機能していない可能性の検証

2.　「SELECT COUNT(*) AS n FROM user WHERE user.initial = 'あ'」
　　※要素キー名が何か悪かった、もしくはjava側でタイプミスがある可能性の検証

3.　「SELECT COUNT(initial) AS countKana FROM user WHERE user.initial = 'あ'」
　　※ちょっと有り得ないけど「*」が手違いでエスケープされている可能性の検証

[hirohiro]

あ、これ、文字コードがjavaとDBで異なってませんか？
「SELECT COUNT(*) AS countKana FROM user」
もしこれで数値が取れたら、「あ」がバイナリコードではDBとプログラムで違っている可能性があります
※つまり「user.initial = 'あ'」の行が1件も存在せず、0であっている

察するにDBも作ったばかり、javaの環境も作ったばかりですよね？
既に日本語を利用したDBがらみの開発を行っている環境なら考えられませんが
「これからはじめる」状態だとおおいにありえます
・javaプログラム記述ファイルの文字コード
・java実行時の文字コード
・DBに初期データを入れたエディタの文字コード
・DBの該当テーブルの文字コード
・「user.initial=''」の値を外部(HTML等)から取り込んでいる場合はその文字コード
どれかが異なっただけで、今回の問題が発生する可能性が出てくると思います。

[Richiko]

何回もありがとうございます。

1のSQL文　→　エラーとなります。
警告: The web application [address] registered the JDBC driver [com.mysql.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered. [火 3 01 00:13:40 GMT+09:00 2016]

2のSQL文　→　nの値は0で返ってきます。
3のSQL文　→　countKanaの値は0で返ってきます。

******************

文字コードの件ですが、その考えはまったく頭にありませんでした。
調べながらになると思いますが、一度確認をとってみます。

大変申し訳ございませんが、また明日、結果をご報告させていただきます…！

[Richiko]

こんばんは。

本日、以下のようにgetConnectionの引数のJDBCのURLを訂正してみたところ、
無事に値をとることができました。

[修正前]
jdbc:mysql://localhost/address

[修正後]
jdbc:mysql://localhost/address?useUnicode=true&characterEncoding=utf8

原因はご指摘いただいた通り文字コードでした。
大変助かりました、本当にありがとうございました！！

[nabe3]

文字コードの件が出た時にやっぱりそこだなって思いました。
無事解決できて良かったです。
また、的外れな回答をして遠回りさせてしまい申し訳ありませんでした。

[Richiko]

いえいえ、こちらこそ数日つきあわせてしまい申し訳なかったです；
nabe3さんも誠にありがとうございました。

Answer 2

hirohiroさんのおっしゃっている通り、文字コードの影響も大いにあり得ます。
また、別の可能性として…

メソッド定義でinitialを一つに限定していることから、下記の繰り返しは不要ではと感じます。
また、rs.getInt(int columnIndex)はcolumnIndex番目の項目のintデータを取得するメソッドです。
項目名での指定はエラーになるのではないのでしょうか？

  /* 質問者ソースコピー */
  while (rs.next()) {
   countKana = rs.getInt("countKana");
  }

kurokoSinさんの回答に対するコメントで、SQL文が固まったようなのでそれを適用すると、
以下のようになるのではないでしょうか？

 /* 例 */
 /* DB接続 */
  Class.forName(bDAO.getDRIVER_NAME());
  con = DriverManager.getConnection(bDAO.getJDBC_URL(), bDAO.getDB_USER(), bDAO.getDB_PASS());

  /* SQL */
  String sql = "SELECT COUNT(*) AS countDB FROM user GROUP BY initial HAVING initial = '" + initial + "'";
  
  pStm = con.prepareStatement(sql);
  ResultSet rs = pStm.executeQuery(); /* 実行の結果、1行だけの結果が返るはず… */

  rs.first(); /* カーソルを先頭に移動 */
  int columnIndex = 1;
  try { 
   /* sql実行結果、該当のイニシャルがある場合 */
   countKana = rs.getInt(columnIndex);
  } catch (Exception e) {
   /* sql実行結果、該当のイニシャルがない場合 */
   countKana = 0;
  }

以上、参考になりますでしょうか？

Comments

[Richiko]

ありがとうございます。
値が返らない原因は、文字コードだったようです。

rs.first()を使って値をとる方法は知らなかったので、大変勉強になります。
こちらも活用させていただきます。

誠にありがとうございました。

Answer 3

/* SQL */
  String sql = "SELECT COUNT(*) AS countKana FROM user WHERE user.initial = ?;"
  pStm = con.prepareStatement(sql);
  pStm.setString(initial);
  ResultSet rs = pStm.executeQuery();

prepareStatementつかうなら、SQL文の条件部分は「?」として、
パラメータをprepareStatementオブジェクトにセットしないとダメじゃないかな。

参考まで
https://docs.oracle.com/javase/jp/6/api/java/sql/PreparedStatement.html

あとちなみにですが、DB設計について
イニシャルカラムを持たせるなら
ふりがなカラムを持たせて前方一致検索するのも
良いかなと思いました。
（前方一致検索ならINDEXが効くはずなので）

Comments

[Richiko]

ありがとうございます。

PrepareStatementに関しては仰るとおりで、私の間違った使用方法でした。
ふりがなで前方一致検索は思いつきませんでした、ぜひ考えてみたいと思います。
ご指摘ありがとうございます。

また原因は文字コードが異なっていたからのようでした。
ご尽力に感謝いたします、ありがとうございました。

Answer 4

SELECT initial, COUNT(*)
  FROM users
 GROUP BY initial

でどうでしょうか？
initialでグループ化し、その件数を求めるSQLです。

Comments

[Richiko]

ご回答ありがとうございます。
いただいたSQL文を参考に以下のようにデータベースに指示を送ってみました。

SELECT COUNT(*) AS countDB FROM user GROUP BY initial HAVING initial = 'あ'

これで正しいあ行の総数の値はかえってきました。
ありがとうございました。

ですが、相変わらず変数countKanaには正しい結果が格納されず、-1のままとなっています。
もしよろしければ、こちらの原因も回答いただけると幸いです。

重ねてのお願いで申し訳ありません。
どうかよろしくお願いいたします。

[kurokoSin]

SQL文の最後に「;(セミコロン)」はつけていますでしょうか？
質問には記載なかったのでちょっと気になった程度ですが。。。

こんな感じです。
例）String sql = "SELECT COUNT(*) AS countKana FROM user WHERE user.initial = '" + initial + "';";

[Richiko]

すみません、セミコロンはついていません。
つけてから再度実行してみましたが、結果は同じになりました。。。

[kurokoSin]

質問になってしまいますが、教えてください。
conの中がnullだったり、想定外の接続先になっていたりとかしていないでしょうか？

countKanaが-1ということは、1回もSQLの結果を受け取れていないと思われるので、
SQLを実行する前の準備段階に原因があるのではと疑っています。

[Richiko]

同じコードで、SQLを以下のようにすると値が取れます。

SELECT COUNT(*) AS countKana FROM user;

なのでデータベースには接続できていると思います。

でもSQLを変えると値が取れるということは、やはりSQLが悪いということでしょうか；

[kurokoSin]

おそらくその通りだと思います。
試しにSQL文組み立てる直前の行でデバッグ出力してみると
（```System.out.println(initial);```）initial変数には何が格納されているのでしょうか

[Richiko]

initialには「あ」の文字が入っています。
メソッドを呼び出す際に引数として受け渡されるので、その他には「か」「さ」…などが入る形になります。

コンソールにも、「あ」と表示されております。

＊＊＊＊
本日は遅くなりましたので、ここで休ませていただきます。
質問をしている身でありながら申し訳ございません。
引き続き明日の夜、ご質問をさせていただきたいと思います。
どうかよろしくお願いいたします。

Answer 5

userテーブルの各カラムの型はどうなってますか？

Comments

[Richiko]

ありがとうございます。
カラムinitialの型は、varchar型です。
他には、name,address,emailがありますが、こちらはvarchar型
id,genderがint型にしています。

不具合の原因は文字コードだったようです。
お手数をおかけいたしました。
ご尽力に感謝いたします。ありがとうございました。

Answer 6

while(rs.next()){
syso("aaa");
}
と入力すると、何回aaaは出力されるでしょうか？
複数回呼ばれているようなら、rsがおかしいと思い聞いてみました。

Comments

[Richiko]

ありがとうございます。

> System.out.println(rs.getString(0));

こちらですが、動かすと以下のSQLエラーが出ます。

java.sql.SQLException: Column Index out of range, 0 < 1.

添え字0は存在しない、ということでしょうか。。

[Richiko]

何度もありがとうございます。
aaaは一度だけコンソールに出力されます。

[uniuni]

Stringの変数に代入する際、「*」を「\\*」にしてみてもだめでしょうか？

Stringの*と、MySQLの*とが意味が異なるため、聞いてみました。

SELECT COUNT(*)が、SELECT COUNT(()として扱われているのではないかと。

[Richiko]

不具合の原因は文字コードだったようです。
大変お手数をおかけいたしました。
何度もありがとうございました！

Answer 7

SQLのCOUNT(*)の結果は1行なので、

  /* 修正前 */
  while (rs.next()) {
   countKana = rs.getInt("countKana");
  }

↓

  /* 修正後 */
  rs.next()
  countKana = rs.getInt("countKana");

と修正して確認してみてください。

Comments

[Richiko]

ご回答ありがとうございます。

いただいたソースに修正してみましたところ、

countKana = rs.getInt("countKana");

に対して
java.sql.SQLException: Illegal operation on empty result set.
が出てしまいます。

[nabe3]

他の方とのやり取りを参考に別の視点で考えてみました。
以下のページにCOUNT(*)で件数を取得するサンプルがあります。
以下のサンプルでは、COUNT(*)に対してASで別名を与えていません。
また結果の取得に[int count = rs.getInt(1);]のようにしています。
これを踏まえて以下の2点を変更してみてどうでしょうか。

(1)SQL文
変更前：String sql = "SELECT COUNT(*) AS countKana FROM user WHERE user.initial = '" + initial + "'";
変更後： String sql = "SELECT COUNT(*) FROM user WHERE user.initial = '" + initial + "'";

(2)結果取得部分
変更前：countKana = rs.getInt("countKana");
変更後：countKana = rs.getInt(1);

参考ページ：
https://docs.oracle.com/javase/jp/1.4/guide/jdbc/getstart/resultset.html
5.1.4 結果セット内の行数の判定

[Richiko]

ありがとうございます。
ご提案の通りに変更して動かしてみましたが、やはり0のままです。

根本的にこの方法では実現不可能なのかも、と少し諦め気味です；

[nabe3]

こちらに件数取得の3つの例が掲載されています。

java.sql.ResultSetで取得したデータの数を得たいのですが？
http://homepage1.nifty.com/docs/java/faq/S135.html#S135-02

この例の2つ目のやり方が今回のやり方なのですが、
3つ目の[rs.last();]、[rs.getRow();]が使えるなら良いですが、
もしダメなら地道に1つ目の例のようにループして取得するしかないでしょうか・・・。
レコード数が多いとレスポンスに問題が出そうなのでお勧めできませんが。

もしCOUNT(*)になんらかの不具合があるのでしたら、SQLを変えて、
SELECT sum(1) AS countKana FROM user WHERE user.initial = 'や';
のようにして見つかったレコード数の合計数を取得して値が取れるでしょうか？

[Richiko]

何度もありがとうございます。
SQLを変えてみましたが、結果は0と返ってきました。

もしかしたらSQLやJavaの問題ではなく、下でhirohiro様がご指摘くださった文字コードの問題かもしれないと思えてきました。
そちらも含めて、明日になってしまいますが結果をご報告させていただきます。
どうかよろしくお願いいたします。

[Richiko]

こんばんは。

不具合の原因は文字コードだったようです。
数日の間おつきあいいただき、誠にありがとうございました。
ご尽力に感謝いたします。

Answer 8

あんまり自信が無いのですが・・・

while (rs.next()) {
   countKana = rs.getInt("countKana");
}

先にrs.next()を呼び出しているためではないかと思います。next（次は無いですから・・・）
countKana = rs.getInt("countKana");だけならどうでしょう？

Comments

[Richiko]

ご回答ありがとうございます。

while(rs.next())を削除した場合、java.sql.SQLExceptionが出て、
動作しなくなってしまいました。

[cateye]

申し訳ない＾＾；
nabe3さんのほうが正解かも？

[Richiko]

とんでもないです。
私こそ全くわかっていないので；；
どうもありがとうございました。