JamesPCSchool: J2EE Part 13

1.SQL insert & delete 語法練習
insert\index.jsp

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<html>
<head>
<title>INSERT 語法練習</title>
</head>
<body bgcolor="skyblue">
SQL INSERT 語法練習    
<a href='../'>回上一頁</a>
<hr>

<form name='f1' method='post' action='insert_db.jsp'>
新增部門：<input type='text' name='d_name'>
<input type='submit' value='新增'>
</form>

<%
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/company?user=root&password=apple&charset=utf8";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();
String strSQL = "SELECT * FROM department order by 'd_name'";
ResultSet rs = stmt.executeQuery(strSQL);

out.print("<table border='1'>");

while ( rs.next() ){
out.println( "<tr><td>" + rs.getString("d_name") + "</td><td><a href='./re_confirm.jsp?d_name=" +rs.getString("d_name") +"'>刪除</a></td></tr>");
}
out.println("</table>");
stmt.close();
con.close();
%>
</body>
</html>

insert\insert_db.jsp (實際執行 insert 的程式，沒有畫面)

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<%
String d_name = request.getParameter("d_name");
//驗證 d_name 是否為正常
if ( d_name != null && d_name.length()>0 ){
d_name = new String ( d_name.getBytes("ISO-8859-1"), "UTF-8");

Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/company?user=root&password=apple&charset=utf8";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();
String strSQL = "select * from department where d_name = '" +d_name +"'";
// rs 只 select 出所輸入的 d_name，所以 select 不到就代表輸入的 d_name 是新的。
ResultSet rs = stmt.executeQuery(strSQL);
//如果 rs 沒有下一行，則執行 insert ，意思是說，如果輸入的是重複資料，就不 insert ，回到 index.jsp
if ( !rs.next()){
strSQL = "insert into department (d_name) values '" +d_name +"'";
stmt.execute(strSQL);
} //if
stmt.close();
con.close();
} //if
response.sendRedirect("index.jsp");
%>

insert\re_confirm.jsp (再確認畫面)

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<%
String d_name = request.getParameter("d_name");
if ( d_name == null || d_name.length() <=0 ){
response.sendRedirect("index.jsp");
}
d_name = new String( d_name.getBytes("ISO-8859-1"), "UTF-8");

%>
<html>
<head>
<title> 再確認 </title>
</head>
<body bgcolor='skyblue'>

  確定是否刪除?
<font color='red'><%= d_name %></font><br><br>

<form name='f1' methos='post' action='del_db.jsp'>
<input name='d_name' type='hidden' value='<%= d_name %>'>
<input type='submit' value='確定'>
</form>



<form name='f2' methos='post' action='index.jsp'>
<input type='submit' value='取消'>
</form>

</body>
</html>

insert\del_db.jsp (實際執行 delete 的程式，沒有畫面)

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<%
String d_name = request.getParameter("d_name");
if ( d_name != null && d_name.length()>0 ){
d_name = new String ( d_name.getBytes("ISO-8859-1"), "UTF-8");

Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/company?user=root&password=apple&charset=utf8";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();
String strSQL = "delete from department where d_name='" +d_name +"'";
stmt.execute(strSQL);
stmt.close();
con.close();
}
response.sendRedirect("index.jsp");
%>

結果：

index.jsp

re_confirm.jsp

J2EE Part 13

No comments: