JDBC MySQL 实例之 用户管理系统

1 Java 和 MySQL 怎么建立连接

2 通过Java怎么对数据库进行操作

package day01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * 用户登录系统
 * Description: 
 */
public class Service {
    private static final int USER_REG = 1;
    private static final int USER_LOGIN = USER_REG + 1;
    private static final int USER_UPDATE = USER_LOGIN + 1;
    private static final int USER_DELETE = USER_UPDATE + 1;
    private static final int USER_INFO = USER_DELETE + 1;
    private static final int USER_TRANSFER = USER_INFO + 1;
    private static final int USER_QUIT = USER_TRANSFER + 1;
    private static final int EXIT = USER_QUIT + 1;
    
    UserInfo user = null;
    
    public static void main(String[] args) {
        Service serv = new Service();
        serv.start();
    }
    
    private void start() {
        welcome();
        int code = getCode();
        execute(code);
    }

    /**
     * 执行选择
     * Description: 
     */
    private void execute(int code) {
        switch (code) {
        case USER_REG:
            user_reg();
            break;
        case USER_LOGIN:
            user_login();
            break;
        case USER_UPDATE:
            user_update();
            break;
        case USER_DELETE:
            user_delete();
            break;
        case USER_INFO:
            user_info();
            break;
        case USER_TRANSFER:
            user_transfer();
            break;
        case USER_QUIT:
            user_quit();
            break;
        case EXIT:
            exit();
            break;
        default:
            System.out.println("输入错误，请重新输入");
            start();
            break;
        }
    }

    /**
     * Description: 
     */
    private void exit() {
        // TODO Auto-generated method stub
        if(null != this.user) {
            System.out.println("当前用户还没有退出，所以执行自动退出当前用户");
            user_quit();
        }else {
            System.out.println("你选择了退出系统");
            System.out.println("系统退出成功");
        }
        
    }

    /**
     * 退出当前用户
     * Description: 
     */
    private void user_quit() {
        // TODO Auto-generated method stub
        if(null != this.user) {
            System.out.println("你选择了退出当前用户功能");
            this.user = null;
            if(null == this.user) {
                System.out.println("成功退出当前用户");
            }else {
                System.out.println("退出当前用户失败");
            }
        }else {
            System.out.println("你还没有登录成功，还不能使用该功能");
            System.out.println("请登录！");
            user_login();
        }
        start();
    }

    /**
     * 转账功能
     * Description: 
     */
    private void user_transfer() {
        // TODO Auto-generated method stub
        if(null != this.user) {
            System.out.println("你选择了转账功能！");
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入转入账户的用户名：");
            String name = scanner.nextLine();
            System.out.println("请输入转账金额：");
            int money = Integer.parseInt(scanner.nextLine());
            
            Connection conn = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/test",
                        "root",
                        "182838");
                Statement state = conn.createStatement();
                
                //转出
                String out_sql = "UPDATE userinfo_fury "
                        + "SET account = account - '"+money+"' "
                        + "WHERE username = '"+this.user.getUsername()+"' ";
                int judge01 = state.executeUpdate(out_sql);
                if(judge01 > 0) {
                    System.out.println("转出成功");
                }else {
                    System.out.println("转出失败");
                }
                
                //转入
                String in_sql = "UPDATE userinfo_fury "
                        + "SET account = account + '"+money+"' "
                        + "WHERE username = '"+name+"' ";
                int judge02 = state.executeUpdate(in_sql);
                if(judge02 > 0) {
                    System.out.println("转入成功");
                }else {
                    System.out.println("转入失败");
                }
            }catch(Exception e) {
                e.printStackTrace();
            }finally {
                if(null != conn) {
                    try {
                        conn.close();
                    }catch(SQLException e1) {
                        e1.printStackTrace();
                    }
                }
            }
        }else {
            System.out.println("请先登录！");
            user_login();
        }
        start();
    }

    /**
     * 查询表中的所有数据
     * Description: 
     */
    private void user_info() {
        // TODO Auto-generated method stub
        if(null != this.user) {
            System.out.println("你选择了查询所有用户功能！");
            Connection conn = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/test",
                        "root",
                        "182838");
                Statement state = conn.createStatement();
                String sql = "SELECT id,username,password,email,nickname,account "
                        + "FROM userinfo_fury ";
                ResultSet rs = state.executeQuery(sql);
                List<UserInfo> list = new ArrayList<UserInfo>();
                
                while(rs.next()) {
                    int id = rs.getInt("id");
                    String username = rs.getString("username");
                    String password = rs.getString("password");
                    String email = rs.getString("email");
                    String nickname = rs.getString("nickname");
                    double account = rs.getDouble("account");
                    UserInfo userinfo = new UserInfo(id, username, password, email, nickname, account);
                    list.add(userinfo);
                }
                for(UserInfo lis : list) {
                    System.out.println(lis);
                }
            }catch(Exception e) {
                e.printStackTrace();
            }finally {
                if(null != conn) {
                    try {
                        conn.close();
                    }catch(SQLException e1) {
                        e1.printStackTrace();
                    }
                }
            }
        }else {
            System.out.println("请先登录");
            user_login();
        }
        start();
    }

    /**
     * 删除用户
     * Description: 
     */
    private void user_delete() {
        // TODO Auto-generated method stub
        if(null != this.user) {
            System.out.println("你选择了删除用户功能");
            System.out.println("你不是超级用户，你无法使用删除用户功能");
        }else {
            System.out.println("请先登录！");
            user_login();
        }
        start();
    }

    /**
     * 修改用户信息
     * Description: 
     */
    private void user_update() {
        // TODO Auto-generated method stub
        if(null != this.user) {
            System.out.println("你选择了修改当前用户功能!");
            //可改进 -->> 可由用户选择需要修改的字段
            System.out.println("你当前的昵称为：" + this.user.getNickname());
            Scanner scanner = new Scanner(System.in);
            System.out.println("你想将你的昵称修改为：");
            String nickname = scanner.nextLine();
            
            Connection conn = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/test",
                        "root",
                        "182838");
                Statement state = conn.createStatement();
                
                String sql = "UPDATE userinfo_fury "
                        + "SET nickname = '"+nickname+"' "
                        + "WHERE username = '"+this.user.getUsername()+"' ";
                int judge = state.executeUpdate(sql);
                if(judge > 0) {
                    this.user.setNickname(nickname);
                    System.out.println("修改昵称成功,当前昵称为：" + this.user.getNickname());
                }else {
                    System.out.println("修改昵称失败");
                }
            }catch(Exception e) {
                e.printStackTrace();
            }finally {
                if(null != conn) {
                    try {
                        conn.close();
                    }catch(SQLException e1) {
                        e1.printStackTrace();
                    }
                }
            }
        }else {
            System.out.println("请登录成功后在进行此操作！");
            user_login();
        }
        start();
    }

    /**
     * 用户登录
     * Description: 
     */
    private void user_login() {
        // TODO Auto-generated method stub
        System.out.println("你选择了用户登录功能！");
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();
        
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test",
                    "root",
                    "182838");
            Statement state = conn.createStatement();
            
            String sql = "SELECT id, username, password,email, nickname,account "
                    + "FROM userinfo_fury "
                    + "WHERE username = '"+username+"' "
                    + "AND password = '"+password+"' ";
            System.out.println(sql);
            ResultSet rs = state.executeQuery(sql);
            if(rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("username");
                String word = rs.getString("password");
                String email = rs.getString("email");
                String nickname = rs.getString("nickname");
                double account = rs.getDouble("account");
                UserInfo userinfo = new UserInfo(id, name, word, email, nickname, account);
                this.user = userinfo;
                System.out.println("登录成功,你的昵称为：" + this.user.getNickname());
            }else {
                System.out.println("登录失败:" + this.user);
            }
            /*
             * 注意：
             *         当用户输入的密码个的格式是：    任意字符' or '数值开头      时无论用户名和密码正确与否，都会登录成功
             *         因为  如果这样输入就改变了 SQL 语句的原意（在SQL语句中AND的优先级要高于OR）
             *         实例 ： asdfaer1234' or '1
             */
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            if(null != conn) {
                try {
                    conn.close();
                }catch(SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        start();
    }

    /**
     * 用户注册
     * Description: 
     */
    private void user_reg() {
        System.out.println("你选择了用户注册功能！");
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();
        System.out.println("请输入邮箱：");
        String email = scanner.nextLine();
        System.out.println("请输入昵称：");
        String nickname = scanner.nextLine();
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test",
                    "root",
                    "182838");
            Statement state = conn.createStatement();
            String sql = "INSERT INTO userinfo_fury "
                    + "(username,password,email,nickname) "
                    + "VALUES "
                    + "('"+username+"','"+password+"','"+email+"','"+nickname+"')";
            int judge = state.executeUpdate(sql);
            if(judge > 0) {
                System.out.println("注册成功");
            }else {
                System.out.println("注册失败");
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            if(null != conn) {
                try {
                    conn.close();
                }catch(SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        start();
    }

    /**
     * 功能选择
     * Description: 
     */
    private int  getCode() {
        System.out.println("请选择功能：");
        Scanner scanner = new Scanner(System.in);
        int code = Integer.parseInt(scanner.nextLine());
        return code;
    }

    /**
     * 界面信息
     * Description: 
     */
    private void welcome() {
        System.out.println("欢迎使用用户登录系统！");
        System.out.println("请输入需要操作的功能序号");
        System.out.println("======================");
        System.out.println("================");
        System.out.println("1 : 用户注册");
        System.out.println("2 : 用户登录");
        System.out.println("3 : 修改用户信息");
        System.out.println("4 : 删除用户");
        System.out.println("5 : 查看所有用户信息");
        System.out.println("6 : 转账业务");
        System.out.println("7 : 用户退出");
        System.out.println("8 : 退出系统");
        System.out.println("================");
        System.out.println("======================");
    }
}

3 改进

　　改程序是典型的 高耦合、低内聚， 改进之处很多很多...

　　三少今天有点懒，笔记待更新...

　　2017年4月5日19:42:31