JDBC 练习1 完成一个可以增删改查的用户数据库

1.数据库创建 db.users

create database db character set utf8;
use db;
create table users(
uid int(32) primary key auto_increment,
uname varchar(32) not null,
upassword varchar(32) not null
);
alter table users convert to character set utf8;

2.(可选)插入一些用户

INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('abc', 'uabc');
INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('神奇的我', 'abc123');
INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('隔壁老王', 'hahaha');
INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('瑟瑟发抖', 'sesefadou');
INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('abcdefg', 'zxcvbnm');
INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('神奇的他', 'aaaa123');
INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('神奇的她', 'bbbbb123');

3.java代码

Main.java

package Main;

import JDBC.MyMysqlTool;

public class Main {
    public static void main(String[] args)  throws Exception {
        MyMysqlTool my=new MyMysqlTool("jdbc:mysql://localhost:3306?characterEncoding=utf8","root","123456");

        //注册
        //my.Register("瑟瑟发抖","aaaa");

        //验证
        //my.Login("abc","1123");

        //注销
        //my.CanceRon("abc","123");

        //修改
        //my.Update("abc","1123","F4","f4444");
    }
}

MyMysqlTool.java

package JDBC;

import java.sql.*;

public class MyMysqlTool {
    String URL=null;
    String user=null;
    String password=null;

    public MyMysqlTool(String URL, String user, String password) {
        this.URL = URL;
        this.user = user;
        this.password = password;
    }

    private boolean LookupUnameExist(String uname) throws Exception
    {
        //查看用户名是否存在
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        Connection conn= DriverManager.getConnection(URL,user,password);
        //创建执行sql语句对象
        Statement st=conn.createStatement();
        //创建sql语句
        String sql="select * from db.users where uname=?";
        //创建预处理对象
        PreparedStatement pstmt=conn.prepareStatement(sql);
        //设置占位符
        pstmt.setString(1,uname);
        //执行查询操作
        ResultSet rs=pstmt.executeQuery();
        boolean rese=false;
        if(rs.next())
        {
            rese=true;
        }
        rs.close();
        st.close();
        conn.close();
        return rese;
    }
    private boolean lookup(String uname, String upassword) throws Exception
    {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        Connection conn= DriverManager.getConnection(URL,user,password);
        //创建执行sql语句对象
        Statement st=conn.createStatement();
        //创建sql语句
        String sql="select * from db.users where uname=? and upassword=? ";
        //创建预处理对象
        PreparedStatement pstmt=conn.prepareStatement(sql);
        //设置占位符
        pstmt.setString(1,uname);
        pstmt.setString(2,upassword);
        //执行查询操作
        ResultSet rs=pstmt.executeQuery();
        boolean rese=false;
        if(rs.next())
        {
            rese=true;
        }
        rs.close();
        st.close();
        conn.close();
        return rese;
    }

    public void Login(String uname, String upassword) throws Exception {
        if(lookup(uname,upassword)==true)
        {
            System.out.println("登录成功");
        }
        else if(LookupUnameExist(uname)) System.out.println("密码错误");
        else System.out.println("用户名不存在");
    }
    public void Register(String uname, String upassword) throws Exception
    {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        Connection conn= DriverManager.getConnection(URL,user,password);
        //创建执行sql语句对象
        Statement st=conn.createStatement();
        if(LookupUnameExist(uname))
        {
            //用户名存在
            System.out.println("用户名存在 注册失败");
        }
        else
        {
            //可以注册
            String sql="insert into db.users values(null,?,?)";
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,uname);
            pstmt.setString(2,upassword);
            int rss=pstmt.executeUpdate();
            if(rss==1)
            System.out.println("注册成功");
            else System.out.println("注册失败");
        }
        st.close();
        conn.close();

    }

    //注销 删除用户
    public void CanceRon(String uname,String upassword) throws Exception {
        if(lookup(uname,upassword)==true)
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn= DriverManager.getConnection(URL,user,password);
            Statement st=conn.createStatement();
            String sql="delete from db.users where uname=? and upassword=?";
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,uname);
            pstmt.setString(2,upassword);
            int rss=pstmt.executeUpdate();
            if(rss==1)
            System.out.println("注销成功");
            else System.out.println("注销失败");
            pstmt.close();
            conn.close();
        }
        else if(LookupUnameExist(uname))System.out.println("密码错误");
        else System.out.println("用户名不存在");
    }

    //修改资料
    public void Update(String oldname,String oldpasswod,String newname,String newpassword) throws Exception
    {
        if(lookup(oldname,oldpasswod))
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn= DriverManager.getConnection(URL,user,password);
            Statement st=conn.createStatement();
            String sql="update db.users set uname=?,upassword=? where uname=? and upassword=?";
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,newname);
            pstmt.setString(2,newpassword);
            pstmt.setString(3,oldname);
            pstmt.setString(4,oldpasswod);
            int rss=pstmt.executeUpdate();
            if(rss==1)
                System.out.println("修改成功");
            else System.out.println("修改失败");
            pstmt.close();
            conn.close();
        }
        else if(LookupUnameExist(oldname)) System.out.println("密码错误");
        else System.out.println("用户名不存在");
    }


}