public boolean alterar(ProdutoPronto pPronto) throws Exception {
boolean status = false;
String sql =
" UPDATE produtopronto SET produtoId=?, encomendaId=?, finalizado=?, dataValidade=?,"
+ " codigo=? where id=?";
PreparedStatement ps = null;
try (Connection conn = ConnectionProvider.getInstance().getConnection()) {
ps = conn.prepareStatement(sql);
ps.setInt(1, pPronto.getProdutoId());
ps.setInt(2, pPronto.getEncomendaId());
ps.setDate(3, Date.valueOf(pPronto.getFinalizado()));
ps.setDate(4, Date.valueOf(pPronto.getDataValidade()));
ps.setString(5, pPronto.getCodigo());
ps.setLong(6, pPronto.getId());
if (ps.executeUpdate() != 0) {
status = true;
}
ps.close();
conn.close();
} catch (SQLException e) {
System.out.println("Erro ao alterar os produtos Prontos\n" + e);
}
return status;
}
@Override
public ProductBean insert(ProductBean bean) {
ProductBean result = null;
try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement stmt = conn.prepareStatement(INSERT); ) {
if (bean != null) {
stmt.setInt(1, bean.getId());
stmt.setString(2, bean.getName());
stmt.setDouble(3, bean.getPrice());
java.util.Date make = bean.getMake();
if (make != null) {
long time = make.getTime();
stmt.setDate(4, new java.sql.Date(time));
} else {
stmt.setDate(4, null);
}
stmt.setInt(5, bean.getExpire());
int i = stmt.executeUpdate();
if (i == 1) {
result = bean;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public int addDevice(Object o) {
int rowsAdded = 0;
try {
Connection connect = conn.use();
Device dev = (Device) o;
String SQL =
"INSERT INTO device(Brand, Model, Serial_Number, Computer_Name, Location, Asset_Tag, Cost, Start_Date, End_Date, Term) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement ps = connect.prepareStatement(SQL);
ps.setString(1, dev.getBrand());
ps.setString(2, dev.getModel());
ps.setString(3, dev.getSerialNumber());
ps.setString(4, dev.getComputerName());
ps.setString(5, dev.getLocation());
ps.setString(6, dev.getAssetTag());
ps.setInt(7, dev.getCost());
ps.setDate(8, dev.getStartDate());
ps.setDate(9, dev.getEndDate());
ps.setInt(10, dev.getTerm());
rowsAdded = ps.executeUpdate();
ps.close();
conn.release(connect);
} catch (SQLException ex) {
System.out.println("Error in add device: ");
Logger.getLogger(DeviceBroker.class.getName()).log(Level.SEVERE, null, ex);
}
return rowsAdded;
}
public License save(License license) throws SQLException {
try (Connection conn = ds.getConnection()) {
PreparedStatement statement =
conn.prepareStatement(
"INSERT INTO License (productId, releaseId, customerId, contractNumber, state, predecessorLicenseId, "
+ "validFrom, validTill, applicationSubmitDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
statement.setInt(1, license.getProduct().getId());
if (license.getRelease() == null) {
statement.setNull(2, java.sql.Types.INTEGER);
} else {
statement.setInt(2, license.getRelease().getId());
}
statement.setInt(3, license.getCustomer().getId());
statement.setString(4, license.getContractNumber());
statement.setInt(5, license.getState().getStateNumber());
statement.setString(6, license.getPredecessorLicenseId());
statement.setDate(7, license.getValidFrom());
statement.setDate(8, license.getValidTill());
// statement.setInt(9, license.getType().getId());
statement.setDate(9, license.getApplicationSubmitDate());
statement.execute();
try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
if (generatedKeys.next()) {
license.setId(generatedKeys.getInt(1));
}
}
}
return license;
}
public void retrieve10MaxTradesStockList(
ErrorObject error, DatabaseClass db, Date start, Date end) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
String sql =
"select ticker, name, isin, currency, "
+ "marketplace, listname, sum(trades) as tradeSum "
+ "from stock_information "
+ "where information_date <= ? and information_date >= ? "
+ "group by ticker, name, isin, currency, marketplace, listname "
+ "order by tradeSum desc ";
this.clear();
try {
conn = DriverManager.getConnection(db.getUrl(), db.getUsr(), db.getPwd());
conn.setAutoCommit(false);
pst = conn.prepareStatement(sql);
long date1 = end.getTime();
java.sql.Date sqlDate1 = new java.sql.Date(date1);
pst.setDate(1, sqlDate1);
long date2 = start.getTime();
java.sql.Date sqlDate2 = new java.sql.Date(date2);
pst.setDate(2, sqlDate2);
rs = pst.executeQuery();
int i = 0;
while (rs.next()) {
if (i < 10) {
StockInformation si = new StockInformation();
si.setTicker(rs.getString(1));
si.setName(rs.getString(2));
si.setIsin(rs.getString(3));
si.setCurrency(rs.getString(4));
si.setMarketplace(rs.getString(5));
si.setListname(rs.getString(6));
si.setPeriodSumTrades(new Integer(rs.getInt(7)));
this.add(si);
i++;
}
}
rs.close();
conn.commit();
pst.close();
conn.close();
} catch (SQLException e) {
System.out.println("DB Error: " + e.getMessage());
error.setError(true);
error.setMessage(e.getMessage());
if (conn != null) {
try {
conn.close();
} catch (SQLException e2) {
}
}
}
}
@Override
public ProductBean update(String name, double price, java.util.Date make, int expire, int id) {
ProductBean result = null;
try ( // Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Connection conn = dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement(UPDATE); ) {
stmt.setString(1, name);
stmt.setDouble(2, price);
if (make != null) {
long time = make.getTime();
stmt.setDate(3, new java.sql.Date(time));
} else {
stmt.setDate(3, null);
}
stmt.setInt(4, expire);
stmt.setInt(5, id);
int i = stmt.executeUpdate();
if (i == 1) {
result = this.select(id);
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public boolean inserir(ProdutoPronto pPronto) throws Exception {
boolean status = false;
String sql =
" INSERT INTO produtopronto (produtoId, encomendaId, finalizado, dataValidade, codigo)"
+ " VALUES (?, ?, ?, ?, ?)";
PreparedStatement ps = null;
try (Connection conn = ConnectionProvider.getInstance().getConnection()) {
ps = conn.prepareStatement(sql);
ps.setInt(1, pPronto.getProdutoId());
if (pPronto.getEncomendaId() != null) {
ps.setInt(2, pPronto.getEncomendaId());
}
ps.setDate(3, Date.valueOf(pPronto.getFinalizado()));
if (pPronto.getDataValidade() != null) {
ps.setDate(4, Date.valueOf(pPronto.getDataValidade()));
}
ps.setString(5, pPronto.getCodigo());
if (ps.executeUpdate() != 0) {
status = true;
}
ps.close();
conn.close();
} catch (SQLException e) {
System.out.println("Erro ao inserir produto Pronto\n" + e);
}
return status;
}
public static Post addMessage(Post post, Connection con) {
try {
DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date date = new Date();
java.sql.Date sqlDate = new java.sql.Date(date.getTime());
// Connection con = Database.getConnection();
PreparedStatement p =
con.prepareStatement("INSERT INTO messages (message,created,author) VALUES(?,?,?)");
p.setString(1, post.getContent());
p.setDate(2, sqlDate);
p.setString(3, post.getContent());
boolean ok = p.execute();
p =
con.prepareStatement(
"select id,created from messages where message = ? and created = ? and author = ?");
p.setString(1, post.getContent());
p.setDate(2, sqlDate);
p.setString(3, post.getContent());
ResultSet resultSet = p.executeQuery();
resultSet.next();
post.setId(resultSet.getInt("id"));
post.setCreated(resultSet.getDate("created"));
resultSet.close();
p.close();
return post;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
}
}
private PreparedStatement prepareFinishedStateStatement(Connection conn) throws SQLException {
final String query =
"update predloha set stavrec=?, findate=?, finuser=?, edidate=?, ediuser=?"
+ " where id=?"
// hack: concurrent modification => update nothing => stavrec='xxx'
+ (oldState != currentState ? " and stavrec='xxx'" : "");
PreparedStatement pstmt = conn.prepareStatement(query);
int col = 1;
String newStateTxt = newState.getDbValue();
StringBuilder sb = new StringBuilder();
sb.append(String.format("column: %s, val: %s\n", col, newStateTxt));
pstmt.setString(col++, newStateTxt);
sb.append(String.format("column: %s, val: %s\n", col, finishDate));
pstmt.setDate(col++, finishDate);
sb.append(String.format("column: %s, val: %s\n", col, finishUser));
pstmt.setString(col++, finishUser);
sb.append(String.format("column: %s, val: %s\n", col, editDate));
pstmt.setDate(col++, editDate);
sb.append(String.format("column: %s, val: %s\n", col, editUser));
pstmt.setString(col++, editUser);
sb.append(String.format("column: %s, val: %s\n", col, recordId));
pstmt.setInt(col++, recordId);
sb.append(query);
LOGGER.fine(sb.toString());
return pstmt;
}
// ---------------------------------------------------------------//
public void addTasktoProject(int projectid, Task task, Connection conn) throws SQLException {
PreparedStatement prepStmt = null;
java.util.Date date = new java.util.Date();
Timestamp currentdate = new Timestamp(date.getTime());
conn = select();
String sql =
"INSERT INTO TASKS(TASK_ID,PROJ_ID,TASK_DESCRIPTION,TASK_NOTES,TASK_DEADLINE,TASK_FROM,TASK_TO,TASK_ACTIVE,TASK_TYPE,TASK_USER_NOTES,ROWVERSION,INSERTED_AT,INSERTED_BY,MODIFIED_AT,MODIFIED_BY)"
+ " VALUES(PROJ_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
prepStmt = conn.prepareStatement(sql);
prepStmt.setInt(1, projectid);
prepStmt.setString(2, task.getTask_DESC());
prepStmt.setString(3, task.getTask_NOTES());
prepStmt.setDate(4, task.getTask_DEADLINE());
prepStmt.setDate(5, task.getTask_STARDATE());
prepStmt.setDate(6, task.getTask_ENDDATE());
if (task.isTask_ACTIVE()) prepStmt.setString(7, "Y");
else prepStmt.setString(7, "N");
prepStmt.setString(8, task.getTask_Type());
prepStmt.setString(9, task.getTask_USERNOTES());
prepStmt.setInt(10, 1);
if (task.getTask_INSERTEDAT() != null) prepStmt.setTimestamp(11, currentdate);
else prepStmt.setDate(11, null);
prepStmt.setString(12, "Grigoris");
if (task.getTask_MODIFIEDAT() != null) prepStmt.setTimestamp(13, currentdate);
else prepStmt.setDate(13, null);
if (task.getTask_MODIFIEDAT() != null) prepStmt.setString(14, "Grigoris");
else prepStmt.setString(14, "Grigoris");
prepStmt.executeUpdate();
return;
}
public void add(GenericVO vo) throws AppException, SysException {
StaffWorkAreaSVO staffWorkArea = (StaffWorkAreaSVO) vo;
StringBuffer sql = new StringBuffer("insert into");
sql.append(
" STAFF_WORK_AREA(STAFF_WORK_AREA_ID,STAFF_ID,WORK_AREA_ID,GRANTOR,ADMIN_FLAG,STS,STS_DATE,CREATE_DATE) values(?,?,?,?,?,?,?,?)");
Connection connection = null;
PreparedStatement ps = null;
try {
connection = ConnectionFactory.getConnection();
ps = connection.prepareStatement(sql.toString());
ps.setString(1, staffWorkArea.getStaffWorkAreaId());
ps.setString(2, staffWorkArea.getStaffId());
ps.setString(3, staffWorkArea.getWorkAreaId());
ps.setString(4, staffWorkArea.getGrantor());
ps.setString(5, staffWorkArea.getAdminFlag());
ps.setString(6, staffWorkArea.getSts());
ps.setDate(7, staffWorkArea.getStsDate());
ps.setDate(8, staffWorkArea.getCreateDate());
ps.execute();
connection.commit();
} catch (SQLException e) {
throw new SysException("", "add error..", e);
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
}
}
}
public void insertUpdateBUCLink(List<BusinessUseCaseLink> bucLinks) {
try {
PreparedStatement bucLinkTableStatement =
LoadFromXML.databaseConnection.prepareStatement(
"MERGE INTO buclinktable(buclinkid, bucid, statusid, startdate, expecteddate, teamid, entrydate, version) KEY(buclinkid) VALUES(?,?,?,?,?,?,?,?)");
for (BusinessUseCaseLink bucLink : bucLinks) {
bucLinkTableStatement.setInt(1, bucLink.getBucLinkId());
bucLinkTableStatement.setInt(2, bucLink.getBusinessUseCase().getBusinessUseCaseId());
bucLinkTableStatement.setInt(3, bucLink.getStatus().getStatusId());
bucLinkTableStatement.setDate(4, new java.sql.Date(bucLink.getStartDate().getTime()));
bucLinkTableStatement.setDate(5, new java.sql.Date(bucLink.getExpectedDate().getTime()));
bucLinkTableStatement.setInt(6, bucLink.getTeam().getTeamId());
bucLinkTableStatement.setDate(7, new java.sql.Date(bucLink.getEntryDate().getTime()));
bucLinkTableStatement.setInt(8, bucLink.getVersion());
try {
bucLinkTableStatement.execute();
} catch (SQLException e) {
logger.severe(
"Failed inserting Business Use Case Link with ID "
+ bucLink.getBucLinkId()
+ ": "
+ e.getMessage());
}
}
bucLinkTableStatement.close();
} catch (SQLException e) {
logger.severe("Failed inserting BUC Links: " + e.getMessage());
}
}
/**
* @param date
* @param vipType 黄钻为0，红钻为1
* @param type 活跃用户为0，新增用户为1
* @param list
* @throws SQLException
*/
private void saveRecord(Date date, int vipType, int type, int[][] list) throws SQLException {
PreparedStatement pstmt;
for (int isYearVip = 0; isYearVip < 2; isYearVip++) {
for (int vipLevel = 0; vipLevel < 8; vipLevel++) {
pstmt =
con.prepareStatement(
"delete from vip_level where date=? and type=? and is_year_vip=? and vip_level=? and vip_type=?");
pstmt.setDate(1, new java.sql.Date(date.getTime()));
pstmt.setInt(2, type);
pstmt.setInt(3, isYearVip);
pstmt.setInt(4, vipLevel);
pstmt.setInt(5, vipType);
pstmt.execute();
pstmt.close();
pstmt =
con.prepareStatement(
"insert into vip_level(date,type,is_year_vip,vip_level,vip_type,count) values(?,?,?,?,?,?)");
pstmt.setDate(1, new java.sql.Date(date.getTime()));
pstmt.setInt(2, type);
pstmt.setInt(3, isYearVip);
pstmt.setInt(4, vipLevel);
pstmt.setInt(5, vipType);
pstmt.setInt(6, list[isYearVip][vipLevel]);
pstmt.execute();
pstmt.close();
}
}
}
@Override
public void Insertar() throws SQLException {
PreparedStatement pstmt = null;
Connection cnx = null;
try {
String sql = "CALL tecnosur.SETCABECERAPAGO(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
cnx = BDAcces.getConeccion();
pstmt = cnx.prepareStatement(sql);
pstmt.setInt(1, this.getIdregpag());
pstmt.setString(2, this.getSerie());
pstmt.setString(3, this.getNumero());
pstmt.setString(4, this.getIdcomprobante());
pstmt.setString(5, this.getIdalumno());
pstmt.setInt(6, this.getIdtipopagante());
pstmt.setDate(7, this.getFechpago());
pstmt.setInt(8, this.getIdorganizacion());
pstmt.setString(9, this.getUsercre());
pstmt.setString(10, this.getWskcre());
pstmt.setDate(11, this.getFechcre());
pstmt.setString(12, this.getUsermod());
pstmt.setString(13, this.getWskmod());
pstmt.setDate(14, this.getFechmod());
pstmt.setInt(15, this.getFlgeli());
pstmt.setString(16, this.getPeriodo());
pstmt.execute();
} finally {
if (pstmt != null) pstmt.close();
}
}
@Override
protected void map(Project project, PreparedStatement stmt) throws SQLException {
// id
stmt.setInt(1, seq.get(Sequence.PROJECT, project.id));
// ref_id
stmt.setString(2, project.id);
// name
stmt.setString(3, project.name);
// description
stmt.setString(4, project.description);
// f_category
if (Category.isNull(project.categoryid)) stmt.setNull(5, java.sql.Types.INTEGER);
else stmt.setInt(5, seq.get(Sequence.CATEGORY, project.categoryid));
// creation_date
stmt.setDate(6, project.creationdate);
// functional_unit
stmt.setString(7, project.functionalunit);
// last_modification_date
stmt.setDate(8, project.lastmodificationdate);
// goal
stmt.setString(9, project.goal);
// f_author
if (project.f_author == null) stmt.setNull(10, java.sql.Types.INTEGER);
else stmt.setInt(10, seq.get(Sequence.ACTOR, project.f_author));
// f_impact_method
stmt.setNull(11, java.sql.Types.INTEGER);
// f_nwset
stmt.setNull(12, java.sql.Types.INTEGER);
stmt.setLong(13, System.currentTimeMillis());
stmt.setLong(14, 4294967296L);
}
private void setParameter(PreparedStatement stmt, List<Object> paramList) throws SQLException {
for (int i = 0; i < paramList.size(); i++) {
Object param = paramList.get(i);
if (param instanceof Arrays) {
List<Object> list = Arrays.asList(param);
for (Object obj : list) {
if (obj instanceof String) {
stmt.setString(i + 1, (String) paramList.get(i));
} else if (obj instanceof Integer) {
stmt.setInt(i + 1, (Integer) paramList.get(i));
} else if (obj instanceof Double) {
stmt.setDouble(i + 1, (Double) paramList.get(i));
} else if (param instanceof Long) {
stmt.setLong(i + 1, (Long) paramList.get(i));
} else if (param instanceof Float) {
stmt.setFloat(i + 1, (Float) paramList.get(i));
} else if (param instanceof Short) {
stmt.setShort(i + 1, (Short) paramList.get(i));
} else if (param instanceof Byte) {
stmt.setByte(i + 1, (Byte) paramList.get(i));
} else if (param instanceof Boolean) {
stmt.setBoolean(i + 1, (Boolean) paramList.get(i));
} else if (param instanceof Date) {
stmt.setDate(i + 1, (Date) paramList.get(i));
} else if (param instanceof Timestamp) {
stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i));
} else if (param instanceof Object) {
stmt.setObject(i + 1, (Object) paramList.get(i));
} else if (param instanceof Arrays) {
stmt.setObject(i + 1, (Object) paramList.get(i));
}
}
}
if (param instanceof String) {
stmt.setString(i + 1, (String) paramList.get(i));
} else if (param instanceof Integer) {
stmt.setInt(i + 1, (Integer) paramList.get(i));
} else if (param instanceof Double) {
stmt.setDouble(i + 1, (Double) paramList.get(i));
} else if (param instanceof Long) {
stmt.setLong(i + 1, (Long) paramList.get(i));
} else if (param instanceof Float) {
stmt.setFloat(i + 1, (Float) paramList.get(i));
} else if (param instanceof Short) {
stmt.setShort(i + 1, (Short) paramList.get(i));
} else if (param instanceof Byte) {
stmt.setByte(i + 1, (Byte) paramList.get(i));
} else if (param instanceof Boolean) {
stmt.setBoolean(i + 1, (Boolean) paramList.get(i));
} else if (param instanceof Date) {
stmt.setDate(i + 1, (Date) paramList.get(i));
} else if (param instanceof Timestamp) {
stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i));
} else if (param instanceof Object) {
stmt.setObject(i + 1, (Object) paramList.get(i));
} else if (param instanceof Arrays) {
stmt.setObject(i + 1, (Object) paramList.get(i));
}
}
}
/*
* Add a computer to the database, and return the id auto incremented of the
* computer added
*/
public Long add(Computer computer) throws SQLException {
logger.debug("Enterring add in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
Long id = null;
String query =
"INSERT INTO `computer-database-db`.`computer` (name,introduced,discontinued,company_id) VALUES (?,?,?,?);";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, computer.getName());
if (computer.getIntroduced() == null) {
statement.setDate(2, null);
} else statement.setDate(2, new java.sql.Date(computer.getIntroduced().getMillis()));
if (computer.getDiscontinued() == (null)) {
statement.setDate(3, null);
} else statement.setDate(3, new java.sql.Date(computer.getDiscontinued().getMillis()));
if (computer.getCompany().getId().equals((0L))) {
statement.setString(4, null);
} else statement.setLong(4, computer.getCompany().getId());
statement.executeUpdate();
ResultSet resultSet = null;
resultSet = statement.getGeneratedKeys();
if (resultSet != null) {
resultSet.next();
id = Long.parseLong(resultSet.getString(1));
}
if (statement != null) statement.close();
if (resultSet != null) resultSet.close();
logger.debug("Leaving add in ComputerDAO.");
return id;
}
// ---------------------------------------------------------------//
public void updateTasktoProject(Task task, Connection conn) throws SQLException {
PreparedStatement prepStmt = null;
java.util.Date date = new java.util.Date();
Timestamp currentdate = new Timestamp(date.getTime());
conn = select();
String sql =
"UPDATE TASKS SET TASK_DESCRIPTION=?,TASK_NOTES=?,TASK_DEADLINE=?,TASK_FROM=?,TASK_TO=?,TASK_ACTIVE=?,TASK_TYPE=?,TASK_USER_NOTES=?,ROWVERSION=+ROWVERSION+1,INSERTED_AT=?,INSERTED_BY=?,MODIFIED_AT=?,MODIFIED_BY=?"
+ " WHERE TASK_ID=?";
prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, task.getTask_DESC());
prepStmt.setString(2, task.getTask_NOTES());
prepStmt.setDate(3, task.getTask_DEADLINE());
prepStmt.setDate(4, task.getTask_STARDATE());
prepStmt.setDate(5, task.getTask_ENDDATE());
if (task.isTask_ACTIVE()) prepStmt.setString(6, "Y");
else prepStmt.setString(6, "N");
prepStmt.setString(7, task.getTask_Type());
prepStmt.setString(8, task.getTask_USERNOTES());
if (task.getTask_INSERTEDAT() != null) prepStmt.setTimestamp(9, currentdate);
else prepStmt.setDate(9, null);
prepStmt.setString(10, "Grigoris");
if (task.getTask_MODIFIEDAT() != null) prepStmt.setTimestamp(11, currentdate);
else prepStmt.setDate(11, null);
prepStmt.setString(12, "Grigoris");
prepStmt.setInt(13, task.getTask_ID());
prepStmt.executeUpdate();
return;
}
@Override
public void update(Connection conn, StatsRecordDAO r) throws SQLException {
if (r.getWord() == null) return;
PreparedStatement updateWords =
conn.prepareStatement(
"UPDATE words SET d = ?, nick = ?, word = ?, "
+ "repetitions = (repetitions+1) WHERE d = ? AND nick = ? "
+ "AND word = ?");
updateWords.setDate(1, new java.sql.Date(r.getDate().getTime()));
updateWords.setString(2, r.getNick());
updateWords.setString(3, r.getWord());
updateWords.setDate(4, new java.sql.Date(r.getDate().getTime()));
updateWords.setString(5, r.getNick());
updateWords.setString(6, r.getWord());
int ret = updateWords.executeUpdate();
updateWords.close();
if (ret == 0) {
PreparedStatement insertWords = conn.prepareStatement("INSERT INTO words values(?, ?, ?, 1)");
insertWords.setDate(1, new java.sql.Date(r.getDate().getTime()));
insertWords.setString(2, r.getNick());
insertWords.setString(3, r.getWord());
insertWords.executeUpdate();
insertWords.close();
}
}
/**
* Fetch the tweets from the db that are between the time frame.
*
* @param startDate
* @param endDate
* @return Returns all the tweets between startDate and endDate
*/
private List<SimpleTweet> fetchTweetsFromDatabase(Date startDate, Date endDate) {
ArrayList<SimpleTweet> results = new ArrayList<SimpleTweet>();
try {
Connection connection = StorageManager.getInstance().getConnection();
PreparedStatement statement =
connection.prepareStatement("SELECT * FROM TWEETS WHERE CREATED BETWEEN ? AND ?");
statement.setDate(1, new java.sql.Date(startDate.getTime()));
statement.setDate(2, new java.sql.Date(endDate.getTime()));
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
// creates a tweet to store the result, using the columns 1 -> tweetId, 2 -> userId, 3 ->
// content, 4 -> createdAt
SimpleTweet tweet =
new SimpleTweet(
resultSet.getString(3),
resultSet.getLong(1),
resultSet.getLong(2),
resultSet.getDate(4));
results.add(tweet);
}
} catch (SQLException e) {
e.printStackTrace();
}
return results;
}
/**
* add an notification
*
* @param notification an object of Notification which will be inserted into the table
* @return a boolean value of the operating result
*/
public boolean addNotification(Notification notification) {
if (DBConnection.conn == null) {
DBConnection.openConn();
}
try {
String sql = "insert into CorrectionNotification values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
ps = DBConnection.conn.prepareStatement(sql);
ps.setString(1, notification.getId());
ps.setString(2, notification.getTitle());
ps.setString(3, notification.getContent());
ps.setString(4, notification.getPublishDept());
ps.setString(5, notification.getAccordingTo());
ps.setInt(6, notification.getHasImgs());
ps.setInt(7, notification.getHasVedio());
ps.setInt(8, notification.getHasText());
ps.setString(9, notification.getImgPath());
ps.setString(10, notification.getVideoPath());
ps.setDate(11, notification.getPublishDate());
ps.setDate(12, notification.getDeadline());
ps.setInt(13, notification.getIsFeedback());
ps.setString(14, notification.getConstructionName());
ps.setString(15, notification.getFeedbackId());
ps.setInt(16, notification.getStatus());
ps.executeUpdate();
DBConnection.closeStatement(ps);
DBConnection.closeConn();
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
/**
* Returns the number of a specific employee's sick days on a specific week.
*
* <p>Saturday and Sunday cannot be sick day.
*
* <p>Work day is overwritten by a sick day, sick days are overwritten by free days, <b>unless
* it's weekend, then it will not count as neither work day/sick day nor day off</b>
*
* @param id the employee's id
* @param date the date that provides the week (can be any day on that week) we want to analyze
* @return the number of sick days
*/
public static int getSickDaysOnAWeekByEmployeeId(int id, Date date) {
LocalDate startDate = new LocalDate(date);
int dayOfWeek = startDate.getDayOfWeek();
startDate = startDate.minusDays(dayOfWeek - 1);
LocalDate endDate = startDate.plusDays(6);
List<WorkSession> sickDaysTemp = new ArrayList<>();
List<WorkSession> dayOffsTemp = new ArrayList<>();
try (PreparedStatement pstmt =
ConnectionHelper.getConnection()
.prepareStatement(
"select session_date, type, sum(duration) duration from worksessions"
+ " where employee_id = ? and session_date between ? and ? group by session_date, type")) {
pstmt.setInt(1, id);
pstmt.setDate(2, new Date(startDate.toDate().getTime()));
pstmt.setDate(3, new Date(endDate.toDate().getTime()));
try (ResultSet rset = pstmt.executeQuery()) {
int temp_id = 1;
while (rset.next()) {
WorkSession ws =
new WorkSession(
temp_id++,
id,
rset.getDate("session_date"),
(short) rset.getInt("duration"),
rset.getString("type"));
switch (ws.getType()) {
case WORK:
break;
case SICKNESS:
sickDaysTemp.add(ws);
break;
case DAY_OFF:
dayOffsTemp.add(ws);
break;
default:
break;
}
}
}
} catch (SQLException | IOException e) {
logger.error("Error while retrieving sickdays on a week by employeeid", e);
throw new PersistentLayerException(e);
}
List<WorkSession> sickDays = new ArrayList<>();
for (WorkSession sickDay : sickDaysTemp) {
boolean contains = false;
for (WorkSession dayOff : dayOffsTemp) {
if (sickDay.getDate().equals(dayOff.getDate())) {
contains = true;
}
}
LocalDate ldate = new LocalDate(sickDay.getDate());
if (!contains && ldate.getDayOfWeek() < 6) {
sickDays.add(sickDay);
}
}
return sickDays.size();
}
public void save(Connection con, JUser juser) throws Exception {
PreparedStatement pstmt = null;
java.sql.Timestamp now = new java.sql.Timestamp(System.currentTimeMillis());
String sql = null;
int idx = 1;
if (JUtil.convertNull(this.getGroup_id()).equals("")) {
sql =
" insert into t_ym_book(group_id, group_name, inquire_date, group_no, contact,"
+ " contact_phone, contact_handset, contact_fax, countries, countries_name, group_type, "
+ " memo, company_id, zxgw_id, zxgw_name, take_out, creater, createrName, created) "
+ " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
pstmt = con.prepareStatement(sql);
this.setGroup_id(JUtil.createUNID());
pstmt.setString(idx++, this.getGroup_id());
pstmt.setString(idx++, this.getGroup_name());
pstmt.setDate(idx++, this.getInquire_date());
pstmt.setString(idx++, this.getGroup_no());
pstmt.setString(idx++, this.getContact());
pstmt.setString(idx++, this.getContact_phone());
pstmt.setString(idx++, this.getContact_handset());
pstmt.setString(idx++, this.getContact_fax());
pstmt.setString(idx++, this.getCountries());
pstmt.setString(idx++, this.getCountries_name());
pstmt.setInt(idx++, this.getGroup_type());
pstmt.setString(idx++, this.getMemo());
pstmt.setString(idx++, this.getCompany_id());
pstmt.setString(idx++, this.getZxgw_id());
pstmt.setString(idx++, this.getZxgw_name());
pstmt.setString(idx++, this.getTake_out());
pstmt.setString(idx++, juser.getUserId());
pstmt.setString(idx++, juser.getUserName());
pstmt.setTimestamp(idx++, now);
pstmt.executeUpdate();
pstmt.close();
} else {
sql =
" update t_ym_book set group_name = ?, inquire_date = ?, memo = ?, "
+ " group_no = ?, contact = ?, contact_phone = ?, contact_handset = ?, contact_fax = ?,"
+ " countries = ?, countries_name = ?, zxgw_id = ?, zxgw_name = ?, take_out = ? "
+ " where group_id = ? ";
pstmt = con.prepareStatement(sql);
pstmt.setString(idx++, this.getGroup_name());
pstmt.setDate(idx++, this.getInquire_date());
pstmt.setString(idx++, this.getMemo());
pstmt.setString(idx++, this.getGroup_no());
pstmt.setString(idx++, this.getContact());
pstmt.setString(idx++, this.getContact_phone());
pstmt.setString(idx++, this.getContact_handset());
pstmt.setString(idx++, this.getContact_fax());
pstmt.setString(idx++, this.getCountries());
pstmt.setString(idx++, this.getCountries_name());
pstmt.setString(idx++, this.getZxgw_id());
pstmt.setString(idx++, this.getZxgw_name());
pstmt.setString(idx++, this.getTake_out());
pstmt.setString(idx++, this.getGroup_id());
pstmt.executeUpdate();
pstmt.close();
}
}
public boolean addNewCredit(int cid, Credit credit) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@oracle.cise.ufl.edu:1521:orcl";
String sql1 = "select max(ID) from Address";
String sql2 = "insert into address values(?,?,?,?,?,?,?,?,?,?,?)";
String sql3 = "insert into CreditCard values(?,?,?,?,?,?)";
String sql4 = "insert into CustomerCredit values(?,?)";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (java.lang.ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
Connection con = DriverManager.getConnection(url, "dawei", "jolly900513");
Statement stmt = con.createStatement();
// stmt.executeUpdate(sql3);
ResultSet rs = stmt.executeQuery(sql1);
int ID = 0; // address id
while (rs.next()) {
ID = rs.getInt(1) + 1;
}
PreparedStatement ps = con.prepareStatement(sql2);
ps.setInt(1, ID);
ps.setString(2, credit.getFirstname());
ps.setString(3, credit.getLastname());
ps.setString(4, credit.getCompany());
ps.setString(5, credit.getState());
ps.setString(6, credit.getPhonenumber());
ps.setInt(7, credit.getZipcode());
ps.setDate(9, new Date(System.currentTimeMillis()));
ps.setString(8, credit.getCity());
ps.setString(10, credit.getAddressline1());
ps.setString(11, credit.getAddressline2());
System.out.println(sql2);
ps.executeUpdate();
ps = con.prepareStatement(sql3);
ps.setLong(1, credit.getCardNumber());
ps.setString(2, credit.getNameOnCard());
ps.setInt(3, credit.getCaredCVV2());
ps.setDate(4, credit.getExperionDate());
ps.setString(5, credit.getCardType());
ps.setInt(6, ID);
System.out.println(sql3);
ps.executeUpdate();
ps = con.prepareStatement(sql4);
System.out.println(sql4);
ps.setInt(1, cid);
ps.setLong(2, credit.getCardNumber());
ps.executeUpdate();
stmt.close();
con.close();
return true;
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
return false;
}
/** Inserts a new row in the project_phases table. */
public ProjectPhasesPk insert(ProjectPhases dto) throws ProjectPhasesDaoException {
long t1 = System.currentTimeMillis();
// declare variables
final boolean isConnSupplied = (userConn != null);
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// get the user-specified connection or get a connection from the ResourceManager
conn = isConnSupplied ? userConn : ResourceManager.getConnection();
stmt = conn.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
int index = 1;
stmt.setInt(index++, dto.getIdprojectPhases());
stmt.setString(index++, dto.getName());
stmt.setString(index++, dto.getDescription());
stmt.setDate(
index++,
dto.getStartDate() == null ? null : new java.sql.Date(dto.getStartDate().getTime()));
stmt.setDate(
index++, dto.getEndDate() == null ? null : new java.sql.Date(dto.getEndDate().getTime()));
if (dto.isStatusNull()) {
stmt.setNull(index++, java.sql.Types.INTEGER);
} else {
stmt.setInt(index++, dto.getStatus());
}
if (dto.isProjectIdNull()) {
stmt.setNull(index++, java.sql.Types.INTEGER);
} else {
stmt.setInt(index++, dto.getProjectId());
}
System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto);
int rows = stmt.executeUpdate();
long t2 = System.currentTimeMillis();
System.out.println(rows + " rows affected (" + (t2 - t1) + " ms)");
// retrieve values from auto-increment columns
rs = stmt.getGeneratedKeys();
if (rs != null && rs.next()) {
dto.setIdprojectPhases(rs.getInt(1));
}
reset(dto);
return dto.createPk();
} catch (Exception _e) {
_e.printStackTrace();
throw new ProjectPhasesDaoException("Exception: " + _e.getMessage(), _e);
} finally {
ResourceManager.close(stmt);
if (!isConnSupplied) {
ResourceManager.close(conn);
}
}
}
// Creating coupon in DB
@Override
public void createCoupon(Coupon c) throws CoupSQLException {
String type = null;
String title = null;
// Check if title is empty.
if (c.getTitle() == null || c.getTitle().trim() == "") {
throw new CoupSQLException("Please Enter Coupon Title.");
} else {
title = c.getTitle();
}
// Setting CouponType. Default setting of CouponType is 'OTHER'
if (c.getType() != null) {
type = c.getType().name();
} else {
type = CouponType.OTHER.name();
}
String message = c.getMessage();
String image = c.getImage();
int amount = c.getAmount();
java.sql.Date startDate = c.getStartDate();
java.sql.Date endDate = c.getEndDate();
double price = c.getPrice();
String sql = "INSERT INTO ";
Connection conn = ConnectionPool.getInstance().getConnection();
PreparedStatement prpst;
try {
prpst =
conn.prepareStatement(
sql
+ "CouponsL (Title , Message , Image, Amount, startDate, endDate, Price, Type) VALUES(?,?,?,?,?,?,?,?) ");
prpst.setString(1, title);
prpst.setString(2, message);
prpst.setString(3, image);
prpst.setInt(4, amount);
prpst.setDate(5, startDate);
prpst.setDate(6, endDate);
prpst.setDouble(7, price);
prpst.setString(8, type);
prpst.executeUpdate();
PreparedStatement prpst2 =
conn.prepareStatement("SELECT id FROM CouponsL WHERE Title='" + title + "'");
ResultSet rs = prpst2.executeQuery();
while (rs.next()) {
long idnum = rs.getLong("id");
c.setId(idnum);
}
} catch (SQLException e) {
throw new CoupSQLException(
"Error: Coupon was not created. Remember, the coupon title has to be unique. " + e);
}
}
/* (non-Javadoc)
* @see org.apache.ibatis.type.TypeHandler#setParameter(java.sql.PreparedStatement, int, java.lang.Object, org.apache.ibatis.type.JdbcType)
*/
public void setParameter(PreparedStatement ps, int i, LocalDate localDate, JdbcType jdbcType)
throws SQLException {
if (localDate != null) {
ps.setDate(i, new Date(localDate.toDateTimeAtStartOfDay().toDate().getTime()));
} else {
ps.setDate(i, null);
}
}
private void checkPrepareBindExecuteFetchDate(Connection connection) throws Exception {
final String sql0 = "select cast(? as varchar(20)) as c\n" + "from (values (1, 'a'))";
final String sql1 = "select ? + interval '2' day as c from (values (1, 'a'))";
final Date date = Date.valueOf("2015-04-08");
final long time = date.getTime();
PreparedStatement ps;
ParameterMetaData parameterMetaData;
ResultSet resultSet;
ps = connection.prepareStatement(sql0);
parameterMetaData = ps.getParameterMetaData();
assertThat(parameterMetaData.getParameterCount(), equalTo(1));
ps.setDate(1, date);
resultSet = ps.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getString(1), is("2015-04-08"));
ps.setTimestamp(1, new Timestamp(time));
resultSet = ps.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getString(1), is("2015-04-08 00:00:00.0"));
ps.setTime(1, new Time(time));
resultSet = ps.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getString(1), is("00:00:00"));
ps.close();
ps = connection.prepareStatement(sql1);
parameterMetaData = ps.getParameterMetaData();
assertThat(parameterMetaData.getParameterCount(), equalTo(1));
ps.setDate(1, date);
resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
ps.setTimestamp(1, new Timestamp(time));
resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
ps.setObject(1, new java.util.Date(time));
resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
resultSet.close();
ps.close();
connection.close();
}
void gra() throws SQLException {
PreparedStatement ps = getPreparedStatement();
ps.setDate(0, new Date(0)); // Noncompliant - First parameter index is 1
ps.setDouble(3, 0.0); // Compliant - Query of the preparedStatement is unknown
PreparedStatement ps2 = ps;
ps2.setDate(0, new Date(0)); // Noncompliant - First parameter index is 1
ps2.setDouble(3, 0.0); // Compliant - Query of the preparedStatement is unknown
}
public int countReportByUserAndTime(
Transaction transaction, String username, Date startDate, Date endDate) throws IOException {
// TODO: STUB CODE, MUST MODIFY, DELETE THIS LINE WHEN DONE
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
try {
Calendar day = new GregorianCalendar();
day.setTime(startDate);
day.set(Calendar.HOUR_OF_DAY, 0);
day.set(Calendar.MINUTE, 0);
day.set(Calendar.SECOND, 0);
day.set(Calendar.MILLISECOND, 0);
Calendar nextDay = new GregorianCalendar();
nextDay.setTime(endDate);
nextDay.set(Calendar.HOUR_OF_DAY, 0);
nextDay.set(Calendar.MINUTE, 0);
nextDay.set(Calendar.SECOND, 0);
nextDay.set(Calendar.MILLISECOND, 0);
nextDay.add(Calendar.DAY_OF_MONTH, 1);
Connection connection = transaction.getResource(Connection.class);
prepareStatement = connection.prepareStatement(COUNT_REPORT_BY_USER_AND_TIME);
prepareStatement.setString(1, username);
prepareStatement.setDate(2, new java.sql.Date(day.getTime().getTime()));
prepareStatement.setDate(3, new java.sql.Date(nextDay.getTime().getTime()));
resultSet = prepareStatement.executeQuery();
int result = 0;
if (resultSet.next()) {
result = resultSet.getInt(1);
}
return result;
} catch (SQLException e) {
throw new IOException(e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
if (prepareStatement != null) {
try {
prepareStatement.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
}
}
}
}
