full_stack_assignment1

数据库+Java

package com.csu.marden;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class JabberServer {
	public static void main(String[] args) {
		JabberServer js=new JabberServer();
		//测试1
//		ArrayList<String> list=js.getFollowerUserIDs(1);
//		System.out.println(list);
		
		//测试2
//		ArrayList<String> list=js.getFollowingUserIDs(1);
//		System.out.println(list);
		
		//测试3
//		ArrayList<ArrayList<String>> list=js.getLikesOfUser(1);
//		System.out.println(list);
		
		//测试4
//		ArrayList<ArrayList<String>>  list= js.getTimelineOfUser(0);
//		System.out.println(list);
		
		//测试5
//		ArrayList<ArrayList<Integer>> list=js.getMutualFollowUserIDs();
//		System.out.println(list);
		
		//测试6
//		js.addUser(14,"marden","969626066@qq.com");
		
		//测试7
//		js.addJab(13,"marden","我是中国人");
		
		//测试8
//		js.addFollower(0,2);
		
		//测试9
//		js.addLike(1, 3);
		
		//测试10
//		 ArrayList<String>  list=js.getUsersWithMostFollowers();
//		System.out.println(list);
	}
	
	
	//1.用户userid被哪些用户关注
	public    ArrayList<String>    getFollowerUserIDs(int    userid){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		ArrayList<String> list=new ArrayList<>();
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			String sql="select useridA from follows where useridB=?";
			
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs=ps.executeQuery();
			
			while(rs.next()){
				list.add(rs.getInt(1)+"");
				//System.out.println(rs.getInt(1));
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(rs!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
		return list;
	}
	
	



	//2.用户userid关注哪些用户
	public    ArrayList<String>    getFollowingUserIDs(int    userid){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		ArrayList<String> list=new ArrayList<>();
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			String sql="select useridB from follows where useridA=?";
			
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs=ps.executeQuery();
			
			while(rs.next()){
				list.add(rs.getInt(1)+"");
				//System.out.println(rs.getInt(1));
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(rs!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
		return list;
	}
	
	
	



	//3.获取用户userid喜欢的帖子内容以及帖子的作者姓名
	public    ArrayList<ArrayList<String>>    getLikesOfUser(int    userid){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		ArrayList<ArrayList<String>> list=new ArrayList<>();
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			//String sql="select jabtext from jab where jabid in (select jabid from likes where userid=?)";
			//String sql="select username from jabberuser where userid in (select jabid from likes where userid=?)";
			String sql="select jabberuser.username,jab.jabtext from jab,jabberuser where jabid in (select jabid from likes where userid=?) and jabberuser.userid = jab.userid";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userid);

			rs=ps.executeQuery();
			
			while(rs.next()){
				ArrayList<String> temp=new ArrayList<>();
				temp.add(rs.getString(1));
				temp.add(rs.getString(2));
				list.add(temp);
				//list.add(rs.getInt(1)+"");
				//System.out.println(rs.getString(1)+":"+rs.getString(2));
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(rs!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
		return list;
	}
	




	
	//4.获取用户userid关注的人以及所发帖子内容
	public    ArrayList<ArrayList<String>>    getTimelineOfUser(int    userid){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		ArrayList<ArrayList<String>> list=new ArrayList<>();
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			String sql="select jabberuser.username,jab.jabtext from jabberuser,jab where jabberuser.userid in (select useridB from follows where useridA=?) and jabberuser.userid=jab.userid";
			
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs=ps.executeQuery();
			
			while(rs.next()){
				//list.add(rs.getInt(1)+"");
				ArrayList<String> temp=new ArrayList<>();
				temp.add(rs.getString(1));
				temp.add(rs.getString(2));
				list.add(temp);
				//System.out.println(rs.getString(1)+":"+rs.getString(2));
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(rs!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
		return list;
	}
	





	//5.获取相互关注的用户对（不重复）
	public    ArrayList<ArrayList<Integer>>    getMutualFollowUserIDs(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		ArrayList<ArrayList<Integer>> list=new ArrayList<>();
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			//未去重复
			String sql="select * from follows f1 inner join follows f2 on f1.useridA=f2.useridB and f1.useridB=f2.useridA";
		

			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			
			while(rs.next()){
				ArrayList<Integer> temp=new ArrayList<>();
				temp.add(rs.getInt(1));
				temp.add(rs.getInt(2));
				list.add(temp);
				//System.out.println(rs.getInt(1)+":"+rs.getInt(2));
			}
			//去重复对
			for(int i=0;i<list.size();i++){
				for(int j=0;j<list.size();j++){
					if(list.get(i).get(0)==list.get(j).get(1) && list.get(i).get(1)==list.get(j).get(0) && i!=j){
						list.remove(j);
					}
				}
			}
			
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(rs!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
		return list;
	}
	






	//6.通过用户名和邮箱号添加新的用户
	public    void    addUser(int userid,String    username,    String    emailadd){
		Connection conn=null;
		PreparedStatement ps=null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			String sql="insert into jabberuser (userid,username,emailadd) values (?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setString(2, username);
			ps.setString(3, emailadd);
			ps.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
	}
	
	






	//7.通过用户名和帖子内容添加帖子
	public    void    addJab(int jabid, String    username,    String    jabtext){
		Connection conn=null;
		PreparedStatement ps1=null;
		ResultSet rs=null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			String sql1="select userid from jabberuser where username=?";
			ps1=conn.prepareStatement(sql1);
			ps1.setString(1, username);
			rs=ps1.executeQuery();
			int userid=0;
			if(rs.next()){
				userid=rs.getInt(1);
			}
			
			
			String sql2="insert into jab (jabid,userid,jabtext) values (?,?,?)";
			ps1=conn.prepareStatement(sql2);
			ps1.setInt(1, jabid);
			ps1.setInt(2, userid);
			ps1.setString(3, jabtext);
			ps1.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(ps1!=null){
				try {
					ps1.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
	}
	





	
	//8.添加新的follow对（useridA和useridB）
	public    void    addFollower(int    userida,    int    useridb){
		Connection conn=null;
		PreparedStatement ps=null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			String sql="insert into follows (useridA,useridB) values (?,?)";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userida);
			ps.setInt(2, useridb);
			ps.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
	}
	







	//9.添加新的like关系（userid和jabid）
	public    void    addLike(int    userid,    int    jabid){
		Connection conn=null;
		PreparedStatement ps=null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			String sql="insert into likes (userid,jabid) values (?,?)";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setInt(2, jabid);
			ps.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
	}
	








	//10.返回follow者最多的用户
	public    ArrayList<String>    getUsersWithMostFollowers(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		ArrayList<String> list=new ArrayList<>();
		ArrayList<ArrayList<Integer>> temp=new ArrayList<>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/full_stack_assignment1","root","root");
			String sql="select useridB,count(*) num from follows group by useridB order by num desc";
			
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			
			while(rs.next()){
				ArrayList<Integer> list1=new ArrayList<>();
				list1.add(rs.getInt(1));
				list1.add(rs.getInt(2));
				//System.out.println(rs.getInt(1)+":"+rs.getInt(2));
				temp.add(list1);
			}
//			System.out.println(temp);
//			list.add(temp.get(0).get(0));
//			System.out.println(list);
			if(temp.size()==1){
				list.add(temp.get(0).get(0)+"");   //第一个元素一定是最大的，判断后面时候有同样大的元素
			}else if(temp.size()>1){
				list.add(temp.get(0).get(0)+""); 
				for(int i=1;i<temp.size();i++){
					if(temp.get(i).get(1)==temp.get(0).get(1)){
						list.add(temp.get(i).get(0)+"");
					}else{
						break;
					}
				}
				//System.out.println(list);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if(rs!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(ps!=null){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}
		return list;
	}
	
}