CODEWITHSUNDEEP
javajdbcoracle11g
Tanzeel
Tanzeel

Reputation: 5008

How to create a column in oracle with object data type

I'm a web developer. I'm from Javascript and Typescript background. A few days ago I decided to learn JDBC with Oracle. I've created a very basic JDBC project with JavaFX and Oracle. (Actually I'm trying to mimic Microsoft Azure). I've a Date field also which will get its value from JavaFx built-in Datepicker component. Here is some useful information:

Columns in table: mybranches

  1. branch_name varchar(50)
  2. branch_created_on date
  3. branch_pulled_from varchar(50)

The problem is the 2nd column i.e. the branch_created_on field.

Here's my java code: DataService.java

import java.time.LocalDate;
...

public class DataService {
    Connection con;
    PreparedStatement pstmt;
    
    static String branch_name="";
    static LocalDate branch_created_on;
    static String branch_pulled_from="";
    
    DataService() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","tiger","scott");
        }
        catch(Exception e){ 
            System.out.println(e);
        }
    }
    
    public void getValue() {
        branch_name=AddNewBranchRecord.branchNameTextField.getText();
        branch_created_on=AddNewBranchRecord.datePicker1.getValue();
        branch_pulled_from=(String) AddNewBranchRecord.combo_box_1.getValue();
    }
    
    public void putValue() {
        System.out.println("Branch name: "+branch_name);
        System.out.println("Branch created on: "+branch_created_on);
        System.out.println("Branch pulled from: "+branch_pulled_from);
    }
    
    public void insertRecord() {
        getValue();
        try {
            pstmt=con.prepareStatement("INSERT INTO mybranches (branch_name,branch_created_on,branch_pulled_from) VALUES (?,?,?)");  
            pstmt.setString(1,branch_name);
            pstmt.setObject(2,branch_created_on);
            pstmt.setString(3,branch_pulled_from);
            pstmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(DataService.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

The output of putValue is also correct:

Branch name: 1000-test

Branch created on: 2020-02-11

Branch pulled from: master

But the moment I call insertRecord method, I get this exception:

SEVERE: null

java.sql.SQLException: Invalid column type

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

...

This exception is not there If I remove that date column from the database table as well as java code. I'm extremely sorry If I'm asking a stupid question. I'm from Angular and React background. Please correct me.

Upvotes: 0

Views: 121

Answers (1)

Alex Poole
Alex Poole

Reputation: 191435

You don't really want to be using setObject().

pstmt.setDate(2,branch_created_on); is giving me error: incompatible types: LocalDateCannot be converted to Date.

You can combine this with @Christopher's comment to do:

pstmt.setDate(2,java.sql.Date.valueOf(branch_created_on));

With that change and the Java variables hard-coded (rather than from a picker) that compiles and runs, with output:

Branch name: 1000-test
Branch created on: 2020-02-11
Branch pulled from: master

and in the database:

select * from mybranches;

BRANCH_NAME                                        BRANCH_CREATED_ON   BRANCH_PULLED_FROM                                
-------------------------------------------------- ------------------- --------------------------------------------------
1000-test                                          2020-02-11 00:00:00 master

Upvotes: 1

Related Questions