How to insert date or NULL to array

Question

I have following question. I'm using JDBC in my project and I made simple method to insert data into my database.

My problem is: What to do when I want to insert something like sysdate or just NULL to auto increment? To my method I send only strings and writing NULL to string doesn't work.

Can you give me any advice how to improve it?

This is the code with constant null in query, but it isn't what I want to.

public static void insertInto(String Table, ArrayList<String> values) throws SQLException
{
    Connection conn = JavaConnectDB.ConnectDb();
    OraclePreparedStatement pst = null;    
    StringBuilder Query = new StringBuilder("INSERT INTO " + Table + " VALUES (NULL, ");

    for (int i = 0; i < values.size(); i++)
    {
        Query.append("? ");
        if (i + 1 != values.size())
            Query.append(", ");
    }
    Query.append(")");

    pst = (OraclePreparedStatement) conn.prepareStatement(Query.toString());

    for (int i = 0; i < values.size(); i++)
    {
        pst.setString(i + 1, values.get(i));
    }

    pst.executeUpdate();
}

This method creates query like "INSERT INTO TABLE VALUES (NULL, ?, ? ,?)" and then fills gaps with values from array.

I don't understand what you want, do you want to insert the string 'NULL' or a NULL value? This code looks ok, provided your database schema allows null values for the field you are inserting to, and if as I believe that field is a primary key such as an id, I suspect it won't. — zoubida13, May 25 '16 at 08:49
In this code i cant insert something like "sysdate" or "null" which i have to. Sometimes i dont need this NULL at start of query but i cant delete it becouse i cant insert that. i found solution to write naive if(values.get(0).equals("NULL")) { Query.append("NULL, "); } but u see how it looks like... — Dizip, May 25 '16 at 08:51

score 1 · Accepted Answer · answered May 25 '16 at 08:49

There is java.sql.PreparedStatement.setNull(int, int)

Try, e.g.

pst.setNull(1, Types.BIGINT);
for (int i = 1; i < values.size(); i++)
{
    pst.setString(i + 1, values.get(i));
}

Change Types.BIGINT for apporpriate type for your column. Note, that values.get(0) is just ignored, but should present in the array.

score 1 · Answer 2 · edited May 23 '17 at 11:58

Problem is the setString function. If you have a string "NULL" or "SYSDATE", it will result in the query being quoted ('NULL', 'SYSDATE'), so this will be inserted as string.

According to this answer, pst.setString(n, null) should do the trick already, so inserting SQL NULL values is yet relatively easy, just insert a Java null value into the array where you want the database value to be SQL NULL.

SYSDATE gets more delicate. But I think, here comes something more fundamental into play: how are you going to handle data types other than VARCHAR (see setString documentation). If you really wanted such a generic method, I would rather to pass ArrayList<Object> as parameter (or with ellipsis ...) and call the appropriate setXXX method for the specific Object type - or setObject with appropriate Type parameter set. You could then create your own class SysDate which could easily be detected.

Is the database layout known to your application? Then I'd rather recommend to have a separate insert method for each table accepting exactly the number of required parameters of correct type, such as

bool insertIntoTest(int someValue, Integer anotherValue, String andAnotherOne)
{
    Connection conn = JavaConnectDB.ConnectDb();
    OraclePreparedStatement pst
        = (OraclePreparedStatement) conn.prepareStatement(
              "INSERT INTO TEST (someValue, anotherValue) VALUES(?, ?)"
          );
    pst.setInt(1, someValue);
    if(anotherValue == 0)
        pst.setNull(2, Types.INTEGER);
    else
        pst.setInt(2, anotherValue);
    // can handle null already...
    pst.setString(3, andAnotherOne);
    /* ... (execute, try/catch, return) */
}

score 0 · Answer 3 · answered May 25 '16 at 08:49

Well you should define exactly what do ou want to do.

It's not possible to put null in an autoinkrement field on database by definition

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

So if you want just to insert some filed to your table and delegate the genration of autoincrement to your database, you should create your query like that :

INSERT INTO TABLE VALUES (?, ? ,?).

Example :

Table employee(id,time,name)

Query :

INSERT INTO employee (time, name) VALUES (?, ?)

Would require to redesign the method, though, as with current signature, there are no column names available... — Aconcagua, May 25 '16 at 09:04

How to insert date or NULL to array

3 Answers3