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

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

目录一.实现效果二.实现代码1.DBUtil.java2.操作程序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.getStKlSNcdring("班级").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 + " -http:// " + 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);

KlSNcd 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

}

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。