// Возвращает список всех ресторанов, выбранных из БД и удовлетворяющих поисковому запросу 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; }