@RequestMapping(value = "/shinfo", method = RequestMethod.POST)
@ResponseBody
public UserModel shortInfo(@RequestParam(value = "email", required = true) String email) {
UserModel userModel = new UserModel();
AccountModel accountModel = new AccountModel();
Connection connection = null;
try {
connection = dataSource.getConnection();
String sqlI =
"select id from user where email='"
+ email
+ "' AND sysdate() between validfrom and validto";
PreparedStatement psI = connection.prepareStatement(sqlI);
ResultSet resultSetI = psI.executeQuery();
if (resultSetI.next()) {
userModel.setId(resultSetI.getInt(1));
}
resultSetI.close();
psI.close();
String sqlA =
"select amount from budget where user_id=(SELECT id FROM user where email='"
+ email
+ "' AND sysdate() between validfrom and validto )";
PreparedStatement psA = connection.prepareStatement(sqlA);
ResultSet resultSetA = psA.executeQuery();
if (resultSetA.next()) {
accountModel.setAmount(resultSetA.getDouble(1));
}
resultSetA.close();
psA.close();
userModel.setEmail(email);
userModel.setAccountModel(accountModel);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
return userModel;
}
@RequestMapping(value = "/shinfoid", method = RequestMethod.POST)
@ResponseBody
public UserModel shortInfoId(@RequestParam(value = "id", required = true) Integer id) {
UserModel userModel = new UserModel();
AccountModel accountModel = new AccountModel();
Connection connection = null;
try {
connection = dataSource.getConnection();
String sqlE = "select email from user where id=" + id;
PreparedStatement psE = connection.prepareStatement(sqlE);
ResultSet resultSetE = psE.executeQuery();
if (resultSetE.next()) {
userModel.setEmail(resultSetE.getString(1));
}
resultSetE.close();
psE.close();
String sqlA = "select amount from budget where user_id=" + id;
PreparedStatement psA = connection.prepareStatement(sqlA);
ResultSet resultSetA = psA.executeQuery();
if (resultSetA.next()) {
accountModel.setAmount(resultSetA.getDouble(1));
}
resultSetA.close();
psA.close();
userModel.setId(id);
userModel.setAccountModel(accountModel);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
return userModel;
}
@RequestMapping(value = "/getallusers", method = RequestMethod.POST)
@ResponseBody
public List<UserModel> getAllUsers() {
String sql = "SELECT * FROM user where sysdate() between validfrom and validto";
Connection connection = null;
List<UserModel> userModelList = new ArrayList<UserModel>();
try {
connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()) {
AccountModel accountModel = new AccountModel();
String sqlA =
"select amount from budget where user_id=(SELECT id FROM user where email='"
+ resultSet.getString(2)
+ "' AND sysdate() between validfrom and validto )";
PreparedStatement psA = connection.prepareStatement(sqlA);
ResultSet resultSetA = psA.executeQuery();
if (resultSetA.next()) {
accountModel.setAmount(resultSetA.getDouble(1));
userModelList.add(
new UserModel(resultSet.getInt(1), resultSet.getString(2), accountModel));
}
resultSetA.close();
psA.close();
}
resultSet.close();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
return userModelList;
}
