Java Code Examples for java.sql.PreparedStatement#getGeneratedKeys()

public static int addApplication(String name, int subscriberId) {
    int applicationId = -1;
    String INSERT_APPLICATIONS_SQL = "INSERT INTO AM_APPLICATION (NAME,SUBSCRIBER_ID) VALUES (?,?)";
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = DriverManager.getConnection(URL, USER, PASS);
        stmt = conn.prepareStatement(INSERT_APPLICATIONS_SQL, new String[]{"APPLICATION_ID"});
        stmt.setString(1, name);
        stmt.setInt(2, subscriberId);
        stmt.execute();
        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            applicationId = rs.getInt(1);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, stmt, rs);
    }
    return applicationId;
}
 

public static int addSubscription(int applicationId, int apiId) {
    int subscriptionId = -1;
    String INSERT_SUBSCRIPTIONS_SQL = "INSERT INTO AM_SUBSCRIPTION ( TIER_ID , API_ID , APPLICATION_ID) VALUES (?,?,?)";
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = DriverManager.getConnection(URL, USER, PASS);
        stmt = conn.prepareStatement(INSERT_SUBSCRIPTIONS_SQL, new String[]{"SUBSCRIPTION_ID"});
        stmt.setString(1, "Unlimited");
        stmt.setInt(2, apiId);
        stmt.setInt(3, applicationId);
        stmt.execute();
        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            subscriptionId = rs.getInt(1);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeConnection(conn, stmt, rs);
    }
    return subscriptionId;
}
 

/**
 * Helper function to get generated key Does consider different methods of
 * retrieving the generated keys depending on the database
 * 
 * @param ps
 *            {@link PreparedStatement}
 * @return {@link ResultSet} with generated key
 * @throws SQLException
 */
protected ResultSet getGeneratedKey(PreparedStatement ps)
		throws SQLException {

	ResultSet rs = null;

	try {

		if (db.getConnection().getMetaData().supportsGetGeneratedKeys()) {
			rs = ps.getGeneratedKeys();
		} else {

			if (Configuration.DatabaseType == DatabaseTypes.SQLITE) {
				// workaround: retrieve keys with 'select
				// last_insert_rowid();'
				rs = ps.getGeneratedKeys();
			} else {
				throw new SQLException(
						"driver does not support retrieving generated keys.");
			}

		}

	} catch (SQLException e) {
		log.log(Level.SEVERE, "getGeneratedKey failed: " + e.getMessage(),
				e);
		throw e;
	}

	return rs;

}
 

@Override
public JobInstance createSubJobInstance(final String name) {
    Connection conn = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    JobInstanceImpl jobInstance = null;

    try {
        conn = getConnection();
        statement = conn.prepareStatement(dictionary.getCreateJobInstance(), Statement.RETURN_GENERATED_KEYS);
        statement.setString(1, name);
        statement.executeUpdate();
        if (!conn.getAutoCommit()) {
            conn.commit();
        }
        rs = statement.getGeneratedKeys();
        if (rs.next()) {
            final long jobInstanceID = rs.getLong(1);
            jobInstance = new JobInstanceImpl(jobInstanceID);
            jobInstance.setJobName(name);
        }
    } catch (final SQLException e) {
        throw new PersistenceException(e);
    } finally {
        cleanupConnection(conn, rs, statement);
    }
    return jobInstance;
}
 

@Override
public int add(InputStream data, int length) throws ContentStoreException {
  try {
    // Push length onto stream
    PushbackInputStream pstream = new PushbackInputStream(data, 4);
    byte[] b = new byte[4];    
    b[3] = (byte)(length >>> 0);
    b[2] = (byte)(length >>> 8);
    b[1] = (byte)(length >>> 16);
    b[0] = (byte)(length >>> 24);
    pstream.unread(b);

    // Add new entry
    PreparedStatement ps = null;
    try {
      ps = conn.prepareStatement(sql_put, Statement.RETURN_GENERATED_KEYS);
      ps.setBinaryStream(1, pstream, length + b.length);
      ps.executeUpdate();

      ResultSet rs = ps.getGeneratedKeys();
      if (rs.next())
        return rs.getInt(1);
      else
        throw new RuntimeException("No keys were generated.");
    } finally {
      if (ps != null) ps.close();
      if (pstream != null) pstream.close();
    }
  } catch (Throwable t) {
    throw new ContentStoreException(t);
  }
}
 

/**
 * 将TMX的header接点的主要属性写入到mheader表中
 * @throws SQLException
 */
public String insertHeader(Hashtable<String, String> params) throws SQLException {
	PreparedStatement stmt = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-mheader");
		stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		int i = 1;
		stmt.setString(i++, params.get("CREATIONTOOL"));
		stmt.setString(i++, params.get("CTVERSION"));
		stmt.setString(i++, params.get("TMF"));
		stmt.setString(i++, Utils.convertLangCode(params.get("SRCLANG")));
		stmt.setString(i++, params.get("ADMINLANG"));
		stmt.setString(i++, params.get("DATATYPE"));
		stmt.setString(i++, params.get("SEGTYPE"));
		stmt.setString(i++, params.get("CREATIONID"));
		stmt.setString(i++, params.get("CREATIONDATE"));
		stmt.setString(i++, params.get("CHANGEID"));
		stmt.setString(i++, params.get("CHANGEDATE"));
		stmt.setString(i++, params.get("ENCODING"));
		int row = stmt.executeUpdate();
		String key = "-1";
		if (row == 1) {
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()) {
				key = rs.getString(1);
			}
			if (rs != null)
				rs.close();
		}
		return key;
	} finally {
		if (stmt != null) {
			stmt.close();
		}
	}
}
 

@SuppressWarnings("unchecked")
public <T> T excuteSqlReturnPK(String sqlOrID, Object...params) throws Throwable {
   	String doSql = JDBCUtils.getFinalSql(sqlOrID);
	SqlContext context = SqlCoreHandle.handleRequest(doSql, params).printSqlLog();
   	Connection conn = ConnectionManager.getConnection(dataSource);
   	
   	PreparedStatement ps = null;
       ResultSet rs = null;
       try {
           ps = conn.prepareStatement(context.getSql(),Statement.RETURN_GENERATED_KEYS);
           JDBCUtils.setParams(ps, context.getParamList().toArray());
           int rowNum = ps.executeUpdate();
           if (rowNum > 1) {
               throw new Throwable("the insert too many");
           }
           rs = ps.getGeneratedKeys();
           ResultSetMetaData metaData = rs.getMetaData();
           while (rs.next()) {
               return (T) JDBCUtils.getValueByObjectType(metaData, rs, 0);
           }
           
       } catch (Throwable e) {
           throw e;
       } finally {
           JDBCUtils.close(rs, ps);
       }
       return null;
   }
 

/**
 * 将数据写入到MTU表中
 * @param params
 * @return
 * @throws SQLException
 *             ;
 */
public int insertTU(int headerId, String tuId, String creationId, String creationDate, String changeId,
		String changeDate, String creationTool, String creationToolVersion, String client, String projectRef,
		String jobRef) throws SQLException {
	PreparedStatement stmt = null;
	ResultSet rs = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-tu");
		if (null == conn) {
			return -1;
		}
		stmt = conn.prepareStatement(sql, java.sql.Statement.RETURN_GENERATED_KEYS);
		int i = 1;
		stmt.setInt(i++, headerId);
		stmt.setString(i++, tuId);
		stmt.setString(i++, creationId);
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(creationDate));
		stmt.setString(i++, changeId);
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(changeDate));
		stmt.setString(i++, creationTool);
		stmt.setString(i++, creationToolVersion);
		stmt.setString(i++, client);
		stmt.setString(i++, projectRef);
		stmt.setString(i++, jobRef);
		stmt.executeUpdate();
		rs = stmt.getGeneratedKeys();
		if (rs.next()) {
			return rs.getInt(1);
		}
	} finally {
		if (rs != null) {
			rs.close();
		}
		if (stmt != null) {
			stmt.close();
		}
	}
	return -1;
}
 

@Override
public void transactionMarker() throws Exception {
    PreparedStatement preparedStatement = connection.prepareStatement(
            "insert into employee (name) values (?)", Statement.RETURN_GENERATED_KEYS);
    try {
        preparedStatement.setString(1, "nobody");
        preparedStatement.executeUpdate();
        ResultSet rs = preparedStatement.getGeneratedKeys();
        while (rs.next()) {
            rs.getString(1);
        }
    } finally {
        preparedStatement.close();
    }
}
 

public static int executeUpdateGetKeys(PreparedStatement stmt, Connection conn) {
    try {
        if (stmt.executeUpdate() > 0) {
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
    } catch (SQLException e) {
        logger.error("SQLException while executing Update and getting generated Keys for statement\n\t" + stmt, e);
    } finally {
        DB.cleanup(conn, stmt);
    }
    return -1;
}
 

/**
 * Execute given insert query in a new statement object and return
 * generated IDs.
 *
 * @param query Query to execute
 * @return Generated ID.
 */
public Long executeInsertQuery(String query, Object... args) {
  LOG.info("Executing query: " + query);
  ResultSet rs = null;

  try {
    PreparedStatement stmt = databaseConnection.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
    for (int i = 0; i < args.length; ++i) {
      if (args[i] instanceof String) {
        stmt.setString(i + 1, (String) args[i]);
      } else if (args[i] instanceof Long) {
        stmt.setLong(i + 1, (Long) args[i]);
      } else if (args[i] instanceof Boolean) {
        stmt.setBoolean(i + 1, (Boolean) args[i]);
      } else {
        stmt.setObject(i + 1, args[i]);
      }
    }

    stmt.execute();
    rs = stmt.getGeneratedKeys();
    if (rs.next()) {
      return rs.getLong(1);
    }
  } catch (SQLException e) {
    LOG.error("Error in executing query", e);
    throw new RuntimeException("Error in executing query", e);
  } finally {
    closeResultSetWithStatement(rs);
  }

  return -1L;
}
 

/**
 * Returns the key that was generated from the given statement, or <code>null</code> if no key
 * was generated or it could not be determined.
 *
 * @param stmt the statement that generated a key
 * @return the key that was generated from the given statement, or <code>null</code> if no key
 *         was generated or it could not be determined.
 */
private String determineGeneratedKey(final PreparedStatement stmt) {
    try {
        final ResultSet generatedKeys = stmt.getGeneratedKeys();
        if (generatedKeys != null && generatedKeys.next()) {
            return generatedKeys.getString(1);
        }
    } catch (final SQLException sqle) {
        // This is not supported by all vendors. This is a best-effort approach.
    }

    return null;
}
 

private long insertAndGetConnectorId(MConnector mc, Connection conn) {
  PreparedStatement baseConnectorStmt = null;
  try {
    baseConnectorStmt = conn.prepareStatement(crudQueries.getStmtInsertIntoConfigurable(),
        Statement.RETURN_GENERATED_KEYS);
    baseConnectorStmt.setString(1, mc.getUniqueName());
    baseConnectorStmt.setString(2, mc.getClassName());
    baseConnectorStmt.setString(3, mc.getVersion());
    baseConnectorStmt.setString(4, mc.getType().name());

    int baseConnectorCount = baseConnectorStmt.executeUpdate();
    if (baseConnectorCount != 1) {
      throw new SqoopException(CommonRepositoryError.COMMON_0009,
          Integer.toString(baseConnectorCount));
    }

    ResultSet rsetConnectorId = baseConnectorStmt.getGeneratedKeys();

    if (!rsetConnectorId.next()) {
      throw new SqoopException(CommonRepositoryError.COMMON_0010);
    }
    // connector configurable also have directions
    insertConnectorDirections(rsetConnectorId.getLong(1), mc.getSupportedDirections(), conn);
    return rsetConnectorId.getLong(1);
  } catch (SQLException ex) {
    throw new SqoopException(CommonRepositoryError.COMMON_0011, mc.toString(), ex);
  } finally {
    closeStatements(baseConnectorStmt);
  }
}
 

public int AddDocumentDetails(Article art)
{
	int id = -1;
	try{
		String articleAuthors = "";
		for(int i = 0; i<art.getAuthors().size();i++)
		{
			articleAuthors = articleAuthors+";"+art.getAuthors().get(i).name;
		}
		if(articleAuthors.length()>1)
			articleAuthors = articleAuthors.substring(1);	
		String URL = "http://www.ncbi.nlm.nih.gov/pmc/articles/PMC"+art.getPmc();
		 Class.forName(jdbcDriver);

         con = DriverManager.getConnection(dbAddress + dbName, userName, password);
         String insertSQL = "INSERT INTO DocumentDetails (PMC,PISSN,EISSN, Title, Authors,Abstract, LongAbstract ,URL, XML) VALUES (?,?,?, ?, ?,?, ?,?,?)";
         PreparedStatement preparedStatement = con.prepareStatement(insertSQL,Statement.RETURN_GENERATED_KEYS);
         preparedStatement.setString(1, art.getPmc());
         preparedStatement.setString(2, art.getPissn());
         preparedStatement.setString(3, art.getEissn());
         preparedStatement.setString(4, art.getTitle());
         preparedStatement.setString(5, articleAuthors);
         preparedStatement.setString(6, art.getShort_abstract());
         preparedStatement.setString(7, art.getAbstract());
         preparedStatement.setString(8, URL);
         preparedStatement.setString(9, art.getXML());

         id = preparedStatement.executeUpdate();
         ResultSet rs = preparedStatement.getGeneratedKeys();
         if (rs.next()){
        	 id=rs.getInt(1);
         }
         con.close();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	return id;
}
 

private OptionalLong fetchGeneratedKey(PreparedStatement statement) throws SQLException {
    try (ResultSet keys = statement.getGeneratedKeys()) {
        if (keys.next()) {
            return OptionalLong.of(keys.getLong(1));
        }
    }
    return OptionalLong.empty();
}
 

@Override
public int removeEnrollment(int deviceId, String currentOwner,
                            int tenantId) throws DeviceManagementDAOException {
    Connection conn;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int status = -1;
    try {
        conn = this.getConnection();
        String sql = "DELETE FROM DM_ENROLMENT WHERE DEVICE_ID = ? AND OWNER = ? AND TENANT_ID = ?";
        stmt = conn.prepareStatement(sql, new String[] {"id"});
        stmt.setInt(1, deviceId);
        stmt.setString(2, currentOwner);
        stmt.setInt(3, tenantId);
        stmt.executeUpdate();

        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            status = 1;
        }
        return status;
    } catch (SQLException e) {
        throw new DeviceManagementDAOException("Error occurred while removing device enrolment", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }
}
 

/** Adds a new template to the DB.
 * @return String */
public String addTemplate() {	
    Connection connection = null;
    try {
        connection = new DBConnectionManager().getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO `aliada`.`template` "
        		+ "(`template_name`, `template_description`, `file_type_code`) VALUES (?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, this.templateName);
        preparedStatement.setString(2, this.templateDescription);
        preparedStatement.setInt(3, this.fileType);
        preparedStatement.executeUpdate();
        ResultSet rs = preparedStatement.getGeneratedKeys();
        int idTemplate = 0;
        if (rs.next()) {
            idTemplate = (int) rs.getInt(1);
        }
        preparedStatement.close();
        Statement statement = connection.createStatement();
        
        Iterator iterator = null;
        if (this.fileType == 0) {
        	iterator = selectedMarcBibTags.iterator();
        } else if (this.fileType == 1) {
        	iterator = selectedMarcAuthTags.iterator();
        } else if (this.fileType == 2) {
        	iterator = selectedLidoTags.iterator();
        } else if (this.fileType == 3) {
        	iterator = selectedDcTags.iterator();
        }
        while (iterator.hasNext()) {
            statement = connection.createStatement();
            statement.executeUpdate("INSERT INTO aliada.template_xml_tag VALUES ('" + idTemplate + "', '" + iterator.next() + "')");
            statement.close();
        }
        connection.close();
        setShowAddTemplateForm(false);
        addActionMessage(getText("template.save.ok"));
        logger.debug(MessageCatalog._00060_CONVERSION_TEMPLATE_ADDED);
        getTemplatesDb();
        return SUCCESS;
    } catch (SQLException e) {
        logger.error(MessageCatalog._00011_SQL_EXCEPTION, e);
        return ERROR;
    }
}
 

public void add(Job job, ACS acs) throws SQLException {
  if (!acs.getUser().isUnittypeAdmin(unittype.getId())) {
    throw new IllegalArgumentException("Not allowed action for this user");
  }
  job.setStatus(JobStatus.READY);
  job.validate();
  Connection c = null;
  PreparedStatement ps = null;
  try {
    if (nameMap.get(job.getName()) != null) {
      throw new IllegalArgumentException("The job name already exists, choose another name");
    }
    c = acs.getDataSource().getConnection();
    DynamicStatement ds = new DynamicStatement();
    ds.setSql("INSERT INTO job (");
    ds.addSqlAndArguments("job_name, ", job.getName());
    ds.addSqlAndArguments("job_type, ", job.getFlags().toString());
    ds.addSqlAndArguments("description, ", job.getDescription());
    ds.addSqlAndArguments("group_id, ", job.getGroup().getId());
    ds.addSqlAndArguments("unconfirmed_timeout, ", job.getUnconfirmedTimeout());
    ds.addSqlAndArguments("stop_rules, ", job.getStopRulesSerialized());
    ds.addSqlAndArguments("status, ", job.getStatus().toString());
    if (job.getFile() != null) {
      ds.addSqlAndArguments("firmware_id, ", job.getFile().getId());
    }
    if (job.getDependency() != null) {
      ds.addSqlAndArguments("job_id_dependency, ", job.getDependency().getId());
    }
    if (job.getRepeatCount() != null) {
      ds.addSqlAndArguments("repeat_count, ", job.getRepeatCount());
    }
    if (job.getRepeatInterval() != null) {
      ds.addSqlAndArguments("repeat_interval, ", job.getRepeatInterval());
    }
    ds.addSqlAndArguments("completed_no_failure, ", 0);
    ds.addSqlAndArguments("completed_had_failure, ", 0);
    ds.addSqlAndArguments("confirmed_failed, ", 0);
    ds.addSqlAndArguments("unconfirmed_failed) ", 0);
    ds.addSql(" VALUES (" + ds.getQuestionMarks() + ")");
    ps = ds.makePreparedStatement(c, "job_id");
    ps.setQueryTimeout(60);
    ps.executeUpdate();
    ResultSet gk = ps.getGeneratedKeys();
    if (gk.next()) {
      job.setId(gk.getInt(1));
    }

    Job dep = job.getDependency();
    if (dep != null && !dep.getChildren().contains(job)) {
      dep.getChildren().add(job);
    }

    idMap.put(job.getId(), job);
    nameMap.put(job.getName(), job);

    updateMandatoryJobParameters(job, acs);
    logger.info("Inserted job " + job.getId());
    if (acs.getDbi() != null) {
      acs.getDbi().publishAdd(job, job.getGroup().getUnittype());
    }
  } finally {
    if (ps != null) {
      ps.close();
    }
    if (c != null) {
      c.close();
    }
  }
}
 

private static List<RespUpdate> modify(List<SQLValue> sqlValues, boolean hasPK, Connection conn) throws SQLException {
	List<RespUpdate> rus = new ArrayList<>();
	for (SQLValue sqlValue : sqlValues) {
		ResultSet rs = null;
		PreparedStatement stat = null;
		RespUpdate ru = new RespUpdate();
		try {
			String sql = sqlValue.getSql();
			QueryContext.addSqls(sql);
			info(sql, sqlValue.getValues());
			if (hasPK) {
				stat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			} else {
				stat = conn.prepareStatement(sql);
			}
			List<Object> values = sqlValue.getValues();
			int len = values.size();
			for (int i = 0; i < len; i++) {
				// 设置sql参数值
				stat.setObject(i + 1, values.get(i));
			}

			ru.setEffect(stat.executeUpdate());

			if (hasPK) {
				rs = stat.getGeneratedKeys();
				if (rs.next()) {
					ru.setPk(rs.getLong(1));
				}
			}
			stat.close();
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			close(rs, stat);
		}

		rus.add(ru);
	}

	return rus;
}
 

public UUID storeClient(UUID uuid)
    {
        
        resetError();
        
        try
        {
            PreparedStatement st;
            if (uuid == null) 
              uuid = UUID.randomUUID();
            
            st = conn.prepareStatement(
                    "INSERT INTO client(uuid, client_type_id, time, sync_group_id, sync_code, terms_and_conditions_accepted, terms_and_conditions_accepted_version)"
                            + "VALUES( CAST( ? AS UUID), ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            
            st.setString(1, uuid.toString());
            st.setInt(2, client_type_id);
            st.setTimestamp(3, time);
            if (sync_group_id > 0)
                st.setInt(4, sync_group_id);
            else
                st.setObject(4, null);
            if (sync_code.length() > 0)
                st.setString(5, sync_code);
            else
                st.setObject(5, null);
            
            st.setBoolean(6, tcAccepted);
            
            st.setInt(7, tcAcceptedVersion);
            
//            System.out.println(st.toString());
            
            final int affectedRows = st.executeUpdate();
            if (affectedRows == 0)
            {
                uid = 0;
                uuid = null;
                client_type_id = 0;
                client_type_name = "";
                time = null;
                setError("ERROR_DB_STORE_CLIENT");
                // errorList.addError(labels.getString("ERROR_DB_STORE_CLIENT"));
            }
            else
            {
                final ResultSet rs = st.getGeneratedKeys();
                if (rs.next())
                    // Retrieve the auto generated key(s).
                    uid = rs.getLong(1);
            }
            st.close();
        }
        catch (final SQLException e)
        {
            setError("ERROR_DB_STORE_CLIENT_SQL");
            // errorList.addError(labels.getString("ERROR_DB_STORE_CLIENT_SQL"));
            e.printStackTrace();
        }
        
        return uuid;
    }