英文:
SQL error or missing database (near ":01": syntax error) in SQLite
问题
我正在尝试使用Java更新一个SQLite表。这是代码的相关部分：
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("成功打开数据库");
stmt = c.createStatement();
String sql = "INSERT INTO AMBROZIJA (LEVEL, DATE, TIME) " +
"VALUES (" + koncentracijaAmbrozije + ", '" + date + "', '" + time + "');";
System.out.println(sql);
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
System.out.println("记录创建成功");
当我运行代码时，我得到以下输出：
INSERT INTO AMBROZIJA (LEVEL, DATE, TIME) VALUES (6.5, '2020-08-21', '18:01:44.087078100');
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL错误或缺少数据库（在“:01”附近：语法错误）
进程以退出代码0结束
“(在“:01”附近：语法错误）”中的“附近 x”部分来自时间的分钟部分，因此它会随着每次执行而改变。
如何修复这个错误（我对SQLite了解不多）？
英文:
I'm trying to update a SQLite table with Java. This is the relevant part of the code:
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "INSERT INTO AMBROZIJA (LEVEL,DATE,TIME) " +
"VALUES ("+koncentracijaAmbrozije+","+date+","+time+");";
System.out.println(sql);
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
When I run the code, I get the following output:
INSERT INTO AMBROZIJA (LEVEL,DATE,TIME) VALUES (6.5,2020-08-21,18:01:44.087078100);
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near ":01": syntax error)
Process finished with exit code 0
The "near x" in (near ":01": syntax error) part is from the minutes part of time so it changes with every execution.
How can I fix this error (I don't know much about SQLite)?
答案1
得分: 2
要修复问题的列是 time，但是 date 应该在引号内传递，并且您可以在连接值时添加引号，
date = "'" + date + "'";
time = "'" + time + "'";
String sql = "INSERT INTO AMBROZIJA (LEVEL,DATE,TIME)" +
" VALUES (" + koncentracijaAmbrozije + "," + date + "," + time + ");";
但是最好的做法并且始终建议的是使用 prepareStatement 绑定值，而不是连接值，尤其在处理日期列时。在找到了一个很好的 SO 链接，您可以查看一下。
英文:
To fix your problem columns time but the date should be passed within quotes and you can add the quotes while concatenating value,
date = "'"+ date +"'";
time = "'"+ time +"'";
String sql = "INSERT INTO AMBROZIJA (LEVEL,DATE,TIME)" +
" VALUES (" + koncentracijaAmbrozije + "," + date + "," + time+ ");";
But it is good practice and always recommended to bind the values instead concatenating it using prepareStatement as also suggested in the comment but its little bit more work when you deal with date columns. found a good SO link which you can take a look.
答案2
得分: 1
将其作为带单引号的字符串传递，或者您可以像这样使用**strftime()**方法：
strftime('%Y-%m-%d','2020-08-21')
您可以查看手册以获取所有有效的替换内容
日期和时间函数
英文:
Pass it as string with single quotes, or you can use strftime() method like this:
strftime('%Y-%m-%d','2020-08-21')
You may take a look at the manual for all valid substitutions
Date And Time Functions
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论