I am trying to insert the data onto different databases. Used dbutils to incorporate QueryRunner.batch() for batch inserts. This worked for SQL Server which casts the data to the corresponding type. I tried the same with PostgreSQL but of no avail. I tried a sample insert but that has failed in PostGres which validates my claim whereas the inserts for SQL Server were successful:
private void postGrestest() throws ClassNotFoundException, SQLException
{
Class.forName("org.postgresql.Driver");
String dropStmt = "DROP TABLE PUBLIC.TEST";
String createStmt = "CREATE TABLE PUBLIC.TEST(COL1 VARCHAR(10), COL2 BOOLEAN)";
String insertStmt = "INSERT INTO PUBLIC.TEST(COL1, COL2) VALUES (?, ?)";
try (Connection connection = DriverManager.getConnection(
"jdbc:postgresql://<host>:5432/<dbname>", "<username>", "<password>");
Statement stmt = connection.createStatement();
PreparedStatement ps = connection.prepareStatement(insertStmt);)
{
//stmt.execute(dropStmt);
stmt.execute(createStmt);
Random r = new Random();
for (int i = 0; i < 100; i++)
{
Object str = "Test" + i;
ps.setObject(1, str);
Object obj = String.valueOf(r.nextBoolean());
ps.setObject(2, obj);
ps.executeUpdate();
}
}
}
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "col2" is of type boolean but expression is of type character varying Hint: You will need to rewrite or cast the expression.
Position: 49
private void sqlserverTest() throws SQLException, ClassNotFoundException
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String dropStmt = "DROP TABLE DBO.TEST";
String createStmt = "CREATE TABLE DBO.TEST(COL1 VARCHAR(10), COL2 BIT)";
String insertStmt = "INSERT INTO DBO.TEST(COL1, COL2) VALUES (?, ?)";
try (Connection connection = DriverManager.getConnection(
"jdbc:sqlserver://<host>:<port>", "<username>", "<password>");
Statement stmt = connection.createStatement();
PreparedStatement ps = connection.prepareStatement(insertStmt);)
{
//stmt.execute(dropStmt);
stmt.execute(createStmt);
Random r = new Random();
for (int i = 0; i < 100; i++)
{
Object str = "Test" + i;
ps.setObject(1, str);
Object obj = String.valueOf(r.nextBoolean());
ps.setObject(2, obj);
ps.executeUpdate();
}
}
}
The reason I have to cast because, I read the data from a file and get as an object array. I do not have idea of the type beforehand and would like to use setObject instead of specific types setBoolean, setInteger, etc.
What other ways that I can use without casting at my application level and let the driver handle the cast?