CODEWITHSUNDEEP
javamysqljdbcresultset
mp2893
mp2893

Reputation: 69

Reuse of ResultSet member variable in a multi-threaded environment

I am implementing a code that uses JDBC driver.

Below is the code that I made.

public class MysqlUtils {
    public Connection conn;
    public ResultSet rs;
    public PreparedStatement stmt;

    public MysqlUtils(String address, String id, String passwd) {
        try {
            conn = DriverManager.getConnection(address, id, passwd);
            stmt = null;
            rs = null;
        } catch (SQLException e) {
            // error management
        }
    }

    public void getSomeData(long id) {
        try {
            stmt = conn.prepareStatement("SELECT * FROM some_table");
            rs = stmt.executeQuery();
            rs.next();
            System.out.println(rs.getString("some_column");
        } catch (SQLException e) {
            // error management
        }
    }
}

I have declared Connection conn, ResultSet rs, PreparedStatement stmt as member variables because somehow I thought that might help me enhance performance.

I have a couple of questions.

  1. If I call getSomeData() consecutively, will stmt and rs be assigned new objects every time?

  2. Regardless of the answer to the question above, if I run this code in a multi-threaded environment(multiple threads using MysqlUtils class), will there be a mix-up because I didn't declare ResultSet rs in getSomeData()?

  3. Was declaring Connection conn, ResultSet rs, PreparedStatement stmt as member variables a bad choice? In other words, is my implementation of JDBC a viable one?

Thanks for the help.

Upvotes: 0

Views: 1242

Answers (2)

JB Nizet
JB Nizet

Reputation: 692073

  1. Yes. The method will be executed, and thus stmt and rs will take new values. Of course, you might have multiple instances of your class, and thus multiple instances of those two fields.
  2. Yes. This code is completele thread-*un*safe. Public fields in general should almost always be avoided. Especially in a multi-threaded environment
  3. Yes, it's a bad choice. The scope of a variable should be as small as possible. And these variables are used in a single method, and reassigned every time. They should be local variable.

Also:

  • a method getSomeData() should return something, and not just printing something
  • the ResultSet and the Statement should be closed, in a finally block
  • I hope the error management doesn't consist in swallowing the exception

I would advise using spring-jdbc, which takes care of all the plumbing code for you, and avoids all the problems your code currently has.

Upvotes: 1

useriby
useriby

Reputation: 55

Do not use ResultSet outside of the method.... with while(rs.next) (rs=resultSet) you are looping through a database table and retrieving values!

Upvotes: 0

Related Questions