通过jsp页面将表单数据插入数据库中的两个不同的表内[英] Insert form data into two different tables on database through jsp page

本文是小编为大家收集整理的关于通过jsp页面将表单数据插入数据库中的两个不同的表内的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个波纹码代码,它不会在数据库上添加任何插入,但是如果我删除第二个" mystatement.executeupdate(insert)",它将带我第一个插入(sqlstring)...我如何运行两个插入物并保存两个表的数据????要更具体地说,我需要从用户表格中获取数据并将其保存到数据库中,但是这是一个重点...在复选框中,用户可以检查两个及以下...因此,如果表格和编队的形成是对的,如果正确,我该怎么办才能将复选框的两个值插入一个表...

 <%@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:您可以看到两个不同的插入表...

html页:

  <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表:

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');

推荐答案

解决方案1 ​​

为您的两个插入操作创建两个不同的语句实例.

解决方案2

如果要重复使用一个单个语句实例,请关闭中的资源,最后块.实际上,将密切的语句终于块中添加 try-catch-resource 始终是一个好主意,JDK7+必须提供.

与您的具体问题无关

  • 强烈建议您避免在JSP文件中使用Java代码.搜索术语" JSP Servlet MVC".
  • 使用准备陈述而不是语句避免SQL注入攻击.

编辑解决方案1:

在您的第一个尝试块中:
在Statement myStatement = myConnection.createStatement();下
添加Statement myStatementTwo = myConnection.createStatement();

在您的第二次尝试块中:
更改myStatement.executeUpdate(sqlString);
到myStatementTwo.executeUpdate(sqlString);

为您的第一个尝试块添加最后一个块,然后在此关闭所有资源.编辑您的代码几乎与从头开始重写所有内容几乎相同,如果您自己做到这一点,它将受益最大.

OP提供HTML代码和数据库后编辑

您的 dest_has_categories 表的 dest_iddest 是 auto_increment 列,但它也是一个外键参考 dest dest 主键 iddest .参考表(联合表)的外键不应是自动increment.如果引用表中不存在DBMS生成的自动增量值,则会存在问题.

请制作外国键而不是auto_increment,当您插入参考表 dest_has_categories 时,请插入现有类别的ID和 dest 的ID.

另外,当您更改代码时,请确保还使用我的解决方案2进行最佳实践.

本文地址:https://www.itbaoku.cn/post/597309.html

问题描述

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.