JDBC接続確認

oracleとのJDBC通信が正常に出来ているか確認するテスト用モジュールが必要だった。

java

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;

/**
 * Servlet implementation class Test
 */
public class Test extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Test() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

       //jdbc接続
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@***:1521:orcl", "TEST", "TEST");

            //ステートメントを作成
            Statement stmt = conn.createStatement();

            //問合せの実行
            ResultSet rset = stmt.executeQuery("select count(1) cnt from TEST");

            //問合せ結果の表示
            while ( rset.next() ) {
               out.println(rs.getInt("cnt"));
            }

            //結果セットをクローズ
            rset.close();

            //ステートメントをクローズ
            stmt.close();

            //接続をクローズ
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();

        } catch (SQLException e) {
            e.printStackTrace();

        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

}

DBCPでのプール取得

import java.io.IOException;
import java.io.PrintWriter;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;

import javax.sql.DataSource;


/**
 * Servlet implementation class Test
 */
public class Test extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Test() {
        super();
    }

    public Connection getConnection() throws NamingException, SQLException{
        InitialContext ic = null;
        DataSource ds = null;
        Connection con = null;

        try {
            ic = new InitialContext();
            ds = (DataSource)ic.lookup("java:comp/env/datasource");
            con = ds.getConnection();

        } catch (NamingException e) {
            e.printStackTrace();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return con;
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // connection pool接続
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        Connection con = null;
        try {
            con = this.getConnection();
        } catch (NamingException e1) {
            e1.printStackTrace();

        } catch (SQLException e1) {
            e1.printStackTrace();
        }

        if (con != null) {
            out.println("Connection OK. select count(1) cnt from TEST = ");
            try {
                Statement st = con.createStatement();
                ResultSet rs = st.executeQuery("select count(1) cnt from TEST");

                while(rs.next()) {
                    out.println(rs.getInt("cnt"));
                }

                rs.close();
                st.close();

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {
            out.println("error!!!!!");
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

}