[Java] 纯文本查看 复制代码
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Scanner;
/*
* author scb
*
* ps:谨以此程序纪念曾经带班的黄云老师
*
*/
public class Test1 {
public static void main(String[] args) throws SQLException {
//注册驱动 获取连接
Connection con = JDBCUtils.getConnection();
//获取sql语句执行平台
PreparedStatement pst = con.prepareStatement("SELECT * FROM student");
//执行sql语句
ResultSet rs = pst.executeQuery();
//处理结果集 封装成学生对象 存进集合
ArrayList<Student> alist = new ArrayList<>();
while(rs.next()){
alist.add(new Student(rs.getInt("id"), rs.getString("NAME"), rs.getInt("age"), rs.getDouble("score")));
}
//两种方式遍历 打印
// printStudentMessage(alist);
//程序启动方法
run(con, alist);
}
private static void run(Connection con, ArrayList<Student> alist)
throws SQLException {
while(true){
System.out.println("----------------学生信息表---------------");
System.out.println("1.增添信息 2.删除信息 3.修改信息 4.查询信息 5.退出");
System.out.println("请输入操作编号[1-5]");
Scanner sc = new Scanner(System.in);
int choose = sc.nextInt();
switch (choose) {
case 1:
//添加学生信息
addStudent(con, alist);
case 2:
//删除学生信息
deleteStudent(con, alist);
case 3:
//修改学生信息
updateStudent(con, alist);
case 4:
System.out.println("您选择的是查询所有学生信息的操作");
//查询所有学生信息
selectStudent(con,alist);
case 5:
//退出程序
System.out.println("谢谢使用！请尽快购买正版。");
System.exit(0);
}
}
}
private static void selectStudent(Connection con, ArrayList<Student> alist) throws SQLException {
//没有使用连接池 所以没有实现题目要求的三种方式的打印
PreparedStatement ps = con.prepareStatement("SELECT * FROM student");
ResultSet rst = ps.executeQuery();
System.out.println("编号\t姓名\t年龄\t分数");
while(rst.next()){
System.out.println(rst.getInt("id")+"\t"+rst.getString("name")+"\t"+rst.getInt("age")+"\t"+rst.getDouble("score"));
}
System.out.println("是否退回主界面？（Y/N）");
Scanner sc = new Scanner(System.in);
String answer = sc.next();
if (answer.equals("Y")) {
run(con, alist);
}
}
private static void deleteStudent(Connection con, ArrayList<Student> alist)
throws SQLException {
System.out.println("您选择的是删除学生信息操作");
ArrayList<Integer> idlist = idList(alist);
selectStudent(con, alist);
Scanner sc = new Scanner(System.in);
System.out.println("请输入您需要删除的学生信息的编号");
int id = sc.nextInt();
if (idlist.contains(id)) {
PreparedStatement ps = con.prepareStatement("DELETE FROM student WHERE id = ?");
ps.setObject(1, id);
//询问客户是否确定执行删除操作
System.out.println("是否执行删除操作?（Y/N）");
String answer = sc.next();
if (answer.equals("Y")) {
ps.executeUpdate();
System.out.println("学生信息删除成功");
}
//退回到主界面
run(con, alist);
}else{
System.out.println("不存在该id编号的学生信息");
//退回到主界面
run(con, alist);
}
}
private static void updateStudent(Connection con, ArrayList<Student> alist)
throws SQLException {
System.out.println("您选择的是修改学生信息操作");
//创建集合存储学生id编号
ArrayList<Integer> idlist = idList(alist);
selectStudent(con, alist);
Scanner sc = new Scanner(System.in);
System.out.println("请输入需要修改的学生id编号");
int id = sc.nextInt();
System.out.println("请输入学生姓名");
String name = sc.next();
System.out.println("请输入学生年龄");
int age = sc.nextInt();
System.out.println("请输入学生分数");
double score = sc.nextDouble();
//如果存在该id号就进行修改操作
if (idlist.contains(id)) {
PreparedStatement ps = con.prepareStatement("UPDATE student SET name = ?,age = ?," +
"score = ? WHERE id = ?");
ps.setObject(1,name);
ps.setObject(2,age);
ps.setObject(3,score);
ps.setObject(4, id);
ps.executeUpdate();
System.out.println("学生信息修改成功");
//退回到主界面
run(con, alist);
}else{
System.out.println("不存在该id编号的学生信息");
//退回到主界面
run(con, alist);
}
}
private static ArrayList<Integer> idList(ArrayList<Student> alist) {
ArrayList<Integer> idlist = new ArrayList<>();
for (Student stu : alist) {
idlist.add(stu.getId());
}
return idlist;
}
public static void addStudent(Connection con, ArrayList<Student> alist)
throws SQLException {
System.out.println("您选择的是添加学生信息操作");
//创建集合 存储已有学生信息
ArrayList<String> namelist = new ArrayList<>();
ArrayList<Integer> agelist = new ArrayList<>();
for (Student stu : alist) {
namelist.add(stu.getName());
agelist.add(stu.getAge());
}
Scanner sc = new Scanner(System.in);
System.out.println("请输入学生姓名");
String name = sc.next();
System.out.println("请输入学生年龄");
int age = sc.nextInt();
System.out.println("请输入学生分数");
double score = sc.nextDouble();
//判断表中是否已经存在该学生信息 若年龄和名字相同 则为同一人
if (!namelist.contains(name)|| !agelist.contains(age)) {
PreparedStatement ps = con.prepareStatement("INSERT INTO student(NAME,age,score) VALUES (?,?,?)");
ps.setObject(1, name);
ps.setObject(2, age);
ps.setObject(3, score);
ps.executeUpdate();
System.out.println("学生信息添加成功");
//退回到主界面
run(con, alist);
}else{
System.out.println("学生信息已经存在！");
//退回到主界面
run(con, alist);
}
}
private static void printStudentMessage(ArrayList<Student> alist) {
for (Student student : alist) {
System.out.println(student);
}
System.out.println("---分------割-------线------");
Iterator<Student> it = alist.iterator();
while(it.hasNext()){
System.out.println(it.next());
}
}
}
[AppleScript] 纯文本查看 复制代码
public class Student {
private int id;
private String name;
private int age;
private double score;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(int id, String name, int age, double score) {
super();
this.id = id;
this.name = name;
this.age = age;
this.score = score;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age
+ ", score=" + score + "]";
}
}
[AppleScript] 纯文本查看 复制代码
import java.sql.Connection;
import java.sql.DriverManager;
public class JDBCUtils {
private static Connection con;
static{
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase",
"root", "123");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println(e + "数据连接失败");
}
}
public static Connection getConnection(){
return con;
}
}