communicating with databases
play

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

Jan 03, 2024 •434 likes •1.09k 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

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

  2. 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: u Expressed in Object Relational Mapping (ORM) 2

  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

  4. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; 1. Build query string 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; } 4

  5. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; 1. Build query string qStr = “SELECT w FROM Weblog w ”; 2. Create query 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

  6. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; 1. Build query string qStr = “SELECT w FROM Weblog w ”; 2. Create query qStr += “WHERE w.id = ?1 ”; 3. Set parameters 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; } 6

  7. Example: Using JPA to query DB String getText(String id, Link link) { String qStr; Query q; 1. Build query string qStr = “SELECT w FROM Weblog w ”; 2. Create query qStr += “WHERE w.id = ?1 ”; 3. Set parameters 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; } 7

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

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

  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

  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

  12. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; 1. Unset parameter 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

  13. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; 1. Unset parameter 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

  14. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; 1. Unset parameter qStr = “SELECT w FROM Weblog w ”; 2. Unsafe param type 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; } 14

  15. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; 1. Unset parameter qStr = “SELECT w FROM Weblog w ”; 2. Unsafe param type 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; } 15

  16. Uncaught Errors String getText(String id, Link link) { String qStr; Query q; 1. Unset parameter qStr = “SELECT w FROM Weblog w ”; 2. Unsafe param type qStr += “WHERE w.id = ?1 ”; 3. Unsafe downcast 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; } 16

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

  18. Refactor: Weblog.id à Weblog.name String getText(String id, Link link) { String qStr; Query q; 1. Unset parameter qStr = “SELECT w FROM Weblog w ”; 2. Unsafe param type qStr += “WHERE w.id = ?1 ”; 3. Unsafe downcast 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; } 18

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

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

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

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

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

  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

  25. Bound Query Analysis String getText(String id, Link link) { qStr = “SELECT … ?2” 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; } 25

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend

Creating Databases and Tables Introduction to Databases in Python Creating Databases

Creating Databases and Tables Introduction to Databases in Python Creating Databases

INTRODUCTION TO DATABASES IN PYTHON Creating Databases and Tables Introduction to Databases in Python Creating Databases Varies by the database type Databases like PostgreSQL and MySQL have command line tools to initialize

878 views • 31 slides
Databases and PHP Accessing databases from PHP PHP & Databases l PHP can connect to

Databases and PHP Accessing databases from PHP PHP & Databases l PHP can connect to

Databases and PHP Accessing databases from PHP PHP & Databases l PHP can connect to virtually any database l There are specific functions built-into PHP to connect with some DB l There is also generic ODBC functions that will work with many

868 views • 28 slides
Communicating results with R&Shiny Mika Mkinen Introduction 1. Communicating results (not

Communicating results with R&Shiny Mika Mkinen Introduction 1. Communicating results (not

PHUSE 2016 DV02 Communicating results with R&Shiny Mika Mkinen Introduction 1. Communicating results (not only producing outputs) 2. Cases from a real study 3. Visualization concept Motivation for interactive & better

740 views • 24 slides
Communicating Rewards: Where we go wrong and how we can improve Tom Raftery Director

Communicating Rewards: Where we go wrong and how we can improve Tom Raftery Director

Communicating Rewards: Where we go wrong and how we can improve Tom Raftery Director Compensation & Benefits Forum 19 June 2012 Dubai What are you communicating about your reward system? Problem of communicating rewards

208 views • 16 slides
Communicating feedback and assessment Dr Joanna Tai and Dr Naomi Winstone Communicating

Communicating feedback and assessment Dr Joanna Tai and Dr Naomi Winstone Communicating

Communicating feedback and assessment Dr Joanna Tai and Dr Naomi Winstone Communicating assessment rationales Which two of these assessments are least familiar to you? Essay Research report Presentation MCQ Exam Portfolio

369 views • 19 slides
3. Text and document databases Normal databases: formatted records; document databases:

3. Text and document databases Normal databases: formatted records; document databases:

3. Text and document databases Normal databases: formatted records; document databases: free-form or semi-structured data (e.g. XML). Application areas: - Office automation, document archives - Digital libraries - Electronic dictionaries

741 views • 38 slides
Communicating Critical Events: Communicating Critical Events: CEO Transitions and Risk to

Communicating Critical Events: Communicating Critical Events: CEO Transitions and Risk to

Communicating Critical Events: Communicating Critical Events: CEO Transitions and Risk to Enterprise Value FTI Consulting | Strategic Communications Practice October 2011 1 Global leadership transitions Research objective & methodology

838 views • 26 slides
Module 3: Creating and Managing Databases Overview Creating Databases Creating

Module 3: Creating and Managing Databases Overview Creating Databases Creating

Module 3: Creating and Managing Databases Overview Creating Databases Creating Filegroups Managing Databases Introduction to Data Structures Creating Databases Defining Databases How the Transaction Log Works

445 views • 18 slides
CSE 462 - Databases Oliver Kennedy okennedy@buffalo.edu 1 Why Study Databases? 2 3 3 2

CSE 462 - Databases Oliver Kennedy okennedy@buffalo.edu 1 Why Study Databases? 2 3 3 2

CSE 462 - Databases Oliver Kennedy okennedy@buffalo.edu 1 Why Study Databases? 2 3 3 2 Queries per Second 3 Interesting Problems Algorithms Systems Databases Hardware Theory 4 $$$ 8 of the top 10 Forbes Global 2000 Software &

1.12k views • 73 slides
Developing & Communicating Your Employee Benefit Program Developing & Communicating Your

Developing & Communicating Your Employee Benefit Program Developing & Communicating Your

Developing & Communicating Your Employee Benefit Program Developing & Communicating Your Employee Benefit Program Scott Smith : Executive Vice President- National Practice Leader/Employee Benefits at SS Nesbitt and Company

607 views • 34 slides
Introduc)on*to*Databases 1 Rela%onal(Databases(with(PostgreSQL

Introduc)on*to*Databases 1 Rela%onal(Databases(with(PostgreSQL

Introduc)on*to*Databases 1 Rela%onal(Databases(with(PostgreSQL Databases(have(tables(to(classify(data Collec3ons(have: rows :(data(defining(an(en3re(rectod,(e.g.(a(user columns

862 views • 25 slides
Introduction to Databases Introduction to Databases in Python A database consists of tables

Introduction to Databases Introduction to Databases in Python A database consists of tables

INTRODUCTION TO DATABASES IN PYTHON Introduction to Databases Introduction to Databases in Python A database consists of tables Census State_Fact state sex age pop2000 pop2008 name abbreviation type New F 0 120355 122194 New York

699 views • 27 slides
HUDOC databases: CPT and ESC Patrick Mller HUDOC databases: CPT and ESC HUDOC HUDOC = Human

HUDOC databases: CPT and ESC Patrick Mller HUDOC databases: CPT and ESC HUDOC HUDOC = Human

HUDOC databases: CPT and ESC Patrick Mller HUDOC databases: CPT and ESC HUDOC HUDOC = Human Rights DOCumentation HUDOC is more than ECHR case-law CPT and ESC databases existed, but were not very visible, and technically

371 views • 9 slides
CS377: Database Systems Distributed Databases Distributed Databases

CS377: Database Systems Distributed Databases Distributed Databases

CS377: Database Systems Distributed Databases Distributed Databases Department of Mathematics and Computer Science Emory University 1 Centralized DBMS on a Network

669 views • 29 slides
1 2 What is covered in this presentation? A brief history of databases NoSQL WHY, WHAT

1 2 What is covered in this presentation? A brief history of databases NoSQL WHY, WHAT

1 2 What is covered in this presentation? A brief history of databases NoSQL WHY, WHAT & WHEN? Characteristics of NoSQL databases Aggregate data models CAP theorem Ashwani Kumar 16 February 2018 NOSQL Databases 3

647 views • 31 slides
Using Social Science Research to Overcome Challenges Communicating HTE FDA and JHU-CERSI:

Using Social Science Research to Overcome Challenges Communicating HTE FDA and JHU-CERSI:

Using Social Science Research to Overcome Challenges Communicating HTE FDA and JHU-CERSI: Assessing and Communicating Heterogeneity of Treatment Effects (HTE) for Patient Subpopulations: Challenges and Opportunities November 28, 2018 Paula

267 views • 8 slides
The Evolution of Web Content and Search Engines Ricardo Baeza Baeza- -Yates Yates Ricardo

The Evolution of Web Content and Search Engines Ricardo Baeza Baeza- -Yates Yates Ricardo

The Evolution of Web Content and Search Engines Ricardo Baeza Baeza- -Yates Yates Ricardo Yahoo! Research, DCC/UChile, UPF/Spain lvaro lvaro Pereira Pereira Jr Jr DCC/UFMG/Brazil Nivio Ziviani Ziviani Nivio DCC/UFMG/Brazil

269 views • 15 slides
Implementing a Real-Time Survey System and the 6E Framework An Early Case Study on Improving

Implementing a Real-Time Survey System and the 6E Framework An Early Case Study on Improving

Implementing a Real-Time Survey System and the 6E Framework An Early Case Study on Improving Patient Experience - Health Informatics Conference - Aug 2017 - Kay de Ridder, Program Manager - Patient Centered Care Clinical Excellence

599 views • 22 slides
Advisory Committee of CERN Users Report Eric Torrence University of Oregon US-LUA Annual

Advisory Committee of CERN Users Report Eric Torrence University of Oregon US-LUA Annual

Advisory Committee of CERN Users Report Eric Torrence University of Oregon US-LUA Annual Meeting November 13, 2015 What is the ACCU? Advisory Committee of CERN Users (ACCU) Advisory to the DG on matters of conditions of users at

235 views • 11 slides
Principles of Software Construction: Objects, Design, and Concurrency A formal design process

Principles of Software Construction: Objects, Design, and Concurrency A formal design process

Principles of Software Construction: Objects, Design, and Concurrency A formal design process Josh Bloch Charlie Garrod Darya Melicher 17-214 1 Administrivia Homework 2 feedback in your GitHub repository Homework 3 due today at

829 views • 28 slides
Discover the Power in YOUR story with AT and Creative Media Presented by: Brooke Brown Founder

Discover the Power in YOUR story with AT and Creative Media Presented by: Brooke Brown Founder

Discover the Power in YOUR story with AT and Creative Media Presented by: Brooke Brown Founder and Story Editor Brooke's Butterfly Touch: Creative Storytelling Services Brookes Credentials I am the author of The Little Butterfly Girl ,

565 views • 17 slides
Opinion Extraction Task Opinion Mining Reviews A popular topic in opinion analysis is

Opinion Extraction Task Opinion Mining Reviews A popular topic in opinion analysis is

Opinion Extraction Task Opinion Mining Reviews A popular topic in opinion analysis is extracting sentiments [Kobayashi et al., 2007] take the approach that most evaluative related to products, entertainment, and service industries. opinions

576 views • 5 slides
ManaTI Web Assistance for the Threat Analyst, supported by Domain Similarity SEBASTIN GARCA

ManaTI Web Assistance for the Threat Analyst, supported by Domain Similarity SEBASTIN GARCA

ManaTI Web Assistance for the Threat Analyst, supported by Domain Similarity SEBASTIN GARCA RAL BENTEZ NETTO sebastian.garcia@agents.fel.cvut.cz raulbeni@gmail.com @eldracote @Piuliss Czech Technical University in Prague

757 views • 34 slides
Linearization Error Polina Zheglova DNOISE Seminar, October 16, 2013 1/7 Linearization Error

Linearization Error Polina Zheglova DNOISE Seminar, October 16, 2013 1/7 Linearization Error

Linearization Error Polina Zheglova DNOISE Seminar, October 16, 2013 1/7 Linearization Error Velocity Model c = v + v v = vr v = v ( z ) is smooth r = r ( z ) is oscillatory random Standard deviation of r is 5% 2/7 Linearization Error

363 views • 7 slides

More recommend