import java.sql.*;
import java.util.Scanner;
public class Stu_System {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
boolean bool=true;
while(bool) {
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("**** 请输入你要进行的操作(1-6) ****");
if (!sc.hasNextInt()) {
System.out.println("请输入数字\n");
} else {
int flag = sc.nextInt();
switch (flag) {
case 1: addstu();break;
case 2: inquirestu();break;
case 3: changestu();break;
case 4: deletestu();break;
case 5: printstu();break;
case 6: bool = false;break;
default:System.out.println("请输入1-6");
break;
}
}
}
sc.close();
System.out.println("系统关闭");
}
//打印学生信息
private static void printstu() {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
String sql = "select * from user";
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String clas = rs.getString("class");
System.out.println("学号:"+id+" 姓名:"+name+" 班级:"+clas);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.close(rs,pst,conn);
}
}
//删除学生信息
private static void deletestu() {
Scanner sc = new Scanner(System.in);
System.out.println("删除的学生学号:");
if(!sc.hasNextInt())
{
System.out.println("未输入数字\n");
}else {
int ID = sc.nextInt();
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
String sql2 = "select * from user where id = ?";
String sql = "delete from user where id = ?";
pst = conn.prepareStatement(sql);
pst.setInt(1, ID);
int i = pst.executeUpdate();
if (i!=0) {
System.out.println("删除成功");
} else {
System.out.println("学号不存在");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.close(rs,pst,conn);
}
}
}
//修改学生信息
private static void changestu() {
Scanner sc = new Scanner(System.in);
System.out.println("修改的学生学号:");
if(!sc.hasNextInt())
{
System.out.println("未输入数字\n");
}else {
int ID = sc.nextInt();
System.out.println("学生学号:");
int id2 = sc.nextInt();
System.out.println("学生姓名:");
String name = sc.next();
System.out.println("学生班级:");
String clas = sc.next();
Connection conn = null;
PreparedStatement pst = null;
try {
conn = DBUtils.getConn();
String sql = "update user set id=? ,name = ? ,class = ? where id =?";
pst = conn.prepareStatement(sql);
pst.setInt(1, id2);
pst.setString(2, name);
pst.setString(3, clas);
pst.setInt(4, ID);
boolean rs = pst.execute();
if (!rs) {
System.out.println("操作成功");
} else {
System.out.println("操作失败");
}
} catch (SQLException e) {
System.out.println("学号重复");
}finally {
DBUtils.close(pst,conn);
}
}
}
//查找学生信息
private static boolean inquirestu() {
Scanner sc = new Scanner(System.in);
System.out.println("查找的学生学号:");
if(!sc.hasNextInt())
{
System.out.println("未输入数字\n");
}else {
int ID = sc.nextInt();
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = DBUtils.getConn();
String sql = "select * from user where id = ?";
pst = conn.prepareStatement(sql);
pst.setInt(1, ID);
rs = pst.executeQuery();
boolean b = rs.next();
if (b) {
int id = rs.getInt("id");
String name = rs.getString("name");
String clas = rs.getString("class");
System.out.println("学号:"+id+" 姓名:"+name+" 班级:"+clas);
} else {
System.out.println("学号不存在");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.close(rs,pst,conn);
}
}
return false;
}
//添加学生信息
private static void addstu() {
Scanner sc = new Scanner(System.in);
System.out.println("学生学号:");
if(!sc.hasNextInt())
{
System.out.println("未输入数字类型\n");
}else {
int ID = sc.nextInt();
System.out.println("学生姓名:");
String name = sc.next();
System.out.println("学生班级:");
String clas = sc.next();
Connection conn = null;
PreparedStatement pst = null;
try {
conn = DBUtils.getConn();
String sql = "insert into user values (?,?,?)";
pst = conn.prepareStatement(sql);
pst.setInt(1, ID);
pst.setString(2, name);
pst.setString(3, clas);
boolean rs = pst.execute();
if (!rs) {
System.out.println("操作成功");
} else {
System.out.println("操作失败");
}
} catch (SQLException e) {
System.out.println("学号重复");
}finally {
DBUtils.close(pst,conn);
}
}
}
}
工具类DBUtils
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
public class DBUtils {
private static String jdbc;
private static String username;
private static String password;
static {
try {
Properties pro = new Properties();
ClassLoader classLoader = DBUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
pro.load(new FileReader(path));
jdbc = pro.getProperty("jdbc");
username = pro.getProperty("username");
password = pro.getProperty("password");
Class.forName("com.mysql.jdbc.Driver");
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConn() throws SQLException {
return DriverManager.getConnection(jdbc, username, password);
}
public static void close(Statement stat, Connection conn) {
if( stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs, PreparedStatement stat, Connection conn) {
if( rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
try {
Connection conn = DBUtils.getConn();
if (conn != null) {
System.out.println("连接成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}