[JAVA] JDBC_2

2017.05.30

JDBC

1.  JDBC 연동 하는 법

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class Class0530 {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

String user = "system";

String pass = "1111";

Connection con = DriverManager.getConnection(url, user, pass);

if(con != null) {

System.out.println("성공");

} else {

System.out.println("실패");

}

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

}

}

=> "성공" 뜨면 연결된거임!

2. DB와 Java연결 통로 Statement생성 해주고 값 입력해주기

- statement는 executeupdate(__) 나 executequery(__) 괄호안에 매번 sql을 넣어줘야 한다.

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class Class0530_insert {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

String user = "system";

String pass = "1111";

Connection con = DriverManager.getConnection(url, user, pass);

String sql = "intser into member valuse('A','78','남자')";

Statement st = con.createStatement(); //java와 db 연결 할 통로 만들어 주는 작업

st.executeUpdate(sql);

int cnt = st.executeUpdate(sql);

if (cnt > 0) { //성공하면 cnt가 0보다 큰 값이 나오는데 insert라서 1의 값만 온다는건 참고만!

System.out.println("성공");

} else {

System.out.println("실패");

}

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

}

}

3. update 사용하기

update member set age='20' where name='A';

4. delete 사용하기

* delete member where name='A';

* delete member where name like '김__' or name like '이__';

* delete member where name like '%다%';

5. Scanner로 입력 받아서 이름 나이 성별 넣기

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

public class Class0530_update {

public static void main(String[] args) {

Scanner sc = new Scanner(System.in);

System.out.print("이름 : ");

String name = sc.nextLine();

System.out.print("나이 : ");

String age = sc.nextLine();

System.out.print("성별 : ");

String gender = sc.nextLine();

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

String user = "system";

String pass = "1111";

Connection con = DriverManager.getConnection(url, user, pass);

String sql;

sql = "insert into member values('"+name+"','"+age+"','"+gender+"')";

Statement st = con.createStatement(); 

int cnt = st.executeUpdate(sql);

if (cnt > 0) {

System.out.println("성공");

} else {

System.out.println("실패");

}

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

}

}

6. Scanner로 입력받아 update하기

sql = "update member set gender='"+gender+"'where name='"+name+"'";

7. Scanner로 입력받아 delete하기

sql = "delete member where age='"+age+"'";

7. ResultSet로 정보 받아오기

- 테이블 형태로 받아온다.

ex) 

name	age	gender
A	99	남자
B	15	여자
C	75	남자

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class Class0530_update {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

String user = "system";

String pass = "1111";

Connection con = DriverManager.getConnection(url, user, pass);

String sql;

sql = "select * from member";

Statement st = con.createStatement();

ResultSet rs = st.executeQuery(sql);

rs.next();

System.out.println(rs.getString("name") + "/" + rs.getString("age") + "/" + rs.getString("gender"));

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

}

}

=> 위의 경우 첫번째에 있는 데이터 값 밖에 받아오질 못한다. 즉, 반복을 해줘야 하는데 얼만큼 반복을 해줘야 하는지를 모으니까 while문을 쓰는게 좋다.

----------------------------------------------------------------

while (rs.next()) { //rs.next()는 불리언타입이라 값이 false가 될때까지 반복실행 된다.

System.out.println(rs.getString("name") + "/" + rs.getString("age") + "/" + rs.getString("gender"));

} 

8. prepared Satement

- statement가 생성되는 동시에 sql문을 준비를 해 놓기때문에 update나 query 괄호안에 sql을 넣어 줄 필요가 없다.

ex)

PreparedStatement pst = con.prepareStatement(sql);

pst.setString(1, name);

pst.setString(2, age);

pst.setString(3, gender);

pst.executeUpdate();

9. prepared Satement이용해서 insert 하기

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

public class Class0530_update {

public static void main(String[] args) {

Scanner sc = new Scanner(System.in);

System.out.print("이름 : ");

String name = sc.nextLine();

System.out.print("나이 : ");

String age = sc.nextLine();

System.out.print("성별 : ");

String gender = sc.nextLine();

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

String user = "system";

String pass = "1111";

Connection con = DriverManager.getConnection(url, user, pass);

String sql;

sql = "insert into member values(?,?,?)";

PreparedStatement pst = con.prepareStatement(sql);

pst.setString(1, name);

pst.setString(2, age);

pst.setString(3, gender);

pst.executeUpdate();

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

}

}

10. prepared Satement이용해서 update 하기

- 이름 바꾸기

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

public class Class0530_update {

public static void main(String[] args) {

Scanner sc = new Scanner(System.in);

System.out.print("바꿀 이름 : ");

String name = sc.nextLine();

System.out.print("어떤 이름 : ");

String name2 = sc.nextLine();

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

String user = "system";

String pass = "1111";

Connection con = DriverManager.getConnection(url, user, pass);

String sql;

sql = "update member set name = ? where name = ?";

PreparedStatement pst = con.prepareStatement(sql);

pst.setString(1, name2);

pst.setString(2, name);

pst.executeUpdate();

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

}

}

=>sql = "update member set name = '?' where name = '?'"; 이게 안되는 이유

 : 입력 값을 넣는게 아니라 ?를 각각에 넣어주는 거기 때문이다.

11. prepared Satement이용해서 delete하기

- 이름 입력 받아서 지워라

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

public class Class0530_update {

public static void main(String[] args) {

Scanner sc = new Scanner(System.in);

System.out.print("삭제 할 이름 : ");

String name = sc.nextLine();

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

String user = "system";

String pass = "1111";

Connection con = DriverManager.getConnection(url, user, pass);

String sql;

sql = "delete member where name = ?";

PreparedStatement pst = con.prepareStatement(sql);

pst.setString(1, name);

pst.executeUpdate();

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

}

}