Communicating with Databases String based queries are prevalent: u - - PowerPoint PPT Presentation

▶
communicating with databases
SMART_READER_LITE
LIVE PREVIEW

Communicating with Databases String based queries are prevalent: u - - PowerPoint PPT Presentation

Jan 03, 2024 434 likes •1.1k views

Communicating with Databases String based queries are prevalent: u JPA, Hibernate, TopLink Strings JAVA DB 1 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

slide-1
SLIDE 1

Communicating with Databases

String based queries are prevalent:

uJPA, Hibernate, TopLink

JAVA DB

Strings

1

slide-2
SLIDE 2

Example: Using JPA to query DB

2

“SELECT w FROM Weblog w WHERE w.id = ?1 AND w.link.id = ?2”

Query in JPA Query Language: Mapping Java Classes to DB Tables:

u Expressed in Object Relational Mapping (ORM)

Java syntax in query String

slide-3
SLIDE 3

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; }

3

slide-4
SLIDE 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; }

  • 1. Build query string

4

slide-5
SLIDE 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; }

  • 1. Build query string
  • 2. Create query

5

slide-6
SLIDE 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; }

  • 1. Build query string
  • 2. Create query
  • 3. Set parameters

6

slide-7
SLIDE 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; }

  • 1. Build query string
  • 2. Create query
  • 3. Set parameters

7

slide-8
SLIDE 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; }

  • 1. Build query string
  • 2. Create query
  • 3. Set parameters
  • 4. Execute query

8

slide-9
SLIDE 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; }

  • 1. Build query string
  • 2. Create query
  • 3. Set parameters
  • 4. Execute query

9

  • Efficient
  • Flexible

Unsafe

slide-10
SLIDE 10

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; }

10

slide-11
SLIDE 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; }

11

slide-12
SLIDE 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; }

  • 1. Unset parameter

12

slide-13
SLIDE 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; }

13

  • 1. Unset parameter
slide-14
SLIDE 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, new Weblog()); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  • 1. Unset parameter
  • 2. Unsafe param type

14

slide-15
SLIDE 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, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

  • 1. Unset parameter
  • 2. Unsafe param type

15

slide-16
SLIDE 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);

Warranty w = (Warranty) q.execQuery();

return w.text; }

  • 1. Unset parameter
  • 2. Unsafe param type
  • 3. Unsafe downcast

16

slide-17
SLIDE 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); Weblog w = (Weblog) q.execQuery(); return w.text; }

  • 1. Unset parameter
  • 2. Unsafe param type
  • 3. Unsafe downcast

17

Java compiler does not reason about the query strings; cannot typecheck.

slide-18
SLIDE 18

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; }

  • 1. Unset parameter
  • 2. Unsafe param type
  • 3. Unsafe downcast

18

slide-19
SLIDE 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; }

  • 1. Unset parameter
  • 2. Unsafe param type
  • 3. Unsafe downcast

19

Refactor Don’t Refactor Need to know type

  • f w and w.link to

refactor safely.

slide-20
SLIDE 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; }

  • 1. Unset parameter
  • 2. Unsafe param type
  • 3. Unsafe downcast
  • 4. Refactoring difficult

20

slide-21
SLIDE 21

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; }

  • 1. Unset parameter
  • 2. Unsafe param type
  • 3. Unsafe downcast
  • 4. Refactoring difficult

21

slide-22
SLIDE 22

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

22

Is this query exec safe? Need to know:

  • 1. query string
  • 2. param types
slide-23
SLIDE 23

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; }

Bound Query:

  • query string
  • param types

23

query : “SELECT … ?1 … ?2 … ?3 …” ?1 : String ?2 : Weblog ?3 : unknown

Example :

At each program point map each var to a set of BQs.

slide-24
SLIDE 24

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; }

24

slide-25
SLIDE 25

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”

25

slide-26
SLIDE 26

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; }

26

qStr = “SELECT … ?2”

slide-27
SLIDE 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; }

qStr = “SELECT … ?2”

27

slide-28
SLIDE 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”

28

query : “SELECT …” ?1 : unknown ?2 : unknown

slide-29
SLIDE 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”

29

query : “SELECT …” ?1 : unknown ?2 : unknown query : “SELECT …” ?1 : String ?2 : unknown

slide-30
SLIDE 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”

30

query : “SELECT …” ?1 : unknown ?2 : unknown query : “SELECT …” ?1 : String ?2 : unknown query : “SELECT …” ?1 : String ?2 : int

slide-31
SLIDE 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; }

Checking param types:

  • 1. Parse query string
  • 2. Check all params set
  • 3. Check param types

Bound Queries:

31

query : “SELECT …” ?1 : String ?2 : int

slide-32
SLIDE 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; }

Checking result type:

  • 1. Infer result type
  • 2. Propagate result type
  • 3. Check downcasts

Bound Queries:

32

query : “SELECT …” ?1 : String ?2 : int query : “SELECT …” ?1 : String ?2 : int result : Weblog

slide-33
SLIDE 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; }

33

If a query passes Deep Typechecking, then it will not cause an error at runtime. Therefore, Bound Query Analysis has no silent failures.

slide-34
SLIDE 34

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; }

35

slide-35
SLIDE 35

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; }

36

Refactor Weblog field: id à name

slide-36
SLIDE 36

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; }

37

Refactor Weblog field: id à name

slide-37
SLIDE 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

Refactor Weblog field: id à name

slide-38
SLIDE 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; }

query :

SELECT w FROM Weblog w WHERE w.id = ?1 AND w.link.id = ?2

?1 : String ?2 : int

39

slide-39
SLIDE 39

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; }

query :

SELECT w FROM Weblog w WHERE w.id = ?1 AND w.link.id = ?2

?1 : String ?2 : int

40

Refactor Weblog field: id à name

  • 1. Refactor full query
slide-40
SLIDE 40

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; }

query :

SELECT w FROM Weblog w WHERE w.name = ?1 AND w.link.id = ?2

?1 : String ?2 : int

41

Refactor Weblog field: id à name

  • 1. Refactor full query
slide-41
SLIDE 41

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; }

query :

SELECT w FROM Weblog w WHERE w.name = ?1 AND w.link.id = ?2

?1 : String ?2 : int

42

Refactor Weblog field: id à name

  • 1. Refactor full query
  • 2. Propagate changes
slide-42
SLIDE 42

Deep Refactoring Example

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

43

Refactor Weblog field: id à name

  • 1. Refactor full query
  • 2. Propagate changes
slide-43
SLIDE 43

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; }

44

slide-44
SLIDE 44

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; }

45

slide-45
SLIDE 45

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; }

46

slide-46
SLIDE 46

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; }

47

slide-47
SLIDE 47

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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

48

slide-48
SLIDE 48

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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

49

slide-49
SLIDE 49

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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

qStr = “SELECT … ?2”

50

slide-50
SLIDE 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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

query : “SEL … ?2” ?1 : String ?2 : int

51

qStr = “SELECT … ?2”

slide-51
SLIDE 51

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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

query : “SEL … ?2” ?1 : String ?2 : int qStr = “SELECT … ?1”

52

qStr = “SELECT … ?2”

slide-52
SLIDE 52

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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

query : “SEL … ?2” ?1 : String ?2 : int qStr = “SELECT … ?1”

53

qStr = “SELECT … ?2”

slide-53
SLIDE 53

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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

query : “SEL … ?2” ?1 : String ?2 : int qStr = “SELECT … ?1”

54

qStr = “SELECT … ?2”

slide-54
SLIDE 54

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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

qStr = “SELECT … ?1” query : “SEL … ?1” ?1 : String query : “SEL … ?2” ?1 : String ?2 : int

55

qStr = “SELECT … ?2”

slide-55
SLIDE 55

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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

query : “SEL … ?1” ?1 : String

“SEL … ?2” ?1 : String ?2 : int “SEL … ?1” ?1 : String

query : “SEL … ?2” ?1 : String ?2 : int qStr = “SELECT … ?1”

56

qStr = “SELECT … ?2”

slide-56
SLIDE 56

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 { q = createQuery(qStr); q.setParam(1, id); } Weblog w = (Weblog) q.execQuery(); return w.text; }

“SEL … ?2” ?1 : String ?2 : int “SEL … ?1” ?1 : String

As before, for each bound query:

  • 1. Check param types
  • 2. Check result type

In general, we express Bound Query Analysis as a dataflow analysis.

57

slide-57
SLIDE 57

Loops

59

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

slide-58
SLIDE 58

Loops

60

String getText(String id, List<Link> links) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “OR w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

slide-59
SLIDE 59

Loops

61

String getText(String id, List<Link> links) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; qStr += “OR w.link.id = ?2”; q = createQuery(qStr); q.setParam(1, id); q.setParam(2, link.id); Weblog w = (Weblog) q.execQuery(); return w.text; }

slide-60
SLIDE 60

Loops

62

String getText(String id, List<Link> links) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; for(int i = 0; i < links.size(); i++) { qStr += “ OR w.link.id = ?” + i; } q = createQuery(qStr); ... }

slide-61
SLIDE 61

String getText(String id, List<Link> links) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; for(int i = 0; i < links.size(); i++) { qStr += “ OR w.link.id = ?” + i; } q = createQuery(qStr); ... }

Loops

63

qStr = ??? qStr = “SELECT … w.id = ?1” ( “ OR w.link.id = ?#” )*

slide-62
SLIDE 62

String getText(String id, List<Link> links) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; for(int i = 0; i < links.size(); i++) { qStr += “ OR w.link.id = ?” + i; } q = createQuery(qStr); ... }

Loops

64

qStr = “SELECT … w.id = ?1” ( “ OR w.link.id = ?#” )*

slide-63
SLIDE 63

String getText(String id, List<Link> links) { String qStr; Query q; qStr = “SELECT w FROM Weblog w ”; qStr += “WHERE w.id = ?1 ”; for(int i = 0; i < links.size(); i++) { qStr += “ OR w.link.id = ?” + i; } q = createQuery(qStr); ... }

Loops

65

qStr = “SELECT … w.id = ?1” ( “ OR w.link.id = ?#” )*

Deep Refactoring:

  • know query structure
  • know fragment locs
  • refactor across loops

Deep Typechecking:

  • unknown # of params
  • do not check params
  • can still check result
slide-64
SLIDE 64

Multiple Methods

66

String mainQueryStr() { return “SELECT ... ?1”; } Object getMain() { String qStr = mainQueryStr(); Query q = createQuery(qStr); q.setParam(1, “main”); return q.execQuery(); } String mainId() { return ((Weblog) getMain()).id; }

String Analysis :

  • interprocedural
  • compute regexps

Bound Query Analysis :

  • intraprocedural
  • no complex aliasing

Result Analysis :

  • interprocedural
  • propagate result type