why I can not insert into oracle in jdbc?-CodePudding

My goal is to transfer a series of songs from the songs table in SQLite DB to a songs table in oracle DB. First I select the information from SQLite and then transfer it to the oracle's songs, but it gives the following error.

    try {
        ResultSet resultSet = sqliteConnectionStatement.executeQuery("select * from songs");
        while (resultSet.next()) {
            oracleConnectionStatement.execute("insert into songs values ("   resultSet.getInt("_id")  
                    ","   resultSet.getInt("track")  
                    ",'"   resultSet.getString("title")  
                    "',"   resultSet.getInt("album")   ")");
        }
        System.out.println("data transferred with no error");
        resultSet.close();
    } catch (SQLException e) {
        System.out.println("Oops!something went wrong! : "   e.getMessage());
        e.printStackTrace();
    }

output:

Oops! something went wrong! : ORA-00917: missing comma

java.sql.SQLSyntaxErrorException: ORA-00917: missing comma

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1231)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:772)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:512)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:123)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1200)
    at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1820)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1472)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2505)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2460)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:328)
    at org.ISOFT.Main.main(Main.java:21)
Caused by: Error : 917, Position : 37, Sql = insert into songs values (1,2,'I Can't Quit You Baby',343), OriginalSql = insert into songs values (1,2,'I Can't Quit You Baby',343), Error Msg = ORA-00917: missing comma

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)
    ... 13 more

CodePudding user response：

NEVER build an SQL statement using string concatenation. Your code will be vulnerable to SQL injection attacks and if you run the same query with different parameters then the SQL engine will need to parse it every time making everything slower.

Use a prepared statement and bind variables. The code will not be vulnerable to SQL injections attacks and the SQL engine will only need to parse it once and then can reuse the previous execution plan.

Something like (untested):

ResultSet resultSet;
try {
  resultSet = sqliteConnectionStatement.executeQuery(
    "select * from songs"
  );
  oracleConnectionStatement.setAutoCommit(false);
  PreparedStatement stmt = oracleConnectionStatement.prepareStatement(
    "INSERT INTO songs (id, track, title, album) VALUES (?, ?, ?, ?)"
  );
  while (resultSet.next()) {
    stmt.setInt(1, resultSet.getInt("_id"));
    stmt.setInt(2, resultSet.getInt("track"));
    stmt.setString(3, resultSet.getString("title"));
    stmt.setInt(4, resultSet.getInt("album"));
    stmt.addBatch();
  }
  stmt.executeBatch();
  oracleConnectionStatement.commit();
  System.out.println("data transferred with no error");
} catch (SQLException e) {
   System.out.println("Oops!something went wrong! : "   e.getMessage());
   e.printStackTrace();
} finally {
  // Close the result set
  if (resultSet != null)
  {
    try {
      resultSet.close();
    } catch (Exception e){}
  }
  // Close the connections
  try {
    sqliteConnectionStatement.close();
  } catch (Exception e){}
  try {
    oracleConnectionStatement.close();
  } catch (Exception e){}
}