Home > Tags > PostgreSQL

PostgreSQL

WTPのTomcat7.0でDataSource(PostgreSQL)からConnectionを取得する

コネクションプーリングをするために、DataSourceからコネクションを取得しようと思ったら、かなりハマってしまったので、備忘録です。

手順は以下。PostgreSQLの例ですが、大体どのDB製品でも同じだと思います。

  1. (サーバの)server.xmlにResourceを定義
  2. (アプリケーションの)web.xmlにresource-refを定義
  3. JDBCドライバを配備
  4. コネクション取得

(サーバの)server.xmlにResourceを定義

まずserver.xmlにResourceを定義します。WTPでサーバを動かしている場合は、以下のようにServersプロジェクト内にserver.xmlがあると思います。

server.xml
server.xml

この中にアプリケーションのContextタグがあると思うので、そこにResourceを追加する。
ここが、Tomcat 6.0と記述が異なっていたので、かなりハマってしまいました。しっかりマニュアルのほうを読まないといけませんね。

server.xml

<Context docBase="Sample" path="/Sample" reloadable="true" source="org.eclipse.jst.jee.server:Sample">
	<Resource name="jdbc/postgresql" auth="Container" type="javax.sql.DataSource"
		driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost/SampleDB" username="postgres"
		password="xxxxxx" maxActive="20" maxIdle="10" maxWait="-1" />
</Context>

(アプリケーションの)web.xmlにresource-refを定義

次にアプリケーション内のweb.xmlにresource-refを定義します。サーバのweb.xmlではないので注意。

web.xml

<resource-ref>
	<res-ref-name>jdbc/postgresql</res-ref-name>
	<res-type>javax.sql.DataSource</res-type>
	<res-auth>Container</res-auth>
</resource-ref>

JDBCドライバを配備

WTPでサーバを起動している場合は、サーバプログラム本体のcommon/libにいくらjarを置いてもクラスパスには追加されません。
WTPでjarを追加するには以下のようにします。

プロジェクト・エクスプローラーを右クリック → 実行(実行構成) → クラスパス → ユーザー・エントリでjarを追加

コネクション取得

これで準備ができたので、実際にServletからコネクションを取得してみます。

SampleServlet

package jp.co.njk.nems.controller.authentication;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@WebServlet("/SampleServlet")
public class SampleServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    private Connection connection;

    public void init(ServletConfig config) throws ServletException {
        try {
            InitialContext context = new InitialContext();
            DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/postgresql");
            connection = dataSource.getConnection();

        } catch (NamingException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        System.out.println(connection);
    }
}

参考

よく使うPostgreSQLコマンド

テーブルの中身を空にする

truncate table テーブル名;

日付関数

select now();
select current_date;
select current_timestamp;

シーケンス関数

シーケンス名を「users_id_seq」としてます。

// 次のシーケンス値を取り出す
select nextval('users_id_seq');

// 現在のシーケンス値を取り出す
// ただし、この関数を呼ぶ前に同じセッションで同じシーケンスに対してnextval('シーケンス名')を呼び出していないと、エラーになる
select currval('users_id_seq');

iBATIS入門

導入

iBATISのインストール

インストールといってもjarファイルをクラスパスに追加するだけです。
以前は以下のような3つのファイルに分かれてたみたいですが、今は1つに集約されてます。

  • ibatis-common-2.jar
  • ibatis-dao-2.jar
  • ibatis-sqlmap-2.jar

こんな感じ(/libフォルダに入ってます。)

  • ibatis-2.3.4.726.jar

http://ibatis.apache.org/ からダウンロード。

あと今回はDBにPostgreSQLを使用するので、PostgreSQLのJDBCドライバもクラスパスに追加しておく。
ちゃんとJDKのバージョンに対応したドライバを使うこと。詳しくはPostgreSQL JDBC ドライバを使用する時の注意点を参照。
http://jdbc.postgresql.org/ からダウンロード。

DBの準備

DBにはPostgreSQLを利用します。
サンプルとして以下のようなテーブルを作成(DB名はsampledbとしています)

id name age
1 Yoshida 23
2 Abe 34
3 Ogawa 17
CREATE TABLE users (
    id serial NOT NULL,
    name character varying(100),
    age integer,
    CONSTRAINT id PRIMARY KEY (id)
)

※ちなみにnameというのはPostgreSQLでは予約語らしいですが。。。

フォルダ構成

ちなみにフォルダ構成はこんな感じにしています。

iBATIS(フォルダ構成)

使ってみる

1. SqlMapConfigを作成する。

まずiBATIS用configファイルを記述します。
DB接続設定やマッピングファイルの参照を書いていきます。

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <!-- DB接続設定 -->
    <transactionManager type="JDBC">
        <dataSource type="SIMPLE">
            <property name="JDBC.Driver" value="org.postgresql.Driver"/>
            <property name="JDBC.ConnectionURL" value="jdbc:postgresql://localhost:5432/sampledb"/>
            <property name="JDBC.Username" value="postgres"/>
            <property name="JDBC.Password" value="postgres"/>
        </dataSource>
    </transactionManager>

    <!-- sqlMapファイルの参照 -->
    <sqlMap resource="ibatis/dao/user.SqlMap.xml" />
</sqlMapConfig>

2. SqlMapConfig.xmlを読み込むクラスを作成する。

DBアクセスがあるたびに毎回SqlMapConfig.xmlを直接読み込んでもいいんですけど、DBアクセスするたびにファイルを開いたりしてたら大変だよね、っていう話で一回だけ読み込んでインスタンスを作成・保持しておくということをしておきます。
こういうのをデザインパターンで「シングルトン(Singleton)」というらしいです。

MyAppSqlConfig.java

package ibatis;

import java.io.IOException;
import java.io.Reader;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

/**
* SqlMapClientを返すクラス
*/
public class MyAppSqlConfig {
    private static SqlMapClient sqlMap;

    static {
        try {
            String resource = "SqlMapConfig.xml";
            //シングルトンとして利用
            if (sqlMap == null) {
                Reader reader = Resources.getResourceAsReader(resource);
                sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlMapClient getSqlMapInstance() {
        return sqlMap;
    }
}

3. DTOを作成する。

DBのテーブルに対応したDTOクラスを作成しておきます。

User.java

package ibatis.dto;

public class User {
    private int id;
    private String name;
    private int age;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

4. マッピングファイル(SQL Mapファイル)を作成する。

ここでJDBCだとDBアクセスのためのDAOクラスを作るんですが、iBATISではそれをマッピングファイルとしてXMLで記述します。

user.SqlMap.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="User">
    <!-- SELECT -->
    <select id="getUser" parameterClass="Integer" resultClass="ibatis.dto.User">
        SELECT
            ID,
            NAME,
            AGE
        FROM
            USERS
        WHERE
            ID = #VALUE#
    </select>

    <!-- UPDATE -->
    <update id="updateUser" parameterClass="ibatis.dto.User">
        UPDATE USERS
        SET NAME = #name#
        <dynamic>
            <isNotEqual prepend="," compareValue="0" property="age">
                AGE = #age#
            </isNotEqual>
        </dynamic>
        WHERE id = #id#
    </update>

    <!-- DELETE -->
    <delete id="deleteUser" parameterClass="Integer">
        DELETE
        FROM USERS
        WHERE ID = #id#
    </delete>

    <!-- INSERT -->
    <insert id="insertUser" parameterClass="ibatis.dto.User">
        INSERT INTO USERS (
            ID,
            NAME,
            AGE
        ) VALUES (
            #id#,
            #name#,
            #age#
        )
    </insert>
</sqlMap>

5. 実際に動かしてみる。

動かしてみます。

Sample1.java

package ibatis;

import ibatis.dto.User;
import com.ibatis.sqlmap.client.SqlMapClient;
import java.sql.SQLException;

public class Sample1 {

    public static void main(String[] args) throws SQLException {
        // SqlMapClientを生成
        SqlMapClient sqlMap = MyAppSqlConfig.getSqlMapInstance();

        // SELECT
        User user = (User) sqlMap.queryForObject("getUser", 1);
        System.out.println(user.getId() + "\t" + user.getName());

        // UPDATE
        user.setId(2);
        user.setName("Yuzawa");
        user.setAge(27);
        sqlMap.update("updateUser", user);

        // INSERT
        user.setId(4);
        user.setName("Morita");
        user.setAge(30);
        sqlMap.insert("insertUser", user);

        // DELETE
        sqlMap.delete("deleteUser", 3);
    }
}

以上を実行すると、テーブルが以下のようになり、

id name age
1 Yoshida 23
2 Yuzawa 27
4 Morita 30

実行結果は以下のようになる。

1	Yoshida

参考

PostgreSQL JDBC ドライバを使用する時の注意点

使用するJDKのバージョンによって、使えるドライバが違うので注意する。
違うドライバを使うと実行エラーが起きる。

JDKのバージョン 対応ドライバ
JDK 1.4, 1.5 JDBC 3
JDK 1.6 JDBC 4

詳細は以下で確認。
http://jdbc.postgresql.org/download.html

Home > Tags > PostgreSQL

Search
Feeds
Meta

Return to page top