tky today: データベースを使う(4)

2014年8月9日土曜日

データを取り出すのは簡単だ。SQLのselectを使えばいいだけ。下のような形で、馬名で指定する馬の一定期間内のデータを取得することが出来る。

 public List<HorseData> select(String horsename, Date mindate, Date maxdate) {

  try {

   PreparedStatement ps = conn.prepareStatement("select * from Horse where name = ? and date < ? and date >= ? order by date DESC");

   ps.setString(1, horsename);

   ps.setDate(2, new java.sql.Date(maxdate.getTime()));

   ps.setDate(3, new java.sql.Date(mindate.getTime()));

   ResultSet rs = ps.executeQuery();

   return readResultSet(rs);

  } catch (SQLException se) {

   for (SQLException e = se; e != null; e = e.getNextException()) {

    TLog.e("%s: %s%n", e.getSQLState(), e.getMessage());

}

}

  return null;

}

 private List<HorseData> readResultSet(ResultSet rs) {

  List<HorseData> list = new ArrayList<HorseData>();

  try {

   while (rs.next()) {

    HorseData hd = new HorseData();

    hd.name = rs.getString("name");

    hd.term = rs.getInt("term");

    hd.place = rs.getInt("place");

    hd.day = rs.getInt("day");

    hd.rnum = rs.getInt("rnum");

    hd.date = new Date(rs.getDate("date").getTime());

    hd.frame = rs.getInt("frame");

    hd.number = rs.getInt("number");

    hd.order = rs.getInt("ordernumber");

    hd.time = rs.getInt("time");

    hd.popularity = rs.getInt("popularity");

    hd.weight = rs.getInt("weight");

    hd.loadweight = rs.getFloat("loadweight");

    hd.threefurlong = rs.getFloat("threefurlong");

    hd.odds = rs.getFloat("odds");

    hd.setPassorder(rs.getString("passorder"));

    list.add(hd);

}

  } catch (SQLException se) {

   for (SQLException e = se; e != null; e = e.getNextException()) {

    TLog.e("%s: %s%n", e.getSQLState(), e.getMessage());

}

}

  return list;

}

tky today