Java 学生管理系统v1

package cn.com.xiebiao.jdbcTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
 *
 * Title  : JdbcStudents.java
 * Company: ZhenBot
 * Author : Vibe Xie  @
 * Time   : Mar 4, 2015 9:34:54 PM
 * Copyright: Copyright (c) 2015
 * Description:学生管理系统v1(mysql实现)
 *
 * ******Statement可以优化为prepareStatement与编译sql语句。
 *
 */
public class JdbcStudents {
    public static Connection connection;
    public static Statement statement;
    public static ResultSet resultSet;
    public static PreparedStatement preparedStatement;

    static final String url="jdbc:mysql://localhost:3306/xiebiaoDB";
    static final String user="xiebiao";
    static final String password="*********";

    public static String sql;

    public static Scanner in=new Scanner(System.in);

    /*静态代码块，加载jdbc和连接Mysql数据库*/
    static{
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            connection=DriverManager.getConnection(url, user, password);
            if(connection!=null){
                System.out.println("连接:"+url+"成功");
            }else {
                System.out.println("连接:"+url+"失败");
                quit();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /*销毁资源*/
    public static void destoryResource(){
        try {
            if(resultSet!=null){
                resultSet.close();
                resultSet=null;
            }

            if(statement!=null){
                statement.close();
                statement=null;
            }

            if(connection!=null){
                connection.close();
                connection=null;
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void add(){
        int addId;
        String addName;
        String addClass;

        System.out.print("请输入Id:");
        addId=in.nextInt();
        System.out.print("请输入Name:");
        addName=in.next();
        System.out.print("请输入Class:");
        addClass=in.next();

        try{
            statement=connection.createStatement();
            sql="insert into students values ("+addId+",'"+addName+"','"+addClass+"');";
            int result=statement.executeUpdate(sql);
            if(result>0){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }
    }

    public static void delete(){
        int choice;
        int deleteId;
        String deleteName;
        String deleteClass;

        System.out.printf("1-按Id删除\n"
                 + "2-按Name删除\n"
                 + "3-按Class删除\n"
                 + "请输入选项:");
        choice=in.nextInt();

        switch (choice) {
        case 1:
            System.out.print("请输入Id:");
            deleteId=in.nextInt();
            sql="delete from students where id="+deleteId+";";
            break;
        case 2:
            System.out.print("请输入Name:");
            deleteName=in.next();
            sql="delete from students where name='"+deleteName+"';";
            break;
        case 3:
            System.out.print("请输入Class:");
            deleteClass=in.next();
            sql="delete from students where class='"+deleteClass+"';";
            break;
        default:
            break;
        }

        try{
            statement=connection.createStatement();
            int result=statement.executeUpdate(sql);
            if(result>0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }

        }catch(Exception ex){

        }
    }

    public static void alter(){
        int id;
        int alterId;
        String alterName;
        String alterClass;

        System.out.print("请输入要修改的Id:");
        id=in.nextInt();
        System.out.print("开始修改!\n");
        System.out.print("请输入Id:");
        alterId=in.nextInt();

        System.out.print("请输入Name:");
        alterName=in.next();

        System.out.print("请输入Class:");
        alterClass=in.next();
        sql="update students set id="+alterId+",name='"+alterName+"',class='"+alterClass+"' where id="+id+";";

        try{
            statement=connection.createStatement();
            int result=statement.executeUpdate(sql);
            if(result>0){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }

        }catch(Exception ex){

        }
    }

    public static void query(){
        int choice;
        int queryId;
        String queryName;
        String queryClass;

        System.out.printf("1-按Id查询\n"
                 + "2-按Name查询\n"
                 + "3-按Class查询\n"
                 + "请输入选项:");
        choice=in.nextInt();

        switch (choice) {
        case 1:
            System.out.print("请输入Id:");
            queryId=in.nextInt();
            sql="select * from students where id="+queryId+";";
            break;
        case 2:
            System.out.print("请输入Name:");
            queryName=in.next();
            sql="select * from students where name='"+queryName+"';";
            break;
        case 3:
            System.out.print("请输入Class:");
            queryClass=in.next();
            sql="select * from students where class='"+queryClass+"';";
            break;
        default:
            break;
        }

        try{
            statement=connection.createStatement();
            resultSet=statement.executeQuery(sql);

            while(resultSet.next()){
                System.out.println("学号："+resultSet.getInt("id")+" 姓名："+resultSet.getString("name")+" 班級："+resultSet.getString("class"));

            }

        }catch(Exception ex){
            ex.printStackTrace();
        }
    }

    public static void queryAll(){
        sql="select * from students;";
        try{
            statement=connection.createStatement();
            resultSet=statement.executeQuery(sql);

            while(resultSet.next()){
                System.out.println("学号："+resultSet.getInt("id")+" 姓名："+resultSet.getString("name")+" 班級："+resultSet.getString("class"));

            }

        }catch(Exception ex){
            ex.printStackTrace();
        }
    }

    public static void quit(){
        System.out.println("成功退出");
        destoryResource();
        System.exit(0);
    }

    public static void menu(){
        System.out.printf("***********学生数据管理系统(Mysql实现)*************\n"
                        + "               1-增加\n"
                        + "               2-删除\n"
                        + "               3-修改\n"
                        + "               4-查询\n"
                        + "               5-查询所有\n"
                        + "               6-退出\n"
                        + "************************************************\n"
                        + "请输入选项:");
    }

    public static void main(String[] args){
        int choice;
        while (true){
            menu();
            choice=in.nextInt();
            switch (choice) {
            case 1:
                add();
                break;
            case 2:
                delete();
                break;
            case 3:
                alter();
                break;
            case 4:
                query();
                break;
            case 5:
                queryAll();
                break;
            default:
                quit();
                break;
            }
        }
    }
}