@POST
@Consumes(MediaType.APPLICATION_JSON)
public Response addEvent(Event event) {
if (authenticated) {
Event evt = new Event(event);
String sql =
"INSERT INTO event (eventName, eventDescription, dateFrom, dateTo) VALUES( '"
+ evt.getEventName()
+ "', '"
+ evt.getEventDescription()
+ "', '"
+ evt.getDateFromString()
+ "'"
+ ",'"
+ evt.getDateToString()
+ "')";
try {
sqlCon.getStatement().executeUpdate(sql);
return Response.status(Status.ACCEPTED).build();
} catch (Exception e) {
e.printStackTrace();
return Response.status(Status.BAD_REQUEST).build();
} finally {
sqlCon.closeConnection();
}
} else {
return Response.status(Status.FORBIDDEN).build();
}
}
public ArrayList<NhanVien> getTatCaNhanVien() {
this.listNhanVien = new ArrayList<NhanVien>();
try {
con = SQLConnection.getConnection();
pstmt = con.prepareStatement(sqlGetTatCaNhanVien);
rs = pstmt.executeQuery();
while (rs.next()) {
this.nhanVien =
new NhanVien(
rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
this.listNhanVien.add(this.nhanVien);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
SQLConnection.closeConnection(this.con);
SQLConnection.closePrepareStatement(pstmt);
SQLConnection.closeResultSet(rs);
}
return this.listNhanVien;
}
/**
* 回调业务逻辑
*
* @param back
* @return
*/
protected <T> T callBack(int type, DaoCallBack<T> back) {
T result = null;
SQLiteDatabase conn = null;
try {
switch (type) {
case TYPE_READ:
conn = sqlConnection.getReadableDatabase();
break;
case TYPE_WRITE:
conn = sqlConnection.getWritableDatabase();
break;
}
if (conn == null) throw new NullPointerException("SQLiteDatabase conn is null");
result = back.invoke(conn);
// conn.beginTransaction();
// conn.setTransactionSuccessful();
} catch (Exception e) {
// conn.endTransaction();
Logger.e(TAG, e);
} finally {
DBUtil.Release(conn, cursor);
}
return result;
}
public NhanVien getNhanVienTheoTenDangNhapVaMatKhau(String tenDangNhap, String matKhau) {
this.nhanVien = null;
try {
con = SQLConnection.getConnection();
pstmt = con.prepareStatement(sqlGetNhanVienTheoTenDangNhapVaMatKhau);
pstmt.setString(1, tenDangNhap);
pstmt.setString(2, matKhau);
rs = pstmt.executeQuery();
if (rs.next()) {
this.nhanVien =
new NhanVien(
rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
SQLConnection.closeConnection(this.con);
SQLConnection.closePrepareStatement(pstmt);
SQLConnection.closeResultSet(rs);
}
return this.nhanVien;
}
@Override
public void updateDomainCreatePostCreateSysPermissions(
SQLConnection connection,
Resource accessorResource,
Resource grantorResource,
Set<DomainCreatePermission> domainCreatePermissions) {
SQLStatement statement = null;
try {
statement =
connection.prepareStatement(
sqlStrings
.SQL_updateInGrantDomainCreatePermissionPostCreateSys_SET_GrantorID_IsWithGrant_PostCreateIsWithGrant_BY_AccessorID_PostCreateSysPermissionID);
for (DomainCreatePermission domainCreatePermission : domainCreatePermissions) {
if (!domainCreatePermission.isSystemPermission()
&& domainCreatePermission.getPostCreateDomainPermission().isSystemPermission()) {
statement.setResourceId(1, grantorResource);
statement.setBoolean(2, domainCreatePermission.isWithGrantOption());
statement.setBoolean(
3, domainCreatePermission.getPostCreateDomainPermission().isWithGrantOption());
statement.setResourceId(4, accessorResource);
statement.setDomainSystemPermissionId(
5, domainCreatePermission.getPostCreateDomainPermission().getSystemPermissionId());
assertOneRowUpdated(statement.executeUpdate());
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
closeStatement(statement);
}
}
@Override
public Set<DomainCreatePermission> getDomainCreatePostCreateSysPermissions(
SQLConnection connection, Resource accessorResource) {
SQLStatement statement = null;
try {
statement =
connection.prepareStatement(
sqlStrings
.SQL_findInGrantDomainCreatePermissionPostCreateSys_withoutInheritance_PostCreateSysPermissionID_PostCreateIsWithGrant_IsWithGrant_BY_AccessorID);
statement.setResourceId(1, accessorResource);
SQLResult resultSet = statement.executeQuery();
// collect the create permissions that this resource has to domains directly
Set<DomainCreatePermission> domainCreatePermissions = new HashSet<>();
while (resultSet.next()) {
domainCreatePermissions.add(getDomainCreatePostCreateSysPermission(resultSet));
}
resultSet.close();
return domainCreatePermissions;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
closeStatement(statement);
}
}
@DELETE
@Path("/{eventId}")
public Response deleteEvent(@PathParam("eventId") String eventId) {
if (authenticated) {
try {
sqlCon.getStatement().executeUpdate("DELETE FROM event WHERE eventId = " + eventId);
return Response.status(Status.ACCEPTED).build();
} catch (Exception e) {
e.printStackTrace();
return Response.status(Status.BAD_REQUEST).build();
} finally {
sqlCon.closeConnection();
}
} else {
return Response.status(Status.FORBIDDEN).build();
}
}
@GET
@Produces(MediaType.APPLICATION_JSON)
@Path("register/{userId}")
public Response getEventsAvailableForRegistration(@PathParam("userId") String userId) {
List<Event> events = new ArrayList<Event>();
if (authenticated) {
try {
ResultSet rs =
sqlCon
.getStatement()
.executeQuery(
"SELECT event.eventId, event.eventName, event.eventDescription, event.dateFrom, event.dateTo "
+ "FROM event where not exists ( select eventId from user_event where event.eventId = user_event.eventId "
+ "AND user_event.userId = "
+ userId
+ " );");
while (rs.next()) {
Event event = new Event();
long eventId = Long.parseLong(rs.getString("eventId"));
event.setEventId(eventId);
event.setEventName(rs.getString("eventName"));
event.setEventDescription(rs.getString("eventDescription"));
Calendar cal = new GregorianCalendar();
cal.set(2012, Calendar.AUGUST, 15, 15, 30);
// event.setDateFrom(cal);
// event.setDateTo(rs.getString("dateTo"));
events.add(event);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlCon.closeConnection();
}
GenericEntity<List<Event>> entity = new GenericEntity<List<Event>>(events) {};
return Response.status(Status.ACCEPTED).entity(entity).build();
} else {
return Response.status(Status.FORBIDDEN).build();
}
}
public int deleteNhanVien(int maNhanVien) {
int result = 0;
try {
con = SQLConnection.getConnection();
pstmt = con.prepareStatement(sqlDeleteNhanVien);
pstmt.setInt(1, maNhanVien);
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
SQLConnection.closeConnection(this.con);
SQLConnection.closePrepareStatement(pstmt);
}
return result;
}
public int addLoaiSanPham(LoaiSanPham lsp) {
int result = 0;
try {
con = SQLConnection.getConnection();
pstmt = con.prepareStatement(sqlAddLoaiSanPham);
pstmt.setString(1, lsp.getTen());
pstmt.setString(2, lsp.getMoTa());
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
SQLConnection.closeConnection(this.con);
SQLConnection.closePrepareStatement(pstmt);
}
return result;
}
public LoaiSanPham getLoaiSanPhamTheoMa(int maLoaiSanPham) {
this.loaiSanPham = null;
try {
con = SQLConnection.getConnection();
pstmt = con.prepareStatement(sqlGetLoaiSanPhamTheoMa);
pstmt.setInt(1, maLoaiSanPham);
rs = pstmt.executeQuery();
if (rs.next()) {
this.loaiSanPham = new LoaiSanPham(rs.getInt(1), rs.getString(2), rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
SQLConnection.closeConnection(this.con);
SQLConnection.closePrepareStatement(pstmt);
SQLConnection.closeResultSet(rs);
}
return this.loaiSanPham;
}
public int addNhanVien(NhanVien nv) {
int result = 0;
try {
con = SQLConnection.getConnection();
pstmt = con.prepareStatement(sqlAddNhanVien);
pstmt.setString(1, nv.getTen());
pstmt.setString(2, nv.getTenDangNhap());
pstmt.setString(3, nv.getMatKhau());
pstmt.setString(4, nv.getGhiChu());
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
SQLConnection.closeConnection(this.con);
SQLConnection.closePrepareStatement(pstmt);
}
return result;
}
public ArrayList<LoaiSanPham> getTatCaLoaiSanPham() {
this.listLoaiSanPham = new ArrayList<LoaiSanPham>();
try {
con = SQLConnection.getConnection();
pstmt = con.prepareStatement(sqlGetTatCaLoaiSanPham);
rs = pstmt.executeQuery();
while (rs.next()) {
this.loaiSanPham = new LoaiSanPham(rs.getInt(1), rs.getString(2), rs.getString(3));
this.listLoaiSanPham.add(this.loaiSanPham);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
SQLConnection.closeConnection(this.con);
SQLConnection.closePrepareStatement(pstmt);
SQLConnection.closeResultSet(rs);
}
return this.listLoaiSanPham;
}
@PUT
@Path("/{eventId}")
@Consumes(MediaType.APPLICATION_JSON)
public Response updateEvent(Event event, @PathParam("eventId") String eventId) {
if (authenticated) {
Event evt = new Event(event);
String sql =
"UPDATE event SET eventName = '"
+ evt.getEventName()
+ "', eventDescription = "
+ "'"
+ evt.getEventDescription()
+ "', dateFrom = "
+ "'"
+ evt.getDateFrom()
+ "'"
+ ", dateTo = "
+ "'"
+ evt.getDateTo()
+ "'"
+ " WHERE eventId ="
+ eventId;
try {
sqlCon.getStatement().executeUpdate(sql);
return Response.status(Status.ACCEPTED).build();
} catch (Exception e) {
e.printStackTrace();
return Response.status(Status.BAD_REQUEST).build();
} finally {
sqlCon.closeConnection();
}
} else {
return Response.status(Status.FORBIDDEN).build();
}
}
@GET
@Produces(MediaType.APPLICATION_JSON)
public Response getEvents() {
List<Event> events = new ArrayList<Event>();
if (authenticated) {
try {
ResultSet rs = sqlCon.getStatement().executeQuery("SELECT * FROM event");
while (rs.next()) {
Event event = new Event();
long eventId = Long.parseLong(rs.getString("eventId"));
event.setEventId(eventId);
event.setEventName(rs.getString("eventName"));
event.setEventDescription(rs.getString("eventDescription"));
Calendar cal = new GregorianCalendar();
cal.set(2012, Calendar.AUGUST, 15, 15, 30);
// event.setDateFrom(cal);
// event.setDateTo(rs.getString("dateTo"));
events.add(event);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlCon.closeConnection();
}
GenericEntity<List<Event>> entity = new GenericEntity<List<Event>>(events) {};
return Response.status(Status.ACCEPTED).entity(entity).build();
} else {
return Response.status(Status.FORBIDDEN).build();
}
}
private CbsID findExistingPlayer(Integer id) throws SQLException {
CbsID cbsID = new CbsID();
String sql = "SELECT * FROM cbsids WHERE cbs_id = ?";
ResultSet resultSet = connection.prepareAndExecuteStatementFetch(sql, id);
if (resultSet.next()) {
cbsID.initializeFromDBObject(resultSet);
} else {
cbsID.initializeForInsert();
cbsID.cbs_id.changeValue(id);
cbsID.dateAdded.changeValue(localDate.toDate());
cbsID.dateModified.changeValue(localDate.toDate());
}
return cbsID;
}
@Override
public void removeDomainCreatePostCreateSysPermissions(
SQLConnection connection, Resource accessorResource) {
SQLStatement statement = null;
try {
statement =
connection.prepareStatement(
sqlStrings.SQL_removeInGrantDomainCreatePermissionPostCreateSys_BY_AccessorID);
statement.setResourceId(1, accessorResource);
statement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
closeStatement(statement);
}
}
@POST
@Path("/register/{userId}/{eventId}")
// @Consumes(MediaType.APPLICATION_JSON)
public Response registerUserForEvent(
@PathParam("userId") String userId, @PathParam("eventId") String eventId) {
if (authenticated) {
String sql =
"INSERT INTO user_event (userId, eventId) VALUES(" + userId + ", " + eventId + ")";
try {
sqlCon.getStatement().executeUpdate(sql);
return Response.status(Status.ACCEPTED).build();
} catch (SQLException e) {
e.printStackTrace();
return Response.status(Status.BAD_REQUEST).build();
}
} else {
return Response.status(Status.FORBIDDEN).build();
}
}
public UserDAO() {
super();
con = SQLConnection.getOracleConnection();
}
/**
* set catalog in given connection
*
* @param connection the SQLConnection to the database
* @param catalogName name of catalog to set
* @return
* @throws SQLException
*/
public void setCurrentCatalog(SQLConnection connection, String catalogName) throws SQLException {
connection.setCatalog(catalogName);
}
