package test1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DB {
// 데이터베이스 연결
private Connection connection() {
Connection con = null;
String driverName = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
try {
// 드라이버 로드
Class.forName(driverName);
// 연결
con = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO 자동 생성된 catch 블록
e.printStackTrace();
}
// 모두완료된 con을 리턴해줌
return con;
}
public boolean login(String idx, String pw) {
String sql = "select * from member where idx= '" + idx + "' and pw='" + pw + "'";
Connection con = connection();
PreparedStatement pstmt;
ResultSet re;
String idxchk = null, pwchk = null;
try {
pstmt = con.prepareStatement(sql);
re = pstmt.executeQuery();
while (re.next()) {
idxchk = re.getString(1);
pwchk = re.getString(2);
}
if (idxchk.equals(idx) && pwchk.equals(pw)) {
return true;
}
con.close();
pstmt.close();
re.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println(e.toString() + " login Method sql error");
return false;
} catch (NullPointerException e1) {
System.out.println(e1.toString() + " login Method null error");
return false;
}
return false;
}
public boolean sign(String idx, String pw, String name, String phone, String addr, String gender, String email) {
String sql = "insert into member(idx,pw,name,phone,addr,gender,email) values(?,?,?,?,?,?,?)";
String sql2 = "select * from member where idx='" + idx + "'";
Connection con = connection();
PreparedStatement pstmt;
ResultSet re = null;
try {
// con.setAutoCommit(false);
pstmt = con.prepareStatement(sql);
pstmt.setString(1, idx);
pstmt.setString(2, pw);
pstmt.setString(3, name);
pstmt.setString(4, phone);
pstmt.setString(5, addr);
pstmt.setString(6, gender);
pstmt.setString(7, email);
pstmt.executeUpdate();
pstmt.close();
// pstmt = con.prepareStatement(sql2);
// re = pstmt.executeQuery();
//
// if (re.next()) {
// con.rollback();
// System.out.println("roll");
// } else {
// con.commit();
// System.out.println("comit");
// }
pstmt.close();
// con.setAutoCommit(true);
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println(e.toString() + " sign Method sql error");
return false;
}
}
public String member_list() {
String sql = "select * from member ";
Connection con = connection();
PreparedStatement pstmt;
ResultSet re = null;
String list = "";
try {
pstmt = con.prepareStatement(sql);
re = pstmt.executeQuery();
while (re.next()) {
list += re.getString(1) + " ";
}
con.close();
pstmt.close();
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println(e.toString() + " sign Method sql error");
}
return list;
}
public String member_info() {
String sql = "select * from member ";
Connection con = connection();
PreparedStatement pstmt;
ResultSet re = null;
String list = "";
String[] item = { "아이디 ", "비밀번호 ", "이름 ", "전화번호 ", "주소 ", "성별 ", "메일 " };
try {
pstmt = con.prepareStatement(sql);
re = pstmt.executeQuery();
while (re.next()) {
for (int i = 1; i < 8; i++) {
if (i == 7) {
list += item[i - 1] + re.getString(i) + ";";
} else {
list += item[i - 1] + re.getString(i) + " ";
}
}
}
con.close();
pstmt.close();
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println(e.toString() + " sign Method sql error");
}
return list;
}
public boolean member_del(String idx) {
String sql = "delete from member where idx= '" + idx + "'";
Connection con = connection();
PreparedStatement pstmt;
String list = "";
try {
pstmt = con.prepareStatement(sql);
pstmt.executeQuery();
con.close();
pstmt.close();
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println(e.toString() + " sign Method sql error");
}
return false;
}
}
