[JDBC] JAVA에서 쿼리문 날리는 방법

1. SELECT문으로 DB 정보 조회 (stmt.executeQuery)

String dbType = "com.mysql.cj.jdbc.Driver";
String connectUrl = "jdbc:mysql://localhost:3306/jdbcprac2?serverTimezone=UTC";
String connectId = "root";
String connectPw = "1111";

try {
Class.forName(dbType); 
Connection con = DriverManager.getConnection(connectUrl,connectId,connectPw); 
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM userinfo");
			
while(rs.next()) { 			
System.out.println(rs.getString(1)); // user_id
System.out.println(rs.getString(2)); // user_pw
System.out.println(rs.getString(3)); // user_name 
System.out.println(rs.getString(4)); // mail
}
	
} catch(Exception e) {
e.printStackTrace();
}

 

2. INSERT문으로 레코드 추가 (stmt.executeUpdate)

Scanner scan = new Scanner(System.in);
System.out.println("아이디를 입력해주세요");
String uid = scan.nextLine();

String dbType = "com.mysql.cj.jdbc.Driver";
String connectUrl = "jdbc:mysql://localhost:3306/jdbcprac2?serverTimezone=UTC";
String connectId = "root";
String connectPw = "1111";

try {
Class.forName(dbType); 
Connection con = DriverManager.getConnection(connectUrl,connectId,connectPw); 
Statement stmt = con.createStatement();
String sql = "INSERT INTO userinfo VALUES ('" + uid + "',2222, 이름, ss@ss.ss)";
stmt.executeUpdate(sql);
// SELECT문을 제외한 나머지 = INSERT, DELETE에서는 좌변에 resultSet을 적지 않는다.

} catch(Exception e) {
e.printStackTrace();
}

 

3. DELETE문으로 DB 삭제  (stmt.executeUpdate)

String dbType = "com.mysql.cj.jdbc.Driver";
String connectUrl = "jdbc:mysql://localhost:3306/jdbcprac2?serverTimezone=UTC";
String connectId = "root";
String connectPw = "1111";

try {
Class.forName(dbType); 
Connection con = DriverManager.getConnection(connectUrl,connectId,connectPw); 
Statement stmt = con.createStatement();
String sql = "DELETE FROM userinfo WHERE user_id = '" + uid + "'";
stmt.executeUpdate(sql);
// SELECT문을 제외한 나머지 = INSERT, DELETE에서는 좌변에 resultSet을 적지 않는다.
} catch(Exception e) {
e.printStackTrace();
}

 

+ 4. Statement 대신 PreparedStatement 객체로 쿼리문 작성

String dbType = "com.mysql.cj.jdbc.Driver";
String connectUrl = "jdbc:mysql://localhost:3306/jdbcprac2?serverTimezone=UTC";
String connectId = "root";
String connectPw = "1111";

Scanner scan = new Scanner(System.in);
System.out.println("아이디를 입력해주세요");
String uid = scan.nextLine();

try {
Class.forName(dbType); 
Connection con = DriverManager.getConnection(connectUrl,connectId,connectPw); 
String sql = "SELECT * FROM userinfo WHERE user_id = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1,uid);
ResultSet rs = pstmt.executeQuery();

if(rs.next()) {
System.out.print(rs.getString(1));
System.out.print(rs.getString(2));
System.out.print(rs.getString(3));
System.out.print(rs.getString(4));
} else {
System.out.print(uid + "는 db에 없는 아이디입니다.");
}
} catch(Exception e) {
e.printStackTrace();
}