How to assign default value to column variable if column value is coming as null

Question

Hi I want to assign default value to column variable if value from select query for that variable is coming as null.

    @Entity
    @Table(name = "CDP_ALERTS")
    public class Country {

       @Column(name = "alert_reported_time")
    private String alertReportedTime;

    @Column(name = "unique_id")
    private String uniqueTrxId;

    @Column(name = "status_data")
    private String status;

public String getAlertReportedTime() {
        return alertReportedTime;
    }

    public void setAlertReportedTime(String alertReportedTime) {
        this.alertReportedTime = alertReportedTime;
    }
public String getUniqueTrxId() {
        return uniqueTrxId;
    }

    public void setUniqueTrxId(String uniqueTrxId) {
        this.uniqueTrxId = uniqueTrxId;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
    }

Here status_data column value is coming as null sometime. below is the select query.

@Query( "SELECT a FROM Country a WHERE to_char(alert_reported_time,'DD-MM-YY')=to_char(current_date,'DD-MM-YY') order by alert_reported_time desc")
    List<Country> findCountryByDate();

Below is the output where in one object I am getting value as closed for status but in one object I am getting value as null.

{

[
        "alertSubject": "CWDigital Alert in MS onlinepayment with StatusCode 400",
        "alertReportedTime": "2020-05-29 15:16:03",
        "uniqueTrxId": "1018",
        "status": null,
        "reason": null,
        "conversationId": "ecd6184d-b2f1-4545-c5f2-1ac6d1df48fc",
        "clusterName": "patlacw01",
        "statusCode": "400\n",
        "nameSpace": "com-att-cwdigital-shop-prod"
    },
    {
        "alertSubject": "CWPOS Alert-500 in mS OrderManagement from pbhmacw01 and m26845@prod.shop.cwdigital.att.com",
        "alertReportedTime": "2020-05-29 15:15:41",
        "uniqueTrxId": "1017",
        "status": "CLOSED",
        "reason": null,
        "conversationId": "ee66359e-f87d-4eff-ce50-02ff6e18879a",
        "clusterName": "pbhmacw01",
        "statusCode": "500\n",
        "nameSpace": "com-att-cwpos-prod"
    }]

You could add a null check in the getter and return the required value if it is a null. — Deepak, May 29 '20 at 12:49
@srp **@Column(name="Price", columnDefinition="Decimal(10,2) default '100.00'")** works for insert statement it does not work for select. — Shubham Gosewade, May 29 '20 at 13:00

score 1 · Answer 1 · answered May 29 '20 at 17:19

You could change your getter method for the property.

public String getStatus(){
   if(status != null) return status;
   return "Whatever you want";
}

Also, if you want to persist default value in db. you could set a default value in property.

@Column
private String status = "My default value";

RKG · Answer 2 · 2020-05-29T14:21:03.983

You need to set the nativeQuery = true, and use each column in select query with some sql method which return the expected value if that column has null value. Like in H2 you can use IFNULL(column, expectedvalue), in oracle you can use nvl(column, exepectedValue).

example:

String query = "Select alertReportedTime, uniqueTrxId, IFNULL(status, 'defaultvalue') as status from CDP_ALERTS";

@Query(value = query, nativeQuery = true)

List <Country> findCountryByDate();

score 0 · Answer 3 · answered Jul 18 '23 at 00:07

If you are adding a new column to existing table, you can use JPA annotations to set a default value. This is very helpful especially if you have @Id annotation on the column. This will set the default value of "SectionInput" instead of null on every row of the table. This is very efficient and super fast.

@Id
@Column(columnDefinition = "varchar(255) default 'SectionInput'")
protected String inputSource;

In my case, this new column was added to the existing composite key, that's why I have @Id annotation on it.

How to assign default value to column variable if column value is coming as null

3 Answers3