Is it Possible to store and retrieve boolean values in a VARCHAR2 column in a table using JDBC?

Yes, in Oracle you can store and retrieve a boolean value into a table for a column with VARCHAR2 datatype.

If you do so, the true and false values are stored as 1 and 0 and the retrieved as same (respectively).

Example

Let us create a table with name sampleTable in the Oracle database Using CREATE statement as −

CREATE TABLE sampleTable(
   ID INT,
   ProductName VARCHAR (20) NOT NULL,
   CustomerName VARCHAR (20) NOT NULL,
   IsBillDue VARCHAR (20) NOT NULL,
   DeliveryDate date,
   Price INT,
   Location varchar(20)
);

The column IsBillDue specified whether bill paid.

Following JDBC program establishes the connection with the Oracle database and populates the table sampleTable, inserting a boolean value in the column IsBillDue which is of type varchar2.

Example

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class BooleanTest {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      //Getting the connection
      String oracleUrl = "jdbc:oracle:thin:@localhost:1521/xe";
      Connection con = DriverManager.getConnection(oracleUrl, "system", "password");
      System.out.println("Connection established......");
      //Inserting values to a table
      String query = "INSERT INTO sampleTable values (?, ?, ?, ?, ?, ?) ";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "Key-Board");
      pstmt.setString(2, "Raja");
      pstmt.setBoolean(3, true);
      pstmt.setDate(4, new Date(1567296000000L));
      pstmt.setInt(5, 7000);
      pstmt.setString(6, "Hyderabad");
      pstmt.execute();
      pstmt.setString(1, "Earphones");
      pstmt.setString(2, "Roja");
      pstmt.setBoolean(3, false);
      pstmt.setDate(4, new Date(1556668800000L));
      pstmt.setInt(5, 2000);
      pstmt.setString(6, "Vishakhapatnam");
      pstmt.execute();
      pstmt.setString(1, "Mouse");
      pstmt.setString(2, "Puja");
      pstmt.setBoolean(3, true);
      pstmt.setDate(4, new Date(1551398399000L));
      pstmt.setInt(5, 3000);
      pstmt.setString(6, "Vijayawada");
      pstmt.execute();
      pstmt.setString(1, "Mobile");
      pstmt.setString(2, "Vanaja");
      pstmt.setBoolean(3, false);
      pstmt.setDate(4, new Date(1551395452000L));
      pstmt.setInt(5, 9000);
      pstmt.setString(6, "Chennai");
      pstmt.execute();
      System.out.println("Contents of the table: ");
      //Retrieving data
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from sampleTable");
      while(rs.next()) {
         System.out.print("Name: "+rs.getString("ProductName")+", ");
         System.out.print("Customer Name: "+rs.getString("CustomerName")+", ");
         System.out.print("Dispatch Date: "+rs.getString("ISBILLDUE")+", ");
         System.out.print("Delivery Time: "+rs.getTime("DELIVERYDATE")+", ");
         System.out.print("Price: "+rs.getInt("PRICE")+", ");
         System.out.print("Location: "+rs.getString("LOCATION"));
         System.out.println();
      }
   }
}

Output

Connection established......
Contents of the table:
Name: Key-Board, Customer Name: Raja, Dispatch Date: 1, Delivery Time: 00:00:00, Price: 7000, Location: Hyderabad
Name: Earphones, Customer Name: Roja, Dispatch Date: 0, Delivery Time: 00:00:00, Price: 2000, Location: Vishakhapatnam
Name: Mouse, Customer Name: Puja, Dispatch Date: 1, Delivery Time: 00:00:00, Price: 3000, Location: Vijayawada
Name: Mobile, Customer Name: Vanaja, Dispatch Date: 0, Delivery Time: 00:00:00, Price: 9000, Location: Chennai