java 简单的Jdbc模板。- 插入和检索 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2235330/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
simpleJdbcTemplate. - insert and retrieve ID
提问by Sebastian
I'm putting the data into database with simpleJdbcTemplate.
我正在使用 simpleJdbcTemplate 将数据放入数据库中。
simpleJdbcTemplate.update("insert into TABLE values(default)");
I dont want to put any data because i dont need it for my unit test purpose.
我不想放置任何数据，因为我的单元测试目的不需要它。
How can i get the id from the inserted row? I can retriev the current sequence value but if somebody else will do a insert then i will be get a next sequence value.
如何从插入的行中获取 id？我可以检索当前序列值，但如果其他人将进行插入，那么我将获得下一个序列值。
Is there any way to use simpleJdbcTemplate to insert a row and get id? The update method retuns the number of inserted rows and i would like to have the id. Thank you for your help.
有什么方法可以使用 simpleJdbcTemplate 插入一行并获取 id？更新方法重新调整插入的行数，我想要ID。谢谢您的帮助。
回答by George
Did you find the answer yet? If not, try to use SimpleJdbcInsertinstead.
For example:
你找到答案了吗？如果没有，请尝试使用SimpleJdbcInsert。例如：
SimpleJdbcInsert sji = new SimpleJdbcInsert(dataSource)
.withTableName(TableName)
.usingColumns(new String[]{your columns})
.usingGeneratedKeyColumns(you auto-increment id colums);
then retrieve
然后检索
sji.executeAndReturnKey(args).longValue();
回答by Esko
You need to manually handle the sequence to get the id easily without tying yourself into any specific RDBMS product.
您需要手动处理序列以轻松获取 id，而无需将自己绑定到任何特定的 RDBMS 产品。
This means that you have to specify a deployment-specific DataFieldMaxValueIncrementerbean and inject that to your database handling class just as you most likely do with your DataSource. The bean definition should look something like this (this example is for PostgreSQL):
这意味着您必须指定一个特定于部署的DataFieldMaxValueIncrementerbean 并将其注入您的数据库处理类，就像您最有可能对DataSource. bean 定义应如下所示（此示例适用于 PostgreSQL）：
<bean id="incrementer" class="org.springframework.jdbc.support.incrementer.PostgreSQLSequenceMaxValueIncrementer">
<property name="dataSource" ref="dataSource" />
<property name="incrementerName" value="seq_name" />
</bean>
Then when you have the incrementer in your class, you can use it in your code to get the id value somewhat like this:
然后当你的类中有增量器时，你可以在你的代码中使用它来获取 id 值，有点像这样：
public long saveBeanAndReturnId(Bean b) {
long id = incrementer.nextLongValue();
simpleJdbc.update("...");
return id;
}
回答by Ramandeep Singh
I dun think its as tough as it seems.. :-O
我不认为它看起来像它一样艰难.. :-O
Y dont you try something like :
你不试试这样的：
int newID = simpleJdbcTemplate.queryForInt("INSERT INTO TABLE(Column_Names)
values (default)
RETURNING ID");
Now newIDwil contains the newly Inserted row ID.
现在newID 将包含新插入的行 ID。
CHEERS..!! :)
干杯..！！:)
回答by Pello X
Using NamedParameterJdbcTemplate you have a keyholder. It abstracts DBMS key generation. Check create method.
使用 NamedParameterJdbcTemplate 您有一个密钥持有人。它抽象了 DBMS 密钥生成。检查创建方法。
package info.pello.spring.persistence;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import java.util.ArrayList;
import java.util.List;
/**
*
*/
/**
* DAO for customer entity
* @author Pello Xabier Altadill Izura
* @greetz Blue Mug
*
*/
public class CustomerDAO {
// I use both jdbcTemplate/namedParameterJdbcTemplate depending on needs
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private final static String CREATE_SQL = "insert into customer (name,address,email) values(:name,:address,:email)";
/**
* gets Customer data from DataBase
* @param customerId
* @return
*/
public Customer read (int customerId) {
Customer customer = null;
return customer;
}
/**
* gets all Customer data from DataBase
* @return list of customers
*/
public List<Customer> readAll () {
List<Customer> customerList = new ArrayList<Customer>();
return customerList;
}
/**
* creates new Customer
* @param newCustomer
* @return
*/
public int create (Customer newCustomer) {
GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
MapSqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("name", newCustomer.getName());
namedParameters.addValue("address", newCustomer.getAddress());
namedParameters.addValue("email", newCustomer.getEmail());
namedParameterJdbcTemplate.update(CREATE_SQL,
namedParameters,
generatedKeyHolder);
newCustomer.setId(generatedKeyHolder.getKey().intValue());
return newCustomer.getId();
}
/**
* updates customer information
* @param customer
* @return
*/
public int update (Customer customer) {
int result = 0;
return result;
}
/**
* delete customer
* @param customerId
* @return
*/
public int delete (int customerId) {
int result = 0;
return result;
}
/**
* @return the jdbcTemplate
*/
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
/**
* @param jdbcTemplate the jdbcTemplate to set
*/
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/**
* @return the namedParameterJdbcTemplate
*/
public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
return namedParameterJdbcTemplate;
}
/**
* @param namedParameterJdbcTemplate the namedParameterJdbcTemplate to set
*/
public void setNamedParameterJdbcTemplate(
NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
}
}
回答by Somaiah Kumbera
simpleJdbcTemplate is deprecated in favour of NamedParameterJdbcTemplate.
simpleJdbcTemplate 已被弃用，取而代之的是 NamedParameterJdbcTemplate。
Pello X has the correct answer, but his submission is too cumbersome to understand. Simplified:
Pello X 有正确的答案，但他的提交太麻烦，无法理解。简化：
If you have a very simple table called SAMPLE with a column called NAME and a primary key that is generated called ID of type bigint:
如果您有一个名为 SAMPLE 的非常简单的表，其中包含一个名为 NAME 的列和一个生成的名为 bigint 类型的 ID 的主键：
MapSqlParameterSource namedParameters = new MapSqlParameterSource().addValue("name", name);
KeyHolder keyHolder = new GeneratedKeyHolder();
int numberOfAffectedRows = namedParameterJdbcTemplate.update("insert into SAMPLE(name) values(:name)", namedParameters, keyHolder);
return numberOfAffectedRows == 1 ? keyHolder.getKey().longValue() : -1L;
This will return the only generated key in the update or -1 if more than 1 row was affected.
如果超过 1 行受到影响，这将返回更新中唯一生成的键或 -1。
Note that since there was only 1 generated key I didn't care about the column name.
请注意，由于只有 1 个生成的键，我不关心列名。
If there is more than 1 key that is generated, look into http://docs.spring.io/spring/docs/3.2.7.RELEASE/javadoc-api/org/springframework/jdbc/support/KeyHolder.html#getKeys%28%29
如果生成的密钥超过 1 个，请查看http://docs.spring.io/spring/docs/3.2.7.RELEASE/javadoc-api/org/springframework/jdbc/support/KeyHolder.html#getKeys %28%29
回答by Aaron Digulla
Answer this question: What are you trying to achieve with your test? Check that the update runs without error? That you get a new ID every time? That the table exists?
回答这个问题：你想通过测试达到什么目的？检查更新运行没有错误？每次都要换新身？该表存在吗？
Depending on the answer, you must modify your test. If you just want to know that the syntax of the statement is correct, you don't need to do anything but run the statement (it will throw an exception if there is an error making the test fail).
根据答案，您必须修改您的测试。如果您只想知道语句的语法是否正确，则无需执行任何操作，只需运行该语句（如果有错误导致测试失败，它将抛出异常）。
If you want to make sure you get a new ID every time, you must query the sequence two times and check that the second value is different from the first.
如果要确保每次都获得新 ID，则必须查询序列两次并检查第二个值与第一个值是否不同。
If you want to check that a row with a new unique ID is inserted, just run the insert and check that it returns 1. If it works, you'll know that the primary key (the ID) wasn't violated and that a row was inserted. Hence, the "add with unique ID" mechanism must work.
如果您想检查是否插入了具有新唯一 ID 的行，只需运行插入并检查它是否返回 1。如果它有效，您就会知道主键（ID）没有被违反，并且行已插入。因此，“添加唯一 ID”机制必须起作用。
[EDIT] There is no way to test a trigger which adds an ID to a new row because Oracle has no means to return the ID it just created. You could read the sequence but there is no guarantee that nextval-1will give you the same result that the trigger saw.
[编辑] 无法测试将 ID 添加到新行的触发器，因为 Oracle 无法返回它刚刚创建的 ID。您可以阅读序列，但不能保证nextval-1会给您与触发器看到的结果相同的结果。
You could try select max(ID)but that can fail if anyone else inserts another row and commits it before you can run the query (using the default transaction level READ_COMMITTED).
您可以尝试，select max(ID)但如果其他人在您可以运行查询（使用默认事务级别READ_COMMITTED）之前插入另一行并提交它，则可能会失败。
Therefore I strongly suggest to get rid of the trigger and use the standard 2-step ("get new ID" plus "insert with new ID") algorithm that anyone else uses. It will make your tests more simple and less brittle.
因此，我强烈建议摆脱触发器并使用其他人使用的标准 2 步（“获取新 ID”加“插入新 ID”）算法。它会让你的测试更简单，更不脆弱。
回答by Adeel Ansari
You should first query the idfrom the appropriate sequence, and then provide that idin your insert statement. As simple as that.
您应该首先id从适当的查询sequence，然后id在您的插入语句中提供它。就如此容易。
Further, we call it integration test, instead of unit test, arguably. You might like to refer to this SO threadto have an idea regarding integration tests and ids.
此外，我们可以称其为集成测试，而不是单元测试。您可能想参考此 SO 线程以了解有关集成测试和 ID 的想法。
[Edited after comment]
[评论后编辑]
In that case, get rid of that trigger. And retrieve the idfrom the sequencedirectly, prior to make a insert.
在这种情况下，摆脱那个触发器。并在 make 之前直接id从 中检索。sequenceinsert
Well, you can fire a SELECT... FOR UPDATEon the table, and grab the last id, and increment that by 1. If your idis not sequential, which I guess wouldn't be the case, you can hold the ROWID, specific to Oracle AFAIK. And then query for idusing that. Indeed, its all kinda work around.
好吧，您可以SELECT... FOR UPDATE在表上触发 a ，并获取最后一个id，并将其增加 1。如果您id不是顺序的，我想情况并非如此，您可以持有ROWID特定于 Oracle AFAIK 的 。然后查询id使用它。事实上，它的所有工作都可以解决。
Note:I strongly suggest you to look at Aaron Digulla's post. See if any of that suffice.
注意：我强烈建议您查看 Aaron Digulla 的帖子。看看这些是否足够。
回答by hev1
With Spring's JdbcTemplateyou can use its updatemethod with a PreparedStatementCreatorand a GeneratedKeyholderto hold the primary key of the newly inserted row.
在 Spring 中，JdbcTemplate您可以使用update带有 aPreparedStatementCreator和 a 的方法GeneratedKeyholder来保存新插入行的主键。
public class SomeDao(){
@Autowired
private JdbcTemplate jdbcTemplate;
//example of a insertion returning the primary key
public long save(final String name){
final KeyHolder holder = new GeneratedKeyHolder();//the newly generated key will be contained in this Object
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException {
final PreparedStatement ps = connection.prepareStatement("INSERT INTO `names` (`name`) VALUES (?)",
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, name);
return ps;
}
}, holder);
return holder.getKey().longValue();//the primary key of the newly inserted row
}
}
