JDBC练习题——登录系统-CSDN博客

一、问题描述：流程如下，先登录，登录成功后显示操作界面，根据界面的提示，执行注册，修改，删除，查询的功能：

1、实现用户的注册功能（登录名不能重复）

表（sys_user）

id username (登录名) password（密码） name（名字）

2、实现用户的登陆功能

3、实现用户的修改，但要考虑用户是否真实存在

4、实现用户的删除功能。

5、实现用户的查询功能，查询全部

6，根据userName名去查询

封装类：

package com.etc.domian;
public class SysUser {

    private String usernaeme;
    private String pasword;
    private String name;
    private String id;

    public String getUsernaeme() {
        return usernaeme;
    }

    public void setUsernaeme(String usernaeme) {
        this.usernaeme = usernaeme;
    }

    public String getPasword() {
        return pasword;
    }

    public void setPasword(String pasword) {
        this.pasword = pasword;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
    public void setId(String id){
        this.id = id;
    }
    public String getId(){
        return id;
    }
}
复制代码

接口：

public interface SysUserService {

    //用户注册功能
    Integer addUser(SysUser  sysUser) throws SQLException;


    //登录功能
    Integer Login(SysUser sysUser) throws SQLException;


    //修改功能
    Integer modify(SysUser sysUser) throws SQLException;

    //删除功能
    Integer Delete(String  id) throws SQLException;

    //查询功能
    List<SysUser> query(SysUser sysUser) throws SQLException;

    //根据userName名去查询
    SysUser getUser(String name) throws SQLException;

}
复制代码

实现接口的类：

public class SysUserServiceImpl implements SysUserService {

    //获取连接
    private Connection conn = DbUtil.getConnection();


    /**
     * 用户添加
     *
     * @param adada
     * @return
     */
    @Override
    public Integer addUser(SysUser adada) throws SQLException {
        String sql = "INSERT INTO sys_user(`username`,`password`,`name`)VALUES (?,?,?)";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1, adada.getUsernaeme());
        st.setString(2, adada.getPasword());
        st.setString(3, adada.getName());
        int pp = st.executeUpdate();
        DbUtil.closeConnection(conn);
        DbUtil.closePreparedStatement(st);
        return pp;
    }

    @Override
    public Integer Login(SysUser sysUser) throws SQLException {

        /**
         * 1.首先要得到用户名和密码
         * 2.编写查询用户名的sql语句
         * 3.获取连接对象中的预编译存储语句对象
         * 4.设置预编译语句中的值
         * 5.发送sql语句到mysql中  查询使用query
         * 6.得到mysql返回  回来的数据并封装到一个user对象中供后续使用
         * 7.比较入参的用户名和密码  与  封装到返回对象的实例中的数据  是否一致
         * 8.判断 不一致说明密码错误，一致说明密码正确  用户实例如果为空  说明用户名不存在
         * 9.返回结果  数字
         */
        Integer result = null;

        String username = sysUser.getUsernaeme() != null ? sysUser.getUsernaeme() : "";

        String password = sysUser.getPasword() != null ? sysUser.getPasword() : "";
        if (username.equals("") || password.equals("")) {
            result = 4;
            return result;
        }

        String sql = "SELECT * FROM sys_user WHERE username= ?";
        PreparedStatement pps = conn.prepareStatement(sql);
        pps.setString(1, sysUser.getUsernaeme());
        ResultSet rs = pps.executeQuery();
        SysUser sysUser1 = new SysUser();
        while (rs.next()) {
            String username1 = rs.getString("username");
            String password1 = rs.getString("password");
            sysUser1.setUsernaeme(username1);
            sysUser1.setPasword(password1);
        }

        String usernameStart = "";
        if (sysUser1.getUsernaeme() == null) {
            usernameStart = "";
        } else {
            usernameStart = sysUser1.getUsernaeme();
        }

        String username3 = sysUser1.getUsernaeme() == null ? sysUser1.getUsernaeme() : "";

        if (!usernameStart.equals("")) {
            if (sysUser.getUsernaeme().equals(sysUser1.getUsernaeme())) {
                result = 1;
            } else {
                result = 2;
            }
        } else {
            result = 0;
        }
        DbUtil.closeConnection(conn);
        DbUtil.closePreparedStatement(pps);
        return result;
    }

    @Override
    public Integer modify(SysUser sysUser) throws SQLException {
        /**
         * 1.根据用户名查询出 用户id  用户名  用户密码  用户名字  封装到一个对象里进行后续的操作
         * 2.如果无返回说明没有该用户信息
         * 3.如果有使用update语句进行 sql的操作
         */
        Integer result = null;
        String sql = "SELECT * FROM sys_user WHERE username= ?";
        PreparedStatement pps = conn.prepareStatement(sql);
        pps.setString(1, sysUser.getUsernaeme());
        ResultSet rs = pps.executeQuery();
        SysUser sysUser1 = new SysUser();
        while (rs.next()) {
            String username1 = rs.getString("username");
            String password1 = rs.getString("password");
            int id1 = rs.getInt("id");
            sysUser1.setUsernaeme(username1);
            sysUser1.setPasword(password1);
            sysUser1.setId(String.valueOf(id1));
        }
        String id = sysUser1.getId() == null ? sysUser1.getId() : "";
        if (id.equals("")) {
            return 0;
        }
        String sql1 = "update sys_user set username = ?,password = ? where id = ?";
        PreparedStatement pps2 = conn.prepareStatement(sql1);
        pps2.setString(1, sysUser.getUsernaeme());
        pps2.setString(2, sysUser.getPasword());
        pps2.setString(3, id);
        result = pps2.executeUpdate();
        DbUtil.closePreparedStatement(pps);
        DbUtil.closeConnection(conn);
        return result;

    }

    @Override
    public Integer Delete(String id) throws SQLException {
        String sql = "DELETE FROM sys_user WHERE id = ?";
        PreparedStatement pps = conn.prepareStatement(sql);
        pps.setString(1, id);
        int result = pps.executeUpdate();
        DbUtil.closeConnection(conn);
        DbUtil.closePreparedStatement(pps);
        return result;
    }

    @Override
    public List<SysUser> query(SysUser sysUser) throws SQLException {
        String sql = "SELECT * FROM sys_user";
        Statement sm = conn.createStatement();
        ResultSet rs = sm.executeQuery(sql);
        List<SysUser> userList = new ArrayList<>();
        while (rs.next()) {
            SysUser sysUser1 = new SysUser();
            String username1 = rs.getString("username");
            String password1 = rs.getString("password");
            int id1 = rs.getInt("id");
            sysUser1.setUsernaeme(username1);
            sysUser1.setPasword(password1);
            sysUser1.setId(String.valueOf(id1));
            userList.add(sysUser1);
        }
        DbUtil.closeConnection(conn);
        DbUtil.closeStatement(sm);
        return userList;
    }

    @Override
    public SysUser getUser(String name) throws SQLException {
        String sql = "SELECT * FROM sys_user WHERE username= ?";
        PreparedStatement pps = conn.prepareStatement(sql);
        pps.setString(1, name);
        ResultSet rs = pps.executeQuery();
        SysUser sysUser1 = new SysUser();
        while (rs.next()) {
            String username1 = rs.getString("username");
            String password1 = rs.getString("password");
            sysUser1.setUsernaeme(username1);
            sysUser1.setPasword(password1);
        }
        DbUtil.closeConnection(conn);
        DbUtil.closeStatement(pps);
        return sysUser1;
    }
}
复制代码

测试类：

public class Test {

    SysUserService sysUserService;

    @Before
    public void bdf(){
        sysUserService = new SysUserServiceImpl();
    }



    @org.junit.Test
    public void add() throws SQLException {
        SysUser sysUser = new SysUser();
        sysUser.setName("欧阳锋");
        sysUser.setPasword("123456");
        sysUser.setUsernaeme("ouyyang");
        Integer sas = sysUserService.addUser(sysUser);
        if (sas == 1){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }
    }
    @org.junit.Test
    public void login() throws SQLException {
        SysUser sysUser = new SysUser();
        sysUser.setUsernaeme("aaasad");
        sysUser.setPasword("1234");
        Integer ss = sysUserService.Login(sysUser);
        if (ss == 1) {
            System.out.println("密码正确");
        } else if (ss == 0) {
            System.out.println("用户名不存在");
        } else if (ss == 2) {
            System.out.println("密码不正确");
        }  else if (ss == 4) {
            System.out.println("用户名和密码为空了");
        }else {
            System.out.println("系统异常");
        }
    }
    @org.junit.Test
    public  void modify() throws SQLException {
        SysUser sysUser = new SysUser();
        sysUser.setId("1");
        sysUser.setName("asda");
        sysUser.setUsernaeme("dada");
        sysUser.setPasword("888");
        Integer sus = sysUserService.modify(sysUser);
        if (sus==0){
            System.out.println("该用户不存在");
        }
    }
    @org.junit.Test
    public void Delete() throws SQLException {
        Integer ss =sysUserService.Delete("1");
    }
    @org.junit.Test
    public void query() throws SQLException {
        SysUser sysUser = new SysUser();
        sysUser.setId("13");
        sysUser.setName("asdadw");
        sysUser.setUsernaeme("dadwda");
        sysUser.setPasword("8886");
        List sus = sysUserService.query(sysUser);
    }
    @org.junit.Test
    public void getUser() throws SQLException {
        SysUser sus = sysUserService.getUser("dadad");
    }

}
复制代码