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

Question

I'm pretty new to JDBC, so this is probably a very straightforward question.

I have to run several SQL statements, so I'm trying to write a generic "runSQLResultSet" method that takes a String sql statement and returns a ResultSet. I want it to take care of opening the database connection, executing the statement, storing the ResultSet in a CachedRowSetImpl object (so that it will persist after the connection is closed), and closing the connection. I created a method that does this and it works.

My problem now is that I want to be able to use it for dynamic statements that are built with variables. I looked around, and it seems that I should really change my method to take a PreparedStatement instead of just a plain String. Then I can build the PreparedStatement on the other side and pass it to the method. The problem is that I can't seem to create a PreparedStatement without a Connection object. I can open the connection before preparing the statement, but that defeats my purpose of factoring out the database processing into the runSQLResultSet method. I need a way to build a 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 with a PreparedStatement? Is there any other statement object I can use instead? Otherwise - is there any better way to do this?

Answer 1

what you are missing is the concept of Database connection pooling, you should never be instantiaing connections directly, the pool controls the database connections under the covers. You can look at my open source project SQL Construction Kit for some inspiration on one light weight way to deal with JDBC and how to build dynamic SQL statements using Factory and Builder patterns.

Answer 2

You cannot create one without a DB connection. A PreparedStatement will be precompiled in the DB and thus really needs an open connection.

You can instead also just consider to dynamically build the SQL string instead. Generating the PreparedStatement placeholders (the ? things) in a loop and and using String#format() to put them in the SQL string. u can also consider to just pass the variables to your runSQLResultSet method and build there instead.

As per the comments, here's 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 3

If I understand you correctly, you want to use the same query with different variables inserted into the query. You can have your method return the ResultSet like it currently does and pass the variables in as parameters to the method.

then you can put the parameters into the query inside of the method.

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;
}

That's a basic example of how you might do something like that if I understood your question correctly.

Answer 4

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;
}