java - 如何从 xml 文件更新数据库

Question

我正在尝试创建一个从 xml 文件更新数据库的函数。我在数据库中的列 - 表:'offers':id(A_I,PK)、城市、国家、酒店、描述、价格、图像

我认为只有在城市、国家和酒店相同的情况下我才会更新报价。否则我将插入一个新行。我首先实现了 xml 文件的读取，效果很好。数据库连接正常，我测试过。

我有两个问题:首先，我是否可以将其缩小。第二个为什么不起作用并说:

Parameter index out of range (1 > number of parameters, which is 0).

我认为这是sql语句的问题，但我不知道如何解决它。 到目前为止我的代码:

package bookings1;
import java.io.File;
import java.sql.*;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory; 

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class addOffers implements Runnable {

    public void run(){
        Connection connection = null;
        connection = ConnectionConfiguration.getConnection();
        try {
             // Start getting offers from xml
            File fXmlFile = new File("c:\\offers.xml");
            DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
            Document doc = dBuilder.parse(fXmlFile);        
            doc.getDocumentElement().normalize();        
            System.out.println("Root element :" + doc.getDocumentElement().getNodeName());       
            NodeList nList = doc.getElementsByTagName("offers");         
            System.out.println("----------------------------");      
            for (int temp = 0; temp < nList.getLength(); temp++) {       
                Node nNode = nList.item(temp);       
                System.out.println("\nCurrent Element :" + nNode.getNodeName());         
                if (nNode.getNodeType() == Node.ELEMENT_NODE) {      
                    Element eElement = (Element) nNode;      
                    String DCity = eElement.getElementsByTagName("city").item(0).getTextContent();
                    String DCountry = eElement.getElementsByTagName("country").item(0).getTextContent();
                    String DHotel = eElement.getElementsByTagName("hotel").item(0).getTextContent();
                    String DDescription= eElement.getElementsByTagName("description").item(0).getTextContent();
                    String DPrice = eElement.getElementsByTagName("price").item(0).getTextContent();
                    String DImage = eElement.getElementsByTagName("image").item(0).getTextContent();                    
                    PreparedStatement st = connection.prepareStatement("SELECT * FROM offers WHERE city=? AND country=? AND hotel=?");
                    st.setString(1, DCity);
                    st.setString(2, DCountry); 
                    st.setString(3, DHotel);  
                    ResultSet rs = st.executeQuery();

                    if(rs.next()) { 
                     int theid = rs.getInt("id");   
                     PreparedStatement preparedStatement = 
                                connection.prepareStatement("UPDATE offers SET description=?, price=?, image=? WHERE id=?");
                                preparedStatement.setString(1, DDescription);
                                preparedStatement.setString(2, DPrice); 
                                preparedStatement.setString(3, DImage);  
                                preparedStatement.setInt(4, theid);  
                                preparedStatement.executeUpdate();
                    }
                    else {
                        PreparedStatement preparedStatement = 
                        connection.prepareStatement("INSERT into orders (city, country, hotel, description, image, phone) VALUES (?,?,?,?,?,?)");                       
                            preparedStatement.setString(1, DCity);
                            preparedStatement.setString(2, DCountry); 
                            preparedStatement.setString(3, DHotel);  
                            preparedStatement.setString(4, DDescription);  
                            preparedStatement.setString(5, DPrice);
                            preparedStatement.setString(6, DImage);
                            preparedStatement.executeUpdate();
                    }

                    connection.close();

                }
            }
            } catch (Exception e) {
              System.out.println("Fetching from xml thread interrupted! " + e.getMessage());
          }


    }   
}

Answer 1

首先，JDBC 带来了大量样板代码。它永远不会短!尝试查看 JPA 和 Hibernate 的数据库功能？

其次，您的错误消息告诉您，您正在尝试在未定义参数的情况下插入参数。

PreparedStatement st = connection.prepareStatement("SELECT * FROM offers WHERE city='Beijing'");
st.setString(1, DCity);
st.setString(2, DCountry); 
st.setString(3, DHotel);     

基本上，如果您想将三个参数插入到准备好的语句中，它应该如下所示:

PreparedStatement st = connection.prepareStatement("SELECT * FROM offers WHERE city=? AND country=? AND hotel=?");
st.setString(1, DCity);
st.setString(2, DCountry); 
st.setString(3, DHotel);      

希望这能让您更清楚地了解事情!