java - Jsoup，MySQL : How to prevent doubleEntries in database via JavaCode

Question

我正在尝试提取数据并将其直接写入我创建的数据库中phpmyadmin(wamp服务器)。数据每天都在增长，我必须保留它今天，所以我将在几天内运行我的程序代码。现在我的问题:如何防止相同的条目？我通过姓名、ID 进行了尝试网址。问题是，某些数据具有相同的 ID。通过使用它抛出的名称SQL语句的语法异常原因(很多名称带有字母像:“;”，“””，“\”，“`”，“:”，这使得它相当困难)当我使用URL不会抛出错误，但是我有很多重复输入。并非所有存在的数据都已进入数据库，但我只能阻止其中的一些数据。我该如何解决这个问题？

这是我的程序代码:

package htmlParser;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import data.DatenBank_Steam_Spiele;
import data.Spiel;

public class Steam_GameID_Links
{
    public ArrayList<Spiel> spiele;
    public final static String DELIMITER = "Trennzeichen";
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String myDatabase = "jdbc:mysql://localhost:3306    /Steam_Spiele";

public static void main(String[] args) throws SQLException, ClassNotFoundException
{
    Connection conn = null;
    Statement stmt = null;

    Steam_GameID_Links wc = new Steam_GameID_Links();
    try
    {
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(myDatabase, "root", "");
        System.out.println("Verbindung erfogreich hergestellt");

        String url = "http://store.steampowered.com/search/?sort_by=_ASC&category1=998&page=1";
        Document document = Jsoup.connect(url).get();
        Elements howMuchPages = document.select(".search_pagination_right");
        String[] stuff = howMuchPages.text().split(" ");
        String tmp = stuff[4].replace(" ", "").replace(".", "");
        StringBuilder sb = new StringBuilder();
        for(int i = 0; i < tmp.length(); i++)
        {
            if(Character.isDigit(tmp.charAt(i)))
            {
                sb.append(tmp.charAt(i));
            }
        }
        String last = sb.toString().trim();;
        int lastPages = Integer.parseInt(last);
        int counter = 0;
        wc.spiele = new ArrayList<>();
        for(int i = 1; i < lastPages + 1; i++)

        {
            System.out.println("______________________ SpieleAngebot Seite " + i + " von insgesamt " + lastPages
                    + " Seiten.");
            url = "http://store.steampowered.com/search/?sort_by=_ASC&category1=998&page=" + i;
            // ACHTUNG: ohne timeout(0) kann der AusleseProzess VORHER abbrechen (dauert zu lange)
            document = Jsoup.connect(url).timeout(0).get();
            // waehlt zunaechst den ElternKnoten: <div id="search_result_container">
            Element parentNode = document.getElementById("search_result_container");
            Elements childNodes = parentNode.getElementsByAttribute("data-ds-appid");
            // FUNZT (releasedates)
            Elements releasedates = document.select("div.col.search_released.responsive_secondrow");
            Elements prices = document.select("div.col.search_price.responsive_secondrow");
            for(int j = 0; j < releasedates.size(); j++)
            {
                String name = childNodes.get(j).getElementsByClass("title").text();
                String id = childNodes.get(j).attr("data-ds-appid");
                int ID = Integer.parseInt(id);
                String href = childNodes.get(j).attr("href");
                String releaseDate = releasedates.get(j).text();
                String priceAll = prices.get(j).text();
                String[] getPrice = priceAll.split(" ");
                String price = getPrice[getPrice.length - 1];
                // Liste befuellen WICHTIG fuer Textfile!!
                wc.spiele.add(new Spiel(name, ID, href, price, releaseDate));

                //check if the game is already in database
                String search = "SELECT * FROM spiel WHERE URL LIKE \"" + href + "\"";
                Statement checkIfAlreadyThere = conn.createStatement();
                ResultSet rs = checkIfAlreadyThere.executeQuery(search);
                if(rs.next())
                {
                    System.out.println("Game already in database");
                }
                else
                {
                    String insert = "INSERT INTO spiel (NAME, ID, URL, VERÖFFENTLICHT, PREIS)"
                            + "VALUES (?,?,?,?,?)";
                    PreparedStatement preparedStatement = conn.prepareStatement(insert);
                    preparedStatement.setString(1, name);
                    preparedStatement.setInt(2, ID);
                    preparedStatement.setString(3, href);
                    preparedStatement.setString(4, releaseDate);
                    if(price.equals("Play") || price.equals("Free"))
                    {
                        price = "kostenlos";
                    }
                    preparedStatement.setString(5, price);
                    preparedStatement.executeUpdate();
                }
                counter++;
            }
        }
        System.out.println("Anzahl Spiele: " + counter);

        for(Spiel out : wc.spiele)
        {
            wc.writeSpielNameIDLink("Spiel:" + out.getName() + DELIMITER + "ID:" + out.getID() + DELIMITER
                    + "SpieleLink:" + out.getLink() + DELIMITER + "veröffentlicht:" + out.getReleaseDate()
                    + DELIMITER + "Preis:" + out.getPrice() + "\n \n");
        }
        wc.writeSpielNameIDLink("\n \n Anzahl Spiele: " + counter);
        Steam_Sales_Per_Game spG = new Steam_Sales_Per_Game();

    }
    catch(IOException e)
    {
        e.printStackTrace();
    }
}

Answer 1

您必须找到游戏的唯一标识符。您应该看看 Steam 是否为游戏提供了非常独特的 ID，并使用该 ID 作为游戏的标识符。当我在代码中检查您的 url 的 steam 链接时，我可以看到每个列出的条目都有一个应用程序 ID。应用程序 ID 作为标签属性存在于顶部链接 <a> 中游戏条目(/app/部分后的 href)

编辑:id 在 href 属性中是唯一的