通过jsp页面将表单数据插入到数据库的两个不同的表中 [英] Insert form data into two different tables on database through jsp page
问题描述
<％@ page import =java.sql。*％>
<％@ page contentType =text / htmlpageEncoding =UTF-8％>
<！DOCTYPE html>
< html>
< head>
< meta http-equiv =Content-Typecontent =text / html; charset = UTF-8>
< title> create dest code< / title>
< / head>
< body>
<％
String id8 = request.getParameter（id8）;
String id9 = request.getParameter（id9）;
String id10 = request.getParameter（id10）;
String ch1 = request.getParameter（dest1）;
String empty =;
boolean flag = false;
if（！（id8.equals（empty）| id9.equals（empty）| id10.equals（empty）））{
try {
Class.forName（com.mysql。 jdbc.Driver）;
String myDatabase =jdbc：mysql：// localhost：3306 / project_app？user = root& password = 1234;
Connection myConnection = DriverManager.getConnection（myDatabase）;
语句myStatement = myConnection.createStatement（）;
String sqlString =INSERT INTO dest（Country，City，URL）VALUES（'+ id8 +'，'+ id9 +'，'+ id10 +'）;
String insert =INSERT INTO dest_has_categories（Categories_idCategories）VALUES（'+ ch1 +'）;
try {
myStatement.executeUpdate（sqlString）;
myStatement.executeUpdate（insert）;
} catch（SQLException sqlException）{
％>
<％
flag = true;
}
if（flag == false）{
}
//关闭与数据库的连接
myStatement.close（）;
myConnection.close（）;
} catch（Exception e）{
e.printStackTrace（）;
}
} else {
％>
<％
}
％>
PS：有两个不同的插入表，你可以看到...
html页面：
< form name =createdestmethod =get action =../ jsp / create-dest-code.jsp>
国家：< input type =textrequired name =id8/> < br>
城市：< input type =text必需name =id9/> < br>
URL视频：< input type =urlrequired name =id10/> < br> < br>
< i>< ins>将目的地分类（最多2个）：< / ins>< / i> < br> < br>
< input type =checkboxname =dest1value =2onClick =return KeepCount（）> Christmas< br&
< input type =checkboxname =dest2value =1onClick =return KeepCount（）> Winter< br>
< input type =checkboxname =dest3value =3onClick =return KeepCount（）> Summer< br> < br>
< input type =submitclass =buttonvalue =CREATE DESTINATION/>
< br>
< / form>
< SCRIPT LANGUAGE =javascript>
function KeepCount（）{
var NewCount = 0;
if（document.createdest.dest1.checked）
{NewCount = NewCount + 1;}
if（document.createdest.dest2.checked）
{NewCount = NewCount + 1;}
if（document.createdest.dest3.checked）$ b $ newCount = NewCount == 3）
{
alert（'Pick Just Two Please'）;
document.createdest; return false;
}
}
< / SCRIPT>
mysql表：
CREATE TABLE IF NOT EXISTS`project_app`.`Dest`（
`idDest` INT NOT NULL AUTO_INCREMENT，
`Country` VARCHAR（45）NULL，
`City VARCHAR（45）NULL，
`URL` VARCHAR（45）NULL，
PRIMARY KEY（`idDest`））
如果NOT EXISTS`project_app`.`Categories` b $ b`idCategories` INT NOT NULL，
`Categories VARCHAR（45）NULL，
PRIMARY KEY（`idCategories`））
如果不存在`project_app`.`Dest_has_Categories `
`Dest_idDest` INT NOT NULL，
`Categories_idCategories` INT NOT NULL，
PRIMARY KEY（`Dest_idDest`，`Categories_idCategories`），
INDEX`fk_Dest_has_Categories_Categories1_idx`
INDEX`fk_Dest_has_Categories_Dest_idx`（`Dest_idDest` ASC），
CONSTRAINT`fk_Dest_has_Categories_Dest`
FOREIGN KEY（`Dest_idDest`）
REFERENCES`mydb`.`Dest ```（`idDest`）
删除无操作
ON UPDATE NO ACTION，
CONSTRAINT`fk_Dest_has_Categories_Categories1`
FOREIGN KEY（`Categories_idCategories`）
参考`mydb` .`Categories`（`idCategories`）
ON DELETE NO ACTION
ON UPDATE NO ACTION）
INSERT INTO类别（idCategories，Categories）VALUES（1，'Winter'）;
INSERT INTO类别（idCategories，Categories）VALUES（2，'Christmas'）;
INSERT INTO类别（idCategories，Categories）VALUES（3，'Summer'）;
解决方案1
为您的两个插入操作创建两个不同的Statement实例。
解决方案2
您想要重复使用一个Statement实例，请关闭最后块中的资源。实际上，最好在 finally 块中放置close语句，或者直接使用jdk7 +提供的 try-catch-resource
与您的具体问题无关
- 强烈建议您避免使用java代码在jsp文件中。搜索术语jsp servlet mvc。
- 使用PreparedStatement而不是Statement避免sql注入攻击。
编辑解决方案1：
< br>
语句myStatement = myConnection.createStatement（）; add
语句myStatementTwo = myConnection.createStatement ）;
在第二个try块中：
更改 myStatement.executeUpdate（sqlString） ;
到 myStatementTwo.executeUpdate（sqlString）;
为您的第一个try块添加最后一个块，并关闭所有的资源。编辑代码与从头开始重写所有代码是一样的，如果你可以自己做，这将是最大的好处。
编辑后OP提供HTML代码和数据库
您的 Dest_has_Categories 表 Dest_idDest 是 auto_increment 列，外键引用目标的主键 idDest 。参考表（联合表）的外键不应为auto_increment。如果DBMS生成的自动递增值在引用表中不存在，则会出现问题。
请将外键设为非自动递增，当您插入参考表 Dest_has_Categories 时，请插入现有类别的ID和目标的ID。
此外，当您更改代码时，请务必也使用我的解决方案2进行最佳实践。
i have the bellow code that it doesnt add on database any insert but if i delete the second one "myStatement.executeUpdate(insert) " , it takes me the first insert (sqlstring)... how can i run both inserts and save data for both tables ???? To be more specific , i need to take data from users form and save them to database, but here is the point... in checkboxes user can check two and less... so can you help me if the tables and formation of them is right and if its right what can i do in order to take the two values of checkboxes and insert them in one table...
<%@page import="java.sql.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>create dest code</title>
</head>
<body>
<%
String id8=request.getParameter("id8");
String id9=request.getParameter("id9");
String id10=request.getParameter("id10");
String ch1=request.getParameter("dest1");
String empty= "";
boolean flag=false;
if (!(id8.equals(empty) | id9.equals(empty) | id10.equals(empty) )) {
try {
Class.forName("com.mysql.jdbc.Driver");
String myDatabase = "jdbc:mysql://localhost:3306/project_app?user=root&password=1234";
Connection myConnection = DriverManager.getConnection(myDatabase);
Statement myStatement = myConnection.createStatement();
String sqlString = "INSERT INTO dest(Country,City,URL) VALUES ('"+id8+"', '"+id9+"','"+id10+"')";
String insert= "INSERT INTO dest_has_categories(Categories_idCategories) VALUES ('"+ch1+"')";
try{
myStatement.executeUpdate(sqlString);
myStatement.executeUpdate(insert);
}catch(SQLException sqlException) {
%>
<%
flag = true;
}
if(flag == false ){
}
// Close the connection to the database
myStatement.close();
myConnection.close();
}catch(Exception e){
e.printStackTrace();
}
}else{
%>
<%
}
%>
PS: there are two different tables for inserts as you can see...
html page :
<form name="createdest" method="get" action="../jsp/create-dest-code.jsp">
Country: <input type="text" required name="id8" /> <br>
City: <input type="text" required name="id9" /> <br>
URL Video: <input type="url" required name="id10" /> <br> <br>
<i><ins>Categorize the destination (max 2): </ins></i> <br> <br>
<input type="checkbox" name="dest1" value="2" onClick="return KeepCount()" >Christmas<br>
<input type="checkbox" name="dest2" value="1" onClick="return KeepCount()" >Winter <br>
<input type="checkbox" name="dest3" value="3" onClick="return KeepCount()" >Summer <br> <br>
<input type="submit" class="button" value="CREATE DESTINATION" />
<br>
</form>
<SCRIPT LANGUAGE="javascript">
function KeepCount() {
var NewCount = 0;
if (document.createdest.dest1.checked)
{NewCount = NewCount + 1;}
if (document.createdest.dest2.checked)
{NewCount = NewCount + 1;}
if (document.createdest.dest3.checked)
{NewCount = NewCount + 1;}
if (NewCount == 3)
{
alert('Pick Just Two Please');
document.createdest; return false;
}
}
</SCRIPT>
mysql tables :
CREATE TABLE IF NOT EXISTS `project_app`.`Dest` (
`idDest` INT NOT NULL AUTO_INCREMENT,
`Country` VARCHAR(45) NULL,
`City` VARCHAR(45) NULL,
`URL` VARCHAR(45) NULL,
PRIMARY KEY (`idDest`))
CREATE TABLE IF NOT EXISTS `project_app`.`Categories` (
`idCategories` INT NOT NULL,
`Categories` VARCHAR(45) NULL,
PRIMARY KEY (`idCategories`))
CREATE TABLE IF NOT EXISTS `project_app`.`Dest_has_Categories` (
`Dest_idDest` INT NOT NULL ,
`Categories_idCategories` INT NOT NULL,
PRIMARY KEY (`Dest_idDest`, `Categories_idCategories`),
INDEX `fk_Dest_has_Categories_Categories1_idx` (`Categories_idCategories` ASC),
INDEX `fk_Dest_has_Categories_Dest_idx` (`Dest_idDest` ASC),
CONSTRAINT `fk_Dest_has_Categories_Dest`
FOREIGN KEY (`Dest_idDest`)
REFERENCES `mydb`.`Dest` (`idDest`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Dest_has_Categories_Categories1`
FOREIGN KEY (`Categories_idCategories`)
REFERENCES `mydb`.`Categories` (`idCategories`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
INSERT INTO categories(idCategories,Categories) VALUES (1,'Winter');
INSERT INTO categories(idCategories,Categories) VALUES (2,'Christmas');
INSERT INTO categories(idCategories,Categories) VALUES (3,'Summer');
解决方案 Solution 1
Create two different Statement instances for your two insert operations.
Solution 2
If you want to reuse one single Statement instance, close the resources in a Finally block. Actually it is always a good idea to put the close statement in finally block or simply use try-catch-resource that jdk7+ has to offer.
Unrelated to your concrete questions
- It is highly recommended that you avoid using java code in jsp file. Search the terms "jsp servlet mvc".
- Use PreparedStatement instead of Statement to avoid sql injection attack.
EDIT for solution 1:
In your first try block:
Under Statement myStatement = myConnection.createStatement();
add Statement myStatementTwo = myConnection.createStatement();
In your second try block:
change myStatement.executeUpdate(sqlString);
to myStatementTwo.executeUpdate(sqlString);
Add a final block for your first try block and close all your resource there. Editing your code is pretty much the same as rewriting everything from scratch, it is going to benefit you most if you can do that yourself.
EDIT after OP provided HTML code and database
Your Dest_has_Categories table's Dest_idDest is an auto_increment column but it is also a foreign key referencing Dest's primary key idDest. Reference Table (joint table) 's foreign keys should not be auto_increment. There will be problems if the auto incremented value generated by DBMS does not exist in the referencing table.
Please make the foreign keys not auto_increment and when you insert to the reference table Dest_has_Categories, please insert existing Categories's id and Dest's id.
Also, when you change your code, make sure to also use my Solution 2 for best practice.
这篇关于通过jsp页面将表单数据插入到数据库的两个不同的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
