1. SQLã©ã€ãã©ãªãšã¯
SQLã©ã€ãã©ãªã¯ãããŒã¿ããŒã¹ç®¡çã·ã¹ãã ïŒDBMSïŒãããå¹ççã«æ±ãäžã§å¿ èŠäžå¯æ¬ ãªãã®ã§ããããã¯ãSQLã®ã¯ãšãªãäœæãç·šéãå®è¡ããããã®ããŒã«ãé¢æ°ãéçŽãããã®ã§ããããããå¹æçã«äœ¿çšããããšã§ããŒã¿æäœã®äœæ¥ãå€§å¹ ã«å¹çåããããšãã§ããŸãã
SQLã©ã€ãã©ãªã¯æ§ã ãªããã°ã©ãã³ã°èšèªã§å©çšå¯èœã§ããããã®å€ãã¯ãªãŒãã³ãœãŒã¹ãšããŠæäŸãããŠããŸãããã®äžéšã§ã¯ãSQLã¯ãšãªãèªåçæããããã®æ©èœãDBMSãšã®æ¥ç¶ã容æã«ããããã®æ©èœãæäŸãããŠããŸãã
ãã®èšäºã§ã¯ãSQLã©ã€ãã©ãªã®åºæ¬æŠå¿µããã®çšéãéèŠæ§ãåºæ¬çãªäœ¿ãæ¹ã«ã€ããŠè§£èª¬ããŠãããŸãã
1.1 SQLãšã¯
SQLïŒStructured Query LanguageïŒã¯ããªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹ç®¡çã·ã¹ãã ïŒRDBMSïŒã§ããŒã¿ç®¡çãè¡ãããã®èšèªã§ããããŒã¿ã®æ¿å ¥ãæŽæ°ãåé€ãæ€çŽ¢ãªã©å¹ åºãæäœãè¡ãããšãã§ããŸãã
SQLã¯ããã®æšæºçãªæ©èœãšå ±ã«å€ãã®DBMSã§äœ¿ãããŠããããã®æ¹èšïŒç¹å®ã®DBMSå°çšã®ã³ãã³ããæ©èœïŒãååšããŸããããããæšæºçãªSQLã³ãã³ãã¯ã»ãšãã©ã®DBMSã§å ±éããŠãããäžåºŠåŠç¿ããã°æ§ã ãªã·ã¹ãã ã§å©çšããããšãå¯èœã§ãã
ãããŠãSQLã®ã¯ãšãªãããå¹ççã«æžãããã«ã¯SQLã©ã€ãã©ãªã®äœ¿çšãæ¬ ãããŸããã
1.2 ã©ã€ãã©ãªãšã¯
ã©ã€ãã©ãªãšã¯ãäºãå®çŸ©ãããé¢æ°ãã¯ã©ã¹ãå®æ°ãªã©ããŸãšãããã®ã§ãããã䜿çšããããšã§ããã°ã©ãã³ã°ã®å¹çãäžããããšãå¯èœã§ããããããã®é¢æ°ãã¯ã©ã¹ã¯ç¹å®ã®ã¿ã¹ã¯ãåŠçããããã«èšèšãããŠãããåãœãããŠã§ã¢ãããã¯ãããããå©çšããããšã§äŸ¿å©ãªæ©èœãè¿œå ããããšãã§ããŸãã
ã©ã€ãã©ãªã¯äžè¬çã«ã¯API(Application Programming Interface)ãšåŒã°ãã圢ã§æäŸããã䜿çšè ã¯ãã®APIã«åŸã£ãŠã©ã€ãã©ãªãæäœããŸãããã®ãããã©ã€ãã©ãªã«ãã£ãŠæäŸãããæ©èœã®ç解ã¯ããã°ã©ãã³ã°ã«ãããŠéåžžã«éèŠãªã¹ãã«ãšãªããŸãã
ãããŠãSQLã©ã€ãã©ãªã¯ãã®äžéšã§ãããäž»ã«SQLã®æäœãç°¡ç¥åããããã®åçš®é¢æ°ãããŒã«ãæäŸããŠããŸãã
1.3 SQLã©ã€ãã©ãªã®å¿ èŠæ§
SQLã©ã€ãã©ãªã®æ倧ã®å©ç¹ã¯ãã³ãŒãã®ç°¡æåãšçç£æ§ã®åäžã§ããæåã§SQLã¯ãšãªãæžãã®ã¯æéããããã ãã§ãªããæœåšçãªãšã©ãŒã®åå ãšãªãããŸããããããSQLã©ã€ãã©ãªã䜿çšããã°ãSQLã³ãŒãã®çæãããŒã¿ããŒã¹æäœãç°¡åã«è¡ãããšãã§ããããã«ãã£ãŠå šäœã®çç£æ§ãåäžãããããšãå¯èœã§ãã
ããã«ãSQLã©ã€ãã©ãªã䜿çšãããšãè€éãªã¯ãšãªã容æã«äœæããããšãå¯èœãšãªããŸããããã¯ãã©ã€ãã©ãªãæäŸãã匷åãªAPIãé«åºŠãªæ©èœã掻çšããããšã§ãããè€éãªããŒã¿æäœãåæãè¡ãããã®ã¯ãšãªãå¹ççã«çæã§ããããã§ãã
æåŸã«ãSQLã©ã€ãã©ãªã¯ãããã°ã©ã ã®å¯èªæ§ãšæŽåæ§ãä¿ã€å©ããšãªããŸããããã¯ãç¹ã«ããŒã ã§ãããžã§ã¯ããéå¶ããå Žåã«éèŠãªèŠçŽ ãšãªããŸãã
1.4 SQLã©ã€ãã©ãªã®åºæ¬çãªäœ¿ãæ¹
SQLã©ã€ãã©ãªã®åºæ¬çãªäœ¿ãæ¹ã¯ããŸãã©ã€ãã©ãªãã€ã³ããŒãããããŒã¿ããŒã¹ã«æ¥ç¶ããããšã§å§ãŸããŸãããã®æ¥ç¶ã¯ã©ã€ãã©ãªç¹æã®é¢æ°ã䜿ã£ãŠè¡ããŸããæ¥ç¶ãæåãããã次ã«è¡ãã®ã¯ã¯ãšãªã®å®è¡ã§ãããããã©ã€ãã©ãªã«å«ãŸããé¢æ°ã䜿ããŸãã
ã¯ãšãªã®å®è¡åŸãååŸããããŒã¿ãããã°ã©ã å ã§å©çšããããšãå€ãã§ããããã«ã¯ã©ã€ãã©ãªãæäŸããæ§ã ãªã¡ãœãããããããã£ãå©çšããŸãããŸããã¯ãšãªã®çµæãå å·¥ãããåæãããããããã«ãããã«æ§ã ãªé¢æ°ãå©çšããããšããããŸãã
æåŸã«ãããŒã¿ããŒã¹æ¥ç¶ã¯é©åã«ã¯ããŒãºããå¿ èŠããããŸããããããã©ã€ãã©ãªãæäŸããé¢æ°ã䜿çšããŸãããããã®æé ã¯äžè¬çãªãã®ã§ãããå ·äœçãªã³ãŒãã¯äœ¿çšããã©ã€ãã©ãªãããŒã¿ããŒã¹ã«ãã£ãŠç°ãªããŸãã
2. SQLã©ã€ãã©ãªã®éžå®åºæº
SQLã©ã€ãã©ãªã®éžå®ã¯ãæ¥åå¹çãäœæ¥ã®å質ã«å€§ãã圱é¿ããŸãããã®ãããéžå®åºæºãèšå®ããŠããã®åºæºã«ç §ãããŠæé©ãªã©ã€ãã©ãªãéžã¶ããšãæ±ããããŸãã以äžã§ã¯ãéèŠãªåºæºãšãªããããã©ãŒãã³ã¹ãããããã¥ã¡ã³ããŒã·ã§ã³ãšãµããŒããããã³ãã¥ããã£ã®æŽ»çºããããäžäœäºææ§ãã«ã€ããŠè©³èª¬ããŸãã
ãããã®åºæºãç解ããèªèº«ã®ç¶æ³ã«åãããæé©ãªSQLã©ã€ãã©ãªãèŠã€ããããã®åèã«ããŠã¿ãŠãã ããã
2.1 ããã©ãŒãã³ã¹
ããã©ãŒãã³ã¹ã¯ãã©ã®ã©ã€ãã©ãªãéžã¶ãã«çŽçµããéèŠãªåºæºã®äžã€ã§ããSQLã©ã€ãã©ãªã®ããã©ãŒãã³ã¹ãé«ããã°ããŒã¿ããŒã¹æäœãé«éã«ãªããã¢ããªã±ãŒã·ã§ã³ã®å šäœçãªæ§èœãåäžãããããšãå¯èœãšãªããŸãã
ãŸããããã©ãŒãã³ã¹ã¯ã³ã¹ãã«ãçŽçµããŸããããã©ãŒãã³ã¹ãé«ãã©ã€ãã©ãªã§ã¯ãããŒã¿ããŒã¹ã®ããŒããŠã§ã¢ãªãœãŒã¹ãå¹ççã«äœ¿ãããšãã§ããã³ã¹ããæããããšãå¯èœã§ãã
ããã©ãŒãã³ã¹ã®è©äŸ¡ã«ã¯ãã©ã€ãã©ãªãã©ããããé«éã«SQLã¯ãšãªãçæããçµæãè¿ããããªã©ãéèŠã§ããããã«ãããããŒã¿ããŒã¹ã®å¿çé床ãã¢ããªã±ãŒã·ã§ã³ã®ããã©ãŒãã³ã¹ã«åœ±é¿ãåãŒããŸãã
2.2 ããã¥ã¡ã³ããŒã·ã§ã³ãšãµããŒã
ã©ãã ãåªããSQLã©ã€ãã©ãªã§ãããã®äœ¿çšæ¹æ³ãæ©èœãç解ããããªããã°ãã®äŸ¡å€ãååã«åŒãåºãããšã¯é£ããã§ãããããã®ãããå å®ããããã¥ã¡ã³ããŒã·ã§ã³ãšæè³æ Œã®ãµããŒããæäŸãããŠããã©ã€ãã©ãªãéžã¶ããšãéèŠãªåºæºã®äžã€ã§ãã
ããã¥ã¡ã³ããŒã·ã§ã³ã¯ãã©ã€ãã©ãªã«çµã¿èŸŒãŸããæ©èœãæ¹æ³ãç解ããããã®ç¬¬äžæ©ã§ããè¯è³ªãªããã¥ã¡ã³ããŒã·ã§ã³ã¯è±å¯ãªäŸãæäŸããç解ãå©ããŠãããŸãã
ãŸãããµããŒãããŸãéèŠã§ããåé¡ãçºçãããšãããç¹å®ã®æ©èœã®äœ¿ãæ¹ãåãããªãæãè¿ éãã€é©åãªãµããŒãããããšå€§å€å©ãããŸãã
2.3 ã³ãã¥ããã£ã®æŽ»çºã
掻çºãªã³ãã¥ããã£ã®ååšã¯ãSQLã©ã€ãã©ãªãéžã¶éã®å€§ããªèŠçŽ ã§ããã³ãã¥ããã£ã掻çºã§ããã°ãæ°ããªãã£ãŒãã£ãŒã®è¿œå ããã°ä¿®æ£ãè¿ éã«è¡ãããã©ã€ãã©ãªãåžžã«ææ°ã®ç¶æ ãä¿ã€ããšãã§ããŸãã
ãŸããåé¡ãçºçããéã«ã¯ã³ãã¥ããã£ã«æçš¿ããä»ã®ãŠãŒã¶ãŒãéçºè ããã®å©ããåŸãããšãå¯èœã§ããããã«ãããããè¿ éã«åé¡ã解決ããããšãã§ããŸãã
ã¢ã¯ãã£ããªã³ãã¥ããã£ã¯ãã©ã€ãã©ãªãé·æéã«ããã£ãŠãµããŒããšæŽæ°ãç¶ããŠãã蚌ã§ããããŸãããã®ãããã³ãã¥ããã£ã®æŽ»çºãã¯ã©ã€ãã©ãªéžå®ã«ãããéèŠãªåºæºã®äžã€ã§ãã
2.4 SQLã©ã€ãã©ãªã®äžäœäºææ§
SQLã©ã€ãã©ãªéžå®ã«ãããŠã¯ããã®äžäœäºææ§ãèæ ®ããããšãéèŠã§ããäžäœäºææ§ãæã€SQLã©ã€ãã©ãªã¯ãæ°ããªããŒãžã§ã³ããªãªãŒã¹ãããŠããæ¢åã®ã³ãŒããä¿®æ£ããã«æžããšãã倧ããªå©ç¹ããããŸãã
äžäœäºææ§ãæ¬ ããŠããã©ã€ãã©ãªã䜿çšãããšãæ°ããªããŒãžã§ã³ãžã®æŽæ°æã«å€å€§ãªæéãšåŽåãå¿ èŠãšããå¯èœæ§ããããŸããããã¯ãéçºè ã®çç£æ§ãäžããåå ãšãªããŸãã
ãããã£ãŠãäžäœäºææ§ãèæ ®ã«å ¥ãããã®ç¹ã§ä¿¡é Œã§ããSQLã©ã€ãã©ãªãéžå®ããããšãæšå¥šãããŸãã
3. SQLã©ã€ãã©ãªã®å°å ¥ãšã»ããã¢ãã
ããããã¯ãSQLã©ã€ãã©ãªã®å°å ¥ãšã»ããã¢ããã«ã€ããŠè§£èª¬ããŸããåããŠSQLã©ã€ãã©ãªãå°å ¥ããæ¹ã§ãåããæã説æãå¿ãããŠããŸãã®ã§ãäžç·ã«åŠã³ãŸãããã
å ·äœçã«ã¯ãäžæºåãšå¿ èŠãªç°å¢ãSQLã©ã€ãã©ãªã®ããŠã³ããŒããšã€ã³ã¹ããŒã«ãåºæ¬çãªèšå®ããããŠéçºç°å¢ãžã®é©å¿ãšããæµãã§é²ããŠè¡ããŸãã
å°ããã€å®è£ ããããšã«ãã£ãŠãæçµçã«ã¯SQLã©ã€ãã©ãªã掻çšããŠããŒã¿æäœãã§ããããã«ãªããŸãã
3.1 äžæºåãšå¿ èŠãªç°å¢
æåã«äžæºåãšå¿ èŠãªç°å¢ã«ã€ããŠèª¬æããŸããSQLã©ã€ãã©ãªã䜿çšããããã«ã¯ããããããç¹å®ã®ãœãããŠã§ã¢ãããŒã«ãã€ã³ã¹ããŒã«ãããŠããå¿ èŠããããŸãã
å ·äœçã«ã¯ãããŒã¿ããŒã¹ç®¡çã·ã¹ãã ãé©åãªéçºç°å¢ããããŠSQLã©ã€ãã©ãªãåäœããããã®ã©ã³ã¿ã€ã ç°å¢ãªã©ãæããããŸãã
ãããã¯ãããããã®å ¬åŒãµã€ãã該åœãããããã€ããŒã®ãŠã§ãããŒãžã§æäŸãããŠããŸãã®ã§ãäºåã«ãã§ãã¯ããŸãããã
3.2 SQLã©ã€ãã©ãªã®ããŠã³ããŒããšã€ã³ã¹ããŒã«
次ã«SQLã©ã€ãã©ãªã®ããŠã³ããŒããšã€ã³ã¹ããŒã«ã«ã€ããŠè§£èª¬ããŸãããŸããããŠã³ããŒãã¯SQLã©ã€ãã©ãªã®å ¬åŒãµã€ããå ¬èªã®ããŠã³ããŒãããŒãžããè¡ããŸãã
ããŠã³ããŒãããã©ã€ãã©ãªã¯ãã€ã³ã¹ããŒã«ãã»ããã¢ããæã«æå®ãããã£ã¬ã¯ããªã«ä¿åããŸãã
ã€ã³ã¹ããŒã«ã¯ãããŠã³ããŒãããããã±ãŒãžãçšããŠãšã¯ã¹ãããŒã©äžã§çŽæ¥å®è¡ããããã³ãã³ãã©ã€ã³ããæäœãè¡ããŸãããªãã·ã§ã³ãèšå®ã¯ã€ã³ã¹ããŒã«ã¬ã€ããåç §ãã ããã
3.3 åºæ¬çãªèšå®
ã€ã³ã¹ããŒã«ãå®äºãããã次ã«åºæ¬çãªèšå®ãè¡ããŸããããã«ã¯ãã©ã€ãã©ãªã®ãã¹ã®èšå®ããããŒã¿ããŒã¹ãžã®æ¥ç¶èšå®ãªã©ãå«ãŸããŸãã
ãããã®èšå®ãè¡ãããšã§ãSQLã©ã€ãã©ãªãæ£åžžã«åäœããããŒã¿ããŒã¹ã«ã¢ã¯ã»ã¹ã§ããããã«ãªããŸãã
å ·äœçãªèšå®æ¹æ³ã¯ãSQLã©ã€ãã©ãªã®ããã¥ã¡ã³ããŒã·ã§ã³ãããã¥ã¢ã«ãåç §ããŠãã ããã
3.4 éçºç°å¢ãžã®é©å¿
æåŸã«éçºç°å¢ãžã®é©å¿ãè¡ããŸããå ·äœçã«ã¯ãSQLã©ã€ãã©ãªãéçºç°å¢å ã§äœ¿çšã§ãããããå¿ èŠãªã©ã€ãã©ãªãã¹ã®èšå®ããç°å¢å€æ°ã®èšå®ãè¡ããŸãã
ãããã®èšå®ãè¡ãããšã§ãSQLã©ã€ãã©ãªãéçºãããžã§ã¯ãå ã§åŒã³åºãã掻çšããããšãå¯èœã«ãªããŸãã
ãããã®èšå®æ¹æ³ããåéçºç°å¢ã®ããã¥ã¡ã³ããŒã·ã§ã³ããSQLã©ã€ãã©ãªã®å ¬åŒããã¥ã¡ã³ããŒã·ã§ã³ãåç §ããŠãã ããã
4. SQLã©ã€ãã©ãªã®å ·äœçãªäœ¿çšæ¹æ³ãšã³ãŒãäŸ
SQLã©ã€ãã©ãªã䜿ã£ãŠå¹ççã«ããŒã¿ããŒã¹æäœãè¡ãæ¹æ³ã解説ããŸããå ·äœçãªã³ãŒãäŸã玹ä»ããã®ã§ããã²å®éã®éçºã«åœ¹ç«ãŠãŠãã ããã
ããã§èª¬æããæäœã¯ããŒã¿ã®æ€çŽ¢ãšååŸãããŒã¿ã®æ¿å ¥ãšæŽæ°ãããŒã¿ã®åé€ããã©ã³ã¶ã¯ã·ã§ã³ã®ç®¡çã§ãã
ãããã®æäœã¯SQLã©ã€ãã©ãªã䜿ããšãããã©ãŒãã³ã¹ãæãªãããšãªãå¹ççã«è¡ããŸããå ·äœçãªäœ¿çšæ¹æ³ãèŠãŠãããŸãããã
4.1 ããŒã¿ã®æ€çŽ¢ãšååŸ
ããŒã¿ããŒã¹ããå¿ èŠãªããŒã¿ãæ€çŽ¢ãååŸããããã«ãSQLã©ã€ãã©ãªã¯SELECTæããµããŒãããŠããŸãã以äžã«å ·äœçãªã³ãŒãäŸã瀺ããŸãã
SELECT * FROM users WHERE age > 20;
ãã®ã³ãŒãã¯ãusersããŒãã«ãã幎霢ã20æ³ä»¥äžã®ãŠãŒã¶ãŒã®å
šãŠã®æ
å ±ãéžæããSQLæã§ãã
SQLã©ã€ãã©ãªã䜿çšãããšããã®ãããªSQLã¯ãšãªãããã°ã©ã ããçŽæ¥çºè¡ã§ããè¿ãããçµæãçŽæ¥åãåãããšãã§ããŸãã
4.2 ããŒã¿ã®æ¿å ¥ãšæŽæ°
æ°ããªããŒã¿ãæ¿å ¥ããããæ¢åã®ããŒã¿ãæŽæ°ãããããã«ã¯ãINSERTæãUPDATEæã䜿çšããŸããããããSQLã©ã€ãã©ãªã䜿ãã°ç°¡åã«è¡ããŸãã
INSERT INTO users (name, age) VALUES ('John', 25);
ãã®ã³ãŒãã¯ãusersããŒãã«ã«æ°ããªãŠãŒã¶ãŒâJohnâã®æ
å ±ãæ¿å
¥ããSQLæã§ãã
UPDATE users SET age = 30 WHERE name = 'John';
ãã®ã³ãŒãã¯ãusersããŒãã«ã®ãŠãŒã¶ãŒâJohnâã®å¹Žéœ¢ã30æ³ã«æŽæ°ããSQLæã§ãã
4.3 ããŒã¿ã®åé€
ããŒã¿ããŒã¹ããäžèŠãªããŒã¿ãåé€ããã«ã¯ãDELETEæã䜿çšããŸãã
DELETE FROM users WHERE name = 'John';
ãã®ã³ãŒãã¯ãusersããŒãã«ãããŠãŒã¶ãŒâJohnâã®æ
å ±ãåé€ããã³ãŒãã§ãã
SQLã©ã€ãã©ãªã䜿ããšãåäžã®ããŒã¿ã ãã§ãªããç¹å®ã®æ¡ä»¶ã«åãè€æ°ã®ããŒã¿ãäžåºŠã«åé€ããããšãå¯èœã§ãã
4.4 ãã©ã³ã¶ã¯ã·ã§ã³ã®ç®¡ç
äžé£ã®åŠçãäžã€ã®ãã©ã³ã¶ã¯ã·ã§ã³ãšããŠç®¡çããããã«ã¯ãBEGIN, COMMIT, ROLLBACKãªã©ã®SQLã³ãã³ãã䜿çšããŸãã
以äžã®ã³ãŒãäŸã¯ãusersããŒãã«ã«æ°ããªãŠãŒã¶ãŒãæ¿å ¥ãããã®åŸã§äœãã®ãšã©ãŒãçºçãããROLLBACKããŠå ã«æ»ãäŸã§ãã
BEGIN; INSERT INTO users (name, age) VALUES ('John', 25); ... COMMIT; CATCH ANY EXCEPTION ROLLBACK;
SQLã©ã€ãã©ãªãçšããããšã«ãããããŒã¿ããŒã¹æäœã«ããããã©ã³ã¶ã¯ã·ã§ã³ç®¡çããã¡ããšè¡ãããšãã§ããŸãã
5. ããããã®SQLã©ã€ãã©ãªãšãã®ç¹åŸŽ
ä»åã匷調ãããéšåã¯4ã€ã®äž»èŠãªSQLã©ã€ãã©ãªã§ãããããããã®ç¹åŸŽãšè©äŸ¡ã«ã€ããŠè©³ãã解説ããŸãããããã®æ å ±ãå©çšããã°ãããªãã®ãããžã§ã¯ãã«æé©ãªã©ã€ãã©ãªãèŠã€ããã®ã«åœ¹ç«ã€ã§ãããã
5.1 SQLAlchemyã®ç¹åŸŽãšè©äŸ¡
ãŸãåãã«çŽ¹ä»ããã®ã¯SQLAlchemyãããã¯Pythonã®ããã®äžè¬çãªSQLã©ã€ãã©ãªã§ãããããŒã¿ããŒã¹ãšããŠã®æœè±¡åå±€ãåããŠããŸããããã«ãããããŸããŸãªããŸããŸãªããŒã¿ããŒã¹ãšç°¡åã«ãããšãããããšãå¯èœã«ãªããŸãã
SQLAlchemyã¯ãé«åºŠãªSQLçæãæ åœããã³ã¢éšåãšãORMïŒãªããžã§ã¯ãé¢ä¿ãããã³ã°ïŒéšåã«åãããŠããŸããããã«ããããšã³ããŠãŒã¶ãŒã¯èªåã®ããŒãºã«æé©ã«å¿ããã¬ã€ã€ãŒãéžæããããšãå¯èœã§ãã
è©äŸ¡ãšããŠã¯ãSQLAlchemyã¯ãã®æè»æ§ãšå æ¬æ§ããéåžžã«é«ãè©äŸ¡ãåããŠãããç¹ã«äžã倧èŠæš¡ã®ãããžã§ã¯ãã§ãã䜿çšãããŸãã
5.2 Dapperã®ç¹åŸŽãšè©äŸ¡
次ã«çŽ¹ä»ããã©ã€ãã©ãªã¯Dapperã§ããããã¯.NETã«çµã¿èŸŒãŸããŠãã軜éã§ããã©ãŒãã³ã¹ã«åªããmicro-ORMãã¬ãŒã ã¯ãŒã¯ã§ãé床ãšå¹çæ§ãäž»ãªç¹åŸŽã§ãã
Dapperã¯çŽæ¥SQLã察象ãšãããšã³ãã£ãã£ãžã®ãããã³ã°æ©èœãæäŸããŠããŸãããŸãããã®äœ¿çšæ¹æ³ã¯éåžžã«ã·ã³ãã«ã§ãSQLã®ç ©éãªéšåãé èœããªãããã匷åãªSQLæ©èœãç°¡åã«å©çšã§ããŸãã
è©äŸ¡ãšããŠã¯ãäž»ã«é©çšç¯å²ã®åºããšããã©ãŒãã³ã¹ã®é«ããè©äŸ¡ãããŠãããäžã倧èŠæš¡ã®ãããžã§ã¯ãã§å¿ èŠãªã¹ããŒãã«å¯ŸããŠéåžžã«æçšã§ãã
5.3 PetaPocoã®ç¹åŸŽãšè©äŸ¡
ç¶ããŠçŽ¹ä»ããã©ã€ãã©ãªã¯PetaPocoã§ããããã¯.NETãšMonoã«å¯Ÿå¿ãã軜éã®micro-ORMã§ãã·ã³ãã«ããšäŸ¿å©ããéèŠããŠããŸãã
PetaPocoã®äž»ãªç¹åŸŽã¯ãããŒã¿ããŒã¹ã¢ã¯ã»ã¹ãç°¡çŽ åããéçºè ãå¿ èŠãšããã»ãšãã©ã®SQLã¿ã¹ã¯ãèªåçã«åŠçããŠãããããšã§ãããŸããèªåãããã³ã°ãPocoãšã®ã·ãŒã ã¬ã¹ãªçžäºéçšæ§ãéèŠãªç¹åŸŽãšãªã£ãŠããŸãã
è©äŸ¡ãšããŠã¯ããã®ã·ã³ãã«ããšæ©èœæ§ãããç¹ã«å°ãäžèŠæš¡ã®ãããžã§ã¯ãã§å€çšãããäžæ¹ã§ã倧èŠæš¡ãªãããžã§ã¯ãã«ãååã«å¯Ÿå¿ã§ããããèšèšãããŠããŸãã
5.4 Jooqã®ç¹åŸŽãšè©äŸ¡
æåŸã«çŽ¹ä»ããã©ã€ãã©ãªã¯Jooqã§ããããã¯Javaã®ORMãã¬ãŒã ã¯ãŒã¯ã§ãã¿ã€ãã»ãŒããªSQLãæäŸããŸãã
Jooqã¯ãSQLã®ã¯ãšãªãŒãJavaã®ã³ãŒããšããŠè¡šçŸããããšãã§ããå¯äžã®ãã¬ãŒã ã¯ãŒã¯ã®äžã€ã§ããããã«ãããJavaã®æ§æãšåçã«å®å šãªSQLã®å®çŸã¯é©ãã»ã©ã·ãŒã ã¬ã¹ã«è¡ããŸãã
è©äŸ¡ãšããŠã¯ãJooqã¯ç¹ã«Javaã®äžã倧èŠæš¡ãªæ¥åã¢ããªã±ãŒã·ã§ã³ã§éåžžã«é«ãè©äŸ¡ãåããŠããããã®ã¿ã€ãã»ãŒããªã¢ãããŒããé«ãè©äŸ¡ãããŠããŸãã
6. SQLã©ã€ãã©ãªã䜿ãéã®æ³šæç¹ãšãã©ãã«ã·ã¥ãŒãã£ã³ã°
SQLã®ã©ã€ãã©ãªã掻çšããéã念é ã«çœ®ãã¹ã泚æç¹ãšãã©ãã«ã·ã¥ãŒãã£ã³ã°ã®æ¹æ³ããå ·äœçã«è§£èª¬ããŸããç解ãšé©åãªããã©ãŒãã³ã¹ãåŸãããã«ã¯ããããã®ç¹ãææ¡ããŠããããšãéèŠã§ãã
6.1 SQLã€ã³ãžã§ã¯ã·ã§ã³æ»æã®é²åŸ¡
æåã«æ³šç®ãã¹ãã¯SQLã€ã³ãžã§ã¯ã·ã§ã³æ»æã§ãããŠãŒã¶ãŒããã®å ¥åãé©åã«ãµãã¿ã€ãºããããšã§ããããã®æ»æãé²ãããšãã§ããŸãã
SQLã©ã€ãã©ãªãæäŸãããã©ã¡ã¿ã©ã€ãºãã¯ãšãªã®æ©èœã䜿ãããšã§ããŠãŒã¶ãŒå ¥åããã®ãŸãŸã¯ãšãªã«çµã¿èŸŒãå±éºæ§ãäœæžã§ããŸããããã¯ä»»æã®æ»æã³ãŒãã®å®è¡ãé²ãéèŠãªæ段ã§ãã
ãŸããã»ãã¥ãªãã£ã«é¢ãããã¹ããã©ã¯ãã£ã¹ãéµå®ããããšãæ¬ ãããŸãããå ·äœçã«ã¯ããã¹ã¯ãŒãã®ããã·ã¥åãã¢ã¯ã»ã¹æš©éã®æå°åãããŒã¿ããã¯ã¢ããã®å®æå®æœãªã©ãæããããŸãã
6.2 ããã©ãŒãã³ã¹ã®æé©å
次ã«ããã©ãŒãã³ã¹ã®æé©åã§ããSQLã©ã€ãã©ãªãæäŸããAPIãå·§ã¿ã«äœ¿ãããšã§ãããŒã¿ããŒã¹ã®ããã©ãŒãã³ã¹ãåäžãããããšãå¯èœã§ãã
å ·äœçã«ã¯ã€ã³ããã¯ã¹ã®é©åãªéçšããã¯ãšãªã®æé©åãéèŠã§ããããããé©åã«è¡ãããšã§ã倧éã®ããŒã¿ãæ±ãéã®ããã©ãŒãã³ã¹ãæ Œæ®µã«ã¢ãããããããšãã§ããŸãã
ãŸããSQLã©ã€ãã©ãªã®å©çšã ãã§ãªããããŒã¿ããŒã¹èªäœã®èšå®ãç°å¢æ§ç¯ã«ã€ããŠãåŠã¶ããšã§ãæŽãªãããã©ãŒãã³ã¹ã®åäžãå³ãããšãå¯èœã§ãã
6.3 ãšã©ãŒãã³ããªã³ã°
ãšã©ãŒãã³ããªã³ã°ããŸããSQLã©ã€ãã©ãªã䜿ãéã«éèŠãªãã¡ã¯ã¿ãŒã§ããé©åãªãšã©ãŒãã³ããªã³ã°ã«ãããåé¡ãèµ·ããéã«æ©æã«å¯ŸåŠã§ããŸãã
å ·äœçã«ã¯ãtry-catchæã䜿çšããŠãå®è¡ããSQLã倱æããå Žåã®åŠçãèšè¿°ããŸããããã«ããããšã©ãŒãäŸå€ãçºçããéã«ãé©åã«åå¿ã§ããŸãã
ãŸãããšã©ãŒãã°ã®é©åãªåãæ±ããéèŠã§ãããšã©ãŒã¡ãã»ãŒãžãé©åã«ææ¡ãããã®åå ãæ©æã«ç¹å®ããããšã§ããã©ãã«ã·ã¥ãŒãã£ã³ã°ã®å¹çã倧ããåäžãããããšãå¯èœã§ãã
6.4 äžè¬çãªåé¡ã®è§£æ±ºç
SQLã©ã€ãã©ãªã®äœ¿çšã«ããã£ãŠãå€ãã®ãŠãŒã¶ãŒãçŽé¢ããäžè¬çãªåé¡ãããã€ãåãäžãããã®è§£æ±ºçãæäŸããŸãã
äžã€ã¯ããŒã¿åã®ãã¹ãããã§ããããšãã°ãæŽæ°åã®å€ãæååãšããŠæ±ã£ãããéã«æååãæŽæ°ãšããŠæ±ã£ãããããšããšã©ãŒãçºçããŸããSQLã©ã€ãã©ãªã§æäŸãããåå€ææ©èœãå©çšããŠãããŒã¿åã®ãã¹ããããé©åã«å¶åŸ¡ããŸãã
ããäžã€ã¯ãæ¥ç¶ã®å€±æãäžå®å®ãã§ããããã«å¯Ÿæããã«ã¯ãåæ¥ç¶ã®ããžãã¯ãèšãããé©åãªã¿ã€ã ã¢ãŠãèšå®ãè¡ãããªã©ã®å¯Ÿçãæå¹ã§ãããããã®åé¡ã«å¯Ÿãã察çãåžžã«å¿æããããšã§ãSQLã©ã€ãã©ãªã®å©çšã«ãããå¹çãšå®å®æ§ãé«ããããšãã§ããŸãã