package cn.sxb.day1102;
public class Student {
private String name;
private int number;
private int age;
private int sex;//0:男 1:女
public Student(){
}
public Student(String name, int number, int age, int sex) {
this.name = name;
this.number = number;
this.age = age;
this.sex = sex;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
}
package cn.sxb.day1102;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class UtilDB {
private static final String URL = "jdbc:mysql://192.168.5.17:3306/db1606";
private static final String USER = "test";
private static final String PASSWORD = "123321";
/**
* 构造方法加载驱动
*/
public UtilDB() {
try {
Class.forName("com.mysql.jdbc.Driver");// 加载mysql驱动
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 建立连接数据库
*/
public Connection getConnection() {
Connection connect = null;
try {
connect = DriverManager.getConnection(URL, USER, PASSWORD);//试图建立到给定数据库URL的连接
if (connect == null) {
System.out.println("数据库连接失败");
} else {
System.out.println("数据库连接成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
return connect;
}
/**
* 关闭数据库连接
*/
public void closeConnection(Connection connect) {
try {
if (connect != null)
connect.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package cn.sxb.day1102;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class StudentDB extends UtilDB {
public StudentDB() {
}
/**
* 创建表
*/
public void createTable() {
Statement statement = null;
Connection connect = getConnection();
try {
String sql = "create table studentThree (" + "id int(11) not null auto_increment primary key," // 主键
+ "name varchar(10) not null," // 姓名
+ "number int(4) not null," // 学号
+ "age int(3)," // 年龄
+ "sex int(1)" // 性别
+ ")";
statement = connect.createStatement();
statement.execute(sql);//执行给定的sql语句
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (statement != null)
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 向表中添加学生
*/
public void addStudent() {
Student stu = new Student();
Scanner scanner = new Scanner(System.in);
System.out.println("请输入学生姓名:");
String s = scanner.next();
stu.setName(s);
System.out.println("请输入学生学号:");
stu.setNumber(scanner.nextInt());
System.out.println("请输入学生年龄:");
stu.setAge(scanner.nextInt());
System.out.println("请输入学生性别:");
stu.setSex(scanner.nextInt());
Connection connect = getConnection();
PreparedStatement ps = null;//sql语句被预编译并存储在PreparedStatement对象中
String sql = "insert into studentThree(name,number,age,sex)values(?,?,?,?)";
try {
ps = connect.prepareStatement(sql); // 执行SQL语句
ps.setString(1, stu.getName());
ps.setInt(2, stu.getNumber());
ps.setInt(3, stu.getAge());
ps.setInt(4, stu.getSex());
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 显示学生
*/
public void showStudent(){
Connection connect = getConnection();
Statement statement = null;
try {
statement = connect.createStatement();
String sql = "select * from studentThree";
ResultSet rs = statement.executeQuery(sql);//执行给定的sql语句,单个ResultSet对象
//ResultSet表示数据库结果集的数据表,通常通过执行查询数据库的语句生成。
System.out.println("姓名\t学号\t年龄\t性别");
while (rs.next()) {
String name = rs.getString("name");
int number = rs.getInt("number");
int age =rs.getInt("age");
int sex = rs.getInt("sex");
System.out.println(name + "\t" + number + "\t" + age + "\t" + sex);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (statement != null)
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 查找学生
*/
public void selectStudent(){
System.out.println("请输入查找的学生学号");
Scanner scanner = new Scanner(System.in);
int num = scanner.nextInt();
Connection connect = getConnection();
PreparedStatement ps = null;
try {
String sql = "select * from studentThree where number = ?";
ps = connect.prepareStatement(sql);
ps.setInt(1,num);
ResultSet rs = ps.executeQuery();// 返回结果集
while (rs.next()) {
String name = rs.getString("name");
int number = rs.getInt("number");
int age =rs.getInt("age");
int sex = rs.getInt("sex");
System.out.println("你要查找的学生是:"+name);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 删除学生
*/
public void deleteStudent(){
System.out.println("请输入删除的学生学号:");
Scanner scanner = new Scanner(System.in);
int num = scanner.nextInt();
Connection connect = getConnection();
PreparedStatement ps = null;
try {
String sql = "delete from studentThree where number = ?";
ps = connect.prepareStatement(sql);
ps.setInt(1,num);
ps.execute();
System.out.println("删除成功");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
// public static void main(String[] args){
// StudentDB sdb = new StudentDB();
// sdb.createTable();
sdb.keyBoard();
sdb.addStudent(stu);
sdb.showStudent();
sdb.selectStudent();
sdb.deleteStudent();
//
// }
}
package cn.sxb.day1102;
import java.util.Scanner;
/**
* 项目名称: 《学生信息管理系统》 英文:StudentManagerSystem
版本号: V1.0
功能描术: 学生信息管理系统具有能对学生信息进行显示,添加,删除,查找(根据学号查找),退出功能.
要求:
1>.创建一个学生类 Student至少包含:姓名(name)、学号(number)两个属性;
在Student类中封装操作方法:
添加学生(addStudent)、显示学生列表(showAllStudent)、删除学生(deleteStudent)
查找学生(findStudentByNumber)、退出系统(exit)
2>在添加,删除学生时要求检查该学生是否存在,如果不存在需给出提示。
3>.通过输入命令进行交互操作,控制台输出效果如下:
===== 欢迎进入《学生信息管理系统》! ======
1 添加学生,2 显示学生列表,3 删除学生,4 查找学生,5 退出
<输入操作命令:>
1
======添加学生信息操作=========
请输入姓名:
小明
请输入学号:
1001
======添加成功 ! 学生姓名是 :小明,学号是 : 1001=========
<输入操作命令:>
2
姓名 学号
小明 1001
<输入操作命令:>
4
======查找学生信息操作=========
请输入学号:
1001
===您查找的学生是:小明====
<输入操作命令:>
3
======删除学生信息操作=========
请输入学号:
1001
====删除学生成功!学号是:1001的学生已从学生管理系统中删除====
<输入操作命令:>
5
==== 您已经退出《学生信息管理系统》 ====
*
*
*/
public class StudentTest {
public static void main(String[] args) {
StudentDB sdb = new StudentDB();
System.out.println("=====欢迎进入学生管理系统=====");
System.out.println(" 1 添加学生,2 显示学生列表,3 删除学生,4 查找学生,5 退出");
Scanner scanner = new Scanner(System.in);
System.out.println("=====添加学生信息操作=====");
for(;;){
System.out.println("<请输入操作命令:>");
int num =scanner.nextInt();
switch(num){
case 1:
sdb.addStudent();
break;
case 2:
sdb.showStudent();
break;
case 3:
sdb.deleteStudent();
break;
case 4:
sdb.selectStudent();
break;
case 5:
System.out.println("你已经退出系统!");
System.exit(0);
break;
default :
break;
}
}
}
}