CODEWITHSUNDEEP
javamysqlresultset
甄士隐
甄士隐

Reputation: 31

Java ResultSet get nothing but sql get result in mysql

I am using MySql database which has one table 'tradeinfo'. Table structure:

    Date          TradeCode 
    2017.01.01    0001
    2017.02.05    0002
    2017.03.05    0001

My sql to find lastest trading day of the one tradecode is 

    SELECT TradeCode, MAX(date)  most_recent_time   FROM tradeinfo  WHERE  TradeCode = '0001'

I test the sql in Mysql db and can get right result which is "2017.03.05 0001" But for my java code which is "lastestdbrecordsdate = rs.getDate("MOST_RECENT_TIME"); ", It ever return right result. But few days later, when run it again, I always get NULL. My java code is:

    Connection con = DriverManager.getConnection("jdbc:mysql://...",user,password);
    String sqlstatement = "SELECT TradeCode, MAX(date)  most_recent_time    FROM tradeinfo  WHERE  TradeCode = '0001'  ";
    PreparedStatement sqlstat = con.prepareStatement(sqlstatement);
    ResultSet rsquery = sqlstat.executeQuery(sqlstatement); 
    CachedRowSetImpl cachedRS = new CachedRowSetImpl();
    cachedRS.populate(rsquery);
    while(cachedRS.next() ) {
        System.out.println(cachedRS.getMetaData().getColumnCount());
        Date lastestdbrecordsdate = cachedRS.getDate("MOST_RECENT_TIME");
    }

Is the problem that I config the mysql wrongly or I write wrong java code? Thanks all!

Upvotes: 0

Views: 253

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You have several problems here. First, you should be using the following query:

SELECT MAX(date) most_recent_time FROM tradeinfo WHERE TradeCode = '0001'

Adding TradeCode to the select list doesn't make any sense because it is not an aggregate, but rather each record has a value for this column.

With regard to why you are getting null results, you need to call ResultSet#next() to advance the cursor to the first line:

Connection con = DriverManager.getConnection("jdbc:mysql://...", user, password);
Statement sqlstat = con.prepareStatement(sqlstatement);
ResultSet rsquery = sqlstat.executeQuery();    // DON'T pass anything to executeQuery()
if (rsquery.next()) {
    Date lastestdbrecordsdate = rs.getDate("most_recent_time");
}

Another problem I just noticed is that you were passing in the query string to your call to Statement#executeQuery(). This is wrong, and you should not be passing anything to this method.

Upvotes: 2

Related Questions