1 / 79

Enhancing Database Communication through Typechecking and Refactoring with JPA

This document explores robust methods of utilizing JPA for database communication through typechecking and refactoring techniques. It demonstrates how to build and execute dynamic SQL queries, efficiently handling object-relational mapping with Java classes. Examples illustrate query creation, parameter setting, and execution, highlighting the importance of maintaining safety and preventing errors, such as unset parameters and unsafe types. This comprehensive approach provides developers with efficient and flexible solutions for string-based queries in Java, enhancing overall database interaction.

stacy
Télécharger la présentation

Enhancing Database Communication through Typechecking and Refactoring with JPA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Deep Typecheckingand Refactoring Zachary Tatlock, Chris Tucker, David Shuffleton, RanjitJhala, Sorin Lerner University of California, San Diego

  2. Communicating with Databases String based queries are prevalent: • JPA, Hibernate, TopLink Strings JAVA DB

  3. Example: Using JPA to query DB Query in JPA Query Language: “SELECT w FROM Weblog w WHERE w.id = ?1 AND w.link.id = ?2” Java syntax in query String • Mapping Java Classes to DB Tables: • Expressed in Object Relational Mapping (ORM)

  4. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  5. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Build query string

  6. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Build query string Create query

  7. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Build query string Create query Set parameters

  8. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Build query string Create query Set parameters

  9. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Build query string Create query Set parameters Execute query

  10. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Build query string Create query Set parameters Execute query • Efficient • Flexible Unsafe

  11. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  12. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); // q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  13. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); // q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Unset parameter

  14. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Unset parameter

  15. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, new Weblog()); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Unset parameter Unsafe param type

  16. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Unset parameter Unsafe param type

  17. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Warranty w = (Warranty)q.execQuery(); return w.text; } Unset parameter Unsafe param type Unsafe downcast

  18. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Unset parameter Unsafe param type Unsafe downcast Java compiler does not reason about the query strings; cannot typecheck.

  19. Refactor: Weblog.id  Weblog.name String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Unset parameter Unsafe param type Unsafe downcast

  20. Refactor: Weblog.id  Weblog.name String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Refactor Unset parameter Unsafe param type Unsafe downcast Don’t Refactor Need to know type of w and w.link to refactor safely.

  21. Refactor: Weblog.id  Weblog.name String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Unset parameter Unsafe param type Unsafe downcast Refactoring difficult

  22. String Based Query Challenges String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Unset parameter Unsafe param type Unsafe downcast Refactoring difficult

  23. Contributions Deep Typechecking: • All parameters set • Parameters correctly set • Results safely downcast Unset parameter Unsafe param type Unsafe downcast Refactoring difficult • Deep Refactoring: • Builds on Deep Typechecking • Enables class and field renaming

  24. Outline • Introduction and Motivation • Deep Typechecking and Refactoring • Straight Line Code • Control Flow • Multiple Methods • Experimental Results • Related Work and Conclusion

  25. Deep Typechecking Example String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } • Query safety: • All params set • Params safely set • Result safely downcast Is this query exec safe? Needto know: query string param types

  26. Deep Typechecking Example • Bound Query: • query string • param types String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Example : query : “SELECT … ?1 … ?2 … ?3 …” ?1 : String ?2 : Weblog ?3 : unknown At each program point map each var to a set of BQs.

  27. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  28. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } qStr = “SELECT … ?2”

  29. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } qStr = “SELECT … ?2”

  30. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } qStr = “SELECT … ?2”

  31. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } qStr = “SELECT … ?2” query : “SELECT …” ?1 : unknown ?2 : unknown

  32. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } qStr = “SELECT … ?2” query : “SELECT …” ?1 : unknown ?2 : unknown query : “SELECT …” ?1 : String ?2 : unknown

  33. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } qStr = “SELECT … ?2” query : “SELECT …” ?1 : unknown ?2 : unknown query : “SELECT …” ?1 : String ?2 : unknown query : “SELECT …” ?1 : String ?2 : int

  34. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Checking param types: Parse query string Check all params set Check param types Bound Queries: query : “SELECT …” ?1 : String ?2 : int

  35. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } Checking result type: Infer result type Propagate result type Check downcasts Bound Queries: query : “SELECT …” ?1 : String ?2 : int result : Weblog query : “SELECT …” ?1 : String ?2 : int

  36. Bound Query Analysis String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } If a query passes Deep Typechecking, then it will not cause an error at runtime. Therefore, Bound Query Analysis has no silent failures.

  37. Deep Refactoring Example String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  38. Deep Refactoring Example Refactor Weblog field: id  name String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  39. Deep Refactoring Example Refactor Weblog field: id  name String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  40. Deep Refactoring Example Refactor Weblog field: id  name String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  41. Deep Refactoring Example Refactor Weblog field: id  name String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } query : SELECT w FROM Weblog w WHERE w.id = ?1 AND w.link.id = ?2 ?1 : String ?2 : int

  42. Deep Refactoring Example Refactor Weblog field: id  name Refactor full query String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } query : SELECT w FROM Weblog w WHERE w.id = ?1 AND w.link.id = ?2 ?1 : String ?2 : int

  43. Deep Refactoring Example Refactor Weblog field: id  name Refactor full query String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } query : SELECT w FROM Weblog w WHERE w.name = ?1 AND w.link.id = ?2 ?1 : String ?2 : int

  44. Deep Refactoring Example Refactor Weblog field: id  name Refactor full query Propagate changes String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } query : SELECT w FROM Weblog w WHERE w.name = ?1 AND w.link.id = ?2 ?1 : String ?2 : int

  45. Deep Refactoring Example Refactor Weblog field: id  name Refactor full query Propagate changes String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.name = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; } query : SELECT w FROM Weblog w WHERE w.name = ?1 AND w.link.id = ?2 ?1 : String ?2 : int

  46. Outline • Introduction and Motivation • Deep Typechecking and Refactoring • Straight Line Code • Control Flow • Multiple Methods • Experimental Results • Related Work and Conclusion

  47. Flow Sensitivity String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  48. Flow Sensitivity String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  49. Flow Sensitivity String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  50. Flow Sensitivity String getText(String id, Link link) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; if(link != null) { qStr += “AND w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); } else { } Weblog w = (Weblog) q.execQuery(); return w.text; }

More Related