index.md (5306B)
1 +++ 2 title = "Database Application Programming" 3 +++ 4 5 # Database Application Programming 6 7 Various ways of using database tech: 8 9  10 11 how to access database from apps: 12 13 - static embedded queries: 14 - preprocessor-based, static SQL 15 - e.g. SQLJ, Embedded SQL (C/C+) 16 - dynamic: 17 - SQL queries are constructed at runtime 18 - e.g. JDBC, Python DATABASE-API, ODBC, etc. 19 - object relational mappings (ORM), etc.: 20 - hide navigational access behind objects 21 - e.g. JPA/Hibernate, Ruby on Rails, ADO.NET/LinQ 22 23 ## Dynamic 24 25 ### JDBC 26 27 provides APIs like `getInt(string)`, `getString(string)` to fetch cols 28 by name. can run `executeQuery(sql query)` to run SQL statements. 29 30 matches approx. SQL types to Java types, not always precise (String vs 31 VARCHAR(20)) 32 33  34 35 to improve performance of JDBC apps: 36 37 - connection pooling: keep database connection open, reduces latency 38 - prepared statements: 39 - SQL calls that are repeated often 40 - allows driver to optimise queries (precompiled by DBMS) 41 - created with `Connection.prepareStatement()` 42 - allows parameters 43 - e.g.: `SELECT * FROM products WHERE id = ?` 44 - store procedures to reduce roundtrips 45 - written in DATABASE-specific language, not portable 46 - accessed with `Connection.prepareCall()` 47 - use driver that\'s bulk-transfer optimised 48 - for large result sets 49 - driver can send multiple tuples in a single network packet 50 51 ### SQL injection 52 53 Exploit SQL statement construction to run your own commands. 54 55 Wrong: 56 57 ```java 58 stat.executeQuery("SELECT balance FROM accounts " + "WHERE name = '" + userName + "'" + " AND passwd = '" + userPassword + "'"); 59 ``` 60 61 So if you fill `'; SELECT * FROM accounts`, it will show all accounts. 62 You can also then update, drop, and do anything you want. 63 64 Solution: don\'t use string concatenation, use prepared statements. 65 66 ```java 67 stat = conn.prepareStatement("SELECT balance FROM accounts " + "WHERE name = ? " + " AND passwd = ?"); 68 stat.setString(1, userName); 69 stat.setString(2, userPassword); 70 stat.executeQuery(); 71 ``` 72 73 ### Impedance mismatch 74 75 database query language does not match app programming language. 76 77 static API (SQLJ): 78 79 - mismatch between SQL and Java types (`isNull`) 80 - SQL checked for correctness at development time 81 - inflexible (preprocessor needed) 82 83 dynamic API (JDBC): 84 85 - mismatch between SQL and Java types (`isNull`) 86 - powerful, flexible, but error-prone 87 - SQL query given as strings may be incorrect, no error checking at 88 dev time and col names/types are unknown at compile time 89 - risk of SQL injection 90 91 an object relational mapping is one attempt to improve even more. 92 93 ### Object Relational Mapping 94 95 maps rows in tables to objects: 96 97 - table ≈ class 98 - row ≈ object 99 - foreign key navigation ≈ pointers/references 100 101 mapping from objects to database (automatic/designed). run-time library 102 handles interaction with database. 103 104  105 106 but you might end up being inefficient and doing a huge amount of stuff 107 in Java that you could just do in SQL and have the DBMS optimise it for 108 you. 109 110 JPA/Hibernate HQL queries: 111 112 - a way to access members, e.g. `employee.department.name` 113 - query may return objects 114 - more efficient, under the hood it\'s translated to joins 115 116 Important aspects of ORM toolkits: 117 118 - mapping specification: map relational data onto objects 119 - query language (e.g. HQL): adds object oriented features to SQL, 120 typically queries as strings 121 - persistence: transaction semantics, languages offer start of 122 transactions/commit/abort 123 - fetch strategies: danger of implementing queries in Java, object 124 caching 125 126 Challenges of ORM: 127 128 - can introduce additional level of complexity, and be difficult to 129 debug 130 - performance analysis is problematic: 131 - database queries are under the hood 132 - sometimes you end up with very complex SQL queries 133 - it\'s difficult to understand what caused the complexity 134 135 #### ADO.NET entity framework 136 137 - different apps can have different views on the data 138 - views are fully client side. avoid polluting database schema with 139 per-app views, no added database-side maintenance 140 - powerful, broad set of updatable views, updatability can be 141 statically verified 142 - Entity Data Model (EDM): 143 - data representation on client side 144 - entity type: structured record with key 145 - entity: instance of Entity Type 146 - entity types can inherit from other entity types 147 148 #### LinQ 149 150 Language Integrated Query. you can query data structures using SQL-like 151 syntax 152 153 advantages: 154 155 - queries are first-class citizens (not strings) 156 - full type-checking and error checking for queries 157 - allows to query all collection structures (lists, sets, etc.) 158 159 disadvantages: 160 161 - not portable, only C\# and VB 162 163 so you can do like 164 165 ```sql 166 int[] my array = new int[] { 5, 3, 2, 6, 4, 2, 5 }; 167 var oddNumbers = from i in myarray where i \% 2 == 1 select i; 168 ``` 169 170 the LinQ runtime translates that to an SQL query, and converts returned 171 rows to objects. provides change tracking, concurrency control, object 172 identity. 173 174 under the hood, the LinQ query is actually chained function calls with 175 lambda expressions.