Is it possible to do an INSERT and an UPDATE in the same query in java?

Question

Is it possible to do an INSERT and an UPDATE in the same query in java?

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

I would like to know if it is possible to make a INSERT and a UPDATE in the same query, that is, in the same operation.

I'm using the following to do both.

public Connection conn = null;
...
conn = DriverManager.getConnection (url, userName,password);
...

String Query_SQL_UPDATE = "UPDATE users SET name = 'exemplo';";
String Query_SQL_INSERT = "INSERT INTO 'users_logs' (user_id, name) VALUES ('1','demo');";

PreparedStatement Update_SQL = conn.prepareStatement(Query_SQL_UPDATE);
                  Update_SQL.execute();
PreparedStatement Insert_SQL = conn.prepareStatement(Query_SQL_INSERT);
                  Insert_SQL.execute();

Is there any way to do this, or even simplify these commands? If so, what are they?

mysql sql java sql-update sql-insert

asked by anonymous 13.01.2017 / 01:16

2 answers

1

This is independent of the programming language. So far as I know, databases (relational, at least) do not accept to do more than one query at the same time (except subqueries, but there it is only for select). You can use transaction and perform a series of queries that change values and then commit .

Correct me if I'm wrong, but by reading your code I understand that you want to "log" every change made, right? There are better ways to do this, but in your implementation, you could outsource this to a method of your generic dao fault and use transaction to ensure that the two information is persisted (both changing the data and logging ). This way, whenever you run a update , for example, you guarantee that you will log the log.

Attention: insert into x values(1,2,3); update tabela set col = 1; does not represent a single query. They are two different queries. The Bank runs one after another. The query is defined up to ; or as far as the SGDB understands that the query goes (lack of ; ). By doing so without transaction you run the risk of executing a query and not executing the query.

13.01.2017 / 11:28

Text field mask does not work in the mobile environment Why does a parameter have two const's in its declaration?

score 1 · Accepted Answer

Yes, it is possible. And there are two ways to do it.

The first is through insertion of these queries into a SP (Stored Procedure).

In this way it is enough to call the SP with the parameters and almost any sequence of procedures can be performed in the database.

More information on creating SPs for MySql can be viewed here .

The second option (and I think is the most convenient for your case) is to change the string connection to the database to allow multiple queries. If this is not done, an exception will invariably be thrown:

String dbStringConn = "jdbc:mysql:///bancodados?allowMultiQueries=true";

After correctly setting up the url connection, just call the execute(query) method. Your code would look something like:

String query = "UPDATE users SET name = 'exemplo'; INSERT INTO 'users_logs' (user_id, name) VALUES ('1','demo');";

PreparedStatement stmt = conn.prepareStatement(query);
stmt.execute();

Notice that the queries are separated by ; (semicolon).

Further details on the parameters allowed in the url connection to MySql via the JDBC driver can be found here .