每次写入Sqlite数据库时重新定义语句 [英] Redefine Statement each time while writing to Sqlite database
本文介绍了每次写入Sqlite数据库时重新定义语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我编写了一个 java 代码,从名为 edges 的 Sqlite 数据库中的一个表中获取值并将其写入另一个名为 distances 的表中.但是在将值写入表距离时,我每次都必须重新定义语句.如果我不重新定义 Statement 的值,循环将在执行下面给出的代码一次迭代后退出:
I have written a java code to take values from one table in Sqlite Database named edges and write it to another table named distances. But while writing the values to the table distances I have to redefine the Statement each time. If I don't redefine the value of Statement the loop exits after one iteration executing the code given below :
statement.executeUpdate(sql);
但是如果我重新定义语句,循环会按预期继续,即我注释掉的代码行.谢谢.
But if I redefine the Statement the loop continues as expected i.e. the code line which I have commented out.Thank You.
public static Connection dbconn;
public static Statement statement;
public void solve(InputReader in,OutputWriter out) throws ClassNotFoundException
{
try
{
Class.forName("org.sqlite.JDBC");
dbconn =DriverManager.getConnection("jdbc:sqlite:/home/src/db.db");
System.out.println("Connected");
statement = dbconn.createStatement();
statement.setQueryTimeout(30);
ResultSet rs = statement.executeQuery("select * from edges;");
while(rs.next())
{
String sql = "INSERT INTO distances (Road_Name,Start_Latitude,Start_Longitude)" +
"VALUES ('"+rs.getString("Road_Name")+"',"+rs.getString("Start_Latitude")+","+rs.getString("Start_Longitude")";
//Statement statement= dbconn.createStatement();
statement.executeUpdate(sql);
}
}
catch(Exception e)
{
System.out.println("Exception : "+e);
}
}
推荐答案
- 你不能重用
Statement statement
,因为它会关闭ResultSet rs
; - 您不应在 SQL 语句中连接数据.在准备好的语句中使用参数;
- 为了获得更好的性能,应禁用自动提交并在所有更新调用后显式调用.
我的建议:
public static Connection dbconn;
public static Statement qry;
public void solve(InputReader in,OutputWriter out) throws ClassNotFoundException
{
try
{
Class.forName("org.sqlite.JDBC");
dbconn =DriverManager.getConnection("jdbc:sqlite:/home/src/db.db");
System.out.println("Connected");
PreparedStatement upd = dbconn.prepareStatement("INSERT INTO distances (Road_Name,Start_Latitude,Start_Longitude) VALUES (?, ?, ?)");
dbconn.setAutoCommit(false);
qry = dbconn.createStatement();
qry.setQueryTimeout(30);
ResultSet rs = qry.executeQuery("select * from edges;");
while(rs.next())
{
upd.setString(1, rs.getString("Road_Name"));
upd.setString(2, rs.getString("Start_Latitude"));
upd.setString(3, rs.getString("Start_Longitude"));
upd.executeUpdate();
}
dbconn.commit();
}
catch(Exception e)
{
System.out.println("Exception : "+e);
}
}
这篇关于每次写入Sqlite数据库时重新定义语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文