자바에서 쿼리호출

변수를 선언하고 만들었던 DBUtil class로 연결한다.

private Connection conn;
	private Statement stat;	//매개변수 X
	private PreparedStatement pstat; // 매개변수 O
	private ResultSet rs;
	
	public AddressDAO() {
				
		try {
			
			this.conn = DBUtil.open();
			this.stat = conn.createStatement();
			
			
		} catch (Exception e) {
			System.out.println("AddressDAO.AddressDAO()");
			e.printStackTrace();
		}
		
		
	}

 

insert query

	public int add(AddressDTO dto) {
		
		
		try {
			
			String sql = "insert into tblAddress values (seqAddress.nextVal, ?, ?, ?, ?, ?, default)";
			
			pstat = conn.prepareStatement(sql);
			
			pstat.setString(1, dto.getName());
			pstat.setString(2, dto.getAge());
			pstat.setString(3, dto.getGender());
			pstat.setString(4, dto.getTel());
			pstat.setString(5, dto.getAddress());
			
			return pstat.executeUpdate();
			
			
		} catch (Exception e) {
			System.out.println("AddressDAO.add()");
			e.printStackTrace();
		}
		
		return 0;
	}

 

select

	public int delete(String seq) {
		
		try {
			
			String sql = "delete from tblAddress where seq = ?";
			
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, seq);
			
			return pstat.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("AddressDAO.delete()");
			e.printStackTrace();
		}
		return 0;
	}

 

delete

try {
			
			String where = "";
			
			if(word != null) {
				where = String.format("where address like '%%%s%%'", word);
			}
			
			String sql = String.format("select seq, name, age, gender, tel, address, regdate from tblAddress %s order by seq desc", where);
			
			rs = stat.executeQuery(sql);
			
			ArrayList <AddressDTO> list = new ArrayList<AddressDTO>();
			
			while(rs.next()) {
				
				//레코드 1개 -> AddressDTO 1개
				AddressDTO dto = new AddressDTO();
				AddressDTO dto1 = new AddressDTO();
				
				dto.setSeq(rs.getString("seq"));
				dto.setName(rs.getString("name"));
				dto.setAge(rs.getString("age"));
				dto.setGender(rs.getString("gender"));
				dto.setTel(rs.getString("tel"));
				dto.setAddress(rs.getString("address"));
				dto.setRegdate(rs.getString("regdate"));
				
				list.add(dto);
			}

 

프로시저

		try {
			
			conn = DBUtil.open();
			
			//?는 out 매개변수
			String sql = "{ call procM4(?, ?, ?, ?) }";
			
			stat = conn.prepareCall(sql);
			
			boolean loop = true;
			
			while(loop) {
				System.out.println("[직원 조회]");
				System.out.print("직원 번호 : ");
				String num = scan.nextLine();
				
				if(!num.equals("")) {
					
					stat.setString(1, num);
					stat.registerOutParameter(2, OracleTypes.VARCHAR);
					stat.registerOutParameter(3, OracleTypes.VARCHAR);
					stat.registerOutParameter(4, OracleTypes.VARCHAR);
					
					stat.executeQuery();
					
					String name = stat.getString(2);
					String buseo = stat.getString(3);
					String jikwi = stat.getString(4);
					
					System.out.printf("이름 : %s\n부서 : %s\n직위 : %s\n",name, buseo, jikwi);
				}
				else {
					loop = false;
				}
			}
			
			
			stat.close();
			conn.close();
			
			
		} catch (Exception e) {
			System.out.println("Ex07_CallableStatement.m3()");
			e.printStackTrace();
		}