Geek asks and answers

Where to put the prepared OBJECTS expression in Java?

I want to use prepared instructions. I read that the advantage of prepared statements is that they do not need to be analyzed or compiled every time to reduce the load. Now my question is: where does the “recognition” of an already prepared statement occur in Java or in my database system? I ask because I want to know where to store my PreparedStatement object in my code: as a class attribute and set parameters with each request or create a new prepared statement object whenever there is a request.

    public class Option1 {
       private PreparedStatement myStatement;
       public Option1() {
          // create the myStatement object
          myStatement = conn.prepareStatement("");
       }
       public List<Items> query() {
          // just use the myStatement object
          myStatement.setString(1, "foo");
       }
   }

    public class Option2 {
       public List<Items> query() {
          PreparedStatement myLocalStatement = conn.prepareStatement("");;
          // create and use the statement
          myLocalStatement.setString(1, "foo");
       }
   }

Now my question is: what is the best way to do this, option 1 or 2? However, should I do a “cleanup” after each run, doing it myStatement.close()right?

, -: ?

: : 1 2, ^^

+5
4

SQL ( where) . , , , ( "?" ).

, . . , Java EE.

, , , , (, ). , JDBC .
, , - .

+4

2

public class Option2 {
   public List<Items> query() {
      PreparedStatement myLocalStatement = conn.prepareStatement("");;
      // create and use the statement
      myLocalStatement.setString(1, "foo");
   }
}

, , , .

:

public class Option2 {
   public List<Items> query(String sql, String parameter) {
      PreparedStatement myLocalStatement = conn.prepareStatement(sql);
      // create and use the statement
      myLocalStatement.setString(1, parameter);
   }
}

:

Option2 myQuery = new Option2();
myQyery.query("SELECT * FROM PERSON WHERE NAME = :?","ANY_NAME");
+2

1: ..

public class Option1 {
       private PreparedStatement myStatement;
       public Option1() {
          // create the myStatement object
          myStatement.setString(1, "foo");
       }
       public List<Items> query() {
          // just use the myStatement object
       }
   }

: . -

pst = con.prepareStatement(myQuery); , Option1()

+1

Personally, I like the second option, and this is how I imagine

public void addAccount(String username, String password, String name) {
String query="INSERT INTO Accounts (username, password, name) VALUES (?,?,?)";
try(Connection connection = dataSource.getConnection()) {
  try(PreparedStatement statement = connection.preparedStatement(query)){
     statement.setString(1, username);
     statement.setString(2, password);
     statement.setString(3, name);
     statement.executeUpdate();
  }
 } catch(SQLException e) {
  e.printStacktrace();
 }

The reason you should use try-with-resource is only because it handles common coding errors such as closing if an error occurs. It also ensures that after execution your statement / connection / resultset will be closed (or any other problems)

0
source

More articles:


All Articles