下面列出了java.sql.PreparedStatement#getGeneratedKeys ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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 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);
}
}
private OptionalLong fetchGeneratedKey(PreparedStatement statement) throws SQLException {
try (ResultSet keys = statement.getGeneratedKeys()) {
if (keys.next()) {
return OptionalLong.of(keys.getLong(1));
}
}
return OptionalLong.empty();
}
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 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);
}
}
/**
* 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;
}
/**
* 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;
}
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;
}
@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();
}
}
/**
* 将数据写入到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;
}
@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;
}
/**
* 将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();
}
}
}
@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);
}
}
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;
}
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;
}
/** 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();
}
}
}