0

We have a product which runs in mysql, oracle and sqlserver. We are writing one custom module which needs to access a customer table and retrieve data. We are writing it using spring data JPA. For this we have Customer pojo as below and created a Repository.

Note that the columns are case sensitive. The POJO that works for oracle is as below,

import lombok.*;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Getter
@Setter
@ToString
@Table(name="customer")
public class Customer{
    @Id
    @Column(name="\"cxCifID\"")
    private String cxCifID;
    @Column(name="\"hostCustId\"")
    private String hostCustId;
    @Column(name="\"custMobile1\"")
    private String custMobile1;
    @Column(name="\"custEmail1\"")
    private String custEmail1;
    @Column(name="\"custName\"")
    private String custName;
    @Column(name="\"custMotherTongue\"")
    private String custMotherTongue;
}

The same will not work in mysql and will give the error as below,

"Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"cxCifID" as cxcifid1_0_, customer0_."custEmail1" as custemai2_0_, customer0_."c' at line 1"

If i use the below POJO for mysql it works without error,

import lombok.*;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Getter
@Setter
@ToString
@Table(name="customer")
public class Customer{
    @Id
    @Column(name="cxCifID")
    private String cxCifID;
    @Column(name="hostCustId")
    private String hostCustId;
    @Column(name="custMobile1")
    private String custMobile1;
    @Column(name="custEmail1")
    private String custEmail1;
    @Column(name="custName")
    private String custName;
    @Column(name="custMotherTongue")
    private String custMotherTongue;
}

The question is how can I create and use a single POJO which can be used in both the databases or in fact in all the databases? Please help

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
Sijo Kurien
  • 95
  • 2
  • 10
  • Doesn't the second one works on oracle? – Jens Feb 16 '23 at 07:51
  • Maybe https://stackoverflow.com/a/36362597/3636601 helps – Jens Feb 16 '23 at 07:53
  • @Jens The second one doesnt work for oracle – Sijo Kurien Feb 16 '23 at 09:23
  • @SijoKurien In what way doesn't it work for Oracle? Does it actually generate an error, or does it just not produce case-sensitive names? Use of always-quoted, case-sensitive names in Oracle is generally considered bad practice. Your *code* can be case sensitive without requiring the actual tables and columns to be if the underlying database doesn't support it. As you are discovering, trying to force Oracle to be case-sensitive is going to force you to deal with Oracle differently than you would any other DB, both in your object naming *and* in your code. – pmdba Feb 16 '23 at 12:26

0 Answers0