java.sql.SQLException: No value specified for parameter 2 ,Inserting arraylist to database using JDBC

Question

I am extracting some data from website using jsoup and storing to arraylist. Now i want to store the data to mysql database. This is my code but i got this java.sql.SQLException: No value specified for parameter 2

This is my class to get data from the website.

public class Twrapper {

    ArrayList<MobilePhone> skrouztPhones = new ArrayList<>();

    protected ArrayList<MobilePhone> getPhoneNames() throws IOException{

        for (int j=1; j<=1;j++){
            Document mobilePhones = Jsoup.connect("http://www.skroutz.gr/c/40/kinhta-thlefwna.html?order_dir=asc&page=" + j).userAgent("Mozilla").get();
            Elements phoneName = mobilePhones.select("div[class=details]");
            for(int i = 1; i<phoneName.size();i++){
                MobilePhone names = new MobilePhone();
                names.setName(phoneName.get(i).text());
                skrouztPhones.add(names);
            }      
        }
    return skrouztPhones;
    }

    protected ArrayList<MobilePhone> getPhonesUrls() throws IOException{

        for(int j =1; j<=1;j++){
            Document mobilePhone = Jsoup.connect("http://www.skroutz.gr/c/40/kinhta-thlefwna.html?order_dir=asc&page=" + j).userAgent("Mozilla").get();
            Elements phoneUrls = mobilePhone.select("div[class=details] a ");
            for(int i =0; i<phoneUrls.size(); i++){
                MobilePhone urls = new MobilePhone();
                urls.setUrl(phoneUrls.get(i).absUrl("href"));
                skrouztPhones.add(urls);
            }
        }
        return skrouztPhones;
    }
}

This is my method to store the data to database.

public static  void main(String[] args) throws  IOException, SQLException{
    Twrapper wrapper = new Twrapper();


    try
    {
        // create a mysql database connection
        String myDriver = "org.gjt.mm.mysql.Driver";
        String myUrl = "jdbc:mysql://83.212.124.175:3306/zadmin_java?useUnicode=yes&characterEncoding=UTF-8";
        Class.forName(myDriver);
        System.out.println("Connecting to a selected database...");
        Connection conn = DriverManager.getConnection(myUrl, "usr", "pass");
        System.out.println("Connected database successfully...");

        // the mysql insert statement
        String query = " INSERT INTO skrouzt(url,name)"
          + " values (?, ?)";
        PreparedStatement updateSkrouztPs = conn.prepareStatement(query);
        //use the wrapper methos to insert to db


        ArrayList<MobilePhone> skroutzPhonesUrls = wrapper.getPhonesUrls();
        for(MobilePhone phone: skroutzPhonesUrls){
            String urls = phone.getUrl();
            updateSkrouztPs.setString(1, urls);
            updateSkrouztPs.executeUpdate();
        }
        ArrayList<MobilePhone> skrouztPhonesNames  = wrapper.getPhoneNames();
        for(MobilePhone phone: skrouztPhonesNames){
            String names = phone.getName();
            updateSkrouztPs.setString(2, names);
            updateSkrouztPs.executeUpdate();
        }







        //close the connection
        conn.close(); 
        System.out.println("Done!!");
    }
    catch(SQLException se){
       //Handle errors for JDBC
       se.printStackTrace();
    }
    catch (Exception e)
    {
      System.err.println("Got an exception!");
      System.err.println(e.getMessage());

    }
}

In your first loop you only specify a value for parameter 1 updateSkrouztPs.setString(1, urls);and you do executeUpdate(), you get an error because u dont specify parameter 2 — Sybren
– Sybren, Commented Apr 24, 2015 at 20:54

Thomas · Accepted Answer · 2015-04-24 21:12:46Z

3

Your prepared statement needs both strings set before you can call executeUpdate. In each loop, you're trying to set 1, then run the update, which means jdbc ends up with a sql statement like

INSERT INTO skrouzt(url,name) values ('your url',)

With no second value set, the statement is invalid. If these urls and names match each other, you should go through a single loop inserting the data in pairs.

Example as requested. Note that this only works if the lists are the same length, but if they're different lengths then the database needs to be designed differently to not have them be two columns in the same table.

for (int x = 0; x < skroutzPhonesUrls.size(); x++)
{
  updateSkrouztPs.setString(1, skroutzPhonesUrls.get(x).getUrl());
  updateSkrouztPs.setString(2, skrouztPhonesNames.get(x).getName());
  updateSkrouztPs.executeUpdate();
}

edited Apr 24, 2015 at 21:12

answered Apr 24, 2015 at 20:53

Thomas

5,0941 gold badge18 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dimit Over a year ago

can you give me an example

Sybren Over a year ago

@dimit if possible you can use one for loop to set both parameters and call executeUpdate()

dimit Over a year ago

the list has the same length(35)....your loop works but in db the name insert start from the last row of the urls(dl.dropboxusercontent.com/u/81348559/sfarrr.JPG)

Jens · Accepted Answer · 2015-04-24 20:53:22Z

1

You do not bind a value for parameter name.

updateSkrouztPs.setString(2, phone.getName());

answered Apr 24, 2015 at 20:53

Jens

69.6k15 gold badges107 silver badges133 bronze badges

1 Comment

dimit Over a year ago

yes but first I must call the wrapper.getPhoneNames and store the results to arraylist of Mobile Phones.

Collectives™ on Stack Overflow

java.sql.SQLException: No value specified for parameter 2 ,Inserting arraylist to database using JDBC

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related