@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;
}
