mysql 批量插数据到数据库以及返回插入生成的id号

小咪咪 2022-09-17 12:20 302阅读 0赞

首先是插入一条数据到数据库，并返回插入时的id号，数据的设计 id号的是自动递增的

public static int  saveHotel(Hotel hotel) \{  
PreparedStatement pstmt = null;  
Connection conn = DB.getConnection();  
try \{  
String sql = "insert into t\_hotel  values(null , ?,? ,? ,?,? ,? ,?,?,?)";

//插入数据时返回的id号   
pstmt = conn.prepareStatement(sql, Statement.RETURN\_GENERATED\_KEYS);  
pstmt.setString(1, hotel.getName());  
pstmt.setString(2, hotel.getPassword());  
pstmt.setString(3, hotel.getAddress());  
pstmt.setString(4, hotel.getPhone());  
pstmt.setInt(5, hotel.getPrice());  
pstmt.setString(6, hotel.getImg());  
pstmt.setString(7, hotel.getCuisine());  
pstmt.setString(8, hotel.getArea());  
pstmt.setString(9, hotel.getStreet());  
pstmt.executeUpdate();  
  
//获得返回的id号  
ResultSet rs = pstmt.getGeneratedKeys();  
rs.next();  
int returnKey = rs.getInt(1);  
return returnKey ;  
\} catch (SQLException e) \{  
e.printStackTrace();  
return 0 ;  
\} finally \{  
DB.closepStmt(pstmt);  
DB.closeConn(conn);  
\}  
\}

批量插入数据并返回id号

public static ResultSet saveHotel(List<hotel> hotels) \{  
PreparedStatement pstmt = null;  
Connection conn = DB.getConnection();  
try \{  
String sql = "insert into t\_hotel  values(null , ?,? ,? ,?,? ,? ,?,?,?)";

//插入数据时返回的id号   
pstmt = conn.prepareStatement(sql, Statement.RETURN\_GENERATED\_KEYS);

for(Hotel hotel :hotels)\{  
pstmt.setString(1, hotel.getName());  
pstmt.setString(2, hotel.getPassword());  
pstmt.setString(3, hotel.getAddress());  
pstmt.setString(4, hotel.getPhone());  
pstmt.setInt(5, hotel.getPrice());  
pstmt.setString(6, hotel.getImg());  
pstmt.setString(7, hotel.getCuisine());  
pstmt.setString(8, hotel.getArea());  
pstmt.setString(9, hotel.getStreet());

pstmt.addBatch();

\}  
pstmt.executeBatch();  
  
//获得返回的id号，是一个set

ResultSet keyRs = pstmt.getGeneratedKeys();

\} catch (SQLException e) \{  
e.printStackTrace();  
return 0 ;  
\} finally \{  
DB.closepStmt(pstmt);  
DB.closeConn(conn);  
\}

return keyRs ;  
\}