CODEWITHSUNDEEP
javamysqlsqlsql-insert
Oliver Hoffman
Oliver Hoffman

Reputation: 550

Making changes to my program after updating my database?

Sorry if my question is not specific or if it has been answered before. I tried looking for it and for a better way to ask but this is the most accurate way.

I have developed a program in Java in which I insert a new row into my database in the following way:

INSERT INTO table_name VALUES (?,?,?)

The thing is that I have this query in many parts of the program, and now I decided to add a fourth column to my table. Do I have to update EVERY SINGLE query with a new question mark in the program? If I dont, it crashes.

What is the best way to proceed in these cases?

Upvotes: 2

Views: 81

Answers (3)

John Woo
John Woo

Reputation: 263933

YES.

you need to add extra ? (parameter placeholder) because you are using implicit INSERT statement. That means that you didn't specify the column names of the table to which the values will be inserted. 

INSERT INTO table_name VALUES (?,?,?)
// the server assumes that you are inserting values for all
// columns in your table
// if you fail to add value on one column. an exception will be thrown

The next time you create an INSERT statement, make sure that you specify the column names on it so when you alter the table by adding extra column, you won't update all your place holders. 

INSERT INTO table_name (Col1, col2, col3) VALUES (?,?,?)
// the server knows that you are inserting values for a specific column

Upvotes: 5

Richard JP Le Guen
Richard JP Le Guen

Reputation: 28753

Do I have to update EVERY SINGLE query with a new question mark in the program?

Probably. What you should do, while you're updating every single one of those queries, is to encapsulate them into an object, probably using a Data Source pattern such as a Table Data Gateway or a Row Data Gateway. That way you Don't Repeat Yourself and the next time you update the table, you only have one place to update the query.

Upvotes: 2

ESG
ESG

Reputation: 9435

Because of the syntax you've used, you might run some issues. I've referring to the lack of column names. Your INSERT queries will start failing as soon as you change your table structure.

If you had used the following syntax: INSERT INTO table_name (C1, C2, C3) VALUES (?,?,?) assuming your new column has a proper default value, then it would've work fine.

Upvotes: 0

Related Questions