- コーディングでの文字コード指定
req.setCharacterEncoding("UTF-8"); res.setContentType("text/html; charset=UTF-8"); a - mysqlの文字コード指定(/etc/mysql/my.cnf)
[client] default-character-set=utf8 [mysqld] character-set-server=utf8 [mysqldump] default-character-set=utf8 [mysql] default-character-set=utf8
ServletのMYSQLテーブル更新
(1) ディレクトリ構成
$ tree test05
test05
├── META-INF
│ └── context.xml
└── WEB-INF
└── classes
├── test05.class
└── test05.java
3 directories, 3 files
(2) テーブルとデータ
$ mysql -unaoki -p sampledb
Enter password:
mysql> create table foods(
-> code varchar(8) PRIMARY KEY,
-> name varchar(100),
-> price integer,
-> index(price)
-> ) engine=InnoDB;
Query OK, 0 rows affected (0.12 sec)
mysql> insert into foods values('000001','りんご',398);
Query OK, 1 row affected (0.03 sec)
mysql> insert into foods values('000002','みかん',480);
Query OK, 1 row affected (0.04 sec)
mysql> insert into foods values('000003','柿' ,450);
Query OK, 1 row affected (0.04 sec)
mysql> select * from foods;
+--------+-----------+-------+
| code | name | price |
+--------+-----------+-------+
| 000001 | りんご | 398 |
| 000002 | みかん | 480 |
| 000003 | 柿 | 450 |
+--------+-----------+-------+
3 rows in set (0.00 sec)
(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>
(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="test05", urlPatterns={"/test05"})
public class test05 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='test05'>"
+ "<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();
PreparedStatement st = cn.prepareStatement(
"select * from foods where code = ?");
st.setString(1,req.getParameter("code"));
ResultSet rs = st.executeQuery();
String code = "";
String name = "";
String price = "";
while(rs.next()) {
code = rs.getString("code");
name = rs.getString("name");
price = rs.getString("price");
counter++;
}
if (counter == 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,String price) {
String HTML_TEXT = "<html>"
+ "<head>"
+ "<title> SERVLET-MYSQL-TEST </title>"
+ "</head>"
+ "<body>"
+ "<form method='post' action='test05'>"
+ "<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='%s'>"
+ "<p>"
+ "<a href=\"test05\">戻る</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=\"test05\">戻る</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();
PreparedStatement st = cn.prepareStatement(
"update foods "
+ "set name = ?,"
+ " price = ? "
+ "where code = ?");
st.setString(1,req.getParameter("name"));
st.setInt(2,Integer.valueOf(req.getParameter("price")));
st.setString(3,req.getParameter("code"));
int counter = st.executeUpdate();
PrintWriter out = res.getWriter();
out.println(update_result_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='test05'>検索画面へ</a>"
+ "</body>"
+ "</html>";
return(String.format(HTML_TEXT,counter));
}
}
(5)コンパイル
javac -cp ~/tomcat7/lib/servlet-api.jar test05/WEB-INF/classes/test05.java
(5)TOMCATへ配備
$ cp -rp test5 ~/tomcat7/webapps(6)実行結果
mysql> select * from foods; +--------+-------------------+-------+ | code | name | price | +--------+-------------------+-------+ | 000001 | りんご(袋詰) | 398 | | 000002 | みかん | 480 | | 000003 | 柿 | 450 | +--------+-------------------+-------+ 3 rows in set (0.00 sec) mysql>
0 件のコメント:
コメントを投稿