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