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構文を実行するオブジェクト
  • Prepared Statementオブジェクト再利用可能/statementオブジェクトオブジェクト作成時のみ指定された構文(再利用不可)
  • を実行する.
  • SQL構文でlikeキーが使用されている場合、予備ステータスは
  • で使用できません.
  • PreparedStatement:変数の位置は?設定時設定~(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());
    	}
    	}
    }
    

    →テーブルで再度テーブルを表示する

    🔎 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());
    			}
    			
    	}
    
    }