return a array from a class java

Question

I'm from a UFT background. With that being said I don't know much about JAVA. I have the following code where I connect to sql server and retrieve some values and loop through them and then print them out to log. There is a string array varialbe "String[] sqlArr" that holds all the values retrieved from sql server. How to return that "String[] sqlArr" variable to a different class? In this case to main class.

I'm not exactly sure how to return the array but I have tried changing the return type to String and place a return statement right before method close curly brace. I get a compilation error.

What I want to know is how can I, 1. Instead of looping it inside the method, return the array to Main so i can use each value of the array to my need. 2. What should be the return type if not void in this case? 3. Maybe someone can recommend me or modify my script in a different way which will look more professional.

Any suggestions comments on that will be highly appreciated.

package com.mmia;

import java.sql.*;

public class Connect2SQLServer {

    //Current Username
    private String currentUser = System.getProperty("user.name");

    public void connect2SQLServer() throws SQLException, ClassNotFoundException {

        //Loading the required JDBC Driver class
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        //Creating a connection to the database
        Connection conn = DriverManager.getConnection("jdbc:sqlserver://XXXXXXXXXXX;databaseName=Data_Table_Name;integratedSecurity=true");

        //Executing SQL query and fetching the result
        Statement st = conn.createStatement();

        //Sql query
        String sqlStr = "Select * From PropertiesTable where Username =" + "'" + currentUser + "'";

        //Execute the query
        ResultSet rs = st.executeQuery(sqlStr);

        while (rs.next()) {

            String Username = rs.getString("Username");
            String Environment = rs.getString("Environment");
            String WebDealer = rs.getString("WebDealer");
            String WebAgent = rs.getString("WebAgent");
            String WebPassword = rs.getString("WebPassword");
            String InternalUser = rs.getString("InternalUser");
            String InternalPassword = rs.getString("InternalPassword");
            String Browser = rs.getString("Browser");

            //String[] sqlArr;
            String[] sqlArr = {Username, Environment, WebDealer, WebAgent, WebPassword, InternalUser, InternalPassword, Browser};

            for (int i = 0; i < sqlArr.length; i++) {
                System.out.println(sqlArr[i]);
            }
        }
    }
}

Answer 1

if you want to use arrays then you'll need to declare a multidimensional array before the while loop specifying the number of rows the query will return (which is difficult considering records could be inserted and removed from the source table) along with the number of columns that the table has.

Thus, I'd recommend that you use a nested ArrayList to store the records. each ArrayList of the outer ArrayList will consist of the values for each field of each record.

how to accomplish the aforementioned?

1. create the nested ArrayList before the while loop to accumulate the other ArrayList's.
2. for each iteration of the loop create a local ArrayList to store the fields of each record then add that ArrayList to the ArrayList outside of the loop.
3. ensure that the method connect2SQLServer has a return type of ArrayList<ArrayList<String>>
4. return the nested ArrayList.

you could easily extend this by creating a concrete type which represents each record of the source table and then you wouldn't need a nested ArrayList<ArrayList<String>> but rather just an ArrayList<TheType>.

---

As a rule of thumb, you shouldn't include all the logic inside connect2SQLServer as this methods sole purpose should be to connect to the DBMS. Rather you should have 4 methods representing the CRUD SQL commands (if you need all them), each method doing its job. However, in this case, since you're only performing a read command you can separate that logic away from the connect2SQLServer method making your code easier to maintain and less error prone when the code gets larger.

Answer 2

I think this is what you are trying to do:

public String[][] connect2SQLServer() throws SQLException, ClassNotFoundException {
    // code..
    List<String[]> data = new LinkedList<>();
    while (rs.next()) {
        // more code..
        String[] sqlArr = {Username, Environment, WebDealer, WebAgent, WebPassword, InternalUser, InternalPassword, Browser};
        data.add(sqlArr);
    }
    return data.toArray(new String[data.size()][]);
}

And your Main:

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connect2SQLServer con = new Connect2SQLServer();
        String[][] data = con.connect2SQLServer();
    }
}

Answer 3

You can use an array for each record but a better approach would be to create an object for each record and collect all these records into a list.

Example:

class Record {

    private String username;
    private String environment;
    private String webDealer;
    private String webAgent;
    private String webPassword;
    private String internalUser;
    private String internalPassword;
    private String browser;

    public Record(String username, String environment, String webDealer, String webAgent, String webPassword,
                  String internalUser, String internalPassword, String browser) {
        this.username = username;
        this.environment = environment;
        this.webDealer = webDealer;
        this.webAgent = webAgent;
        this.webPassword = webPassword;
        this.internalUser = internalUser;
        this.internalPassword = internalPassword;
        this.browser = browser;
    }

    public String getUsername() {
        return username;
    }

    public String getEnvironment() {
        return environment;
    }

    public String getWebDealer() {
        return webDealer;
    }

    public String getWebAgent() {
        return webAgent;
    }

    public String getWebPassword() {
        return webPassword;
    }

    public String getInternalUser() {
        return internalUser;
    }

    public String getInternalPassword() {
        return internalPassword;
    }

    public String getBrowser() {
        return browser;
    }
}

And then you can use it:

// the method signature has changed and now it returns a list of records
public List<Record> connect2SQLServer() throws SQLException, ClassNotFoundException {
    // ...


    // create the list that will hold the results (records)
    List<Record> results = new LinkedList<>();
    while (rs.next()) {

        String username = rs.getString("username");
        String environment = rs.getString("environment");
        String webDealer = rs.getString("webDealer");
        String webAgent = rs.getString("webAgent");
        String webPassword = rs.getString("webPassword");
        String internalUser = rs.getString("internalUser");
        String internalPassword = rs.getString("internalPassword");
        String browser = rs.getString("browser");

        // create a new record
        Record record = new Record(username, environment, webDealer, webAgent,
                                   webPassword, internalUser, internalPassword, browser);

        // add the record to results
        results.add(record);
    }

    return results;
}

Comment: I took the liberty to modify the names of your variables to . not start with capital-case in order to comply with Java code conventions