Day 42

Kim·2020년 10월 29일

Primary key (기본키)

Condition
Primary key is in a column without any other data(단일 칼럼에 존재해야함)
It should be unique. There is no overlap in the table.(유일해야함)
not null
One table has ONLY one primary key
How to create Primary key

column_Name data type PRIMARY KEY

or

CONSTRAINTS limit(제약조건명) PRIMARY KEY(column_Name,...)

Foreign key (외래키)

Condition
Reference table should be existed before creating foreign key. (Process : Reference table > foreign key)
The maximum number of columns in Foreign key is 32.
Multiple
foreign key data = primary key data
foreign key and primary key exist DIFFERENT TABLE
How to create Foreign key

CONSTRAINT 외래키명 FOREIGN KEY(column_name, ...)

REFERENCES reference table(refernce_table_name, ...)

Table

FK = PK
They have the same data
For data integrity
Data integrity is the maintenance of, and the assurance of, the accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data.

Link Java and DB

JDBC Driver

Set up JDBC Driver

1. Create Java Project file

2. JRE System Library => Alt + Enter

3. Click Installed JREs...

4. Select jre and click Edit btn

5. Add External JARs from driver

6. Apply and close

7. Check the JDBC driver works well

Enter this code

import java.sql.DriverManager;
import java.sql.SQLException;

String driver="oracle.jdbc.driver.OracleDriver";
		String url="jdbc:oracle:thin:@localhost:1521:orcl";
		String user="system";
		String password="human123";
		try {
			Class.forName(driver);
			DriverManager.getConnection(url,user,password);
			System.out.println("Oracle connection has been succeeded");
		}catch(ClassNotFoundException e) {
				System.out.println("JDBC Driver loading has been failed");
			} catch(SQLException e) {
				System.out.println("Oracle connection has been failed");
			}

8. Run the Java Application

`Select from` on Eclipse

Import these columns to Eclipse and use the code below

import java.sql.DriverManager;
import java.sql.SQLException;

String driver="oracle.jdbc.driver.OracleDriver";
		String url="jdbc:oracle:thin:@localhost:1521:orcl";
		String user="system";
		String password="human123";
		try {
			Class.forName(driver);
			DriverManager.getConnection(url,user,password);
			System.out.println("Oracle connection has been succeeded");
		}catch(ClassNotFoundException e) {
				System.out.println("JDBC Driver loading has been failed");
			} catch(SQLException e) {
				System.out.println("Oracle connection has been failed");
			}

1. Add a variable which contains `select` `from` statement into `try`

      String user="system";
      String password="human123";
      try {
         Class.forName(driver);
         Connection conn=DriverManager.getConnection(url,user,password);
         System.out.println("Oracle connection has succeeded");
         
//         String sql="select channel_id,channel_desc "+"from channels";
//         Statement stmt=conn.createStatement();
//         ResultSet rs=stmt.executeQuery(sql);
//         while(rs.next()) {
//            int e_id = rs.getInt("channel_id");
//            String e_name = rs.getString("channel_desc");
////            int m_id = rs.getInt("manager_id");
//            System.out.println(e_id+","+e_name);
//         }
//         rs.close();
//         stmt.close(); 
         
         String sql = "insert into menu values(?,?)";
         PreparedStatement ps=conn.prepareStatement(sql); //앞 Prepare'd' 뒤 prepare 구분 
         
         //메뉴이름과 가격 넣기
         
         System.out.println("메뉴 이름");
         Scanner s = new Scanner(System.in);
         Scanner t = new Scanner(System.in);//Scanner에 String과 int 둘 다 사용시 new Scanner 만들어서 구분 
         String name = s.nextLine();
         while(!name.equals("")) {
            
            System.out.println("가격");
            int price = t.nextInt();
            ps.setString(1,name);
            ps.setInt(2,price);
            ps.executeUpdate();
            System.out.println("메뉴 이름");
            name = s.nextLine();
            
         }
         System.out.println("입력완료");

         ps.close();
         conn.close();
      }catch(ClassNotFoundException e) {
            System.out.println("JDBC Driver loading has failed");
         } catch(SQLException e) {
            System.out.println("Oracle connection has failed");
         }
      
      
      }
   }

`Update` on Eclipse

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;  //ALWAYS import from java.sql

public class DBapp {

	public static void main(String[] args) {
		String driver="oracle.jdbc.driver.OracleDriver"; //included in connection~~
		String url="jdbc:oracle:thin:@localhost:1521:orcl"; //included in connection~~
		String user="system";
		String password="human123";
		try {
			Class.forName(driver);
			Connection conn=DriverManager.getConnection(url,user,password); //Connection = class, conn = instance variable
			System.out.println("Oracle connection has been succeeded");
			String sql="insert into person values (?,?,?,?)";
			PreparedStatement ps=conn.prepareStatement(sql);  //PrepareStatement, Preparestate  "d"!!!!
			ps.setNString(1, "Joel");
			ps.setNString(2, "M");
			ps.setNString(3, "55558888");
			ps.setNString(4, "Jeju");
			ps.executeUpdate();
            
            ps.close();
			conn.close();
            
		}catch(ClassNotFoundException e) {
				System.out.println("JDBC Driver loading has been failed");
			} catch(SQLException e) {
				System.out.println("Oracle connection has been failed");
			}
		}
	}

Not hard coding

import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;  //ALWAYS import from java.sql

public class DBapp {

	public static void main(String[] args) {
		String driver="oracle.jdbc.driver.OracleDriver"; //included in connection~~
		String url="jdbc:oracle:thin:@localhost:1521:orcl"; //included in connection~~
		String user="system";
		String password="human123";
		try {
			Class.forName(driver);
			Connection conn=DriverManager.getConnection(url,user,password); //Connection = class, conn = instance variable
			System.out.println("Oracle connection has been succeeded");
			String sql="insert into person values (?,?,?,?)";
			PreparedStatement ps=conn.prepareStatement(sql);  //PrepareStatement, Preparestate  "d"!!!!
			
			Scanner s = new Scanner(System.in);
			String name=s.nextLine();
			while(!name.equals("")) {
				String gender = s.nextLine();
				String mobile = s.nextLine();
				String city = s.nextLine();
				
				ps.setString(1, name);
				ps.setString(2, gender);
				ps.setString(3, mobile);
				ps.setString(4, city);
				
				ps.executeUpdate();
				name=s.nextLine();
			}
            ps.close();
			conn.close();
		}catch(ClassNotFoundException e) {
				System.out.println("JDBC Driver loading has been failed");
			} catch(SQLException e) {
				System.out.println("Oracle connection has been failed");
			}
		}
	}

Example 2.

Make simple menu program.
Create menu table which has name column and price column.
Then, get name and price from the user.

import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;  //ALWAYS import from java.sql

public class DBapp {

	public static void main(String[] args) {
		String driver="oracle.jdbc.driver.OracleDriver"; //included in connection~~
		String url="jdbc:oracle:thin:@localhost:1521:orcl"; //included in connection~~
		String user="system";
		String password="human123";
		
		try {
			Class.forName(driver);
			Connection conn=DriverManager.getConnection(url,user,password); //Connection = class, conn = instance variable
			System.out.println("Oracle connection has been succeeded");
			
			//sql_01 will be sent to DB
			String sql_01="insert into menu values (?,?)";
			PreparedStatement ps=conn.prepareStatement(sql_01);  //PrepareStatement, Preparestate  "d"!!!!
			
			//Get data from the user
			System.out.println("Enter a product name");
			Scanner s = new Scanner(System.in);
			Scanner s_02 = new Scanner(System.in);
			String name=s.nextLine();
			
			while(!name.equals("")) {
				System.out.println("Enter the price");
				int price = s_02.nextInt();
				
				ps.setString(1, name);
				ps.setInt(2, price);
				
				ps.executeUpdate();
				System.out.println("Enter a product name");
				name=s.nextLine();
			}
            ps.close();
			conn.close();
			
		}catch(ClassNotFoundException e) {
				System.out.println("JDBC Driver loading has been failed");
			} catch(SQLException e) {
				System.out.println("Oracle connection has been failed");
			}
		}
	}

Delete

import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;  //ALWAYS import from java.sql

public class DBapp {

	public static void main(String[] args) {
		String driver="oracle.jdbc.driver.OracleDriver"; //included in connection~~
		String url="jdbc:oracle:thin:@localhost:1521:orcl"; //included in connection~~
		String user="system";
		String password="human123";
		
		try {
			Class.forName(driver);
			Connection conn=DriverManager.getConnection(url,user,password); //Connection = class, conn = instance variable
			System.out.println("Oracle connection has been succeeded");
			
			//sql_01 will be sent to DB
			String sql_01="delete from menu where name=?";
			PreparedStatement ps=conn.prepareStatement(sql_01);  //PrepareStatement, Preparestate  "d"!!!!
			
			//Get data from the user
			Scanner s = new Scanner(System.in);
			String name=s.nextLine();
			
			while(!name.equals("")) {
				ps.setString(1, name);
				ps.executeUpdate();
				name=s.nextLine();
			}
		}catch(ClassNotFoundException e) {
				System.out.println("JDBC Driver loading has been failed");
			} catch(SQLException e) {
				System.out.println("Oracle connection has been failed");
			}
		}
	}

Menu system 만들기
1. Add menu 2. Delete 3. Edit 4. Show menu 0. Exit 이며,
드라이브 로드해서 switch문 사용

import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;  //ALWAYS import from java.sql

public class DBapp {

	public static void main(String[] args) {
		String driver="oracle.jdbc.driver.OracleDriver"; //included in connection~~
		String url="jdbc:oracle:thin:@localhost:1521:orcl"; //included in connection~~
		String user="system";
		String password="human123";
		
		try {
			Class.forName(driver);
			Connection conn=DriverManager.getConnection(url,user,password); //Connection = class, conn = instance variable
			System.out.println("Oracle connection has been succeeded");
			
			//all
			String sql, name;
			int price;
			Scanner s_01=new Scanner(System.in); //name
			Scanner s_02=new Scanner(System.in); //price
			
			System.out.println("1. Add menu 2. Delete 3. Edit 4. Show menu 0. Exit");
			Scanner s=new Scanner(System.in); //answer
			int answer = s.nextInt();
			
			while(answer!=0) {
				switch(answer) {
				//Insert
				case 1:
					//DB statement
					sql="insert into menu values (?,?)";
					PreparedStatement ps=conn.prepareStatement(sql);  //PrepareStatement, Preparestate  "d"!!!!
					
					System.out.println("Enter a product name");
					name = s_01.nextLine();
					
					while(!name.equals("")) {
						System.out.println("Enter the price");
						price = s_02.nextInt();
						
						ps.setString(1, name);
						ps.setInt(2, price);
						ps.executeUpdate();
						
						System.out.println("Enter a product name");
						name = s_01.nextLine();
					}
					ps.close();
					break;
				//Delete	
				case 2:
					//DB statement
					sql="delete from menu where name=?";
					PreparedStatement ps_01=conn.prepareStatement(sql);
					
					System.out.println("Which product you want to delete?");
					name = s_01.nextLine();
					
					while(!name.equals("")) {
						ps_01.setString(1, name);
						ps_01.executeUpdate();
						
						System.out.println("Which product you want to delete?");
						name=s_01.nextLine();
					}
					ps_01.close();
					break;
				case 3:
					//Update DB
					sql="update menu set name=?, price=? where name=?";
					PreparedStatement ps_02=conn.prepareStatement(sql);
					
					System.out.println("Which product you want to change?");
					name=s_01.nextLine();
					
					while(!name.equals("")) {
						ps_02.setString(3, name);
						
						System.out.println("Enter a new name");
						name=s_01.nextLine();
						System.out.println("Enter the new price");
						price = s_02.nextInt();
						
						ps_02.setString(1, name);
						ps_02.setInt(2, price);
						ps_02.executeUpdate();
						
						System.out.println("Which product you want to change?");
						name=s_01.nextLine();
					}
					ps_02.close();
					break;
				case 4:
					//DB statement
					sql="select name, price from menu";
					Statement stmt=conn.createStatement();
			        ResultSet rs=stmt.executeQuery(sql);
			        
					while(rs.next()) {
						String m_name = rs.getString("name");
						int m_price = rs.getInt("price");
						System.out.println(m_name+", "+m_price);
					}
					rs.close();
			        stmt.close(); 
				} // switch Fin
				System.out.println("1. Add menu 2. Delete 3. Edit 4. Show menu 0. Exit");
				answer = s.nextInt();  //default XXX
			} // while Fin
			conn.close();
			System.out.println("Have a good day");
		
		}catch(ClassNotFoundException e) {
				System.out.println("JDBC Driver loading has been failed");
			} catch(SQLException e) {
				System.out.println("Oracle connection has been failed");
			}
		}
	}

Kim

이전 포스트

10월7일 : switch문

다음 포스트

Day 42

Primary key (기본키)

Foreign key (외래키)

Table

Link Java and DB

JDBC Driver

Set up JDBC Driver

1. Create Java Project file

2. JRE System Library => Alt + Enter

3. Click Installed JREs...

4. Select jre and click Edit btn

5. Add External JARs from driver

6. Apply and close

7. Check the JDBC driver works well

8. Run the Java Application

`Select from` on Eclipse

1. Add a variable which contains `select` `from` statement into `try`

`Update` on Eclipse

Example 2.

10월7일 : switch문

NOV 02~03

0개의 댓글

Day 42

Primary key (기본키)

Foreign key (외래키)

Table

Link Java and DB

JDBC Driver

Set up JDBC Driver

1. Create Java Project file

2. JRE System Library => Alt + Enter

3. Click Installed JREs...

4. Select jre and click Edit btn

5. Add External JARs from driver

6. Apply and close

7. Check the JDBC driver works well

8. Run the Java Application

Select from on Eclipse

1. Add a variable which contains select from statement into try

Update on Eclipse

Example 2.

10월7일 : switch문

NOV 02~03

0개의 댓글

`Select from` on Eclipse

1. Add a variable which contains `select` `from` statement into `try`

`Update` on Eclipse