데이터베이스 환경구축 (Maria DB 초기설정, JDBC사용,JPA설정)

개발기술/데이터베이스

데이터베이스 환경구축 (Maria DB 초기설정, JDBC사용,JPA설정)

bsh6226 2024. 5. 2. 18:19

데이터베이스

왜 사용하는가?

데이터는 엑셀이나 파일시스템으로도 관리할 수 있지만, 1. 데이터의 동시성(웹의 특성상 복수의 사용자가 데이터를 저장하는 일이 발생)으로 인한 보안문제나 데이터 안정성의 문제도 있을 것임. 2. 데이터의 종속성(응용프로그램에 따라서 데이터가 변해야함), 3. 데이터의 중복성(동일한 데이터가 다른 위치의 여러파일에 존재하여 공간낭비가능)의 단점으로 데이터베이스의 도입이 필요하다.
데이터베이스는 파일시스템과 같이 물리적 위치에 따라 참조하는 것이 아니라 데이터 값을 사용하여 조건을 제시하면 이에 해당하는 데이터를 찾을 수있다

데이터베이스관리시스템

DBMS : 데이터베이스를 운영하고 관리하는 소프트웨어
정형화된 데이터를 관리하는 RDBMS, 비정형 데이터를 관리하는 NoSQL이 존재한다.오라클은 기업, 금융권 등 대형시스템에서 오라클 주로 사용하는 상용소프트웨어. 그외 공개소프트웨어 mysql, mariadb, postgresql 등이 있다.

DBMS가 최적화된 연산 : Query Optimization
- Databases have built-in query optimizers that analyze SQL queries and determine the most efficient way to execute them. These optimizers consider the available indexes, the structure of the data, the current workload, statistics about the data distribution, and more, to generate an optimal execution plan.

DBMS의 계층구조

1. Server : At the highest level The server manages all the databases it contains and handles access control, processing SQL commands, transactions, and other database operations. a single computer can host multiple servers with mutliple port and other method.
2. Databases Within the MariaDB server, you can create multiple databases. Each database acts as a separate container for organizing and managing data in a structured way.
3.Tables : Each database contains one or more tables. A table is where data is actually stored, and it consists of rows and columns. Each table is defined by a schema that specifies its columns, each column’s data type, and other constraints like primary keys, indexes, etc.
4. Users and PrivilegesUsers are not contained within a database but are managed at the server level. Users can be granted various levels of access (privileges) to one or more databases or even specific tables within those databases. This allows for detailed control over who can view or manipulate data.
5. Hosts : When you define a user, you also specify the host from which they can connect. This can be localhost (indicating connections made from the same machine the server is running on), a specific IP address, a range of IP addresses, or % for any host.

DB 환경구축 공통프로세스

1. 컴퓨터 내에 MySQL이나 MariaDB가 설치되어있는 지 확인하고 설치되어 있지 않다면 설치한다 (Homebrew 사용가능)

2. CLI를 통해서 Root계정과 사용자 계정을 설정하고 순서대로 Host, Port, User, Password를 설정한다.

3. 그 후 CLI 혹은 GUI(WorkBench, DBeaver)을 통해서 데이터베이스 - 테이블(스키마)를 셋업한다.

4. Spring에 JDBC Dependency와 Application Property를 통해서 ID, PW, Port, DB명을 설정하여 연결을 생성해준다.

5. 환경과 무관하게 배포하기 위해서 Docker을 사용하여 자동적 Build를 진행한다.

Maria DB CLI 환경구축

맥은 mariadb 홈페이지에서 다운로드 하는 방식이 아니라 homebrew라는 패키지매니저를 통해서 brew에 등록되어있는 서비스를 설치/업데이트 및 실행한다.

MariaDB CLI를 통한 사용법

MariaDB를 CLI에 입력하면 MariaDB가 실행된다. exit을 선택하면 프로그램이 종료됨.
MariaDB에 접속하고나서는 sql문을 통해서 모든 정보를 확인해야한다. 계정의 권한이나
계정과 데이터베이스 정보확인
- 이미 생성된 데이터베이스 인스턴스 확인 : show databases
- 특정 데이터베이스를 선택한다 : use databasename; use mysql를 통해서 계정정보 조회가 가능하다.
- 존재하는 계정확인
  - 1. user table을 활용하여 계정별 권한 확인 ; select Host, User, Password from user; : mysql database를 선택한 상태에서, sql구문을 활용하여 user의 모든 정보를 확인
  - 2. CLI 명령으로 계정별 권한 확인 ; SHOW GRANTS FOR 'username'@'hostname'; / show grants for username;
- show tables; 선택한 데이터베이스의 데이블 목록확인
- desc table(user); 선택한 테이블의 칼럼 값들을 보여준다.
데이터베이스 인스턴스 생성
- create database '데이터베이스 인스턴스명';
- drop database '데이터베이스 인스턴스명' ;
데이터베이스 계정 생성 및 권한설정
- create user ['username'] @ ['accessclient` or '%' for acess all or 'localhost'] identified by ['password'] /
- client에 접속하기 위해서는 host에 '%'로 표기될 수 있도록 해야한다. 현재 user가 localhost권한으로 생성되어있다면 % 권한으로 user을 한번더 생성하면 권한이 확장된다.
- grant all privileges on [databasename.*] to ['username']@['accessclient'] identified by ['password'];
- flush privileges; : 현재의 변경사항들이 저장되도록 함.
Maria DB로그인하기 : mysql -u adminname -p -h localhost testdb
- -u [username]: Specifies the username to use when connecting to the server.
- -p: Prompts for the password of the specified user.
- -h [hostname]: Connects to a host other than localhost. 외부의 컴퓨터로 서버의 데이터베이스로 접근시 ip를 기재
- -P [port]: Specifies a port number to connect to if the server is not on the default port (3306 for MySQL and MariaDB).
- [database_name]: Directly connects to a specific database.
  - Launching the Client
    - Entering mysql in the terminal and hitting Enter will attempt to start the MariaDB client. If you just type mysql without any additional parameters, it tries to connect to the database server using the default settings (i.e., username 'root', no password, connecting to localhost, default port).
  - Launching as root in local
    - sudo mariadb -u root
    - Unix Socket Authentication need to be passed in order to login as root : When a client application requests a database connection using Unix socket authentication, the server checks the user identity of the process making the connection request (i.e., which Unix system user is running the process).

그외 MariaDB Command
- select version(); : 현재 데이터베이스 버전확인
- information_schema, mysql, performance_schema, sys와 같은 데이터베이스는 시스템 데이터베이스이며, 계정을 root권한으로 접속하였기에 시스템파일을 볼수 있었던 것이다. 기본적으로 데이터베이스는 그에 맞는 서비스 유저명을 생성, 권한부여하여 필요한 데이터베이스만 접근할 수 있도록 하는게 일반적이다.
- 그러므로 서비스 빌드를 시작하려면 데이터베이스를 만들고 그에 접근할 수 있는 유저를 생성하고 권한을 부여해야한다. 루트로 접근해서 조작하는 것은 good practice가 아님.
데이터베이스 툴 연결(DBeaver)
- DBeaver을 인스톨하고 계정을 선택하여 connection을 만들어야 db를 조작할 수 있음.
- create Connection - 사용하는 DB선택 - host ip, port(DB의 default는 3306), dbname, username, password를 입력하여 연결한다.
- 코드실행 : control + enter;
- 새로운편집기 : control + ];
- 새로고침 : fn +F5
- use databasename : 선택된 데이터베이스를 바꾼다

Pure 자바를 통한 데이터베이스 연결

각 DBMS는 DB서버와 통신할 수 있는 API를 제공한다. 프로그래밍 언어들은 이 API를 이용해서 데이터베이스를 사용한다. Java에서 사용한느 API가 JDBC 드라이버임. 드라이버는 보통 장치를 조작하기위해 실행시키는 코드의 집합을 일컫는데, DB도 마찬가지로 접근하기 위한 드라이버를 설치해야한다.

JDBC: Designed for database connectivity, providing a standard API for Java applications to interact with databases. JDBC Operates at the application layer but relies on underlying database-specific protocols (which operate over TCP/IP) to communicate with the database server.

mariadb사이트에서 MariaDB Connector/J를 다운받아서 프로젝트 폴더에 넣어둔다. 그리고 인텔리제의 상단 탭의 프로젝트 구조에 들어가서 라이브러리에 해당 파일을 등록하여 classpath를 설정한다.

tring url = "jdbc:mariadb://localhost:3306/test1";
String user = "testuser1";
String DBpassword = "!";

try {
    Class.forName("org.mariadb.jdbc.Driver");
} catch (ClassNotFoundException e) {
    throw new RuntimeException(e);
}

3. SQL을 위한 Statement 객체 생성 ;PreparedStatement preparedStatement = connection.prepareStatement(sql)
4. SQL 문장 실행 ; int affected = preparedStatement.executeUpdate();
- connection 개체는sql문을 input으로하여 preparedstatement를 생성한다. prearedstatement는 SQL injection attacks을 막기 위해서 쿼리문과 데이터를 분리하고 대입하는 방식으로 값을 처리한다. SQL문에 대입할 parameter 자리는 ?로 표기한다.
- SQL문을 실행할때 parameter을 단순하게 String으로 처리하는 경우, 해커들이 쿼리조건문 뒤에 'or 1==1' 같은 조건문을 붙여서 내부 데이터를 모두 쿼리할 수 있게할 수 있기때문에 보안 조치가 필요하다. 때문에 statement객체를 생성해서 string을 통체로 input하는 것이 아니라 preparedstatement로 조건절과 쿼리문을 별개로 input하여 보안성을 강화한다.
- 이부분은 Spring에서 해결해주지만 참고할것

Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
    connection = DriverManager.getConnection(url, user, DBpassword);


    String sql = "select name, email, password\n" +
            "from zerobase_member1 zm \n" +
            "where marketing_yn = ? and  name = ?" +
            ";";

     preparedStatement  = connection.prepareStatement(sql); // sql String을 url에서 조작하는 것을 방지하기 위해서 조건절값 코드 내 숨기기
     preparedStatement.setString(1,marketingValue); // 1. sql문의 ?에 해당 값이 입력됨
     preparedStatement.setString(2,nameValue); // 2. sql문의 ?에 해당 값이 입력됨

5. SQL 실행 결과 처리 ;
- update문의 경우에는 sql실행결과로 영향받은 record의 수가 int로 return 되기 때문에 System.out.printf("affected: %d", affected); 실행이 잘되었는지 int를 출력하여 확인할 수 있다;
- select문은 sql실행결과로 resultset을 반환하는데 It acts as a cursor pointing to a single row of the result set initially positioned before the first row. As you iterate through ResultSet, you can move the cursor forward to access data row by row.
- resultset : ResultSet is a Java class that represents the result set of a database query. hen you execute a query using a Statement or PreparedStatement, you typically get a ResultSet object that contains the data
  - The ResultSet 은 최초에는 첫번째 레코드보다 더 앞에 위치하고 있다. 그러므로 첫번째 레코드로 위치시키려면 next(), previous(), first(), last() 같은 함수를 사용해서 커서를 움직여야한다.
    - null값도 resultset이 값이 존재하는 것으로 인지하므로 유의할 것,
  - You can retrieve data from the current row using various getter methods like getInt(), getString(), getDouble()

// Print column names
for (int i = 1; i <= columnCount; i++) {
    System.out.print(resultSet.getMetaData().getColumnName(i) + "\t");
}
System.out.println();

// Process the result set
while (resultSet.next()) {
    for (int i = 1; i <= columnCount; i++) {
        System.out.print(resultSet.getString(i) + "\t");
    }
    System.out.println();

reultset의 모든 결과를 확인할 수 있는 수식

while (resultSet.next()) {
    String name = resultSet.getString("name");
    String email = resultSet.getString("email");
    String password = resultSet.getString("password");

    System.out.println(name + " " + email + " " + password);
}

6. JDBC 객체들 연결 해제; preparedStatement.close(); connection.close(); 콘솔에서 exit하는 것.
- 서버에서 연결할 수 있는 연결수는 한정되어있기때문에 사용하고나서는 반드시 연결해체를 해줘야한다. 그렇지 않다면 서버가 다운되는 경우도 발생함.
위의 케이스는 select문을 java에서 실행한 것이지만, 다른 쿼리문 실행도 크게 다르지 않다. 다만 다른점은 sql 실행메소드와 실행결과 확인이 다르다
- Insert문 : sql실행메소드 - int affected = preparedStatement.executeUpdate(); 실행결과 확인- System.out.printf("affected: %d", affected); 영향을 받은 라인의 개수로 판단한다.

Spring JPA를 통한 데이터베이스 연결

Dependency 추가

implementation 'mysql:mysql-connector-java:8.0.33'

Configuration 수정

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/zerobase_heritage;
    username: testuser
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver

implementation 'mysql:mysql-connector-java:8.0.33'

테스트 데이터베이스

h2데이터베이스 사용

Automatic Schema Creation: H2 is typically used in in-memory mode or file-based mode. In these modes, the schema and data are either stored in memory (and discarded when the application stops) or saved to a file. When used with frameworks like Spring Boot, H2 can automatically create and drop schemas on startup and shutdown.

일반데이터베이스 사용

MariaDB is a server-based, persistent relational database designed for production use. Its goal is to provide a robust, scalable, and persistent database solution that stores data across sessions and requires manual management of database schemas.

Manual Schema Setup: Since MariaDB is a production-grade database, it assumes that the database schema will be designed and managed by database administrators or developers. Unlike H2, which is optimized for quick testing setups, MariaDB requires you to explicitly create your database and schema before using it.