Insert data into MySQL using JDBC

Question

I am trying to insert data into MySQL database using JDBC. My problem is that in the second row data starts from the end of the first! .The picture explains better than me. This is an example of my code. I would like to be stored from the first column and down.

public static void main(String[] args) throws SQLException {

    Connection conn = null;
    Statement stmt = null;
    try{
       //STEP 2: Register JDBC driver
       Class.forName("com.mysql.jdbc.Driver");

       //STEP 3: Open a connection
       System.out.println("Connecting to a selected database...");
       conn =  DriverManager.getConnection("jdbc:mysql://83.212.124.175:3306/zadmin_java?useUnicode=yes&characterEncoding=UTF-8","username", "pass.." );
       System.out.println("Connected database successfully...");

       //STEP 4: Execute a query
       System.out.println("Inserting records into the table...");
       stmt = (Statement) conn.createStatement();

        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();
            //get  elements
            Elements phoneUrls = mobilePhones.select("div[class=details] a ");
            Elements phoneName = mobilePhones.select("div[class=details]");
            Elements phonePrice = mobilePhones.select("p[class=price]");
            Elements phoneRating = mobilePhones.select("div[class=rating-wrapper] span");
            Elements phoneSpecs = mobilePhones.select("p[class=specs]");

            //insert all urls to db
            for(int i = 1; i<phoneUrls.size();i++){

                String urls = phoneUrls.get(i).absUrl("href");
                String sql = "INSERT INTO skrouzt(url) " +
                    "VALUES ( '"+urls+"' )";
                stmt.executeUpdate(sql);

            }


             //insert all names to db
            for(int i =1; i<phoneName.size();i++){
                String names = phoneName.get(i).text();
                String insert = "INSERT INTO skrouzt(name) " +
                    "VALUES ( '"+names+"' )";

                stmt.executeUpdate(insert);

            }               
        }


       System.out.println("Inserted records into the table...");

    }catch(SQLException se){
       //Handle errors for JDBC
       se.printStackTrace();
    }catch(Exception e){
       //Handle errors for Class.forName
       e.printStackTrace();
    }finally{
       //finally block used to close resources
       try{
          if(stmt!=null)
             conn.close();
       }catch(SQLException se){
       }// do nothing
       try{
          if(conn!=null)
             conn.close();
       }catch(SQLException se){
          se.printStackTrace();
       }//end finally try
    }//end try
    System.out.println("Goodbye!");
}//end of the main method

You only can use INSERT once per row, Next time you want to add or update something use UPDATE. Check here http://stackoverflow.com/questions/2002500/what-are-differences-between-insert-and-update-in-mysql — chinna_82, Apr 15 '15 at 01:57
yes but the name row is empty. I must insert data no update!! — dimit, Apr 15 '15 at 02:13

score 1 · Accepted Answer · answered Apr 15 '15 at 06:13

Replace the 2 loops with one like this.

        int rowCount=Math.max(phoneUrls.size(),phoneName.size()); 
        for(int i = 1; i<rowCount;i++){

            String urls = (i<phoneUrls.size()) ? "'"+phoneUrls.get(i).absUrl("href")+"'" : "NULL";
            String names = (i<phoneName.size()) ? "'"+phoneName.get(i).text()+"'" : "NULL";
            String sql = "INSERT INTO skrouzt(url, name) " +
                "VALUES ( "+urls+","+names+" )";
            stmt.executeUpdate(sql);

        }

thank you, works fine...i can't vote up because I don't have enough reputation — dimit, Apr 15 '15 at 20:45

Insert data into MySQL using JDBC

1 Answers1