2月14日总结——老年人评估系统7

今天对老年人评估系统进行了最后的测试和修改，也不得不吐槽，用ai制作的前端页面，虽说比我做的好看，但是实用性甚至更差了，因为查询数据那里都是错开的，歪歪扭扭，还有颜色也对应不上，表头没法展示等问题（这个在ai改完之后整的我也找不到原先未修改的版本在哪里了，本地历史记录变动太多了，尤其是改bug的时候，确定的一个bug动三四处，不确定的动更多次）预计明天发视频进行评价
这是修改后的dao，之前因为频繁变动没有发过。

package org.example.oldmandemo3;

import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Dao {
    public Connection getConnection()throws SQLException,ClassNotFoundException{
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/runoob";
        String user = "root";
        String password = "123456";
        return DriverManager.getConnection(url, user, password);
    }
    public String denglu(user user) throws SQLException, ClassNotFoundException, UnsupportedEncodingException {
        String sql = "select * from user where id = ?";
        try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getId());
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                if (user.getPassword().equals(rs.getString("password"))) {
                    return rs.getString("type");
                }
                else
                {
                    return "密码错误";
                }
            }
            else {
                return "尚未注册";
            }
        }
    }
    public boolean addbasic(basic data)throws SQLException ,ClassNotFoundException{
        if(data==null||data.getNumber().length()!=10) return false;
        String sql1="select * from basic where number=?";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql1)){
            ps.setString(1,data.getNumber());
            ResultSet rs=ps.executeQuery();
            if(rs.next()) {
                if(rs.getString("number").equals(data.getNumber())) return false;//判断编号是否已存在
            }
        }
        String sql2 = "insert into basic values(?,?,?)";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql2)){
            ps.setString(1,data.getNumber());
            ps.setDate(2,data.getDate());
            ps.setString(3,data.getReason());
            return ps.executeUpdate()>0;
        }
    }

    public boolean adduser1(user user) throws SQLException, ClassNotFoundException {
        String sql="insert into user values(?,?,?)";
        try(Connection conn=getConnection(); PreparedStatement ps= conn.prepareStatement(sql)){
            ps.setString(1,user.getId());
            ps.setString(2,user.getPassword());
            ps.setString(3,user.getType());
            return ps.executeUpdate()>0;
        }
    }
    public boolean adduser2(user user) throws SQLException, ClassNotFoundException {
        String sql="insert into user values(?,?,?)";
        try(Connection conn=getConnection(); PreparedStatement ps= conn.prepareStatement(sql)){
            ps.setString(1,user.getId());
            ps.setString(2,user.getPassword());
            ps.setString(3,user.getType());
            return ps.executeUpdate()>0;
        }
    }
    public boolean addpeople(people data)throws SQLException ,ClassNotFoundException{
        if(data==null||data.getNumber().length()!=10||data.getIdnumber().length()!=18||data.getSocialid().length()!=9) return false;
        String sql1="select * from people where number=?";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql1)){
            ps.setString(1,data.getNumber());
            ResultSet rs=ps.executeQuery();
            if(rs.next()) {
                if(rs.getString("number").equals(data.getNumber())) return false;
            }
        }
        String sql2 = "insert into people values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql2)){
            ps.setString(1,data.getName());
            ps.setString(2,data.getSex());
            ps.setDate(3,data.getBirthday());
            ps.setString(4,data.getIdnumber());
            ps.setString(5,data.getSocialid());
            ps.setString(6,data.getNation());
            ps.setString(7,data.getEducation());
            ps.setString(8,data.getReligion());
            ps.setString(9,data.getMarriage());
            ps.setString(10, data.getReside());
            ps.setString(11,data.getMedical());
            ps.setString(12,data.getIncome());
            ps.setString(13,data.getStupid());
            ps.setString(14,data.getMental());
            ps.setString(15,data.getChronic());
            ps.setString(16,data.getTumble());
            ps.setString(17,data.getLost());
            ps.setString(18,data.getDysphoria());
            ps.setString(19,data.getSuicide());
            ps.setString(20,data.getOther());
            ps.setString(21,data.getNumber());
            return ps.executeUpdate()>0;
        }
    }
    public boolean addinformation(information data)throws SQLException ,ClassNotFoundException{
        if(data==null||data.getNumber().length()!=10||data.getPhone().length()!=11||data.getIdnumber().length()!=18) return false;
        String sql1="select * from information where number=?";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql1)){
            ps.setString(1,data.getNumber());
            ResultSet rs=ps.executeQuery();
            if(rs.next()) {
                if(rs.getString("number").equals(data.getNumber())) return false;
            }
        }
        String sql2 = "insert into information values(?,?,?,?,?,?)";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql2)){
            ps.setString(1,data.getName());
            ps.setString(2,data.getRelation());
            ps.setString(3,data.getContacts());
            ps.setString(4,data.getPhone());
            ps.setString(5,data.getNumber());
            ps.setString(6,data.getIdnumber());
            return ps.executeUpdate()>0;
        }
    }
    public List<basic> checkbasic(String number) throws SQLException, ClassNotFoundException {
        String sql;
        if(number.isEmpty()) sql="select * from basic";
        else sql="select * from basic where number=?";
        List<basic> list = new ArrayList<>();
        try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            if(!number.isEmpty()) ps.setString(1,number);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                basic data=new basic(rs.getString("number"),
                        rs.getDate("date"),
                        rs.getString("reason"));
                list.add(data);
            }
            return list;
        }
    }
    public List<people> checkpeople(String type,String number)throws SQLException,ClassNotFoundException{
        String sql;
        if(type.equals("评估员")||type.equals("管理员")) {
            if (number.isEmpty()) sql = "select * from people";
            else sql = "select * from people where number=?";
        }else sql = "select * from people where idnumber=?";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql)){
            if(!number.isEmpty()) ps.setString(1,number);
            ResultSet rs=ps.executeQuery();
            List<people> list=new ArrayList<>();
            while(rs.next()){
                people data=new people(rs.getString("name"),
                        rs.getString("sex"),
                        rs.getDate("birthday"),
                        rs.getString("idnumber"),
                        rs.getString("socialid"),
                        rs.getString("nation"),
                        rs.getString("education"),
                        rs.getString("religion"),
                        rs.getString("marriage"),
                        rs.getString("reside"),
                        rs.getString("medical"),
                        rs.getString("income"),
                        rs.getString("stupid"),
                        rs.getString("mental"),
                        rs.getString("chronic"),
                        rs.getString("tumble"),
                        rs.getString("lost"),
                        rs.getString("dysphoria"),
                        rs.getString("suicide"),
                        rs.getString("other"),
                        rs.getString("number"));
                list.add(data);
            }
            return list;
        }
    }
    public List<information> checkinformation(String type,String number)throws SQLException,ClassNotFoundException{
        String sql;
        if(type.equals("评估员")||type.equals("管理员")) {
            if (number.isEmpty()) sql = "select * from information";
            else sql = "select * from information where number=?";
        }else sql = "select * from information where idnumber=?";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql)){
            if(!number.isEmpty()) ps.setString(1,number);
            ResultSet rs=ps.executeQuery();
            List<information> list=new ArrayList<>();
            while(rs.next()){
                information data=new information(rs.getString("name"),
                        rs.getString("relation"),
                        rs.getString("contacts"),
                        rs.getString("phone"),
                        rs.getString("number"),
                        rs.getString("idnumber"));
                list.add(data);
            }
            return list;
        }
    }
    public List<summary> checksummary(String type,String number)throws SQLException,ClassNotFoundException{
        String sql;
        if(type.equals("评估员")||type.equals("管理员")) {
            if (number.isEmpty()) sql = "select * from summary";
            else sql = "select * from summary where number=?";
        }else sql = "select * from summary where idnumber=?";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql)) {
            if(!number.isEmpty()) ps.setString(1, number);
            ResultSet rs = ps.executeQuery();
            List<summary> list=new ArrayList<>();
            while(rs.next()){
                summary data=new summary(
                        rs.getInt("level1"),
                        rs.getInt("level2"),
                        rs.getInt("level3"),
                        rs.getInt("level4"),
                        rs.getInt("level5"),
                        rs.getInt("level6"),
                        rs.getInt("level7"),
                        rs.getString("name1"),
                        rs.getString("name2"),
                        rs.getDate("date1"),
                        rs.getString("name3"),
                        rs.getDate("date2"),
                        rs.getString("idnumber"),
                        rs.getString("number")
                );
                list.add(data);
            }
            return list;
        }
    }
    public boolean changeinformation(information data)throws SQLException,ClassNotFoundException{
        String sql="update information set name=?,relation=?,contacts=?,phone=? where number=?";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql)){
            ps.setString(1,data.getName());
            ps.setString(2,data.getRelation());
            ps.setString(3,data.getContacts());
            ps.setString(4,data.getPhone());
            ps.setString(5,data.getNumber());
            return ps.executeUpdate()>0;
        }
    }
    public boolean changepeople(people data)throws SQLException,ClassNotFoundException{
        String sql="update people set name=?,sex=?,birthday=?,idnumber=?,socialid=?,nation=?,education=?,religion=?,marriage=?," +
                "reside=?,medical=?,income=?,stupid=?,mental=?,chronic=?,tumble=?,lost=?,dysphoria=?,suicide=?,other=? where number=?";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql)){
            ps.setString(1,data.getName());
            ps.setString(2,data.getSex());
            ps.setDate(3,data.getBirthday());
            ps.setString(4,data.getIdnumber());
            ps.setString(5,data.getSocialid());
            ps.setString(6,data.getNation());
            ps.setString(7,data.getEducation());
            ps.setString(8,data.getReligion());
            ps.setString(9,data.getMarriage());
            ps.setString(10, data.getReside());
            ps.setString(11,data.getMedical());
            ps.setString(12,data.getIncome());
            ps.setString(13,data.getStupid());
            ps.setString(14,data.getMental());
            ps.setString(15,data.getChronic());
            ps.setString(16,data.getTumble());
            ps.setString(17,data.getLost());
            ps.setString(18,data.getDysphoria());
            ps.setString(19,data.getSuicide());
            ps.setString(20,data.getOther());
            ps.setString(21,data.getNumber());
            return ps.executeUpdate()>0;
        }
    }
    public boolean adddaily(daily data)throws SQLException,ClassNotFoundException{
        String sql1="select number from people where idnumber=?";
        String number = "";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql1)) {
            ps.setString(1, data.getIdnumber());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                number = rs.getString("number");
            }
        }
        String sql = "insert into daily values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, data.getEat());
            ps.setInt(2, data.getBath());
            ps.setInt(3, data.getWash());
            ps.setInt(4, data.getCloth());
            ps.setInt(5, data.getStool());
            ps.setInt(6, data.getUrinal());
            ps.setInt(7, data.getToilet());
            ps.setInt(8, data.getWalk());
            ps.setInt(9, data.getMove());
            ps.setInt(10, data.getFloor());
            ps.setInt(11, data.getTotal());
            ps.setInt(12, data.getLevel());
            ps.setString(13, number);
            ps.setString(14, data.getIdnumber());
            return ps.executeUpdate()>0;
        }
    }
    public boolean addmind(mind data)throws SQLException,ClassNotFoundException{
        String sql1="select number from people where idnumber=?";
        String number = "";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql1)) {
            ps.setString(1, data.getIdnumber());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                number = rs.getString("number");
            }
        }
        String sql = "insert into mind values(?,?,?,?,?,?,?)";
        try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, data.getCogintion());
            ps.setInt(2, data.getAttack());
            ps.setInt(3,data.getDepression());
            ps.setInt(4, data.getTotal());
            ps.setInt(5, data.getLevel());
            ps.setString(6, number);
            ps.setString(7, data.getIdnumber());
            return ps.executeUpdate()>0;
        }
    }
    public boolean addfeel(feel data)throws SQLException,ClassNotFoundException{
        String sql1="select number from people where idnumber=?";
        String number = "";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql1)) {
            ps.setString(1, data.getIdnumber());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                number = rs.getString("number");
            }
        }
        String sql = "insert into feel values(?,?,?,?,?,?,?)";
        try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, data.getConsciousness());
            ps.setInt(2, data.getVision());
            ps.setInt(3,data.getHearing());
            ps.setInt(4, data.getCommunication());
            ps.setInt(5,data.getLevel());
            ps.setString(6,number);
            ps.setString(7, data.getIdnumber());
            return ps.executeUpdate()>0;
        }
    }
    public boolean addsocial(social data)throws SQLException,ClassNotFoundException{
        String sql1="select number from people where idnumber=?";
        String number = "";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql1)) {
            ps.setString(1, data.getIdnumber());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                number = rs.getString("number");
            }
        }
        String sql = "insert into social values(?,?,?,?,?,?,?,?,?)";
        try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, data.getLive());
            ps.setInt(2, data.getWork());
            ps.setInt(3,data.getTimespace());
            ps.setInt(4, data.getRelation());
            ps.setInt(5,data.getSocialing());
            ps.setInt(6, data.getTotal());
            ps.setInt(7,data.getLevel());
            ps.setString(8, number);
            ps.setString(9, data.getIdnumber());
            return ps.executeUpdate()>0;
        }
    }
    public boolean addsummary(summary data)throws SQLException,ClassNotFoundException{
        String sql1="select number from people where idnumber=?";
        String number = "";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql1)) {
            ps.setString(1, data.getIdnumber());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                number = rs.getString("number");
            }
        }
        String sql="insert into summary values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql)) {
            ps.setInt(1,data.getLevel1());
            ps.setInt(2,data.getLevel2());
            ps.setInt(3,data.getLevel3());
            ps.setInt(4,data.getLevel4());
            ps.setInt(5,data.getLevel5());
            ps.setInt(6,data.getLevel6());
            ps.setInt(7,data.getLevel7());
            ps.setString(8,data.getName1());
            ps.setString(9,data.getName2());
            ps.setDate(10,data.getDate1());
            ps.setString(11,data.getName3());
            ps.setDate(12,data.getDate2());
            ps.setString(13, data.getIdnumber());
            ps.setString(14, number);
            return ps.executeUpdate()>0;
        }
    }
    public boolean deletebasic(String number) throws SQLException, ClassNotFoundException {
// 定义每个DELETE语句
        String sqlPlan = "DELETE FROM basic WHERE number = ?";
        String sqlPeople = "DELETE FROM people WHERE number = ?";
        String sqlInformat = "DELETE FROM information WHERE number = ?";
        String sqlDaily = "DELETE FROM daily WHERE number = ?";
        String sqlMood = "DELETE FROM mind WHERE number = ?";
        String sqlPerception = "DELETE FROM feel WHERE number = ?";
        String sqlSocial = "DELETE FROM social WHERE number = ?";
        try (Connection conn = getConnection()) {
//开始事务
            conn.setAutoCommit(false);
            try (PreparedStatement psPlan = conn.prepareStatement(sqlPlan);
                 PreparedStatement psPeople = conn.prepareStatement(sqlPeople);
                 PreparedStatement psInformat = conn.prepareStatement(sqlInformat);
                 PreparedStatement psDaily = conn.prepareStatement(sqlDaily);
                 PreparedStatement psMood = conn.prepareStatement(sqlMood);
                 PreparedStatement psPerception = conn.prepareStatement(sqlPerception);
                 PreparedStatement psSocial = conn.prepareStatement(sqlSocial)) {
//设置参数
                psPlan.setString(1, number);
                psPeople.setString(1, number);
                psInformat.setString(1, number);
                psDaily.setString(1, number);
                psMood.setString(1, number);
                psPerception.setString(1, number);
                psSocial.setString(1, number);
// 执行每个DELETE语句
                int rowsDeletedPlan = psPlan.executeUpdate();
                int rowsDeletedPeople = psPeople.executeUpdate();
                int rowsDeletedInformat = psInformat.executeUpdate();
                int rowsDeletedDaily = psDaily.executeUpdate();
                int rowsDeletedMood = psMood.executeUpdate();
                int rowsDeletedPerception = psPerception.executeUpdate();
                int rowsDeletedSocial = psSocial.executeUpdate();
//提交事务
                conn.commit();
//返回结果
                return rowsDeletedPlan > 0 || rowsDeletedPeople > 0 || rowsDeletedInformat > 0 ||
                        rowsDeletedDaily > 0 || rowsDeletedMood > 0 || rowsDeletedPerception > 0 ||
                        rowsDeletedSocial > 0;
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        }
    }
    public boolean changebasic(basic data)throws SQLException,ClassNotFoundException{
        String sql="update basic set date=?,reason=? where number=?";
        try(Connection conn=getConnection(); PreparedStatement ps=conn.prepareStatement(sql)){
            ps.setDate(1,data.getDate());
            ps.setString(2,data.getReason());
            ps.setString(3,data.getNumber());
            return ps.executeUpdate()>0;
        }
    }
}