TIL 2022-09-01

JAVA와 SQL연결

LIST 출력

	public void getList() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		int cnt = 0;
		try {
			con = pool.getConnection();
			sql = "select * from member";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				String mem_id=rs.getString("mem_id");
				String mem_name=rs.getString("mem_name");
				int mem_number=rs.getInt("mem_number");
				String addr=rs.getString("addr");
				String phone1=rs.getString("phone1");
				String phone2=rs.getString("phone2");
				int height =rs.getInt("height");
				String debut_date=rs.getString("debut_date");
				
				System.out.println(mem_id+" "+mem_name+" "
				+ mem_number+"" +addr+" "+phone1+""+phone2+""+height+" "
				+ debut_date+" ");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt, rs);
		}
	}
    
    public static void main(String[] args) {
		MySQLConnection m=new MySQLConnection();
        m.getList();

insert

public void insert(String mem_id,String mem_name, int mem_number, String addr, String phone1,
			String phone2, int height, String debut_date) {
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			con = pool.getConnection();
			sql = "insert member values(?,?,?,?,?,?,?,?)";  //8개를 부르니까. method
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, mem_id);  //1은 첫번째 물음표를 말한다.
			pstmt.setString(2, mem_name);  
			pstmt.setInt(3, mem_number);  //3,7은 int로 받아들인다.
			pstmt.setString(4, addr);  
			pstmt.setString(5, phone1);  
			pstmt.setString(6, phone2);
			pstmt.setInt(7, height);  
			pstmt.setString(8, debut_date);  
			//실행해달라고 요청하기
			int cnt=pstmt.executeUpdate();
			if(cnt==1) {
				System.out.println("입력성공");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt);
		}
	}
    public static void main(String[] args) {
		MySQLConnection m=new MySQLConnection();
        

sql = "insert member values(?,?,?,?,?,?,?,?)";
?개수 = 변수 개수
?의 순서에 맞게 넣는 것이 중요하다.

update문

public void update(String mem_id,String mem_name, int mem_number, String addr, String phone1,
			String phone2) {
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			con = pool.getConnection();
			sql = "update member set mem_name=?, mem_number=?,"
					+ "addr=?, phone1=?, phone2=? where mem_id=?";  //이 순서가 중요. 매개변수 상관없다.
			pstmt = con.prepareStatement(sql);
			 //1은 첫번째 물음표를 말한다.
			pstmt.setString(1, mem_name);  
			pstmt.setInt(2, mem_number);  
			pstmt.setString(3, addr);  
			pstmt.setString(4, phone1);  
			pstmt.setString(5, phone2);
			pstmt.setString(6, mem_id); 
			//실행해달라고 요청하기
			int cnt=pstmt.executeUpdate(); //int로 돌려받음 ->return. 오른쪽에서 실행 후 왼쪽에 대입
			if(cnt==1) {
				System.out.println("수정성공");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt);
		}
	}
    
    public static void main(String[] args) {
			MySQLConnection m=new MySQLConnection();
            m.update("WON","원더걸스2", 5, "성남", "032", "9876543");

delete

public void delete(String mem_id) { //갈색: 예약어
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			con = pool.getConnection();
			sql = "delete from member where mem_id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1,mem_id);  //이게 mem_id=?에 들어가게 된다.
			//실행
			int cnt = pstmt.executeUpdate(); //()가 있으면 무조건 method
			if(cnt==1) {
				System.out.println("삭제성공");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			pool.freeConnection(con, pstmt);
		}
	}
    
    public static void main(String[] args) {
			MySQLConnection m=new MySQLConnection();
            m.delete("WON");

덤프 3000문제 -java에 자신감 생김(문법)
ocjp

public Vector<MemberBean> selectAll() {  
		Connection con = null;    //java.sql을 데려와야 한다!!
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		Vector<MemberBean>vlist = new Vector<MemberBean>();
		try {
			con = pool.getConnection();
			sql = "select * from tblmember1";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				MemberBean bean = new MemberBean();  //bean은 한 줄로 받아들인다. bean 사용하지않으면 2개=>8개됨.
				bean.setId(rs.getInt("id"));
				bean.setName(rs.getString("name"));
				bean.setPhone(rs.getString("phone"));
				bean.setTeam(rs.getString("team"));
				vlist.add(bean);  //주머니에 콩 넣는다.
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt, rs);
		}
		return vlist;
	}