// Возвращает список всех ресторанов, выбранных из БД и удовлетворяющих поисковому запросу
public static ArrayList<Restaurant> getAllRestaurantsBySearch(String searchQuery) {
ArrayList<Restaurant> list = new ArrayList<Restaurant>();
try {
Connection conn = getDBConnection();
PreparedStatement stmt =
conn.prepareStatement(
"SELECT id, name, cuisine_rating, interior_rating, service_rating FROM restaurants WHERE name LIKE ? OR review LIKE ?");
stmt.setString(1, "%" + searchQuery + "%");
stmt.setString(2, "%" + searchQuery + "%");
ResultSet rslt = stmt.executeQuery();
while (rslt.next()) {
list.add(
new Restaurant(
rslt.getInt("id"),
rslt.getString("name"),
rslt.getByte("cuisine_rating"),
rslt.getByte("interior_rating"),
rslt.getByte("service_rating")));
}
conn.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
// e.printStackTrace();
// System.exit(0);
}
return list;
}
// Возвращает один заданный ресторан, выбранный из БД
public static Restaurant getRestaurant(int id) {
Restaurant selectedRest = null;
try {
Connection conn = getDBConnection();
PreparedStatement stmt =
conn.prepareStatement(
"SELECT id, name, review, cuisine_rating, interior_rating, service_rating FROM restaurants WHERE id = ?");
stmt.setInt(1, id);
ResultSet rslt = stmt.executeQuery();
// Изначально курсор расположен непосредственно перед первой строкой объекта ResultSet.
// Первый вызов метода next() устанавливает курсор на первой строке и делает ее текущей.
rslt.next();
selectedRest =
new Restaurant(
rslt.getInt("id"),
rslt.getString("name"),
rslt.getString("review"),
rslt.getByte("cuisine_rating"),
rslt.getByte("interior_rating"),
rslt.getByte("service_rating"));
conn.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
// e.printStackTrace();
// System.exit(0);
}
return selectedRest;
}
// Возвращает список всех ресторанов, выбранных из БД. Параметр метода задает способ сортировки
// результата.
public static ArrayList<Restaurant> getAllRestaurants(String sort) {
ArrayList<Restaurant> list = new ArrayList<Restaurant>();
try {
Connection conn = getDBConnection();
Statement stmt = conn.createStatement();
ResultSet rslt = null;
switch (sort) {
case "total_rating":
rslt =
stmt.executeQuery(
"SELECT id, name, cuisine_rating, interior_rating, service_rating FROM restaurants ORDER BY (cuisine_rating * 0.4 + interior_rating * 0.3 + service_rating * 0.3) DESC");
break;
case "cuisine_rating":
rslt =
stmt.executeQuery(
"SELECT id, name, cuisine_rating, interior_rating, service_rating FROM restaurants ORDER BY cuisine_rating DESC");
break;
case "interior_rating":
rslt =
stmt.executeQuery(
"SELECT id, name, cuisine_rating, interior_rating, service_rating FROM restaurants ORDER BY interior_rating DESC");
break;
case "service_rating":
rslt =
stmt.executeQuery(
"SELECT id, name, cuisine_rating, interior_rating, service_rating FROM restaurants ORDER BY service_rating DESC");
break;
}
while (rslt.next()) {
list.add(
new Restaurant(
rslt.getInt("id"),
rslt.getString("name"),
rslt.getByte("cuisine_rating"),
rslt.getByte("interior_rating"),
rslt.getByte("service_rating")));
}
conn.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
// e.printStackTrace();
// System.exit(0);
}
return list;
}
@Ignore
@Test
public void getObject() throws SQLException {
stat.executeUpdate(
"create table testobj (" + "c1 integer, c2 float, c3, c4 varchar, c5 bit, c6, c7);");
PreparedStatement prep = conn.prepareStatement("insert into testobj values (?,?,?,?,?,?,?);");
prep.setInt(1, Integer.MAX_VALUE);
prep.setFloat(2, Float.MAX_VALUE);
prep.setDouble(3, Double.MAX_VALUE);
prep.setLong(4, Long.MAX_VALUE);
prep.setBoolean(5, false);
prep.setByte(6, (byte) 7);
prep.setBytes(7, b1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1,c2,c3,c4,c5,c6,c7 from testobj;");
assertTrue(rs.next());
assertEquals(Integer.MAX_VALUE, rs.getInt(1));
assertEquals(Integer.MAX_VALUE, (int) rs.getLong(1));
assertEquals(Float.MAX_VALUE, rs.getFloat(2));
assertEquals(Double.MAX_VALUE, rs.getDouble(3));
assertEquals(Long.MAX_VALUE, rs.getLong(4));
assertFalse(rs.getBoolean(5));
assertEquals((byte) 7, rs.getByte(6));
assertArrayEq(rs.getBytes(7), b1);
assertNotNull(rs.getObject(1));
assertNotNull(rs.getObject(2));
assertNotNull(rs.getObject(3));
assertNotNull(rs.getObject(4));
assertNotNull(rs.getObject(5));
assertNotNull(rs.getObject(6));
assertNotNull(rs.getObject(7));
assertTrue(rs.getObject(1) instanceof Integer);
assertTrue(rs.getObject(2) instanceof Double);
assertTrue(rs.getObject(3) instanceof Double);
assertTrue(rs.getObject(4) instanceof String);
assertTrue(rs.getObject(5) instanceof Integer);
assertTrue(rs.getObject(6) instanceof Integer);
assertTrue(rs.getObject(7) instanceof byte[]);
rs.close();
}
public ArrayList<DLNAMediaInfo> getData(String name, long modified) {
ArrayList<DLNAMediaInfo> list = new ArrayList<>();
Connection conn = null;
ResultSet rs = null;
PreparedStatement stmt = null;
try {
conn = getConnection();
stmt = conn.prepareStatement("SELECT * FROM FILES WHERE FILENAME = ? AND MODIFIED = ?");
stmt.setString(1, name);
stmt.setTimestamp(2, new Timestamp(modified));
rs = stmt.executeQuery();
while (rs.next()) {
DLNAMediaInfo media = new DLNAMediaInfo();
int id = rs.getInt("ID");
media.setDuration(toDouble(rs, "DURATION"));
media.setBitrate(rs.getInt("BITRATE"));
media.setWidth(rs.getInt("WIDTH"));
media.setHeight(rs.getInt("HEIGHT"));
media.setSize(rs.getLong("SIZE"));
media.setCodecV(rs.getString("CODECV"));
media.setFrameRate(rs.getString("FRAMERATE"));
media.setAspectRatioDvdIso(rs.getString("ASPECT"));
media.setAspectRatioContainer(rs.getString("ASPECTRATIOCONTAINER"));
media.setAspectRatioVideoTrack(rs.getString("ASPECTRATIOVIDEOTRACK"));
media.setReferenceFrameCount(rs.getByte("REFRAMES"));
media.setAvcLevel(rs.getString("AVCLEVEL"));
media.setBitsPerPixel(rs.getInt("BITSPERPIXEL"));
media.setThumb(rs.getBytes("THUMB"));
media.setContainer(rs.getString("CONTAINER"));
media.setModel(rs.getString("MODEL"));
if (media.getModel() != null && !FormatConfiguration.JPG.equals(media.getContainer())) {
media.setExtrasAsString(media.getModel());
}
media.setExposure(rs.getInt("EXPOSURE"));
media.setOrientation(rs.getInt("ORIENTATION"));
media.setIso(rs.getInt("ISO"));
media.setMuxingMode(rs.getString("MUXINGMODE"));
media.setFrameRateMode(rs.getString("FRAMERATEMODE"));
media.setStereoscopy(rs.getString("STEREOSCOPY"));
media.setMatrixCoefficients(rs.getString("MATRIXCOEFFICIENTS"));
media.setFileTitleFromMetadata(rs.getString("TITLECONTAINER"));
media.setVideoTrackTitleFromMetadata(rs.getString("TITLEVIDEOTRACK"));
media.setVideoTrackCount(rs.getInt("VIDEOTRACKCOUNT"));
media.setImageCount(rs.getInt("IMAGECOUNT"));
media.setVideoBitDepth(rs.getInt("BITDEPTH"));
media.setMediaparsed(true);
ResultSet subrs;
try (PreparedStatement audios =
conn.prepareStatement("SELECT * FROM AUDIOTRACKS WHERE FILEID = ?")) {
audios.setInt(1, id);
subrs = audios.executeQuery();
while (subrs.next()) {
DLNAMediaAudio audio = new DLNAMediaAudio();
audio.setId(subrs.getInt("ID"));
audio.setLang(subrs.getString("LANG"));
audio.setAudioTrackTitleFromMetadata(subrs.getString("TITLE"));
audio.getAudioProperties().setNumberOfChannels(subrs.getInt("NRAUDIOCHANNELS"));
audio.setSampleFrequency(subrs.getString("SAMPLEFREQ"));
audio.setCodecA(subrs.getString("CODECA"));
audio.setBitsperSample(subrs.getInt("BITSPERSAMPLE"));
audio.setAlbum(subrs.getString("ALBUM"));
audio.setArtist(subrs.getString("ARTIST"));
audio.setSongname(subrs.getString("SONGNAME"));
audio.setGenre(subrs.getString("GENRE"));
audio.setYear(subrs.getInt("YEAR"));
audio.setTrack(subrs.getInt("TRACK"));
audio.getAudioProperties().setAudioDelay(subrs.getInt("DELAY"));
audio.setMuxingModeAudio(subrs.getString("MUXINGMODE"));
audio.setBitRate(subrs.getInt("BITRATE"));
media.getAudioTracksList().add(audio);
}
subrs.close();
}
try (PreparedStatement subs =
conn.prepareStatement("SELECT * FROM SUBTRACKS WHERE FILEID = ?")) {
subs.setInt(1, id);
subrs = subs.executeQuery();
while (subrs.next()) {
DLNAMediaSubtitle sub = new DLNAMediaSubtitle();
sub.setId(subrs.getInt("ID"));
sub.setLang(subrs.getString("LANG"));
sub.setSubtitlesTrackTitleFromMetadata(subrs.getString("TITLE"));
sub.setType(SubtitleType.valueOfStableIndex(subrs.getInt("TYPE")));
media.getSubtitleTracksList().add(sub);
}
subrs.close();
}
list.add(media);
}
} catch (SQLException se) {
LOGGER.error(null, se);
return null;
} finally {
close(rs);
close(stmt);
close(conn);
}
return list;
}
/**
* 获取游戏接口参数
* @param rs ResultSet
* @return
*/
protected static wh.game.model.GameInterfaceParams getModel(ResultSet rs) throws SQLException{
wh.game.model.GameInterfaceParams m = null;
if (rs != null)
{
m = new wh.game.model.GameInterfaceParams();
try{
m.setGameInterfaceParamsId(rs.getInt("GameInterfaceParamsId"));
wh.game.model.GameInterface gameInterface = new wh.game.model.GameInterface();
gameInterface.setGameInterfaceId(rs.getInt("GameInterface_GameInterfaceId"));
gameInterface.setGame(new wh.game.model.Game());
gameInterface.getGame().setGameId(rs.getInt("GameInterface_GameId"));
gameInterface.setName(rs.getString("GameInterface_Name"));
gameInterface.setUrl(rs.getString("GameInterface_Url"));
gameInterface.setInterfaceType(rs.getByte("GameInterface_InterfaceType"));
gameInterface.setPayUnitType(rs.getByte("GameInterface_PayUnitType"));
gameInterface.setCreateDate(rs.getTimestamp("GameInterface_CreateDate"));
gameInterface.setStatus(rs.getByte("GameInterface_Status"));
gameInterface.setSignJoinSymbol(rs.getString("GameInterface_SignJoinSymbol"));
m.setGameInterface(gameInterface);
wh.game.model.Game game = new wh.game.model.Game();
game.setGameId(rs.getInt("Game_GameId"));
game.setProvider(new wh.game.model.Provider());
game.getProvider().setProviderId(rs.getInt("Game_ProviderId"));
game.setManager(new wh.member.model.Manager());
game.getManager().setManagerId(rs.getInt("Game_ManagerId"));
game.setGameCategory(new wh.game.model.GameCategory());
game.getGameCategory().setGameCategoryId(rs.getInt("Game_GameCategoryId"));
game.setGameName(rs.getString("Game_GameName"));
game.setPosterPath(rs.getString("Game_PosterPath"));
game.setCreateDate(rs.getTimestamp("Game_CreateDate"));
game.setGameType(rs.getByte("Game_GameType"));
game.setEnableType(rs.getByte("Game_EnableType"));
game.setStatus(rs.getByte("Game_Status"));
game.setGameMoneyType(rs.getByte("Game_GameMoneyType"));
game.setGameMoneyRate(rs.getInt("Game_GameMoneyRate"));
game.setContent(rs.getString("Game_Content"));
game.setRecommendType(rs.getByte("Game_RecommendType"));
game.setHomeUrl(rs.getString("Game_HomeUrl"));
game.setCPosterPath(rs.getString("Game_CPosterPath"));
game.setSPosterPath(rs.getString("Game_SPosterPath"));
game.setLPosterPath(rs.getString("Game_LPosterPath"));
m.setGame(game);
m.setParamName(rs.getString("ParamName"));
m.setParamValue(rs.getString("ParamValue"));
m.setParamExplain(rs.getString("ParamExplain"));
m.setSignType(rs.getByte("SignType"));
m.setSignIndex(rs.getByte("SignIndex"));
m.setParamType(rs.getByte("ParamType"));
m.setParamInType(rs.getByte("ParamInType"));
m.setParamOutType(rs.getByte("ParamOutType"));
m.setSignFormatValue(rs.getString("SignFormatValue"));
}catch (SQLException e) {
throw new SQLException(e.getMessage(),e);
}
}
return m;
}
/**
* 获取激活码
* @param rs ResultSet
* @return
*/
protected static wh.game.model.ActiveCode getModel(ResultSet rs) throws SQLException{
wh.game.model.ActiveCode m = null;
if (rs != null)
{
m = new wh.game.model.ActiveCode();
try{
m.setActiveCodeId(rs.getInt("ActiveCodeId"));
wh.game.model.Game game = new wh.game.model.Game();
game.setGameId(rs.getInt("Game_GameId"));
game.setProvider(new wh.game.model.Provider());
game.getProvider().setProviderId(rs.getInt("Game_ProviderId"));
game.setManager(new wh.member.model.Manager());
game.getManager().setManagerId(rs.getInt("Game_ManagerId"));
game.setGameCategory(new wh.game.model.GameCategory());
game.getGameCategory().setGameCategoryId(rs.getInt("Game_GameCategoryId"));
game.setGameName(rs.getString("Game_GameName"));
game.setPosterPath(rs.getString("Game_PosterPath"));
game.setCreateDate(rs.getTimestamp("Game_CreateDate"));
game.setGameType(rs.getByte("Game_GameType"));
game.setEnableType(rs.getByte("Game_EnableType"));
game.setStatus(rs.getByte("Game_Status"));
game.setGameMoneyType(rs.getByte("Game_GameMoneyType"));
game.setGameMoneyRate(rs.getInt("Game_GameMoneyRate"));
game.setContent(rs.getString("Game_Content"));
game.setRecommendType(rs.getByte("Game_RecommendType"));
game.setHomeUrl(rs.getString("Game_HomeUrl"));
game.setCPosterPath(rs.getString("Game_CPosterPath"));
game.setSPosterPath(rs.getString("Game_SPosterPath"));
game.setLPosterPath(rs.getString("Game_LPosterPath"));
m.setGame(game);
wh.game.model.Server server = new wh.game.model.Server();
server.setServerId(rs.getInt("Server_ServerId"));
server.setGame(new wh.game.model.Game());
server.getGame().setGameId(rs.getInt("Server_GameId"));
server.setManager(new wh.member.model.Manager());
server.getManager().setManagerId(rs.getInt("Server_ManagerId"));
server.setProvider(new wh.game.model.Provider());
server.getProvider().setProviderId(rs.getInt("Server_ProviderId"));
server.setServerName(rs.getString("Server_ServerName"));
server.setServerCode(rs.getString("Server_ServerCode"));
server.setSequenceNumber(rs.getInt("Server_SequenceNumber"));
server.setServerIP(rs.getString("Server_ServerIP"));
server.setServerDomain(rs.getString("Server_ServerDomain"));
server.setServerPort(rs.getString("Server_ServerPort"));
server.setServerUrl(rs.getString("Server_ServerUrl"));
server.setOpenDate(rs.getTimestamp("Server_OpenDate"));
server.setCreateDate(rs.getTimestamp("Server_CreateDate"));
server.setLineType(rs.getByte("Server_LineType"));
server.setEnableType(rs.getByte("Server_EnableType"));
server.setRechargeType(rs.getByte("Server_RechargeType"));
server.setRecommendType(rs.getByte("Server_RecommendType"));
server.setEnableMessageType(rs.getByte("Server_EnableMessageType"));
server.setServerType(rs.getByte("Server_ServerType"));
server.setStatus(rs.getByte("Server_Status"));
m.setServer(server);
m.setName(rs.getString("Name"));
m.setCode(rs.getString("Code"));
m.setActiveCodeType(rs.getByte("ActiveCodeType"));
m.setCreateDate(rs.getTimestamp("CreateDate"));
m.setModifyDate(rs.getTimestamp("ModifyDate"));
m.setStatus(rs.getByte("Status"));
m.setUsedType(rs.getByte("UsedType"));
}catch (SQLException e) {
throw new SQLException(e.getMessage(),e);
}
}
return m;
}
