1.插入一条记录返回刚插入记录的id
- public int addBean(final Bean b){
-
- final String strSql = "insert into buy(id,c,s,remark,line,cdatetime," +
- "c_id,a_id,count,type) values(null,?,?,?,?,?,?,?,?,?)";
- KeyHolder keyHolder = new GeneratedKeyHolder();
-
- this.getJdbcTemplate().update(
- new PreparedStatementCreator(){
- public java.sql.PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
- int i = 0;
- java.sql.PreparedStatement ps = conn.prepareStatement(strSql);
- ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);
- ps.setString(++i, b.getC());
- ps.setInt(++i,b.getS() );
- ps.setString(++i,b.getR() );
- ps.setString(++i,b.getline() );
- ps.setString(++i,b.getCDatetime() );
- ps.setInt(++i,b.getCId() );
- ps.setInt(++i,b.getAId());
- ps.setInt(++i,b.getCount());
- ps.setInt(++i,b.getType());
- return ps;
- }
- },
- keyHolder);
-
- return keyHolder.getKey().intValue();
- }
2.批量插入数据
- public void addBuyBean(List<BuyBean> list)
- {
- final List<BuyBean> tempBpplist = list;
- String sql="insert into buy_bean(id,bid,pid,s,datetime,mark,count)" +
- " values(null,?,?,?,?,?,?)";
- this.getJdbcTemplate().batchUpdate(sql,new BatchPreparedStatementSetter() {
-
- @Override
- public int getBatchSize() {
- return tempBpplist.size();
- }
- @Override
- public void setValues(PreparedStatement ps, int i)
- throws SQLException {
- ps.setInt(1, tempBpplist.get(i).getBId());
- ps.setInt(2, tempBpplist.get(i).getPId());
- ps.setInt(3, tempBpplist.get(i).getS());
- ps.setString(4, tempBpplist.get(i).getDatetime());
- ps.setString(5, tempBpplist.get(i).getMark());
- ps.setInt(6, tempBpplist.get(i).getCount());
- }
- });
- }
3.批量插入并返回批量id
注:由于JDBCTemplate不支持批量插入后返回批量id,所以此处使用jdbc原生的方法实现此功能
- public List<Integer> addProduct(List<ProductBean> expList) throws SQLException {
- final List<ProductBean> tempexpList = expList;
-
- String sql="insert into product(id,s_id,status,datetime,"
- + " count,o_id,reasons"
- + " values(null,?,?,?,?,?,?)";
-
- DbOperation dbOp = new DbOperation();
- dbOp.init();
- Connection con = dbOp.getConn();
- con.setAutoCommit(false);
- PreparedStatement pstmt = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
- for (ProductBean n : tempexpList) {
- pstmt.setInt(1,n.getSId());
- pstmt.setInt(2,n.getStatus());
- pstmt.setString(3,n.getDatetime());
- pstmt.setInt(4,n.getCount());
- pstmt.setInt(5,n.getOId());
- pstmt.setInt(6,n.getReasons());
- pstmt.addBatch();
- }
- pstmt.executeBatch();
- con.commit();
- ResultSet rs = pstmt.getGeneratedKeys();
- List<Integer> list = new ArrayList<Integer>();
- while(rs.next()) {
- list.add(rs.getInt(1));
- }
- con.close();
- pstmt.close();
- rs.close();
-
- return list;
-
- }
以上三组代码直接复制把对应的实体类名一改就可以直接使用在项目中,希望对大家有帮助
声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
|
- ahack
- 等级: 初级会员
- 文章: 18
- 积分: 30
|
发表时间:2014-10-04 最后修改:2014-10-04
的确用KeyHolder就可以了,简单封装下吧挨个set好费劲。
- public SysTask createNewTask(final SysTask sysTask) {
- KeyHolder keyHolder = new GeneratedKeyHolder();
- jdbcTemplate.update(new PreparedStatementCreator() {
- public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
- String[] str = new String[] {"task_type","domain_id","machine_id","status"};
- Object[] obj = new Object[]{sysTask.getTaskType(),sysTask.getDomainId(),sysTask.getMachineId(),sysTask.getStatus()};
- PreparedStatement ps = conn.prepareStatement("insert into sys_task (task_type,domain_id,machine_id,status,publish_time) values(?,?,?,?,now())",
- str
- );
- for (int i = 0; i < obj.length; i++) {
- ps.setObject(i+1, obj[i]);
- }
- return ps;
- }
- }, keyHolder);
- int taskId = keyHolder.getKey().intValue();
- return getTaskById(taskId);
- }
|