[java] DB CRUD

driver = org.mariadb.jdbc.Driver
url = jdbc:mysql://localhost:3306/test
user = root
password = 369369

package pack1;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class Dbtest2 { //중요 정보 별도 파일로 작성후 읽기 + CRUD (insert, select, update, delete)
	private Connection conn;	
	private Statement stmt;		
	private ResultSet rs1, rs2;
	Properties properties = new Properties();
	
	public Dbtest2() {
		try {								//  \\ (윈도우 방식)= / (리눅스 방식)
			properties.load(new FileInputStream("D:\\work\\jsou\\java_pro4db\\src\\pack1\\test.properties"));//절대경로로 찍어야 함
			
			Class.forName(properties.getProperty("driver"));
			conn = DriverManager.getConnection(
					properties.getProperty("url"),
					properties.getProperty("user"),
					properties.getProperty("password"));
			
			stmt = conn.createStatement();
			
			String sql = "";
			/*
			//INSERT
			sql = "INSERT INTO sangdata VALUES(5,'새우깡',2,1500)";
			int re = stmt.executeUpdate(sql);	//Select 이외의 SQL 문 (한번에 치환까지 실행 불가능해서 분리) 
			if(re == 1) System.out.println("추가 성공");
			
			//Transaction : 하나의 작업 단위. (Insert, Update, Delete... 후 commit/rollback(save point))
			//하지만 지금 기본으로는 auto-commit
			conn.setAutoCommit(false);	//Transaction 수동
			sql = "INSERT INTO sangdata VALUES(6,'감자깡',2,2500)";
			stmt.executeUpdate(sql);
//			conn.rollback();			//UPDATE 안됨
			conn.commit();
			conn.setAutoCommit(true);	//Transaction 자동 (수동 종료) */ //아니면 deadlock 다음 작업 처리 불가능 (wait) 상태
			
			/*
			//UPDATE (UPDATE & DELETE 는 입력한 수 만큼 return 값 반한)
			sql = "UPDATE sangdata SET sang = '허니칩', dan = 2100 where code=5";
			int re = stmt.executeUpdate(sql);
			if(re >= 1) System.out.println("수정 성공"); */
	
			//DELETE 
			sql = "DELETE FROM sangdata WHERE code = 5";
			int re = stmt.executeUpdate(sql);
			
			if(re >= 1) System.out.println("삭제 성공");
			else System.out.println("삭제 실패");
			
			
			//SELECT
			rs1 = stmt.executeQuery("SELECT * FROM sangdata ORDER BY code DESC");
			int cou = 0;
			while(rs1.next()) {
				System.out.println(
					rs1.getString("code") + " " +
					rs1.getString("sang") + " " +
					rs1.getString("su") + " " +
					rs1.getString("dan")
				);
				cou += 1;
			}
			System.out.println("건수 : " + cou);
			
		} catch (Exception e) {
			System.out.println("err: " + e);
		} finally {
			try {
				if (rs1 != null)
					rs1.close();
				if (rs2 != null)
					rs2.close();
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
				
			} catch (Exception e2) {
				
			}
		}
	}

	public static void main(String[] args) {
		new Dbtest2();
		
	}

}