List of usage examples for java.sql Statement RETURN_GENERATED_KEYS
int RETURN_GENERATED_KEYS
To view the source code for java.sql Statement RETURN_GENERATED_KEYS.
Click Source Link
From source file:br.com.cobranca.util.Util.java
public static <T> int inserirRegistro(T obj, Connection con) throws Exception { int id = 0;/* ww w .ja v a 2s.c o m*/ String nomeTabela = obj.getClass().getSimpleName(); String strSql = "INSERT INTO " + nomeTabela.toUpperCase() + " ("; boolean usarVirgula = false; for (Field field : obj.getClass().getDeclaredFields()) { field.setAccessible(true); if (usarVirgula) { strSql = strSql + ", "; } strSql = strSql + field.getName(); if (!usarVirgula) { usarVirgula = true; } } strSql = strSql + ") VALUES ("; usarVirgula = false; for (Field field : obj.getClass().getDeclaredFields()) { field.setAccessible(true); if (usarVirgula) { strSql = strSql + ", "; } strSql = strSql + "?"; if (!usarVirgula) { usarVirgula = true; } } strSql = strSql + ")"; PreparedStatement ps = con.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS); try { int i = 1; for (Field field : obj.getClass().getDeclaredFields()) { String tipoColuna = field.getType().getSimpleName(); if (tipoColuna.toUpperCase().contains("INT")) { tipoColuna = "Int"; } else { tipoColuna = StringPrimeiraLetraMaiuscula(tipoColuna); } // obj . get + nome do campo Method met = obj.getClass().getMethod("get" + StringPrimeiraLetraMaiuscula(field.getName())); if (tipoColuna.equals("Int")) { Integer valor = (Integer) met.invoke(obj); if (valor == null) { ps.setString(i, null); } else { ps.setInt(i, valor); } } else if (tipoColuna.equals("String")) { String valor = (String) met.invoke(obj); ps.setString(i, valor); } else if (tipoColuna.equals("Double")) { Double valor = (Double) met.invoke(obj); if (valor == null) { ps.setString(i, null); } else { ps.setDouble(i, valor); } } else if (tipoColuna.equals("Float")) { Float valor = (Float) met.invoke(obj); if (valor == null) { ps.setString(i, null); } else { ps.setFloat(i, valor); } } else if (tipoColuna.equals("Long")) { Long valor = (Long) met.invoke(obj); if (valor == null) { ps.setString(i, null); } else { ps.setLong(i, valor); } } else if (tipoColuna.equals("Boolean")) { Boolean valor = (Boolean) met.invoke(obj); if (valor == null) { ps.setString(i, null); } else { ps.setBoolean(i, valor); } } else if (tipoColuna.equals("Date")) { Date valor = (Date) met.invoke(obj); if (valor == null) { ps.setString(i, null); } else { ps.setDate(i, new java.sql.Date(valor.getTime())); } } else { return 0; } i++; } int qtdLinhasAfetadas = ps.executeUpdate(); if (qtdLinhasAfetadas > 0) { try (ResultSet rs = ps.getGeneratedKeys()) { if (rs.next()) { id = rs.getInt(1); } } } } catch (Exception ex) { throw new Exception(ex.getMessage()); } finally { ps.close(); } return id; }
From source file:org.springframework.jdbc.core.simple.AbstractJdbcInsert.java
/** * Create a PreparedStatement to be used for an insert operation with generated keys. * @param con the Connection to use//from w ww.j a v a 2 s. co m * @return the PreparedStatement */ private PreparedStatement prepareStatementForGeneratedKeys(Connection con) throws SQLException { if (getGeneratedKeyNames().length < 1) { throw new InvalidDataAccessApiUsageException("Generated Key Name(s) not specified. " + "Using the generated keys features requires specifying the name(s) of the generated column(s)."); } PreparedStatement ps; if (this.tableMetaDataContext.isGeneratedKeysColumnNameArraySupported()) { if (logger.isDebugEnabled()) { logger.debug("Using generated keys support with array of column names."); } ps = con.prepareStatement(getInsertString(), getGeneratedKeyNames()); } else { if (logger.isDebugEnabled()) { logger.debug("Using generated keys support with Statement.RETURN_GENERATED_KEYS."); } ps = con.prepareStatement(getInsertString(), Statement.RETURN_GENERATED_KEYS); } return ps; }
From source file:org.wso2.carbon.device.mgt.core.dao.impl.DeviceDAOImpl.java
@Override public int addEnrollment(Device device, int tenantId) throws DeviceManagementDAOException { Connection conn;// w w w .j a v a2 s .co m PreparedStatement stmt = null; ResultSet rs = null; int enrolmentId = -1; try { conn = this.getConnection(); String sql = "INSERT INTO DM_ENROLMENT(DEVICE_ID, OWNER, OWNERSHIP, STATUS,DATE_OF_ENROLMENT, " + "DATE_OF_LAST_UPDATE, TENANT_ID) VALUES(?, ?, ?, ?, ?, ?, ?)"; stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); stmt.setInt(1, device.getId()); stmt.setString(2, device.getEnrolmentInfo().getOwner()); stmt.setString(3, device.getEnrolmentInfo().getOwnership().toString()); stmt.setString(4, device.getEnrolmentInfo().getStatus().toString()); stmt.setTimestamp(5, new Timestamp(new Date().getTime())); stmt.setTimestamp(6, new Timestamp(new Date().getTime())); stmt.setInt(7, tenantId); stmt.execute(); rs = stmt.getGeneratedKeys(); if (rs.next()) { enrolmentId = rs.getInt(1); } return enrolmentId; } catch (SQLException e) { throw new DeviceManagementDAOException("Error occurred while adding enrolment", e); } finally { DeviceManagementDAOUtil.cleanupResources(stmt, rs); } }
From source file:org.codesearch.commons.database.DBAccessImpl.java
/** * {@inheritDoc}/* w w w.java 2 s . c om*/ */ @Override public synchronized int ensureThatRecordExists(String filePath, String repository) throws DatabaseAccessException { int fileId = getFileIdForFileName(filePath, repository); Connection conn = null; PreparedStatement statement = null; try { if (fileId == -1) { conn = dataSource.getConnection(); // In case no record for this data exists statement = conn.prepareStatement(STMT_CREATE_FILE_RECORD, Statement.RETURN_GENERATED_KEYS); statement.setString(1, filePath); statement.setString(2, repository); statement.execute(); ResultSet generatedKeys = statement.getGeneratedKeys(); generatedKeys.first(); fileId = generatedKeys.getInt(1); } } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex); } finally { if (conn != null) { try { conn.close(); } catch (SQLException ex) { } } if (statement != null) { try { statement.close(); } catch (SQLException ex) { } } } return fileId; }
From source file:com.untzuntz.coredata.QueryRunner.java
private <T> T insert(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException { if (conn == null) { throw new SQLException("Null connection"); }//from www. j av a2 s . co m if (sql == null) { if (closeConn) { close(conn); } throw new SQLException("Null SQL statement"); } if (rsh == null) { if (closeConn) { close(conn); } throw new SQLException("Null ResultSetHandler"); } PreparedStatement stmt = null; T generatedKeys = null; try { stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); this.fillStatement(stmt, params); stmt.executeUpdate(); ResultSet resultSet = stmt.getGeneratedKeys(); generatedKeys = rsh.handle(resultSet); } catch (SQLException e) { this.rethrow(e, sql, params); } finally { close(stmt); if (closeConn) { close(conn); } } return generatedKeys; }
From source file:edu.pitt.apollo.db.ApolloDbUtils.java
public int getRunKey(RunSimulationMessage runSimulationMessage) throws ApolloDatabaseException, Md5UtilsException { Authentication auth = runSimulationMessage.getAuthentication(); int userKey = getUserKey(auth.getRequesterId(), auth.getRequesterPassword()); int softwareKey = getSoftwareIdentificationKey(runSimulationMessage.getSoftwareIdentification()); String hash = md5Utils.getMd5(runSimulationMessage); String query = "SELECT id FROM run WHERE md5_hash_of_run_message = ?"; try (Connection conn = datasource.getConnection()) { PreparedStatement pstmt = conn.prepareStatement(query); pstmt.setString(1, hash);/*w ww .ja v a 2s. c om*/ ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return rs.getInt(1); } else { query = "INSERT INTO run (requester_id, software_id, md5_hash_of_run_message) VALUES (?,?,?)"; pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); pstmt.setInt(1, userKey); pstmt.setInt(2, softwareKey); pstmt.setString(3, hash); pstmt.execute(); rs = pstmt.getGeneratedKeys(); rs.next(); return rs.getInt(1); } } catch (SQLException ex) { throw new ApolloDatabaseException("SQLException getting run key: " + ex.getMessage()); } }
From source file:org.dcache.chimera.FsSqlDriver.java
/** * * creates an entry in t_inodes table with initial values. * for optimization, initial value of reference count may be defined. * for newly created files , file size is zero. For directories 512. * * @param id/*from ww w. java 2 s .c o m*/ * @param uid * @param gid * @param mode * @param nlink */ Stat createInode(String id, int type, int uid, int gid, int mode, int nlink, long size) { Timestamp now = new Timestamp(System.currentTimeMillis()); KeyHolder keyHolder = new GeneratedKeyHolder(); _jdbc.update(con -> { PreparedStatement ps = con.prepareStatement( "INSERT INTO t_inodes (ipnfsid,itype,imode,inlink,iuid,igid,isize,iio," + "ictime,iatime,imtime,icrtime,igeneration) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS); ps.setString(1, id); ps.setInt(2, type); ps.setInt(3, mode & UnixPermission.S_PERMS); ps.setInt(4, nlink); ps.setInt(5, uid); ps.setInt(6, gid); ps.setLong(7, size); ps.setInt(8, _ioMode); ps.setTimestamp(9, now); ps.setTimestamp(10, now); ps.setTimestamp(11, now); ps.setTimestamp(12, now); ps.setLong(13, 0); return ps; }, keyHolder); Stat stat = new Stat(); stat.setIno((Long) keyHolder.getKeys().get("inumber")); stat.setId(id); stat.setCrTime(now.getTime()); stat.setGeneration(0); stat.setSize(size); stat.setATime(now.getTime()); stat.setCTime(now.getTime()); stat.setMTime(now.getTime()); stat.setUid(uid); stat.setGid(gid); stat.setMode(mode & UnixPermission.S_PERMS | type); stat.setNlink(nlink); stat.setDev(17); stat.setRdev(13); return stat; }
From source file:uk.co.swlines.cifreader.cif.CIFDatabase.java
public void insertScheduleBulk(ArrayList<CIFSchedule> r) throws LogicException { if (r.size() == 0) return;//from w w w. ja v a 2s . co m try { final StringBuilder schedulesStatement = new StringBuilder( "INSERT INTO schedules_t (train_uid, date_from, date_to," + "runs_mo, runs_tu, runs_we, runs_th, runs_fr, runs_sa, runs_su, bank_hol, status, category, train_identity, headcode, " + "service_code, portion_id, power_type, timing_load, speed, train_class, sleepers, reservations, catering_code, " + "service_branding, stp_indicator, uic_code, atoc_code, ats_code, rsid, bus, train, ship, passenger, " + "oc_b, oc_c, oc_d, oc_e, oc_g, oc_m, oc_p, oc_q, oc_r, oc_s, oc_y, oc_z) VALUES "); final StringBuilder locationsStatement = new StringBuilder( "INSERT INTO locations_t (id, location_order, location_type, " + "tiploc_code, tiploc_instance, arrival, arrival_aftermidnight, public_arrival, public_arrival_aftermidnight, " + "pass, pass_aftermidnight, departure, departure_aftermidnight, public_departure, public_departure_aftermidnight, " + "platform, line, path, engineering_allowance, pathing_allowance, performance_allowance, public_call, " + "actual_call, order_time, public_time, act_a, act_ae, act_bl, act_c, act_d, act_minusd, act_e, act_g, act_h, act_hh, act_k, act_kc, " + "act_ke, act_kf, act_ks, act_l, act_n, act_op, act_or, act_pr, act_r, act_rm, act_rr, act_s, act_t, act_minust, act_tb, " + "act_tf, act_ts, act_tw, act_u, act_minusu, act_w, act_x) VALUES "); final StringBuilder locationsChange = new StringBuilder( "INSERT INTO locations_change_t (schedule_id, location_id, category, " + "train_identity, headcode, service_code, portion_id, power_type, timing_load, speed, train_class, sleepers, " + "reservations, catering_code, service_branding, uic_code, rsid, oc_b, oc_c, oc_d, oc_e, oc_g, oc_m, oc_p, " + "oc_q, oc_r, oc_s, oc_y, oc_z) VALUES " + createInsertPlaceholders(29)); PreparedStatement stmtSchedules = getConnection().prepareStatement( schedulesStatement.append(createInsertPlaceholders(46)).toString(), Statement.RETURN_GENERATED_KEYS); PreparedStatement stmtLocChange = getConnection().prepareStatement(locationsChange.toString()); int locationTotal = 0; for (CIFSchedule schedule : r) { int parameterIndex = 1; stmtSchedules.clearParameters(); stmtSchedules.setString(parameterIndex++, schedule.getUid()); stmtSchedules.setString(parameterIndex++, schedule.getDate_from()); stmtSchedules.setString(parameterIndex++, schedule.getDate_to()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_mo()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_tu()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_we()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_th()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_fr()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_sa()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_su()); if (schedule.getBank_holiday() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getBank_holiday())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getStatus()).trim()); stmtSchedules.setString(parameterIndex++, schedule.getCategory()); stmtSchedules.setString(parameterIndex++, schedule.getTrain_identity()); stmtSchedules.setString(parameterIndex++, schedule.getHeadcode()); if (schedule.getService_code() != null) stmtSchedules.setInt(parameterIndex++, schedule.getService_code()); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.INTEGER); if (schedule.getPortion_id() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getPortion_id())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); stmtSchedules.setString(parameterIndex++, schedule.getPower_type()); stmtSchedules.setString(parameterIndex++, schedule.getTiming_load()); stmtSchedules.setString(parameterIndex++, schedule.getSpeed()); if (schedule.getTrain_class() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getTrain_class())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); if (schedule.getSleepers() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getSleepers())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); if (schedule.getReservations() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getReservations())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); stmtSchedules.setString(parameterIndex++, schedule.getCatering_code()); stmtSchedules.setString(parameterIndex++, schedule.getService_branding()); stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getStp_indicator()).trim()); stmtSchedules.setString(parameterIndex++, schedule.getUic_code()); stmtSchedules.setString(parameterIndex++, schedule.getAtoc_code()); stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getAts()).trim()); stmtSchedules.setString(parameterIndex++, schedule.getRsid()); stmtSchedules.setBoolean(parameterIndex++, schedule.isBus()); stmtSchedules.setBoolean(parameterIndex++, schedule.isTrain()); stmtSchedules.setBoolean(parameterIndex++, schedule.isShip()); stmtSchedules.setBoolean(parameterIndex++, schedule.isPassenger()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_b()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_c()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_d()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_e()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_g()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_m()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_p()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_q()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_r()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_s()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_y()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_z()); stmtSchedules.execute(); locationTotal += schedule.getLocations().size(); ResultSet rs = stmtSchedules.getGeneratedKeys(); while (rs.next()) { schedule.setDatabaseId(rs.getInt(1)); } rs.close(); } stmtSchedules.close(); int parameterIndex = 1; PreparedStatement stmtLocations = getConnection().prepareStatement( locationsStatement.append(createInsertPlaceholdersList(locationTotal, 59)).toString()); for (CIFSchedule schedule : r) { int locationOrder = -1; Integer originDeparture = null, originPublicDeparture = null; CIFLocation origin = schedule.getLocations().get(0); if (origin instanceof CIFLocationOrigin) { originDeparture = ((CIFLocationOrigin) origin).getDeparture(); originPublicDeparture = ((CIFLocationOrigin) origin).getPublic_departure(); } if (originDeparture == null) throw new LogicException(schedule); for (CIFLocation location : schedule.getLocations()) { stmtLocations.setInt(parameterIndex++, schedule.getDatabaseId()); stmtLocations.setInt(parameterIndex++, ++locationOrder); stmtLocations.setString(parameterIndex++, location.getLocationType()); stmtLocations.setString(parameterIndex++, location.getTiploc()); stmtLocations.setString(parameterIndex++, String.valueOf(location.getTiploc_instance()).trim()); Integer orderTime = null; Integer publicTime = null; if (location instanceof CIFLocationArrival && ((CIFLocationArrival) location).getArrival() != null) { CIFLocationArrival locationArrival = (CIFLocationArrival) location; stmtLocations.setInt(parameterIndex++, locationArrival.getArrival()); stmtLocations.setBoolean(parameterIndex++, locationArrival.getArrival() < originDeparture); orderTime = locationArrival.getArrival(); if (locationArrival.getPublic_arrival() != null) { stmtLocations.setInt(parameterIndex++, locationArrival.getPublic_arrival()); stmtLocations.setBoolean(parameterIndex++, originPublicDeparture != null ? locationArrival.getPublic_arrival() < originPublicDeparture : null); publicTime = locationArrival.getPublic_arrival(); } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } if (location instanceof CIFLocationIntermediate && ((CIFLocationIntermediate) location).getPass() != null) { CIFLocationIntermediate locationIntermediate = (CIFLocationIntermediate) location; stmtLocations.setInt(parameterIndex++, locationIntermediate.getPass()); stmtLocations.setBoolean(parameterIndex++, locationIntermediate.getPass() < originDeparture); orderTime = locationIntermediate.getPass(); } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } if (location instanceof CIFLocationDepart && ((CIFLocationDepart) location).getDeparture() != null) { CIFLocationDepart locationDeparture = (CIFLocationDepart) location; stmtLocations.setInt(parameterIndex++, locationDeparture.getDeparture()); stmtLocations.setBoolean(parameterIndex++, locationDeparture.getDeparture() < originDeparture); orderTime = locationDeparture.getDeparture(); if (locationDeparture.getPublic_departure() != null) { stmtLocations.setInt(parameterIndex++, locationDeparture.getPublic_departure()); stmtLocations.setBoolean(parameterIndex++, originPublicDeparture != null ? locationDeparture.getPublic_departure() < originPublicDeparture : null); publicTime = locationDeparture.getPublic_departure(); } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } if (location.getPlatform().length() != 0) stmtLocations.setString(parameterIndex++, location.getPlatform()); else stmtLocations.setNull(parameterIndex++, java.sql.Types.VARCHAR); if (location instanceof CIFLocationDepart && ((CIFLocationDepart) location).getLine().length() != 0) stmtLocations.setString(parameterIndex++, ((CIFLocationDepart) location).getLine()); else stmtLocations.setNull(parameterIndex++, java.sql.Types.VARCHAR); if (location instanceof CIFLocationArrival && ((CIFLocationArrival) location).getPath().length() != 0) stmtLocations.setString(parameterIndex++, ((CIFLocationArrival) location).getPath()); else stmtLocations.setNull(parameterIndex++, java.sql.Types.VARCHAR); if (location instanceof CIFLocationDepart) { CIFLocationDepart locationDeparture = (CIFLocationDepart) location; stmtLocations.setInt(parameterIndex++, locationDeparture.getAllowance_engineering()); stmtLocations.setInt(parameterIndex++, locationDeparture.getAllowance_pathing()); stmtLocations.setInt(parameterIndex++, locationDeparture.getAllowance_performance()); } else { stmtLocations.setInt(parameterIndex++, 0); stmtLocations.setInt(parameterIndex++, 0); stmtLocations.setInt(parameterIndex++, 0); } stmtLocations.setBoolean(parameterIndex++, location.isPublic_call()); stmtLocations.setBoolean(parameterIndex++, location.isActual_call()); // stmtLocations.setInt(parameterIndex++, location.isActual_call() ? ( ? : ) : ((CIFLocationIntermediate) location).getPass()); stmtLocations.setInt(parameterIndex++, orderTime); if (publicTime != null) stmtLocations.setInt(parameterIndex++, publicTime); else stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setBoolean(parameterIndex++, location.isAc_a()); stmtLocations.setBoolean(parameterIndex++, location.isAc_ae()); stmtLocations.setBoolean(parameterIndex++, location.isAc_bl()); stmtLocations.setBoolean(parameterIndex++, location.isAc_c()); stmtLocations.setBoolean(parameterIndex++, location.isAc_d()); stmtLocations.setBoolean(parameterIndex++, location.isAc__d()); stmtLocations.setBoolean(parameterIndex++, location.isAc_e()); stmtLocations.setBoolean(parameterIndex++, location.isAc_g()); stmtLocations.setBoolean(parameterIndex++, location.isAc_h()); stmtLocations.setBoolean(parameterIndex++, location.isAc_hh()); stmtLocations.setBoolean(parameterIndex++, location.isAc_k()); stmtLocations.setBoolean(parameterIndex++, location.isAc_kc()); stmtLocations.setBoolean(parameterIndex++, location.isAc_ke()); stmtLocations.setBoolean(parameterIndex++, location.isAc_kf()); stmtLocations.setBoolean(parameterIndex++, location.isAc_ks()); stmtLocations.setBoolean(parameterIndex++, location.isAc_l()); stmtLocations.setBoolean(parameterIndex++, location.isAc_n()); stmtLocations.setBoolean(parameterIndex++, location.isAc_op()); stmtLocations.setBoolean(parameterIndex++, location.isAc_or()); stmtLocations.setBoolean(parameterIndex++, location.isAc_pr()); stmtLocations.setBoolean(parameterIndex++, location.isAc_r()); stmtLocations.setBoolean(parameterIndex++, location.isAc_rm()); stmtLocations.setBoolean(parameterIndex++, location.isAc_rr()); stmtLocations.setBoolean(parameterIndex++, location.isAc_s()); stmtLocations.setBoolean(parameterIndex++, location.isAc_t()); stmtLocations.setBoolean(parameterIndex++, location.isAc__t()); stmtLocations.setBoolean(parameterIndex++, location.isAc_tb()); stmtLocations.setBoolean(parameterIndex++, location.isAc_tf()); stmtLocations.setBoolean(parameterIndex++, location.isAc_ts()); stmtLocations.setBoolean(parameterIndex++, location.isAc_tw()); stmtLocations.setBoolean(parameterIndex++, location.isAc_u()); stmtLocations.setBoolean(parameterIndex++, location.isAc__u()); stmtLocations.setBoolean(parameterIndex++, location.isAc_w()); stmtLocations.setBoolean(parameterIndex++, location.isAc_x()); if (location instanceof CIFLocationIntermediate) { CIFLocationEnRouteChange change = ((CIFLocationIntermediate) location).getChangeRecord(); if (change != null) { stmtLocChange.setInt(1, schedule.getDatabaseId()); stmtLocChange.setInt(2, locationOrder); stmtLocChange.setString(3, change.getCategory()); stmtLocChange.setString(4, change.getTrain_identity()); stmtLocChange.setString(5, change.getHeadcode()); stmtLocChange.setString(6, change.getService_code()); if (change.getPortion_id() != ' ') stmtLocChange.setString(7, String.valueOf(change.getPortion_id())); else stmtLocChange.setNull(7, java.sql.Types.CHAR); stmtLocChange.setString(8, change.getPower_type()); stmtLocChange.setString(9, change.getTiming_load()); stmtLocChange.setString(10, change.getSpeed()); if (change.getTrain_class() != ' ') stmtLocChange.setString(11, String.valueOf(change.getTrain_class())); else stmtLocChange.setNull(11, java.sql.Types.CHAR); if (change.getSleeper() != ' ') stmtLocChange.setString(12, String.valueOf(change.getSleeper())); else stmtLocChange.setNull(12, java.sql.Types.CHAR); if (change.getReservations() != ' ') stmtLocChange.setString(13, String.valueOf(change.getReservations())); else stmtLocChange.setNull(13, java.sql.Types.CHAR); stmtLocChange.setString(14, change.getCatering_code()); stmtLocChange.setString(15, change.getService_branding()); stmtLocChange.setString(16, change.getUic()); stmtLocChange.setString(17, change.getRsid()); stmtLocChange.setBoolean(18, change.isOc_b()); stmtLocChange.setBoolean(19, change.isOc_c()); stmtLocChange.setBoolean(20, change.isOc_d()); stmtLocChange.setBoolean(21, change.isOc_e()); stmtLocChange.setBoolean(22, change.isOc_g()); stmtLocChange.setBoolean(23, change.isOc_m()); stmtLocChange.setBoolean(24, change.isOc_p()); stmtLocChange.setBoolean(25, change.isOc_q()); stmtLocChange.setBoolean(26, change.isOc_r()); stmtLocChange.setBoolean(27, change.isOc_s()); stmtLocChange.setBoolean(28, change.isOc_y()); stmtLocChange.setBoolean(29, change.isOc_z()); stmtLocChange.addBatch(); } } } } stmtLocChange.executeBatch(); stmtLocations.execute(); stmtLocations.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); System.exit(1); } }
From source file:edu.ku.brc.specify.config.FixDBAfterLogin.java
/** * //from w w w . j a v a2 s . c o m */ public static void fixUserPermissions(final boolean doSilently) { final String FIXED_USER_PERMS = "FIXED_USER_PERMS"; boolean isAlreadyFixed = AppPreferences.getRemote().getBoolean(FIXED_USER_PERMS, false); if (isAlreadyFixed) { return; } String whereStr = " WHERE p.GroupSubClass = 'edu.ku.brc.af.auth.specify.principal.UserPrincipal' "; String whereStr2 = "AND p.userGroupScopeID IS NULL"; String postSQL = " FROM specifyuser su " + "INNER JOIN specifyuser_spprincipal ss ON su.SpecifyUserID = ss.SpecifyUserID " + "INNER JOIN spprincipal p ON ss.SpPrincipalID = p.SpPrincipalID " + "LEFT JOIN spprincipal_sppermission pp ON p.SpPrincipalID = pp.SpPrincipalID " + "LEFT OUTER JOIN sppermission pm ON pp.SpPermissionID = pm.SpPermissionID " + whereStr; String sql = "SELECT COUNT(*)" + postSQL + whereStr2; log.debug(sql); if (BasicSQLUtils.getCountAsInt(sql) < 1) { sql = "SELECT COUNT(*)" + postSQL; log.debug(sql); if (BasicSQLUtils.getCountAsInt(sql) > 0) { return; } } final String updatePermSQL = "DELETE FROM %s WHERE SpPermissionID = %d"; final String updatePrinSQL = "DELETE FROM %s WHERE SpPrincipalID = %d"; sql = "SELECT p.SpPrincipalID, pp.SpPermissionID" + postSQL; log.debug(sql); HashSet<Integer> prinIds = new HashSet<Integer>(); for (Object[] row : query(sql)) { Integer prinId = (Integer) row[0]; if (prinId != null) { prinIds.add(prinId); } Integer permId = (Integer) row[1]; if (permId != null) { update(String.format(updatePermSQL, "spprincipal_sppermission", permId)); update(String.format(updatePermSQL, "sppermission", permId)); log.debug("Removing PermId: " + permId); } } StringBuilder sb1 = new StringBuilder(); for (Integer prinId : prinIds) { update(String.format(updatePrinSQL, "specifyuser_spprincipal", prinId)); update(String.format(updatePrinSQL, "spprincipal", prinId)); log.debug("Removing PrinId: " + prinId); if (sb1.length() > 0) sb1.append(","); sb1.append(prinId.toString()); } log.debug("(" + sb1.toString() + ")"); // Create all the necessary UperPrincipal records // Start by figuring out what group there are and then create one UserPrincipal record // for each one TreeSet<String> nameSet = new TreeSet<String>(); sql = "SELECT su.Name, su.SpecifyUserID, p.userGroupScopeID, p.SpPrincipalID FROM specifyuser su " + "INNER JOIN specifyuser_spprincipal sp ON su.SpecifyUserID = sp.SpecifyUserID " + "INNER JOIN spprincipal p ON sp.SpPrincipalID = p.SpPrincipalID " + "WHERE p.GroupSubClass = 'edu.ku.brc.af.auth.specify.principal.GroupPrincipal'"; String fields = "TimestampCreated, TimestampModified, Version, GroupSubClass, groupType, Name, Priority, Remarks, userGroupScopeID, CreatedByAgentID, ModifiedByAgentID"; String insertSQL = "INSERT INTO spprincipal (" + fields + ") VALUES(?,?,?,?,?,?,?,?,?,?,?)"; String insertSQL2 = "INSERT INTO specifyuser_spprincipal (SpecifyUserID, SpPrincipalID) VALUES(?,?)"; String searchSql = "SELECT " + fields + " FROM spprincipal WHERE SpPrincipalID = ?"; sb1 = new StringBuilder(); PreparedStatement selStmt = null; PreparedStatement pStmt = null; PreparedStatement pStmt2 = null; try { Connection conn = DBConnection.getInstance().getConnection(); pStmt = conn.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS); pStmt2 = conn.prepareStatement(insertSQL2); selStmt = conn.prepareStatement(searchSql); String adtSQL = "SELECT DISTINCT ca.AgentID FROM specifyuser AS su INNER Join agent AS ca ON su.CreatedByAgentID = ca.AgentID"; Integer createdById = BasicSQLUtils.getCount(conn, adtSQL); if (createdById == null) { createdById = BasicSQLUtils.getCount(conn, "SELECT AgentID FROM agent ORDER BY AgentID ASC LIMIT 0,1"); if (createdById == null) { UIRegistry.showError("The permissions could not be fixed because there were no agents."); AppPreferences.shutdownAllPrefs(); DBConnection.shutdownFinalConnection(true, true); return; } } for (Object[] row : query(sql)) { String usrName = (String) row[0]; Integer userId = (Integer) row[1]; Integer collId = (Integer) row[2]; Integer prinId = (Integer) row[3]; nameSet.add(usrName); log.debug("usrName: " + usrName + " prinId: " + prinId); if (sb1.length() > 0) sb1.append(","); sb1.append(prinId.toString()); selStmt.setInt(1, prinId); ResultSet rs = selStmt.executeQuery(); if (rs.next()) { log.debug(String.format("%s - adding UserPrincipal for Collection %d / %d", usrName, rs.getInt(9), collId)); Integer createdByAgentID = (Integer) rs.getObject(10); Integer modifiedByAgentID = (Integer) rs.getObject(11); pStmt.setTimestamp(1, rs.getTimestamp(1)); pStmt.setTimestamp(2, rs.getTimestamp(2)); pStmt.setInt(3, 1); // Version pStmt.setString(4, "edu.ku.brc.af.auth.specify.principal.UserPrincipal"); // GroupSubClass pStmt.setString(5, null); // groupType pStmt.setString(6, rs.getString(6)); // Name pStmt.setInt(7, 80); // Priority pStmt.setString(8, rs.getString(8)); // Remarks pStmt.setInt(9, rs.getInt(9)); // userGroupScopeID pStmt.setInt(10, createdByAgentID != null ? createdByAgentID : createdById); pStmt.setInt(11, modifiedByAgentID != null ? modifiedByAgentID : createdById); // Create UserPrincipal pStmt.executeUpdate(); int newPrinId = BasicSQLUtils.getInsertedId(pStmt); // Join the new Principal to the SpecifyUser record pStmt2.setInt(1, userId); pStmt2.setInt(2, newPrinId); pStmt2.executeUpdate(); } else { // error } rs.close(); } log.debug("(" + sb1.toString() + ")"); AppPreferences.getRemote().putBoolean(FIXED_USER_PERMS, true); } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (pStmt != null) pStmt.close(); if (pStmt2 != null) pStmt2.close(); if (selStmt != null) selStmt.close(); } catch (Exception ex) { } } final StringBuilder sb = new StringBuilder(); for (String nm : nameSet) { if (sb.length() > 0) sb.append('\n'); sb.append(nm); } if (!doSilently) { JTextArea ta = UIHelper.createTextArea(15, 30); ta.setText(sb.toString()); ta.setEditable(false); JEditorPane htmlPane = new JEditorPane("text/html", //$NON-NLS-1$ UIRegistry.getResourceString("FDBAL_PERMFIXEDDESC")); htmlPane.setEditable(false); htmlPane.setOpaque(false); CellConstraints cc = new CellConstraints(); PanelBuilder pb = new PanelBuilder(new FormLayout("f:p:g", "p:g,8px,f:p:g")); pb.add(htmlPane, cc.xy(1, 1)); pb.add(UIHelper.createScrollPane(ta), cc.xy(1, 3)); pb.setDefaultDialogBorder(); CustomDialog dlg = new CustomDialog((Frame) UIRegistry.getMostRecentWindow(), UIRegistry.getResourceString("FDBAL_PERMFIXED"), true, CustomDialog.OK_BTN, pb.getPanel()); dlg.setOkLabel(UIRegistry.getResourceString("CLOSE")); UIHelper.centerAndShow(dlg); } }
From source file:com.pinterest.pinlater.backends.mysql.PinLaterMySQLBackend.java
@Override protected String enqueueSingleJob(String queueName, PinLaterJob job, int numAutoRetries) throws Exception { final long currentTimeMillis = System.currentTimeMillis(); Connection conn = null;//from www.ja v a 2 s . co m PreparedStatement stmt = null; ResultSet rs = null; final ImmutableMap.Entry<String, MySQLDataSources> shard = getRandomEnqueueableShard(); try { conn = shard.getValue().getGeneralDataSource().getConnection(); String jobsTableName = MySQLBackendUtils.constructJobsTableName(queueName, shard.getKey(), job.getPriority()); stmt = conn.prepareStatement(String.format(MySQLQueries.ENQUEUE_INSERT, jobsTableName), Statement.RETURN_GENERATED_KEYS); stmt.setInt(1, PinLaterJobState.PENDING.getValue()); stmt.setInt(2, job.getNumAttemptsAllowed()); stmt.setInt(3, job.getNumAttemptsAllowed()); stmt.setString(4, job.getCustomStatus()); stmt.setTimestamp(5, new Timestamp(currentTimeMillis)); stmt.setTimestamp(6, new Timestamp( job.isSetRunAfterTimestampMillis() ? job.getRunAfterTimestampMillis() : currentTimeMillis)); stmt.setBytes(7, job.getBody()); stmt.executeUpdate(); rs = stmt.getGeneratedKeys(); rs.next(); return new PinLaterJobDescriptor(queueName, shard.getKey(), job.getPriority(), rs.getLong(1)) .toString(); } catch (SQLException e) { boolean shouldRetry = checkExceptionIsRetriable(e, shard.getKey(), "enqueue"); if (shouldRetry && numAutoRetries > 0) { // Retry the enqueue, potentially on a different shard. Stats.incr("enqueue-failures-retry"); return enqueueSingleJob(queueName, job, numAutoRetries - 1); } // Out of retries, throw the exception. Wrap it into a PinLaterException if the exception // is recognized and return the appropriate error code. if (MySQLBackendUtils.isDatabaseDoesNotExistException(e)) { throw new PinLaterException(ErrorCode.QUEUE_NOT_FOUND, "Queue not found: " + queueName); } throw e; } finally { JdbcUtils.closeResultSet(rs); JdbcUtils.closeStatement(stmt); JdbcUtils.closeConnection(conn); } }