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 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 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();
}
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 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);
}
}
public Boolean move_first() {
try {
return results.first();
} catch (Exception ex) {
throw new InvalidQueryException("Database access failed");
}
}
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 void insertChartUpdate(String doi, int lastNcites, Connection con) throws SQLException {
PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY);
getUpdateStmt.setString(1, doi);
ResultSet rs = getUpdateStmt.executeQuery();
boolean isNew = true;
if (rs.first()) {
isNew = false;
}
rs.close();
getUpdateStmt.close();
PreparedStatement stmt = null;
if (isNew) {
stmt = con.prepareStatement(DEF_INS_UPDATE_STMT);
stmt.setString(1, doi);
stmt.setInt(2, lastNcites);
} else {
stmt = con.prepareStatement(DEF_UPDATE_UPDATE_STMT);
stmt.setInt(1, lastNcites);
stmt.setString(2, doi);
}
stmt.executeUpdate();
stmt.close();
} // - insertChartUpdate
/**
* 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;
}
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<>();
}
public int[] searchVideo(String key) {
int i = 0;
int j = 0;
int[] ans = null;
String str = "SELECT * FROM video WHERE name LIKE '%" + key + "%'";
try {
row = stm.executeQuery(str);
row.last();
i = row.getRow();
row.first();
if (i == 0) {
ans = new int[1];
} else {
ans = new int[i];
}
if (i > 0) {
do {
ans[j] = Integer.parseInt(row.getString("id"));
j++;
} while (row.next());
} else {
ans[0] = 0;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ans;
}
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 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;
}
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 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();
}
}
private String getUserIDFromSessionID(String sessionID) throws SQLException {
String cmd = "SELECT user_id FROM User WHERE session_id = \"" + sessionID + "\";";
ResultSet rs = query(cmd);
if (rs.first()) {
return rs.getString(1);
}
return null;
}
public void ramasserObjet() {
boolean vide = true;
try {
ResultSet rset =
stmt.executeQuery(
"select idObjet from objet where positionX="
+ positionX
+ " and positionY="
+ positionY);
Hashtable<String, String> tabObj = new Hashtable<String, String>();
while (rset.next()) {
vide = false;
String idObj = rset.getString("idObjet");
System.out.println(idObj);
tabObj.put(idObj, idObj);
}
rset.close();
if (!vide) {
System.out.println("Quel objet voulez vous ramasser ? : ");
String obj = IO.lireChaine();
if (tabObj.containsKey(obj)) {
proc = conn.prepareCall("{call rammasser(?,?)}");
proc.setInt(1, idTroll);
proc.setString(2, obj);
proc.executeUpdate();
proc.close();
rset = stmt.executeQuery("select typeObjet from objet where idObjet='" + obj + "'");
rset.first();
String type = rset.getString("typeObjet");
if (type.equals("potion")) {
menu.supprimerPopo(obj);
} else {
menu.supprimerObjet(obj);
}
rset = stmt.executeQuery("select paRestants from troll where idTroll=" + idTroll);
int ancVal = 0;
while (rset.next()) {
ancVal = rset.getInt("paRestants");
}
stmt.executeUpdate(
"update troll SET paRestants = " + (ancVal - 1) + " where idTroll=" + idTroll);
paRestants = paRestants - 1;
} else {
System.out.println("Cet objet ne se trouve pas sur votre case !");
}
} else {
System.out.println("Il n'y a aucun objet sur votre case");
}
} catch (SQLException E) {
System.err.println("SQLException: " + E.getMessage());
System.err.println("SQLState: " + E.getSQLState());
}
}
public String getUserPosition(String username, String password) {
try {
Statement getUserPosition = connection.createStatement();
String query = "select position from users where username='******'";
ResultSet result = getUserPosition.executeQuery(query);
result.first();
return result.getString(1);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public int record_count(String target, String source, String criteria) {
try {
Statement s = connection.createStatement();
if (criteria.isEmpty()) s.execute("Select count(" + target + ") From " + source);
else s.execute("Select count(" + target + ") From " + source + " Where " + criteria);
if (results != null) results.close();
results = s.getResultSet();
if (results.first()) return results.getInt(1);
else return -1;
} catch (Exception ex) {
throw new InvalidQueryException("Database count failed");
}
}
public Team find(int id) {
Team team = new Team();
try {
ResultSet result =
this.connect
.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
.executeQuery("SELECT * FROM teams WHERE te_id = " + id);
if (result.first()) team = new Team(id, result.getFloat("te_points"));
} catch (SQLException e) {
e.printStackTrace();
}
return team;
}
// === USER OPERATIONS ===
public boolean login(String username, String hash) {
try {
Statement login = connection.createStatement();
String query = "select password from users where username='******';";
System.out.println(query);
ResultSet result = login.executeQuery(query);
result.first();
String password = result.getString(1);
if (password.equals(hash)) return true;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
/**
* Get event given it's ID
*
* @return the event as a bean, or null if no matching event found
*/
public EventRequestBean getEventRequest(int eventId) {
String query =
"select id, name, clientid, type, fromdate, todate, budget, status, details_photo_video, details_music, details_poster, details_decoration, details_network, details_food_beverages, details_waiter, feedback_budget from eventrequests where id=?;";
try {
final PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, eventId);
final ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.first()) {
return getFromResultSet(resultSet);
}
} catch (SQLException e) {
e.printStackTrace();
}
return new EventRequestBean();
}
/** 为借用 查询所有记录 */
public String[][] searchAllForUse() {
Database DB = new Database();
AssetsBean aBean = new AssetsBean();
PersonBean pBean = new PersonBean();
String[][] sn = null;
int row = 0;
int i = 0;
sql = "select * from AssetsTrjn where FromAcc ='设备借用' order by JourNo";
try {
DB.OpenConn();
rs = DB.executeQuery(sql);
if (rs.last()) {
row = rs.getRow();
}
if (row == 0) {
sn = new String[1][6];
sn[0][0] = " ";
sn[0][1] = " ";
sn[0][2] = " ";
sn[0][3] = " ";
sn[0][4] = " ";
sn[0][5] = " ";
} else {
sn = new String[row][6];
rs.first();
rs.previous();
while (rs.next()) {
sn[i][0] = rs.getString("JourNo");
sn[i][1] = aBean.getAssetsName(rs.getString("AssetsID"));
sn[i][2] = rs.getString("RegDate");
sn[i][3] = pBean.getPersonName(rs.getString("PersonID"));
sn[i][4] = rs.getString("Use");
sn[i][5] = rs.getString("Other");
i++;
}
}
} catch (Exception e) {
} finally {
DB.closeStmt();
DB.closeConn();
}
return sn;
}
public boolean verificaSenha(int codigo, String senha) {
String sql;
conecta();
try {
sql = "SELECT senha FROM usuario WHERE codigo=" + codigo + ";";
System.out.println(sql);
rs = stmt.executeQuery(sql);
rs.first();
if (rs.getString("senha").equals(senha)) {
return true;
} else {
return false;
}
} catch (SQLException e) {
return false;
}
}
public SingleNameReport(WritableWorkbook workbook, Statement new_statement, String[] arguments) {
String main_tsn = "";
String main_rank = "";
String main_kingdom = "";
row = 0;
try {
statement = new_statement;
func = new AdditionalFunctions(statement);
copy = workbook;
kingdom = arguments[0];
hrank = arguments[1];
lrank = arguments[2];
scientificName = arguments[3];
date_from = arguments[4];
date_to = arguments[5];
user = arguments[6];
String temp = "";
temp =
"SELECT tsn,rank_id,kingdom_id from Tree where scientificName='" + scientificName + "'";
System.out.println(temp);
result = statement.executeQuery(temp);
metadata = result.getMetaData();
int numberOfRows = 0;
int hrankVal = Integer.parseInt(hrank);
int main_rankVal = 0;
if (result.last()) numberOfRows = result.getRow();
result.first();
if (numberOfRows > 0 && metadata.getColumnCount() > 0) {
main_tsn = result.getString(1);
main_rank = result.getString(2);
main_rankVal = Integer.parseInt(main_rank);
main_kingdom = result.getString(3);
}
if (HasChildren(main_tsn) == true) FindChildInformation(main_tsn, arguments, func);
FileInFrontPage frontPage = new FileInFrontPage(copy, statement, arguments);
copy.write();
copy.close();
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
} // end of constructor
/**
* Handles the HTTP <code>GET</code> method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {
Class.forName("com.mysql.jdbc.Driver");
connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/baza", "root", "");
if (connect != null) {
statement = connect.createStatement();
HttpSession session = request.getSession();
String Login = (String) session.getAttribute("login");
Integer Id = (Integer) session.getAttribute("id");
out.println("Hello <b>" + Login + "</b>!");
resultSet =
statement.executeQuery(
"select * from users where Nickname='" + Login + "' and idUsers='" + Id + "'");
resultSet.next(); // /?
if (resultSet.first() == true) {
String Surname = resultSet.getString("Surname");
String Name = resultSet.getString("Name");
String Password = resultSet.getString("password");
request.setAttribute("Name", Name);
request.setAttribute("Surname", Surname);
request.setAttribute("Login", Login);
request.setAttribute("Password", Password);
RequestDispatcher rd = request.getRequestDispatcher("profile.jsp");
rd.forward(request, response);
response.sendRedirect("profile.jsp");
} else {
connect.close();
request.setAttribute("error", "Something went wrong :/");
RequestDispatcher rd = request.getRequestDispatcher("profile.jsp");
rd.forward(request, response);
response.sendRedirect("profile.jsp");
}
}
} catch (SQLException ex) {
Logger.getLogger(Profile.class.getName()).log(Level.SEVERE, null, ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(Profile.class.getName()).log(Level.SEVERE, null, ex);
}
}
public ResultSet buscaUsuario(String login) {
String sql;
conecta();
try {
sql = "SELECT * FROM usuario WHERE login='******';";
System.out.println(sql);
rs = stmt.executeQuery(sql);
rs.first();
if (rs.getString("login") != null) {
return rs;
} else {
return null;
}
} catch (SQLException e) {
return null;
}
}
// public method that checks for existance of children
public boolean HasChildren(String tsn) {
String temp = "SELECT tsn from Tree where parent_tsn='" + tsn + "'";
if (lrank.compareTo("0") != 0 && lrank.compareTo("7") != 0)
temp = temp + " AND rank_id<=" + lrank;
System.out.println(temp);
int numberOfRows = 0;
boolean flag = false;
try {
result = statement.executeQuery(temp);
metadata = result.getMetaData();
if (result.last()) numberOfRows = result.getRow();
result.first();
if (numberOfRows > 0 && metadata.getColumnCount() > 0) flag = true;
} catch (SQLException sql) {
sql.printStackTrace();
System.exit(1);
}
return flag;
} // end of method HasChildren
private String lookupTaskInfo(String param) {
String cmd =
"SELECT instructions, code_template, test_code FROM Task"
+ " WHERE task_id = "
+ param
+ ";";
JSONObject obj = new JSONObject();
try {
ResultSet rs = query(cmd);
if (rs.first()) {
obj.put("instructions", rs.getString(1));
obj.put("code_template", rs.getString(2));
obj.put("test_code", rs.getString(3));
return obj.toString();
}
} catch (SQLException e) {
return "";
}
return "";
}
public void logado() {
String sql;
conecta();
try {
sql = "SELECT * FROM usuario WHERE logado='s'";
System.out.println(sql);
rs = stmt.executeQuery(sql);
rs.first();
nom = rs.getString("nome");
codi = Integer.parseInt(rs.getString("codigo"));
} catch (SQLException e) {
System.out.println("Erro ao executar o comando SQL:" + e.toString());
}
}
