Not getting MySQL SELECT LPAD() in ResultSet

Question

I am trying to select multiple columns from different tables in MySQL.

MySQL:

CREATE TABLE IF NOT EXISTS movie(
    movie_id TINYINT(3) UNSIGNED AUTO_INCREMENT,
    movie_title VARCHAR(255) NOT NULL,
    movie_genre VARCHAR(255) NOT NULL,
    movie_rating ENUM('G', 'PG', 'R-13', 'R-16', 'R-18', 'PENDING') NOT NULL,
    movie_cast VARCHAR(255) NOT NULL,
    movie_runtime TINYINT(3) UNSIGNED NOT NULL,
    movie_poster VARCHAR(255) NOT NULL,
    PRIMARY KEY(movie_id)
);

CREATE TABLE IF NOT EXISTS mall(
    mall_id VARCHAR(255),
    mall_name VARCHAR(255) NOT NULL,
    PRIMARY KEY(mall_id)
);

CREATE TABLE IF NOT EXISTS schedule(
    schedule_id TINYINT(3) UNSIGNED AUTO_INCREMENT,
    movie_id TINYINT(3) UNSIGNED NOT NULL,
    mall_id VARCHAR(255) NOT NULL,
    schedule_cinema TINYINT(2) UNSIGNED NOT NULL,
    schedule_price DECIMAL(5, 2) NOT NULL,
    schedule_date DATE NOT NULL,
    schedule_time TIME NOT NULL,
    schedule_seats TINYINT(2) UNSIGNED NOT NULL,
    PRIMARY KEY(schedule_id),
    FOREIGN KEY(movie_id) REFERENCES movie(movie_id),
    FOREIGN KEY(mall_id) REFERENCES mall(mall_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS transaction(
    transaction_id SMALLINT(5) UNSIGNED AUTO_INCREMENT,
    user_id VARCHAR(255) NOT NULL,
    schedule_id TINYINT(3) UNSIGNED NOT NULL,   
    transaction_date DATE NOT NULL,
    PRIMARY KEY(transaction_id),
    FOREIGN KEY(user_id) REFERENCES user(user_id),
    FOREIGN KEY(schedule_id) REFERENCES schedule(schedule_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)ENGINE=INNODB;

I tested this query directly in XAMPP MySQL and it returned all desired columns. The transaction_id is also left-padded as intended.

SELECT SQL:

SELECT LPAD(transaction_id, 5, 0), transaction_date, movie_title, schedule_price, mall_name, schedule_cinema, schedule_date, schedule_time FROM transaction INNER JOIN schedule ON transaction.schedule_id = schedule.schedule_id INNER JOIN movie ON schedule.movie_id = movie.movie_id INNER JOIN mall ON schedule.mall_id = mall.mall_id WHERE user_id = 'admin';

This method was supposed to return a list of History objects, using the above SELECT SQL.

public List<History> getTransactionHistory(String currentUserId){
    History history;    
    List<History> historyList = new ArrayList<History>();

    sql = "SELECT LPAD(transaction_id, 5, 0), transaction_date, movie_title, schedule_price, "
        + "mall_name, schedule_cinema, schedule_date, schedule_time FROM transaction "
        + "INNER JOIN schedule ON transaction.schedule_id = schedule.schedule_id "
        + "INNER JOIN movie ON schedule.movie_id = movie.movie_id "
        + "INNER JOIN mall ON schedule.mall_id = mall.mall_id "
        + "WHERE user_id = ?";

    try {       
        ps = conn.prepareStatement(sql);
        ps.setString(1, currentUserId);

        rs = ps.executeQuery();

        while(rs.next()) {
            history = HistoryAssembler.getInstance(
                rs.getString("transaction_id"),
                rs.getDate("schedule_date"),
                rs.getString("movie_title"),
                rs.getBigDecimal("schedule_price"),
                rs.getString("mall_name"),
                rs.getInt("schedule_cinema"),
                rs.getDate("schedule_date"),
                rs.getTime("schedule_time")
            );

            System.out.println(history.getTransactionId());

            historyList.add(history);
        }

    } catch(SQLException se) {
        se.printStackTrace();
    }

    return historyList; 
}

History (Bean):

public class History {

    private String transactionId;   
    private Date transactionDate;
    private String movieTitle;
    private BigDecimal schedulePrice;
    private String mallName;
    private Integer scheduleCinema;
    private Date scheduleDate;
    private Time scheduleTime;

    // getters and setters
}

HistoryAssembler:

public static History getInstance(String transactionId, Date transactionDate, String movieTitle, BigDecimal schedulePrice,
    String mallName, Integer scheduleCinema, Date scheduleDate, Time scheduleTime) {

    History history = new History();

    history.setTransactionId(transactionId);
    history.setTransactionDate(transactionDate);
    history.setMovieTitle(movieTitle);
    history.setSchedulePrice(schedulePrice);
    history.setMallName(mallName);
    history.setScheduleCinema(scheduleCinema);
    history.setScheduleDate(scheduleDate);
    history.setScheduleTime(scheduleTime);

    return history;
}

However, I am getting a java.sql.SQLException: Column 'transaction_id' not found, when I do have the said column.
From what I understand, LPAD() should return a String, so that is why I set the transactionId in the bean as such.
Your assistance is much appreciated.

Answer 1

After the "LPAD" function the column name is changed and "XAMPP" fixed for you. You must put alias in this query after "LPAD(transaction_id, 5, 0) as transaction_id".

SELECT LPAD(transaction_id, 5, 0) as transaction_id
, transaction_date
, movie_title
, schedule_price
, mall_name
, schedule_cinema
, schedule_date
, schedule_time 
FROM transaction INNER JOIN schedule ON transaction.schedule_id = schedule.schedule_id
INNER JOIN movie ON schedule.movie_id = movie.movie_id
INNER JOIN mall ON schedule.mall_id = mall.mall_id
WHERE user_id = 'admin';