@Override public Set<EmpVO> getEmpsByDeptno(Integer deptno) { Set<EmpVO> set = new LinkedHashSet<EmpVO>(); EmpVO empVO = null; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName(driver); con = DriverManager.getConnection(url, userid, passwd); pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT); pstmt.setInt(1, deptno); rs = pstmt.executeQuery(); while (rs.next()) { empVO = new EmpVO(); empVO.setEmpno(rs.getInt("empno")); empVO.setEname(rs.getString("ename")); empVO.setJob(rs.getString("job")); empVO.setHiredate(rs.getDate("hiredate")); empVO.setSal(rs.getDouble("sal")); empVO.setComm(rs.getDouble("comm")); empVO.setDeptno(rs.getInt("deptno")); set.add(empVO); // Store the row in the vector } // Handle any driver errors } catch (ClassNotFoundException e) { throw new RuntimeException("Couldn't load database driver. " + e.getMessage()); // Handle any SQL errors } catch (SQLException se) { throw new RuntimeException("A database error occured. " + se.getMessage()); } finally { if (rs != null) { try { rs.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (con != null) { try { con.close(); } catch (Exception e) { e.printStackTrace(System.err); } } } return set; }
/** * Test importing an unsupported field e.g. datetime, the program should continue and assign null * value to the field. * * @throws Exception */ @Test public void testImportUnsupportedField() throws Exception { ConnectionProperties p = getConnectionProperties(); Connection cn = DatabaseConnection.getConnection(p); try { List<Field> verifiedFields = new Vector<Field>(); String[] fields = "ListingId, Title".split(","); String tableName = "Listings"; DatabaseConnection.verifyTable(p, tableName, fields, verifiedFields); Collection<JsonNode> importNodes = new LinkedList<JsonNode>(); JsonNodeFactory f = JsonNodeFactory.instance; int listingId = 1559350; ObjectNode n; n = new ObjectNode(f); n.put("ListingId", listingId); importNodes.add(n); JsonArrayImporter importer = new JsonArrayImporter(p); importer.doImport(tableName, verifiedFields, importNodes); Statement st = cn.createStatement(); ResultSet rs = st.executeQuery("SELECT ListingId, Title FROM Listings"); assertTrue("Expected result set to contain a record", rs.next()); assertEquals(listingId, rs.getInt("ListingId")); assertEquals(null, rs.getString("Title")); } finally { cn.close(); } }
SqlDialect get(DataSource dataSource) { Connection connection = null; try { connection = dataSource.getConnection(); DatabaseMetaData metaData = connection.getMetaData(); String productName = metaData.getDatabaseProductName(); String productVersion = metaData.getDatabaseProductVersion(); List key = Arrays.asList(productName, productVersion); SqlDialect dialect = map.get(key); if (dialect == null) { final SqlDialect.DatabaseProduct product = SqlDialect.getProduct(productName, productVersion); dialect = new SqlDialect(product, productName, metaData.getIdentifierQuoteString()); map.put(key, dialect); } connection.close(); connection = null; return dialect; } catch (SQLException e) { throw new RuntimeException(e); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { // ignore } } } }
public void closeConnections() throws SQLException { if (itemPreparedStatement != null) { itemPreparedStatement.close(); } if (itemConnection != null) { itemConnection.close(); } if (holdingsPreparedStatement != null) { holdingsPreparedStatement.close(); } if (holdingsConnection != null) { holdingsConnection.close(); } if (bibResultSet != null) { bibResultSet.close(); } if (bibStatement != null) { bibStatement.close(); } if (bibConnection != null) { bibConnection.close(); } if (connection != null) { connection.close(); } }
/** * Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the * student's directory which relates to the course. Caution: vizualisation file will be deleted * eventhough it also relates to another course if the student is also registered to that course. * (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course, * error occured during deletion, or other exception occured. * * @param username student's user name * @param courseID course id (course number + instructor name) * @throws InvalidDBRequestException */ public void deleteStudent(String username, String courseID) throws InvalidDBRequestException { try { Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; int count = 0; // check if student registered to the course rs = stmt.executeQuery( "select * from courseRoster where course_id = '" + courseID + "' and user_login = '******'"); if (!rs.next()) throw new InvalidDBRequestException("Student is not registered to the course"); // remove student from the course count = stmt.executeUpdate( "delete from courseRoster where course_id = '" + courseID + "' and user_login = '******'"); if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!"); // delete the quiz visualization files rs = stmt.executeQuery( "select distinct unique_id, s.test_name from scores s, courseTest t " + "where s.test_name = t.test_name " + "and course_id = '" + courseID + "' " + "and user_login = '******'"); while (rs.next()) { deleteVisualization(rs.getString(1), username, rs.getString(2)); count = stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim()); } rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in addstudent: " + e.getMessage()); throw new InvalidDBRequestException("???"); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } }
@Override public void delete(Integer deptno) { int updateCount_EMPs = 0; Connection con = null; PreparedStatement pstmt = null; try { Class.forName(driver); con = DriverManager.getConnection(url, userid, passwd); // 1●設定於 pstm.executeUpdate()之前 con.setAutoCommit(false); // 先刪除員工 pstmt = con.prepareStatement(DELETE_EMPs); pstmt.setInt(1, deptno); updateCount_EMPs = pstmt.executeUpdate(); // 再刪除部門 pstmt = con.prepareStatement(DELETE_DEPT); pstmt.setInt(1, deptno); pstmt.executeUpdate(); // 2●設定於 pstm.executeUpdate()之後 con.commit(); con.setAutoCommit(true); System.out.println("刪除部門編號" + deptno + "時,共有員工" + updateCount_EMPs + "人同時被刪除"); // Handle any driver errors } catch (ClassNotFoundException e) { throw new RuntimeException("Couldn't load database driver. " + e.getMessage()); // Handle any SQL errors } catch (SQLException se) { if (con != null) { try { // 3●設定於當有exception發生時之catch區塊內 con.rollback(); } catch (SQLException excep) { throw new RuntimeException("rollback error occured. " + excep.getMessage()); } } throw new RuntimeException("A database error occured. " + se.getMessage()); } finally { if (pstmt != null) { try { pstmt.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (con != null) { try { con.close(); } catch (Exception e) { e.printStackTrace(System.err); } } } }
public void listerArticles() { Connection con = null; Statement st = null; ResultSet rs = null; try { Class.forName(driver).newInstance(); con = DriverManager.getConnection(url, usr, passwd); st = con.createStatement(); rs = st.executeQuery("SELECT id, nom, quantite FROM articles"); // remise à 0 de la liste - utile pour les mises à jour list.clear(); // Stocker les enregistrements dans la liste while (rs.next()) { int id = rs.getInt(1); String nom = new String(rs.getString(2)); int quantite = rs.getInt(3); list.add(new ElementBDD(nom, quantite)); // ajout System.out.println(nom + " " + quantite); } } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (rs != null) rs.close(); if (st != null) st.close(); if (con != null) con.close(); } catch (SQLException e) { } } }
void update_war(int db_id, long duration, long remaining, long time_to_start, int current_chain) { Connection con = null; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement( "UPDATE `wars` SET `duration` = ? ,`remaining` = ?, `time_to_start` = ?, `current_chain` = ? WHERE id = ?"); s.setLong(1, duration); s.setLong(2, remaining); s.setLong(3, time_to_start); s.setInt(4, current_chain); s.setInt(5, db_id); s.executeUpdate(); s.close(); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
void joinChannel(Channel channel) { Connection con = null; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement("INSERT INTO `channels` (`channel`) VALUES (?)"); s.setString(1, channel.getName().toLowerCase()); s.executeUpdate(); s.close(); if (!this.channel_data.containsKey(channel.getName().toLowerCase())) { ChannelInfo new_channel = new ChannelInfo(channel.getName().toLowerCase()); new_channel.setDefaultOptions(); this.channel_data.put(channel.getName().toLowerCase(), new_channel); } this.saveChannelSettings(this.channel_data.get(channel.getName().toLowerCase())); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
private void getIgnoreList() { Connection con = null; try { con = pool.getConnection(timeout); Statement s = con.createStatement(); s.executeQuery("SELECT `name`, `type` FROM `ignores`"); ResultSet rs = s.getResultSet(); this.ignore_list.clear(); this.soft_ignore_list.clear(); while (rs.next()) { if (rs.getString("type").equals("hard")) { this.ignore_list.add(rs.getString("name").toLowerCase()); } else { this.soft_ignore_list.add(rs.getString("name").toLowerCase()); } } rs.close(); s.close(); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
public void addToChannelGroup(String group, ChannelInfo channel) { Connection con = null; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement( "REPLACE INTO `channel_groups` SET `name` = ?, `channel_id` = (SELECT `id` FROM `channels` WHERE `channel` = ?)"); s.setString(1, group.toLowerCase()); s.setString(2, channel.channel.toLowerCase()); s.executeUpdate(); s.close(); // Will do nothing if the channel is not in the list. if (!this.channel_groups.containsKey(group.toLowerCase())) { this.channel_groups.put(group.toLowerCase(), new HashSet<>()); } this.channel_groups.get(group.toLowerCase()).add(channel); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
public void closeConnection(Connection connection) { try { connection.close(); } catch (SQLException e) { LOG.error("Can't close JDBC connection to Cassandra", e); } }
/** * Check the user's name and password and verify that the user is an instructor. Throws * InvalidDBRequestException if user is not an instructor, wrong password, or if any error occured * to the database connection. * * @param name user's user name * @param pass user's password * @throws InvalidDBRequestException */ public void instructorLogin(String name, String pass) throws InvalidDBRequestException { try { Connection db; Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; // check if instructor rs = stmt.executeQuery("select password from instructor where login = '******'"); if (!rs.next()) { if (debug) System.out.println("User not found in the instructor table"); throw new InvalidDBRequestException("Instructor not registered"); } // check password if (!rs.getString(1).equals(pass)) { if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"Invalid SQL in instructor login: "******"???"); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Server Error"); } }
public ArrayList<VentasBean> obtenerMensajes() { Connection cn = null; ArrayList<VentasBean> mensaje = null; Statement st; ResultSet rs; try { cn = getConnection(); st = cn.createStatement(); String tsql; tsql = "select * from ventas"; rs = st.executeQuery(tsql); mensaje = new ArrayList<VentasBean>(); while (rs.next()) { VentasBean m = new VentasBean( rs.getInt("id_venta"), rs.getInt("id_linea"), rs.getString("fecha_venta"), rs.getString("descripcion")); mensaje.add(m); } cn.close(); } catch (Exception e) { e.printStackTrace(); } return (mensaje); }
/** * Test batched prepared statement concurrency. Batch prepares must not disappear between the * moment when they were created and when they are executed. */ public void testConcurrentBatching() throws Exception { // Create a connection with a batch size of 1. This should cause prepares and actual batch // execution to become // interspersed (if correct synchronization is not in place) and greatly increase the chance of // prepares // being rolled back before getting executed. Properties props = new Properties(); props.setProperty(Messages.get(net.sourceforge.jtds.jdbc.Driver.BATCHSIZE), "1"); props.setProperty( Messages.get(net.sourceforge.jtds.jdbc.Driver.PREPARESQL), String.valueOf(TdsCore.TEMPORARY_STORED_PROCEDURES)); Connection con = getConnection(props); try { Statement stmt = con.createStatement(); stmt.execute( "create table #testConcurrentBatch (v1 int, v2 int, v3 int, v4 int, v5 int, v6 int)"); stmt.close(); Vector exceptions = new Vector(); con.setAutoCommit(false); Thread t1 = new ConcurrentBatchingHelper(con, exceptions); Thread t2 = new ConcurrentBatchingHelper(con, exceptions); t1.start(); t2.start(); t1.join(); t2.join(); assertEquals(0, exceptions.size()); } finally { con.close(); } }
// metoda za pretragu po broju stanovnika public ArrayList<Country> SearchCountryPopulation(long Population) { ArrayList<Country> countries = new ArrayList<Country>(); try { Connection connection = getConnected("world"); PreparedStatement statement = connection.prepareStatement( "SELECT * FROM country WHERE Population <= " + Population + ";"); ResultSet result = statement.executeQuery(); while (result.next()) { countries.add( new Country( result.getString("Code"), result.getString("Name"), result.getString("Continent"), result.getString("Region"), result.getDouble("SurfaceArea"), result.getInt("IndepYear"), result.getLong("Population"), result.getDouble("LifeExpectancy"), result.getDouble("GNP"), result.getDouble("GNPOld"), result.getString("LocalName"), result.getString("GovernmentForm"), result.getString("HeadOfState"), result.getInt("Capital"), result.getString("Code2"))); } connection.close(); } catch (Exception e) { System.out.println(e.toString()); return null; } return countries; }
/** * Returns the messages which ID starts with the given ID * * @param ID message ID * @return text and ID of the message */ public synchronized Hashtable getMessagesWithId(String ID) { // System.out.println("getMessagesWithId:"+ID); Hashtable res = new Hashtable(); Enumeration enm = dict.keys(); while (enm.hasMoreElements()) { String key = "" + enm.nextElement(); if (key.startsWith(ID)) res.put(key, dict.get(key)); } if (res.size() == 0) { Connection connection = null; try { connection = getMessageDBConnection(); PreparedStatement proc = connection.prepareStatement("SELECT tkey,txt FROM messages_texts WHERE tkey like ?"); proc.setString(1, ID + "%"); ResultSet rs = proc.executeQuery(); while (rs.next()) { res.put(rs.getString(1), rs.getString(2)); } connection.close(); } catch (Exception e) { /*not message connection*/ e.printStackTrace(); } } return res; }
public void mettreAjourQuantite(String nomArticle, int value) { Connection con = null; Statement st = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); con = DriverManager.getConnection("jdbc:mysql://192.168.2.5/inventaire", "guest", "guest"); st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); rs = st.executeQuery("SELECT id, nom, quantite FROM articles"); // modifier la quantité pour l'article nomArticle while (rs.next()) { int id = rs.getInt(1); String nom = rs.getString(2); int quantite = rs.getInt(3); if (nom.equals(nomArticle)) { if ((quantite == 0) && (value == -1)) { break; } rs.updateInt(3, (quantite + value)); rs.updateRow(); break; } } } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (rs != null) rs.close(); if (st != null) st.close(); if (con != null) con.close(); } catch (SQLException e) { } } }
public int update(String tableName, String colName, Object newValue, Object oldValue) throws SQLException { // syntax: // update [table] set [column] = [new value] // where [column] = [old value] int updated = 0; boolean hasWhere = false; if ((oldValue != null) && !(oldValue.equals(""))) { hasWhere = true; } PreparedStatement pstmt = pstmtBuilder.buildUpdateStatement(conn, tableName, colName, hasWhere); try { pstmt.setObject(1, newValue); if (hasWhere) { pstmt.setObject(2, oldValue); } updated = pstmt.executeUpdate(); } catch (SQLException sqle) { System.out.println(sqle); } catch (Exception e) { System.out.println(e); } finally { try { pstmt.close(); conn.close(); } catch (Exception e) { System.out.println(e); } } return updated; }
/** * Deletes students who are older than a certain number of years and not registered to any course. * * @param year students older than year are candidates to be deleted * @throws InvalidDBRequestException */ public void deleteOldStudent(int year) throws InvalidDBRequestException { try { Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); // query all student who have been in the database longer than a number of years and not // registered to any course ResultSet rs = stmt.executeQuery( "select login, count(course_id) " + "from student s left join courseRoster r on login = user_login " + "where date_entered < SUBDATE(now(), INTERVAL " + new String().valueOf(year).trim() + " YEAR) " + "group by login, date_entered"); // delete them while (rs.next()) if (rs.getInt(2) == 0) purgeStudent(rs.getString(1).trim()); rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in addCourse: " + e.getMessage()); throw new InvalidDBRequestException("??? "); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } }
public void removeFromChannelGroup(String group, ChannelInfo channel) { Connection con = null; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement( "DELETE `channel_groups`.* FROM `channel_groups` INNER JOIN `channels` ON (`channel_groups`.`channel_id` = `channels`.`id`) WHERE `channels`.`channel` = ? AND `channel_groups`.`name` = ?"); s.setString(1, channel.channel); s.setString(2, group); s.executeUpdate(); s.close(); // Will do nothing if the channel is not in the list. this.channel_groups.get(group.toLowerCase()).remove(channel); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
/** * Delete a student from the database. Also deletes the student's folders. Throws * InvalidDBRequestException if any error in database connection. * * @param username student's user name * @throws InvalidDBRequestException */ private void purgeStudent(String username) throws InvalidDBRequestException { try { Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); int count; // delete from scores count = stmt.executeUpdate("delete from scores where user_login = '******'"); // delete from student count = stmt.executeUpdate("delete from student where login = '******'"); // delete student's folder File studentDir = new File("./StudentQuizzes/" + username); if (!(studentDir.delete())) { System.err.println("Error in deleting folder for student: " + username); } stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in addCourse: " + e.getMessage()); throw new InvalidDBRequestException("??? "); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } }
private void getPokemon() { Connection con = null; try { con = pool.getConnection(timeout); try (Statement s = con.createStatement()) { ResultSet rs; s.executeQuery("SELECT `name` FROM `pokemon`"); rs = s.getResultSet(); this.pokemon.clear(); while (rs.next()) { this.pokemon.add(rs.getString("name")); } rs.close(); s.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
/** * Deletes an instructor from the database. Deletes the instructor's courses by invoking the * deleteCourse method. Throws InvalidDBRequestException if instructor not in the database or * other database connection problems. * * @see deleteCourse * @param instructor instructor's user name * @throws InvalidDBRequestException */ public void deleteInstructor(String instructor) throws InvalidDBRequestException { try { Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); int count; // delete the instructor's courses ResultSet rs = stmt.executeQuery( "select course_num from course where instructor = '" + instructor + "'"); while (rs.next()) deleteCourse(rs.getString(1).trim(), instructor); // delete the instructor's record count = stmt.executeUpdate("delete from instructor where login ='******'"); rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in addCourse: " + e.getMessage()); throw new InvalidDBRequestException("??? "); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } }
String getSetting(String key) { Connection con = null; String value = ""; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement("SELECT `value` FROM `settings` WHERE `key` = ?"); s.setString(1, key); s.executeQuery(); ResultSet rs = s.getResultSet(); while (rs.next()) { value = rs.getString("value"); } rs.close(); s.close(); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } return value; }
/** * Gets a list of courses that belongs to an instructor. Throws InvalidDBRequestException if any * error occured to the database connection. * * @param name the instructor's user name * @return a vector containing the list of courses * @throws InvalidDBRequestException */ public Vector getCourseList(String name) throws InvalidDBRequestException { Vector courseList = new Vector(); try { Connection db; Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; // get the course list rs = stmt.executeQuery( "select course_num, course_name from course where instructor = '" + name + "' order by course_num"); while (rs.next()) courseList.add(rs.getString(1) + " - " + rs.getString(2)); rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in getCourseList: " + e.getMessage()); throw new InvalidDBRequestException("???"); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Server Error"); } return courseList; }
String getRandomChannelWithVelociraptors(String exclude) { Connection con = null; String value = ""; try { con = pool.getConnection(timeout); PreparedStatement s = con.prepareStatement( "SELECT `channel` FROM `channels` WHERE `channel` != ? AND `active_velociraptors` > 0 ORDER BY (RAND() * active_velociraptors) DESC LIMIT 1;"); s.setString(1, exclude); s.executeQuery(); ResultSet rs = s.getResultSet(); while (rs.next()) { value = rs.getString("channel"); } rs.close(); s.close(); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } return value; }
public Connection getConnection() throws SQLException { synchronized (pool) { if (!pool.isEmpty()) { int last = pool.size() - 1; Connection pooled = (Connection) pool.remove(last); boolean conn_ok = true; String test_table = prop.getProperty("test_table"); if (test_table != null) { Statement stmt = null; try { stmt = pooled.createStatement(); stmt.executeQuery("select * from " + prop.getProperty("test_table")); } catch (SQLException ex) { conn_ok = false; // 连接不可用 } finally { if (stmt != null) { stmt.close(); } } } if (conn_ok == true) { return pooled; } else { pooled.close(); } } } Connection conn = DriverManager.getConnection( prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password")); return conn; }
/** * Establishes a connection to a registry. * * @param queryUrl the URL of the query registry * @param publishUrl the URL of the publish registry */ public void makeConnection(String queryUrl, String publishUrl) { Context context = null; ConnectionFactory factory = null; /* * Define connection configuration properties. * To publish, you need both the query URL and the * publish URL. */ Properties props = new Properties(); props.setProperty("javax.xml.registry.queryManagerURL", queryUrl); props.setProperty("javax.xml.registry.lifeCycleManagerURL", publishUrl); try { // Create the connection, passing it the // configuration properties context = new InitialContext(); factory = (ConnectionFactory) context.lookup("java:comp/env/eis/JAXR"); factory.setProperties(props); connection = factory.createConnection(); System.out.println("Created connection to registry"); } catch (Exception e) { e.printStackTrace(); if (connection != null) { try { connection.close(); } catch (JAXRException je) { } } } }
/** * Get waiting User name; * * @return An waiter ArrayList */ public static ArrayList<String> getWaiter() throws SQLException { ArrayList<String> waiterArray = new ArrayList<String>(); Connection conn = DBConn.GetConnection(); PreparedStatement st = conn.prepareStatement("SELECT * FROM cs_conversion where isstart=?"); st.setInt(1, 1); ResultSet rs = st.executeQuery(); while (rs.next()) { if (rs.getString("username").length() != 0) { if (waiterArray.size() == 0) { waiterArray.add(rs.getString("username")); } else { for (int i = 0; i < waiterArray.size(); i++) { if (!rs.getString("username").equals(waiterArray.get(i).toString())) { waiterArray.add(rs.getString("username")); } } } } } rs.close(); st.close(); conn.close(); return waiterArray; }