Java ãããžã§ã¯ãã®äœæã«é¢ããã·ãªãŒãºã®èšäºããã®ç®æšã¯äž»èŠãªãã¯ãããžãŒãåæããããšã§ããããã®çµæãšããŠé»å ±ããããäœæããããšã«ãªããŸããå°å
¥éšåã¯ãã¡ãã§ãã ã¢ããããœãããŠã§ã¢ãšã³ãžãã¢ã®çãããä»æ¥ã¯ããŒã¿ããŒã¹ãš SQL èšèªã«ã€ããŠè©±ããŸãããã®èšäºã¯èª°ã察象ãšããŠããŸãã? ã¯ããèå³ã®ããæ¹ã¯ã©ããããã®èšäºããæ
ãå§ãã人ãããã°ãèå³æ·±ãäºå®ã®èšæ¶ãæ°ãã«ãã人ãããŸãããã®éšåã¯çŽç²ã«çè«çãªãã®ã«ãªããŸããããŒã¿ããŒã¹ãš SQL æ§æãäœã§ãããã説æããåã«ããªãããããç 究ããã®ããç解ãã決å®ããå¿
èŠããããŸãããã®æ®µéã§ã¯ãSQL ããŒã¿ããŒã¹ã«ã€ããŠã®ã¿èª¬æããŸãããã®äžé£ã®èšäºã§ã¯ NoSQL ã«ã€ããŠã¯èæ
®ããŸããã
ããŒã¿ããŒã¹: ããã¯äœã§ãã
ããŒã¿ããŒã¹ (以äžãDB) ã¯ãã¯ãšãªèšèªã䜿çšããŠååŸã§ããæ§é åããŒã¿ãæ ŒçŽãããå Žæã§ããããŒã¿ããŒã¹ã¯ã倧éã®æ å ±ãä¿åããã ãã§ãªããåŠçããã³å€æŽããããšãã§ããŸãã泚: ããã€ãã®åºæºã«åºã¥ã㊠Excel ã®å€ã®ã»ããããã°ããå€æŽããŠã¿ãŠãã ãããååãšããŠãè€éãªããšã¯äœããããŸãããããžã¿ã«åãããŠããªãçŸå®äžçãšã¯ç°ãªããŸããããšãã°ãè»ç·ãéããéœåžã®ååãå€æŽããŸãããã¹ãŠãä»®æ³åãããŠããŒã¿ããŒã¹ã«å ¥åãããå Žåãããã¯æ£èŠåãããããã¯ããã€ãã®äºçŽ°ãªããšã«ãªããŸããçŸç¶ã§ã¯ãå€ãã®ã¹ãã¬ãŒãž ã¡ãã£ã¢ã¯èªåçã«å€æŽã§ããªãããã«åé¡ãæ±ããŠããŸãããã®æèã«ãããŠãããŒã¿ããŒã¹çš SQL ã¯ãããŒã¿ããŒã¹ãç解ããããã«å¿ããŠå¿çããèšèªã§ããããšãã°ãéœåžã®ååãå€æŽããã®ã¯é£ãããªããããããããããŠã·ã¯ãããããã«å€æŽãããšæ¬¡ã®ããã«ãªããŸããUPDATE city SET name = âÐМОпÑПâ WHERE id = 1231;
ãã以éã®ã·ã¹ãã ãžã®ãªã¯ãšã¹ãã¯ãã¹ãŠãå¿
èŠãªååããã§ã«çæããŠããŸãã
ãªãããŒã¿ããŒã¹ãå¿ èŠãªã®ã§ãããã?
ãã§ã«è¿°ã¹ãããã«ãããŒã¿ããŒã¹ã䜿çšãããšããŒã¿ãä¿åã§ããŸãããããããã®ããŒã¿ã¯äœã§ãããã? å®éã®äŸã䜿çšããŠç解ããããã«ãããã€ãã®ã¢ããªã±ãŒã·ã§ã³ã«ã€ããŠèª¬æããŸããããšãã°ãåãé»å ±ãããã«ã€ããŠã§ãããããã䜿çšããŠãã人ãäœäººããããç¥ããããšèããŠããŸããããã¯ã©ã®ããã«å®è£ ã§ããã®ã§ãããã? éåžžã® Java ã¢ããªã±ãŒã·ã§ã³ã§ã¯ããŠãŒã¶ãŒã®ããã¯ããŒã ããã£ãã ID ãæ ŒçŽãã Set ãšããåºæã®èŠçŽ ãå€æ°äœæã§ããŸããããã¯ããŸããããŸããïŒææãJava ã¢ããªã±ãŒã·ã§ã³ãåæ¢ããããŸã§ã®éãããã³ Java ã¢ããªã±ãŒã·ã§ã³ãåã³éå§ããããšããã«ãäžæã®èŠçŽ ã®ã»ããã¯ç©ºã«ãªããŸããã€ãŸããå®è¡äžã®ã¢ããªã±ãŒã·ã§ã³ã«ä¿åãããŠããããŒã¿ãåã«æ¶ããŠããŸã£ãã®ã§ããããã«ã€ããŠäœãã§ããã§ããããïŒã·ã¹ãã ç¶æ (ããŒã¿) ã®ã¹ãã¬ãŒãžã Java ã¢ããªã±ãŒã·ã§ã³ããå¥ã®å Žæã«ç§»åã§ããŸããããã CSV 圢åŒã§ã³ã³ãã¥ãŒã¿äžã®éåžžã®ãã¡ã€ã«ã«ä¿åã§ããŸããå¥ã®ãã¡ã€ã«ã® 1 è¡ã§å®è¡ã§ããŸãããããå®è¡ãããšãJava ã¢ããªã±ãŒã·ã§ã³ãåæ¢ããåã«ãã®ããŒã¿ãäžæžãã§ããŸããé²ç»ãæåãããšããä¿èšŒã¯ 100% ã«ã¯çšé ãã§ããããµãŒããŒã®é»æºã³ãŒããæãã ãã§ã¯é²ç»ã¯è¡ãããªãããã§ãããã®ã¢ãããŒãã«ã¯é倧ãªæ¬ ç¹ããããŸããããã¯ããã¡ã€ã« ã¬ãã«ã§æ å ±ãååŸãéçŽãæ€çŽ¢ããæ©èœããªãããšã§ããã¯ãããã¡ããããã¡ã€ã«ãèªã¿åã£ãŠãã®ãªããžã§ã¯ããäœæããããšã¯ã§ããŸãããæ¯åãã¹ãŠã®ããŒã¿ãã¢ããªã±ãŒã·ã§ã³ã«éã蟌ãå¿ èŠããããŸãããããŠããããã¯ãããšãã°æ°ã®ã¬ãã€ããªã©ã倧éã«ååšããå¯èœæ§ããããŸããããã¯ãããŒã¿ãããŒã¿ããŒã¹ã«ä¿åããããšã§åé¿ã§ããŸããã©ããã£ãŠïŒããŒãã«ãšããŒãã«éã®é¢ä¿ãéããŠãããã¯ããã¹ãŠã®ãªã¬ãŒã·ã§ãã« ããŒã¿ããŒã¹ã®åºç€ãšãªã£ãŠããŸããSQL: ããŒã¿ããŒã¹ãç解ã§ããèšèª
Database Management System (以äžãDBMS) ããããããããç解ã§ããèšèªã¯ SQL ã§ããSQL ã¯ãããŒã¿ããŒã¹ãæäœããã³ç®¡çããããã®ããã°ã©ãã³ã°èšèªã§ãããããã©ã®ããã«æ©èœããããç解ããããã«ã次ã®å³ãèŠãŠã¿ãŸãããããŠãŒã¶ãŒã¯ SQL ã¯ãšãªã DBMS ã«éä¿¡ããDBMS ã¯äœãè¡ãå¿ èŠãããããç解ãããããå®è¡ãããªã¯ãšã¹ããããŒã¿ãååŸãããã®ã§ããã°ããããè¿ããŸãããããã£ãŠãéçºè 㯠SQL ã¯ãšãªèšèªããã¹ã¿ãŒããå¿ èŠããããŸããããããå¥ã®èšèªã ããšæããããããŸãããç¥ã®å©ãã®ãããã§ãç§ã¯ãã㧠Java ãã»ãšãã©ç解ã§ããŸããããããªãã¯ããã«å¥ã®èšèªãç§ã«æäŸããŠãããŸããããããã¯çå®ã§ã¯ãããŸãããSQL ã¯ãããŒã¿ããŒã¹ãããã°ã©ãã³ã°å šè¬ã«ããŸãçžã®ãªãäŒèšå£«ã§ããã®èšèªã§èšè¿°ã§ããããã«ãDBMS ã®ä»æ§èšèªãšããŠçºæãããŸãããããã¯ããããåŠã¶ããšã¯ããã»ã©é£ãããªãããšãæå³ããŸããéèŠãªã®ã¯ç·Žç¿ãç·Žç¿ãç·Žç¿ã§ããä»æ¥ã¯çè«ã®è©±ã«ãªããŸããã次åã¯å®è·µç·šã§ãããªã¬ãŒã·ã§ãã« ããŒã¿ããŒã¹ã¯ 2 次å ã®é åããŒãã«ãšãããã®éã®é¢ä¿ã®ã»ããã§ããããããã®äœæ¥ã¯ããããåé¿ãããã®ã«ãªããŸããäŸã«ã€ããŠèª¬æãããšããåœããšãéœåžããšãã 2 ã€ã®ããŒãã«ãšãããããã©ã®ããã«æ¥ç¶ãããŠãããã瀺ãããšãã§ããŸãããã®æ®µéã§ã¯ãããŒãã«å ã®ã¬ã³ãŒãã Java äžçã®ãªããžã§ã¯ãã«é¢ããããŒã¿ã§ããããšãç解ããããšãéèŠã§ããããšãã°ãããã 2 ã€ã®ããŒãã«ã¯ Java ã§æ¬¡ã®ããã«èšè¿°ã§ããŸããpublic class Country {
private Long id;
private String name;
}
public class City {
private Long id;
private String name;
private Country country;
private Integer population;
}
ãŸãã綺éºãããªãã§ãããããŒã¿ããŒã¹ã«ã¯ä»»æã®æ°ã®ããŒãã«ãæ ŒçŽã§ããŸããç§ãã¡ã®å Žåãããã㯠2 ã€ãããŸãã
ããŒãã«æ§é
誰ããäœããã®åœ¢ã§ Excel ã®è¡šã«ééããããšããããè¡ãšåãšããçšèªã¯ç解ããŠãããšæããŸããããŒã¿ããŒã¹ã®ã³ã³ããã¹ãã§ã¯ãã¬ã³ãŒããšãã£ãŒã«ãã«ã€ããŠè©³ãã説æããŸãããããã£ãŠãCity ã¯ã©ã¹ã®åãªããžã§ã¯ãã¯ãããŒã¿ããŒã¹å ã®ããŒãã«å ã®ã¬ã³ãŒãã§ããããšãããããŸããäž»ããŒ
ããŒã¿ããŒã¹å ã®ãã£ãŒã«ããåãå€ãæã€ããšããããããŸããããšãã°ããœãŒã·ã£ã« ãããã¯ãŒã¯ã§ã¯ãååãå§ãããã«ã¯ç¶ç§°ã§è€æ°ã®äººãèŠã€ããããšãã§ããŸãããŸãããªã¬ãŒã·ã§ãã« ããŒã¿ããŒã¹ã«ã¯ãã¬ã³ãŒããžã®ã¢ã¯ã»ã¹ã«äœ¿çšã§ããäžæã®ãã£ãŒã«ããå¿ èŠã§ããããã¯ãæåã®ããŒãŸãã¯äž»ããŒãšåŒã°ããŸããéåžžãID(id) ãã£ãŒã«ãã¯ãã®ãããªããŒãšããŠäœ¿çšãããŸããããã¯èå¥åã®ç¥ã§ãããã®ããããã¹ãŠã®ããŒãã«ã«IDãã£ãŒã«ããè¿œå ããå¿ èŠããããŸããå€éšããŒ
ãã®äŸã§ã¯ããã®ãããªãã£ãŒã«ã㯠City ããŒãã«ã«ããã Country ã®ããŒã䜿çšããŸãããããŠãããã¯æ¬¡ã®ããã«æ©èœããŸããåéœåžã¯ãã®åœã®äžæã®æ å ±èå¥åãç¥ã£ãŠããããããååŸããŠããŒã¿ããŒã¹ã«ã¯ãšãªãäœæãããšããã®åœã«é¢ããå æ¬çãªæ å ±ãåŸãããŸããå³ãããããããã«ã2 ã€ã®ããŒãã«ã®éã«ã¯é¢ä¿ããããŸããå€éšããŒã®åçãããã«ç€ºããŸããSQLã«ã¯ã©ã®ãããªã»ã¯ã·ã§ã³ããããŸãã?
ã¡ãªã¿ã«ãé¢æ¥ã§ã¯ SQL ã§ã¯ã©ã®ãããªæäœãè¡ãããã®ããã質åãããŸãã- DDL (ããŒã¿å®çŸ©èšèª) ã¯ãããŒãã«ããã®æ§é ãªã©ãå€æŽ/äœæããæŒç®åã®ã°ã«ãŒãã§ããã€ãŸããããŒãã«ã®äœæãããŒãã«ã®åé€ãããŒãã«å ã®ãã£ãŒã«ãã®äœæ/åé€ã§ããæ°ããäž»ããŒã®äœæãªã©ã
- DML (Data Manipulation Language) ã¯ãããŒã¿ã®å€æŽã管çããæŒç®åã®ã°ã«ãŒãã§ãããããã¯ãã¹ãŠãããŒã¿ããŒã¹å ã®ããŒã¿ãå€æŽããæäœ (è¿œå ãååŸãå€æŽãåé€) ã§ãã
- DCL (ããŒã¿å¶åŸ¡èšèª) ã¯ãã¢ã¯ã·ã§ã³ãå®è¡ããããã®ãŠãŒã¶ãŒæš©éã確èªããæ段ã§ããç¹å®ã®ãŠãŒã¶ãŒã DDL/DML æäœãå®è¡ã§ããããã«ãã¢ã¯ã»ã¹æš©ãšæš©éãä»äžããæäœã
SQLã«ã¯ã©ã®ãããªããŒã¿åãããã®ã
ããŒãã«ã¯ç¹å®ã®çš®é¡ã®ããŒã¿ãä¿åããã³åŠçã§ããŸããããã«ãããã®ã¯ãã¹ãŠãJava ã§äœ¿çšãããã®ãšäŒŒãŠããŸããäž»ãªãã®ã«ã€ããŠè©±ããŸããããããã㯠3 ã€ã ãã§ãããå¿ èŠãŸãã¯åžæã«å¿ããŠä»ã®ãã®ãè¿œå ããŸããåçãããããããã«ããããã¯æ¬¡ã®ãšããã§ãã- INT - æŽæ°å€ãäžæã®èå¥åãšåçŽãªæŽæ°ã«äœ¿çšãããŸãã
- VARCHARã¯æååã§ãã
- DATE㯠LocalDate ã§ãã
GO TO FULL VERSION