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

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
Advanced topics in databases Multimedia Databases V. Megalooikonomou XML ( based on slides

Advanced topics in databases Multimedia Databases V. Megalooikonomou XML ( based on slides

Advanced topics in databases Multimedia Databases V. Megalooikonomou XML ( based on slides by Silberschatz, Korth and Sudarshan at Bell Labs and Indian Institute of Technology ) General Overview - XML Introduction Motivation

1.06k views • 71 slides
DISTRIBUTED DATABASES CHAPTER 25 LECTURE OVERVIEW What are distributed databases?

DISTRIBUTED DATABASES CHAPTER 25 LECTURE OVERVIEW What are distributed databases?

DISTRIBUTED DATABASES CHAPTER 25 LECTURE OVERVIEW What are distributed databases? Transparency and autonomy Fragmentation, allocation, replication Homogeneous vs. heterogeneous DDBMS Distributed transaction

1.27k views • 15 slides
Databases Peter Zaitsev Percona You Might Know I am passionate about Open Source Databases

Databases Peter Zaitsev Percona You Might Know I am passionate about Open Source Databases

Maximizing the Power and Value of Open Source Databases Peter Zaitsev Percona You Might Know I am passionate about Open Source Databases 2 Open Source Databases Leading in Growth Source: https://db-engines.com/en/ranking 3 Long Term

708 views • 42 slides
CSC 337 LECTURE 20: RELATIONAL DATABASES AND SQL Relational databases relational database : A

CSC 337 LECTURE 20: RELATIONAL DATABASES AND SQL Relational databases relational database : A

CSC 337 LECTURE 20: RELATIONAL DATABASES AND SQL Relational databases relational database : A method of structuring data as tables associated to each other by shared attributes. a table row corresponds to a unit of data called a record ;

1.1k views • 30 slides
CSE 154 LECTURE 13:RELATIONAL DATABASES AND SQL Relational databases relational database : A

CSE 154 LECTURE 13:RELATIONAL DATABASES AND SQL Relational databases relational database : A

CSE 154 LECTURE 13:RELATIONAL DATABASES AND SQL Relational databases relational database : A method of structuring data as tables associated to each other by shared attributes. a table row corresponds to a unit of data called a record ; a

521 views • 27 slides
CSE 154 LECTURE 22:RELATIONAL DATABASES AND SQL Relational databases relational database : A

CSE 154 LECTURE 22:RELATIONAL DATABASES AND SQL Relational databases relational database : A

CSE 154 LECTURE 22:RELATIONAL DATABASES AND SQL Relational databases relational database : A method of structuring data as tables associated to each other by shared attributes. a table row corresponds to a unit of data called a record ; a

602 views • 26 slides
Databases F. Sadri Contents: First Half Introduction Relational Databases Database

Databases F. Sadri Contents: First Half Introduction Relational Databases Database

Databases F. Sadri Contents: First Half Introduction Relational Databases Database Design (Normalisation) functional dependencies 1NF, 2NF, 3NF losslessness dependency preservation Boyce-Codd Normal Form 1 Contents cntd.

683 views • 39 slides
Relational Databases Week 13 LBSC 671 Creating Information Infrastructures Databases

Relational Databases Week 13 LBSC 671 Creating Information Infrastructures Databases

Relational Databases Week 13 LBSC 671 Creating Information Infrastructures Databases Database Collection of data, organized to support access Models some aspects of reality DataBase Management System (DBMS) Software to

732 views • 50 slides

More recommend