[Java 기초문법] 자바와 데이터베이스 연결하기, JDBC code, H2, Squirrel SQL

[Java 기초문법] by Professional Java Developer Career Starter: Java Foundations @ Udemy

 

---

 

새로운 Gradle 프로젝트를 만든다. 

 

 

H2 Databata와의 연결성을 위해 Gradle Builder에서 dependency를 추가해주어야 한다. 

 

 

Add dependency > 

 

H2 Database Engine을 추가 

 

 

 

TDD 프로젝트를 위해 AssertJ도 추가해준다. 

 

 

 

---

 

 

테스트 클래스에서 canSave 역할을 할 메소드를 만들어주고 

 

PeopleRepository 인스턴스를 만든다.

 

 

main에도 추가를 해주면서 에러를 해결해준다. 

 

 

 

 

 

Person 타입을 생성해서 실제 사람을 instantiate 하도록 하고 

repo에 save하는 기능까지 만든다,

 

public class PeopleRepositoryTests {

    @Test
    public void canSave(){
        PeopleRepository repo = new PeopleRepository();
        Person john = new Person("John", "Smith", ZonedDateTime.of(1980,11,15,15,15,0,0, ZoneId.of("-6")));
        repo.save(john);
    }
}

 

 

여기서 assert 해야할 것은 무엇일까? 

 

Let's assert that upon calling the save method, we would receive an object back out and that object will now have an ID and that ID would be generated by the database. 

 

 

Person savedPerson = repo.save(john);
assertThat(savedPerson)

 

say that we get back a saved person and that saved person is going to have an actual ID, that is set to something. 

 

so that saved peroson getId() => aseert that that the saved persons' Id => is greater than zero.

 

 

 

 

 

getId 메소드에서 하드코딩으로 0 이상의 것을 리턴하게 함으로써 테스트를 통과하게 해준다. 

 

 

 

 

 

 

Now in the next test, save two people.

 

 

    @Test
    public void canSaveTwoPeople() {
        PeopleRepository repo = new PeopleRepository();
        Person john = new Person("John", "Smith", ZonedDateTime.of(1980, 11, 15, 15, 15, 0, 0, ZoneId.of("-6")));
        Person bobby = new Person("Bobby", "Smith", ZonedDateTime.of(1982, 11, 13, 10, 15, 0, 0, ZoneId.of("-6")));
        Person savedPerson1 = repo.save(john);
        Person savedPerson2 = repo.save(bobby);
        assertThat(savedPerson1.getId()).isNotEqualTo(savedPerson2.getId());
    }
}

 

 

Here both of the people's id is hardcoded as 1, which returns failure. 

 

So now, one of the very first things we're going to need if we're going be talking to the database via our Java code is connection to H2 db. And there is this driver that utilize the connection, to use driver manage, is to reference drive manager, which is 

 

 

and url is as such, 

 

 

here you don't have to know the path explicitly because Java has a funtion that tells the current directory via System method, which direct the path to user.home. 

 

DriverManager.getConnection("jdbc:h2:~/peopletest".replace("~",System.getProperty("user.home")));

 

now let's make connection with repo, and that causes constructor error which can be solved as 

 

 

 

 

 

and the connection in test to be set up fields 

 

 

---

 

So main thing we want to do here is to be able to save this person and that we get there automatically generated ID after we've saved them because the db is going to generate ID. And then take the Id stuff it back into this person 

 

What should be done now is in save class, is connection function that commands actually connected with SQL is as follows. 

 

 

package com.neutrio.peopledb.repository;

import java.sql.*;
import java.time.ZoneId;

public class PeopleRepository {
    private Connection connection;
    public PeopleRepository(Connection connection) {
        this.connection = connection;
    }

    public Person save(Person person) {
        String sql = "INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, DOB) VALUES(?, ?, ?)";

        try {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, person.getFirstName());
            ps.setString(1, person.getLastName());
            ps.setTimestamp(3, Timestamp.valueOf(person.getDob().withZoneSameInstant(ZoneId.of("+0")).toLocalDateTime()));
            int recordsAffected = ps.executeUpdate();
            System.out.println(recordsAffected);
            ResultSet rs = ps.getGeneratedKeys();

            while (rs.next()){
                long id = rs.getLong(1);
                person.setId(id);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return person;
    }
}

 

 

and test code is  

 

package com.neutrio.peopledb.repository;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.time.ZoneId;
import java.time.ZonedDateTime;

import static org.assertj.core.api.Assertions.assertThat;

public class PeopleRepositoryTests {

    private Connection connection;



    @BeforeEach
    void setUp() throws SQLException {
        connection = DriverManager.getConnection("jdbc:h2:~/peopletest".replace("~", System.getProperty("user.home")));
    }

//    @AfterEach
//    void tearDown() throws SQLException {
//        if (connection !=null){
//            connection.close();
//        }
//    }

    @Test
    public void canSaveOnePerson() throws SQLException {
        PeopleRepository repo = new PeopleRepository(connection);
        Person john = new Person("John", "Smith", ZonedDateTime.of(1980,11,15,15,15,0,0, ZoneId.of("-6")));
        Person savedPerson = repo.save(john);
        assertThat(savedPerson.getId()).isGreaterThan(0);
    }

    @Test
    public void canSaveTwoPeople() {
        PeopleRepository repo = new PeopleRepository(connection);
        Person john = new Person("John", "Smith", ZonedDateTime.of(1980, 11, 15, 15, 15, 0, 0, ZoneId.of("-6")));
        Person bobby = new Person("Bobby", "Smith", ZonedDateTime.of(1982, 11, 13, 10, 15, 0, 0, ZoneId.of("-6")));
        Person savedPerson1 = repo.save(john);
        Person savedPerson2 = repo.save(bobby);
        assertThat(savedPerson1.getId()).isNotEqualTo(savedPerson2.getId());
    }
}

 

turns out to be : 

 

 

데이터베이스 연동