목차
CRUD 실습하기
- TABLE
CREATE TABLE jdbc_example (
id NUMBER PRIMARY KEY,
name VARCHAR2(32)
);1. CREATE
1.1. INSERT
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Application {
public static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
public static final String DB_USER = "system";
public static final String DB_PW = "oracle";
public static void main(String[] args) throws ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");
try(final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PW))
{
final int id = 1;
final String name = "공유";
final String sql = "INSERT INTO jdbc_example VALUES(?, ?)";
final PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.setString(2, name);
final int affectedRows = pstmt.executeUpdate(); // 업데이트 된 갯수
System.out.println("Update : " + affectedRows);
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}1.1.1. sql
2. READ
2.1. SELECT01
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Application {
public static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
public static final String DB_USER = "system";
public static final String DB_PW = "oracle";
public static void main(String[] args) throws ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");
try(final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PW))
{
final String sql = "SELECT * FROM jdbc_example";
final PreparedStatement pstmt = conn.prepareStatement(sql);
final ResultSet rs = pstmt.executeQuery();
rs.next();
final int id = rs.getInt(1); // sql index값은 1부터 시작한다.
final String name = rs.getString(2);
System.out.println(id);
System.out.println(name);
rs.close();
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
2.1.1. sql
2.2. SELECT02
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Application4 {
public static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
public static final String DB_USER = "system";
public static final String DB_PW = "oracle";
public static void main(String[] args) throws ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");
try(final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PW))
{
final String sql = "SELECT * FROM jdbc_example";
final PreparedStatement pstmt = conn.prepareStatement(sql);
final ResultSet rs = pstmt.executeQuery();
final List<Jdbc_Example> list = new ArrayList<>();
while(rs.next()) {
final Jdbc_Example jdbcExample = read(rs);
list.add(jdbcExample);
}
for (Jdbc_Example a : list) {
System.out.println(a);
}
rs.close();
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
private static Jdbc_Example read(ResultSet rs) throws SQLException{
final long id = rs.getLong("id");
final String name = rs.getString("name");
return new Jdbc_Example(id, name);
}
}
2.2.2 eclipse
3. UPDATE
3.1. UPDATE
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Application5 {
public static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
public static final String DB_USER = "system";
public static final String DB_PW = "oracle";
public static void main(String[] args) throws ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");
try(final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PW))
{
final String sql = "UPDATE jdbc_example SET name = ? WHERE id = 1";
final PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "신민아"); //인덱스거 1번인 공유에서 -> 신민아로 변경
final int affectedRows = pstmt.executeUpdate(); //몇개가 업데이트 되었는지
System.out.println("Update : " + affectedRows);
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}3.1. sql
4. DELETE
4.1. DELETE
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Application5 {
public static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
public static final String DB_USER = "system";
public static final String DB_PW = "oracle";
public static void main(String[] args) throws ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");
try(final Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PW))
{
final String sql = "DELETE FROM jdbc_example WHERE id = 3";
// id가 3인 사람의 row를 삭제하는 구문
final PreparedStatement pstmt = conn.prepareStatement(sql);
final int affectedRows = pstmt.executeUpdate(); //몇개가 업데이트 되었는지
System.out.println("Update : " + affectedRows);
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}4.1.1 sql
Reference
- 🎈2020.11.15
- 🎈정리 : VScode
- 🎈실습 : Eclipse, SQL Developer
안녕하세요:)