Java 和 MySQL 插入语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13147143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java and MySQL insert statement
提问by Vladimir
I am just starting with JBDC. I need to create an Java/MySQL insert statement which inserts null, strings, and strings parsed to int (as an input from user using Scanner). I tried many possibilities and nothing is working so far. What would be the correct way of writing such an insert statement or maybe there is an other recommended way I should consider? Thanks for your help!
我刚开始使用 JBDC。我需要创建一个 Java/MySQL 插入语句，它插入空、字符串和解析为 int 的字符串（作为用户使用扫描仪的输入）。我尝试了很多可能性，但到目前为止没有任何效果。编写这样的插入语句的正确方法是什么，或者我应该考虑其他推荐的方法？谢谢你的帮助！
import java.util.ArrayList;
import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class AddBook {
static Scanner keyboard = new Scanner(System.in);
static InvMenu invmenu = new InvMenu();
static MainMenu mainmenu = new MainMenu();
static boolean b = true;
static int x;
static double y;
static double z;
static String choice;
static char letter;
public static void addBook(){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
System.out.println("+----------------+");
System.out.println("| Add a Book |");
System.out.println("+----------------+\n");
System.out.print("\nEnter ISBN number: ");
String isbn = keyboard.next();
System.out.print("Enter Book Title: ");
String title = keyboard.next();
System.out.print("Enter Author's name: ");
String author = keyboard.next();
System.out.print("Enter Publisher's name: ");
String publisher = keyboard.next();
System.out.print("Enter The Date the Book is Added to the Inventory (MM/DD/YYYY): ");
String dateAdded = keyboard.next();
do{
b = true;
System.out.print("Enter The Quantity of Book Being Added: ");
String qtyOnHand = keyboard.next();
try {
x = Integer.parseInt(qtyOnHand);
}
catch(NumberFormatException nFE) {
b = false;
System.out.println();
System.out.println("--------------------------------------------------------");
System.out.println(" !!! You did not enter a valid value. Try again !!!");
System.out.println("--------------------------------------------------------");
System.out.println();
}
}while(b == false);
do{
b = true;
System.out.print("Enter The Wholesale Cost of the Book: ");
String wholesale = keyboard.next();
try {
y = Double.parseDouble(wholesale);
}
catch(NumberFormatException nFE) {
b = false;
System.out.println("--------------------------------");
System.out.println(" ! Wrong Value. Try again !");
System.out.println("--------------------------------\n");
}
}while(b == false);
do{
b = true;
System.out.print("Enter The Retail Price of the Book: ");
String retail = keyboard.next();
try {
z = Double.parseDouble(retail);
}
catch(NumberFormatException nFE) {
b = false;
System.out.println("--------------------------------");
System.out.println(" ! Wrong Value. Try again !");
System.out.println("--------------------------------\n");
}
}while(b == false);
System.out.println("-------------------------------------------");
System.out.println("ISBN number: " + isbn);
System.out.println("Book Title: " + title);
System.out.println("Author's name: " + author);
System.out.println("Publisher's name: " + publisher);
System.out.println("The Date the Book is Added to the Inventory (MM/DD/YYYY): " + dateAdded);
System.out.println("The Quantity of Book Being Added: " + x);
System.out.printf("The Wholesale Cost of the Book: $ %6.2f\n", y);
System.out.printf("The Retail Price of the Book: $ %6.2f\n", z);
System.out.println("-------------------------------------------\n");
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String connectionUrl = "jdbc:mysql://localhost:3306/serendipity";
String connectionUser = "root";
String connectionPassword = "password";
conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
stmt = conn.createStatement();
stmt.executeUpdate("INSERT INTO books(book_id, isbn, title, author, publisher, dateAdded, qtyOnHand, wholesale, retail) VALUES ('"+null+ "','" +isbn+ " ','" +title+ " ',' " +author+ " ',' " +publisher+ " ',' " +dateAdded+ " ',' " +x+ " ',' " +y+ " ',' " +z+" ')");
}
catch (Exception e) {
e.printStackTrace();
}
finally {
try {
if (rs != null) rs.close();
}
catch (SQLException e) {
e.printStackTrace();
}
try {
if (stmt != null) stmt.close();
}
catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("+------------------------------------+");
System.out.println("| The Book is Added to the Inventory |");
System.out.println("+------------------------------------+\n");
}
}
}
回答by kosa
You may need to consider using PreparedStatementand use setNull()to insert null.
您可能需要考虑使用PreparedStatement并使用setNull()插入 null。
Raw SQL statements are prone to SQL injection attacks.
原始 SQL 语句容易受到 SQL 注入攻击。
See this exampleto learn more about to how to use setNull
请参阅此示例以了解有关如何使用 setNull 的更多信息
回答by code4j
Are you ensure the connection is work? Then, you may consider to use preQueryStatement
您确定连接正常吗？那么，你可以考虑使用preQueryStatement
Here is an small example:
这是一个小例子：
cnnct = getConnection();
String preQueryStatement = "INSERT INTO CUSTOMER VALUES (?,?,?,?)";
pStmnt = cnnct.prepareStatement(preQueryStatement);
pStmnt.setString(1, CustId);
pStmnt.setString(2, Name);
pStmnt.setString(3, Tel);
pStmnt.setInt(4, Age);
