[JAVA] JDBC #2

JDBC #1 의 예제로 이어짐

 

 

1.

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

public class JDBCtest01 {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1. driver 등록
		Class.forName("com.mysql.jdbc.Driver");
		
		//2.connection 객체 생성 
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/multi","root","1234");
		
		
		//3. 쿼리 실행 
		Statement stmt = con.createStatement(); 
		ResultSet rs = stmt.executeQuery(" SELECT * FROM EMPLOYEE ");
		
		//4. 결과 처리
		while(rs.next()) {
			System.out.println(rs.getInt(1) + " " + rs.getString(2)+" " + rs.getInt("SALARY"));
		}
		
		//5.DB종료 
		rs.close();
		stmt.close();
		con.close();
		->
200 선동일 8000000
201 송종기 6000000
202 노옹철 3700000
203 송은희 2800000
204 유재식 3400000
205 정중앙 3900000
206 박나라 1800000
207 하이유 2200000
208 김해술 2500000
209 심봉선 3500000
210 윤은해 2000000
211 전형돈 2000000
212 장쯔위 2550000
213 하동운 2320000
214 방명수 1380000
215 대북혼 3760000
216 차태연 2780000
217 전지연 3660000
218 이오리 2890000
219 임시환 1550000
220 이중석 2490000
221 유하진 2480000
222 이태림 2436240


}

 

2.

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

public class JDBCTest02 {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1.
		Class.forName("com.mysql.cj.jdbc.Driver");
		
		//2.
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/multi",
													"root",
													"1234");
		//3.
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM DEPARTMENT ");
		
		//4.
		while(rs.next()) {
			System.out.println(rs.getString(1)+ " : "+rs.getString(2)+" : " +
								rs.getString("LOCATION_ID"));
		}
		
		
		//5.
		rs.close();
		stmt.close();
		con.close();
		}

}
D1 : 인사관리부 : L1
D2 : 회계관리부 : L1
D3 : 마케팅부 : L1
D4 : 국내영업부 : L1
D5 : 해외영업1부 : L2
D6 : 해외영업2부 : L3
D7 : 해외영업3부 : L4
D8 : 기술지원부 : L5
D9 : 총무부 : L1

 

3.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import common.JDBCTemplate;

public class JDBCTest03 extends JDBCTemplate {

	public static void main(String[] args) throws SQLException {
		//MYTEST 테이블에 insert 
		
		//0.준비 
		Connection con = null;
		Statement stmt = null;
		int res = 0;
		//키보드로 값 입력받아보자
		Scanner sc = new Scanner(System.in);
		System.out.println("NO: ");
		int no = sc.nextInt();
		System.out.println("NAME: ");
		String name = sc.next();
		System.out.println("NICKNAME: ");
		String nickName = sc.next();
		
		//insert into mytest values( mno, 'mname' , 'nickname');
		String sql = "INSERT INTO MYTEST VALUES( "+no+", '"+name+"','"+nickName+"') ";
		System.out.println(sql);
		// 싱글코테이션 주의 
		
		//1.
		con = getConnection();
		// jdbc템플릿 상속받아빨간줄 없애줌 
		
		//2. 
		stmt = con.createStatement();
		res = stmt.executeUpdate(sql); // query 실행 후 적용된 row의 숫자를 리턴
		
		//3.
		if(res>0) {
			System.out.println("insert 성공");
		}else {
			System.out.println("실패");
		}
		
		//4.
		Close(stmt);
		Close(con);
		sc.close();
		
	}
}

 

4.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
import static common.JDBCTemplate.*;

public class JDBCTest04 {

	public static void main(String[] args) throws SQLException {
		// 0.준비
		Connection con = null;
		PreparedStatement pstm = null;
		int res = 0;

		int no = 0;
		String name = null;
		String nickName = null;

		String sql = "INSERT INTO MYTEST VALUES(?,?,?)";
		// 입력받은것들을 물음표에 채워주겠음
		Scanner sc = new Scanner(System.in);
		System.out.println("NO : ");
		no = sc.nextInt();
		System.out.println("NAME: ");
		name = sc.next();
		System.out.println("NICKNAME: ");
		nickName = sc.next();

		//
		con = getConnection();

		//
		pstm = con.prepareStatement(sql);
		pstm.setInt(1, no);
		pstm.setString(2, name);
		pstm.setString(3, nickName);

		res = pstm.executeUpdate();

		if (res > 0) {
			System.out.println("성공");
			Commit(con);
		} else {
			System.out.println("실패");
			Rollback(con);
		}

		//
		Close(pstm);
		Close(con);
		sc.close();

	}

}

→ 매개변수를 이용하는 statement