上次是Java+MySQL，这次是Java+SQL server
如有错误，提前感谢您来指正~
设置时候遇到的一些问题：
1.Java连接sqlserver
参考
https://blog.csdn.net/lq1759336950/article/details/87527914
2.找启用TCP/IP启动参考https://developers.blog.csdn.net/article/details/538974863.不给select权限参考https://blog.csdn.net/qq_38861743/article/details/897867704.语句没有返回集参考https://blog.csdn.net/lsh199196/article/details/40889469
5...........忘记了
6.再水一个 连接登录先创建个用户（我的sa用户登陆不上），因为是普通用户所以又有了第2条
create login wang with password ='XX'
create user wang代码部分
package StudentManagementSystem;
public class runSystem {
public static void main(String[] args) throws Exception {
Management m = new Management();
m.getConnection();
m.services();
}
}package StudentManagementSystem;
import java.sql.*;
import java.util.Scanner;
public class Management{
private Connection conn;
private Statement stmt;
private ResultSet rs;
/*进行操作的学生*/
String addSno ="201215109";
String addSname ="yuelin";
char addSsex='男';
int addSage=20;
String addSdept="CS";
/*进行操作的课程*/
String addCno = "8";
String addCname="Java程序设计2";
int addCpno =2;
int addCcredit=4;
/*进行修改的选课记录*/
int addGrade = 80;
/*静态代码只加载一次*/
static {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public Connection getConnection(){
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=StudentSystem";
try {
conn = DriverManager.getConnection(url, "wang", "123");
stmt = conn.createStatement();
System.out.println("连接成功");
} catch (SQLException e) {
System.out.println("连接失败");
e.printStackTrace();
}
return conn;
}
public void services() throws Exception {
Scanner sc = new Scanner(System.in);
while(true){
System.out.println("*----------------------------------------------------*");
System.out.println("请选择你要操作的表：\n1.Student学生表\n2.Course课程表\n3.SC选课成绩表\n4.退出");
String tableChoice = sc.nextLine();
switch (tableChoice){
case "1":
StudentManage(sc);
break;
case "2":
CourseManage(sc);
break;
case "3":
SCManage(sc);
break;
case "4":
conn.close();
stmt.close();
rs.close();
sc.close();
return;
default:
System.out.println("请检查您的输入！");
}
System.out.println("*----------------------------------------------------*");
}
}
public void StudentManage(Scanner sc){
try {
while (true) {
System.out.println("*----------------------------------------------------*");
String sql= "select * from StudentSystem..Student";
rs=stmt.executeQuery(sql);
System.out.println("\tSno\t Sname\t\t\t Ssex Sage Sdept");
while(rs.next()){
String no=rs.getString("Sno");
String name=rs.getString("Sname");
String sex=rs.getString("Ssex");
int age=rs.getInt("Sage");
String dept=rs.getString("Sdept");
System.out.println(no+"\t"+name+sex+"\t"+age+"\t"+dept);
}
while (true) {
System.out.println("*----------------------------------------------------*");
System.out.println("您可以执行以下操作：\n0.查询某学生记录\n1.增加学生记录\n2.删除学生记录\n3.更改学生信息\n4.返回\n请输入您的选择：");
String choice=sc.nextLine();
switch(choice){
case "0":
queryStudent(addSno);
System.out.println("*----------------------------------------------------*");
break;
case "1":
addStudent(addSno);
System.out.println("*----------------------------------------------------*");
break;
case "2":
deleteStudent(addSno);
System.out.println("*----------------------------------------------------*");
break;
case "3":
changeStudent(addSno);
System.out.println("*----------------------------------------------------*");
break;
case "4":
return;
default:
System.out.println("请检查您的输入！");
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private int queryStudentBySno(String Sno){
try {
String sqlCount= "select count(*) from Student where Sno = "+Sno;
rs=stmt.executeQuery(sqlCount);
int flag = 0;
while(rs.next()){
flag += rs.getInt(1);
}
if(flag==0){
return 0;//未添加
}else{
return 1;
}
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
}
private void queryStudent(String Sno) {
try {
if(queryStudentBySno(Sno)>0){
System.out.println("学号为"+Sno+"的同学信息如下");
String sql = "select * from Student where Sno = "+Sno;
rs=stmt.executeQuery(sql);
while (rs.next()){
String no=rs.getString("Sno");
String name=rs.getString("Sname");
String sex=rs.getString("Ssex");
int age=rs.getInt("Sage");
String dept=rs.getString("Sdept");
System.out.println(no+"\t"+name+sex+"\t"+age+"\t"+dept);
}
}else{
System.out.println("学号为"+Sno+"的同学信息还没有添加！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void changeStudent(String Sno){
try {
if(queryStudentBySno(Sno)>0){
String sql = "update Student set Ssex='女' where Sno = "+Sno;
int rsUpdate=stmt.executeUpdate(sql);
System.out.println("学号为"+Sno+"的学生信息已更新！");
}else{
System.out.println("学号为"+Sno+"的同学信息还没有添加！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void deleteStudent(String Sno){
try {
if (queryStudentBySno(Sno)>0) {
String sql = "delete Student where Sno = "+addSno;
int rsDelete=stmt.executeUpdate(sql);
System.out.println("学号为"+addSno+"的学生信息已删除！");
} else {
System.out.println("学号为"+Sno+"的同学信息不存在！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void addStudent(String Sno){
try {
if (queryStudentBySno(Sno)==0) {
String sqlInsert = "Insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('"+addSno+"','"+addSname+"','"+addSsex+"',"+addSage+",'"+addSdept+"')";
int rsInsert=stmt.executeUpdate(sqlInsert);
System.out.println("学号为"+addSno+"的学生信息已添加！");
} else {
System.out.println("学号重复，拒绝添加！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void CourseManage(Scanner sc){
try {
while (true) {
System.out.println("*----------------------------------------------------*");
String sql= "select * from StudentSystem..Course";
rs=stmt.executeQuery(sql);
System.out.println("Cno\t Cname\t\t\t\t\t\t\t\t Cpno\tCcredit");
while(rs.next()){
String no=rs.getString("Cno");
String name=rs.getString("Cname");
int pno=rs.getInt("Cpno");
int credit=rs.getInt("Ccredit");
System.out.println(no+"\t"+name+"\t"+pno+"\t"+credit);
}
while (true) {
System.out.println("*----------------------------------------------------*");
System.out.println("您可以执行以下操作：\n0.查询课程\n1.增加课程记录\n2.删除课程记录\n3.更改课程信息\n4.返回\n请输入您的选择：");
int choice=sc.nextInt();
switch(choice){
case 0:
queryCourse(addCno);
System.out.println("*----------------------------------------------------*");
break;
case 1:
addCourse(addCno);
System.out.println("*----------------------------------------------------*");
break;
case 2:
deleteCourse(addCno);
System.out.println("*----------------------------------------------------*");
break;
case 3:
changeCourse(addCno);
System.out.println("*----------------------------------------------------*");
break;
case 4:
return;
default:
System.out.println("请检查您的输入！");
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void queryCourse(String Cno) {
try {
if(queryCourseByCno(Cno)>0){
System.out.println("课程号为"+Cno+"的课程信息如下");
String sql= "select * from StudentSystem..Course where Cno="+Cno;
rs=stmt.executeQuery(sql);
System.out.println("\tCno\tCname\tCpno Ccredit");
while(rs.next()){
String no=rs.getString("Cno");
String name=rs.getString("Cname");
int pno=rs.getInt("Cpno");
int credit=rs.getInt("Ccredit");
System.out.println(no+"\t"+name+pno+"\t"+credit);
}
}else{
System.out.println("课程号为"+Cno+"的课程不存在！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private int queryCourseByCno(String Cno) {
try {
String sqlCount= "select count(*) from Course where Cno = "+Cno;
rs=stmt.executeQuery(sqlCount);
int flag = 0;
while(rs.next()){
flag += rs.getInt(1);
}
if(flag==0){
return 0;//未添加
}else{
return 1;
}
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
}
private void changeCourse(String Cno) {
try {
if(queryCourseByCno(Cno)>0){
String sql = "update Course set Cname='Java程序设计' where Cno = "+Cno;
int rsUpdate=stmt.executeUpdate(sql);
System.out.println("课程号为"+Cno+"的课程信息已更新！");
}else{
System.out.println("课程号为"+Cno+"的课程信息还没有添加！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void deleteCourse(String Cno) {
try {
if (queryCourseByCno(Cno)>0) {
String sql = "delete Course where Cno = "+Cno;
int rsDelete=stmt.executeUpdate(sql);
System.out.println("课程号为"+Cno+"的课程信息已删除！");
} else {
System.out.println("课程号为"+Cno+"的课程信息不存在！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void addCourse(String Cno) {
try {
if (queryCourseByCno(Cno)==0) {
String sqlInsert = "Insert into Course(Cno,Cname,Cpno,Ccredit) values('"+addCno+"','"+addCname+"','"+addCpno+"','"+addCcredit+"')";
int rsInsert=stmt.executeUpdate(sqlInsert);
System.out.println("课程为"+addCno+"的课程信息已添加！");
} else {
System.out.println("课程重复，拒绝添加！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void SCManage(Scanner sc){
try {
while (true) {
System.out.println("*----------------------------------------------------*");
String sql= "select * from StudentSystem..SC";
rs=stmt.executeQuery(sql);
System.out.println("\tSno\t Cno\t Grade");
while(rs.next()){
String sno=rs.getString("Sno");
String cno=rs.getString("Cno");
int grade=rs.getInt("Grade");
System.out.println(sno+"\t"+cno+"\t"+grade);
}
while (true) {
System.out.println("*----------------------------------------------------*");
System.out.println("您可以执行以下操作：\n0.查询课程\n1.增加学生选课记录\n2.删除学生选课记录\n3.更改学生选课记录\n4.返回\n请输入您的选择：");
int choice=sc.nextInt();
switch(choice){
case 0:
querySC("201215121","1",sc);
System.out.println("*----------------------------------------------------*");
break;
case 1:
addSC(addSno,addCno);
System.out.println("*----------------------------------------------------*");
break;
case 2:
deleteSC(addSno,addCno);
System.out.println("*----------------------------------------------------*");
break;
case 3:
changeSC(addSno,addCno);
System.out.println("*----------------------------------------------------*");
break;
case 4:
return;
default:
System.out.println("请检查您的输入！");
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void querySC(String Sno,String Cno,Scanner sc) {
try {
while (true) {
System.out.println("请您选择您的查询模式：\n1.查询某学生选的某课的成绩\n2.查询某节课的得分情况\n3.查询某学生的得分情况\n4.返回");
String choice = sc.nextLine();
switch (choice) {
case "1":
/*1.学号和课程号同时有*/
querySCBySnoCno(Sno, Cno);
break;
case "2":
/*2.一门课有哪些学生选*/
querySCByCno(Cno);
break;
case "3":
/*3.一个学生选了哪些课*/
querySCBySno(Sno);
break;
case "4":
return;
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
private void querySCBySno(String Sno) {
try {
String sqlCount= "select count(*) from SC where Sno="+Sno;
rs=stmt.executeQuery(sqlCount);
int flag = 0;
while(rs.next()){
flag += rs.getInt(1);
}
if(flag==0){
System.out.println("不存在该记录！");
//不存在
}else{
String sql = "select * from SC,Course where Sno="+Sno+"and Course.Cno=SC.Cno";
rs = stmt.executeQuery(sql);
System.out.println("Cno\t\tCname\tGrade");
while(rs.next()){
String Cno=rs.getString("Cno");
String Cname=rs.getString("Cname");
int grade = rs.getInt("Grade");
System.out.println(Cno+"\t"+Cname+grade);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void querySCByCno(String Cno) {
try {
String sqlCount= "select count(*) from SC where Cno ="+Cno;
rs=stmt.executeQuery(sqlCount);
int flag = 0;
while(rs.next()){
flag += rs.getInt(1);
}
if(flag==0){
System.out.println("不存在该条记录！");
//不存在
}else{
String sql = "select * from SC,Student where Cno ="+Cno+" and Student.Sno=SC.Sno";
rs = stmt.executeQuery(sql);
System.out.println("选了课程号为"+Cno+"的成绩如下：\n\tSno\t Sname\tGrade");
while(rs.next()){
String Sno=rs.getString("Sno");
String Sname = rs.getString("Sname");
int grade = rs.getInt("Grade");
System.out.println(Sno+"\t "+Sname+"\t "+grade);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void querySCBySnoCno(String Sno, String Cno) {
try {
String sqlCount= "select count(*) from SC where Cno ="+Cno+"and Sno="+Sno;
rs=stmt.executeQuery(sqlCount);
int flag = 0;
while(rs.next()){
flag += rs.getInt(1);
}
if(flag==0){
System.out.println("不存在该条记录！");
//不存在
}else{
String sql = "select * from SC where Cno ="+Cno+"and Sno="+Sno;
rs = stmt.executeQuery(sql);
System.out.println("\tSno\t Cno\t Grade");
while(rs.next()){
int grade = rs.getInt("Grade");
System.out.println(Sno+"\t"+Cno+"\t"+grade);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void changeSC(String Sno,String Cno) {
try {
String sqlCount= "select count(*) from SC where Cno ="+Cno+"and Sno="+Sno;
rs=stmt.executeQuery(sqlCount);
int flag = 0;
while(rs.next()){
flag += rs.getInt(1);
}
if(flag==0){
System.out.println("不存在该条记录！");
//不存在
}else{
String sql = "update SC set Grade=85 where Sno = "+Sno+"and Cno="+Cno;
int rsUpdate = stmt.executeUpdate(sql);
System.out.println("学号为"+Sno+"的课程号为"+Cno+"的课程成绩已更新！");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void deleteSC(String Sno,String Cno) {
try {
String sqlCount= "select count(*) from SC where Cno ="+Cno+"and Sno="+Sno;
rs=stmt.executeQuery(sqlCount);
int flag = 0;
while(rs.next()){
flag += rs.getInt(1);
}
if(flag==0){
System.out.println("不存在该条记录！");
//不存在
}else{
String sql = "delete SC where Sno = "+Sno+"and Cno="+Cno;
int rsUpdate = stmt.executeUpdate(sql);
System.out.println("学号为"+Sno+"的课程号为"+Cno+"的选课记录已删除");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void addSC(String Sno,String Cno) {
try {
String sqlCount= "select count(*) from SC where Cno ="+Cno+"and Sno="+Sno;
rs=stmt.executeQuery(sqlCount);
int flag = 0;
while(rs.next()){
flag += rs.getInt(1);
}
if(flag==1){
System.out.println("重复添加，拒绝访问！");
//不存在
}else{
String sql = "Insert into SC(Sno,Cno,Grade) values("+Sno+","+Cno+ ","+addGrade+")";
int rsUpdate = stmt.executeUpdate(sql);
System.out.println("学号为"+Sno+"的课程号为"+Cno+"的选课记录已添加");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
