登录
当前位置： 首页 > 工具软件 > full-reactive-stack > 使用案例 >

full_stack_assignment1

洪永长
2023-12-01

数据库+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;
	}
	
}

 

 类似资料：

相关阅读

相关文章

相关问答

快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.