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

Asked

Viewed 1,474 times

4

I wonder if it is possible to make one INSERT and a UPDATE in the same query, that is, in the same operation.

I’m using the following to do both operations.

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.

java mysql sql sql-update sql-insert

  • Since you are using free query. I imagine that if you put a ";" and the second instruction, it works.

  • It seems there’s nothing in the JDBC documentation that says you can’t repurpose, I’ve even done something similar (Insert followed by select) and had no problems. In the case of Statement, you can reuse without problems, but if you need a return, the result is not reusable.

  • @mauhumor had already tried, however unsuccessfully, the reason I do not know. Let’s expect some response and see if the certain diegofm

  • @Florida the way I did was passing the first query, having it executed, then passing the second query to the same statement and running again. Send the two together at once I don’t know if it’s possible(nor recommendable).

  • So I guess you can’t do that, because they’re different operations. I don’t see any problem with the way you’re doing it. Perhaps, to maintain integrity, in case one of the operations fails, it would be good to use transaction.

2 answers

2


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

To first is by inserting these queries in an SP (Stored Procedure).

This way it is enough to call the AS with the parameters and practically any sequence of procedures can be performed in the database.

More information about creating Sps for Mysql can be viewed here.

To second option (and I think it 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, a Exception will invariably be launched:

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

After properly configuring the url connection, just call the method execute(query). 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 the darlings are separated by ; (semicolon).

More details on the parameters allowed in url Mysql connection via JDBC driver can be consulted here.

  • I hadn’t noticed that from allowMultiQueries=true, later I’ll try using it.

  • I searched after your question and found some information about another option addBatch, executeBatch if I could enrich the answer with this other method, I believe that is what is missing for an answer to be accepted. :)

  • 1

    addBatch and executeBatch are related to running the same query multiple times. For example, when we want to perform 3000 database insertions, we call this batch (batch) executions. Your doubt refers to the execution of multiple queries, not a single query multiple times. So in this case, addBatch and executeBatch escape the scope of the question. An example can be seen here http://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc

0

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

Correct me if I’m wrong, but reading your code, I understand that you want to "log in" every change you make, right? There are better ways to do this, but in your implementation, you could outsource this in a method of your dao generic and use transaction to ensure that the two pieces of information are persisted (both the data change and the log record). Thus, whenever you run a update, for example, you guarantee that you will register the log.

Heed: do insert into x values(1,2,3); update tabela set col = 1; does not represent a single query. They’re two different darlings. The Bank executes one after the other. The query is set to the ; or how far SGDB understands that the query goes (missing from ;). In doing so without transaction you run the risk of executing one query and not executing the other.

Browser other questions tagged java mysql sql sql-update sql-insert

You are not signed in. Login or sign up in order to post.