MySQL open every time a new Statement?

Question

I'm working with MySQL for long time now and I know that I should only open one Connection at one time.

But now I have an other question that I think is not asked yet. If so please don't blame me, because I couldn't find an answer yet.

Every time I run a MySQL-Query or Update I open a new Statement like this:

Statement st = c.createStatement();
ResultSet res = st.executeQuery("SELECT * FROM ...;");
...
st = c.createStatement();
st.executeUpdate("INSERT INTO ...;");
...
st = c.createStatement();
res = st.executeQuery("SELECT * FROM ...;");
...

First of all. Is it possible to create just one Statement for all the Querys and Updates? Like this:

Statement st = c.createStatement();
ResultSet res = st.executeQuery("SELECT * FROM ...;");
...
st.executeUpdate("INSERT INTO ...;");
...
res = st.executeQuery("SELECT * FROM ...;");
...

And the other question I have is, if I have to close these Statements in the end or if Java does this automatically.

score 1 · Accepted Answer · edited May 23 '17 at 12:08

1

You can use a Statement for multiple times. It would be inefficient if you create another Statement object each time.

For JDBC 4.0 and earlier (Java 6 and earlier):

A Statement is closed when the connection is closed

So if you close your connections you dont have to worry about closing statements. Closing of connections was disscussed here

edited May 23 '17 at 12:08

Community

1
1

answered Jun 17 '16 at 14:46

Sergei Podlipaev

1,331
1
14
34

Thanks... Because I don't close the MySQL-Connection for days – Nicola Uetz Jun 17 '16 at 14:48

CSK · Answer 2 · 2016-06-17T14:50:20.287

0

you can create a method to do the Update/Insert action

public void updateQuery(String query){
...
}

To prevent SQL injection, try to use PreparedStatement

edited Jun 17 '16 at 14:50

answered Jun 17 '16 at 14:48

CSK

352
1
6

So I should use only one Statement for Updates... Shall I use a Statement every time I open a new Query? – Nicola Uetz Jun 17 '16 at 14:49
you can pass the Statement to the method if you want – CSK Jun 17 '16 at 14:52
So I can use one only Statement for all Querys and all Updates? – Nicola Uetz Jun 17 '16 at 14:56

MySQL open every time a new Statement?

2 Answers2