package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class JDBCMysqlStatementTest {
public static void main(String[] args) {
Scanner sc= new Scanner(System.in);
Connection conn = null;
try {
// 1. 드라이버 로드
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Driver Load!");
// 2. DB 연결 localhost == 127.0.0.1
String jdbcUrl ="jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
String user = "hyo";
String password = "admin";
conn = DriverManager.getConnection(jdbcUrl, user, password);
System.out.println("데이터베이스에 접속했습니다!");
// 3. statement 인스턴스생성
Statement stmt = conn.createStatement();
System.out.println("부서 이름을 입력해주세요.");
String userDname= sc.nextLine();
System.out.println("부서의 위치를 입력해주세요.");
String userLoc = sc.nextLine();
// 입력 : insert
String sqlInsert = "insert into dept (deptno, dname, loc) values (80, '"+userDname+"','"+userLoc+"')";
int resultCnt = stmt.executeUpdate(sqlInsert); // sql문을 실행하고 횟수반환
if(resultCnt>0) {
System.out.println("정상적인 입력");
}
// 4. sql 실행 : 부서리스트 출력
String sql="select * from dept order by deptno";
ResultSet rs = stmt.executeQuery(sql);
// 5. ResultSet을 이용해서 결과 출력
while(rs.next()) { // rs.next(); // boolean Type
int deptno = rs.getInt("deptno");
String dname = rs.getString("dname");
String loc = rs.getString(3);
// System.out.println(deptno+"\t"+dname+"\t"+loc);
System.out.printf("%5s",deptno+"\t");
System.out.printf("%12s",dname+"\t");
System.out.printf("%12s",loc+"\n");
}
rs.close();
stmt.close();
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("Driver 로드 실패");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCMysqlConnectionTest {
public static void main(String[] args) {
Connection conn = null;
try {
// 1. 드라이버 로드
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Driver Load!");
// 2. DB 연결 localhost == 127.0.0.1
String jdbcUrl ="jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
String user = "hyo";
String password = "admin";
conn = DriverManager.getConnection(jdbcUrl, user, password);
System.out.println("데이터베이스에 접속했습니다.");
// ....
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("Driver 로드 실패");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class JDBCMysqlPreparedTest {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
Connection conn = null;
try {
// 1. 드라이버 로드
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Driver Load!");
// 2. DB 연결 localhost == 127.0.0.1
String jdbcUrl = "jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
String user = "hyo";
String password = "admin";
conn = DriverManager.getConnection(jdbcUrl, user, password);
System.out.println("데이터베이스에 접속했습니다.");
// Statement stmt = conn.createStatement();
System.out.println("부서 번호 입력해주세요.");
int userDeptno = sc.nextInt();
sc.nextLine();
System.out.println("부서 이름을 입력해주세요.");
String userDname = sc.nextLine();
System.out.println("부서의 위치를 입력해주세요.");
String userLoc = sc.nextLine();
// PreparedStatement 인스턴스 생성
String sqlInsert = "insert into dept values (?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sqlInsert);
pstmt.setInt(1, userDeptno);
pstmt.setString(2, userDname);
pstmt.setString(3, userLoc);
int resultCnt = pstmt.executeUpdate();
if (resultCnt > 0) {
System.out.println("정상적으로 입력되었음.");
} else {
System.out.println("데이터 입력이 되지 않았음.");
}
// 부서 리스트 출력
String sqlList = "select * from dept order by loc";
pstmt = conn.prepareStatement(sqlList);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.print(rs.getInt(1) + ", ");
System.out.print(rs.getString(2) + ", ");
System.out.print(rs.getString(3) + "\n");
}
rs.close();
pstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("Driver 로드 실패");
} catch (SQLException e) {
e.printStackTrace();
}
}
}