无法使用 ExecuteQuery()发出数据操作语句

小开

最佳答案

要操作数据，实际上需要 executeUpdate()而不是 executeQuery()。

Here's an extract from the executeUpdate() javadoc which is already an answer at its own:

执行给定的 SQL 语句，该语句可以是 INSERT、 UPDATE 或 DELETE 语句，也可以是不返回任何值的 SQL 语句，例如 SQL DDL 语句。

小开

这就是 executeUpdate的作用。

Here's a very brief summary of the difference: http://www.coderanch.com/t/301594/JDBC/java/Difference-between-execute-executeQuery-executeUpdate

小开

executeQuery()返回一个 ResultSet。我不太熟悉 Java/MySQL，但是要创建索引，你可能需要一个 executeUpdate()。

小开

使用 executeUpdate()发出数据操作语句。executeQuery()仅用于 SELECT 查询(即返回结果集的查询)。

小开

Besides executeUpdate() on the parentheses, you must also add a variable to use an SQL statement.

例如:

PreparedStatement pst =  connection.prepareStatement(sql);
int numRowsChanged = pst.executeUpdate(sql);

小开

在执行 DML 语句时，应该使用 executeUpdate/execute而不是 executeQuery。

下面是一个简短的比较:

小开

这段代码适合我: 我使用 INSERT 设置值，并使用 SELECT 获得该值的 LAST _ INSERT _ ID () ; 我使用 java NetBeans 8.1、 MySql 和 java.JDBC 驱动程序

                try {


String Query = "INSERT INTO `stock`(`stock`, `min_stock`,
`id_stock`) VALUES ("


+ "\"" + p.get_Stock().getStock() + "\", "
+ "\"" + p.get_Stock().getStockMinimo() + "\","
+ "" + "null" + ")";


Statement st = miConexion.createStatement();
st.executeUpdate(Query);


java.sql.ResultSet rs;
rs = st.executeQuery("Select LAST_INSERT_ID() from stock limit 1");
rs.next(); //para posicionar el puntero en la primer fila
ultimo_id = rs.getInt("LAST_INSERT_ID()");
} catch (SqlException ex) { ex.printTrace;}

小开

如果您使用的是 Spring boot，只需添加一个@Amendment 注释即可。

@Modifying
@Query
(value = "UPDATE user SET middleName = 'Mudd' WHERE id = 1", nativeQuery = true)
void updateMiddleName();

小开

对于删除查询-在 @Query之前使用 @Modifying和 @Transactional，如:-

@Repository
public interface CopyRepository extends JpaRepository<Copy, Integer> {


@Modifying
@Transactional
@Query(value = "DELETE FROM tbl_copy where trade_id = ?1 ; ", nativeQuery = true)
void deleteCopyByTradeId(Integer id);


}

它不会出现 java.sql.SQLException: Can not issue data manipulation statements with executeQuery()错误。

编辑:

由于这个答案得到了许多赞同，我将向您介绍文档以获得更多的理解。

@ Transactional

By default, CRUD methods on repository instances are transactional. For read operations,
the transaction configuration readOnly flag is set to true.
All others are configured with a plain @Transactional so that default transaction
configuration applies.

@ 修改

Indicates a query method should be considered as modifying query as that changes the way
it needs to be executed. This annotation is only considered if used on query methods defined
through a Query annotation). It's not applied on custom implementation methods or queries
derived from the method name as they already have control over the underlying data access
APIs or specify if they are modifying by their name.


Queries that require a @Modifying annotation include INSERT, UPDATE, DELETE, and DDL
statements.

小开

@Modifying
@Transactional
@Query(value = "delete from cart_item where cart_cart_id=:cart", nativeQuery = true)
public void deleteByCart(@Param("cart") int cart);

不要忘记在“查询”之前添加“修改”和“跨国”。这对我很有用。

要使用带有某些条件的本机查询和 JPA 删除记录，上面提到的注释非常重要。

小开

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java_swing_db", "root", "root");


Statement smt = conn.createStatement();


String sql = "SELECT * FROM `users` WHERE `email` = " + email + " AND `password` = " + password + " LIMIT 1;";


String registerSql = "INSERT INTO `users`(`email`, `password`, `name`) VALUES ('" + email + "','" + password + "','" + name + "')";


System.out.println("SQL: " + registerSql);
            

int result = smt.executeUpdate(registerSql);
System.out.println("Result: " + result);


if (result == 0) {
JOptionPane.showMessageDialog(this, "This is alredy exist");
} else {
JOptionPane.showMessageDialog(this, "Welcome, Your account is sucessfully created");
App.isLogin = true;
this.dispose();
new HomeFrame().show();
}
conn.close();