List of usage examples for java.sql PreparedStatement executeUpdate
int executeUpdate() throws SQLException;
PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement. From source file:de.static_interface.reallifeplugin.module.stockmarket.database.table.StockUsersTable.java
@Override public void create() throws SQLException { String sql;/*from w w w . ja v a 2 s .co m*/ switch (db.getType()) { case H2: sql = "CREATE TABLE IF NOT EXISTS " + getName() + " (" + "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY," + "amount INT NOT NULL," + "stock_id INT NOT NULL," + "user_id INT NOT NULL," + "FOREIGN KEY (stock_id) REFERENCES " + db.getConfig().getTablePrefix() + StocksTable.TABLE_NAME + "(id) ON UPDATE CASCADE ON DELETE CASCADE," + "FOREIGN KEY (user_id) REFERENCES " + db.getConfig().getTablePrefix() + CorpUsersTable.TABLE_NAME + "(id) ON UPDATE CASCADE ON DELETE CASCADE," + "INDEX stock_id_I (stock_id)," + "INDEX user_id_I (user_id)" + ");"; break; case MYSQL: default: sql = "CREATE TABLE IF NOT EXISTS `" + getName() + "` (" + "`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY," + "`amount` INT NOT NULL," + "`stock_id` INT NOT NULL," + "`user_id` INT NOT NULL," + "FOREIGN KEY (`stock_id`) REFERENCES `" + db.getConfig().getTablePrefix() + StocksTable.TABLE_NAME + "`(`id`) ON UPDATE CASCADE ON DELETE CASCADE," + "FOREIGN KEY (`user_id`) REFERENCES `" + db.getConfig().getTablePrefix() + CorpUsersTable.TABLE_NAME + "`(`id`) ON UPDATE CASCADE ON DELETE CASCADE," + "INDEX `stock_id_I` (`stock_id`)," + "INDEX `user_id_I` (`user_id`)" + ");"; break; } PreparedStatement statement = db.getConnection().prepareStatement(sql); statement.executeUpdate(); statement.close(); }
From source file:ece356.UserDBAO.java
public static void syncSampleData() throws ClassNotFoundException, SQLException, NamingException { Connection con = null;/*from www. ja v a2s .co m*/ PreparedStatement pstmt = null; DoctorData ret; try { con = getConnection(); pstmt = con.prepareStatement("INSERT INTO userType (userType) VALUES ('doctor');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO userType (userType) VALUES ('patient');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO specializationType (specTypeName) VALUES ('surgeon');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO specializationType (specTypeName) VALUES ('family');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO specializationType (specTypeName) VALUES ('cardiologist');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO specializationType (specTypeName) VALUES ('eye');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO specializationType (specTypeName) VALUES ('brain');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO specializationType (specTypeName) VALUES ('ENT');"); pstmt.executeUpdate(); String salt = generateSalt(); pstmt = con.prepareStatement("INSERT INTO user VALUES ('bmsaadat', '" + salt + "', SHA2(CONCAT('" + salt + "', 'password123'), 256), 'Behroz', 'M', 'Saadat', 'behrozsaadat@gmail.com', 1);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO doctor VALUES ('bmsaadat', 2012, 'male');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO doctorSpecialization VALUES ('bmsaadat', 5);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO doctorSpecialization VALUES ('bmsaadat', 4);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO doctorSpecialization VALUES ('bmsaadat', 3);"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO workAddress VALUES (1, 'Glenora Dr', 1329, NULL, 'London', 'Ontario', 'N5X1T6', 'bmsaadat');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO workAddress VALUES (4, 'Glengarry', 123, NULL, 'Sarnia', 'Ontario', 'B6G3D2', 'bmsaadat');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO workAddress VALUES (5, 'Adelaide', 1, NULL, 'London', 'Ontario', 'H6G3D2', 'bmsaadat');"); pstmt.executeUpdate(); salt = generateSalt(); pstmt = con.prepareStatement("INSERT INTO user VALUES ('sabash', '" + salt + "', SHA2(CONCAT('" + salt + "', 'weakPassword'), 256), 'Sabashan', '', 'Ragavan', 'sabes@gmail.com', 1);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO doctor VALUES ('sabash', 1993, 'male');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO doctorSpecialization VALUES ('sabash', 1);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO doctorSpecialization VALUES ('sabash', 4);"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO workAddress VALUES (2, 'Lester St.', 231, NULL, 'Waterloo', 'Ontario', 'N6Y1T2', 'sabash');"); pstmt.executeUpdate(); salt = generateSalt(); pstmt = con.prepareStatement("INSERT INTO user VALUES ('abishek', '" + salt + "', SHA2(CONCAT('" + salt + "', 'weakPassword123'), 256), 'Abishek', '', 'Sisodia', 'abishek@gmail.com', 1);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO doctor VALUES ('abishek', 2000, 'male');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO doctorSpecialization VALUES ('abishek', 6);"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO workAddress VALUES (3, 'Bay St.', 1329, 8, 'Toronto', 'Ontario', 'Y6T6K4', 'abishek');"); pstmt.executeUpdate(); salt = generateSalt(); pstmt = con.prepareStatement( "INSERT INTO user VALUES ('bmsaadat_patient', '" + salt + "', SHA2(CONCAT('" + salt + "', 'weakPassword123'), 256), 'Behroz', 'M', 'Saadat', 'bms_300@gmail.com', 2);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO patient VALUES ('bmsaadat_patient', 'London', 'Ontario');"); pstmt.executeUpdate(); salt = generateSalt(); pstmt = con.prepareStatement("INSERT INTO user VALUES ('sabash_patient', '" + salt + "', SHA2(CONCAT('" + salt + "', 'password123'), 256), 'Sabashan', '', 'Ragavan', 'sabes_patient@gmail.com', 2);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO patient VALUES ('sabash_patient', 'Waterloo', 'Ontario');"); pstmt.executeUpdate(); salt = generateSalt(); pstmt = con.prepareStatement("INSERT INTO user VALUES ('abishek_patient', '" + salt + "', SHA2(CONCAT('" + salt + "', 'password123'), 256), 'Abishek', '', 'Sisodia', 'abishek_patient@gmail.com', 2);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO patient VALUES ('abishek_patient', 'Edmonton', 'Alberta');"); pstmt.executeUpdate(); salt = generateSalt(); pstmt = con.prepareStatement("INSERT INTO user VALUES ('john', '" + salt + "', SHA2(CONCAT('" + salt + "', 'password123'), 256), 'John', '', 'Doe', 'john@gmail.com', 2);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO patient VALUES ('john', 'Toronto', 'Ontario');"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO friend VALUES ('bmsaadat_patient', 'sabash_patient', 0);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO friend VALUES ('bmsaadat_patient', 'abishek_patient', 0);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO friend VALUES ('sabash_patient', 'abishek_patient', 1);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO friend VALUES ('bmsaadat_patient', 'john', 1);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO friend VALUES ('john', 'sabash_patient', 1);"); pstmt.executeUpdate(); pstmt = con.prepareStatement("INSERT INTO friend VALUES ('abishek_patient', 'john', 1);"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (1, 'bmsaadat', 'bmsaadat_patient', '2014-11-01 12:45:34', 3, 'Great Doctor!!!! He did everything for me properly... bleh bleh');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (2, 'bmsaadat', 'bmsaadat_patient', '2014-11-02 12:45:34', 2, 'My second visit was not too great...');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (3, 'bmsaadat', 'bmsaadat_patient', '2014-11-03 12:45:34', 1, 'My third visit was horrendous!');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (4, 'bmsaadat', 'abishek_patient', '2014-11-04 12:45:34', 5, 'Good job doctor');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (5, 'bmsaadat', 'sabash_patient', '2014-11-05 12:45:34', 4, 'Good job doctor behroz!!');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (6, 'sabash', 'bmsaadat_patient', '2014-11-06 12:45:34', 5, 'Great Doctor!!!! He did everything for me properly... bleh bleh');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (7, 'sabash', 'sabash_patient', '2014-11-07 12:45:34', 4, 'YES good JOB');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (8, 'sabash', 'abishek_patient', '2014-11-08 12:45:34', 5, 'My stomach hurts');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (9, 'sabash', 'sabash_patient', '2014-11-09 12:45:34', 3, 'YEAH BUDDY');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (10, 'abishek', 'sabash_patient', '2014-11-01 12:45:34', 1, 'BAD DOCTOR');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (11, 'abishek', 'bmsaadat_patient', '2014-11-02 12:45:34', 1, 'BAD DOCTOR !!!');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (12, 'abishek', 'abishek_patient', '2014-11-03 12:45:34', 1, 'BAD DOCTOR BAD BAD!!');"); pstmt.executeUpdate(); pstmt = con.prepareStatement( "INSERT INTO review VALUES (13, 'abishek', 'abishek_patient', '2014-11-04 12:45:34', 1, 'BAD DOCTOR A!!!');"); pstmt.executeUpdate(); } finally { if (pstmt != null) { pstmt.close(); } if (con != null) { con.close(); } } }
From source file:dao.MykeywordsAddQuery.java
/** * Method is used to insert mykeywords/*from w w w .j av a 2 s . com*/ * @param conn - the connection1 * @param keywords - the keywords * @param loginid - the loginid **/ public void run(Connection conn, String keyword, String loginid) throws BaseDaoException { Long myloginid = new Long(loginid); try { PreparedStatement stmt = conn .prepareStatement("insert into mykeywords values(" + myloginid + ", '" + keyword + "')"); stmt.executeUpdate(); } catch (Exception e) { logger.warn("Error occured while executing MykeywordsAddQuery ", e); throw new BaseDaoException("Error occured while executing MykeywordsAddQuery ", e); } }
From source file:dao.PblogIncrementHitsQuery.java
/** * This method is not called by spring.//from w w w.java 2 s.c o m * * @param conn the connection passed to this. * @param pblogid the pblogid (the loginid of this personal blog) * @exception BaseDaoException */ public void run(Connection conn, String pblogid) throws BaseDaoException { try { PreparedStatement query = conn.prepareStatement( "update low_priority pblog set hits=hits+1 where loginid=" + pblogid + " limit 1"); query.executeUpdate(); } catch (Exception e) { throw new BaseDaoException("Error occured executing pblog update hits", e); } }
From source file:dao.DirectoryDeleteQuery.java
/** * This method deletes directory/*w w w . j a va2 s .c o m*/ * This method can be invoked only either the admins or the authors of this directory * @param conn the connection * @param cid the category id * @param ownerid the id of the owner * @exception BaseDaoException */ public void run(Connection conn, String directoryId) throws BaseDaoException { /** * dont use low_priority for queries that are in transaction */ try { PreparedStatement stmt = conn .prepareStatement("delete from directory where directoryid=" + directoryId + " limit 1"); stmt.executeUpdate(); } catch (Exception e) { throw new BaseDaoException( "Error occured while executing db query, delete from directory where directoryid=" + directoryId, e); } }
From source file:de.static_interface.reallifeplugin.module.contract.database.table.ContractUserOptionsTable.java
@Override public void create() throws SQLException { String sql;//w w w. ja v a2 s .c o m switch (db.getType()) { case H2: sql = "CREATE TABLE IF NOT EXISTS " + getName() + " (" + "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY," + "user_id INT NOT NULL," + "contract_id INT NOT NULL," + "money DOUBLE," + "isCreator BOOLEAN NOT NULL" + "FOREIGN KEY (user_id) REFERENCES " + db.getConfig().getTablePrefix() + ContractUsersTable.TABLE_NAME + "(id) ON UPDATE CASCADE ON DELETE CASCADE," + "FOREIGN KEY (contract_id) REFERENCES " + db.getConfig().getTablePrefix() + ContractsTable.TABLE_NAME + "(id) ON UPDATE CASCADE ON DELETE CASCADE," + "INDEX isCreator_I (isCreator)" + ");"; break; case MYSQL: default: sql = "CREATE TABLE IF NOT EXISTS `" + getName() + "` (" + "`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY," + "`user_id` INT NOT NULL," + "`contract_id` INT NOT NULL," + "`money` DOUBLE," + "`isCreator` BOOLEAN NOT NULL" + "FOREIGN KEY (`user_id`) REFERENCES " + db.getConfig().getTablePrefix() + ContractUsersTable.TABLE_NAME + "(`id`) ON UPDATE CASCADE ON DELETE CASCADE," + "FOREIGN KEY (`contract_id`) REFERENCES " + db.getConfig().getTablePrefix() + ContractsTable.TABLE_NAME + "(`id`) ON UPDATE CASCADE ON DELETE CASCADE," + "INDEX `isCreator_I` (`isCreator`)" + ");"; break; } PreparedStatement statement = db.getConnection().prepareStatement(sql); statement.executeUpdate(); statement.close(); }
From source file:com.flexive.core.LifeCycleInfoImpl.java
/** * Update a tables LifeCycleInfo/*from w w w. j a v a 2 s . c om*/ * * @param table table that contains the lifecycle * @param idField field containing the id * @param id the id to update * @param verField field containing the id (optional) * @param ver the version to update (optional) * @param updateCreated update created by/at as well? * @param throwOnNone throw an exception if no rows were updated? * @throws FxUpdateException if a database field could not be updated */ public static void updateLifeCycleInfo(String table, String idField, String verField, long id, int ver, boolean updateCreated, boolean throwOnNone) throws FxUpdateException { final UserTicket ticket = FxContext.getUserTicket(); Connection con = null; PreparedStatement stmt = null; try { con = Database.getDbConnection(); stmt = con.prepareStatement("UPDATE " + table + " SET MODIFIED_BY=?, MODIFIED_AT=?" + (updateCreated ? ", CREATED_BY=?, CREATED_AT=?" : "") + " WHERE " + idField + "=?" + (verField != null && ver > 0 ? " AND " + verField + "=?" : "")); final long now = System.currentTimeMillis(); stmt.setInt(1, (int) ticket.getUserId()); stmt.setLong(2, now); if (updateCreated) { stmt.setInt(3, (int) ticket.getUserId()); stmt.setLong(4, now); stmt.setLong(5, id); } else stmt.setLong(3, id); if (verField != null && ver > 0) stmt.setInt((updateCreated ? 6 : 4), ver); int iCnt = stmt.executeUpdate(); if (iCnt != 1 && throwOnNone) throw new FxUpdateException("Updating LifeCycleInfo failed. " + iCnt + " rows were updated!"); } catch (SQLException se) { throw new FxUpdateException(LOG, se.getMessage(), se); } finally { Database.closeObjects(LifeCycleInfoImpl.class, con, stmt); } }
From source file:dao.PblogMsgAttrDeleteQuery.java
/** * This method deletes PblogMsgAttr attributes of the user * @param conn - the connection/* w w w .j a va 2 s. c o m*/ * @param loginid - the loginid * @param rid - the rid * @throws BaseDaoException - when error occurs **/ public void run(Connection conn, String loginid, String rid) throws BaseDaoException { try { PreparedStatement stmt = conn.prepareStatement( "delete LOW_PRIORITY from pblogmsgattr where loginid=" + loginid + " and rid=" + rid + ""); stmt.executeUpdate(); } catch (Exception e) { throw new BaseDaoException("Error occured while executing PblogMsgAttrDeleteQuery ", e); } }
From source file:dao.CarryonDeleteEntryQuery.java
/** * This method deletes an entry from carryon * @param conn - the connection/*from w w w. ja v a 2s .co m*/ * @param entryid - the entryid * @param loginid - the loginid * @throws BaseDaoException - when error occurs * Used as part of the transaction, skip LOW_PRIORITY **/ public void run(Connection conn, String entryid, String loginid) throws BaseDaoException { try { PreparedStatement stmt = conn.prepareStatement( "delete from carryon where entryid=" + entryid + " and loginid=" + loginid + " limit 1"); stmt.executeUpdate(); } catch (Exception e) { throw new BaseDaoException("Error occured in CarryonDeleteEntryQuery ", e); } }
From source file:dao.PblogTopicAttrDeleteQuery.java
/** * This method deletes pblog topic attributes of the user * @param conn - the connection//www .ja va 2 s . c o m * @param loginid - the loginid * @throws BaseDaoException - when error occurs **/ public void run(Connection conn, String loginid, String tid) throws BaseDaoException { try { PreparedStatement stmt = conn.prepareStatement( "delete LOW_PRIORITY from pblogtopicattr where loginid=" + loginid + " and tid=" + tid + ""); stmt.executeUpdate(); } catch (Exception e) { throw new BaseDaoException("Error occured while executing pblog query ", e); } }