java - 使用JDBC向MySQL插入数据

Question

我正在尝试使用 JDBC 将数据插入 MySQL 数据库。我的问题是第二行数据从第一行末尾开始! . The picture explains better than me .这是我的代码示例。我想从第一列往下存储。

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

Answer 1

用这样的循环替换 2 个循环。

        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);

        }