jdbc mysql select,使用JDBC在MySQL中插入select

I would like to have a value from a row inserted into an other row here is my code:

static void addVipMonth(String name) throws SQLException

{

Connection conn = (Connection) DriverManager.getConnection(url, user, pass);

PreparedStatement queryStatement = (PreparedStatement) conn.prepareStatement("INSERT INTO vips(memberId, gotten, expires) " +

"VALUES (SELECT name FROM members WHERE id = ?, NOW(), DATEADD(month, 1, NOW()))"); //Put your query in the quotes

queryStatement.setString(1, name);

queryStatement.executeUpdate(); //Executes the query

queryStatement.close(); //Closes the query

conn.close(); //Closes the connection

}

This code is not valid. How do I correct it?

解决方案

I get an error 17:28:46 [SEVERE] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MyS QL server version for the right syntax to use near ' NOW(), DATE_ADD( now(), INT ERVAL 1 MONTH )' at line 1 – sanchixx

It was due to error in SELECT .. statement.

Modified statement is:

INSERT INTO vips( memberId, gotten, expires )

SELECT name, NOW(), DATE_ADD( now(), INTERVAL 1 MONTH )

FROM members WHERE id = ?

You don't require VALUES key word when inserting with a select.

You used a wrong DATEADD function syntax. Correct syntax is Date_add( date_expr_or_col, INTERVAL number unit_on_interval).

You can try your insert statement as corrected below:

INSERT INTO vips( memberId, gotten, expires )

SELECT name FROM members

WHERE id = ?, NOW(), DATE_ADD( now(), INTERVAL 1 MONTH )

Refer to: