Java SQL异常无效的游标状态 - 没有当前行 [英] Java SQL Exception Invalid Cursor State - no current row
本文介绍了Java SQL异常无效的游标状态 - 没有当前行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我一直在努力解决这个问题。首先，我有一个 insertProduct（Product p）方法，该方法应检查数据库中是否存在具有指定代码的产品。如果是这样，此方法将显示错误消息。否则，它应该将产品添加到数据库并将其打印到控制台。我不确定我是否正确地做到了这一点。
I've been having a hard time trying to figure this out. First I have an insertProduct(Product p) method that is supposed to check if a product with the specified code exists in the database. If so, this method will display an error message. Otherwise, it should add the product to the database and print it to the console. I'm not sure if I'm doing that correctly.
其次， deleteProduct（产品p）方法应该删除<$添加的产品c $ c> insertProduct 方法。现在问题是，当我尝试添加产品时，我不断收到SQL异常，然后 deleteProduct 方法只是逐个删除数据库中的产品程序运行的时间，直到非剩余。我不确定这两种方法有什么问题。
Second, the deleteProduct(Product p) method is supposed to delete the product that was added by the insertProduct method. Now the thing is that I keep getting a SQL Exception when I try to add the product and then the deleteProduct method just keeps deleting the products that are in the database one by one each time the program is run until non are left. I'm not sure what's wrong with both of these methods.
控制台输出：
Derby has been started.
Product list:
bvbn Murach's Beginning Visual Basic .NET $49.50
cshp Murach's C# $49.50
java Murach's Beginning Java $49.50
jsps Murach's Java Servlets and JSP $49.50
mcb2 Murach's Mainframe COBOL $59.50
sqls Murach's SQL for SQL Server $49.50
zjcl Murach's OS/390 and z/OS JCL $62.50
First product:
bvbn Murach's Beginning Visual Basic .NET $49.50
Last product:
zjcl Murach's OS/390 and z/OS JCL $62.50
Product by code: cshp
cshp Murach's C# $49.50
Insert test:
java.sql.SQLException: Invalid cursor state - no current row.
Product list:
bvbn Murach's Beginning Visual Basic .NET $49.50
cshp Murach's C# $49.50
java Murach's Beginning Java $49.50
jsps Murach's Java Servlets and JSP $49.50
mcb2 Murach's Mainframe COBOL $59.50
sqls Murach's SQL for SQL Server $49.50
zjcl Murach's OS/390 and z/OS JCL $62.50
Delete test:
zjcl Murach's OS/390 and z/OS JCL $62.50
Product list:
bvbn Murach's Beginning Visual Basic .NET $49.50
cshp Murach's C# $49.50
java Murach's Beginning Java $49.50
jsps Murach's Java Servlets and JSP $49.50
mcb2 Murach's Mainframe COBOL $59.50
sqls Murach's SQL for SQL Server $49.50
Derby has been shut down.
代码：
import java.sql.*;
public class DBTesterApp
{
private static Connection connection = null;
private static Product p = null ;
public static void main(String args[])
{
// get the connection and start the Derby engine
connection = MurachDB.getConnection();
if (connection != null)
System.out.println("Derby has been started.\n");
// select data from database
printProducts();
printFirstProduct();
printLastProduct();
printProductByCode("cshp");
// modify data in the database
p = new Product("test", "Test Product", 49.50);
insertProduct(p);
printProducts();
deleteProduct(p);
printProducts();
// disconnect from the database
if (MurachDB.disconnect())
System.out.println("Derby has been shut down.\n");
}
public static void printProducts()
{
// Product p = null ;
try (Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM Products"))
{
System.out.println("Product list:");
while(rs.next())
{
String code = rs.getString("ProductCode");
String description = rs.getString("Description");
double price = rs.getDouble("Price");
p = new Product(code, description, price);
printProduct(p);
}
System.out.println();
}
catch(SQLException e)
{
e.printStackTrace(); // for debugging
}
}
public static void printFirstProduct()
{
try(Statement statement = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = statement.executeQuery("SELECT * FROM Products")){
System.out.println("First product:");
rs.first();
rs.last();
if(rs.isFirst() == false){
rs.previous();
}
if(rs.isLast() == false){
rs.next();
}
rs.absolute(1);
String code = rs.getString(1);
String description = rs.getString(2);
double price = rs.getDouble(3);
p = new Product(code , description , price);
printProduct(p);
System.out.println();
}
catch(SQLException e){
e.printStackTrace();
}
}
public static void printLastProduct()
{
try(Statement statement = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = statement.executeQuery("SELECT * FROM Products")){
System.out.println("Last product:");
rs.first();
rs.last();
if(rs.isFirst() == false){
rs.previous();
}
if(rs.isLast() == false){
rs.next();
}
rs.absolute(7);
String code = rs.getString(1);
String description = rs.getString(2);
double price = rs.getDouble(3);
p = new Product(code, description, price);
printProduct(p);
System.out.println();
}
catch(SQLException e){
e.printStackTrace();
}
}
public static void printProductByCode(String productCode)
{
String sql =
"SELECT ProductCode, Description, Price " +
"FROM Products " +
"WHERE ProductCode = ?";
try(PreparedStatement ps = connection.prepareStatement(sql);){
ps.setString(1, productCode);
ResultSet rs = ps.executeQuery();
if(rs.next()){
String description = rs.getString("Description");
double price = rs.getDouble("Price");
p = new Product(productCode, description, price);
System.out.println("Product by code: " + productCode);
printProduct(p);
}
else{
rs.close();
}
}
catch(SQLException e){
System.err.println(e);
}
System.out.println();
}
public static void insertProduct(Product p)
{
System.out.println("Insert test: ");
//check if product code exists in database
try(Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM Products")){
String code = rs.getString(1);
if (p.getCode().equals(code) ){
System.out.println("Error: This product is already in the database!");
}
else{
String sql =
"INSERT INTO Products (productCode, Description, Price) " +
"VALUES (?, ?, ?)";
try(PreparedStatement ps = connection.prepareStatement(sql)){
ps.setString(1, p.getCode());
ps.setString(2, p.getDescription());
ps.setDouble(3, p.getPrice());
ps.executeUpdate();
}
catch(SQLException e){
System.err.println(e);
}
} //end else
printProduct(p);
System.out.println();
}//end try
catch(SQLException e ){
System.out.println(e);
}
}
private static void deleteProduct(Product p)
{
System.out.println("Delete test: ");
String sql = "DELETE FROM Products " +
"WHERE ProductCode = ?";
try(PreparedStatement ps = connection.prepareStatement(sql)){
ps.setString(1, p.getCode());
ps.executeUpdate();
}
catch(SQLException e){
System.err.println(e);
}
// add code that deletes the specified product from the database
// if a product with the specified code doesn't exist, display an error message
printProduct(p);
System.out.println();
}
// use this method to print a Product object on a single line
private static void printProduct(Product p)
{
String productString =
StringUtils.padWithSpaces(p.getCode(), 8) +
StringUtils.padWithSpaces(p.getDescription(), 44) +
p.getFormattedPrice();
System.out.println(productString);
}
}
推荐答案
你需要先调用 ResultSet.next（）才能检索列值。
You need to call ResultSet.next() before you can retrieve a column value.
// check if product code exists in database
try(Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM Products")){
if (rs.next()) // THIS is MISSING!
String code = rs.getString(1);
以上代码无异常运行，但由于您选择了所有产品，因此仍会在逻辑上失败只检查数据库返回的第一个代码。检查产品是否已存在的 正确 方式是
The above code would run without exceptions but would still fail logically since you're selecting all the products and just checking the code for the first one returned by the database. The correct way to check if the product already exists is
// check if product code exists in database
try(Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(
"SELECT * FROM Products WHERE ProductCode = '" + p.getCode() + "'")){
if (rs.next()) {
System.out.println("Error: This product is already in the database!");
return;
}
这篇关于Java SQL异常无效的游标状态 - 没有当前行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
