如何在使用if语句后插入到JDBC中创建的表中

Question

嗨，伙计，我需要知道如何(插入(创建的表，因为我遇到了很多错误：

Connection con = DriverManager.getConnection(DB_URL,username,password);
//query for the offer Validity
String query = "SELECT chargingtime,CHARGINGPARTYNUMBER,SUBSCRIBERID,OFFERNAME,OFFERID,prepaidbalance,LIFECYCLE_DAYS,LVL,OPERATIONID FROM ncs_sub_unsub";
try (Statement stmt = con.createStatement()) {
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String SUBSCRIBERI = rs.getString("SUBSCRIBERID");
String OFFERNAME = rs.getString("OFFERNAME");
String OFFERID = rs.getString("OFFERID");
String LIFECYCLE_DAYS = rs.getString("LIFECYCLE_DAYS");
int LEVEL1 = rs.getInt("LVL");
String CHARGINGPARTY =rs.getString("CHARGINGPARTYNUMBER");
int OPERATIONID= rs.getInt("OPERATIONID");
float prepaid_balance = rs.getFloat("PREPAIDBALANCE");
LocalDate CHARGINGTIME1 = rs.getObject("CHARGINGTIME", LocalDate.class);
LocalDate CHARGINGTIME7 = CHARGINGTIME1.plusDays(7);
LocalDate CHARGINGTIME14 = CHARGINGTIME1.plusDays(14);
//if the level is one , the time should be 7 days
if(LEVEL1 == 1){
if((OPERATIONID == 4050001 && CHARGINGTIME1.isBefore(CHARGINGTIME7)) || CHARGINGTIME1.isEqual(CHARGINGTIME7)){
String q = String.valueOf(stmt.executeUpdate("INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'S')"));
} else if ((OPERATIONID == 4050018 && CHARGINGTIME7.isAfter(CHARGINGTIME1)) || CHARGINGTIME7.isEqual(CHARGINGTIME1)) {
String q = String.valueOf(stmt.executeUpdate("INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U')"));
}else {
String q = String.valueOf(stmt.executeUpdate("INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'E')"));
}
}
}
}// end of try
catch (SQLException e){
e.printStackTrace();
}

}
public static void main(String[] args) throws SQLException {
ViewTable();

}
}

错误：

Caused by: Error : 984, Position : 111, Sql = INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U'), OriginalSql = INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U'), Error Msg = ORA-00984: column not allowed here

Answer 1

看起来Insert sql查询是错误的。

我认为您正在尝试将不带"的字符串值和字符串值插入浮点列中。

INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U')

这不是一个有效的sql查询，因为preaid_balance不是数字，SUBSCRIBERI也不是字符串(请尝试"SUBSCRIBERI"等(

Answer 2

stmt.executeUpdate("INSERT INTO aggregation VALUES (SUBSCRIBERI,CHARGINGPARTY,OPERATIONID,OFFERNAME,prepaid_balance,LIFECYCLE_DAYS,LEVEL1,'U')"));

这不起作用。您不能在字符串中使用java变量名，并期望系统来解决它。该字符串是基于SQL JDBC驱动程序的逐字，该驱动程序无法执行这类操作。

你想要的是[A]而不是executeUpdate，它不能做到这一点。您需要prepareStatement。该声明类似于：

"INSERT INTO aggregation VALUES (?,?,?,?,?,?,?,'U')"

然后使用PreparedStatement必须使用的各种.setX方法来设置值：.setString(1, SUBSCRIBERI);，依此类推。完成所有这些之后，就可以对准备好的语句对象运行.executeUpdate()。

网上有成千上万的教程向您展示如何在这里使用PreparedStatement。