Java SQL

Java SQL

---

Connecting to the Database:

	try{
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@classdb.jccc.edu:1521/cs260", "USERNAME", 
				"PASSWORD");
		System.out.println("Connected");
	}
	catch(Exception e){
		e.printStackTrace();
	}

Basic SQL Query

Statement myStatement= conn.createStatement();
ResultSet rs = myStatement.executeQuery("Select course_no From student.course");

      try{
	  Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@classdb.jccc.edu:1521/cs260", "USER", 
				"PASSWORD");
			
		Statement statement = conn.createStatement();
		ResultSet rs = statement.executeQuery("Select Course_no From Student.Course");
	
		while(rs.next()){
			int courseNo = rs.getInt("course_No");
			System.out.println("Course Number:" + courseNo);
				
		}

	}
	catch(Exception e){
		e.printStackTrace();
	}

• Can use the column index, or column name to get the value.

			
		ResultSet rs1 = statement1.executeQuery("Select course_no From Student.Course");
			
		// get result
		while(rs1.next()) {
			int courseNo = rs1.getInt("course_no");
			System.out.println("Course Number: " + courseNo);			
      }
		
		ResultSet rs2 = statement1.executeQuery("Select course_no From Student.Course");
		while(rs2.next()) {
				int courseNo = rs2.getInt(1);
				System.out.println("Course Number: " + courseNo);
			}

Soundex

			ResultSet rs = myStatement.executeQuery("select name from employee_copy where soundex(name) = soundex('Merry')");
	
			while(rs.next()){
				++count;
				name = rs.getString(1);
				String DeleteStatement1 = "Delete from employee_copy " +  "Where name = '" + name + "'";
				myStatement2.executeUpdate(DeleteStatement1);
			}
			System.out.println("records deleted: " + count);

---

Update

• Count number of rows affected

		
	Statement statementUpdate = conn.createStatement();
			
	String insertState = "Insert into Employee(EMPLOYEE_ID,NAME,SALARY,TITLE) " + "VALUES ('333', 
                                                                    'BOB', '3000', 'SINGER')";
			
	int count = statementUpdate.executeUpdate(insertState);
    System.out.println(count);