Question

write a Java program that interfaces with a database using JDBC (Java Database Connectivity). Your program should operate on the following database with two relations:

person (pid:integer, name:string, age:integer)

likes(pid:integer, mid:integer) The person relation gives information about a person. The likes relation gives information about which person likes what other person. That is, if (100,200) is a tuple in likes then this shows that the person with pid 100 likes the person with pid 200. (Note this does not imply that 200 likes 100). Observe that, pid is not a key of the likes relation. The attributes pid and mid in person are both foreign keys referencing pid in person.

Can you please help me how to write a SQL query using Java to retrieve the following information?

Output the average age of persons liked, directly or indirectly, by the person with given pid. Example: 5 50 If the transaction code is 5, then you have to output the average age of all persons that are directly or indirectly liked by the person with the given pid. If the average age is not an integer, round it to the closest integer.

Answer 1

 So, the tables will look like: person:  likes: 1 likes 2, 3 and 3 likes 2  now suppose , you want to know the average age of persons who likes 2. 2 is liked by 1 and 3 average age of 1 and 2 = (20+40)/2 = 30 below sql will generate this , with user entry as 2: you will join person and likes and filter using mid value which the person liked by pid. the rounded average will produce your output. select mid,round(avg(age),0) as avgage from person a join likes b on a.pid = b.pid where mid = 2   below code using JDBC should be used. As i dont have access to your database, please use this as sample and update accordigly: //Step1: SQL and scanner packages import java.sql.*; import java.util.Scanner; public class JDBCClass { // Step2: set the param,eter for JDBC driver name and database path, user and password to //access the DB. //Given example for mysql for a database database_name static final String JDBCDRIVER = "com.mysql.jdbc.Driver"; static final String DB = "jdbc:mysql://localhost/database_name"; static final String USER = "username"; static final String PASS = "password"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ //Step 3: register the JDBC driver Class.forName("com.mysql.jdbc.Driver"); //Step 4: Open connection System.out.println("Connecting to the database..."); conn = DriverManager.getConnection(DB, USER, PASS); System.out.println("Connection successful"); //Step 5: get the data i.e. person who is liked from user and execute a query     Scanner sc = new Scanner(System.in);     System.out.println("Enter the person id for whom the average likes you need:");   int m_id = sc.nextInt(); //Step 6: SQL statement create stmt = conn.createStatement(); //Step 7: create sql select query     String sql = "select mid,round(avg(age),0) as avgage from person a join likes b on a.pid = b.pid where mid= " + m_id;  //STEP 8: data from result set ResultSet r_s = stmt.executeQuery(sql); while(r_s.next()){ //get the column name int mid = r_s.getInt("mid"); int avg_age = r_s.getInt("avgage"); //print System.out.print("Person ID who is liked: " + mid); System.out.print(", Average Age: " + avg_age); } r_s.close(); }catch(SQLException se){ //exception handling for JDBC se.printStackTrace(); }catch(Exception e){ // exception handling for driver class Class.forName e.printStackTrace(); }finally{ //finally block is needed to close resources try{ if(stmt!=null) conn.close(); }catch(SQLException se){ }// do nothing try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); }//end finally try }//end try System.out.println("Operations complete..Bye!"); }//end main here }//end JDBCClass here