如何在JSP中将值插入数据库以进行derby?
我在JSP中创建了一个表单,用于在derby中将数据插入数据库中,但是它不起作用。
数据库名称为CUSTOMER。表格:
ID (int), CNAME (varchar), ADDRESS (varchar), PHONENUMBER (varchar)
client.jsp的内容:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<jsp:useBean id="client" class="database.client" scope="page" />
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body bgcolor="lightgrey">
<%
String ID=request.getParameter("ID");
String NAME=request.getParameter("CNAME");
String ADDRESS=request.getParameter("ADDRESS");
String PHONENUMBER=request.getParameter("PHONENUMBER");
%>
<form method="post" action="">
<table border="0" align="left">
<tr>
<td>Name</td>
<td><input type="text" name="CNAME" style="background-color:beige"/></td>
</tr>
<tr>
<td>Address</td>
<td><input type="text" name="ADDRESS" style="background-color:beige"/></td>
</tr>
<tr>
<td>TelNumber</td>
<td><input type="text" name="PHONENUMBER" style="background-color:beige"/></td>
</tr>
<input type="submit" name="OK" onclick="
<%
if(NAME!=null && ADDRESS!=null && PHONENUMBER!=null){
client.newClient(NAME, ADDRESS, PHONENUMBER);
}
%>" value="OK"/>
</table>
</form>
</body>
client.java的内容。
package database;
public class Client implements DatabaseConnection{
private static Connection conn = null;
private static void createConnection(){
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException ex) {
Logger.getLogger(client.class.getName()).log(Level.SEVERE, null, ex);
}
}
private static void closeConnection(){
if (conn != null){
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(client.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
public void newClient(String name, String address, String phoneNumber){
try {
createConnection();
String insert="INSERT INTO CUSTOMER(CNAME,ADDRESS, PHONENUMBER) VALUES(?,?,?)";
PreparedStatement ps=conn.prepareStatement(insert);
ps.setString(2, name);
ps.setString(3, address);
ps.setString(4, phoneNumber);
ps.executeUpdate();
ps.close();
} catch (Exception e) {
}
}
}
databaseConnection的内容。
package database;
public interface DatabaseConnection{
String URL="jdbc:derby://localhost:1527/Test";
String USER="myusername";
String PASSWORD="mypassword";
}
编辑
错误信息:
Caused by: org.apache.derby.client.am.SqlException: Column position '4' is
outside the allowed range. The columns of ResultSet are '3'.
Org.apache.derby.client.am.ColumnMetaData.checkForValidColumnIndex (unknown
source)
编辑2
public void deleteClient(String ID){
try {
String delete="DELETE FROM CUSTOMER WHERE ID=?";
PreparedStatement ps=conn.prepareStatement(delete);
ps.setString(1, ID);
ps.executeUpdate();
} catch (Exception e) {
}
}
-
您的prepareStatement索引应该从1开始,而不是从2开始,因此请尝试
public void newClient(String name, String address, String phoneNumber){ PreparedStatement ps = null; try { createConnection(); String insert="INSERT INTO CUSTOMER(ID,CNAME,ADDRESS, PHONENUMBER) VALUES(?,?,?,?)"; ps=conn.prepareStatement(insert); ps.setString(1, name); ps.setString(2, address); ps.setString(3, phoneNumber); ps.executeUpdate(); } catch (Exception ex) { ex.printStackTrace(); } finally { ps.close(); closeConnection(); } }
PreparedStatement或Connection关闭方法应位于
try catch finally
块的最后一部分。与该
closeConnection()
方法类似,您可以创建另一个用于关闭prepareStatement的方法,并在finally块中调用该方法。更新1
如果要捕获Java中的ID,请尝试
public void newClient(Integer id, String name, String address, String phoneNumber){ PreparedStatement ps = null; try { createConnection(); String insert="INSERT INTO CUSTOMER(CNAME,ADDRESS, PHONENUMBER) VALUES(?,?,?)"; ps=conn.prepareStatement(insert); ps.setInt(1, id) ps.setString(2, name); ps.setString(3, address); ps.setString(4, phoneNumber); ps.executeUpdate(); } catch (Exception ex) { ex.printStackTrace(); } finally { ps.close(); closeConnection(); } }
在JSP中,将其修改为
if(ID!=null && NAME!=null && ADDRESS!=null && PHONENUMBER!=null){ client.newClient(ID, NAME, ADDRESS, PHONENUMBER); }