'How to skip the first n rows in a MySQL database with a Java program?

I have a problem. I am writing a java application which shall move some files. Because of performance issues the user can select the amount of files that shall be moved. If the file is moved, my database shall be updated with the new paths. I've already coded that but my problem now is that it just works for the first, for example, 10 files. Then the application terminates.

So until now everything works as expected. Now I want to start the application again and it shall move 10 files again. Now the problem is that it starts again at the top of the database and tries to move the first 10 files, which have been already moved in the first run of my application. So how can I tell my program that it shall skip the first 10(n) files?

I had the idea of generating a new column to the database with ones and zeros. And every file that has been moved gets a 1 and its set by default to zero. Then the program checks if there is a one or a zero and if there is a one then it just continues with the next row. But I didn't find a way on how to create a column in a database with java. The next problem is that this column shall be deleted after the run of the application.

I tried to write a procedure and I found this:

DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
  DECLARE _count INT;
    SET _count =( SELECT COUNT(*)
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME = 'test_cm_documents' AND
                    COLUMN_NAME='subscribe_all');
  IF _count = 0 THEN
    ALTER TABLE test_cm_documents
    ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
        ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;
  END IF;
END$$
DELIMITER ;

It worked if I pasted it into the MySQL command line but it did not worked when I tried to execute it with a Java program. I also tried to save that as a *.sql file but I can't execute it from my Java program.

I have two Java Programs with which I tried to solve my Problems but I got several errors.

import java.io.*;

public class CmdExec {

public static void main(String[] args) {
    try {
        String line;
        Process p = Runtime.getRuntime().exec(
                "mysql -u myuser -p xxxx -d cm_database -h 3306 -f C://Users//****//Desktop//procedure.sql");
        BufferedReader input =
                new BufferedReader
                    (new InputStreamReader(p.getInputStream()));
        while ((line = input.readLine()) != null) {
            System.out.println(line);
        }
        input.close();
    }catch(Exception err) {
        err.printStackTrace();
    }
}

}

ERRORS:

java.io.IOException: Cannot run program "mysql": CreateProcess error=2, 
Das 
  System kann die angegebene Datei nicht finden
    at java.lang.ProcessBuilder.start(Unknown Source)
    at java.lang.Runtime.exec(Unknown Source)
    at java.lang.Runtime.exec(Unknown Source)
      at CmdExec.main(CmdExec.java:8)
  Caused by: java.io.IOException: CreateProcess error=2, Das System kann 
 die angegebene Datei nicht finden
at java.lang.ProcessImpl.create(Native Method)
at java.lang.ProcessImpl.<init>(Unknown Source)
at java.lang.ProcessImpl.start(Unknown Source)
... 5 more

Second Java program:

import java.sql.*;

public class startProcedure {

public static void main(String[] args) {
    try(Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cm_database?allowPublicKeyRetrieval=true&SSL=false&serverTimezone=UTC",
            "myuser","xxxx");
            
            Statement delimiter = conn.createStatement();){
        
            String delimiterStr = 
                    "CREATE PROCEDURE Alter_Table() "
                    + " BEGIN "
                    + " DECLARE _count INT; "
                    + " SET _count =( SELECT COUNT(*) "
                                   + " FROM INFORMATION_SCHEMA.COLUMNS "
                                   + " WHERE TABLE_NAME='test_cm_documents' AND "
                                          + " COLUMN_NAME='subscribe_all'); "
                    + " IF _count = 0 THEN "
                        + " ALTER TABLE test_cm_documents"
                           + " ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,"
                           + " ADD COLUMN subscribe_category varchar(512) DEFAULT NULL "
                           + " END IF; "
                    + " END; "
                    ;
            
            delimiter.executeUpdate(delimiterStr);
    }catch(SQLException ex) {
        ex.printStackTrace();
    }
    
    
}
 
    }

I get following error: You have an error in your SQL Statement.

Maybe someone can help me, because I am quite new to the topic. Excellent will be an application which will just skip n rows. I thinks this will be the most easy solution.

javamysqlsqljdbc


Solution 1:[1]

OFFSET is what you are looking for :

select * from table_name limit x offset y; -- to skip y rows

Or use LIMIT with two parameters. For example, to return 30 rows with first 10 rows skipped (will return 11 to 40), use:

select * from table_name limit 10, 30;

Solution 2:[2]

Use Cte query to skip first 10(n) files and get recent save same row. for example

UID GID MID
1 1 1
2 1 2
3 1 2
4 1 2
5 2 1
6 2 2
7 2 2
8 2 3
9 2 4 

with Demo as
(
select *, ROW_NUMBER() over (partition by GID, MID order by UID) as ROWNUMBER  from Table1
)

cte create a table 

UID     GID   MID   ROWNUMBER
1        1      1      1
2        1      2      1
3        1      2      2
4        1      2      3 
5        2      1      1
6        2      2      1
7        2      2      2
8        2      3      5
9        2      4      4

select  * from Demo ROWNUMBER  = 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 sovannarith cheav
Solution 2

Related Questions