JDBC DbManager (insert, update, delete, select 코드 모음)

개선1.

• enum으로 name,... 오타날 가능성도 줄이고, 딱 그것밖에 못받게 만들어놓기

pstmt.setString(1, (String) datas.get("name"));
↓
pstmt.setString(1, (String) datas.get(STUDENTCOLS.NAME));

• 근데 enum 대문자로 쓰고 싶은데 들어갈 때는 소문자로 들어가야하니까
• 각각 enum tostring으로 소문자로 재정의
  
  
• public class DbManager: 상속은 퍼블릭을 받아서 프라이빗으로 좁게 바꿀수는 없음

0. DbManager 코드

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

enum STUDENTCOLS {
	NAME {
		@Override
		public String toString() {
			return super.toString().toLowerCase();
		}
	},
	AGE {
		@Override
		public String toString() {
			return super.toString().toLowerCase();
		}
	},
	GENDER {
		@Override
		public String toString() {
			return super.toString().toLowerCase();
		}
	},
	GRADE {
		@Override
		public String toString() {
			return super.toString().toLowerCase();
		}
	},
	TYPE {
		@Override
		public String toString() {
			return super.toString().toLowerCase();
		}
	},

	;

}

public class DbManager implements DBHandler, AutoCloseable {
	private final String URL = "jdbc:mysql://localhost:3306/javadb?useUnicode=true&characterEncoding=utf8";
	private final String ID = "javadb";
	private final String PASSWORD = "admin1234";
	private Connection conn;

	public DbManager() {
		super();
		connectDB();
	}

	@Override
	public Connection connectDB() {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			this.conn = DriverManager.getConnection(URL, ID, PASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	@Override
	public boolean insertData(Map<String, Object> insertMap) {
		String sql = "  insert into student(name,age,gender,grade,type) values(?,?,?,?,?) ";
		try (PreparedStatement pstmt = conn.prepareStatement(sql);) {
			pstmt.setString(1, (String) insertMap.get(STUDENTCOLS.NAME.toString()));
			pstmt.setInt(2, (int) insertMap.get(STUDENTCOLS.AGE.toString()));
			pstmt.setInt(3, (int) insertMap.get(STUDENTCOLS.GENDER.toString()));
			pstmt.setInt(4, (int) insertMap.get(STUDENTCOLS.GRADE.toString()));
			pstmt.setInt(5, (int) insertMap.get(STUDENTCOLS.TYPE.toString()));
			if (pstmt.executeUpdate() == 1)
				return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	
// 선생님 방법 이해@@
	@Override
	public boolean updateData(String id, Map<String, Object> datas) {
		String sql = "update student set ";
		
		for (Entry<String, Object> entry : datas.entrySet()) {
			if(entry.getValue() instanceof String)
				sql += entry.getKey()+" = '" + entry.getValue() + "' ,";
			else
				sql += entry.getKey()+" = " + entry.getValue() + " ,";
		} 
		// update student set name = 동해 ,age = 20 ,
		sql = sql.substring(0, sql.length()-1);
		sql += " where id = "+id;
		
		try(
				PreparedStatement pstmt =  conn.prepareStatement(sql);
				) 
		{
			if(pstmt.executeUpdate() == 1)
				return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}


	@Override
	public List< Map<String, Object> > selectData() {
		List< Map<String, Object> > result = new ArrayList< Map<String, Object> >();
		String sql = "select * from student";
		try (
				PreparedStatement pstmt = conn.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery(sql);
				)
		{
			while (rs.next()) {
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("name", rs.getString("name")) ;
				m.put("id", rs.getInt("id")) ;
				m.put("age", rs.getInt("age")) ;
				m.put("gender", rs.getInt("gender")) ;
				m.put("grade", rs.getInt("grade")) ;
				m.put("type", rs.getInt("type")) ;
				result.add(m);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public boolean deleteData(String id) {
		String sql = " DELETE FROM student WHERE id = ?";
		try (
				PreparedStatement pstmt = conn.prepareStatement(sql);
				) {
			pstmt.setInt(1, Integer.parseInt(id));
			if (pstmt.executeUpdate() == 1)
				return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public void close() throws Exception {
		conn.close();

	}

}

1. insert

import java.util.HashMap;
import java.util.Map;

public class InsertDemo {
	public static void main(String[] args) {
		// insert
		DbManager dbm = new DbManager();
		Map<String, Object> insertMap = new HashMap< String, Object>();
		insertMap.put(STUDENTCOLS.NAME.toString(), "철수");
		insertMap.put(STUDENTCOLS.AGE.toString() , 12);
		insertMap.put(STUDENTCOLS.GENDER.toString() , 1);
		insertMap.put(STUDENTCOLS.GRADE.toString() , 1);
		insertMap.put(STUDENTCOLS.TYPE.toString() , 1);
		dbm.insertData(insertMap);
	}
}

2. update

import java.util.HashMap;
import java.util.Map;

public class UpdateDemo {

	public static void main(String[] args) {
		DbManager dbm = new DbManager();
		Map<String, Object> updateMap = new HashMap<String, Object>();
		updateMap.put(STUDENTCOLS.NAME.toString(), "자동차");
		updateMap.put(STUDENTCOLS.NAME.toString(), "자동차");
		updateMap.put(STUDENTCOLS.TYPE.toString(), 4);
		
		System.out.println( dbm.updateData("20", updateMap) ?  "update success" : "update false") ;
	}
}

3. delete

public class DeleteDemo {

	public static void main(String[] args) {
		// delete
		DbManager dbm = new DbManager();
		System.out.println("before delete..........");
		dbm.selectData().forEach(x -> System.out.println(x));
		dbm.deleteData("5");
		System.out.println("after delete..........");
		dbm.selectData().forEach(x -> System.out.println(x));
	}
}

4. select

import java.util.List;
import java.util.Map;

public class SelectDemo {

	public static void main(String[] args) {
		// select
		DbManager dbm = new DbManager();
		List< Map<String, Object> > result = dbm.selectData();
		result.forEach(x -> System.out.println(x));
	}
}