public boolean checkChartUpdateRequired(String doi, Connection con) throws SQLException {
PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY);
getUpdateStmt.setString(1, doi);
ResultSet rs1 = getUpdateStmt.executeQuery();
int lastNcites = 0;
if (rs1.first()) {
lastNcites = rs1.getInt("lastNcites");
} else {
rs1.close();
getUpdateStmt.close();
return true;
}
rs1.close();
getUpdateStmt.close();
PreparedStatement getNcitesStmt = con.prepareStatement(DEF_GET_NCITES_QUERY);
getNcitesStmt.setString(1, doi);
int ncites = 0;
ResultSet rs2 = getNcitesStmt.executeQuery();
if (rs2.first()) {
ncites = rs2.getInt("ncites");
}
rs2.close();
getNcitesStmt.close();
if (ncites != lastNcites) {
return true;
} else {
return false;
}
} // - checkChartUpdateRequired
public static void main(String[] args) throws Exception {
try (Connection conn = DBUtil.getConnection(DBType.HSQLDB);
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT stateId, stateName FROM states"); ) {
States.displayData(rs);
rs.last();
System.out.println("Number of rows: " + rs.getRow());
rs.first();
System.out.println("The first state is " + rs.getString("stateName"));
rs.last();
System.out.println("The last state is " + rs.getString("stateName"));
rs.absolute(10);
System.out.println("The 10th state is " + rs.getString("stateName"));
} catch (SQLException e) {
System.err.println(e);
}
}
public int[] getAd() {
int i = 0;
int j = 0;
int[] ans = null;
String str = "SELECT * FROM ad ORDER BY id";
try {
row = stm.executeQuery(str);
row.last();
i = row.getRow();
row.first();
ans = new int[i + 1];
if (i > 0) {
ans[0] = i;
do {
j++;
ans[j] = Integer.parseInt(row.getString("priority"));
} while (row.next());
} else {
ans[0] = 0;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ans;
}
public static void namejudge(Socket socket, Connection conn, BufferedReader is) {
System.out.println("用户查询存在模块");
try {
PrintWriter os = new PrintWriter(socket.getOutputStream());
String rec;
rec = is.readLine();
// 执行SQL语句
Statement stmt = conn.createStatement(); // 创建语句对象，用以执行sql语言
if (rec.charAt(0) == '1') {
ResultSet rs = stmt.executeQuery("select * from teacher_table where logname=" + rec);
if (rs.first()) {
os.println(rec);
os.flush();
} else {
os.println("no" + rec);
os.flush();
}
} else if (rec.charAt(0) == '2') {
ResultSet rs = stmt.executeQuery("select * from student_table where logname=" + rec);
if (rs.first()) {
os.println(rec);
os.flush();
} else {
os.println("no" + rec);
os.flush();
}
} else {
os.println("no" + rec);
os.flush();
}
} catch (Exception e) {
System.out.println("Error:" + e);
}
}
public void testTemp() throws Exception {
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.execute("CREATE TABLE #temp ( pk INT PRIMARY KEY, f_string VARCHAR(30), f_float FLOAT )");
// populate in the traditional way
for (int i = 0; i < 100; i++) {
stmt.execute(
"INSERT INTO #temp " + "VALUES( " + i + "," + "'The String " + i + "'" + ", " + i + ")");
}
dump(stmt.executeQuery("SELECT Count(*) FROM #temp"));
// Navigate around
ResultSet rs = stmt.executeQuery("SELECT * FROM #temp");
rs.first();
dumpRow(rs);
rs.last();
dumpRow(rs);
rs.first();
dumpRow(rs);
stmt.execute("DROP TABLE #temp");
stmt.close();
}
@Override
public boolean isuserExist(String email, String userName, Connection c) throws SQLException {
System.out.println("isexistimpl");
Statement cs = c.createStatement();
String sql = "Select * from User where email='" + email + "' and username='******' ";
ResultSet eq = cs.executeQuery(sql);
System.out.println("isexist = " + eq.first());
return eq.first();
}
public static void show(Socket socket, Connection conn, BufferedReader is) {
System.out.println("用户信息输出模块");
try {
PrintWriter os = new PrintWriter(socket.getOutputStream());
os.flush();
String rec;
rec = is.readLine();
System.out.println("Test:");
Statement stmt = conn.createStatement(); // 创建语句对象，用以执行sql语言
if (rec.charAt(0) == '1') {
ResultSet rs = stmt.executeQuery("select * from teacher_table where logname=" + rec);
rs.first();
String send;
send = rs.getString("name");
os.println(send);
os.flush();
send = rs.getString("course");
os.println(send);
os.flush();
send = rs.getString("address");
os.println(send);
os.flush();
send = rs.getString("phone");
os.println(send);
os.flush();
send = rs.getString("email");
os.println(send);
os.flush();
}
if (rec.charAt(0) == '2') {
ResultSet rs = stmt.executeQuery("select * from student_table where logname=" + rec);
rs.first();
String send;
send = rs.getString("name");
os.println(send);
os.flush();
send = rs.getString("academy");
os.println(send);
os.flush();
send = rs.getString("address");
os.println(send);
os.flush();
send = rs.getString("phone");
os.println(send);
os.flush();
send = rs.getString("email");
os.println(send);
os.flush();
}
} catch (Exception e) {
System.out.println("Error:" + e);
}
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
String studentid = request.getParameter("userid");
String searchfor = request.getParameter("year");
String sql =
"select name , studentid from student where yearofgrad like '"
+ searchfor
+ "' and studentid <> 'admin';"; // add clause so user cant search for himself
System.out.println(sql);
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn =
(Connection)
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/alumniportal", "root", "student");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
System.out.println("REACHED HERE THREE");
String people = "<h4>Search results:- </h4>";
if (!rs.first()) {
people =
"No results found. Try using a percentage symbol in your search for wildcard search.";
} else {
// rs.first();
rs.beforeFirst();
while (rs.next()) {
people =
people
+ " <form action='ProfilePage_Intermediate' method='post'> <input style=\"background:transparent; border:transparent; color:black;\" class=\"btn\" type=\"submit\" value='"
+ rs.getString("name")
+ "'/><input type=\"hidden\" value='"
+ rs.getString("studentid")
+ "' name='studentid' /><input type=\"hidden\" value='"
+ studentid
+ "' name='fromstudentid' /></form>";
}
}
sql = "select password from login where studentid='" + studentid + "';";
rs = stmt.executeQuery(sql);
rs.first();
System.out.println(studentid + rs.getString("password"));
request.setAttribute("user", studentid);
request.setAttribute("password", rs.getString("password"));
request.setAttribute("people", people);
request.getRequestDispatcher("HomePage_Student").forward(request, response);
} catch (Exception e) {
}
}
private void validateSQLdata(boolean hasdata) throws NamingException, SQLException {
Connection con = h2Datasource.getConnection();
Statement stmt = null;
try {
stmt = con.createStatement();
ResultSet resultSet = null;
try {
resultSet = stmt.executeQuery("SELECT * FROM TEST");
} catch (SQLException e) {
}
if (hasdata) {
assertNotNull(resultSet);
assertTrue(resultSet.first());
assertEquals("txt", resultSet.getString("A"));
} else {
assertTrue(resultSet == null || resultSet.getFetchSize() == 0);
}
} finally {
if (stmt != null) {
stmt.close();
}
con.close();
}
}
public static void getBlogs() throws Exception {
BlockingQueue<String> queue = new ArrayBlockingQueue<String>(numCrawler * 4);
CrawlerC[] crawler = new CrawlerC[numCrawler];
for (int i = 0; i < crawler.length; i++) {
crawler[i] = new CrawlerC(queue);
crawler[i].start();
}
ResultSet rs = null;
int offset = 1;
while (true) {
offset += 100;
myStm.executeQuery("SELECT blogID from blogs where country = 'BR' LIMIT " + offset + ",100");
System.out.println("\n---" + offset + "---");
rs = myStm.getResultSet();
try {
if (!rs.first()) break;
if (false) break;
while (rs.next()) {
// System.out.println(rs.getString("blogID"));
if (!queue.offer(rs.getString("blogID"), 60, TimeUnit.SECONDS)) {
System.out.println("Offer.Timeout");
}
}
} catch (Exception e) {
}
}
queue.clear();
for (int i = 0; i < crawler.length; i++) queue.put(CrawlerC.NO_MORE_WORK);
for (int i = 0; i < crawler.length; i++) crawler[i].join();
}
private void isEdit(Boolean isEdit) {
if (isEdit) {
try {
Connection con = FrameLogin.getConnect();
String sql = "SELECT * FROM Persons WHERE personId = " + Id;
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.first()) {
String firstname = rs.getString("FirstName");
String lastname = rs.getString("LastName");
String cellphone = rs.getString("CellPhoneNo");
String homephone = rs.getString("HomePhoneNo");
String gradyear = rs.getString("Graduation Year");
String Gender = rs.getString("Gender");
firstName.setText(firstname);
lastName.setText(lastname);
cellPhone.setText(cellphone);
homePhone.setText(homephone);
gradYear.setText(gradyear);
gender.setSelectedItem(Gender);
jLabel7.setVisible(false);
studentId.setVisible(false);
} else {
MessageBox.infoBox("Error: ID not found", "Error");
}
} catch (Exception e) {
MessageBox.infoBox(e.toString(), "Error in isEdit");
}
}
FrameLogin.closeConnect();
}
public static void createDefaultShippingRegion(Connection connection) throws SQLException {
String sql =
"insert "
+ "into shipping_region "
+ " (published, rec_create_by, rec_create_datetime, rec_update_by, rec_update_datetime, shipping_region_name, site_id, system_record) "
+ "values ('Y', 'admin', now(), 'admin', now(), 'default', ?, 'Y')";
PreparedStatement insertStatement = connection.prepareStatement(sql);
Statement select = connection.createStatement();
sql =
"select site_id "
+ "from site "
+ "where site_id != '_system' "
+ "and system_record = 'Y'";
ResultSet result = select.executeQuery(sql);
while (result.next()) {
String siteId = result.getString("site_id");
sql =
"select count(*) "
+ "from shipping_region "
+ "where site_id = '"
+ siteId
+ "' "
+ "and system_record = 'Y'";
Statement countStatement = connection.createStatement();
ResultSet shippingRegionResult = countStatement.executeQuery(sql);
shippingRegionResult.first();
int count = shippingRegionResult.getInt(1);
if (count == 0) {
insertStatement.setString(1, siteId);
insertStatement.executeUpdate();
}
}
}
@BeforeClass
public void setUp() throws Exception {
// Start ZK and Kafka
startZk();
kafkaStarter =
KafkaStarterUtils.startServer(
KafkaStarterUtils.DEFAULT_KAFKA_PORT,
KafkaStarterUtils.DEFAULT_BROKER_ID,
KafkaStarterUtils.DEFAULT_ZK_STR,
KafkaStarterUtils.getDefaultKafkaConfiguration());
// Create Kafka topic
KafkaStarterUtils.createTopic(KAFKA_TOPIC, KafkaStarterUtils.DEFAULT_ZK_STR);
// Start the Pinot cluster
startController();
startBroker();
startServer();
// Unpack data
final List<File> avroFiles = unpackAvroData(_tmpDir, SEGMENT_COUNT);
File schemaFile = getSchemaFile();
// Load data into H2
ExecutorService executor = Executors.newCachedThreadPool();
setupH2AndInsertAvro(avroFiles, executor);
// Initialize query generator
setupQueryGenerator(avroFiles, executor);
// Push data into the Kafka topic
pushAvroIntoKafka(avroFiles, executor, KAFKA_TOPIC);
// Wait for data push, query generator initialization and H2 load to complete
executor.shutdown();
executor.awaitTermination(10, TimeUnit.MINUTES);
// Create Pinot table
setUpTable(
"mytable",
"DaysSinceEpoch",
"daysSinceEpoch",
KafkaStarterUtils.DEFAULT_ZK_STR,
KAFKA_TOPIC,
schemaFile,
avroFiles.get(0));
// Wait until the Pinot event count matches with the number of events in the Avro files
long timeInFiveMinutes = System.currentTimeMillis() + 5 * 60 * 1000L;
Statement statement =
_connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.execute("select count(*) from mytable");
ResultSet rs = statement.getResultSet();
rs.first();
int h2RecordCount = rs.getInt(1);
rs.close();
waitForRecordCountToStabilizeToExpectedCount(h2RecordCount, timeInFiveMinutes);
}
/**
* Checks if the specified {@link Operation} has been modified.
*
* @param operation - the {@link Operation} to check.
* @return <code>true</code> if has been modified, <code>false</code> otherwise.
* @throws OHException if an error occurs during the check.
*/
public boolean hasOperationModified(Operation operation) throws OHException {
DbQueryLogger dbQuery = new DbQueryLogger();
boolean result = false;
// we establish if someone else has updated/deleted the record since the last read
String query = "SELECT OPE_LOCK FROM OPERATION WHERE OPE_ID_A = ?";
List<Object> parameters = Collections.<Object>singletonList(operation.getCode());
try {
// we use manual commit of the transaction
ResultSet resultSet = dbQuery.getDataWithParams(query, parameters, true);
if (resultSet.first()) {
// ok the record is present, it was not deleted
result = resultSet.getInt("OPE_LOCK") != operation.getLock();
} else {
throw new OHException(
MessageBundle.getMessage("angal.sql.couldntfindthedataithasprobablybeendeleted"));
}
} catch (SQLException e) {
throw new OHException(
MessageBundle.getMessage("angal.sql.problemsoccurredwiththesqlistruction"), e);
} finally {
dbQuery.releaseConnection();
}
return result;
}
public void workerNameForCurrentWorker() throws Exception {
userIdForCurrentUser();
if (Global.user_id == 0) {
return;
}
if (!newConnection()) {
return;
}
try {
/* PreparedStatement to issue SQL query */
// Search worker using worker_id
preparedStatement =
connection.prepareStatement(
"select last_name, first_name from workers where id = " + Global.worker_id);
resultSet = preparedStatement.executeQuery();
resultSet.first();
String _worker_last_name = resultSet.getString("last_name");
String _worker_first_name = resultSet.getString("first_name");
Global.worker_name = _worker_last_name + ", " + _worker_first_name;
} catch (Exception e) {
throw e;
} finally {
closeDataObjects();
}
}
public void workerIdForCurrentUser() throws Exception {
userIdForCurrentUser();
if (Global.user_id == 0) {
Global.worker_id = 0;
return;
}
if (!newConnection()) {
Global.worker_id = 0;
return;
}
try {
/* PreparedStatement to issue SQL query */
// Search worker using user_id to extract worker_id
preparedStatement =
connection.prepareStatement(
"select id, last_name, first_name from workers where user_id = " + Global.user_id);
resultSet = preparedStatement.executeQuery();
resultSet.first();
Integer _worker_id = resultSet.getInt("id");
if (_worker_id == null) {
_worker_id = 0;
}
Global.worker_id = _worker_id;
} catch (Exception e) {
throw e;
} finally {
closeDataObjects();
}
}
public List<ClientBean> getClients() {
String query = "select id, firstname, lastname, email, phonenumber from clients;";
Statement statement = null;
try {
statement = connection.createStatement();
final ResultSet resultSet = statement.executeQuery(query);
List<ClientBean> result = new ArrayList<>();
boolean hasNext = resultSet.first();
while (hasNext) {
int id = resultSet.getInt(1);
ClientBean client = new ClientBean(id);
String firstName = resultSet.getString(1);
client.setFirstName(firstName);
String lastName = resultSet.getString(2);
client.setLastName(lastName);
String email = resultSet.getString(3);
client.setEmail(email);
String phoneNumber = resultSet.getString(4);
client.setPhone(phoneNumber);
result.add(client);
hasNext = resultSet.next();
}
return result;
} catch (SQLException e) {
e.printStackTrace();
}
return new ArrayList<>();
}
/**
* Add recruitment request to permanent store.
*
* @param toAdd request to be added
* @return id of added request or -1 if unsuccessful
*/
public int addRecruitmentRequest(RecruitmentRequestBean toAdd) {
String insertStatement =
"insert into recruitmentrequests (requestdep, requestdate, jobtitle, contracttype, yearsofexperience, jobdescription, status) values (?,?,?,?,?,?,?);";
try {
PreparedStatement statement =
connection.prepareStatement(insertStatement, Statement.RETURN_GENERATED_KEYS);
statement.setNString(1, toAdd.getRequestDepartment());
statement.setDate(2, new java.sql.Date(toAdd.getRequestDate().getTime()));
statement.setNString(3, toAdd.getJobTitle());
statement.setNString(4, toAdd.getContractType());
statement.setInt(5, toAdd.getYearsOfExperience());
statement.setNString(6, toAdd.getJobDescription());
statement.setNString(7, "active");
statement.executeUpdate();
ResultSet rs = statement.getGeneratedKeys();
if (rs.first()) {
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
/**
* This is called AsyncPlayerLoginEvent so no need to make it Async
*
* @param p_name
*/
public void loadData(String p_name) {
try (PreparedStatement pst =
ConnectionPool.getConnection()
.prepareStatement("SELECT * FROM hearthstone WHERE p_name = ?")) {
pst.setString(1, p_name);
ResultSet rst = pst.executeQuery();
if (!rst.first()) {
tp_loc = HearthstoneMechanics.spawn_map.get("Cyrennica");
tp_name = "Cyrennica";
sendInsertQuery();
return;
}
tp_name = rst.getString("location_name");
if (tp_name == null) {
System.out.print("Location name was null for " + p_name);
}
tp_loc = HearthstoneMechanics.spawn_map.get(rst.getString("location_name"));
setTimer(rst.getInt("timer"));
// TODO: Download the data from tables and set their spawns
System.out.print("[HearthstoneMechanics] Set " + p_name + " location to " + tp_name);
pst.close();
} catch (SQLException sqlE) {
sqlE.printStackTrace();
}
}
// ########################################################################################################################################
public int getPlayerId(String playerName) {
ResultSet rs =
this.databaseConnection.sqlSafeQuery(
"SELECT `id` FROM `wp_users` WHERE `user_login`='" + playerName + "'");
try {
while (rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
// No match found write player to database
rs =
this.databaseConnection.insertSafeQueryAndKeys(
"INSERT INTO `wp_users` (`user_login`, `user_status`) VALUES ('"
+ playerName
+ "', -1)");
try {
rs.first();
return rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
private String lookupUser(String credentials) throws Exception {
// Parse user and password
String[] args = parseCredentials(credentials);
String cmd;
try {
cmd =
"SELECT user_id"
+ " FROM User"
+ " WHERE username = \""
+ args[USERNAME_INDEX]
+ "\" AND password = \""
+ args[PASSWORD_INDEX]
+ "\";";
} catch (IndexOutOfBoundsException e) {
throw new Exception("No password given.");
}
JSONObject obj = new JSONObject();
try {
ResultSet rs = query(cmd);
if (rs.first()) {
String userID = Integer.toString(rs.getInt(1));
obj.put("user_id", userID);
obj.put("username", args[USERNAME_INDEX]);
String sessionID = UUID.randomUUID().toString();
updateUserSessionID(sessionID, args[USERNAME_INDEX]);
obj.put("session_id", sessionID);
obj.put("task_list", lookupUserProgress(userID));
return obj.toString();
}
} catch (NullPointerException | SQLException e) {
throw new Exception("Error with SQL statement.");
}
throw new InvalidCredentialsException("Invalid user credentials.");
// Look up the tasks the user has completed
}
public String[] getVideo() {
int i = 0;
int j = 0;
String[] ans = null;
String str = "SELECT * FROM video ORDER BY id";
try {
row = stm.executeQuery(str);
row.last();
i = row.getRow();
row.first();
ans = new String[i + 1];
if (i > 0) {
ans[0] = Integer.toString(i);
do {
j++;
ans[j] = row.getString("name");
} while (row.next());
} else {
ans[0] = "0";
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ans;
}
public static boolean isIsbnExisting(String isbn) throws Exception {
boolean validate = false;
String otherISBN = IsbnUtil.convert(isbn);
String sql = "";
PreparedStatement ps = null;
if (otherISBN == null) {
// otherISBN is ISBN10 and is not existing
sql = "SELECT COUNT(*) From Books WHERE Isbn13 = ?";
ps = Connector.getConnection().prepareStatement(sql);
ps.setString(1, isbn);
} else {
sql = "SELECT COUNT(*) From Books WHERE Isbn13 = ? and Isbn10 = ?";
ps = Connector.getConnection().prepareStatement(sql);
if (isbn.length() == 13) {
ps.setString(1, isbn);
ps.setString(2, otherISBN);
} else {
// argument is ISBN10
ps.setString(1, otherISBN);
ps.setString(2, isbn);
}
}
ResultSet rs = ps.executeQuery();
rs.first();
if (rs.getInt(1) > 0) {
validate = true;
}
Connector.close();
return validate;
}
public static String getDomainNameForColumn(Connector c, Table table, String columnName)
throws SQLException {
String schemaName = Helper.getSchemaName(c, table);
String tableName = table.getName();
String sql =
"SELECT domain_name "
+ "FROM information_schema_fuzzy.domains AS D JOIN "
+ "information_schema_fuzzy.columns AS C ON (D.domain_id = C.domain_id) "
+ "WHERE C.table_schema = '"
+ schemaName
+ "' "
+ "AND C.table_name = '"
+ tableName
+ "' "
+ "AND C.column_name = '"
+ columnName
+ "'";
Logger.debug("Looking for domain name with query:\n" + sql);
ResultSet rs = c.executeRawQuery(sql);
SQLException e = null;
try {
if (rs.first()) {
return rs.getString("domain_name");
}
} catch (SQLException ex) {
e = ex;
}
throw new SQLException(
"Domain name not found for " + schemaName + "." + tableName + "." + columnName,
"42000",
3020,
e);
}
public static void createCustomerClass(Connection connection) throws SQLException {
String sql =
"insert "
+ "into customer_class "
+ " (cust_class_name, rec_create_by, rec_create_datetime, rec_update_by, rec_update_datetime, site_id, system_record) "
+ "values ('Regular', 'admin', now(), 'admin', now(), ?, 'Y')";
PreparedStatement insertStatement = connection.prepareStatement(sql);
Statement select = connection.createStatement();
sql =
"select site_id "
+ "from site "
+ "where site_id != '_system' "
+ "and system_record = 'Y'";
ResultSet result = select.executeQuery(sql);
while (result.next()) {
String siteId = result.getString("site_id");
sql = "select count(*) " + "from customer_class " + "where site_id = '" + siteId + "'";
Statement countStatement = connection.createStatement();
ResultSet customerClassResult = countStatement.executeQuery(sql);
customerClassResult.first();
int count = customerClassResult.getInt(1);
if (count == 0) {
insertStatement.setString(1, siteId);
insertStatement.executeUpdate();
}
}
}
public static Event fromID(int ID) throws Exception {
DBWrapper db = new DBWrapper();
PreparedStatement st =
db.getPreparedStatement(
"SELECT e.ID as ID, e.Name as Name, e.Description as Description, e.CreatedBy as CreatedBy e.OwnedBy as OwnedBy, e.CreatedOn as CreatedOn, e.LastModifiedOn as LastModifiedOn, e.IsPrivate as IsPrivate, e.IsActive as IsActive, e.IsDeleted as IsDeleted FROM Event e WHERE e.ID=? LIMIT 1");
st.setInt(0, ID);
ResultSet rs = db.executeThisQuery(st);
if (rs.first()) {
Event e = new Event();
e.ID = rs.getInt("ID");
e.name = rs.getString("Name");
e.description = rs.getString("Description");
e.createdByID = rs.getInt("CreatedBy");
e.ownedByID = rs.getInt("OwnedBy");
e.createdOn = new DateTime((Date) rs.getTimestamp("CreatedOn"));
e.lastModifiedOn = new DateTime((Date) rs.getTimestamp("LastModifiedOn"));
e.isPrivate = rs.getInt("IsPrivate");
e.isActive = rs.getInt("IsActive");
e.isDeleted = rs.getInt("IsDeleted");
e.isNew = false;
return e;
}
return null;
}
public static ArrayList getContexts() {
try {
ArrayList<String> arraylist = new ArrayList<String>();
java.sql.Connection con = Database.getConnection();
java.sql.Statement s =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet contexts;
contexts = s.executeQuery("select name from contexts");
if (contexts.next()) {
contexts.first();
arraylist.add(contexts.getString("name"));
while (contexts.next()) {
arraylist.add(contexts.getString("name"));
}
}
return arraylist;
} catch (SQLException ex) {
Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
public Set<Student> getAllStudents() throws DataAccessException {
Set<Student> students = new HashSet<Student>();
try {
Statement stmnt = conn.createStatement();
ResultSet rs = stmnt.executeQuery("SELECT id, lastName, firstName, birthDate FROM Student");
if (rs.first()) {
do {
int id = rs.getInt("id");
String lastName = rs.getString("lastName");
String firstName = rs.getString("firstName");
Date birthDate = new Date(rs.getLong("birthDate"));
Set<Section> sections = getStudentSections(id);
Student student = new Student(id, lastName, firstName, birthDate, sections);
students.add(student);
} while (rs.next());
}
rs.close();
} catch (SQLException e) {
throw new DataAccessException(e);
}
return students;
}
@Override
public void action() {
System.out.println("BDD reçu = " + query1);
System.out.println("BDD reçu = " + query2);
Connection connection = ((BDDAgent) myAgent).connectDatabase();
if (connection != null) {
System.out.println("Connexion de la database");
try {
Statement statement = connection.createStatement();
if (query1 != null) {
int result = statement.executeUpdate(query1);
if (result == 0) this.id = -1;
else {
if (query2 != null) {
ResultSet rs = statement.executeQuery(query2);
if (rs.first()) {
do {
this.id = rs.getInt(1);
} while (rs.next());
}
}
}
myAgent.send(this.createMessage());
}
} catch (SQLException e) {
e.printStackTrace();
}
((BDDAgent) myAgent).disconnectDatabase(connection);
} else {
System.out.println("FAIL");
}
}
public static ArrayList getAllThoughts() {
try {
ArrayList arrayList = new ArrayList<Gedachte>();
java.sql.Connection con = Database.getConnection();
java.sql.Statement s =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet thoughts;
thoughts = s.executeQuery("select * from thoughts");
if (thoughts.next()) {
thoughts.first();
int id1 = thoughts.getInt("id");
String notes1 = thoughts.getString("notes");
Gedachte gedachte1 = new Gedachte(id1, notes1);
arrayList.add(gedachte1);
while (thoughts.next()) {
int id = thoughts.getInt("id");
String notes = thoughts.getString("notes");
Gedachte gedachte = new Gedachte(id, notes);
arrayList.add(gedachte);
}
}
return arrayList;
} catch (SQLException ex) {
ex.printStackTrace();
}
return null;
}
