Run

java -cp ojdbc8.jar;. OracleQueryExample

Java Code

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class OracleQueryExample {
	
	
    public static void main(String[] args) {
        // Database connection settings
		
        String jdbcUrl = "jdbc:oracle:thin:@//localhost:1521/maximodatabase"; // Update with your Oracle connection URL
        String username = "USERDB"; // Update with your Oracle username
        String password = "PASSWORD"; // Update with your Oracle password
    	
        // SQL queries
        String mainQuery = "SELECT * from maxsequence where sequencename = ?";
        //String subQuery = "select max(logintrackingid) from ";

        // List of strings
        List<String> stringList = new ArrayList<>();
        stringList.add("EVENTRESPONSESEQ");
        stringList.add("MULTIASSETLOCCISEQ");
        stringList.add("ASSETSEQ");


        // List to store objects
        List<YourObject> objectList = new ArrayList<>();

        try {
            // Establishing a database connection
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Executing the main query for each string in the list
            for (String value : stringList) {
                // Executing the main query and retrieving results
                PreparedStatement mainStatement = connection.prepareStatement(mainQuery);
                mainStatement.setString(1, value); // Setting the parameter for the main query
                ResultSet mainResultSet = mainStatement.executeQuery();

                // Iterating over the main query results
                while (mainResultSet.next()) {
                    YourObject obj = new YourObject();

                    // Retrieve data from main query result and set properties in the object
					obj.sequencename = value;
                    obj.tablename = mainResultSet.getString("tbname");
                    obj.columnname = mainResultSet.getString("name");
					obj.maxreserved = mainResultSet.getInt("maxreserved");

                    // Executing the sub-query for each object
					String subQuery = "select max(" + mainResultSet.getString("name") + ") as maxvalue from " + mainResultSet.getString("tbname");
                    PreparedStatement subStatement = connection.prepareStatement(subQuery);
                    ResultSet subResultSet = subStatement.executeQuery();

                    // Process sub-query result and set additional properties in the object
                    if (subResultSet.next()) {
                        obj.maxvalue = subResultSet.getInt("maxvalue");
                    }

                    // Executing the sub-query for each object
					String subQuery2 = "select " + value + ".nextval as nextval from dummy_table";
                    PreparedStatement subStatement2 = connection.prepareStatement(subQuery2);
                    ResultSet subResultSet2 = subStatement2.executeQuery();

                    // Process sub-query result and set additional properties in the object
                    if (subResultSet2.next()) {
                        obj.nextval = subResultSet2.getInt("nextval");
                    }


                    // Adding the object to the list
                    objectList.add(obj);

                    // Closing the sub-query result set and statement
                    subResultSet.close();
                    subStatement.close();
                }

                // Closing the main query result set and statement
                mainResultSet.close();
                mainStatement.close();
            }

            // Closing the database connection
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // Print the objects in the list
        for (YourObject obj : objectList) {
            // System.out.println(obj.toString());
			
			
			int highest = Math.max(Math.max(obj.maxreserved, obj.maxvalue), obj.nextval) + 1;
			System.out.println();
			System.out.println();
			System.out.println("-- sequenceName=" + obj.sequencename  + ", maxreserved=" + obj.maxreserved + ", maxvalue=" + obj.maxvalue  +", nextval=" + obj.nextval );
			System.out.println();
			System.out.println("Drop Sequence " + obj.sequencename + " ;");
            System.out.println("Create Sequence " + obj.sequencename + " Start With " +  highest + " Increment By 1 Nocache Nocycle ;");
            System.out.println("Update maxsequence Set maxreserved=" +  highest + " Where sequencename='" + obj.sequencename + "';");
        }
    }
}

class YourObject {
	public String sequencename;
    public String tablename;
    public String columnname;
    public int maxreserved;
	public int maxvalue;
	public int nextval;
	public int highest;

    // Constructors, getters, setters, and other methods

    @Override
    public String toString() {
        //this.highest = findHighest(maxreserved, maxvalue, nextval);
		
        return "YourObject{" +
                "sequenceName=" + sequencename +
				", tablename='" + tablename + '\'' +
                ", columnname='" + columnname + '\'' +
                ", maxreserved='" + maxreserved + '\'' +
				", maxvalue='" + maxvalue + '\'' +
				", nextval='" + nextval + '\'' +
                '}';
    }
	
	public int findHighest(int a, int b, int c) {
        return Math.max(Math.max(a, b), c);
    }
}

Ver também