我可以在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();
}