CODEWITHSUNDEEP
javasql-server
Aishu
Aishu

Reputation: 1330

Java - Sql query with Alias

I want to retrieve a particular column from the database. For a simple Select statement, I can able to able to retrieve a column like below

public String getDbColumnValue(String tableName, String columnName, String applicationNumber) {
    String columnValue = null;

    try {
        PreparedStatement ps = null;
        String query = "SELECT " + columnName + " FROM " + tableName + 
                       " WHERE ApplicationNumber = ?;";

        ps = conn.prepareStatement(query);
        ps.setString(1, applicationNumber);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            columnValue = rs.getString(columnName);
            return columnValue;
        }
    } 
    catch (Exception ex) {
    }
    return columnValue;
}

But, I'm using alias in my query like below. And this query works fine. How to use this in Java to retrieve a particular column

select S.StatusDesc from application A, StatusMaster S 
where A.StatusMasterId = S.StatusMasterId and A.ApplicationNumber = '100041702404'

Any help would be greatly appreciated!

Upvotes: 1

Views: 3876

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

I think you are confusing simple aliases, which are used for table names, with the aliases used for column names. To solve your problem, you can just alias each column you want to select with a unique name, i.e. use this query:

select S.StatusDesc as sc
from application A
inner join StatusMaster S 
    on A.StatusMasterId = S.StatusMasterId and
       A.ApplicationNumber = '100041702404'

Then use the following code and look for your aliased column sc in the result set.

PreparedStatement ps = null;
String query  = "select S.StatusDesc as sc ";
       query += "from application A ";
       query += "inner join StatusMaster S ";
       query += "on A.StatusMasterId = S.StatusMasterId ";
       query += "and A.ApplicationNumber = ?";
ps = conn.prepareStatement(query);
ps.setString(1, applicationNumber);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
    columnValue = rs.getString("sc");
    return columnValue;
}

Note: I refactored your query to use an explicit inner join instead of joining using the where clause. This is usually considered the better way to write a query.

Upvotes: 2

Related Questions