Eliminating column names in insert statement using JDBC

Question

I am trying to create an insert statement using JDBC. For the table I am trying to insert values that include 82 columns. I am wondering if there is a way where I could just call the insert function and insert values directly into the table rather than specifying each and every column name? eg: assume a statement is : insert into table( id, age,...) values( 1, 21,..) . I dont want to specify column names in the statement ( id, age and all). Is there a way to do it without dealing with the column names?

Without posting relevant code, no one can help you. Downvoting. You can refer how to ask a question by checking http://stackoverflow.com/help/how-to-ask and how to create an example by reading this : http://stackoverflow.com/help/mcve — We are Borg, Sep 23 '15 at 15:10
A table with 82 columns inidcates a design smell (unless you convince me of the opposite) — Gyro Gearless, Sep 23 '15 at 15:24

score 1 · Accepted Answer · answered Sep 23 '15 at 15:15

You can do:

INSERT INTO table VALUES (1, 21, 345, ...)

... but then you have to make sure you provide every column value in your table, even the nullable ones. And you also have to make sure that you provide them in the exact order they are defined in the database.

Do yourself a favor, and don't do that. You may find it annoying now to have to list all the columns explicitly. But by doing so:

You can choose to not provide values for certain columns
The order of the columns doesn't need to match the table's column definition order
(And most importantly) If someone adds a new nullable column to your table, your code won't break.

score 0 · Answer 2 · answered Sep 23 '15 at 15:26

another option would be to write a stored procedure on your MySql instance, and call that. In this case you would only have to pass in the values you want to write, and the stored proc will do the actual insert.

here's a simple example

public void callProc () throws Exception{
      CallableStatement cs;
      Connection con = getMySqlConnection();
           cs = con.prepareCall("CALL getHash(?,?);");
    cs.setString(1, "some string");
    cs.setString(2, "some other string");
      cs.execute();
}

Eliminating column names in insert statement using JDBC

2 Answers2