Is there a way to prepare a sql statement in Java without using a Connection object?

Question

I am new to JDBC, so this is probably a very simple question.

I need to run several SQL statements, so I'm trying to write a generic method runSQLResultSetthat accepts an Stringsql statement and returns ResultSet. I want him to take care of opening a connection to the database, executing the instruction, storing ResultSetin the object CachedRowSetImpl(so that it remains after the connection is closed) and closing the connection. I created a method that does this and it works.

Now my problem is that I want to use it for dynamic statements constructed with variables. I looked around, and it seems to me that I should change my method to Stringuse instead PreparedStatement. Then I can build it PreparedStatementfrom the other side and pass it to the method. The problem is that I cannot create PreparedStatementwithout an object Connection. I can open the connection before preparing the instruction, but this defeats my goal of decomposing the database processing into a method runSQLResultSet. I need a way to create an SQL statement with dynamic components without a connection object and pass it to a method that will then execute it. Is there any way to do this withPreparedStatement? Is there any other expression object that I can use instead? Otherwise - is there a better way to do this?

Answer 1

You cannot create it without connecting to the database. A PreparedStatementwill be precompiled in the database and therefore really needs an open connection.

Instead, you can simply consider dynamically assembling an SQL string. Generating placeholders PreparedStatement(objects ?) in a loop and using String#format()put them in an SQL string. u may also consider simply passing the variables to your method runSQLResultSetand building there instead.

According to the comments, here is an example:

try {
    connection = database.getConnection();
    statement = connection.prepareStatement(SQL);
    setValues(statement, values);

    // ...

.

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

Answer 2

public ResultSet excuteStatement(String statement, Object... params){
  statement = conn.prepareStatement(statement);
  int i = 1;
  for (Object o:params){
    statement.setObject(i++,o);
  }
  ResultSet rs = statement.executeQuery();
  return rs;
}

Answer 3

, , , , . SQL Construction Kit JDBC , SQL Factory Builder.

Answer 4

, , . ResultSet, , .

.

public ResultSet getResult(String param1, String param2){
 statement = conn.prepareStatement(yourQuery);// conn must be an open connection
 statement.setString(1,param1);
 statement.setString(2,param2);
 ResultSet rs = statement.execusteQuery();
 return rs;
}

This is a basic example of how you can do something like this if I understood your question correctly.