JDBC query

DataBase/JDBC

by H_Develop 2022. 8. 10. 17:42

sql insert / 자바씨 34 입력

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class JDBC2 {
	public static void main(String[] args) {
		Connection con = null;
		Statement st = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con = DriverManager.getConnection
("jdbc:oracle:thin:@61.98.237.228:1521:orcl123", "test", "rootoor");
			String sql = "insert into test_T values('자바씨','34')";
			st = con.createStatement();
			st.executeUpdate(sql);
			System.out.println("입력 성공");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(st != null) {st.close();}
				if(con != null) {con.close();}
			} catch (Exception e) {}
		}
		
	}
}

입력 성공

select * form test_T

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
// oracle java 연결
public class JDBC1 {
	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.OracleDriver");	
			// 무조건 해야함 oracledriver 연결
			// jdbc:oracle:thin:@localhost:port:sid
			// jdbc:oracle:db_1:@localhost:1521:
			String url = "jdbc:oracle:thin:@61.98.237.228:1521:orcl123";
			// db_1 그냥 문법
			String user = "test";
			String pwd = "rootoor";
			Connection con = DriverManager.getConnection(url, user, pwd);
//			(Statement, PreparedStatement, CallableStatement);
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select * from test_T");
			while(rs.next()) {
				System.out.println(rs.getString("name") +  "/" + rs.getInt("age"));
			}
			rs.close();
			st.close();
			con.close();
		} catch (Exception e) {e.printStackTrace();}
	}
}

자바씨/34

sql insert / 파이썬양/23 추가 입력

public class JDBC3 {
	public static void main(String[] args) throws Exception {
			Class.forName("oracle.jdbc.OracleDriver");
			Connection con = DriverManager.getConnection(
					"jdbc:oracle:thin:@61.98.237.228:1521:orcl123","test","rootoor");
			PreparedStatement pstmt = con.prepareStatement(
					"insert into test_T (age, name) values(?,?)");
			// values(?,?)를 밑에 변수로 채운다
			String name = "파이썬양";
			String age = "23";
			pstmt.setString(1, age);
			pstmt.setString(2, name);
			int cnt = pstmt.executeUpdate();
			if (cnt > 0) {System.out.println("저장완료");}
			if (pstmt != null) {pstmt.close();}
	}
}

저장완료

Scanner를 이용한, 데이터 입력

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class JDBC4 {
	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection(
				"jdbc:oracle:thin:@61.98.237.228:1521:orcl123","test","rootoor");
		PreparedStatement pstmt = con.prepareStatement(
				"insert into test_T(age, name) values(?,?)");
		// 위 코드는 공식처럼 사용된다.
		while(true) {
			System.out.println("이름을 입력하세요 : ");
			Scanner sc = new Scanner(System.in);
			String name = sc.next();
			System.out.println("나이를 입력하세요 : ");
			String age = sc.next();
			pstmt.setString(1, age);
			pstmt.setString(2,name);
			int cnt = pstmt.executeUpdate();
			if (cnt > 0) {
				System.out.println("저장완료");
				System.out.println("종료하시겠습니까 Y|N");
				String select = sc.next();
				if(select.equalsIgnoreCase("y")) {
					if(pstmt != null) {pstmt.close();}
					if(con != null) {con.close();}
					break;
				} else if (select.equalsIgnoreCase("n")) {
					continue;
				}
			}
		}
		
	}
}

이름을 입력하세요 :
JPark
나이를 입력하세요 :
40
저장완료
종료하시겠습니까 Y|N
y

데이터 삭제

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class JDBC5 {
	public static void main(String[] args) throws Exception{
		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection (
				"jdbc:oracle:thin:@61.98.237.228:1521:orcl123","test","rootoor");
		PreparedStatement pstmt = con.prepareStatement(
				"delete from test_T where name=? and age=?");
		
		while(true) {
			System.out.println("삭제할 이름을 입력");
			Scanner sc = new Scanner(System.in);
			String name = sc.next();
			System.out.println("삭제할 나이를 입력");
			String age = sc.next();
			pstmt.setString(1, name);
			pstmt.setString(2, age);
			int cnt = pstmt.executeUpdate();
			if(cnt > 0) {
				System.out.println("삭제완료");
			} else {
				System.out.println("정보 불일치");
			}
			System.out.println("종료할래? y|n");
			String select = sc.next();
			if (select.equalsIgnoreCase("y")) {
				if(pstmt != null) {pstmt.close();}
				if(con != null) {con.close();}
				break;
			} else if (select.equalsIgnoreCase("n")) {
				continue;
			}
		}
				
	}
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
public class JDBC6 {
	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection(
				"jdbc:oracle:thin:@61.98.237.228:1521:orcl123","test","rootoor");
		// 이름을 검색하기 위한 쿼리
		PreparedStatement pstmt = con.prepareStatement(
				"select * from test_T where name=?");
// 검색한 이름으로 비밀번호(여기서는 나이) 일치여부를 판단한 후 삭제하는 쿼리
		PreparedStatement pstmt2 = con.prepareStatement(
				"delete from test_t where name=? and age=?");
		while (true) {
			System.out.print("삭제할 이름 : ");
			Scanner sc = new Scanner(System.in);
			String name = sc.next();
			pstmt.setString(1, name);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				// rs.next()를 통해 입력받은 이름이 있는지 db 검사
				System.out.print(name + "의 나이 : ");
				String age = sc.next();
				pstmt2.setString(1, name);
				pstmt2.setString(2, age);
				int cnt = pstmt2.executeUpdate();
				if (cnt > 0) {
					System.out.println(name + " 삭제성공!!");
				} else {
					System.out.println("나이가 일치하지 않음");
				}
			} else {
				System.out.println("해당 이름이 없습니다.");
			}
			System.out.print("종료하시겠습니까? y | n : ");
			String select = sc.next();
			if (select.equalsIgnoreCase("y")) {  // 종료 할 경우
				if (pstmt != null)
					pstmt.close();
				if (pstmt2 != null)
					pstmt2.close();
				if (con != null)
					con.close();
				break;
			} else if (select.equalsIgnoreCase("n")) { // 계속인 경우
				continue;
			}	
		} 		
	}
}

Update

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class JDBC7 {
	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@61.98.237.228:1521:orcl123","test","rootoor");
		PreparedStatement pstmt = con.prepareStatement(
				"update test_T set age = ? where name = ?");
		// "update test_T set id=?, pwd=? where name= ?" 이렇게 활용 가능
		while(true) {
			System.out.println("이름 입력");
			Scanner sc = new Scanner(System.in);
			String name = sc.next();
			System.out.println("수정할 나이 입력");
			String age = sc.next();
			pstmt.setString(1, age);
			pstmt.setString(2, name);
			int n = pstmt.executeUpdate();
			if(n > 0) {
				System.out.println("수정 성공");
			} else {
				System.out.println("수정 실패");
			}
			System.out.println("종료할래? Y|N");
			String select = sc.next();
			if (select.equalsIgnoreCase("y")) {
				if(pstmt != null) {pstmt.close();}
				if(con != null) {pstmt.close();}
				break;
			} else if (select.equalsIgnoreCase("n")) {
				continue;
			}
			
		}
		
	}
}