When writing SQL statements in Java code, you must note that PostgreSQL

When writing SQL statements in Java code, you must note that PostgreSQL

Recently, you need to manually write SQL statements to Java code from time to time. When writing SQL statements, you need to pay attention to a few minor issues.

Let's take a look at some simple SQL statements I wrote before. I thought it was okay, but an error was reported directly during compilation.

1 String str = "insert into XXX (a, B, c) values ('". getA () "','". getB () "','". getC ()"');";

After studying for half a day, we found that it should be a connection string problem. After the first modification, we added "+" before and after the value assignment field to complete the SQL statement. The code after correction is as follows:

1 String str = "insert into XXX (a, B, c) values ('" +. getA () + "','" +. getB () + "','" +. getC () + "');";

It turns out that the dynamic assignment of fields in the database must be completed in the form of '+... +. All right, after compilation, put the result value of the running str into the SQL database for testing. There is no problem. I think everything is okay, and an error is reported again when the result is running. I am troubled by this. I tested it repeatedly and compared it with SQL statements in the database. There is no problem. Now I want to put the code that I succeeded in the end. Let's see if there is any difference.

1 String str = "insert into XXX (a, B, c) values ('" +. getA () + "','" +. getB () + "','" +. getC () + "')";

That's right, it's the last Semicolon. It turns out that the semicolon cannot be added to a common SQL statement in a java statement. Although no error is reported in the database, but you must pay attention to this small problem in java.

In java, how to use SQL statements to modify and add delete operations is not only SQL statements, but specific code is required.

// Insert a record
SQL = "insert into TUser values (user_sequence.nextval ,?,?,?,?,?) ";
// Create a connection
Conn = DBUtil. getConnection ();
// Create a PreparedStatement object
Stmt = conn. prepareStatement (SQL );
// "?" In the SQL statement "? "Value assignment
// Stmt. setInt (1, ub. getUser_ID ());
Stmt. setString (1, ub. getMobile_Number ());
Stmt. setString (2, ub. getRoaming_Status ());
Stmt. setString (3, ub. getCom_Level ());
Stmt. setInt (4, ub. getCustomer_ID ());
Stmt. setInt (5, ub. getAccount_ID ());

// Return the number of affected rows after the update operation is executed.
Int rst=stmt.exe cuteUpdate ();
// Modify data
SQL = "update TUser set Mobile_Number = ?, Roaming_Status = ?, Com_Level = ?, Customer_ID = ?, Account_ID =? Where User_ID = "+ ub. getUser_ID ();
// Create a connection
Conn = DBUtil. getConnection ();
// Create a PreparedStatement object
Stmt = conn. prepareStatement (SQL );
// "?" In the SQL statement "? "Value assignment
// Stmt. setInt (1, ub. getUser_ID ());
Stmt. setString (1, ub. getMobile_Number ());
Stmt. setString (2, ub. getRoaming_Status ());
Stmt. setString (3, ub. getCom_Level ());
Stmt. setInt (4, ub. getCustomer_ID ());
Stmt. setInt (5, ub. getAccount_ID ());

// Delete data
SQL = "delete from TUser where User_ID =" + id;
// Create a connection
Conn = DBUtil. getConnection ();
<G id = "1"> </G>
 
Java SQL insert statement

Is your parameter a string? You should enclose it in quotation marks.
It should be written as follows:
SQL = "insert into list1 (username, password, gender, age)" + "VALUES ('A', 'B', 'C', 'D ')";
Remember not to use quotation marks for Field Names