jdbc的进阶

JDBC的进阶

上篇忘了注明进行jdbc操作的时候要导入jar包，步骤如下：

1.新建lib包
2.copy jar包
3.右击add as library

这次进阶将代码分布在了不同包中，包结构如下：

bean
dao 增删改查
service 业务
controller //暂不讲

一、bean

实体类之前经常做，手打也很快，之前用Hibernate自动生成，这里完成了一个userbean

package Bean;

public class UserBean {
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public UserBean(){}
    public UserBean(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    @Override
    public String toString() {
        return "UserBean{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

二、Dao

然后在dao里将准备用到的方法完成：查询、增删改、登录注册等…

	public static ArrayList<UserBean> findAll() throws SQLException {
        ArrayList<UserBean> users = new ArrayList<>();
        try {
            conn = Util.getConnection();
            String sql = "select * from user";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
                UserBean userBean = new UserBean();
                userBean.setId(rs.getInt("id"));
                userBean.setUsername(rs.getString("username"));
                userBean.setPassword(rs.getString("password"));
                users.add(userBean);
            }
            ListIterator  li = users.listIterator();
            while(li.hasNext()){
                System.out.println(li.next());
            }
        }catch (Exception e){

        }finally{
            Util.close(conn,pstmt,rs);
        }
        return users;
    }
//增、删、改
    public static void adu(String sql2) throws SQLException {
        try {
            conn = Util.getConnection();
            pstmt = conn.prepareStatement(sql2);
            pstmt.executeUpdate();
            //search();
            users = (ArrayList<UserBean>) findAll();
        }catch (Exception e){

        }finally {
            Util.close(conn,pstmt,rs);
        }
    }

    public void adduser(String username,String password) throws SQLException {
        try {
            conn = Util.getConnection();
            String sql ="insert into user(username,password) values('"+username+"','"+password+"') ";
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
        }catch (Exception e){

        }finally {
            Util.close(conn,pstmt,rs);
        }

    }

    public boolean login(String username, String password) throws SQLException {
        try {
            conn = Util.getConnection();
            String sql = "select * from user where username='"+username+"' and password = '"+password+"'";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()){
                return true;
            }
            else {
                return false;
            }
        }catch (Exception e){

        }finally{
            Util.close(conn,pstmt,rs);
        }

        return false;
    }

3、Service

最后在service中进行调用

public static void main(String[] args) throws SQLException {

        if(login()){
            ArrayList<UserBean> users;
            UserBean userBean = new UserBean();
            UserDao userDao = new UserDao();

            int i = 0 ;
            Scanner sc = new Scanner(System.in);
            System.out.println("欢迎进入用户管理系统，请按照提示进行用户管理");
            System.out.println("输入1添加用户，输入2删除用户，输入3修改用户");
            i = sc.nextInt();
            String sql = "";
            switch (i){
                case 1:
                    System.out.println("请输入用户名");
                    String t = sc.nextLine();
                    String username = sc.nextLine();
                    System.out.println("用户名为"+username+",请输入密码");
                    String password = sc.nextLine();
                    sql ="insert into user(username,password) values('"+username+"','"+password+"') ";
                    userDao.adu(sql);
                    break;
                case 2:
                    users = userDao.findAll();
                    System.out.println("输入要删除的账号id");
                    t = sc.nextLine();
                    int id = sc.nextInt();
                    sql = "delete from user where id="+id;
                    userDao.adu(sql);
                    break;
                case 3:
                    users = userDao.findAll();
                    System.out.println("输入要修改的账号id");
                    id = sc.nextInt();
                    System.out.println("请输入修改后的用户名");
                    t = sc.nextLine();
                    username = sc.nextLine();
                    System.out.println("请输入修改后的密码");
                    password = sc.nextLine();
                    sql = "update user set username='"+username+"',password='"+password+"' where id="+id;
                    userDao.adu(sql);
                    break;
                default:
                    System.out.println("输入错误请重新输入"); break;
            }
        }
    }
    public static boolean login() throws SQLException {
        boolean loginflag = false;

        System.out.println("欢迎进入用户管理系统");
        System.out.println("请输入用户名：");
        Scanner sc = new Scanner(System.in);
        String username = sc.next();
        System.out.println("请输入密码：");
        String password = sc.next();
        loginflag=userDao.login(username,password);
        if (loginflag){
            System.out.println("登录成功");
            return true;
        }
        else {
            System.out.println("登录失败，请先注册");
            register();
            login();
            return true;
        }
    }
    public static void register() throws SQLException {

        Scanner sc = new Scanner(System.in);

        System.out.println("请输入要注册的用户名：");
        String newuser = sc.next();
        System.out.println("请输入要注册的密码：");
        String newpas = sc.next();
        userDao.adduser(newuser,newpas);
        System.out.println("注册成功，请重新登录");
    }

以上就是8.9号的课程内容