如何在JSP中将值插入数据库以进行derby? [英] How can I insert value to a database to derby in JSP?
问题描述
我在JSP中创建了一个表单，用于在derby中将数据插入数据库中，但是它不起作用.
I created a form in a JSP to insert data to a database in derby but it doesn't work.
数据库名称为CUSTOMER.表格:
The database name is CUSTOMER. The tables:
ID (int), CNAME (varchar), ADDRESS (varchar), PHONENUMBER (varchar)
client.jsp的内容:
The content of 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的内容.
The content of 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的内容.
The content of 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)
EDIT2
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开始，因此请尝试
Your preparedStatement index should start from 1, not from 2, so try as
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块的最后一部分.
PreparedStatement or Connection close methods should be in the finally part of try catch finally block.
类似于closeConnection()方法，您可以创建另一个用于关闭prepareStatement的方法，并在finally块中调用它.
Similar to the closeConnection() method, you can create another method for closing preparedStatement and call that in the finally block.
更新1
如果要捕获Java中的ID，请尝试使用
If you are capturing Id in Java, then try as
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中，将其修改为
And in the JSP, modify as
if(ID!=null && NAME!=null && ADDRESS!=null && PHONENUMBER!=null){
client.newClient(ID, NAME, ADDRESS, PHONENUMBER);
}
这篇关于如何在JSP中将值插入数据库以进行derby?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
