무결성 제약조건 |
역할 |
NOUT NULL |
NULL을 허용하지 않음 |
UNIQUE |
중복된 값을 허용하지 않음 |
PRIMARY KEY |
NULL 중복된 값을 허용하지 않음 |
FOREIGN KEY |
참조되는 테이블의 컬럼의 값이 존재 하면 허용 |
CHECK, DEFAULT |
저장 가능한 데이터의 범위나 조건을 지정 |
****************************************************************
테이블 만들기
create table teach(
ID number(3) primary key,
pw number(3) not null,
name varchar2(10) not null,
class number(2),
subject varchar2(20),
age number(3),
gender char(10),
experience number(3));
확인
select * from teach
행추가
insert into TEACH
values(1,123,'Kim',1,'math',40,'male',30);
****************************************************************
SQL Scrapbook
create table teach(
ID number(3) primary key,
pw number(3) not null,
name varchar2(10) not null,
class number(2),
subject varchar2(20),
age number(3),
gender char(10),
experience number(3));
select * from teach
insert into TEACH
values(10,123,'Yeom',10,'Act',32,'male',3);
create table prod(
keynumber number(3) primary key,
name varchar2(20) not null,
price number(5) not null,
stock number(3));
select*from prod
*******************************************************************
<내코드>
#메인
package java_0811;
import java.util.Scanner;
public class DbTest {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
SelectTeacher sct = new SelectTeacher();
while(true){
System.out.println("메뉴를 선택하세요.");
System.out.println("1.전체조회 2.선택조회 3.입력 4.수정 5.종료");
int menu = sc.nextInt();
if(menu==1){
sct.selectAll();
}else if(menu==2){
sct.findOne();
}else if(menu==3){
}else if(menu==3){
}else if(menu==3){
}else{
break;
}
}
}
}
#검색
package java_0811;
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 SelectTeacher {
Scanner sc = new Scanner(System.in);
public void selectAll(){
String id = "user01";
String pw = "user01";
String url = "jdbc:oracle:thin:@192.168.116.128:1521:xe";
String driver = "oracle.jdbc.driver.OracleDriver";
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
Class.forName(driver);
System.out.println("드라이버 로딩성공");
con = DriverManager.getConnection(url, id, pw);
System.out.println("데이터베이스 접속성공");
st = con.createStatement();
String sql = "select*from teach";
rs = st.executeQuery(sql);
while(rs.next()){
int sid = rs.getInt("ID");
int spw = rs.getInt("PW");
String sname = rs.getString("NAME");
int sclass = rs.getInt("CLASS");
String ssubject = rs.getString("SUBJECT");
int sage = rs.getShort("AGE");
String sgender = rs.getString("GENDER");
int sexp = rs.getInt("EXPERIENCE");
System.out.println("ID번호 :" +sid);
System.out.println("PW번호 :"+spw);
System.out.println("이름 :"+sname);
System.out.println("담임 :"+sclass+"반");
System.out.println("과목 :"+ssubject);
System.out.println("나이 :"+sage);
System.out.println("성별 :"+sgender);
System.out.println("경력 :"+sexp+"년");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
rs.close();
st.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void findOne(){
String id = "user01";
String pw = "user01";
String url = "jdbc:oracle:thin:@192.168.116.128:1521:xe";
String driver = "oracle.jdbc.driver.OracleDriver";
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
Class.forName(driver);
System.out.println("드라이버 로딩성공");
con = DriverManager.getConnection(url, id, pw);
System.out.println("데이터베이스 접속성공");
st = con.createStatement();
System.out.println("찾은 ID를 입력하세요.");
String wid = sc.next();
String sql = "select*from teach where ID="+"'"+wid+"'";
rs = st.executeQuery(sql);
if(rs.next()){
int sid = rs.getInt("ID");
int spw = rs.getInt("PW");
String sname = rs.getString("NAME");
int sclass = rs.getInt("CLASS");
String ssubject = rs.getString("SUBJECT");
int sage = rs.getShort("AGE");
String sgender = rs.getString("GENDER");
int sexp = rs.getInt("EXPERIENCE");
System.out.println("ID번호 :" +sid);
System.out.println("PW번호 :"+spw);
System.out.println("이름 :"+sname);
System.out.println("담임 :"+sclass+"반");
System.out.println("과목 :"+ssubject);
System.out.println("나이 :"+sage);
System.out.println("성별 :"+sgender);
System.out.println("경력 :"+sexp+"년");
}else{
System.out.println("해당 ID가 없습니다.");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
rs.close();
st.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
<같은것>
package java_0811;
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 SelectTeacher {
Scanner sc = new Scanner(System.in);
String id = "user01";
String pw = "user01";
String url = "jdbc:oracle:thin:@192.168.116.128:1521:xe";
String driver = "oracle.jdbc.driver.OracleDriver";
Connection con = null;
Statement st = null;
ResultSet rs = null;
public void selectAll(){
try {
Class.forName(driver);
System.out.println("드라이버 로딩성공");
con = DriverManager.getConnection(url, id, pw);
System.out.println("데이터베이스 접속성공");
st = con.createStatement();
String sql = "select*from teach";
rs = st.executeQuery(sql);
while(rs.next()){
int sid = rs.getInt("ID");
int spw = rs.getInt("PW");
String sname = rs.getString("NAME");
int sclass = rs.getInt("CLASS");
String ssubject = rs.getString("SUBJECT");
int sage = rs.getShort("AGE");
String sgender = rs.getString("GENDER");
int sexp = rs.getInt("EXPERIENCE");
System.out.println("ID번호 :" +sid);
System.out.println("PW번호 :"+spw);
System.out.println("이름 :"+sname);
System.out.println("담임 :"+sclass+"반");
System.out.println("과목 :"+ssubject);
System.out.println("나이 :"+sage);
System.out.println("성별 :"+sgender);
System.out.println("경력 :"+sexp+"년");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
rs.close();
st.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void findOne(){
try {
Class.forName(driver);
System.out.println("드라이버 로딩성공");
con = DriverManager.getConnection(url, id, pw);
System.out.println("데이터베이스 접속성공");
st = con.createStatement();
System.out.println("찾은 ID를 입력하세요.");
String wid = sc.next();
String sql = "select*from teach where ID="+"'"+wid+"'";
rs = st.executeQuery(sql);
if(rs.next()){
int sid = rs.getInt("ID");
int spw = rs.getInt("PW");
String sname = rs.getString("NAME");
int sclass = rs.getInt("CLASS");
String ssubject = rs.getString("SUBJECT");
int sage = rs.getShort("AGE");
String sgender = rs.getString("GENDER");
int sexp = rs.getInt("EXPERIENCE");
System.out.println("ID번호 :" +sid);
System.out.println("PW번호 :"+spw);
System.out.println("이름 :"+sname);
System.out.println("담임 :"+sclass+"반");
System.out.println("과목 :"+ssubject);
System.out.println("나이 :"+sage);
System.out.println("성별 :"+sgender);
System.out.println("경력 :"+sexp+"년");
}else{
System.out.println("해당 ID가 없습니다.");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
rs.close();
st.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
**********************************************************
<선생님 코드>
#메인
public class DMain {
public static void main(String[] args) {
SelectTeacher st = new SelectTeacher();
st.selTeacher();
//st.selectAll();
}
}
#검색
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class SelectTeacher {
String id="user01";
String pw="user01";
String url="jdbc:oracle:thin:@192.168.40.128:1521:xe";
String driver="oracle.jdbc.driver.OracleDriver";
Connection con;
public SelectTeacher() {
try {
Class.forName(driver);
con = DriverManager.getConnection(url, id, pw);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void selectAll(){
ResultSet rs=null;
try {
Statement st = con.createStatement();
String sql="select * from teachers";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("id"));
System.out.println(rs.getString("password"));
System.out.println(rs.getString("name"));
System.out.println(rs.getString("gender"));
System.out.println(rs.getInt("age"));
System.out.println(rs.getString("subject"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void selTeacher(){
ResultSet rs=null;
try {
String sql = "select * from teachers where id=?";
//Statement st = con.createStatement();
PreparedStatement st = con.prepareStatement(sql);
Scanner sc = new Scanner(System.in);
System.out.println("찾을 ID를 입력하세요");
String idd = sc.next();
st.setString(1, idd);
rs = st.executeQuery();
if(rs.next()){
System.out.println(rs.getString("id"));
System.out.println(rs.getString("password"));
System.out.println(rs.getString("name"));
System.out.println(rs.getString("gender"));
System.out.println(rs.getInt("age"));
System.out.println(rs.getString("subject"));
}else{
System.out.println("그런 사람 없다");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
PrepareStatement 사용법 익히기
?사용법. 그리고 preparestatement에서 인덱스번호는 1번부터 시작
프리페어드 사용법
con = DriverManager.getConnection(url,id,pw);
String sql = " " ;
PreparedStatement st = con.preparedStatement(sql);
st.setString(1,"id");
st.executeQuery();
******************************************************
PreparedStatement 사용해서 insert문 써보기
[내가짠거]
package java_0811;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class InsertTeacher {
Scanner sc = new Scanner(System.in);
public void insert(){
String id = "user01";
String pw = "user01";
String url = "jdbc:oracle:thin:@192.168.116.128:1521:xe";
String driver = "oracle.jdbc.driver.OracleDriver";
Connection con = null;
PreparedStatement st = null;
try {
Class.forName(driver);
System.out.println("드라이버 로딩성공");
con = DriverManager.getConnection(url, id, pw);
System.out.println("데이터베이스 접속성공");
String sql = "insert into teach values(?,?,?,?,?,?,?,?)";
st = con.prepareStatement(sql);
System.out.println("선생님을 등록합니다.");
System.out.println("ID번호를 입력하세요");
String iid = sc.next();
st.setString(1, iid);
System.out.println("PW번호를 입력하세요.");
String ipw = sc.next();
st.setString(2, ipw);
System.out.println("추가할 이름 입력");
String iname = sc.next();
st.setString(3, iname);
System.out.println("반번호 입력");
String iclass = sc.next();
st.setString(4, iclass);
System.out.println("과목이름 입력");
String isub = sc.next();
st.setString(5, isub);
System.out.println("나이 입력");
String iage = sc.next();
st.setString(6, iage);
System.out.println("성별입력");
String igender = sc.next();
st.setString(7, igender);
System.out.println("경력입력");
String iexp = sc.next();
st.setString(8, iexp);
st.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
st.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
******************************************************************
ID 중복검사 기능 추가
[쌤코드]
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 InsertTeacher {
public void insertTest(){
String id="user01";
String pw="user01";
String url="jdbc:oracle:thin:@192.168.40.128:1521:xe";
String driver="oracle.jdbc.driver.OracleDriver";
Scanner sc = new Scanner(System.in);
Connection con;
PreparedStatement st=null;
ResultSet rs=null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, id, pw);
//DB 연결 끝
System.out.println("id를 입력하세요");
String yId= sc.next();
String sql ="select * from teachers where id=?";
st = con.prepareStatement(sql);
st.setString(1, yId);
rs = st.executeQuery();
if(rs.next()){
System.out.println("아이디가 있다.");
}else{
System.out.println("사용 가능한 아이디 입니다.");
System.out.println("비번을 입력하세요");
String ypw = sc.next();
System.out.println("이름을 입력하세요");
String name = sc.next();
System.out.println("성별을 입력하세요");
String gender = sc.next();
System.out.println("나이를 입력하세요");
int age = sc.nextInt();
System.out.println("과목을 입력하세요");
String subject = sc.next();
rs.close();
st.close();
sql ="insert into teachers values(?,?,?,?,?,?)";
st = con.prepareStatement(sql);
st.setString(1, yId);
st.setString(2, ypw);
st.setString(3, name);
st.setString(4, gender);
st.setInt(5, age);
st.setString(6, subject);
st.executeUpdate();
System.out.println("회원가입에 성공했다");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
//rs.close();
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
***쌤거 보고 동일하게 한거
package java_0811;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class InsertTeacher {
Scanner sc = new Scanner(System.in);
public void insert(){
String id = "user01";
String pw = "user01";
String url = "jdbc:oracle:thin:@192.168.116.128:1521:xe";
String driver = "oracle.jdbc.driver.OracleDriver";
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
Class.forName(driver);
System.out.println("드라이버 로딩성공");
con = DriverManager.getConnection(url, id, pw);
System.out.println("데이터베이스 접속성공");
System.out.println("선생님을 등록합니다.");
System.out.println("ID번호를 입력하세요");
String iid = sc.next();
String sql = "select*from teach where id=?";
st = con.prepareStatement(sql);
st.setString(1, iid);
rs = st.executeQuery();
if(rs.next()){
System.out.println("이미 존재하는 ID입니다.");
}else{
System.out.println("사용가능한 아이디 입니다.");
System.out.println("PW번호를 입력하세요.");
String ipw = sc.next();
System.out.println("추가할 이름 입력");
String iname = sc.next();
System.out.println("반번호 입력");
String iclass = sc.next();
System.out.println("과목이름 입력");
String isub = sc.next();
System.out.println("나이 입력");
String iage = sc.next();
System.out.println("성별입력");
String igender = sc.next();
System.out.println("경력입력");
String iexp = sc.next();
rs.close();
st.close();
sql = "insert into teach values(?,?,?,?,?,?,?,?)";
st = con.prepareStatement(sql);
st.setString(1, iid);
st.setString(2, ipw);
st.setString(3, iname);
st.setString(4, iclass);
st.setString(5, isub);
st.setString(6, iage);
st.setString(7, igender);
st.setString(8, iexp);
st.executeUpdate();
System.out.println("등록 성공");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
*******************************************************
로그인기능 구현하기
선생님거 보고 만든거
package java_0811;
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 LogDb {
public void log(){
String id = "user01";
String pw = "user01";
String url = "jdbc:oracle:thin:@192.168.116.128:1521:xe";
String driver = "oracle.jdbc.driver.OracleDriver";
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
Scanner sc = new Scanner(System.in);
try {
Class.forName(driver);
con = DriverManager.getConnection(url, id, pw);
System.out.println("아이디를 입력하세요.");
String lid = sc.next();
System.out.println("비밀번호를 입력하세요.");
String lpw = sc.next();
String sql = "select*from teach where id=? and pw=?";
st = con.prepareStatement(sql);
st.setString(1, lid);
st.setString(2, lpw);
rs = st.executeQuery();
if(rs.next()){
System.out.println("로그인성공");
}else{
System.out.println("로그인실패");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
메인클래스
package java_0811;
import java.util.Scanner;
public class DbTest {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
SelectTeacher sct = new SelectTeacher();
InsertTeacher ict = new InsertTeacher();
LogDb lg = new LogDb();
while(true){
System.out.println("메뉴를 선택하세요.");
System.out.println("1.전체조회 2.선택조회 3.등록 4.수정 5.로그인 6.종료");
int menu = sc.nextInt();
if(menu==1){
sct.selectAll();
}else if(menu==2){
sct.findOne();
}else if(menu==3){
ict.insert();
sct.selectAll();
}else if(menu==4){
}else if(menu==5){
lg.log();
}else{
break;
}
}
}
}
**************************************************************
제품 데이터베이스 만들고
구매하기 구현
제품번호
제품이름
제품가격
재고수량
