how to print Statement (CallableStatement) in Java?

How can I print this OracleCallableStatement ?

   ocstmt = (OracleCallableStatement) connection.prepareCall("{?= call 
            package.method(id => ?, name=>?)}");
   ocstmt.registerOutParameter(1, OracleTyp开发者_StackOverflow中文版es.CURSOR);            
   ocstmt.setInt(2, obj.getId());
   ocstmt.setString(3, obj.getName());
   ocstmt.execute();
   resultSet = ocstmt.getCursor(1);

What I mean is how can I know that what query goes to the database, how can I print the query? because sometimes it gives me error like "Wrong type" that is why I want to look at this query

---

Are you using log4j?

If so, add loggers for sql like below.

log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG

If you are using a ORM framework such as ibatis, you could add additional logger like below.

log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG

---

Yes, you can do this. You can either wrap your callable statement in a proxy that can substitute the actual values when you print it (and show the sql), or hunt around for a driver that has a meaningful toString. javaworld article There is also p6spy, and others. Stored procedures are harder, but still doable.

---

You can't get the SQL by printing the Statement.

Is the example you posted one of the "sometimes" that triggers the error?

Why do you have to case this to an OracleCallableStatement? What part of the call is not the standard CallableStatement?

---

In general, use myObject.toString() to see what it prints. You may or may not be able to see the full query though. If you can't get it to go, the first thing that I would look at is the API documentation(javadocs for that Oracle library or driver that you're using)

---

I'm not sure if I understand the question, but it seems like you want to see this:

  String sql = "{?= call package.method(id => ?, name=>?)}";
  System.out.println(sql);
  ocstmt = (OracleCallableStatement) connection.prepareCall(sql);
  ...

---

It's possible to use proxy jdbc driver to log all jdbc database actions. this driver can prints all statements with values and all results.

---

My solution is use ProxyDataSourceBuilder(use it in Spring Boot project).

@Bean
public DataSource dataSource() {
    SLF4JQueryLoggingListener loggingListener = new SLF4JQueryLoggingListener();
    return ProxyDataSourceBuilder
            .create(datasource)
            .listener(loggingListener)
            .build();
}

...

@Autowired
private DataSource dataSource;

Connection connection = dataSource.getConnection();
ocstmt = (OracleCallableStatement) connection.prepareCall("{?= call 
        package.method(id => ?, name=>?)}");

And just turn on logging in application.yml:

logging: 
    level:
      net.ttddyy.dsproxy.listener.logging: debug

---

Try

 > java -classpath ojdbc8.jar oracle.jdbc.driver.OracleSql false false "<your sql here>"

That will print the SQL that the driver sends to the database, among other things. This is not documented nor supported, but it's been around forever.

---

I thought it might be useful if you are looking whether executed query has value or not

System.out.println("value : "+CallableStatement.execute());

i.e The "false" returned by "CallableStatement.execute()" means that the JDBC statement didn't read any rows, (so there's no ResultSet to read). This is what you'd expect, as stored procedures don't directly return ResultSets, they only return values for any OUT or INOUT parameters.