Как падать записи процессов в SQL и Java, обеспечивая при этом идентификаторы соответствующих документов в одной и той ж ⇐ JAVA

[Anonymous]

У меня есть таблица базы данных, где у каждой записи есть уникальный идентификатор, DocumentID (который можно повторить по нескольким рядам) и пакид (который изначально NULL). < /p>
Я хочу обработать эти данные в партиях, используя SQL и Java с следующими условиями: < /p>

• 
  
  . /> Все строки, принадлежащие к одному и тому же документу, должны быть в одной и той же партии. But a batch should never be smaller than 10.
• Once a batch is formed, I need to update the batchid for all records belonging to the documentids in that batch.

id < /th>
documentId < /th>
batchid < /th>
< /tr>
< /thead>


1 < /td>
101 < /td>
< /td> < /td>
101 < /td>
< /td> < /td> < /td> < /td> < /td> < /td> < /td> < /td> < /td> < /td> < /td> < /td> < /td> /> < /tr>

2 < /td>
101 < /td>
null < /td>
< /tr>

3 < /td>
101
3 < /td>
101 < /td>

101
3 < /td>
101 /> < /tr>

4 < /td>
101 < /td>
null < /td>
< /tr>

5 < /td>
101
5 < /td>
101

101
/> < /tr>

6 < /td>
102 < /td>
null < /td>
< /tr>

7 < /td>
102

102 < /td>

102
/> < /tr>

8 < /td>
102 < /td>
null < /td>
< /tr>

9 < /td>
102

102 < /td>

102
9 < /td>
102 /> < /tr>

10 < /td>
102 < /td>
null < /td>
< /tr>

11 < /td>
103

103 < /td>

103
< /td>
103
/> < /tr>

12 < /td>
104 < /td>
null < /td>
< /tr>

13 < /td>
104

104

104
13 < /td>
104
/> < /tr>

14 < /td>
104 < /td>
null < /td>
< /tr>

15 < /td>
105
15 < /td>
105

105
/> < /tr>
< /tbody>
< /table> < /div>
и т. Д. />documentid
batchid




1
101
batch_1


2 < /td>
101 < /td>
batch_1 < /td>
< /tr>

3 < /td>
101 < /td>
< /td>
101 < /td>
< /td>
101 < /td>
/> < /tr>

4 < /td>
101 < /td>
batch_1 < /td>
< /tr>

5 < /td>
101
5 < /td>
101 /> batch_1 < /td>
< /tr>

6 < /td>
102 < /td>
batch_1 < /td>
< /tr>

7 < /td>
< /tr>

/> 102 < /td>
batch_1 < /td>
< /tr>

8 < /td>
102 < /td>
batch_1 < /td>
< /tr>
/> 9 < /td>
102 < /td>
batch_1 < /td>
< /tr>

10 < /td>
102 < /td>
batch_1 < /td>

102 < /td>
batch_1
102 < /td>
batch_1
102 < /td>
batch_1 />
11 < /td>
103 < /td>
batch_2 < /td>
< /tr>

12 < /td>
104 < /td>
< /td>
104 < /td>
/> < /tr>

13 < /td>
104 < /td>
batch_2 < /td>
< /tr>

14 < /td>
104
14 < /td>
104
14 < /td>
104 /> batch_2 < /td>
< /tr>

15 < /td>
105 < /td>
batch_2 < /td>
< /tr>

. />....
....
< /tr>
< /tbody>
< /table> < /div>
package abc;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.Properties;
import java.util.Scanner;

import Database.InterimDBConnection;
import Database.InterimDatabaseOperation;
import Database.PropertiesLoader;

public class test {

static Properties appConfigProp = null;
static Properties sqlConfigProp = null;
static InterimDBConnection idco = new InterimDBConnection();
InterimDatabaseOperation ido = new InterimDatabaseOperation(idco.getConnection());

private static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("ddMMyy");
private static String lc = LocalDate.now().format(formatter);

private static final String TABLE2 = "BATCH_test";

//private static final int BATCH_SIZE = 1000;
// private static Timestamp beforeUpdate;

public static void main(String[] args) {
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
Statement statement = null;
PropertiesLoader ucmProperties = new PropertiesLoader();
appConfigProp = ucmProperties.loadAppConfigParam();
PropertiesLoader ucmProperties1 = new PropertiesLoader();
sqlConfigProp = ucmProperties1.loadAppSqlQueryParam();
System.out.println("Enter table name");
Scanner scanner = new Scanner(System.in);
String TABLE1 = scanner.nextLine();
System.out.println("Enter batch size");
Scanner scanner1 = new Scanner(System.in);
String BATCH_SIZE = scanner1.nextLine();
// System.out.println("Enter the table name: "+TABLE1);

try {

int batchCounter = 1;

while (true) {

String query = "SELECT distinct TOP " + BATCH_SIZE + " docid FROM " + TABLE1
+ " WHERE migration_batch_id IS NULL";
pstmt = idco.getConnection().prepareStatement(query);
resultSet = pstmt.executeQuery();

if (!resultSet.isBeforeFirst()) {
break;
}

String batchId = generateBatchId(batchCounter, TABLE1);

int rowCount = 0;
while (resultSet.next()) {
rowCount++;
}

Timestamp beforeUpdate = new Timestamp(System.currentTimeMillis());
updateBatchId(batchId, rowCount, TABLE1,BATCH_SIZE);

// createBatchTable(batchId, TABLE1);

Timestamp afterUpdate = new Timestamp(System.currentTimeMillis());

insertRecord(batchId, rowCount, beforeUpdate, afterUpdate, TABLE1);
batchCounter++;
}

} catch (SQLException e) {
System.out.println("Error accessing the database: " + e.getMessage());
} finally {

if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
System.out.println("Error closing result set: " + e.getMessage());
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
System.out.println("Error closing prepared statement: " + e.getMessage());
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
System.out.println("Error closing statement: " + e.getMessage());
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
System.out.println("Error closing connection: " + e.getMessage());
}
}
}
}

/*private static void createBatchTable(String batchId, String TABLE1) {
System.out.println("create");
String createTable = "select * into " + batchId + " from " + TABLE1 + " WHERE migration_batch_id='" + batchId
+ "'";
System.out.println(createTable);
PreparedStatement pstmt1 = null;
try {
pstmt1 = idco.getConnection().prepareStatement(createTable);
pstmt1.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// resultSet = pstmt1.executeQuery();
System.out.println("before update");

System.out.println("update");

}*/

private static String generateBatchId(int batchCounter, String TABLE1) {

System.out.println("id");
return TABLE1 + "_batch_" + batchCounter;

}

private static void updateBatchId(String batchId, int rowCount, String TABLE1,String BATCH_SIZE) throws SQLException {
System.out.println("upinside");

// while(resultSet.next()) {
String updateQuery = "UPDATE " + TABLE1 + " SET Migration_Batch_Id = '" + batchId
+ "' WHERE documentid in (SELECT distinct TOP " + BATCH_SIZE + " documentid FROM " + TABLE1+ " WHERE migration_batch_id IS NULL)";

PreparedStatement pstmt1 = idco.getConnection().prepareStatement(updateQuery);
// resultSet = pstmt1.executeQuery();
System.out.println("before update");

pstmt1.executeUpdate();

System.out.println("update");
}
// }

private static void insertRecord(String batchId, int rowCount, Timestamp beforeUpdate, Timestamp afterUpdate,
String TABLE1) throws SQLException {
String[] arr = TABLE1.split("_");
String archive = arr[0];

String insertQuery = "INSERT INTO " + TABLE2
+ sqlConfigProp.getProperty("BATCH")
+ batchId + "', " + rowCount + ", '" + TABLE1 + "','" + beforeUpdate + "','" + afterUpdate + "','"
+ archive + "',NULL,NULL,NULL,NULL,NULL,'N')";
System.out.println(insertQuery);
PreparedStatement pstmt2 = idco.getConnection().prepareStatement(insertQuery);

pstmt2.executeUpdate();
// rowCounter++;
System.out.println("insert");


Подробнее здесь: https://stackoverflow.com/questions/795 ... cument-ids