Pacific-Design.com

    
Home Index

1. Java

2. Stored Procedure

Java / Stored Procedure /

Execute MSSQL Stored Procedure, incrementally write result set data into CSV file

import au.com.bytecode.opencsv.CSVWriter;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Properties;


public class RunSQLProcedure {

    static JobConfig config;

    static String sqlServer;
    static String database;
    static String userName;
    static String password;

    static String spName;       // "store_procedure_name";
    static String apParams;     // "null, one, two, three"
    static String fileName;     // "/tmp/datafile.csv"

    private static final DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    public RunSQLProcedure(Properties args) {
        config = new JobConfig(args);
    }


    public void run() {

        sqlServer = config.getSqlServer();
        database = config.getDatabase();
        userName = config.getUserName();
        password = config.getPassword();

        spName = config.getStoredProcedure();
        apParams = config.getSpParameters();
        fileName = config.getFileName();

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (Exception e) {
            e.printStackTrace();
        }
        

        try {

            String url = "jdbc:sqlserver://"+sqlServer+";" + "databaseName=" + database;
            Connection con = DriverManager.getConnection(url, userName, password);


            LocalDateTime now = LocalDateTime.now();
            System.out.println("Execution Started: " + dtf.format(now));

            // execute the procedure
            executeStoredProcedure(con, spName);

            if (con != null) {
                con.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        
    }
    
    private void executeStoredProcedure(Connection con, String spName) throws FileNotFoundException {

        try {

            CallableStatement proc = con.prepareCall("{ call " + spName + " (?,?) }",
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            String[] params = apParams.split(",");
            int i=0;
            for (String param: params) {
                System.out.println("PARAM " + i++ + " " + param.replaceAll("[\\s|\\u00A0]+", ""));
                if(param.equals("null")) {
                    String myNull  = null;
                    proc.setString(i, myNull);
                } else {
                    proc.setString(i, param.replaceAll("[\\s|\\u00A0]+", ""));
                }
            }

            boolean hasResult = proc.execute();

            if(hasResult) {

                ResultSet rs = proc.getResultSet();

                    FileWriter fileWriter = new FileWriter(fileName, true);
                    CSVWriter csvWriter = new CSVWriter(fileWriter);
                    csvWriter.writeAll(rs, false);
                    csvWriter.flush();

                while (hasResult) {
                    hasResult = proc.getMoreResults();

                    if (hasResult) {
                        rs = proc.getResultSet();
                        if (rs.next()) {

                            csvWriter.writeAll(rs, false);
                            csvWriter.flush();
                        }
                    } else {
                        hasResult = false;
                    }
                }

                csvWriter.close();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

App.java

import java.util.Properties;

public class Main {

    public static void main(String[] args) 
    {        
        Properties properties = new Properties();
        properties.setProperty("STORED_PROCEDURE","get_sales_data");
        properties.setProperty("SP_PARAMETERS","null, one, two, three");
        properties.setProperty("FILE_NAME","/tmp/datafile.csv");

        RunSQLProcedure process = new RunSQLProcedure(properties);
        process.run();

    }
}