package cn.sxb.day1107;
public class Student {
private int number;
private String name;
private int grade;
// public Student(){
//
// }
public Student(int number, String name, int grade) {
this.number = number;
this.name = name;
this.grade = grade;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
@Override
public String toString() {
return "Student [number=" + number + ", name=" + name + ", grade=" + grade + "]";
}
}
</pre><p></p><pre name="code" class="java">package cn.sxb.day1107;
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.day1107;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
public class KeyBoardUtil {
/**
* 使用io流,获取从键盘输入到程序。使用Input(从外部设备到程序为输入,程序到外部设备为输出);
* System.in(out)标准系统输入输出中,不需要关闭流,由系统自动关闭。否则在后面测试类中会回报流关闭错误
*/
public static String getStringKeyBoard() {
String name = "";
InputStreamReader isr = null;
BufferedReader br = null;
try {
isr = new InputStreamReader(System.in);
br = new BufferedReader(isr);
name = br.readLine();
// int number=Integer.parseInt(name);把String类型转换成int型
} catch (IOException e) {
e.printStackTrace();
}
return name;
}
public static int getintKeyBoard() {
while (true) {
int number = 0;
InputStreamReader isr = null;
BufferedReader br = null;
try {
isr = new InputStreamReader(System.in);
br = new BufferedReader(isr);
String s = br.readLine();// 在缓冲流中用readLine(),每行读成String型。在转换成int型
number = Integer.parseInt(s);
return number;
} catch (IOException e) {
e.printStackTrace();
} catch (NumberFormatException a) {
System.out.println("请输入正确数值格式");
}
}
}
}
package cn.sxb.day1107;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DBDemo extends UtilDB {
/**
* 单例模式:
* 1.构造方法私有化
* 2.设置静态私有 实例对象为空
* 3.定义公共静态方法返回实例
*/
private static DBDemo db = null;
public static DBDemo getInstance(){
if(db == null){
db = new DBDemo();
}
return db;
}
private DBDemo(){
}
// public DBDemo() {
//
// }
/**
* 业务层添加学生
*/
public Student addStudentByKeyBoard(){
System.out.println("请输入学生学号:");
int number = KeyBoardUtil.getintKeyBoard();
System.out.println("请输入学生姓名:");
String name = KeyBoardUtil.getStringKeyBoard();
System.out.println("请输入学生成绩:");
int grade = KeyBoardUtil.getintKeyBoard();
Student student = new Student(number,name,grade);
return student;
}
/**
* 添加学生 + "(1001,'小明',89)," + "(1002,'小丽',90)," + "(1003,'张三',95)," +
* "(1004,'李四',89)";
*/
public void addStudent(Student student) {
// Student student = addStudentByKeyBoard();
String sql = "insert into tdb_ks(number,name,grade)values(?,?,?)";
Connection connect = null;
PreparedStatement ps = null;
try {
connect = getConnection();
ps = connect.prepareStatement(sql);
ps.setInt(1, student.getNumber());
ps.setString(2, student.getName());
ps.setInt(3, student.getGrade());
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 打印所有考生信息
*/
public void printMessageAllStudent(){
String sql = "SELECT * FROM tdb_ks";
Connection connect = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connect = getConnection();
ps = connect.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
int number = rs.getInt("number");
String name = rs.getString("name");
int grade = rs.getInt("grade");
System.out.println(number+"\t"+name+"\t"+grade);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(rs != null)
rs.close();
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 根据姓名查找学生信息
*/
public void findStudentMessageByName(){
String sql = "SELECT * FROM tdb_ks WHERE name = ?";
System.out.println("请输入查找的学生姓名:");
String n = KeyBoardUtil.getStringKeyBoard();
System.out.println("学号"+"\t"+"name"+"\t"+"grade");
Connection connect = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connect = getConnection();
ps = connect.prepareStatement(sql);
ps.setString(1, n);
rs = ps.executeQuery();//执行sql查询语句
if(rs.next()){
int number = rs.getInt("number");
String name = rs.getString("name");
int grade = rs.getInt("grade");
System.out.println(number+"\t"+name+"\t"+grade);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 根据姓名更改考生信息
* @param student
* @param name
*/
public void updateStudentByName(Student student,String name){
String sql = "UPDATE tdb_ks SET number = ? WHERE name = ?";
Connection connect = null;
PreparedStatement ps = null;
try {
connect = getConnection();
ps = connect.prepareStatement(sql);
ps.setInt(1, student.getNumber());
ps.setString(2, name);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 根据考号删除考生
* @param num
*/
public void deleteStudentById(int num){
Connection connect = null;
PreparedStatement ps = null;
try {
String sql = "DELETE FROM tdb_ks WHERE number = ?";
connect = getConnection();
ps = connect.prepareStatement(sql);
ps.setInt(1, num);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 查询所有学生总成绩
* @return
*/
public int getSum(){
String sql = "SELECT sum(grade) FROM tdb_ks";
Connection connect = null;
PreparedStatement ps = null;
ResultSet rs = null;
int sumGrade = 0;
try {
connect = getConnection();
ps = connect.prepareStatement(sql);
rs = ps.executeQuery();
if(rs.next()){
sumGrade = rs.getInt("sum(grade)");//"sum(grade)"可以写成1
// System.out.println(sumGrade);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
return sumGrade;
}
/**
* 查询高于平均分的所有学生
* @param args
*/
public List<Student> getAllStudent(){
ArrayList<Student> lists = new ArrayList<Student>();
String sql = " SELECT * FROM tdb_ks WHERE grade > (SELECT AVG(grade) FROM tdb_ks)";
Connection connect = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connect = getConnection();
ps = connect.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
int number = rs.getInt("number");
String name = rs.getString("name");
int grade = rs.getInt("grade");
Student stu = new Student(number,name,grade);
lists.add(stu);
}
} catch (SQLException e) {
e.printStackTrace();
}
return lists;
}
public static void main(String[] args){
DBDemo db = new DBDemo();
// Student stu = new Student(1001,"小明",89);
//db.addStudent(stu);
// db.addStudent(new Student(1002,"小丽",90));
// db.addStudent(new Student(1003,"张三",95));
// db.addStudent(new Student(1004,"李四",89));
// db.printMessageAllStudent();
// db.deleteStudentById(1001);
// db.findStudentMessageByName();
// Student student = new Student(1007,"张三",95);
// db.updateStudentByName(student, "张三");
// int sum = db.getSum();
// System.out.println(sum);
// List<Student> studentList = db.getAllStudent();
// for(Student stu : studentList){
// System.out.println(stu);
}
// }
}
package cn.sxb.day1107;
import java.util.List;
import java.util.Scanner;
public class StudentTest {
public static void main(String[] args) {
StudentTest s = new StudentTest();
s.startSystem();
}
public void startSystem(){
System.out.println("欢迎进入学生管理系统!");
for(;;){
System.out.println("<操作命令: 1 添加考生 , 2 显示考生信息 ,3根据姓名查找学生信息, 4 根据姓名修改考生信息 , 5 根据考号删除考生, "
+ "6 查询考生总成绩 , 7 查询考生平均分 ,8 退出系统>");
System.out.println("输入操作命令:");
int code = KeyBoardUtil.getintKeyBoard();
switch(code){
case 1:
DBDemo.getInstance().addStudent(DBDemo.getInstance().addStudentByKeyBoard());
break;
case 2:
DBDemo.getInstance().printMessageAllStudent();
break;
case 3:
DBDemo.getInstance().findStudentMessageByName();
break;
case 4://先定义一个方法查询到要修改的学生,再返回学生对象后传入修改的方法中
System.out.println("请输入要修改的学生姓名:");
String name = KeyBoardUtil.getStringKeyBoard();
DBDemo.getInstance().updateStudentByName(DBDemo.getInstance().addStudentByKeyBoard(), name);
break;
case 5:
System.out.println("请输入要删除的学生学号:");
Scanner scanner = new Scanner(System.in);
int num = scanner.nextInt();
DBDemo.getInstance().deleteStudentById(num);
break;
case 6:
int sum = DBDemo.getInstance().getSum();
System.out.println(sum);
break;
case 7:
List<Student> studentList = DBDemo.getInstance().getAllStudent();
for(Student stu : studentList){
System.out.println(stu);
}
break;
case 8:
System.out.println("你已退出本系统!");
System.exit(0);
}
}
}
}
注:1.在后面操作数据库时可能不会一次性操作全部功能,所以需每个功能随使用随关闭连接。
2.本系统采用的是饱汉式:
优点:当类DBDemo被加载的时候,静态变量static的db未被创建并分配内存空间,当getInstance方法第一次被调用时,初始化db变量,并分配内存,因此在某些特定条件下会节约了内存;
缺点:并发环境下很可能出现多个DBDemo实例。