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());
}
}
}
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;
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에서 테이블 조회
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());
}
}
}