public void eliminarConfiguracion(int usuario) throws DAOException {
log.info("eliminarConfiguracion(int usuario)");
Connection cons = null;
PreparedStatement stmt = null;
try {
String query = "DELETE FROM cv_servicio_usuario WHERE IDUSUARIO=?";
cons = dataSource.getConnection();
stmt = cons.prepareStatement(query);
stmt.setInt(1, usuario);
stmt.executeUpdate();
query =
"insert into cv_servicio_usuario(idusuario,idservicio,columna,posicion,visible,estado) "
+ "select ?,idservicio,columna,posicion,usuario_visible,usuario_estado "
+ "from cv_servicio_maestro";
stmt = cons.prepareStatement(query);
stmt.setInt(1, usuario);
stmt.executeUpdate();
} catch (SQLException e) {
log.info(e.toString());
throw new DAOException(e.toString());
} catch (Exception e) {
log.info(e.toString());
throw new DAOException(e.toString());
} finally {
closeStatement(stmt);
closeConnection(cons);
}
}
/** Get the Manuscript that contains this particular Folio */
public Manuscript(int folio) throws SQLException {
String query = "select msID from folios where pageNumber=?";
Connection j = null;
PreparedStatement ps = null;
try {
j = DatabaseWrapper.getConnection();
ps = j.prepareStatement(query);
ps.setInt(1, folio);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
this.id = rs.getInt(1);
query = "select city,repository, msIdentifier,archive from manuscript where id=?";
ps = j.prepareStatement(query);
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
this.city = rs.getString(1);
this.repository = rs.getString(2);
this.collection = rs.getString(3);
this.archive = rs.getString(4);
}
}
} finally {
if (j != null) {
DatabaseWrapper.closeDBConnection(j);
DatabaseWrapper.closePreparedStatement(ps);
}
}
}
public int deleteDevice(int dID) {
int rowsDeleted = 0;
try {
Connection connect = conn.use();
String SQLcheck = "SELECT * FROM device WHERE deviceID = " + dID + ";";
PreparedStatement check = connect.prepareStatement(SQLcheck);
if (check.execute()) {
String SQL = "DELETE FROM device WHERE deviceID = " + dID + ";";
PreparedStatement psDelete = connect.prepareStatement(SQL);
rowsDeleted = psDelete.executeUpdate();
psDelete.close();
conn.release(connect);
}
} catch (SQLException ex) {
System.out.println("Error in delete device: ");
Logger.getLogger(DeviceBroker.class.getName()).log(Level.SEVERE, null, ex);
}
return rowsDeleted;
}
/**
* Method called by the Form panel to delete existing data.
*
* @param persistentObject value object to delete
* @return an ErrorResponse value object in case of errors, VOResponse if the operation is
* successfully completed
*/
public Response deleteRecord(ValueObject persistentObject) throws Exception {
PreparedStatement stmt = null;
try {
EmpVO vo = (EmpVO) persistentObject;
// delete from WORKING_DAYS...
stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
stmt.close();
// delete from EMP...
stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
gridFrame.reloadData();
frame.getGrid().clearData();
return new VOResponse(vo);
} catch (SQLException ex) {
ex.printStackTrace();
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
conn.commit();
} catch (SQLException ex1) {
}
}
}
/**
* @param conn
* @param fingerprint
* @param sampleID
* @return
* @throws SQLException
*/
private static void insertSampleSet(Connection conn, Fingerprint fingerprint, Integer sampleID)
throws SQLException {
if (fingerprint.getSampleSetID() == null) {
/*
* Insert whole new SampleSetID.
*/
String query = "INSERT INTO `SampleSets`(`SampleID`) VALUES(?);";
PreparedStatement insertSampleSet =
conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
insertSampleSet.setInt(1, sampleID);
insertSampleSet.execute();
ResultSet rs = insertSampleSet.getGeneratedKeys();
if (rs.next()) {
fingerprint.setSampleSetID(rs.getInt(1));
}
rs.close();
insertSampleSet.close();
} else {
/*
* Insert new SampleID for existing SampleSetID.
*/
String query = "INSERT INTO `SampleSets`(`SampleSetID`,`SampleID`) VALUES(?, ?);";
PreparedStatement insertSampleSet = conn.prepareStatement(query);
insertSampleSet.setInt(1, fingerprint.getSampleSetID());
insertSampleSet.setInt(2, sampleID);
insertSampleSet.execute();
insertSampleSet.close();
}
}
private void btnSuperUserSubmitActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_btnSuperUserSubmitActionPerformed
String IDNO = txtIDNO.getText();
String OldPassword = txtOldPassword.getText();
String NewPassword = txtNewPassword.getText();
String ConfirmPassword = txtConfirmPassword.getText();
String sqlQueryPwd = "select Password from super_user where IDNO=? and Password=?";
String sqlQueryP =
"update super_user set Password='******' where IDNO='" + IDNO + "'";
try {
PreparedStatement ps = x.prepareStatement(sqlQueryPwd);
PreparedStatement ps1 = x.prepareStatement(sqlQueryP);
ps.setString(1, IDNO);
ps.setString(2, OldPassword);
ResultSet rs = ps.executeQuery();
if (rs.next() && NewPassword.equals(ConfirmPassword)) {
ps1.execute(sqlQueryP);
JOptionPane.showMessageDialog(null, "Password successfully changed!");
dispose();
Login L = new Login();
L.setVisible(true);
} else {
JOptionPane.showMessageDialog(null, "Invalid! Cross check!");
}
rs.close();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
} // GEN-LAST:event_btnSuperUserSubmitActionPerformed
public void addAgent(Agent agent) {
try {
conn = JDBC_Conn.getConnection();
String sql = "insert into agents values(?,?,?,?,?,?,?,?)";
stmt = conn.prepareStatement(sql);
stmt.setString(1, agent.getAgent_id());
stmt.setString(2, agent.getPhone_number());
stmt.setString(3, agent.getEmail());
stmt.setString(4, agent.getFirst_nm());
stmt.setString(5, agent.getLast_nm());
stmt.setDate(6, new java.sql.Date(agent.getDob().getTime()));
stmt.setString(7, agent.getZip());
stmt.setString(8, agent.getGender());
stmt.executeUpdate();
sql = "insert into position values(?,?)";
stmt = conn.prepareStatement(sql);
stmt.setString(1, agent.getAgent_id());
stmt.setString(2, agent.getPosition());
stmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
throw new DAOException();
} finally {
JDBC_Conn.releaseConnection(conn, stmt, rs);
}
}
public void checkPOStatus(String poNumber) throws SQLException {
/**
* For retrieving the details of purchase order using poNumber corresponding to particular
* vendorNumber
*
* @param poNumber
* @return
* @throws SQLException
*/
DBConnection dbConnection = new DBConnection();
Connection connection = dbConnection.getConnection();
PreparedStatement preparedStatement =
connection.prepareStatement(Selection.selectPoProductFromPo);
logger.debug("retrieving the details of purchase order using poNumber" + poNumber);
preparedStatement.setString(1, poNumber);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
} else {
PreparedStatement preparedStatement2 = connection.prepareStatement(Selection.updatePoStatus);
logger.debug("updating status CLOSED of PO for particular PO_NO" + poNumber);
preparedStatement2.setString(1, "CLOSED");
preparedStatement2.setString(2, poNumber);
preparedStatement2.execute();
}
}
public void save() throws SQLException {
try (Connection connection = DbHelper.getConnection()) {
if (id == -1) {
Contact.LOGGER.debug("Adding new contact: {}", this);
final String sql = "INSERT INTO contacts(name, contacts) VALUES(?, ?)";
try (PreparedStatement pstmt =
connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, name);
pstmt.setString(2, contacts);
pstmt.execute();
try (final ResultSet rs = pstmt.getGeneratedKeys()) {
rs.next();
id = rs.getLong(1);
}
}
} else {
Contact.LOGGER.debug("Updating existing contact: {}", this);
final String sql = "UPDATE contacts SET name = ?, contacts = ? WHERE id = ?";
try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setString(2, contacts);
pstmt.setLong(3, id);
pstmt.execute();
}
}
}
}
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();
}
}
/**
* fügt einen neuen Kontakt zur Kontaktliste hinzu
*
* @param besitzer Der Besitzer der Kontaktliste
* @param kontakt Der Kontakt, der hinzugefügt werden soll
*/
public void kontaktZurKontaktListeHinzufuegen(String besitzer, String kontakt) {
int kNr = bestimmeBNrBenutzer(kontakt);
int bNr = bestimmeBNrBenutzer(besitzer);
try {
// überprüft, ob der Konktakt noch nicht in der Kontaktliste ist
boolean gefunden = false;
ResultSet rueckgabewert = null;
String query1 = "select count(*) from kontaktliste where besitzer = ? and kontakt = ?";
PreparedStatement anweisung1 = con.prepareStatement(query1);
anweisung1.setString(1, besitzer);
anweisung1.setString(1, kontakt);
rueckgabewert = anweisung1.executeQuery();
// werdet den Rückgabewert aus
while (rueckgabewert.next()) {
gefunden = rueckgabewert.getBoolean(1);
}
if (!gefunden) {
// fügt den Kontakt zur Kontakliste hinzu
String query = "insert into kontaktliste (besitzer, kontakt) values(?, ?);";
PreparedStatement anweisung = con.prepareStatement(query);
anweisung.setInt(1, bNr);
anweisung.setInt(2, kNr);
anweisung.executeUpdate();
}
} catch (SQLException e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(1);
}
}
public String removeItem(String[] idList) throws SQLException {
String sql1 = "delete from sellitem where itemid = ?;";
PreparedStatement stmnt1 = connection.prepareStatement(sql1);
String sql2 = "delete from solditem where itemid = ?;";
PreparedStatement stmnt2 = connection.prepareStatement(sql2);
String sql3 = "delete from userhistory where itemid = ?;";
PreparedStatement stmnt3 = connection.prepareStatement(sql3);
String sql4 = "delete from item where id = ?;";
PreparedStatement stmnt4 = connection.prepareStatement(sql4);
for (int i = 0; i < idList.length; i++) {
stmnt1.setInt(1, Integer.parseInt(idList[i]));
stmnt1.executeUpdate();
stmnt2.setInt(1, Integer.parseInt(idList[i]));
stmnt2.executeUpdate();
stmnt3.setInt(1, Integer.parseInt(idList[i]));
stmnt3.executeUpdate();
stmnt4.setInt(1, Integer.parseInt(idList[i]));
stmnt4.executeUpdate();
}
connection.close();
return "Remove succeed!";
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// This resets the vote counts of the options of a poll
int pollIdToReset = Integer.parseInt(req.getParameter("id"));
try {
Connection c =
DriverManager.getConnection(
Constants.DATABASE_URL, Constants.MYSQL_USERNAME, Constants.MYSQL_PASSWORD);
String setNewPollCount = "update poll set total_count=0 where poll_id =?";
PreparedStatement pstmt4 = c.prepareStatement(setNewPollCount);
pstmt4.setInt(1, pollIdToReset);
pstmt4.executeUpdate();
String setNewPageCount = "update poll_option set vote_count=0 where poll_id =?";
PreparedStatement pstmt = c.prepareStatement(setNewPageCount);
pstmt.setInt(1, pollIdToReset);
pstmt.executeUpdate();
String deleteIps = "delete from poll_ip where poll_id =?";
PreparedStatement pstmt2 = c.prepareStatement(deleteIps);
pstmt2.setInt(1, pollIdToReset);
pstmt2.executeUpdate();
resp.sendRedirect("./Admin");
c.close();
} catch (SQLException e) {
throw new ServletException(e);
}
}
@Override
protected void do_addOrUpdatePlayer(BBPlayerInfo pi) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = ConnectionManager.getConnection();
if (conn == null) return;
if (pi.getNew() && getUserFromDB(pi.getName()) == null) {
/*String statement = "IF EXISTS (SELECT \"name\" FROM \""+getTableName()+"\" WHERE \"name\" = ?) THEN"
+ " UPDATE \""+getTableName()+"\" SET \"flags\" = ? WHERE \"name\" = ?;"
+ " ELSE "
+ " INSERT INTO \""+getTableName()+"\" (name,flags) VALUES(?,?)"
+ " END IF;";
ps = conn.prepareStatement(statement);*/
ps = conn.prepareStatement("INSERT INTO " + getTableName() + " (name,flags) VALUES (?,?)");
ps.setString(1, pi.getName());
ps.setInt(2, pi.getFlags());
} else {
ps = conn.prepareStatement("UPDATE " + getTableName() + " SET flags = ? WHERE id=?");
ps.setInt(1, pi.getFlags());
ps.setInt(2, pi.getID());
}
BBLogging.debug(ps.toString());
ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
BBLogging.severe("Can't update the user `" + pi.getName() + "`.", e);
} finally {
ConnectionManager.cleanup("BBUsersMySQL.do_addOrUpdatePlayer", conn, ps, null);
}
}
public static boolean deleteFromInvite(int meetingId, int id) {
// 通过meeting id和User id撤销对某人的邀请
Connection connection = ConnectionFactory.getConnection();
if (connection == null) return false;
try {
String sql = "select account from user where userid=" + id + "";
PreparedStatement pStatement = connection.prepareStatement(sql);
ResultSet set = pStatement.executeQuery(sql);
while (set.next() == true) {
String sql1 =
"delete from invite where meetingid="
+ meetingId
+ " and account='"
+ set.getString(1)
+ "'";
PreparedStatement ps = connection.prepareStatement(sql1);
int rs = ps.executeUpdate(sql1);
if (rs == 1) return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
public int updateAssignToNode(String node, int i, int numNodes, long maxtime)
throws DatabaseException {
if (__log.isDebugEnabled())
__log.debug(
"updateAsssignToNode node=" + node + " " + i + "/" + numNodes + " maxtime=" + maxtime);
Connection con = null;
PreparedStatement ps = null;
try {
con = getConnection();
if (_dialect == Dialect.SQLSERVER) {
ps = con.prepareStatement(UPGRADE_JOB_SQLSERVER);
} else if (_dialect == Dialect.DB2) {
ps = con.prepareStatement(UPGRADE_JOB_DB2);
} else if (_dialect == Dialect.SYBASE) {
ps = con.prepareStatement(UPGRADE_JOB_SYBASE);
} else if (_dialect == Dialect.SYBASE12) {
ps = con.prepareStatement(UPGRADE_JOB_SYBASE12);
} else {
ps = con.prepareStatement(UPGRADE_JOB_DEFAULT);
}
ps.setString(1, node);
ps.setInt(2, numNodes);
ps.setInt(3, i);
ps.setLong(4, maxtime);
return ps.executeUpdate();
} catch (SQLException se) {
throw new DatabaseException(se);
} finally {
close(ps);
close(con);
}
}
@Override
protected void createSkipColumnTable() throws Exception {
Connection con = getDataSource().getConnection();
String stmt =
"create table "
+ DB2TestUtil.SCHEMA_QUOTED
+ ".\"skipcolumn\" (\"fid\" int generated always as identity (start with 0, increment by 1),"
+ "\"id\" int ,"
+ " \"geom\" DB2GSE.ST_GEOMETRY, "
+ "\"weirdproperty\" XML, "
+ "\"name\" varchar(255), "
+ "primary key (\"fid\"))";
con.prepareStatement(stmt).execute();
DB2Util.executeRegister(DB2TestUtil.SCHEMA, "auto", "geom", DB2TestUtil.SRSNAME, con);
con.prepareStatement(
"INSERT INTO "
+ DB2TestUtil.SCHEMA_QUOTED
+ ".\"skipcolumn\" "
+ "(\"id\",\"geom\",\"weirdproperty\",\"name\") "
+ "VALUES (0, db2gse.st_GeomFromText('POINT(0 0)', "
+ DB2TestUtil.SRID
+ "), null, 'GeoTools')")
.execute();
con.close();
}
private void saveBuildings(int building_id, String customers, Connection conn) throws Exception {
PreparedStatement psDelete = null;
PreparedStatement psAdd = null;
try {
psDelete =
conn.prepareStatement("delete from maps.building_to_customers where building_id=?");
psDelete.setInt(1, building_id);
psDelete.executeUpdate();
if (customers.length() > 0) {
String cusIds[] = customers.split(",");
psAdd =
conn.prepareStatement(
"insert into maps.building_to_customers (building_id,cusid) values (?,?)");
for (String cusId : cusIds) {
psAdd.setInt(1, building_id);
psAdd.setInt(2, new Integer(cusId));
psAdd.addBatch();
}
psAdd.executeBatch();
}
} finally {
try {
psDelete.close();
} catch (Exception e2) {
// TODO: handle exception
}
try {
psAdd.close();
} catch (Exception e2) {
// TODO: handle exception
}
}
}
public String addBillDetails(String poNumber, double totalAmount, double totalQuantity)
throws SQLException {
/**
* For retrieving the details of purchase order using poNumber corresponding to particular
* vendorNumber
*
* @param poNumber
* @return
* @throws SQLException
*/
DBConnection dbConnection = new DBConnection();
Connection connection = dbConnection.getConnection();
String billNo = "";
PreparedStatement ps = connection.prepareStatement("select 'BILL'||BILL_SEQ.nextval FROM dual");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
billNo = rs.getString(1);
System.out.println(billNo);
logger.debug("generating bill no" + billNo);
}
PreparedStatement preparedStatement =
connection.prepareStatement("INSERT INTO BILL_DETAILS VALUES(?,?,?,?)");
logger.debug("inserting values into Bill_Details" + billNo);
preparedStatement.setString(1, billNo);
preparedStatement.setString(2, poNumber);
preparedStatement.setDouble(3, totalAmount);
preparedStatement.setDouble(4, totalQuantity);
preparedStatement.execute();
return billNo;
}
public static String saveSearchResult(String uuid, String building_ids, int srid, Connection conn)
throws Exception {
PreparedStatement psDelete = null;
PreparedStatement psAdd = null;
PreparedStatement psGet = null;
ResultSet rs = null;
try {
psDelete =
conn.prepareStatement(
"delete from cust_search_result where uu_id=? or create_time<now() - cast('1 day' as interval)");
psDelete.setString(1, uuid);
psDelete.executeUpdate();
if (building_ids.length() > 0) {
psAdd =
conn.prepareStatement(
"insert into cust_search_result (buid,uu_id) select distinct building_id,? from maps.building_to_customers "
+ "where building_id = ANY(string_to_array(?,',')::int[])");
psAdd.setString(1, uuid);
psAdd.setString(2, building_ids);
psAdd.executeUpdate();
psGet =
conn.prepareStatement(
"select ST_AsText(ST_transform(ST_Buffer(ST_Envelope (ST_Collect(the_geom)),100),?)) from buildings b"
+ " where buid= ANY(string_to_array(?,',')::int[])");
psGet.setInt(1, srid);
psGet.setString(2, building_ids);
rs = psGet.executeQuery();
if (rs.next()) {
String res = rs.getString(1);
return res;
}
}
return "";
} finally {
try {
psDelete.close();
} catch (Exception e2) {
// TODO: handle exception
}
try {
psAdd.close();
} catch (Exception e2) {
// TODO: handle exception
}
try {
psGet.close();
} catch (Exception e2) {
// TODO: handle exception
}
try {
rs.close();
} catch (Exception e2) {
// TODO: handle exception
}
}
}
public void updateAgent(Agent agent) {
try {
conn = JDBC_Conn.getConnection();
String sql =
"update agents set phone_number=?, email=?, first_nm=?, last_nm=?, dob=?, zip=?, gender=? where agent_id=?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, agent.getPhone_number());
stmt.setString(2, agent.getEmail());
stmt.setString(3, agent.getFirst_nm());
stmt.setString(4, agent.getLast_nm());
stmt.setDate(5, new java.sql.Date(agent.getDob().getTime()));
stmt.setString(6, agent.getZip());
stmt.setString(7, agent.getGender());
stmt.setString(8, agent.getAgent_id());
stmt.executeUpdate();
sql = "update position set position = ? where agent_id=?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, agent.getPosition());
stmt.setString(2, agent.getAgent_id());
stmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
throw new DAOException();
} finally {
JDBC_Conn.releaseConnection(conn, stmt, rs);
}
}
public void copyData(Connection conn) throws SQLException {
ResultSet rs = null;
PreparedStatement stmt = null;
PreparedStatement stmt2 = null;
String q2 = "insert into modmodlinks2 (pModID,cModID,courseID) values (?,?,?)";
try {
String q =
"select parentModID,childModID,courseID from modmodulelinks order by parentModId,childModID";
stmt = conn.prepareStatement(q);
rs = stmt.executeQuery();
while (rs.next()) {
int p = rs.getInt(1);
int c = rs.getInt(2);
int crs = rs.getInt(3);
stmt2 = conn.prepareStatement(q2);
stmt2.setInt(1, p);
stmt2.setInt(2, c);
stmt2.setInt(3, crs);
System.out.println("Insert p=" + p + " c=" + c + " crs=" + crs);
stmt2.executeUpdate();
stmt2.close();
}
} finally {
if (stmt != null) stmt.close();
if (rs != null) rs.close();
}
}
/**
* Method updateOwnerInDB.
*
* @param clan Clan
*/
private void updateOwnerInDB(Clan clan) {
_owner = clan;
Connection con = null;
PreparedStatement statement = null;
try {
con = DatabaseFactory.getInstance().getConnection();
statement =
con.prepareStatement("UPDATE clan_data SET hasFortress=0 WHERE hasFortress=? LIMIT 1");
statement.setInt(1, getId());
statement.execute();
DbUtils.close(statement);
if (clan != null) {
statement =
con.prepareStatement("UPDATE clan_data SET hasFortress=? WHERE clan_id=? LIMIT 1");
statement.setInt(1, getId());
statement.setInt(2, getOwnerId());
statement.execute();
clan.broadcastClanStatus(true, false, false);
}
} catch (Exception e) {
_log.error("", e);
} finally {
DbUtils.closeQuietly(con, statement);
}
}
public void deleteUser(String username) {
if (isReadOnly()) {
// Reject the operation since the provider is read-only
throw new UnsupportedOperationException();
}
Connection con = null;
PreparedStatement pstmt = null;
boolean abortTransaction = false;
try {
// Delete all of the users's extended properties
con = DbConnectionManager.getTransactionConnection();
pstmt = con.prepareStatement(DELETE_USER_PROPS);
pstmt.setString(1, username);
pstmt.execute();
pstmt.close();
// Delete the actual user entry
pstmt = con.prepareStatement(DELETE_USER);
pstmt.setString(1, username);
pstmt.execute();
} catch (Exception e) {
Log.error(e);
abortTransaction = true;
} finally {
DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction);
}
}
/**
* Create a CharacteristicBean.
*
* @param conn A connection to the database.
* @param num_samples The number of samples in the database.
* @param value The value of this sample.
* @return
* @throws SQLException
*/
private static CharacteristicBean getCharacteristicBean(
Connection conn, int num_samples, String dbname, Long value) throws SQLException {
CharacteristicBean chrbean = new CharacteristicBean();
PreparedStatement getCount;
String querystr = "SELECT COUNT(*) FROM `Samples` WHERE `" + dbname + "`";
if (value != null) {
chrbean.setValue(value.toString());
querystr += " = ?;";
getCount = conn.prepareStatement(querystr);
getCount.setLong(1, value);
} else {
chrbean.setValue(NO_JAVASCRIPT);
querystr += " IS NULL;";
getCount = conn.prepareStatement(querystr);
}
ResultSet rs = getCount.executeQuery();
rs.next();
int count = rs.getInt(1);
rs.close();
chrbean.setInX(((double) num_samples) / ((double) count));
chrbean.setBits(Math.abs(Math.log(chrbean.getInX()) / Math.log(2)));
return chrbean;
}
/**
* @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();
}
}
}
public PostgresClientManager() {
try {
connection = DriverManager.getConnection(url, "postgres", "kuba22");
statement = connection.createStatement();
ResultSet rs = connection.getMetaData().getTables(null, null, null, null);
boolean tableExists = false;
while (rs.next()) {
if (rs.getString("TABLE_NAME").equalsIgnoreCase("Client")) {
tableExists = true;
break;
}
}
if (!tableExists) {
statement.executeUpdate(createTable);
}
getAllClient = connection.prepareStatement("" + "Select * From Klient");
deleteClient = connection.prepareStatement("" + "DELETE From Klient where name=?");
getClient = connection.prepareStatement("" + "SELECT * From Klient where id=?");
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public int update(Object obj) {
int result = 0;
try {
conn.setAutoCommit(false);
pstmt =
conn.prepareStatement(
"update THEME_MESSAGE set NAME=?,EMAIL=?,IMAGE=?,TITLE=? "
+ "where THEME_MESSAGE_ID=?");
pstmt =
conn.prepareStatement("update THEME_CONTENT set CONTENT=? " + "where THEME_MESSAGE_ID=?");
pstmt.setString(1, bean.getName());
pstmt.setString(2, bean.getEmail());
pstmt.setString(3, bean.getImage());
pstmt.setString(4, bean.getTitle());
pstmt.setInt(5, bean.getId());
pstmt.executeUpdate();
pstmt.setCharacterStream(1, null);
pstmt.setInt(2, bean.getId());
pstmt.executeUpdate();
conn.commit();
} catch (SQLException ex) {
ex.printStackTrace();
System.out.println("업데이트 에러");
} finally {
}
return result;
}
private void jButton3ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton3ActionPerformed
// TODO add your handling code here:
if (itemcode.getText().length() == 0) {
JOptionPane.showMessageDialog(
this, "Please Input Item Code", "Error", JOptionPane.ERROR_MESSAGE);
itemcode.requestFocus();
return;
}
String sql = "delete from item where item_code=?";
try {
ps = con.prepareStatement(sql);
ps.setString(1, itemcode.getText());
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "Data Delete Successfully");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "Data not Delete");
}
String sqll = "delete from stock where item_code=?";
try {
ps = con.prepareStatement(sqll);
ps.setString(1, itemcode.getText());
ps.executeUpdate();
// JOptionPane.showMessageDialog(null, "Data Delete Successfully");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "Data not Delete");
}
} // GEN-LAST:event_jButton3ActionPerformed
public static boolean saveDetalle(Detalle a) {
try {
conn = ds.getConnection();
stmt = conn.prepareStatement("call savedetalle(?,?,?,?,?)");
Date fech = new Date(a.getFecha().getTime());
stmt.setInt(1, a.getFormaDePago().getId());
stmt.setInt(2, a.getUsuario().getIdUsuario());
stmt.setDate(3, fech);
stmt.setDouble(4, a.getTotal());
stmt.setTime(5, new java.sql.Time(a.getHora().toDateTimeToday().getMillis()));
rs = stmt.executeQuery();
while (rs.next()) {
a.setId(rs.getInt(1));
}
stmt = conn.prepareStatement("call saveproductosventa(?,?)");
for (Producto x : a.getProductos()) {
stmt.setInt(1, a.getId());
stmt.setInt(2, x.getIdProducto());
stmt.execute();
}
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}