Java - Sql query with Alias

Question

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!

Just as a sidenode, you´re already using the PreparedStatement half correctly for the where clause, why don´t you also do it in the beginning for the column and the table — SomeJavaGuy
– SomeJavaGuy, Commented Apr 12, 2017 at 6:02
@SomeJavaGuy - I want to use the alias query in java to retrieve the column from db. Not sure how can i use this for alias in Java — Aishu
– Aishu, Commented Apr 12, 2017 at 6:05

Tim Biegeleisen · Accepted Answer · 2017-04-12 06:07:50Z

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.

Collectives™ on Stack Overflow

Java - Sql query with Alias

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related