Servletからストアドプロシージャを使う
(1) ディレクトリ構成
$ tree test06
test06
├── META-INF
│ └── context.xml
└── WEB-INF
└── classes
├── test06.class
└── test06.java
3 directories, 3 files
(2) テーブルとデータ
mysql -unaoki -ppassword sampledb <<EOF
drop table if exists foods;
create table foods(
code varchar(8) primary key,
name varchar(100),
price integer
) engine=InnoDB;
insert into foods values('000001','りんご',398);
insert into foods values('000002','みかん',480);
insert into foods values('000003','柿' ,450);
delimiter $$
drop procedure if exists ptest01$$
create procedure ptest01(
in p_code varchar(8),
out p_name varchar(100),
out p_price int,
out p_count int
)
begin
select name,price into p_name,p_price from foods where code = p_code;
select row_count() into p_count;
end;
$$
drop procedure if exists ptest02$$
create procedure ptest02(
in p_code varchar(8),
in p_name varchar(100),
in p_price int,
out p_count int
)
begin
update foods
set name = p_name,
price = p_price
where code = p_code;
select row_count() into p_count;
end;
$$
EOF
(3) コンテキストファイルコネクションプールはDBCPとJDBCという２つの仕組みが提供されているらしい。TOCAT7ではデフォルトはDBCPらいしいが、切り替えるには、factory で指定できるらしい。jndi の定義は、本来は tomcat7/conf 配下の context.xml で設定するべきだと思うのだが、アプリケーション単位に設定できる。全く同じ jndi名で片方のアプリケーションがコネクションプールを使いきっても、もう一方のアプリケーションが独自に context.xml を使っていれば影響はなかった。サーバー管理としては、一箇所で管理したいところだとは思うが、個別にアプリをテストする時には便利な仕様である。
$ cat test02/META-INF/context.xml
<Context>
<Resource name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
username="naoki"
password="password"
driverClassName="com.mysql.jdbc.Driver"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
url="jdbc:mysql://localhost:3306/sampledb"/>
<!-- factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" -->
</Context>
※ ~/tomcat7/lib 配下にmysql-connector-java-5.1.27-bin.jarを配置(4) SERVLEAT(JAVA)ファイル
import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.annotation.*;
import javax.servlet.http.*;
@WebServlet(name="test06", urlPatterns={"/test06"})
public class test06 extends HttpServlet {
@Override
public void doGet(HttpServletRequest req,
HttpServletResponse res)
throws ServletException,IOException {
res.setContentType("text/html; charset=UTF-8");
PrintWriter out = res.getWriter();
out.println(base_page());
out.close();
}
private String base_page() {
String HTML_TEXT = "<html>"
+ "<head>"
+ "<title> SERVLET-MYSQL-TEST </title>"
+ "</head>"
+ "<body>"
+ "<h3>検索</h3>"
+ "<form method='post' action='test06'>"
+ "<input type='hidden' name='mode' value='select'>"
+ "code：<input type='text' name='code'>"
+ "<p>"
+ "<input type='submit' value='検索'>"
+ "</form>"
+ "</body>"
+ "</html>";
return(HTML_TEXT);
}
@Override
public void doPost(HttpServletRequest req,
HttpServletResponse res)
throws ServletException,IOException {
req.setCharacterEncoding("UTF-8");
res.setContentType("text/html; charset=UTF-8");
String mode = req.getParameter("mode");
if (mode.equals("select")) {
selectprocess(req,res);
}
else if (mode.equals("update")) {
updateprocess(req,res);
}
}
public void selectprocess(HttpServletRequest req,
HttpServletResponse res)
throws ServletException,IOException {
InitialContext initctx = null;
Connection cn = null;
int counter = 0;
try {
initctx = new InitialContext();
DataSource ds = (DataSource)initctx.lookup("java:comp/env/jdbc/TestDB");
cn = ds.getConnection();
CallableStatement st = cn.prepareCall("{call ptest01(?,?,?,?)}");
String code = req.getParameter("code");
st.setString(1,code);
st.registerOutParameter(2,java.sql.Types.VARCHAR);
st.registerOutParameter(3,java.sql.Types.INTEGER);
st.registerOutParameter(4,java.sql.Types.INTEGER);
st.execute();
String name = st.getString(2);
Integer price = st.getInt(3);
Integer count = st.getInt(4);
if (count == 1) {
PrintWriter out = res.getWriter();
out.println(search_result_page(code,name,price));
out.close();
}
else {
PrintWriter out = res.getWriter();
out.println(search_error_page(counter,code));
out.close();
}
cn.close();
initctx.close();
}
catch(NamingException e) {
e.printStackTrace();
}
catch(SQLException e) {
e.printStackTrace();
}
finally {
try {
if (cn != null) cn.close();
}
catch(SQLException e) {
e.printStackTrace();
}
try {
if (initctx != null) initctx.close();
}
catch(NamingException e) {
e.printStackTrace();
}
}
}
private String search_result_page(String code, String name,Integer price) {
String HTML_TEXT = "<html>"
+ "<head>"
+ "<title> SERVLET-MYSQL-TEST </title>"
+ "</head>"
+ "<body>"
+ "<form method='post' action='test06'>"
+ "<input type='hidden' name='mode' value='update'>"
+ "code:%s"
+ "<input type='hidden' name='code' value='%s'>"
+ "名前:<input type='text' name='name' value='%s'>"
+ "値段:<input type='text' name='price' value='%d'>"
+ "<p>"
+ "<a href=\"test06\">戻る</a>"
+ "<input type='submit' value='更新'>"
+ "</form>"
+ "</body>"
+ "</html>";
return(String.format(HTML_TEXT,code,code,name,price));
}
private String search_error_page(int counter,String code) {
String HTML_TEXT = "<html>"
+ "<head>"
+ "<title> SERVLET-MYSQL-TEST </title>"
+ "</head>"
+ "<body>"
+ "code : %s は検索件数 %d 件でした。<br />"
+ "<a href=\"test06\">戻る</a>"
+ "</body>"
+ "</html>";
return(String.format(HTML_TEXT,code,counter));
}
public void updateprocess(HttpServletRequest req,
HttpServletResponse res)
throws ServletException,IOException {
InitialContext initctx = null;
Connection cn = null;
try {
initctx = new InitialContext();
DataSource ds = (DataSource)initctx.lookup("java:comp/env/jdbc/TestDB");
cn = ds.getConnection();
CallableStatement st = cn.prepareCall("{call ptest02(?,?,?,?)}");
String code = req.getParameter("code");
st.setString(1,req.getParameter("code"));
st.setString(2,req.getParameter("name"));
st.setInt(3,Integer.parseInt(req.getParameter("price")));
st.registerOutParameter(4,java.sql.Types.INTEGER);
st.execute();
Integer counter = st.getInt(4);
if (counter == 1) {
PrintWriter out = res.getWriter();
out.println(update_result_page(counter,req.getParameter("code")));
out.close();
}
else {
PrintWriter out = res.getWriter();
out.println(search_error_page(counter,req.getParameter("code")));
out.close();
}
cn.close();
initctx.close();
}
catch(NamingException e) {
e.printStackTrace();
}
catch(SQLException e) {
e.printStackTrace();
}
finally {
try {
if (cn != null) cn.close();
}
catch(SQLException e) {
e.printStackTrace();
}
try {
if (initctx != null) initctx.close();
}
catch(NamingException e) {
e.printStackTrace();
}
}
}
private String update_result_page(int counter,String code) {
String HTML_TEXT = "<html>"
+ "<head>"
+ "<title> SERVLET-MYSQL-TEST </title>"
+ "</head>"
+ "<body>"
+ "更新件数：%d 件"
+ "<a href='test06'>検索画面へ</a>"
+ "</body>"
+ "</html>";
return(String.format(HTML_TEXT,counter));
}
private String update_error_page(int counter,String code) {
String HTML_TEXT = "<html>"
+ "<head>"
+ "<title> SERVLET-MYSQL-TEST </title>"
+ "</head>"
+ "<body>"
+ "code : %s は更新件数 %d 件でした。<br />"
+ "<a href=\"test06\">戻る</a>"
+ "</body>"
+ "</html>";
return(String.format(HTML_TEXT,code,counter));
}
}
(5)コンパイル
javac -cp ~/tomcat7/lib/servlet-api.jar test06/WEB-INF/classes/test06.java
(6)TOMCATへ配備
$ cp -rp test06 ~/tomcat7/webapps(7)実行結果
0 件のコメント:
コメントを投稿