PreparedStatement / Quiz

🔎 Quiz) 데이터 베이스 자료 역순으로 출력하기

• MySQL DB구현
  
  
  
• Eclipse Class구현

package pack_SelectDrill;

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

public class Main {

	public static void main(String[] args) {
	
		Connection 	conn 	= null;
		Statement 	stmt 	= null;
		ResultSet 	res 	= null;		
		
		try {
			
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost:3308/db_test?";
			url += "useSSL=false&"; 
			url += "serverTimezone=Asia/Seoul&"; 
			url += "useUnicode=true&"; 
			url += "characterEncoding=UTF-8";
			
			String uid = "root";
			String upw = "1234";
			conn = DriverManager.getConnection(url, uid, upw);
			
			stmt = conn.createStatement();		
			String sql = "select * from datalist order by num desc";
			res = stmt.executeQuery(sql);
			
			System.out.println("번호    코드명");
			System.out.println("--------------");
			
			while (res.next()) {
				System.out.println(res.getInt("num") + "     " + res.getString("codename"));
			}
			
			res.close();
			stmt.close();
			conn.close();
			
			
		} catch (ClassNotFoundException e) {
			System.out.println(e.getMessage());
		}catch (SQLException e) {
			System.out.println(e.getMessage());
		}

}
}

👩‍💻 Statement / PreparedStatement

• SQL구문을 실행시키는 객체
• PreparedStatement 객체는 재사용 가능 / Statement 객체는 객체 생성시 지정된 구문만 실행 가능 (재사용 불가능)
• SQL구문에서 like 키워드를 사용할 경우 PreparedStatement 사용 불가능
• PreparedStatement : 변수가 들어갈 자리는 ?로 표시 / 대응되는 값 지정 시 set~(setString, setInt, ..)메소드 사용
  
  
  

🔎 Quiz ①) 이클립스에서 입력받고 출력하기

• MySQL DB구현

create database booksStore;
use booksStore;

create table list(
num		int,
bookCode	char(10)	primary key,
writer		char(10),
stock		int
);

insert into list values
(2, 'R23A', '전현무', 60),
(3, 'D095', '전소민', 9);

select * from list;

• Eclipse Class구현

package pack_Quiz;

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

public class Insert {
	
	public static void main(String[] args) {
		
		Scanner scanner = new Scanner(System.in);
		
		System.out.print("번호 입력 : ");
		int num = scanner.nextInt();
		
		System.out.print("책코드 입력 : ");
		String bookCode = scanner.next();
		
		System.out.print("저자 입력 : ");
		String writer = scanner.next();
		
		System.out.print("재고량 입력 : ");
		int stock = scanner.nextInt();
		
		scanner.close();
		
		
		
		Connection		objConn	 = null;
		PreparedStatement	objPstmt = null; 
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");		
			
			String url ="jdbc:mysql://127.0.0.1:3308/booksStore?";
			url += "useSSL=false&";
			url += "serverTimezone=Asia/Seoul&";
			url += "useUnicode=true&";
			url += "characterEncoding=UTF-8";
			String uid = "root";
			String upw = "1234";
			
			objConn = DriverManager.getConnection(url, uid, upw);
			
			String sql = "insert into list values (?, ?, ?, ?)";
			objPstmt = objConn.prepareStatement(sql);
			objPstmt.setInt(1, num);
			objPstmt.setString(2, bookCode);
			objPstmt.setString(3,writer);
			objPstmt.setInt(4, stock);
			
			int result = objPstmt.executeUpdate();
			
			if (result > 0) { // or "if(result==1)"
				System.out.println("입력이 완료되었습니다. \n프로그램이 종료되었습니다.");
			} else {
				System.out.println("오류가 발생하여 미입력되었습니다.");
			}
			
			objPstmt.close();
			objConn.close();
		
			
		} catch (ClassNotFoundException e) {
			System.out.println("ClassNotFound : " + e.getMessage());
		} catch (SQLException e) {
			System.out.println("SQLException : " + e.getMessage());
	}
	}
}

→ 다시 workbench에서 테이블 조회

🔎 Quiz ②) ① 데이터 역순으로 출력하기

package pack_Quiz;

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

public class Select {

	public static void main(String[] args) {
		
		Connection 	objConnection 	= null;
		Statement 	objStatement	= null;
		ResultSet 	objResultSet 	= null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost:3308/booksStore?";
			url += "useSSL=false&";
			url += "serverTimezone=Asia/Seoul&";
			url += "useUnicode=true&";
			url += "characterEncoding=UTF-8";
			String uid = "root";
			String upw = "1234";
			
			objConnection = DriverManager.getConnection(url, uid, upw);
		
			objStatement = objConnection.createStatement();
			String sql = "select * from list order by num desc";	
			objResultSet = objStatement.executeQuery(sql);
			
				 
		        System.out.println("번호	코드	저자	재고량");
		        System.out.println("------------------------------");
		        	
		        while (objResultSet.next()) {
		        		
		        	System.out.print(objResultSet.getInt("num") + "      ");
		        	System.out.print(objResultSet.getString("bookCode") + "      ");
		        	System.out.print(objResultSet.getString("writer") + "      ");
		        	System.out.println(objResultSet.getInt("stock") + "      ");
		       	}
	
				
				objResultSet.close();
				objStatement.close();
				objConnection.close();
				
			} catch (Exception e) {
				
				System.out.println(e.getMessage());
			}
			
	}

}