2024/4/8

今日任务：学习github并熟练使用，完成部分结对作业函数方法调用：

package com.example.team.dao;

import static com.example.fri.SelectDao.getAllf1;
import static com.example.team.utils.JDBCUtils.getConn;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.fri.FriendBean;
import com.example.team.MyDatabaseHelper;
import com.example.team.entity.Book;
import com.example.team.entity.Space;
import com.example.team.entity.SpacePlus;
import com.example.team.entity.User;
import com.example.team.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;

public class UserDao {
    private Context context;
    private SQLiteOpenHelper dbHelper;
    private Connection conn;


    public void closeConnection() {
        JDBCUtils.close(conn);
    }

    public UserDao(Context context) {
        this.context = context;
        conn = getConn();
        dbHelper = new MyDatabaseHelper(context); // 假设有一个名为DbHelper的SQLiteOpenHelper类
    }
    public boolean login(String username, String password) {
        String sql = "select * from login where username = ? and password = ?";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, username);
            pst.setString(2, password);
            ResultSet rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
    }
    public boolean register(User user) {
        String checkIfExistsSql = "select * from login where username = ?";
        String insertSql = "insert into login(username, name, phone, password) values (?, ?, ?, ?)";
        try {
            // Check if the username already exists
            PreparedStatement checkIfExistsPst = conn.prepareStatement(checkIfExistsSql);
            checkIfExistsPst.setString(1, user.getUsername());
            ResultSet rs = checkIfExistsPst.executeQuery();
            if (rs.next()) {
                return false; // Username already exists
            }

            // Register the user if the username is not found
            PreparedStatement insertPst = conn.prepareStatement(insertSql);
            insertPst.setString(1, user.getUsername());
            insertPst.setString(2, user.getName());
            insertPst.setString(3, user.getPhone());
            insertPst.setString(4, user.getPassword());
            int value = insertPst.executeUpdate();

            return value > 0; // Registration successful
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }finally {
            closeConnection(); // 关闭数据库连接
        }
    }

    @SuppressLint("Range")
    public SpacePlus findUser1(String username) {
        String sql = "select * from login where username = ?";
        SpacePlus user = null;
        try {
            if (conn != null) { // 添加空指针检查
                PreparedStatement pst = conn.prepareStatement(sql);
                pst.setString(1, username);
                ResultSet rs = pst.executeQuery();
                if (rs.next()) {
                    username = rs.getString(1);
                    String namedb = rs.getString(2);
                    user = new SpacePlus(username, namedb);
                }
            } else {
                System.out.println("数据库连接为null");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return user;
    }

    public boolean space(Space space) {
        String sql = "insert into space(username, name, space) values (?, ?, ?)";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, space.getUsername());
            pst.setString(2, space.getName());
            pst.setString(3, space.getSpace());
            int value = pst.executeUpdate();
            return value > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
    }

    public User findUser(String name) {
        String sql = "select * from login where username = ?";
        User user = null;
        try {
            if (conn != null) { // 添加空指针检查
                PreparedStatement pst = conn.prepareStatement(sql);
                pst.setString(1, name);
                ResultSet rs = pst.executeQuery();
                if (rs.next()) {
                    String username = rs.getString(1);
                    String namedb = rs.getString(2);
                    String phone = rs.getString(3);
                    String passworddb = rs.getString(4);
                    user = new User(username, namedb,phone, passworddb);
                }
            } else {
                System.out.println("数据库连接为null");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return user;
    }
    public static ArrayList<Book> getAllBookNames() {

        ArrayList<Book> item = new ArrayList<>();
        try {


            String sql = "SELECT * FROM book";
            Connection conn = getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql);

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {

                String a = rs.getString("name");
                String b= rs.getString("passage");
                String c = rs.getString("page");
                String d = rs.getString("value");
                Book f = new Book(a,b,c,d);
                item.add(f);
            }

            rs.close();
            pstmt.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
//            JDBCUtils.close(conn);
        }

        return item;
    }
    public static ArrayList<Space> findSpacesByUsername(String username) {//获取全部space

        ArrayList<Space> item = new ArrayList<>();
        try {


            String sql = "SELECT * FROM space WHERE username = ?";
            Connection conn = getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {

                String a = rs.getString("username");
                String b= rs.getString("name");
                String c = rs.getString("space");


                Space f = new Space(a,b,c);

                item.add(f);
            }

            rs.close();
            pstmt.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
//            JDBCUtils.close(conn);
        }

        return item;
    }

    public static ArrayList<Space> findSpacesByname(String username) {//获取全部space

        ArrayList<Space> item = new ArrayList<>();
        try {


            String sql = "SELECT * FROM space WHERE name = ?";
            Connection conn = getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {

                String a = rs.getString("username");
                String b= rs.getString("name");
                String c = rs.getString("space");


                Space f = new Space(a,b,c);

                item.add(f);
            }

            rs.close();
            pstmt.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
//            JDBCUtils.close(conn);
        }

        return item;
    }

    public static List<String> getUsernamesFromFriendTable() {
        List<String> usernames = new ArrayList<>();
        Future<List<String>> future = Executors.newSingleThreadExecutor().submit(() -> {
            List<String> result = new ArrayList<>();
            Connection conn = getConn();
            if (conn == null) {
                // 处理数据库连接为null的情况
                return result;
            }
            String query = "SELECT username FROM friend";
            try {
                PreparedStatement preparedStatement = conn.prepareStatement(query);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    String username = resultSet.getString("username");
                    result.add(username);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.close(conn);
            }
            return result;
        });

        try {
            usernames = future.get();
        } catch (InterruptedException | ExecutionException e) {
            e.printStackTrace();
        }
        return usernames;
    }
    public static ArrayList<Space> findAllSpacesIncludingSelf(String name) {
        ArrayList<Space> allSpaces = new ArrayList<>();


            // 查询自己的space
            ArrayList<Space> mySpaces = findSpacesByname(name);
            allSpaces.addAll(mySpaces); // 将自己的space添加到结果列表中

            // 获取全部好友
            ArrayList<FriendBean> friends = getAllf1(name);

            // 遍历好友列表，获取每个好友的space信息
            for (FriendBean friend : friends) {
                String friendUsername = friend.getSf(); // 假设FriendBean中的sf字段存储的是好友的用户名
                ArrayList<Space> friendSpaces = findSpacesByname(friendUsername);
                allSpaces.addAll(friendSpaces); // 将好友的space添加到结果列表中
            }


        return allSpaces;
    }
}

今日问题：暂无