使用Prepared Statement操作資料庫
database_setup.jsp(修正上星期檔案)
Statement stmt=null;
改成→PreparedStatement pstmt=null;
insert.jsp
String name=request.getParameter(name);
String sql="insert into資料表名(欄位名1,欄位2)values(?)";
//values("+name+");
//欄位名1、2可由「?」表示
pstnt=conn.prepareStatement(sql);//prepareStatement為方法
pstmt.setString(1,name);//1指sql字串中的第一個問號
pstmt.setInt(2,hieght);
pstmt.executeUpdate();
---------------predatabase_setup.jsp-----------
<%@ page import="java.sql.*" %>
<%
Connection conn = null;
PreparedStatement pstmt = null;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//載入連資料庫的驅動程式
conn=DriverManager.getConnection("jdbc:odbc:test");//抓資料來源
}catch(SQLException sqle){
out.println("SQL Exception"+sqle);
}
%>
--------------------preinsert.jsp---------------------------
<%@ include file="predatabase_setup.jsp" %>
<%
if(request.getParameter("insert")!=null){
String sql="insert into table1(col1) values(?)";
pstmt=conn.prepareStatement(sql);
String name=request.getParameter("col1");
pstmt.setString(1, name);
pstmt.executeUpdate();
}
%>
<html>
<head><title>preinsert</title></head>
<body>
<form action="preinsert.jsp" method="post">
Name:<input type="text" name="col1" size="10">
<br/>
<input type="submit" name="insert" value="Insert">
</form>
<a href="prequery.jsp">check</a>
</body>
</html>
--------------------predelete.jsp--------------------------
<%@ include file="predatabase_setup.jsp" %>
<%
if(request.getParameter("delete")!=null){
String sql="delete from table1 where id=?";
pstmt=conn.prepareStatement(sql);
int num=Integer.parseInt(request.getParameter("id"));
pstmt.setInt(1, num);
pstmt.executeUpdate();
}
%>
<html>
<head><title>predelete</title></head>
<body>
<form action="predelete.jsp" method="post">
ID:<input type="text" name="id" size="10" value="">
<br/>
<input type="submit" name="delete" value="Delete">
</form>
<a href="prequery.jsp">check</a>
</body>
</html>
--------------------ppremodify.jsp------------------------
<%@ include file="predatabase_setup.jsp" %>
<%
if(request.getParameter("modify")!=null){
int num=Integer.parseInt(request.getParameter("id"));
String name= request.getParameter("name");
String sql="update table1 set col1=? where id=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setInt(2, num);
pstmt.executeUpdate();
}
%>
<html>
<head><title>premodify</title></head>
<body>
<form action="premodify.jsp" method="post">
Name:<input type="text" name="name" size="10" value="">
<br/>
ID:<input type="text" name="id" size="10" value="">
<br/>
<input type="submit" name="modify" value="Modify">
</form>
<a href="prequery.jsp">check</a>
</body>
</html>
---------------------prequery.jsp--------------------------
<%@ include file="predatabase_setup.jsp" %>
<html>
<head><title>preparedquery</title></head>
<body>
<%
String sql="select * from Table1";//取出Table1的所有欄位
pstmt=conn.prepareStatement(sql);
ResultSet rs=pstmt.executeQuery();//執行SQL語法
out.println("<table border='1'>");
out.println("<tr>");
out.println("<td> Name</td>");
out.println("<td>Num</td>");
out.println("</tr>");
while(rs.next()){
String name=rs.getString("col1");
String num=rs.getString("id");
out.println("<tr>");
out.println("<td>"+name+"</td>");
out.println("<td>"+num + "</td>");
out.println("</tr>");
}
out.println("</table>");
rs.close();
%>
</body>
</html>
沒有留言:
張貼留言