How to add a new column to an existing database table while using javax.persistence.schema-generation.database.action

Question

I am developing a three-tier Java enterprise application in NetBeans, the architecture includes a model for tables, a facade for table interactions, JavaBeans as controllers, and XHTML web pages for display.

I have a "Booking" table generated from an entity class with primary key ID, status, and date. I need to add new columns (FOOD, SEATS, and RATING), but doing so led to the following error when navigating to a webpage that calls objects from the table.

Internal Exception: java.sql.SQLSyntaxErrorException: Column 'FOOD' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'FOOD' is not a column in the target table. Error Code: 30000

Based on the error message I am getting, I'd assume the issue is Java is not creating the new columns on the existing Booking table. How can I solve this?

I found this question which is similar to my issue. However I am using javax persistence and not eclipse. How can I add another column to my table without dropping it entirely?

I have the following line in my persistence.xml, however the new column is still not generated <property name="javax.persistence.schema-generation.database.action" value="create"/>

I have also tried labelling the column names on my variable declaration in my model as shown below though it does not seem to do anything as the columns were not generated in the first place.

public class Booking implements Serializable {

    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Long id;
    
    
    @Temporal(TemporalType.DATE)
    private Date bookingDate;
    
    private String status;
    @Column(name = "FOOD")
    private String food;
    @Column(name = "SEATS")
    private Long seats;
    @Column(name = "RATING")
    private Long rating;
    @Column(name = "REVIEW")
    private String review;
    
    private Long assignedKitchenStaffId;
    private Long customerId;

    @JoinColumn(name = "ASSIGNEDKITCHENSTAFF_ID")
    @ManyToOne
    private User assignedKitchenStaff;
    
    @JoinColumn(name = "CUSTOMER_ID")
    @ManyToOne
    private User customer;

Create will only create tables - but in your case, they are already there. If this is a Dev environment, you probably should look at clearing the data out and recreating tables as your past iterations of the app may not play nice with the new ones (drop and create). Your JPA provider may have its own non-JPA settings to extend existing tables ( https://stackoverflow.com/a/12528659/496099 or https://stackoverflow.com/a/306825/496099 ) — Chris, Jul 31 '23 at 15:37
my bad, I am using Apache Derby. Though I found a fix by changing the value of a property in my persistence.xml. The property is called javax.persistence.schema-generation.database.action and I changed the value from "create" to "drop-and-create". This dropped the tables thus I lost all of my data but the new columns seem to be working. — crispy, Jul 31 '23 at 15:51

crispy · Answer 1 · 2023-07-31T15:57:18.867

0

I figured out a way to add the new columns. By modifying the value of my schema generation property in my persistence.xml from "create" to "drop-and-create" as shown below

<property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>

One issue with it is it drops the entire database so all data within the database will be lost. Though my project is not for production so I am able to afford the cost of losing my data to create additional columns for my tables. I hope this helps

edited Jul 31 '23 at 15:57

answered Jul 31 '23 at 15:57

crispy

1
1

I personally would develop the skill you need for this as for the most part, there *will* be existing data that should not be lost – g00se Jul 31 '23 at 16:04

How to add a new column to an existing database table while using javax.persistence.schema-generation.database.action

1 Answers1