Creating a unique listing_no (Java/PostgreSQL)

Question

I am working on a option in a Menu function that posts the car for the sale in a database. The option asks for the user to enter the year, make, condition and price, which is then inserted into the table car_sale in the database. However, a unique listing_no must also be generated during this option. I cannot define my tables to uniquely generate the 10 digit number the option but I must code the program to insert uniquely generated listing_no. Below you will find the code of me trying to do this, however the code only works in Oracle but I cannot use Oracle. I can only PostGreSQL and Java. Therefore, my problem arises as the functions and relations I am using cannot be used in PostGre.

Code to Generate Listing No:

 public int generateListingNo() throws SQLException
 {
  int listingSeq = 0;
  Statement select = connection.createStatement();
  result = select.executeQuery("select (to_char(sysdate,'yyyymmdd')||AUDIT_SEQ.NEXTVAL)valnext from dual");;
  if(result.next())
  {
     listingSeq = result.getInt(1);
  }

  int seq = listingSeq;

  return seq;
 }

Code in The Option Function to insert the lisitng_no generated from generateListingNo()

public void option() throws SQLException
   {
int listing_no = generateListingNo();
         // insert information into books_for_sale table
         sql_insert = "INSERT INTO car_sale VALUES(" + listing_no +", "
                       + "'" + year + "'" + ", " +
                       "'" + make + "'" +", " +
                       "'" + condition + "'" + ", "
                       + price + ")";

Erros I am Getting:

    Exception in thread "main" org.postgresql.util.PSQLException: ERROR: relation "dual" does not exist
  Position: 69 at 
      org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:217)
           at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:421)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:318)
           at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:281)

Creating the car_sale table

create table car_sale(
listing_no int not null,
year varchar not null,
make varchar not null,
condition varchar not null,
price decimal(12,2) not null,
primary key (listing_no),

@RehanAzher I have edited the error in my question. Any help is appreciated! — AbbyS
– AbbyS, Commented Mar 31, 2018 at 14:52
Can u also post the create statement of your table? Doesn't looks like an issue with your code. — Rehan Azher
– Rehan Azher, Commented Mar 31, 2018 at 14:53

Rehan Azher · Accepted Answer · 2018-03-31 16:25:31Z

1

Change you query for generateListingNo as below:

select   q from (select (to_char(now(),'yyyymmdd') || NEXTVAL('AUDIT_SEQ') )q )sq

or

select (to_char(now(),'yyyymmdd') || NEXTVAL('AUDIT_SEQ')) as newseqval

or on your cocde:

public int generateListingNo() throws SQLException
 {
  int listingSeq = 0;
  Statement select = connection.createStatement();
  result = select.executeQuery("select (to_char(now(),'yyyymmdd') || NEXTVAL('AUDIT_SEQ')) as newseqval");;
  if(result.next())
  {
     listingSeq = result.getInt(1);
  }

  int seq = listingSeq;

  return seq;
 }

Since you dont have sequence :

Either create sequence using below query:

CREATE SEQUENCE public."AUDIT_SEQ"
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

or use UUID:

public String generateListingNo() throws SQLException
     {
       return UUID.randomUUID().toString();     
     }

your table structure will need to change :

create table car_sale(
listing_no varchar not null,
year varchar not null,
make varchar not null,
condition varchar not null,
price decimal(12,2) not null,
primary key (listing_no),

edited Mar 31, 2018 at 16:25

answered Mar 31, 2018 at 15:24

Rehan Azher

1,3401 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Rehan Azher Over a year ago

Please Make Sure you have set the starting value of Seq to be more than 2 digits. Also, I recommend not use INT but BigInt in case your record will grow more than 10 digits.

AbbyS Over a year ago

Hi, it doesn't work. I am getting the error audit_seq does not exist

Rehan Azher Over a year ago

Can u try to print the value of listseq in if block.

AbbyS Over a year ago

It won't allow me because of the error of 'audit_seq relation does not exist'. Can I use this in PostGreSQL?

Rehan Azher Over a year ago

Yes i have tested this in PG, please check if u have created the sequence or not.

|

Sanjay · Accepted Answer · 2018-03-31 15:28:46Z

0

For PostgreSQL, you have to call query this way from java :

SELECT nextval('ACCOUNT_TRANSACTION_NO')

edited Mar 31, 2018 at 15:28

answered Mar 31, 2018 at 15:00

Sanjay

2,5131 gold badge16 silver badges29 bronze badges

2 Comments

Rehan Azher Over a year ago

OP is trying to concatenate a date to make it 10 digits. problem is DUAL

Sanjay Over a year ago

dual is not used in postgresql

Collectives™ on Stack Overflow

Creating a unique listing_no (Java/PostgreSQL)

2 Answers 2

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related