TIL 2023.02.22 Java Web 11 (JdbcTemplate, MongoDB replica set)

JdbcTemplate

---------------- RowMapper.java
package com.bit.framework.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;

public interface RowMapper<T> {
	T rows(ResultSet rs) throws SQLException;
}

----------------- JdbcTemplate.java
package com.bit.framework.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.bit.model.DeptDto;

public class JdbcTemplate<T> {
	DataSource dataSource;
	
	public JdbcTemplate() {
		
	}

	public JdbcTemplate(DataSource dataSource) {
		super();
		this.dataSource = dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public int update(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				pstmt.setObject(i+1, args[i]);
			}
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	public T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) {
		return queryForList(sql, rowMapper, args).get(0);
	}
	
	public List<T> queryForList(String sql, RowMapper<T> rowMapper){
		return queryForList(sql, rowMapper, new Object[] {});
	}
	public List<T> queryForList(String sql, RowMapper<T> rowMapper, Object... args){
		List<T> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sql);
			for(int i = 0 ; i < args.length; i++) {
				pstmt.setObject(i+1, args[i]);
			}
			rs = pstmt.executeQuery();
			while(rs.next()) {
				list.add(rowMapper.rows(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
	}

	
}
-------------- DeptDao.java
package com.bit.model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.h2.jdbcx.JdbcDataSource;

import com.bit.framework.jdbc.JdbcTemplate;
import com.bit.framework.jdbc.RowMapper;

public class DeptDao {
	DataSource dataSource;
	
	
	public DeptDao() {
		JdbcDataSource jds = new JdbcDataSource();
		jds.setUrl("jdbc:h2:tcp://localhost/~/test");
		jds.setUser("sa");
		dataSource = jds;
	}
	
	public List<DeptDto> selectAll(){
		String sql = "select * from dept";
		JdbcTemplate<DeptDto> template = new JdbcTemplate<DeptDto>(dataSource);
		RowMapper<DeptDto> rowMapper= new RowMapper<DeptDto>() {
			@Override
			public DeptDto rows(ResultSet rs) throws SQLException {
				return new DeptDto(rs.getInt("deptno"),
						rs.getNString("dname"),
						rs.getNString("ename"));
			}
		};
		return template.queryForList(sql, rowMapper);
	}
	
	public Object selectOne(int deptno) {
		String sql = "select * from dept where deptno=?";
		JdbcTemplate<DeptDto> template = new JdbcTemplate<DeptDto>(dataSource);
		return template.queryForObject(sql, new RowMapper<DeptDto>() {
			@Override
			public DeptDto rows(ResultSet rs) throws SQLException {
				return new DeptDto(rs.getInt("deptno"),
						rs.getNString("dname"),
						rs.getNString("ename"));
			}
		},deptno);
	}

	public int insertOne(int deptno, String dname, String ename) {
		String sql = "insert into dept (deptno, dname, nalja, ename) values(?,?,now(),?)";
		JdbcTemplate<DeptDto> template = new JdbcTemplate<DeptDto>(dataSource);
		return template.update(sql, deptno, dname, ename);
	}
	
	public int updateOne(int deptno, String dname, String ename) {
		String sql = "update dept set dname=?, ename=? where deptno=?";
		JdbcTemplate<DeptDto> template = new JdbcTemplate<DeptDto>(dataSource);
		return template.update(sql, dname, ename, deptno);
	}
}

MongoDB Replica set

메모리 DB는 잘죽기 때문에 Replica로 여러개의 서버를 하나로 묶어서 관리
Primary만 관리를 할 수 있고 Second 들은 Primary의 데이터를 복사해서 가지고만 있다가
Primary가 죽게되면 다음 Secondary가 Primary가 되어서 관리를 할 수 있도록 하게 함

폴더 구성 및 config 파일 세팅

repl.cfg 파일 3개 추가 db, log 폴더 각각 추가

 

cmd창 3개를 띄워서 각각 실행
mongod -f repl1.cfg 

몽고쉘을 통해 접속
mongosh --port 27117 

replica set 지정(cfg에 설정된 값과 같아야함)

rs.initiate( {
   _id : "rs0",
   members: [
      { _id: 0, host: "127.0.0.1:27117" },
      { _id: 1, host: "127.0.0.1:27217" },
      { _id: 2, host: "127.0.0.1:27317" }
   ]
})

설정되고 나면 primary 인지 secondary인지 접속했을 때 구분이되며
primary만 db를 관리 및 접근 가능하며 secondary들은 전부 불가능하다.