C#
[220321] [C# +Java] Employees 직원 데이터 CRUD
감자탈출기
2022. 3. 21. 16:44
C# front
Read
직원 리스트 조회
Create
직원 추가
Update
직원 정보 수정 -> 기존 정보 바인딩
최근 업데이트 직원 행 자동 select
Delete
직원 삭제
FormEmployeesInfo.cs
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace EmployeesCRUD
{
public partial class FormEmployeesInfo : Form
{
private FormEmployee _subForm;
public FormEmployeesInfo()
{
InitializeComponent();
_subForm = new FormEmployee(this, DataGridView);
}
public void Display()
{
string empList = EmpHttpRequest.GetEmployeesList();
//바인딩 json ->net
dynamic dynamicObject = JsonConvert.DeserializeObject(empList);
DataGridView.DataSource = dynamicObject;
//seq 컬럼 제외
DataGridView.Columns.Remove("seq");
}
public void RowSelect()
{
}
private void BtnNew_Click(object sender, EventArgs e)
{
_subForm.Clear();
_subForm.ShowDialog();
}
private void FormEmployeesInfo_Shown(object sender, EventArgs e)
{
Display();
}
private void DataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
//Edit
if(e.ColumnIndex == 0)
{
//기본생성자에서 new subform 생성된 상태
_subForm.Clear();
_subForm._empNo = DataGridView.Rows[e.RowIndex].Cells[3].Value.ToString();
_subForm._birthDate = DataGridView.Rows[e.RowIndex].Cells[4].Value.ToString();
_subForm._firstName = DataGridView.Rows[e.RowIndex].Cells[5].Value.ToString();
_subForm._lastName = DataGridView.Rows[e.RowIndex].Cells[6].Value.ToString();
_subForm._gender = DataGridView.Rows[e.RowIndex].Cells[7].Value.ToString();
_subForm.SetOldInfo();
_subForm.ShowDialog();
DataGridView.ClearSelection();
DataGridView.Rows[e.RowIndex].Selected = true;
return;
}
//Delete
if (e.ColumnIndex == 1)
{
if (MessageBox.Show("직원 정보를 영구 삭제하시겠습니까?", "Information", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information) == DialogResult.Yes)
{
string empNo = DataGridView.Rows[e.RowIndex].Cells[3].Value.ToString();
string deleteMsg = EmpHttpRequest.RemoveEmployees(empNo);
MessageBox.Show(deleteMsg);
Display();
}
return;
}
//checkbox
if (e.ColumnIndex == 2)
{
return;
}
}
}
}
FormEmployee.cs
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace EmployeesCRUD
{
public partial class FormEmployee : Form
{
private readonly FormEmployeesInfo _parent;
private readonly DataGridView _parentGrid;
public string _empNo, _birthDate, _firstName, _lastName, _gender, _hireDate = string.Empty;
public FormEmployee(FormEmployeesInfo parent, DataGridView parentGrid)
{
InitializeComponent();
_parent = parent;
_parentGrid = parentGrid;
}
public void Clear()
{
lbltext.Text = "Add Employee";
BtnSave.Text = "Save";
BirthDate.Text = LastName.Text = FirstName.Text = String.Empty;
Gender.SelectedIndex = -1;
}
public void SetOldInfo()
{
lbltext.Text = "Edit Employee";
BtnSave.Text = "Update";
BirthDate.Text = _birthDate;
FirstName.Text = _firstName;
LastName.Text = _lastName;
Gender.SelectedItem = _gender;
}
private void BtnSave_Click(object sender, EventArgs e)
{
string seq = string.Empty;
if (BirthDate.Text.Trim().Length == 0)
{
MessageBox.Show("생년월일을 입력하세요.");
return;
}
if (FirstName.Text.Trim().Length == 0)
{
MessageBox.Show("이름을 입력하세요.");
return;
}
if (LastName.Text.Trim().Length == 0)
{
MessageBox.Show("성을 입력하세요.");
return;
}
if (Gender.SelectedIndex == -1)
{
MessageBox.Show("성별을 선택하세요.");
return;
}
if(BtnSave.Text == "Save")
{
//사용자 입력값 json 객체에 담아서 AddJobject() post httpbody로 전송
string resMap = EmpHttpRequest.AddEmployeePost(AddJobject());
Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(resMap);
Console.WriteLine();
MessageBox.Show(dic["insertMsg"]);
seq = dic["seq"];
Clear();
}
if(BtnSave.Text == "Update")
{
//MessageBox.Show("디버그 ->" + BirthDate.Text.Trim().ToString());
JObject jobject = AddJobject();
jobject.Add("empNo", _empNo);//update위한 PK 추가
string Updatemsg = EmpHttpRequest.EditEmployeePost(jobject);
MessageBox.Show(Updatemsg);
Clear();
this.Close();
}
_parent.Display();
if(seq != null)
{
int rowIndex;
foreach (DataGridViewRow row in _parentGrid.Rows)
{
try
{
if (row.Cells[3].Value.ToString().Equals(seq))
{
rowIndex = row.Index;
//_parentGrid.CurrentCell = _parentGrid.Rows[rowIndex].Cells[3];
_parentGrid.ClearSelection();
_parentGrid.Rows[rowIndex].Selected = true;
break;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
// _parentGrid.Rows[e.RowIndex].Cells[3].Value.ToString();
}
public JObject AddJobject()
{
JObject jobject = new JObject
{
{ "birthDate", BirthDate.Text.Trim().ToString() },
{ "firstName", FirstName.Text.Trim().ToString() },
{ "lastName", LastName.Text.Trim().ToString() },
{ "gender", Gender.SelectedItem.ToString() }
};
return jobject;
}
}
}
EmpHttpRequest.cs
using Newtonsoft.Json.Linq;
using System;
using System.IO;
using System.Net;
using System.Text;
namespace EmployeesCRUD
{
internal class EmpHttpRequest
{
public static string GetEmployeesList()
{
string result = string.Empty;
try
{
WebClient client = new WebClient();
client.Encoding = Encoding.UTF8;
result = client.DownloadString("http://localhost:8081/getEmployeesList");
Console.WriteLine(result);
/*결과동일
//result = client.DownloadString("http://localhost:8081/getEmployeesList");
//특정 요청 헤더값을 추가
//client.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)");
using (Stream data = client.OpenRead("http://localhost:8081/getEmployeesList"))
{//openRead
using (StreamReader reader = new StreamReader(data))
{
result = reader.ReadToEnd();
Console.WriteLine("스트림->" + result);
}
}
*/
}
catch (Exception e)
{
Console.WriteLine(e.ToString()); //통신 실패
}
return result;
}
public static string AddEmployeePost(JObject jsonStr)
{
// url를 통해 HttpWebRequest 클래스를 생성한다.
var httpWebRequest = (HttpWebRequest)WebRequest.Create("http://localhost:8081/addEmployee");
// 해더의 메소드를 정의한다.
httpWebRequest.Method = "POST";
// 해더의 ContentType를 정의한다.
httpWebRequest.ContentType = "application/json; charset=utf-8";
// 프로퍼티로 정의된 해더의 경우, 아래와 같이 정의할 경우 에러가 발생한다. (???)
//request.Headers["Upgrade-Insecure-Requests"] = "1";
using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
{
streamWriter.Write(jsonStr);
}
//
HttpWebResponse httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
using (StreamReader streamReader = new StreamReader(httpResponse.GetResponseStream()))
{
string result = streamReader.ReadToEnd();
Console.WriteLine(result);
return result;
}
}
public static string RemoveEmployees(string empNo)
{
//get 문자열
WebClient client = new WebClient();
client.Encoding = Encoding.UTF8;
StringBuilder sb = new StringBuilder();
sb.Append("http://localhost:8081/removeEmployees?");
sb.AppendFormat("empNo={0}", empNo);
//웹 서버에서 HTML이나 문자열을 다운로드 받기 위해서 DownloadString(), get형식
string result = client.DownloadString(sb.ToString());
Console.WriteLine(result);
return result;
}
public static string EditEmployeePost(JObject jsonStr)
{
string result = String.Empty;
WebClient client = new WebClient();
client.Encoding = Encoding.UTF8;
client.Headers.Add(HttpRequestHeader.ContentType, "application/json");
try
{
//지정된 문자열을 지정된 리소스에 업로드 UploadString(), post형식
result = client.UploadString("http://localhost:8081/editEmployee", jsonStr.ToString());
}
catch (Exception e)
{
Console.WriteLine(e.ToString()); //통신 실패
}
return result;
}
}
}JAVA : back
application.yml
server:
port: 8081
tomcat:
threads:
max: 1
spring:
config:
import:
- demoConfig.yml
datasource:
# username: sa
# password: Brique01@
# jdbc-url: jdbc:mariadb://nipagpu.brique.kr:8306/common_db
# driver-class-name: org.mariadb.jdbc.Driver
username: root
password: brique115
driver-class-name: org.mariadb.jdbc.Driver
jdbc-url: jdbc:mariadb://localhost:3306/employees
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.4</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- Mybatis -->
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client -->
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<!-- <version>2.7.4</version> -->
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
<build>
<!-- 이런짓 하면 xml 못읽어서(?) 빈 이상해짐... sqlSessingFactory오류
<resources>
<resource>
<directory>src/main/resources</directory>
<filtering>true</filtering>
<includes>
<include>*.yml</include>
<include>*.yaml</include>
<include>*.properties</include>
<include>*.xml</include>
</includes>
</resource>
</resources>
-->
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true" />
<setting name="callSettersOnNulls" value="true" />
</settings>
</configuration>
EmployeesMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mini.lmry.dao.EmployeesDao">
<select id="selectEmployeesInfo" resultType="com.mini.lmry.dto.EmployeesDto">
SELECT * FROM EMPLOYEES WHERE EMP_NO < 10010 <!-- < -->
</select>
<insert id="insertEmployeeOne" parameterType="com.mini.lmry.dto.EmployeesDto">
INSERT INTO EMPLOYEES VALUES (NEXTVAL(SQ_EMP_NO), #{birthDate}, #{firstName}, #{lastName}, #{gender}, SYSDATE())
<selectKey keyProperty="seq" resultType="int" order="AFTER">
SELECT LASTVAL(SQ_EMP_NO) FROM DUAL
</selectKey>
</insert>
<select id="selectEmployeeOne" parameterType="int" resultType="com.mini.lmry.dto.EmployeesDto">
SELECT * FROM EMPLOYEES WHERE EMP_NO = #{seq}
</select>
<update id="updateEmployeeInfo" parameterType="com.mini.lmry.dto.EmployeesDto">
UPDATE EMPLOYEES SET BIRTH_DATE = #{birthDate}, FIRST_NAME = #{firstName}, LAST_NAME = #{lastName}, GENDER = #{gender} WHERE EMP_NO = #{empNo}
</update>
<delete id="deleteEmployees" parameterType="com.mini.lmry.dto.EmployeesDto">
DELETE FROM EMPLOYEES WHERE EMP_NO = #{empNo}
</delete>
</mapper>
DataSourceConfig.java
package com.mini.lmry.config;
//sql로 임포트할것
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
//Resource 임포트
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
@Configuration
@MapperScan ("com.mini.lmry.dao")
public class DataSourceConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sessionFactory.setMapperLocations(resolver.getResources("classpath:mappers/*.xml"));
Resource mybatisConfig = new PathMatchingResourcePatternResolver().getResource("classpath:mybatis-config.xml");
sessionFactory.setConfigLocation(mybatisConfig);
return sessionFactory.getObject();
}
}
/*@MapperScan
매퍼를 하나씩 등록하는게 아닌 페키지 경로를 지정하여 이하 위치에있는 인터페이스들은 전부 맵퍼로 사용할수있다.
단일 페키지 지정 방법
@MapperScan("com.test")
다중 페키지 지정 방법들
@MapperScan(value = {"com.test","com.board"})
@MapperScan("com.test, com.board")
*/
service
package com.mini.lmry.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.mini.lmry.dao.EmployeesDao;
import com.mini.lmry.dto.EmployeesDto;
@Service
public class EmployeesService{
@Autowired
EmployeesDao employeesDao;
public List<EmployeesDto> getEmployeesList() {
return employeesDao.selectEmployeesInfo();
}
public int addEmployee(EmployeesDto dto) {
return employeesDao.insertEmployeeOne(dto);
}
public int removeEmployees(int empNo) {
return employeesDao.deleteEmployees(empNo);
}
public int editEmployee(EmployeesDto dto) {
return employeesDao.updateEmployeeInfo(dto);
}
public EmployeesDto getEmployeeOne(int seq) {
return employeesDao.selectEmployeeOne(seq);
}
}
dao
package com.mini.lmry.dao;
import java.util.List;
//import org.springframework.stereotype.Repository;
import com.mini.lmry.dto.EmployeesDto;
public interface EmployeesDao {
List<EmployeesDto> selectEmployeesInfo();
int insertEmployeeOne(EmployeesDto dto);
int deleteEmployees(int empNo);
int updateEmployeeInfo(EmployeesDto dto);
EmployeesDto selectEmployeeOne(int seq);
}
dto
package com.mini.lmry.dto;
import java.sql.Date;
import com.fasterxml.jackson.annotation.JsonFormat;
public class EmployeesDto {
int seq = -1;
int empNo;
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+9")//timezone 설정 국내로 안하면 여기 들렀다 나갈때 날짜 -1씩 되거나 그럴수 있음ㅠ..
Date birthDate;
String firstName;
String lastName;
char gender; //enum type coulmn
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+9")
Date hireDate;
public int getSeq() {
return seq;
}
public void setSeq(int seq) {
this.seq = seq;
}
public int getEmpNo() {
return empNo;
}
public void setEmpNo(int empNo) {
this.empNo = empNo;
}
public Date getBirthDate() {
return birthDate;
}
public void setBirthDate(Date birthDate) {
this.birthDate = birthDate;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public char getGender() {
return gender;
}
public void setGender(char gender) {
this.gender = gender;
}
public Date getHireDate() {
return hireDate;
}
public void setHireDate(Date hireDate) {
this.hireDate = hireDate;
}
@Override
public String toString() {
return "EmployeesDto [empNo=" + empNo + ", birthDate=" + birthDate + ", firstName=" + firstName + ", lastName="
+ lastName + ", gender=" + gender + ", hireDate=" + hireDate + "]";
}
}
controller
package com.mini.lmry.controller;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.configurationprocessor.json.JSONObject;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.mini.lmry.dto.EmployeesDto;
import com.mini.lmry.service.EmployeesService;
@RestController
public class EmployeesController {
@Autowired
private EmployeesService employeesService;
//로컬db
@GetMapping("/getEmployeesList")
public List<EmployeesDto> getEmployeesList() {
System.out.println(employeesService.getEmployeesList().toString());
return employeesService.getEmployeesList();
}
@PostMapping("/addEmployee")
@ResponseBody
public Map<String, String> addEmployee(@RequestBody EmployeesDto dto) throws IOException{
System.out.println(dto.toString());
int res = employeesService.addEmployee(dto);
int seq = dto.getSeq();
if(res == 1) {
EmployeesDto addedEmp = employeesService.getEmployeeOne(seq);
Map<String, String> map = new HashMap<String, String>();
map.put("insertMsg", "직원이 추가되었습니다.");
map.put("seq", String.valueOf(seq));
/*추가된 직원정보(object to string) 서버로 리턴하고 싶다면
ObjectMapper mapper = new ObjectMapper();
String jstr = mapper.writeValueAsString(addedEmp);
System.out.println("insertData : " + jstr);
return jstr;
*/
return map;
} else {
return null;
}
//Map이든 json이든 결과적으로 도달하는 생김새는 같음....
}
@GetMapping("/removeEmployees")
public String removeEmployees(int empNo) {
System.out.println("empNo : " + empNo);
int res = employeesService.removeEmployees(empNo);
if(res != 0) {
return "직원정보가 삭제 되었습니다.";
}
return "삭제 실패!";
}
@PostMapping("/editEmployee")
@ResponseBody
public String editEmployee(@RequestBody EmployeesDto dto) {
System.out.println("postMapping");
System.out.println("받은 dto -> " + dto);
int res = employeesService.editEmployee(dto);
//int seq = dto.getSeq();
if(res != 0) {
return "직원정보가 업데이트 되었습니다.";
}
return "직원정보 업데이트 실패!";
}
}