java+sqlserver实现学生信息管理系统

目录

• 一.实现效果
• 二.实现代码
• 1.DBUtil.java
• 2.操作程序test.java

前提：

1.建立了与sqlserver数据库的连接
(JTDS连接sqlserver数据库的包jtds-1.2.7.jar)
2. 了解JDBC执行SQL的语法

一.实现效果

二.实现代码

1.DBUtil.java

说明：直接复制必然出错。

因为要连接自己的数据库，其中部分数据说明：

            Connection conn = getSQLConnection("192.168.223.1", "admin", "123123", "物流寄存");
//本机V8 ip   192.168.223.1
//在数据库中建立的一个登录名 admin
//登录名admin 的密码 123123
//要连接的数据库 物流寄存 （因为是临时作业就先随便找个数据库放了）

DBUtil.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DBUtil
{
    //连接数据库
    private static Connection getSQLConnection(String ip, String user, String pwd, String db)
    {
        Connection con = null;
        try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            //con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + ip + ":1433/" + db + ";charset=utf8", user, pwd);
            //jdbc:jtds:sqlserver://localhost:1433/dbname
            //解决输出中文乱码
            con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + ip + ":1433/" + db , user, pwd);

        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        return con;
    }

    //查询
    public static String QuerySQL()
    {
        String result = "";
        try
        {  //10.0.2.2 android ip
            //本机V8 ip   192.168.223.1
            Connection conn = getSQLConnection("192.168.223.1", "admin", "123123", "物流寄存");
            String sql = "select * from 学生信息表";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            //System.out.println(rs);
            while (rs.next())
            {//学号、姓名、班级、性别、专业、学院
                String s1 = rs.getString("学号").trim();
                String s2 = rs.getString("姓名").trim();
                String s3 = rs.getString("班级").trim();
                String s4 = rs.getString("性别").trim();
                String s5 = rs.getString("专业").trim();
                String s6 = rs.getString("学院").trim();
                result += s1 + "  " + s2 + "  " + s3 + "  " + s4+ "  " + s5+" "+s6+"\n";
               // System.out.println(s1 + "  -  " + s2);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
            result += "查询数据异常!" + e.getMessage();
        }
        return result;
    }

    //插入学生信息
    public static String insert_student(String sno,String name,String banji,String sex,String shuanye,String xueyuan)
    {//学号、姓名、班级、性别、专业、学院
        String result = "";
        try
        {  //10.0.2.2 android ip
            //本机V8 ip   192.168.223.1
            Connection conn = getSQLConnection("192.168.223.1", "admin", "123123", "物流寄存");
            String sql = "insert into  学生信息表 values ('"+sno+"','"+name+"','"+banji+"','"+sex+"','"+shuanye+"','"+xueyuan+"');";
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql);

            stmt.close();
            conn.close();
            result+="插入成功";
        } catch (SQLException e)
        {
            e.printStackTrace();
            result += "查询数据异常!" + e.getMessage();
        }
        return result;
    }

    //删除学生信息
    public static String delete_student(String sno)
    {//学号、姓名、班级、性别、专业、学院
        String result = "";
        try
        {  //10.0.2.2 android ip
            //本机V8 ip   192.168.223.1
            Connection conn = getSQLConnection("192.168.223.1", "admin", "123123", "物流寄存");
            String sql = "delete 学生信息表 where 学号 = " + sno;
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql);

            stmt.close();
            conn.close();
            result+="删除成功";
        } catch (SQLException e)
        {
            e.printStackTrace();
            result += "查询数据异常!" + e.getMessage();
        }
        return result;
    }

    //按学号查询
    public static String QuerySQL_sno(String sno)
    {
        String result = "";
        try
        {  //10.0.2.2 android ip
            //本机V8 ip   192.168.223.1  171.120.157.130
            Connection conn = getSQLConnection("192.168.223.1", "admin", "123123", "物流寄存");
            String sql = "select * from 学生信息表 where 学号 = '"+ sno+"';";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next())
            {//学号、姓名、班级、性别、专业、学院
                String s1 = rs.getString("学号").trim();
                String s2 = rs.getString("姓名").trim();
                String s3 = rs.getString("班级").trim();
                String s4 = rs.getString("性别").trim();
                String s5 = rs.getString("专业").trim();
                String s6 = rs.getString("学院").trim();
                result += s1 + "  " + s2 + "  " + s3 + "  " + s4+ "  " + s5+" "+s6+"\n";
                // System.out.println(s1 + "  -  " + s2);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
            result += "查询数据异常!" + e.getMessage();
        }
        return result;
    }

    //按性别查询
    public static String QuerySQL_sex(String sex)
    {
        String result = "";
        try
        {  //10.0.2.2 android ip
            //本机V8 ip   192.168.223.1  171.120.157.130
            Connection conn = getSQLConnection("192.168.223.1", "admin", "123123", "物流寄存");
            String sql = "select * from 学生信息表 where 性别 = '"+sex+"';";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next())
            {//学号、姓名、班级、性别、专业、学院
                String s1 = rs.getString("学号").trim();
                String s2 = rs.getString("姓名").trim();
                String s3 = rs.getString("班级").trim();
                String s4 = rs.getString("性别").trim();
                String s5 = rs.getString("专业").trim();
                String s6 = rs.getString("学院").trim();
                result += s1 + "  " + s2 + "  " + s3 + "  " + s4+ "  " + s5+" "+s6+"\n";
                // System.out.println(s1 + "  -  " + s2);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
            result += "查询数据异常!" + e.getMessage();
        }
        return result;
    }
    //按学院查询
    public static String QuerySQL_xueyuan(String xueyuan)
    {
        String result = "";
        try
        {  //10.0.2.2 android ip
            //本机V8 ip   192.168.223.1  171.120.157.130
            Connection conn = getSQLConnection("192.168.223.1", "admin", "123123", "物流寄存");
            String sql = "select * from 学生信息表 where 学院 = '"+ xueyuan +"';";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            //System.out.println(rs);
            while (rs.next())
            {//学号、姓名、班级、性别、专业、学院
                String s1 = rs.getString("学号").trim();
                String s2 = rs.getString("姓名").trim();
                String s3 = rs.getString("班级").trim();
                String s4 = rs.getString("性别").trim();
                String s5 = rs.getString("专业").trim();
                String s6 = rs.getString("学院").trim();
                result += s1 + "  " + s2 + "  " + s3 + "  " + s4+ "  " + s5+" "+s6+"\n";
                // System.out.println(s1 + "  -  " + s2);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
            result += "查询数据异常!" + e.getMessage();
        }
        return result;
    }
    public static void main(String[] args)
    {
        QuerySQL();
    }

}

2.操作程序test.java

说明：只要DBUtil.java无错误，并且可以利用DBUtil.java操作数据库，则这个test.java可以直接复制

import java.util.*;
public class test {
    public static void main(String[] args){
        Scanner in = new Scanner(System.in);
        int flag = 0;
        String sno = "", name = " ", banji = " ", sex = " ", shuanye = " ", xueyuan = " ";
        System.out.println("  学生信息管理程序  ");
        System.out.println(" 0.查看控制面板  ");
        System.out.println(" 1.查询全体学生信息   ");
        System.out.println(" 2.插入学生信息   ");
        System.out.println(" 3.删除学生   ");
        System.out.println(" 4.修改学生信息   ");
        System.out.println(" 5.查询相关信息   ");
        System.out.println(" 6.退出   ");
        while(true) {
            System.out.println(" 输入要继续执行的操作:");
            flag = in.nextInt();
            in.nextLine();
            if(flag == 6) break;
            else {
                switch (flag) {
                    case 0:
                        System.out.println(" 0.查看控制面板  ");
                        System.out.println(" 1.查询全体学生信息   ");
                        System.out.println(" 2.插入学生信息   ");
                        System.out.println(" 3.删除学生   ");
                        System.out.println(" 4.修改学生信息   ");
                        System.out.println(" 5.查询相关信息   ");
                        System.out.println(" 6.退出   ");
                        break;
                    case 1://查询全部
                        System.out.println("查询全体学生信息：");
                        System.out.print(DBUtil.QuerySQL());
                        break;
                    case 2://插入信息
                        System.out.println("请输入要插入的学生的信息（以空格隔开）：");
                        String str = in.nextLine();
                        String[] S = str.split(" ");
                        sno = S[0];
                        name = S[1];
                        banji = S[2];
                        sex = S[3];
                        shuanye = S[4];
                        xueyuan = S[5];
                        System.out.print(DBUtil.insert_student(sno, name, banji, sex, shuanye, xueyuan));
                        break;
                    case 3://删除学生信息
                        System.out.println("请输入要删除的学生的学号：");
                        sno = in.nextLine();
                        System.out.print(DBUtil.delete_student(sno));
                        break;
                    case 4://修改学生信息
                        System.out.println("请输入要修改的学生的学号：");
                        sno = in.nextLine();
                        DBUtil.delete_student(sno);
                        System.out.println("请输入要修改的学生的信息以空格隔开(学号不可修改)：");
                        String str2 = in.nextLine();
                        String[] S2 = str2.split(" ");
                        name = S2[0];
                        banji = S2[1];
                        sex = S2[2];
                        shuanye = S2[3];
                        xueyuan = S2[4];
                        DBUtil.insert_student(sno, name, banji, sex, shuanye, xueyuan);
                        System.out.println("修改之后的数据：");
                        break;
                    case 5://查询相关信息
                        System.out.println(" 1.按学院查询   ");
                        System.out.println(" 2.按学号查询  ");
                        System.out.println(" 3.按性别查询  ");

                        int FLG = Integer.parseInt(in.nextLine());
                        //in.nextInt();
                        switch(FLG){
                            case 1 ://按学院查询
                                System.out.println("要查询的学院：");
                                String temp_xueyuan = in.nextLine();
                                System.out.print(DBUtil.QuerySQL_xueyuan(temp_xueyuan));
                                break;
                            case 2 ://按学号查询
                                System.out.println("要查询学生的学号：");
                                String temp_sno = in.nextLine();
                                System.out.print(DBUtil.QuerySQL_sno(temp_sno));
                                break;
                            case 3://按性别查询
                                System.out.println("要查询的性别：");
                                String temp_sex = in.nextLine();
                                System.out.print(DBUtil.QuerySQL_sex(temp_sex));
                                break;
                        }

                        break;
                }//switch
            }//else
        }
    }

}