Why I cannot update data in sqlite according data I scrape

Question

0.00/5 (No votes)

See more:

I have the problem when using updating data using existing table.

First I have the table that have id , now I want try to update the table, so the table have the id and link .

So I write the code to scrape the link and id .After this ,I using update query to update the table and using where clause to compare with the id that already have in the table.
So expected result is should be :

 Name                         | Link               
|-----------------------------|---------------------------
|CDE                          | https://xxxx./abcde  
|ABC                          | https://xxxx./ffcde

but now is the result is:

 Name                         | Link               
|-----------------------------|---------------------------
|CDE                          | null 
|ABC                          | https://xxxx./ffcde

It seems cannot update successfully through my code .So how to solve this problem?

*I run print out it is works and have all the id and link. But not idea cannot update into a table.

What I have tried:

The code I try to update the table:

package com;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;

public class yiutube {
    static String Url;
    static String githubid;
    final String url =
            "https://github.com/STIW3054-A202/class-activity-A202/issues/2";
    public static void main(String[] args) {



        new yiutube().list();
        new yiutube().lists();


    }
    public void list () {

        try {
            final Document document = Jsoup.connect(url) //it's in milliseconds, so this means 5 seconds.
                    .get();

            for (Element row : document.getElementsByClass("author Link--primary css-truncate-target width-fit").subList(1, 23)) {
                if (row.select("[href]").text().equals("")) {
                    continue;
                } else {
                  githubid = row.select("[href]").text();
                    
                    System.out.println(ulink);
                    System.out.println(githubid);


                }
            }

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


    }

    private java.sql.Connection connect () {
        // SQLite connection string
        String url = "jdbc:sqlite:C://sqlite/db/test3.db";
        java.sql.Connection conn = null;
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }



    public void lists() {
        try {
            final Document document = Jsoup.connect(url).get();


            Elements links = document.select("div.edit-comment-hide tr");
            Elements linkss = links.select("a[href]");
            for (Element link : linkss.subList(0, 22)) {

                Url = link.attr("href");

                yiutube l = new yiutube();
                l.update();
            }

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


    }

    public void update() {
        String sql = "UPDATE uList SET youtubelink = ? " + " WHERE githubId = ?";

        try (java.sql.Connection conn = this.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // set the corresponding param

            pstmt.setString(1, Url);
            pstmt.setString(2,githubid);
            pstmt.executeUpdate();

            // update
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

Posted 6-May-21 20:22pm

Cow cow Lee

Updated 8-May-21 22:47pm

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2021-05-06T21:37:00

Solution 1

You are creating the data in your (static) main method. You then create a new yiutube instance and call its add method, but without any parametersd. You should change it so main captures the information and passes the data to add thus:

Java

yiutube t = new yiutube(); // create instance here
for (Element row : document.getElementsByClass("author Link--primary css-truncate-target width-fit").subList(1, 23)) {
    if (row.select("[href]").text().equals("")) {
        continue;
    } else {
        String githubid = row.select("[href]").text();
       
         System.out.println(githubid);
         t.add(githubid, url); // add the data here
         
    }
}

Change the add method to:

Java

public void add(String githubid, String url) {

Posted 6-May-21 21:37pm

Richard MacCutchan

Comments

Cow cow Lee 7-May-21 3:54am

ok, I have try it this code ,but still same result like :

Name | Link
|------|---------------------------
|CDE | https://xxxx./abcde
|ABC | https://xxxx./abcde

How I can insert the link that "different link with different name".

I have the different link but insert it that only one of the link to insert the table

Richard MacCutchan 7-May-21 5:39am

You need to have a single loop:

For each item that you want to save:
  Capture both fields (githubid and URL)
  Pass them to the add method

Cow cow Lee 7-May-21 5:55am

can share some code because I not have idea where to insert the single loop and where to pass them to add method

Richard MacCutchan 7-May-21 7:05am

Are you saying you do not understand your own code? You already have the loop you just need to extract both fields inside it.

Cow cow Lee 7-May-21 7:12am

just now try doing but still same result ,may you help me take a look ,see what are wrong with my code. Because not familiar with looping. Now I update the question.

It means need doing two loop?

Richard MacCutchan 7-May-21 7:27am

You still have two loops. In the first one you capture the URL string. In the second one you capture the githubid string, and pass it to the add method with the existing URL. So every entry that you add to the database will have the same URL.

You need to capture both items in the same loop.

Cow cow Lee 7-May-21 9:49am

ok but I have not idea to combine two loop into one loop. May show example to me?

Richard MacCutchan 7-May-21 10:02am

Just move the code in the first loop inside the second one. I do not understand what the loops are actually doing, so you will have to work it out, based on what data you are trying to extract. But the key point is that you must capture the two connected fields each time round the main loop, which you then pass to the add method.

Cow cow Lee 9-May-21 6:43am

I have change the code and become the two methods but the result is not duplicate link and it become null like the question that I update. May I ask still same problem about the loop??

Richard MacCutchan 9-May-21 6:49am

Do not use static variables to pass values between methods. As i said before, use the loop to capture the data (URL and githubid), and pass those to the update method:

for (whatever count values you use for the loop){
    URL = // capture the URL for this item
    githubid = // capture the URL for this item
    update(URL, githubid)
}

    public void update(string url, string githubid) {
        // pass the data to the database
    }

Cow cow Lee 9-May-21 9:54am

ok, but two different class from webscraping, so I try to using two loops. I have not idea scrapping two different class from webscrapp.I search internet most of is for same class so that can using 1 loop.

Richard MacCutchan 9-May-21 9:59am

The problem is the same:
Capture each field from the scraping.
Pass both fields to the update method.

Try writing the steps out on paper:
1. What do you need to do first?
2. Where do the inputs come from, and how do you capture them?
3. Once you have the inputs where do you need to put them?
... etc.

Cow cow Lee 9-May-21 10:53am

ok thanks I try to do