无法将数据插入 MySQL



我可以在Netbeans中使用这个连接:

 Connection conn = null;
 try{
     conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/librarymangementsystem","root","root");
     if(conn!= null)
     {
     System.out.println("connected");
     }
 }catch(Exception e)
             {
             System.out.println("not connected");
             }
}

但是当涉及到向列中添加数据时,我就是不能。

try{
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/librarymangementsystem","root","root");
        Statement stmt=conn.createStatement();
        String Query = "INSERT into librarian_details(username, name, email, password) VALUES("+ uname +", "+ fname +", " + emails + ",  " + psword +")";
        stmt.executeUpdate(Query);
        JOptionPane.showMessageDialog(null, "Success!");
     }

有谁知道问题所在吗?更新问题:

String Query = "insert into book_details(Book_Name, ISBN, Author, Category,>Quantity, BookShelfNo,Row,Column) VALUES('" +bookname +"', '" + ISBN +"', '" + AuthorName +"', '" + Category +"', '" + Quantity +"', '" + ?BookShelfNo +"', '">+ Row +"', '" + Column +"')";

我似乎不能插入任何数据行和列使用这个:

String Row = jTextField9.getText();字符串列= jTextField10.getText();行和列数据类型为int。

我猜用户名,姓名,电子邮件和密码字段是字符串类型和数据类型使用,而创建列librarian_details的用户名,姓名,电子邮件和密码是Varchar

如果是,则需要将查询字符串更新为以下代码:

String Query = "INSERT into librarian_details(username, name, email, password) 
VALUES('"+ uname +"','"+ fname +"','" + emails + "','" + psword +"')";

如果您的输入字符串有一个撇号(')字符,那么您需要添加一个额外的撇号(')字符作为转义序列。

例如:您的密码是abc'aa

        String uname = "abc";
        String fname = "xyz";
        String emails = "abc@xyz.com";
        String psword = "abc''aa";//extra apostrophe (') character added
        String Query = "INSERT into librarian_details(username, name, email, password) VALUES('"+ uname+ "','"+ fname+ "','"+ emails+ "','"+ psword+ "')";

注意:在现有的(')字符上添加额外的撇号(')与双引号不同。

下面的代码是为您更新的查询

        String bookname ="abc";
        String ISBN="qwerty123";
        String AuthorName="user3213";
        String Category="New";
        String Quantity="1";
        String BookShelfNo="5";
        int Row=1;
        int Column=5;
        String Query = "insert into book_details(Book_Name, ISBN, Author, Category, Quantity, BookShelfNo,`Row`,`Column`) VALUES('" +bookname + "','" + ISBN + "','" + AuthorName + "','" + Category + "','" + Quantity + "','" + BookShelfNo +"', " + Row + ", " + Column + ")";
        stmt.execute(Query);

注意:您使用的是sql的保留关键字,如Row和Column。我猜你在DB中的列名是Book_Name, ISBN,作者,类别,数量,BookShelfNo,行和列。

建议:

    使用PreparedStatement将节省编写查询的时间(不需要记住变量和列的数据类型)
  • PreparedStatement使用查询缓存功能。
  • 因此,执行比简单语句快。

下面的代码描述了查询中预处理语句的用法。

        //query parameters will be dynamically set 
        String Query = "INSERT INTO book_details VALUES (?,?,?,?,?,?,?,?)";
        //create a Prepared statement using connection object.
        PreparedStatement pstmt = con.prepareStatement(Query);
        //assign the query parameter values
        pstmt.setString(1, bookname);
        pstmt.setString(2, ISBN);
        pstmt.setString(3, AuthorName);
        pstmt.setString(4, Category);
        pstmt.setString(5, Quantity);
        pstmt.setString(6, BookShelfNo);
        pstmt.setInt(7, Row);
        pstmt.setInt(8, Column);
        //display query string generated by PreparedStatement.
        System.out.println("Query: "+pstmt.toString());
        //Display result; result=1 means success.
        int result = pstmt.executeUpdate();
        System.out.println("result: "+result);

转义文本值,或者使用预处理语句更好。

String Query = "INSERT into librarian_details(username, name, email, password) 
VALUES('"+ uname +"', '"+ fname +"', '" + emails + "', '" + psword +"')";

准备好的语句:

String Query = "INSERT into librarian_details(username, name, email, password) 
VALUES(?, ?, ?, ?)";

将值连接到查询中是不安全的,它打开了SQL注入的大门。您需要使用预处理语句:

try (PreparedStatement pstmt = connection.prepareStatement(
        "INSERT into librarian_details(username, name, email, password) " 
        + "VALUES(?, ?, ?, ?)")) {
    pstmt.setString(1, uname);
    pstmt.setString(2, fname);
    pstmt.setString(3, emails);
    pstmt.setString(4, psword);
    pstmt.executeUpdate();
}

相关内容

  • 没有找到相关文章

最新更新