PreparedStatement to insert unknown amount of values with unknown types

Question

I have a method insert() which inserts a list of values into a table which is chosen by the user.

The problem is that since the user gets to choose the table, the method does not know how many values that are to be inserted and of which type they are. I've solved the variable amount of values with a loop that uses a stringbuilder to insert the correct amount of "?"-chars into the values part of the query.

I also have a loop that splits the values received into a String array, but I then have a problem with ints being processed like strings. Can I get around this using some trick with sql-syntax, or do I need to fetch information about which kind of data type each value is?

And if I have to fetch info about the data types, how do I do that? (Preferably an sql query that returns nothing but the types since I want to use the result directly in my java code).

Answer 1

Firstly, what I suspect you are doing is wrong, or certainly sub-optimal.

Assuming you were adamant this is how you want to go about it, you need to retrieve a row from your table and then call getMetadata() on the ResultSet.

You would end up with something like:

rs.getMetaData().getColumnTypeName(int column)

Once you know the column type, you can parse/sanitize your user entered data accordingly.