Challenge Engineer Life !

エンジニア人生を楽しみたい!仕事や趣味で学んだ技術的なことを書いていくブログです。

NetBeans 8.2のSQL Profiler機能

ふとTwitter眺めていたら以下情報が流れてきました。

NetBeans SQL Profilerとな!

せっかくなので、私もnightly buildを落として実際に触ってみました。
ちなみに次のNetBeans、Docker対応なんかもあるっぽいです。以下アイコンがありました。NetBeans 8.2で入る予定の機能とかは最後簡単に触れます。

f:id:kikutaro777:20160504172816j:plain

NetBeans IDE Build 201605020002インストール

Nightly Build版は以下から取得しました。

Index of /dev/nightly/

最初「NetBeans IDE Build 201605030002」を落としたのですが、手元のWindows 8.1だと以下エラーに(^^;

f:id:kikutaro777:20160504171618j:plain

何回かDLしたけど同じだったため、1つ古い「NetBeans IDE Build 201605020002」をインストールしました。

SQL Profiler

デフォで入ってるJava DBのサンプルDBと、NetBeansのコード自動生成で簡単なDB CRUDのJava EEを作成して試しました。

SQL Profilerの設定

スクショ取るのが手間だったので…以下gifアニメにて。使い方はNetBeansが持ってる通常のプロファイラ機能とほとんど一緒です。SQL Profilerを選ぶだけ。

f:id:kikutaro777:20160504165318g:plain

DBデータの一覧表示

Webアプリ側でDBの一覧表示をすると、実行されたSQLがガガガーっと表示されました。ほー。

f:id:kikutaro777:20160504170115g:plain

SQL実行の実行時間や呼出し回数などが表示されています。

f:id:kikutaro777:20160504172259j:plain

DBデータの更新

以下のキャプチャはinsertだけですが、当然deleteなども同じように表示されました。

f:id:kikutaro777:20160504165614g:plain

実行されたクエリも「View SQL Query」を選ぶと

f:id:kikutaro777:20160504172545j:plain

以下、確認できます。

f:id:kikutaro777:20160504172552j:plain

NetBeansロードマップ

NetBeansのロードマップは以下ページで確認できます。

netbeans.org

次の8.2は今年2016年の8月リリースを目指して動いてるようです。JDK9への対応具合などは多分Java Day Tokyo 2016JJUG CCC 2016 Springなどで何らか少しは出てくるのではないかと。

8.2では以下の機能が挙げられています。

JShellやES6対応辺りは注目度高そう。また、冒頭キャプチャだけ載せたDockerも入ってます。
そして、Oracle JET…お、おう。

  • ECMAScript 6 support
  • NodeJS enhancements and updates
  • Oracle JET support enhancements
  • PHP7 support
  • JavaShell support (JDK REPL)
  • Docker Support
  • Editor Multicarets
  • Pinnable Watches
  • SQL profiling
  • CND updates
  • more bug fixing

Editor MulticaretsとかPinnable Watchesは何だろう…(^^;全然わからないのでどこかで調べてみたい。

Querydslを触ってみる

今のJava EE開発では、JPQLを書く際

  • 動的クエリ(Dynamic Query)
  • 名前付きクエリ(Named Query)

を使うようにしていて、本来、型のことなど考えるとCriteriaで書くべきだなんだろうな…と思いつつ、可読性や簡易性を優先して上記選択にしています。

ただ、どうしても動的クエリはStringBuilderなどでクエリをダラダラと連結することになって、わりとイケてないらしい…ですね。

ということで、前に@megascusさんがブログで紹介されていたQuerydslを思い出し、これから少し触っていこうかなと。

QuerydslでJPAが思ったよりも捗る

上記ブログ記事の中にあるサンプルコードや、公式サイトのチュートリアルに接頭辞「Q」が付いたEntityクラスみたいなものが出てくるので、なんなんだろあれ?と思ってたのですが、定義したEntityクラスをベースにQuerydslが(というか実際にはapt(Annotation Processing Tool))が自動生成するクラスなんですね。

ってかapt初めて知りました(^^;
ググるとき気を付けないとLinuxのaptが出てきそう…(^^;

……

そんなこんな調べつつ、QuerydlsのドキュメントからQuerying JPAのチュートリアルを参考に触ってみました。

Mavenによる依存性定義

pom.xmlは以下としました。

<dependency>
    <groupId>com.mysema.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>com.mysema.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
    <version>3.3.2</version>
</dependency>

公式のみるとslf4jも入れたほうが良いようですね(今回は省略しましたが一応動いてます)。

今日時点で最新の3.3.2は2014年3月24日にリリースされたものなので、わりと最近でもメンテされてるライブラリのようです。

3系の最新定義するなら上記pom.xmlのversionタグを

<version>[3,)</version>

のように指定してもいいかもしれません。

maven-apt-pluginの定義

コード自動生成のためにpom.xmlのbuildタグ内のpluginsで以下定義を追加します。

<plugin>
    <groupId>com.mysema.maven</groupId>
    <artifactId>maven-apt-plugin</artifactId>
    <version>1.0</version>
    <executions>
      <execution>
        <goals>
          <goal>process</goal>
        </goals>
        <configuration>
          <outputDirectory>target/generated-sources/java</outputDirectory>
          <processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
        </configuration>
      </execution>
    </executions>
  </plugin>

今回Java EE 7、GlassFish 4.0でサンプルを作りましたが、Entityの生成などはJava EE 6と同様にNetBeans任せで、データベースから自動的に作りました。

そしてビルドすると

f:id:kikutaro777:20140421223509j:plain

EntityのActress.java(NetBeansから自動生成したEntity)に対してQActress.java(Querydls&aptで自動生成)が出来ています。

テーブルサンプル

Actressテーブルは以下定義しました。

ID 名前 年齢
1 堀北真希 25
2 橋本環奈 15
3 橋本愛 18
4 能年玲奈 20
5 有村架純 21

サンプルコード

堀北さんをピンポイントで拾ってみます。EJBで

public Actress getHorikita(){
    QActress actress = QActress.actress;
    JPQLQuery query = new JPAQuery(em, EclipseLinkTemplates.DEFAULT);
    return query.
            from(actress).
            where(actress.name.eq("堀北真希")).
            uniqueResult(actress);
}
[ id:1, name:堀北真希, age:25 ]

橋本さんをlike文で検索して、名前でソート昇順に。

public List<Actress> getHashimoto(){
    QActress actress = QActress.actress;
    JPQLQuery query = new JPAQuery(em, EclipseLinkTemplates.DEFAULT);
    return query.
            from(actress).
            where(actress.name.like("橋本%")).
            orderBy(actress.name.asc()).
            list(actress);
}
[[ id:3, name:橋本愛, age:18 ], [ id:2, name:橋本環奈, age:15 ]]

成人した女優さん達は

public List<Actress> getAdult(){
    QActress actress = QActress.actress;
    JPQLQuery query = new JPAQuery(em, EclipseLinkTemplates.DEFAULT);
    return query.
            from(actress).
            where(actress.age.goe(20)).
            list(actress);
}
[[ id:1, name:堀北真希, age:25 ], [ id:4, name:能年玲奈, age:20 ], [ id:5, name:有村架純, age:21 ]]

goeは「>=」でgtが「>」。逆はloe、lt。

テーブルが超簡易なのであれですが、良い感じ!
もう少し複雑なクエリ組んでみよう。

これ採用してもいいんじゃないかなー。開発元はmysemaというフィンランドの企業がやっているらしい。

コードはGitHubで管理されてて、わりと活発そうです。
https://github.com/mysema/querydsl

発行されるJPQLをどうやって確認できるのかな?と思ったのですが、普通にNetBeansでも表示されてるっぽい。

f:id:kikutaro777:20140421233638j:plain

Java EEを利用したオフラインWebシステム構築にChallenge! ~その1~

少し前に「オフラインのWebシステム構築」を調査・検証・プロト作成することになりました(^^;

実際に動くモノがだいぶ形になったので、少しずつ書いていこうかなと。

まずは、そもそもなんでそんなことをすることになったか…の背景から整理してみます。

オフライン機能への需要

お客さんからよく聞く要望

自分の所属組織では日頃、案件管理や販売管理のシステムを主に受託開発しています。

昔はWindows Formなどで開発するのがメインでしたが、最近だとイントラでの利用であってもWebシステムを求められることが多くなり、直近で開発したものはほとんどWebシステムでした。

で、案件管理とか販売管理システムは、実際の利用者(エンドユーザ)の方が営業担当者であったりするため

「出先でネットにつながらない場合もあるので、オフラインでシステムを利用したい。」

と言った話をよく聞きます。

また、私は実際に触ったことはないのですが、Salesforce CRMにもそういった機能があるので、よくある話なのかなと。

どんな状況下でも仕事ができるようにならねばならないとは大変だなぁと思いつつ(^^;

自分達でも欲しいケース

一方で、自分達でも欲しいときがあります(^^;

それはオフラインというよりは、手軽に起動できるWebシステム、なのかもしれませんが。

例えば、営業さんのサポートでWebシステムをデモする場合など、自分がデモする際にはノートPCに環境整ってるし特に困ることはないです。

ただ、営業の方が突如「単独でデモしたいんだけど!!」となった場合

  • 操作動画でデモしてもらう
  • VPNでつないでもらう
  • デモ対象のWebシステムを公開サーバに置く
  • その人のノートPCに環境を構築する

などなど選択肢は色々ありますが、最後の選択肢を求める人が多い気がします。

で、それなりの時間をかけて

環境準備して…

DB入れて…

APサーバ立てて…

と、環境構築するだけでそれなりに手間がかかりますし

「マシン変えた」…「他の営業にも同じ構築を」…「お客さんに貸し出す」…

などなど。手順書渡したりしても失敗したり、インストーラ作っても環境によって失敗しちゃったり。

究極的には「コピペすれば動く」くらいにもっていけると…と思ったり(^^;

組込系を上手く使うことで、この辺りも実現できるかと。

実現方法は?

オフラインと聞いて、自分がまず思い浮かぶのはhtml5を利用して…Web Storageを利用して…といったイメージです。

去年参加したhtml5j conferenceで、まさにそういう内容の話を聞きました。
http://events.html5j.org/conference/2013/11/sessions

セッションの動画が公開されてました!↓

ただ、私のいる環境ではまだhtml5でシステム提供をした経験がないし、詳しい人もいない…。

そうなると、完全にクライアント専用のGUIを新たに構築する…といった選択肢?
元々クライアントがWindows Formなどで、Webサービスと通信、みたいなクラサバで作っていれば、少し頑張ればいける気がしますが(^^;

でもそもそも提供したのがWebシステムだったりすると、エンドユーザさんとしては日頃見慣れたブラウザでの画面で操作できるのが良いわけで…。

で、去年からJava EEをメインに開発しているので、それ前提にした場合、どのように構築できるか?を模索してきました。

そこで

  • Embedded GlassFish
  • 組込みデータベース

を利用しつつ、いくつかの条件が揃えば実現できるのでは?ということで、実際にJava EE6で構築した運用システムのオフライン化を検証していきました。

現在作ってるもの

最終的には以下のような構成図の仕組みです。

f:id:kikutaro777:20140207202741p:plain

わりと素直な構成だと思うのですが、一番の問題点はオンラインとオフラインで異なるベンダのデータベースとなっている点です。

今回オフライン構築対象のWebシステムでは、もともとMicrosoft SQL Server 2012がDBとして使われていました。

であればオフラインでは、SQL Server 2012のLocalDBとかを選択するのが素直なのですが、まだ対応したJDBCドライバがないっていう…(-_-;
まあLocalDBは開発用って感じで実際に運用で使えるかどうか、その辺りも見えないのですが;

ちなみに今後JDBCドライバを設ける予定はありそうな感じの情報も。
http://connect.microsoft.com/SQLServer/feedback/details/771612/jdbc-support-for-localdb

この「異なるベンダのデータベース」と聞いて思い出すのは、よくオラクルさんのJava EEセミナーなどでちょこちょこ聞く「JPAを使うことでデータベースの差異を吸収」「JPQLを使うことでクエリ変更も不要」という売りです(^^;

思想は理解しつつも、実際は大変だろうなぁ、ってかそもそも1つのシステム内で異なるDBを複数使うことはあまりないよなー、と聞き流していたのですが、まさか使うシチュエーションになるとはっ(^^;

でも実際に上記構築をしてみて、JPQLを利用してて良かった…と思いました。
1箇所だけNative Queryで書かれた部分があったのですが、幸いそこはJPQLに書き換え可能だったので書き換えることで問題なくいけました。

ただ、この辺に関しては色々条件がそろわないと駄目(利用してる型とかストアドがないとか)で、そこらへんはまた別途まとめようかなと思います。

JPQLで自己結合

開発の中で、自己結合(Self Join)したいケースがあって、JPQLでどう書くのかやってみました。
今回、あまり調べず試行錯誤してしまったので、あっているかあまり自信ない…(-_-;

とりあえず簡単なサンプルを作って確認しました。

テーブルは以下のような定義で、ロール(役割)階層を表現しています。

CREATE TABLE ROLE_TBL(
    ROLEID VARCHAR(32) PRIMARY KEY,
    ROLENAME VARCHAR(64),
    UPPERROLE VARCHAR(32),
    CONSTRAINT FK_ROLE FOREIGN KEY(UPPERROLE) REFERENCES ROLE_TBL(ROLEID)
)

イメージは単純に以下のような感じで、ロールに親子関係があるだけ。
親1に対して子供は多。

f:id:kikutaro777:20130422230341j:plain

データは

f:id:kikutaro777:20130422230354j:plain

と絵に合わせて積んでみます。

NetBeansでデータベースからエンティティを自動生成すると
(※今回のサンプルではApache Derby使いました)

@Entity
@Table(name = "ROLE_TBL")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "RoleTbl.findAll", query = "SELECT r FROM RoleTbl r"),
    @NamedQuery(name = "RoleTbl.findByRoleid", query = "SELECT r FROM RoleTbl r WHERE r.roleid = :roleid"),
    @NamedQuery(name = "RoleTbl.findByRolename", query = "SELECT r FROM RoleTbl r WHERE r.rolename = :rolename")})
public class RoleTbl implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 32)
    @Column(name = "ROLEID")
    private String roleid;
    @Size(max = 64)
    @Column(name = "ROLENAME")
    private String rolename;
    @OneToMany(mappedBy = "upperrole")
    private Collection<RoleTbl> roleTblCollection;
    @JoinColumn(name = "UPPERROLE", referencedColumnName = "ROLEID")
    @ManyToOne
    private RoleTbl upperrole;
//以下省略

こんな感じで生成されていました。RoleTblのコレクションと単一のRoleTbl持つんだ…。うーむ。

とりあえず、Entityはそのままにして、クエリで、自分+親、のEntityを一緒に取ることをやってみました。

JPQLクエリは

SELECT r,ru FROM RoleTbl r LEFT OUTER JOIN r.upperrole AS ru

って書いて、NetBeansのJPQLツールで実行すると

f:id:kikutaro777:20130422230725j:plain

取れた(^^;あってんのか。
ちなみに外部結合にしているのは、親のいないCEOも含めて取りたいからです。

最初、WHERE句で「r.upperrole.roleId = ru.roleId」とか色々書いては実行エラーで怒られ続けたけど、rとruは既に関係を持ってるので不要…?と思ったら当たりっぽい。

JPQL、やはり細かい所になってくると難しいです(^^;
JSR317の仕様を調べつつ、トライして、って感じで。

ちょっと自信ないのでググってみよう;

JPA2.0のJPQLではON句が使えない

知らなかった…JPA2.0ではON句が使えないのですね…ということでメモ。
JSR317のドキュメントで外部結合のところをみると例えばLEFT JOINでは以下の例があります。
※OUTERはオプションなので記述なくても同じ

SELECT c FROM Customer c LEFT OUTER JOIN c.orders o WHERE c.status = 1

このc.ordersがリレーションしてるものだから書けるようで、例えば別のテーブルで

SELECT e FROM Employee e LEFT JOIN MailingAddress a ON e.address = a.address

みたいなのは書けない…。ただし、EclipseLinkの2.4では、JPA2.1のdraftとして上記ON句の記述ができるようになってるようです。

EclipselinkでのJPQL ON句

さてさてどうしたもんか…。

JPQLインジェクションを確認してみました

Java EE6を学んで、JPAを学んで、JPQLを知ったとき

「Native Queryを使っていない場合、通常のSQLインジェクションされにくいよなぁ」

と個人的に思ったりしたことがあります(^^;JPQL構文で攻撃する人は少ないだろうなと。

でもそんな甘い考えが通じない世界なわけで、ちゃんと確認しておこうと思って以下やってみました。

以下のBookテーブル、レコードがあります。

ID 書籍名 著者
1 Horikita MakiMaki
2 Aoi Miyiazaki

プログラムでは以下2つのメソッドを用意しました。

Prepared Statement(プレースホルダ、バインド変数)なしのJPQL
public int queryBookNotParameterized(String param){
    Query query = em.createQuery("SELECT b FROM Book b WHERE b.title = '" + param + "'");
    List<Book> books = query.getResultList();
    return books.size();;
}
Prepared Statement(プレースホルダ、バインド変数)ありのJPQL
public int queryBookParameterized(String param){
    Query query = em.createQuery("SELECT b FROM Book b WHERE b.title = :param");
    query.setParameter("param", param);
    List<Book> books = query.getResultList();
    return books.size();
}
呼び出し
System.out.println(bookFacade.queryBookNotParameterized("' OR 'X' = 'X"));
System.out.println(bookFacade.queryBookParameterized("' OR 'X' = 'X"));
結果

f:id:kikutaro777:20130317011035j:plain

プレースホルダなしの場合はJPQLインジェクションが成功してしまいました(^^;
入力した文字列(' OR 'X' = 'X)は通常よくあるSQLインジェクションと変わらないので、「JPQLだから大丈夫」とはいえない感じです。

同じこと考える人は当然いるわけで、海外にも情報ありました。

というわけで、ちゃんと考えて実装しないと、というお話でした(^^;

JPQLを利用する際に行っていること

今回の開発では初めてJava EE6を触り、初めてORマッパーを触るので、DBまわりや性能は結構気になる所です(^^;

ちょくちょく「ORマッパー使うなんて危ないねぇ」なんて周囲にも言われたりもしますが、JPAやEclipseLinkをキーワードに海外での情報も多く、Java EE6としてもJPAを利用することが標準(ですよね!?)だと思っているので、とりあえずは開発段階で色々と気を付けながら使ってみています。

JPQLはメインで動的クエリを採用(Criteriaを利用したかったのですが…断念)し、非常に簡単なものはNamed Queryを利用してます。

で、タイトルにある「JPQLを利用する際に行っていること」ですが、あまり大それたことではありません。ただ、NetBeans7.3になってから組み込まれたツールのおかげで、作業が劇的に軽減されたので、手順をまとめておこうかなと。

Java Persistence JPQL testing toolでJPQLクエリを組む/あるいはNetBeansの補完を利用しながらJPQLクエリを組む

以前に「NetBeans7.3で嬉しかったこと!」のエントリで書いたのですが、このツールは本当に便利です。

f:id:kikutaro777:20130311221804j:plain

ツール上でも(Ctrl+Shiftかなぁ)補完もちゃんと効きます。ただ、たまにご機嫌斜めになることもあるけど…(^^;気のせい?

f:id:kikutaro777:20130311223228j:plain

プレースホルダ(バインド変数)のところは一旦tool上、手で書いたりします。

実行して結果を確認する

tool上で実行して確認できるので、修正したり、実験したり、色々できます。

f:id:kikutaro777:20130311221813j:plain

実クエリを取得する

SQL」タブ(タブなのかな…?)で実際に利用しているDBMSに合わせたクエリが発行・表示されるので、これをコピーします。前まではログから毎回拾ってて、辛かった…(^^;

f:id:kikutaro777:20130311222057j:plain

DBに実クエリを投げて実行計画解析(explain等)を行う

以下はMS SQL Serverの例ですが、Oracleや他DBでも分析機能があるので、それを利用してコストとかインデックス効いてるかとかチェックします。

f:id:kikutaro777:20130311222107j:plain

で、上記をなるべく大量データで行っています。

あとはArqullian Persistence Extensionでも大量データ+テスト時にテストレコード追加、で確認を行うため、性能的に異常なケースが出れば割と早期に発見は可能かなと(^^;

さらにJMeterSelenium等で負荷試験を行っていく予定です。

実際の運用になると色々問題は出るかもしれないけれど…色々初めてすぎてやってみないとわからない(^^;

JPQLのLIKE式で%や_を含んだ検索をする

開発が進んで、いよいよJPQLの世界にどっぷり入っていく必要がありそうです。
今の開発では、動的クエリをメインに利用する方針にしています。

間違い防止やスキーマ変更に強くするためにもCriateria APIを利用したい所ですが、業務系のシステムではクエリが複雑になるケースも多々あるため、可読性や効率性を選んで今回Criteriaは止めました(^^;

あれがゴリゴリ書ける人は本当すごいと思います。。。

さて、今日出会った小さな壁は、検索処理の際、パーセント(%)やアンダースコア(_)を含んだ検索をしたい、というものです。

例えば普通に

SELECT o FROM Opportunity o WHERE o.opportunityName LIKE '商談%'

とJPQLを書いて検索すると

商談ほにゃらら
商談hogehoge

みたいな商談名が拾えます。普通のLIKE式です。

で、今回は以下のような商談名があった場合

この商談は駄目確率0%
100%上手くいく商談

検索で「0%」を含むやつ、とか、「100%」で始まる、とか検索したい、みたいな。

金魚本みたのですが情報はなく、今回は珍しく色々ググる前にJSRをみてみました(^^;
http://jcp.org/en/home/index

JPAのspecの中にLike Expressionsのページがあり、かなりわかりやすい例でExamplesがあって、すぐ解決しました。

SELECT o FROM Opportunity o WHERE o.opportunityName LIKE '%0\%' ESCAPE '\'
SELECT o FROM Opportunity o WHERE o.opportunityName LIKE '100\%%' ESCAPE '\'

f:id:kikutaro777:20130228230745j:plain
f:id:kikutaro777:20130228230752j:plain
上記ウィンドウはNetBeans 7.3から入ったJava Persistence JPQL testing toolの結果ウィンドウです。これがあるとないでは開発効率に差が出る気がします(^^;
過去エントリで少し触れています→「NetBeans7.3で嬉しかったこと!

エスケープでいける、ってことで通常のSQLと大差ない感じのようです。
上記例は、%のみですが、アンダースコア(_)も同じです。

にほんブログ村 IT技術ブログへ
にほんブログ村 にほんブログ村 IT技術ブログ Javaへ
にほんブログ村