Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SpringBootとMyBatisでデータベースを可視化する

su-kun1899
November 18, 2017

 SpringBootとMyBatisでデータベースを可視化する

JJUG CCC 2017 Fall で発表した資料です。

su-kun1899

November 18, 2017
Tweet

More Decks by su-kun1899

Other Decks in Programming

Transcript

  1. 6

  2. 8

  3. ࠓ೔࿩͢͜ͱ • લ൒ • ϨΨγʔγεςϜͱσʔλߏ଄ͷՄࢹԽ • shishamoͱ͍͏WebΞϓϦͷ঺հ • ޙ൒ •

    shishamoΛࢧ͑Δٕज़ • Spring Boot • MyBatis • Spock ※͋·Γٕज़Λਂ۷Γͨ͠࿩͸͠·ͤΜɻԿ͔Λ࢝ΊΔ͖͔͚ͬ΍ώϯτͱ͠ ͯॿ͚ʹͳΕ͹ɻ 15
  4. Information Schema • table, view, column, procedureͷ৘ใΛಡΈऔΓઐ ༻Viewͱͯ͠ఏڙ • ඪ४SQLͰఆٛ͞Ε͍ͯΔ

    SELECT column_name, data_type, column_default, is_nullable FROM information_schema.columns WHERE table_name='alpha'; column_name | data_type | column_default | is_nullable -------------+-----------+----------------+------------- foo | integer | | YES bar | character | | YES (2 rows) https://en.wikipedia.org/wiki/Information_schema 30
  5. SchemaSpyͷ໰୊఺ • HTMLੜ੒ʹΑΔλΠϜϥά • σϓϩΠ৔ॴ • S3ʹϗεςΟϯάͯͨ͠ • ฒྻίετ͕ߴΊ •

    ฒߦ։ൃͬͯී௨ʹ͋ΔΑͶɻɻ • ࠷৽͚ͩͰͳ͘։ൃதͷ΋ͷ΋ݟ͍ͨ • ଞͷERٯੜ੒πʔϧ΋େ֓ಉ༷ͷ໰୊ • MySQL Workbenchͱ͔ʢ͍͢͝ΜͰ͚͢ͲͶʣ 33
  6. ͙͢࢖͑Δ 1. git clone 2. Ҿ਺ʹ઀ଓઌDBΛࢦఆͯ͠boot:run 3. ϒϥ΢βΞΫηε $ git

    clone [email protected]:su-kun1899/shishamo.git $ cd shishamo/ $ ./mvnw spring-boot:run \ -Dspring.datasource.url=<Your database url> \ -Dspring.datasource.schema=<Your mysql schema> \ -Dspring.datasource.username=<Your mysql user> \ -Dspring.datasource.password=<Your mysql password> 36
  7. 38

  8. Spring Boot Spring Boot makes it easy to create stand-alone,

    production-grade Spring based Applications that you can “just run”. 44
  9. model public class User { private int id; private String

    name; private String sex; // ΞΫηα͸লུ } 51
  10. Result Map <resultMap id="userResultMap" type="User"> <id property="id" column="user_id" /> <result

    property="name" column="user_name"/> <result property="sex" column="user_sex"/> </resultMap> 53
  11. Result Map • autoMappingଐੑΛ͚ͭͯ΍Ε͹Α͍ • άϩʔόϧʹઃఆ͢Δ͜ͱ΋Մೳ <resultMap id="userResultMap" type="User" autoMapping="true">

    <!-- <id property="id" column="user_id" /> --> <!-- <result property="name" column="user_name"/> --> <!-- <result property="sex" column="user_sex"/> --> </resultMap> 57
  12. Address address_id address_city 11 tokyo 12 osaka User user_id user_name

    user_sex address_id Tokyo Kantō Tokyo 11 Kanagawa Kantō Yokohama 12 61
  13. ͜Μͳײ͡ͰऔΔ user id user name user sex address id address

    city 1 yamada male 11 tokyo 2 sato female 12 osaka 62
  14. model public class User { private Address address; private int

    id; private String name; private String sex; // ΞΫηα͸লུ } public class Address { private int id; private String city; // ΞΫηα͸লུ } 63
  15. Result Map <!-- User --> <resultMap id="userResultMap" type="User" autoMapping="true" columnPrefix="user_">

    <id column="id"/> <association property="address" resultMap="addressResultMap" columnPrefix="address_"/> </resultMap> <!-- Address --> <resultMap id="addressResultMap" type="Address" autoMapping="true"> <id property="id" column="id"/> </resultMap> 65
  16. Statement <select id="selectUsers" resultMap="userResultMap"> select some_table.user_id as user_id, some_table.user_name as

    user_name, some_table.sex as user_sex, address.address_id as address_id, address.address_city as address_city from some_table inner join address on some_table.address_id = address.address_id where some_table.user_id = #{id} </select> 67
  17. User user_id user_name user_sex address_id 1 yamada male 11 2

    sato female 12 Hobby hobby_id hobby_name 21 football 22 running 23 video game 24 programming 69
  18. ͜Μͳײ͡ͰऔΔ user_id user_name user_sex hobby_id hobby_nam e 1 yamada male

    21 football 1 yamada male 23 video game 1 yamada male 24 programmin g 2 sato female 22 runnning 2 sato female 24 programmin g 71
  19. model public class User { private Address address; private int

    id; private String name; private String sex; private List<Hobby> hobbies; // ΞΫηα͸লུ } public class Hobby { private int id; private String name; // ΞΫηα͸লུ } 72
  20. Result Map <resultMap id="userResultMap" type="User" autoMapping="true" columnPrefix="user_"> <id column="id"/> <collection

    property="hobbies" ofType="Hobby" resultMap="hobbyResultMap" columnPrefix="hobby_"/> </resultMap> <resultMap id="hobbyResultMap" type="Hobby" autoMapping="true"> <id property="id" column="id"/> </resultMap> 74
  21. Statement <select id="selectUsers" resultMap="userResultMap"> select some_table.user_id as user_id, some_table.user_name as

    user_name, some_table.sex as user_sex, hobby.hobby_id as hobby_id, hobby.hobby_name as hobby_name from some_table inner join user_hobby on some_table.user_id = user_hobby.user_id inner join hobby on user_hobby.hobby_id = hobby.hobby_id where some_table.id = #{id} </select> 75
  22. ςετͷҙਤ͕໌ന def "HashMap accepts null key"() { given: def map

    = new HashMap() when: map.put(null, "elem") then: notThrown(NullPointerException) } 87
  23. ڧྗͳMockػߏ given: def subscriber = Mock(Subscriber) subscriber.receive("message1") >> "ok" and:

    def publisher = new Publisher(); when: publisher.send("message1") then: 1 * subscriber.receive("message1") 88
  24. ͜Μͳ͜ͱ΋ // Ҿ਺͕helloͷͱ͖͚ͩ 1 * subscriber.receive("hello") // Ҿ਺͕helloͰ͸ͳ͍ͱ͖͚ͩ 1 *

    subscriber.receive(!"hello") // Ҿ਺ͻͱͭͳΒͳΜͰ΋ 1 * subscriber.receive(_) // Ҿ਺͍ͭ͘Ͱ΋ͳΜͰ΋ 1 * subscriber.receive(*_) // StringͷҾ਺ͻͱͭͳΒͳΜͰ΋ 1 * subscriber.receive(_ as String) // predicate΋࢖͑ΔʢҾ਺ͷsize͕ 3 ΑΓେ͖͍ʣ 1 * subscriber.receive({ it.size() > 3 }) 89
  25. Data Tables def "maximum of two numbers"() { expect: Math.max(a,

    b) == c where: a | b || c 1 | 3 || 3 7 | 4 || 7 0 | 0 || 0 } 90
  26. JUnitͰͷύϥϝʔλϥΠζυςετ @ParameterizedTest @ValueSource(strings = { "Hello", "World" }) void testWithStringParameter(String

    argument) { assertNotNull(argument); } • JUnit5Ͱ͍ͩͿָʹͳͬͨ໛༷ • JUnit4ͩͱTheoriesΛ࢖ͬͯ΋ͬͱେม 98
  27. SpockͷData Tables def "with string parameter"() { expect: argument !=

    null where: argument | _ "Hello" | _ "World" | _ } 99
  28. ςετର৅Ϋϥε @Service public class UserService { private UserRepository userRepository; //

    ίϯετϥΫλ public UserService(UserRepository userRepository) { this.userRepository = userRepository; } // ςετର৅ϝιου @Transactional(readOnly = true) public List<User> findBy(Sex sex) { return userRepository.selectBySex(sex); } } 103
  29. ςετΫϥε class UserServiceSpec extends Specification { UserService userService UserRepository userRepository;

    def 'Get users by sex'() { given: 'Mocking repository' def userRepository = Mock(UserRepository) userRepository.selectBySex(Sex.MALE) >> {[ new User(name: 'Taro', sex: Sex.MALE), new User(name: 'Jiro', sex: Sex.MALE), ]} and: def userService = new UserRepository(userRepository) when: def actual = userService.get(Sex.MALE) then: actual.size() == 2 actual.collect { it.name } == ['Taro', 'Jiro'] } } 104
  30. given given: 'Mocking repository' def userRepository = Mock(UserRepository) userRepository.selectBySex(Sex.MALE) >>

    {[ new User(name: 'Taro', sex: Sex.MALE), new User(name: 'Jiro', sex: Sex.MALE), ]} and: 'ίϯετϥΫλΠϯδΣΫγϣϯ࠷ߴ' def userService = new UserRepository(userRepository) 105
  31. when-then when: def actual = userService.get(Sex.MALE) then: actual.size() == 2

    actual.collect { it.name } == ['Taro', 'Jiro'] 106
  32. ࠓ೔ݴ͍͔ͨͬͨ͜ͱ • SpringBoot + MyBatis + Spock ͱ͍͏։ൃύοΫ • SpringBoot͸؆୯Ͱߴػೳ

    • MyBatisͷϚοϐϯά͸ڧྗ • Spock࠷ߴͳͷͰࢼͯ͠Լ͍͞ • طଘࢿ࢈Λվળ͢Δʹ͸ՄࢹԽ͕େࣄ • ʮݱঢ়ʯΛਖ਼֬ʹද͢ • υΩϡϝϯτʹ΋ίʔυʹ΋པΓ͗͢ͳ͍ • DBʹݶͬͨ࿩Ͱ͸ͳ͍ 128
  33. ͓͠·͍ Spring Boot makes it easy to create stand-alone, production-

    grade Spring based Applications that you can “just run”. 130