Handle Dates in generic preparedStatement

Question

I am facing troubles when building a generic preparedStatement : I have 8 SQL Tables, which are all manipulated the same way, so I'd like to build a unique manager which could insert into / select from any of the 8 tables.

To do so, each table has a descriptor, which can provide the fields of a table, its name and the array of values when inserting.

In the manager, the prepared statement to insert is of the following form :

"INSERT INTO " + table_name + " VALUES (?)"

Then, I fill the gap with something like

myPreparedStatement.setString(1, values.getAllValues());

the getAllValues() method must return a string which holds every fields, like " 'This', 'Is', 3, 'example' ". I have no problem with strings and numbers, but I can't add any date in those values...

Using September 3rd, 2008 as example, I used the following formats : 2008-09-03, 08-09-03, 080903, 03092018, but all fail. "yyMMdd" format seemed like the best option from what I saw here and there, but I have the error :

"java.sql.SQLDataException: ORA-01843: not a valid month"

And I have no idea why... has anyone faced this issue before ?

I know there are lots of posts here that talks about inserting dates in database, but they all use the

preparedStatement.setDate(pos, Date);

Statement, and I can't do that since the dates aren't in the same position in all of my tables.

EDIT :

As asked in the comment, here is a minimal sample that reproduce what I'm trying to do. If you want to reproduce as well, I let you handle the connection and database setup :

public class Sample {

public void saveAll() throws ServiceException {

    Connection c = null;
    PreparedStatement ps = null;
    String sql = "INSERT INTO " + getTableName() +" VALUES (?)";

    try {
        c = getConnection();
        c.setAutoCommit(false);

        batch = c.prepareStatement(sql);
        batch.setString(getAllFieldValues());

        int res = batch.executeUpdate();
        c.commit();

    } catch (BatchUpdateException b) {
        throw new ServiceException("Erreur lors de l'exécution du batch", b);
    } catch (SQLException s) {
        throw new ServiceException("Impossible de sauvegarder les beans en base.", s);
    } finally {
        getManager().close(batch);
        freeConnection(c);
    }
}

public String getAllFieldValues() {
        return "'Hello', 'World', 42, '171228'"; 
}

public String getTableName() {
    return "myTableName";
}

}

Answer 1

There is no such thing as generic preparedStatement in JDBC. To insert four columns in table T you must use

 INSERT into T (col1,col2,col3,col4) values (?,?,?,?)

You may ommit the first list with the column names, but this is a bad practice as you trust on the actual columns of the table that may change.

Using only

 INSERT into T  values (?,?,?,?)

work fine until somebody modifies the table by adding or dropping a column and will fail afterwards.

All bind variables must be set extra with the setXXX method with appropriate type and index of the column starting with 1.

stmt.setInt(1,100)
stmt.setString(2,'xxx') 

Answer 2

If I can understand your question correctly. For dynamically placing your date value in prepared statement you can override setString() method to have your custom code to check for date value or else.

Or rather in case you can also have local method to check if coming string is of format date.

For this you can simply pass Date String with some prefix attached so that you can check it in custom setString() method.

setString(String string, int position){
if(string.contains("SPECIFIC_PREFIX_CONSTANT")){
//batch.setDate(position, string.substring("REMOVE PREFIX AND ATTACH"));
}else{
//batch.setString(position, string);
}
}

Answer 3

Ok guys, I managed to have my stuff working, big thanks to all of you ! In case somebody else would end on my question, I'll recap the Code I have now, which works :)

So, as said previously, we have one Manager that interacts with the database and which has no knowledge of the table's he interacts with.

Here is the code of the save method of this manager :

public void saveAll(AbstractBeanClass[] values, String refSelected) {
    // connexion setup
    Connection c = null;
    PreparedStatement batch = null;
    // fetch table's fields, to prepare the placeholders
    String fields = values[0].getAllFields();
    String sql = "INSERT INTO " + values[0].getTableName() + " (" + fields + ") VALUES (";
    StringBuffer places = new StringBuffer();
    int [] res = null;
    // Start at 1 to have one field left, to end the parenthesis
    for(int i = 1; i < values[0].getNumberOfFields(); i++) {
        places.append("?, ");
    }
    // last field
    places.append("?)");

    sql = sql.concat(places.toString());  // We now have a full (?, ..., ?) 

    try {
        c = getConnection();
        c.setAutoCommit(false);
        batch = c.prepareStatement(sql);

        // Filling the batch
        int j = 1;
        for(AbstractBeanClass bean : values) {
            int i = 1;
            for(String type : bean.getAllTypes()) {
                switch(type) {
                    case "int" : {
                        batch.setInt(i, (int) bean.getOrderedValue(i)); 
                    }
                    break;
                    case "String" : {
                        batch.setString(i, (String)bean.getOrderedValue(i));
                    }
                    break;
                    case "Date" : {
                        batch.setDate(i, (java.sql.Date) bean.getOrderedValue(i));
                    }
                    break;
                }
                i++;
            }
            batch.addBatch();
            // In case of numerous insertions, some Databases don't allow more than 1000 inserts at a time
            if(j%1000 == 0) {
                res = batch.executeBatch();
                for(int k : res) {
                    if(k == Statement.EXECUTE_FAILED) {
                        getManager().close(batch);
                        freeConnection(c);
                        throw new RuntimeException("Error while inserting values.");
                    }
                }
            }
            j++;
        }
        // last execution
        res = batch.executeBatch();
        for(int i : res) {
            if(i == Statement.EXECUTE_FAILED) {
                getManager().close(batch);
                freeConnection(c);
                throw new RuntimeException("Error while inserting values in database.");
            }
        }

        c.commit();
        logger.debug("Insertion succeeded, we inserted " + j + " lines.");

    } catch (BatchUpdateException b) {
        throw new RuntimeException("Error in batch : ", b);
    } catch (SQLException s) {
        throw new RuntimeException("Error : we couldn't save the values : ", s);
    } finally {
        getManager().close(batch);
        freeConnection(c);
    }
}

So this is the main part of the program, but it needs the table descriptor. To keep it simple, I made an abstract class which declares the methods I need, and all table descriptors extends this class, here is the declaration :

package com.fr.sncf.fret.boctarification.domaine.referentiel;

import java.io.Serializable;
import java.text.SimpleDateFormat;

public abstract class DaoGenericReferentielBean implements Serializable {

private static final long serialVersionUID = 1L;
protected String allFields;
// the date Format used to insert the dates in base
protected final SimpleDateFormat format = new SimpleDateFormat("yy-MM-dd");

public DaoGenericReferentielBean() {
    // empty constructor
}

/**
 * Return all columns' names, ordered according to database's order
 * @return
 */
public String getAllFields() {
    return this.allFields;
}

/**
 * Returns all values ordered by columns' order
 * @return String
 */
public abstract String getAllFieldsValues();

/**
 * @return the table name
 */
public abstract String getTableName();

/**
 * @return the number of field in this table
 */
public abstract int getNumberOfFields();

/**
 * Returns the ordered list of column's type
 */
public abstract String[] getAllTypes();

/**
 * Return the value corresponding to the given index
 * Values are treated here according to the database's columns order
 * @param index the column's number
 * @return an Object, either an int, or a String, or a Date
 */
public abstract Object getOrderedValue(int index);

}

All you need now is to describe your table according to this model, Hope it helps !