//package jdbcsetnulltest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.UUID;

/**
 *
 * @author imaskar
 */
public class JdbcSetNullTest {

  /**
   * @param args the command line arguments
   * @throws java.lang.ClassNotFoundException
   * @throws java.sql.SQLException
   */
  public static void main(String[] args) throws ClassNotFoundException, SQLException {
    Class.forName("org.postgresql.Driver");
    final String jdbcString = ""; //use your own
    final String sql = "select 'ok' where null=null or (null is null)";
    final String sqlBind = "select 'ok' where ?=? or (? is null)";
    final String sqlBindCorrect = "select 'ok' where ?=? or (cast (? as uuid) is null)";
    final Connection conn = DriverManager.getConnection(jdbcString);
    
    final UUID uuid = UUID.randomUUID();
    
    try (Statement st = conn.createStatement(); PreparedStatement stp = conn.prepareStatement(sqlBind);) {
      ResultSet rs = st.executeQuery(sql);
      printOut("Fixed", rs);
      
      try {
        stp.setObject(1, uuid, Types.INTEGER);
        stp.setNull(2, Types.INTEGER);
        stp.setNull(3, Types.INTEGER);
        rs = stp.executeQuery();
        printOut("Prepared INTEGER", rs);
      } catch (SQLException sQLException) {
        System.out.println("Prepared INTEGER returned exception "+sQLException.getMessage());
      }
      try {
        stp.clearParameters();
        stp.setObject(1, uuid, Types.BINARY);
        stp.setNull(2, Types.BINARY);
        stp.setNull(3, Types.BINARY);
        rs = stp.executeQuery();
        printOut("Prepared BINARY", rs);
      } catch (SQLException sQLException) {
        System.out.println("Prepared BINARY returned exception "+sQLException.getMessage());
      }
      try {
        stp.clearParameters();
        stp.setNull(1, Types.BINARY);
        stp.setNull(2, Types.BINARY);
        stp.setNull(3, Types.BINARY);
        rs = stp.executeQuery();
        printOut("Prepared DOUBLE BINARY", rs);
      } catch (SQLException sQLException) {
        System.out.println("Prepared DOUBLE BINARY returned exception "+sQLException.getMessage());
      }
      try {
        stp.clearParameters();
        stp.setObject(1, uuid, Types.OTHER);
        stp.setNull(2, Types.OTHER);
        stp.setNull(3, Types.OTHER);
        rs = stp.executeQuery();
        printOut("Prepared OTHER", rs);
      } catch (SQLException sQLException) {
        System.out.println("Prepared OTHER returned exception "+sQLException.getMessage());
      }
      try {
        stp.clearParameters();
        stp.setObject(1, uuid.toString(), Types.VARCHAR);
        stp.setNull(2, Types.VARCHAR);
        stp.setNull(3, Types.VARCHAR);
        rs = stp.executeQuery();
        printOut("Prepared VARCHAR", rs);
      } catch (SQLException sQLException) {
        System.out.println("Prepared VARCHAR returned exception "+sQLException.getMessage());
      }
    }
    
  }

  private static void printOut(final String header, final ResultSet rs) throws SQLException {
    System.out.print(header);
    try {
      while (rs.next()) {
        System.out.print(": returned ");
        System.out.println(rs.getString(1));
      }      
      rs.close();
    } catch (SQLException sQLException) {
        System.out.println("exception "+sQLException.getMessage());
    }
  }
  
}