1 / 89

DB Programming

DB Programming. Database Systems Presented by Rubi Boim. Basic MySQL Usage Little More Complex MySQL stuff.. JDBC Coding Tips. Agenda. There are 3 main groups of types: Numeric Date String http://dev.mysql.com/doc/refman/5.0/en/data-types.html. MySQL Data Types. Integers

merle
Download Presentation

DB Programming

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. DB Programming Database Systems Presented by Rubi Boim

  2. Basic MySQL Usage Little More Complex MySQL stuff.. JDBC Coding Tips Agenda

  3. There are 3 main groups of types: • Numeric • Date • String • http://dev.mysql.com/doc/refman/5.0/en/data-types.html MySQL Data Types

  4. Integers INT(M) – number of digits to display..(no restrictions… don’t use it..) MySQL Data Types - Numeric

  5. Approximate Value Float/Double Float(M,D) – M=#digits, D=#digits after “.” Float(7,4) will look like -999.9999 Exact-Value Decimal (==Numeric) Decimal(5,2) range from -999.99 to 999.99 Numeric (Floating-Point)

  6. Bit(M) – number of bits.. Bit = Bit(1) Numeric (Bit)

  7. Date - range is '1000-01-01' to '9999-12-31' DateTime - 'YYYY-MM-DD HH:MM:SS‘ Timestamp - range is '1970-01-01 00:00:01' to '2038-01-19 03:14:07‘(number of seconds since..) MySQL Data Types – Date/Time

  8. Zero values ODBC can’t handle 0  convert to null (Use the table for the types..) MySQL Data Types – Date/Time

  9. Storage MySQL Data Types – Date/Time

  10. Important FunctionsDate_format, Datediff, Dayname…..http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html MySQL Data Types – Date/Time

  11. Char and Varchar are similar but differ in: Storage – Chars are “padded” Max length: char(255), varchar(65535) MySQL Data Types - String

  12. For larger size use Blob and Text Blob - binary strings (byte strings). They have no character set.. Text - They have a character set, and values are sorted and compared based on the character set. MySQL Data Types - String

  13. Blob - TINYBLOB BLOB MEDIUMBLOB LONGBLOB Text - TINYTEXT TEXT MEDIUMTEXT LONGTEXT MySQL Data Types - String

  14. MySQL Data Types - String

  15. Don’t forget to define the primary key on the other table.. What happens when you delete the “key record” from the “primary table”? - Restrict - Cascade - Set null Define Foreign keys

  16. Define Foreign keys

  17. Demo.. - create table (data types) - define primary key - define foreign keys (insert / delete data) Basic oracle usage - Demo

  18. Basic MySQL Usage Little More Complex MySQL stuff.. JDBC Coding Tips Agenda

  19. Index improves the speed of operations on a table Can be created using one or more fields You will later learn more.. But don’t forget, its important Index

  20. Index - HowTo

  21. Clustered Index Index – Clustered

  22. How do you know how to assign an ID?? “AutoNumber”

  23. Lock table new_id = 1 + select max id from table insert into table values(new_id, ”Rubi”); Unlock table “AutoNumber” – Algorithm?

  24. Just mark a simple flag.. In Oracle you need to define a “Sequence” and to use it via a “Trigger”.. MySQL – Life is easy…

  25. A database trigger is procedural code that is automatically executed in response to certain events on a particular table Events: BEFORE INSERT AFTER INSERT BEFORE UPDATE AFTER UPDATE BEFORE DELETE AFTER DELETE Triggers

  26. Occurs for each row CREATE OR REPLACE TRIGGER <trigger_name> <BEFORE | AFTER> <ACTION> ON <table_name> FOR EACH ROW BEGIN  <trigger_code> END; Triggers – Row Level

  27. You can not “just use the GUI” - you need to “code” the trigger” Triggers – Row Level – Example

  28. Use “NEW” to refer to the row CREATE TRIGGER count_check BEFORE INSERT ON student FOR EACH ROW BEGIN IF NEW.count < 0 THEN SET NEW.count = 0; ELSEIF NEW.count > 100 THEN SET NEW.count = 100; END IF; END; Triggers – Row Level – Example

  29. What if your query returns 1,000,000 results? How to return the TOP n results How to return the results from n to m Limit the Results

  30. What if your query returns 1,000,000 results? How to return the TOP n results How to return the results from n to m Limit the Results

  31. Very simple… just use the “Limit” keywordLIMIT [offset,] row_count SELECT * FROM `sakila`.`film` limit 10,5 MySQL’s Limit

  32. FYI… (We are using MySQL this semester..) Its assigned BEFORE sorting or aggregation ROWNUM value is incremented only after it is assigned Read the previous two lines 5 more times! Oracle’s Rownum – NOT THAT SIMPLE!

  33. SELECT * FROM students WHERE ROWNUM > 1 What NOT to do…  Oracle’s Rownum – Example

  34. SELECT * FROM ( SELECT a.*, ROWNUM rnum FROM ( SELECT * FROM students ORDER BY students.name ) a WHERE ROWNUM < 20 ) WHERE rnum >= 10 That’s the way…  Oracle’s Rownum – How to Limit..

  35. Demo.. - Create index - Create “Autonumber”: - Create Sequence - Create Trigger - Create Trigger - Limit the results.. Little More Complex MySQL Stuff

  36. A schema can contain tables of different engines Depends on the usage.. IMPORTANT TO UNDERSTAND THE DIFFERENCES!!!! http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.htmlhttp://www.kavoir.com/2009/09/mysql-engines-innodb-vs-myisam-a-comparison-of-pros-and-cons.html Table Engine – InnoDBvsMyISAM

  37. strict in data integrity supports foreign keys supports transactions (MyISAM does not..) Row-level lock for insert and update (MyISAM is Table-level) Better crash recovery InnoDB Advantages

  38. Full-text Indexing! (InnoDB does not..) Faster… Reads are more efficient When a single user use the system (y?), batch inserts are MUCH MUCH faster MyISAM Advantages

  39. Not so simple… but here are a few rules: If you need foreign keys  InnoDB If you need transactions  InnoDB If you need Fulltext Index  MyISAM More speed  MyISAMBUT only if not used by users simultaneously How to choose?

  40. If you are not using both type in the project you are doing something wrong……… Important Tip

  41. Basic MySQL Usage Little More Complex MySQL stuff.. JDBC Coding Tips Agenda

  42. During the last episode… Application DB infrastructure DB driver transport DB engine Storage

  43. Concepts vs APIs Concepts APIs/Language Connection Connection pooling Error Handling Fetching results Rowset Prepared statements Batch processing ODBC JDBC OCI/OCCI ADO.NET X

  44. ODBC – Open Database Connectivity API • Pros: • Cross platform and cross databases • Easy to use • Cons: • Too low level • We wont use it.. But its very very common

  45. JDBC is a standard interface for connecting to relational databases from Java JDBC

  46. How to execute SQL using JDBC

  47. Download MySQL’sJDBC driver:http://www.mysql.com/downloads/connector/j/ Can also be found at the course page Setup Eclipse: - add “mysql-connector-java-5.1.15-bin.jar” to the project Preparing the Environment 1

  48. Preparing the Environment 2 If you copy the jar file to the project directory, press “add JAR”. Otherwise, “Add external JAR”

  49. import java.sql.* (JDBC API) Register the driver in the code:Class.forName("com.mysql.jdbc.Driver"); Preparing the Environment 3

  50. Connection class - java.sql.Connection use the DriverManager with JDBC URL conn = DriverManager.getConnection( "jdbc:mysql://host_addr:port/schema_name" “username", “password"); Opening a Connection

More Related