Java 8일차 - java + MySQL연동 / INSERT/ SELECT

MySQL연동/ INSERT (Statement사용)

//DBConnect.java
package net.bit.day26;

import java.sql.*;
import java.util.Scanner;

public class DBConnect {
	//mysql 연결
	public static Connection getConnection() {
		String url = "jdbc:mysql://localhost/naver?serverTimezone=Asia/Seoul";
		//jdbc:mysql://localhost/naver(데이터베이스명기입)
		Connection conn = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			System.out.println("db연결중");
			conn = DriverManager.getConnection(url, "root", "1234");
			//DriverManager.getConnection(url, 아이디, 비밀번호);
			System.out.println("db연결 성공");
		}catch(ClassNotFoundException e) { 
			System.out.println("db못찾음");
		}catch(SQLException e) {
			System.out.println("db연결 실패");
		}
		return conn;
	}
	
	//mysql 실행 INSERT
	public static void main(String[] args) throws SQLException {
		Connection conn = getConnection();
		Statement stmt = conn.createStatement();
		
		Scanner sc = new Scanner(System.in);
		System.out.print("학생이름 > ");
		String name = sc.nextLine();
		System.out.print("국어점수 > ");
		int kor = (int)sc.nextInt();
		System.out.print("영어점수 > ");
		int eng = (int)sc.nextInt();
		System.out.print("수학점수 > ");
		int mat = (int)sc.nextInt();
		String sql = "INSERT INTO student (name, kor, eng, mat) VALUES ('"+name+"', '"+kor+"', '"+eng+"', '"+mat+"')";
//		
		//String sql = "insert into student (name, kor, eng, mat) values('홍길동', '90', '80', '85')";
		if(stmt.executeUpdate(sql) == 1) {
			System.out.println("학생추가 성공");
		}else {
			System.out.println("학생추가 실패");
		}
		
		conn.close();
		stmt.close();
	}
	
}

[입출력]
db연결중
db연결 성공
학생이름 > 김철수
국어점수 > 90
영어점수 > 80
수학점수 > 85
학생추가 성공

 

 

 

MySQL연동 / INSERT (PreparedStatement사용)

//DBConnect2.java
package net.bit.day26;

import java.sql.*;
import java.util.Scanner;

public class DBConnect2 {
	//mysql 연결
	public static Connection getConnection() {
		String url = "jdbc:mysql://localhost/naver?serverTimezone=Asia/Seoul";
		Connection conn = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			System.out.println("db연결중");
			conn = DriverManager.getConnection(url, "root", "1234");
			System.out.println("db연결 성공");
		}catch(ClassNotFoundException e) { 
			System.out.println("db못찾음");
		}catch(SQLException e) {
			System.out.println("db연결 실패");
		}
		return conn;
	}
	
	//mysql 실행 INSERT
	public static void main(String[] args) throws SQLException {
		Connection conn = getConnection();
		Scanner sc = new Scanner(System.in);
		System.out.print("학생이름 > ");
		String name = sc.nextLine();
		System.out.print("국어점수 > ");
		int kor = (int)sc.nextInt();
		System.out.print("영어점수 > ");
		int eng = (int)sc.nextInt();
		System.out.print("수학점수 > ");
		int mat = (int)sc.nextInt();
		
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO student (name, kor, eng, mat)");
		sql.append("VALUES (?, ?, ?, ?)");
		PreparedStatement pstmt = conn.prepareStatement(sql.toString());
		pstmt.setString(1, name);
		pstmt.setInt(2, kor);
		pstmt.setInt(3, eng);
		pstmt.setInt(4, mat);
		pstmt.execute();
	}
	
}

[입출력]
db연결중
db연결 성공
학생이름 > 김철수
국어점수 > 90
영어점수 > 80
수학점수 > 85

 

 

 

MySQL연동 / SELECT (PreparedStatement사용)

//DBConnect3.java
package net.bit.day26;

import java.sql.*;
import java.util.Scanner;

public class DBConnect3 {
	//mysql 연결
	public static Connection getConnection() {
		String url = "jdbc:mysql://localhost/naver?serverTimezone=Asia/Seoul";
		Connection conn = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			System.out.println("db연결중...");
			conn = DriverManager.getConnection(url, "root", "1234");
			System.out.println("db연결 성공");
		}catch(ClassNotFoundException e) { 
			System.out.println("db못찾음");
		}catch(SQLException e) {
			System.out.println("db연결 실패");
		}
		return conn;
	}
	
	//mysql 실행 SELECT
	public static void main(String[] args) throws SQLException {
		Connection conn = getConnection();
		String sql = "SELECT * FROM student";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		while(rs.next()) {
			System.out.print("이름 : " + rs.getString("name") + "\t");
			System.out.print("국어점수 : " + rs.getString("kor") + "\t");
			System.out.print("영어점수 : " + rs.getString("eng") + "\t");
			System.out.print("수학점수 : " + rs.getString("mat") + "\n");
		}
		
	}
	
}