Guest User

Untitled

a guest
Aug 30th, 2018
99
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.39 KB | None | 0 0
  1. How to use INSERT INTO ALL statement in jdbc prepared statement with beans
  2. public class AttendanceBean {
  3. private int atteid;
  4. private String attdname;
  5. private int attday;
  6. private int attmonth;
  7. private int attyear;
  8.  
  9. public static Connection getAttConnection() throws Exception {
  10. String driver = "oracle.jdbc.driver.OracleDriver";
  11. String url = "jdbc:oracle:thin:@localhost:1521:globldb3";
  12. String username = "scott";
  13. String password = "tiger";
  14. Class.forName(driver);
  15. Connection conn = DriverManager.getConnection(url, username, password);
  16. return conn;
  17. }
  18. public String addAttendance(){
  19. Connection conn = null;
  20. PreparedStatement pstmt = null;
  21. boolean committed = false;
  22. try {
  23. conn = getAttConnection();
  24. conn.setAutoCommit(false);
  25. String query = "INSERT ALL INTO attendance VALUES (?,?,?,?,?)";
  26. pstmt = conn.prepareStatement(query);
  27. pstmt.setInt(1,this.atteid);
  28. pstmt.setString(2,this.attdname);
  29. pstmt.setInt(3,this.attday);
  30. pstmt.setInt(4,this.attmonth);
  31. pstmt.setInt(5,this.attyear);
  32. pstmt.executeUpdate();
  33. conn.commit();
  34. conn.setAutoCommit(true);
  35. committed = true;
  36. return "home.xhtml";
  37. } catch (Exception e) {
  38. e.printStackTrace();
  39. return "CRM.xhtml";
  40. } finally {
  41. try{
  42. if (!committed) conn.rollback();
  43. if (pstmt != null) pstmt.close();
  44. if (conn != null) conn.close();
  45. }catch(Exception e){
  46. e.printStackTrace();
  47. }
  48. }
  49. }
  50. }
  51.  
  52. private static final String SQL_INSERT = "INSERT INTO tbl (col1, col2, col3) VALUES (?, ?, ?)";
  53.  
  54. public void save(List<Entity> entities) throws SQLException {
  55. Connection connection = null;
  56. PreparedStatement statement = null;
  57.  
  58. try {
  59. connection = database.getConnection();
  60. statement = connection.prepareStatement(SQL_INSERT);
  61.  
  62. for (Entity entity : entities) {
  63. statement.setObject(1, entity.getCol1());
  64. statement.setObject(2, entity.getCol2());
  65. statement.setObject(3, entity.getCol3());
  66. statement.addBatch();
  67. }
  68.  
  69. statement.executeBatch();
  70. } finally {
  71. if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
  72. if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
  73. }
  74. }
Add Comment
Please, Sign In to add comment