使用建立一个Student类，利用数据库来存储多个Student，写完一个方法在main中写一段测试代码，运行以保证目前所做工作的正确性。有以下方法：
1）add(Student stu)：可以向其中增加新的学生，并保存在数据库中
测试add方法是否正确：用add方法向数据库增加一个新的学生，然后在数据库的图形管理界面中查询，确认是否增加
2）dispAll()：可以显示所有的学生信息
3）findById(long id)：可以按照学号来查找，并显示符合条件的学生信息，查无该人的话显示错误信息
4）findByName(String name)：可以按照姓名查找学生，找到后显示其信息，查无此人显示错误信息
5）delById(long id)：可以按照id删除学生的信息，然后显示找到该人。若查无此人，显示相应的错误信息
6）sortByXXX：可以按照指定的字段排序，并显示排序后的信息
1 public class Student { 2
3 private long id; 4 private String name; 5 private String major; 6 private String clas; 7 private double mathScore; 8 private double osScore; 9
10 private double javaScore; 11
12 public void setId(long id){ 13 this.id=id; 14 } 15 public void setName(String name){ 16 this.name=name; 17 } 18 public void setMajor(String major) { 19 this.major = major; 20 } 21 public void setClas(String clas) { 22 this.clas = clas; 23 } 24 public void setMathScore(double mathScore) { 25 this.mathScore = mathScore; 26 } 27 public void setOsScore(double osScore) { 28 this.osScore = osScore; 29 } 30 public void setJavaScore(double javaScore){ 31 this.javaScore=javaScore; 32 } 33 public long getId() { 34 return id; 35 } 36 public String getName() { 37 return name; 38 } 39 public String getMajor() { 40 return major; 41 } 42 public String getClas() { 43 return clas; 44 } 45 public double getMathScore() { 46 return mathScore; 47 } 48 public double getOsScore() { 49 return osScore; 50 } 51 public double getJavaScore() { 52 return javaScore; 53 } 54 public String show(){ 55 String str=id+"\t"+name+"\t"+major+"\t"+clas+"\t"+mathScore+"\t"+osScore+"\t"+javaScore; 56 return str; 57 } 58 }
1 import java.sql.ResultSet; 2 import java.sql.SQLException; 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.Statement; 6 import java.util.Scanner; 7 import java.sql.PreparedStatement; 8
9 public class StuInfoManage { 10
11 //连接数据库
12 String driver="org.hsqldb.jdbcDriver"; 13 String url="jdbc:hsqldb:hsql://localhost/"; 14 String user="SA"; 15 String pass=""; 16 private Connection conn; 17 public StuInfoManage() throws ClassNotFoundException{ 18 String sql = "create table if not exists students(id bigint,name varchar(20),"; 19 sql+= "major varchar(10),class varchar(10),math double,os double,java double)"; 20 try { 21 if(conn!=null) return;//已经初始化
22 Class.forName("org.hsqldb.jdbcDriver");// 动态加载mysql驱动
23 conn = DriverManager.getConnection(url,user,pass);//建立和数据库的连接
24 Statement stmt = conn.createStatement(); 25 stmt.executeUpdate(sql); 26 stmt.close(); 27 } catch (SQLException e) { 28 e.printStackTrace(); 29 } 30 } 31
32
33
34
35
36 public void close() throws SQLException{ 37 if(conn!=null) conn.close(); 38 } 39
40
41
42
43
44 public void add(Student stu){ 45 String sql="insert into students(id,name,major,class,math,os,java)"+"values(?,?,?,?,?,?,?)"; 46 try { 47 PreparedStatement pstmt=(PreparedStatement) conn.prepareStatement(sql); 48 pstmt.setLong(1, stu.getId()); 49 pstmt.setString(2,stu.getName()); 50 pstmt.setString(3,stu.getMajor()); 51 pstmt.setString(4,stu.getClas()); 52 pstmt.setDouble(5,stu.getMathScore()); 53 pstmt.setDouble(6,stu.getOsScore()); 54 pstmt.setDouble(7,stu.getJavaScore()); 55 pstmt.executeUpdate(); 56 pstmt.close(); 57 } catch (SQLException e) { 58 e.printStackTrace(); 59 } 60 } 61
62
63
64
65
66 public void delById(long id) { 67 String sql="delete from students where id="+id; 68 try { 69 Statement stmt=conn.createStatement(); 70 stmt.executeUpdate(sql); 71 stmt.close(); 72 } catch (SQLException e) { 73 e.printStackTrace(); 74 } 75 } 76
77
78
79
80
81 public void findById(long id){ 82 String sql="select * from students where id="+id; 83 try { 84 Statement stmt=conn.createStatement(); 85 ResultSet rs=stmt.executeQuery(sql); 86
87 while(rs.next()){ 88 long id1=rs.getLong("id"); 89 String name=rs.getString("name"); 90 String major=rs.getString("major"); 91 String clas=rs.getString("class"); 92 double mathScore=rs.getDouble("math"); 93 double osScore=rs.getDouble("os"); 94 double javaScore=rs.getDouble("java"); 95 Student stu=new Student(); 96 stu.setId(id1);stu.setName(name); 97 stu.setMajor(major);stu.setClas(clas); 98 stu.setMathScore(mathScore);stu.setOsScore(osScore);stu.setJavaScore(javaScore); 99 System.out.println("学号\t 姓名 专业 班级:\tmath:\t os:\tjava:"); 100 System.out.println(stu.show()); 101 } 102 rs.close(); 103 stmt.close(); 104 } catch (SQLException e) { 105 e.printStackTrace(); 106 } 107 } 108
109
110
111
112
113 public void findByName(String name){ 114 String sql="select * from students where name=?"; 115 try { 116 PreparedStatement pstmt=(PreparedStatement) conn.prepareStatement(sql); 117 pstmt.setString(1, name); 118 ResultSet rs=pstmt.executeQuery(); 119 while(rs.next()) { 120 long id1=rs.getLong("id"); 121 String name1=rs.getString("name"); 122 String major=rs.getString("major"); 123 String clas=rs.getString("class"); 124 double mathScore=rs.getDouble("math"); 125 double osScore=rs.getDouble("os"); 126 double javaScore=rs.getDouble("java"); 127 Student stu=new Student(); 128 stu.setId(id1);stu.setName(name1); 129 stu.setMajor(major);stu.setClas(clas); 130 stu.setMathScore(mathScore); 131 stu.setOsScore(osScore); 132 stu.setJavaScore(javaScore); 133 System.out.println("学号\t 姓名 专业 班级:\tmath:\t os:\tjava:"); 134 System.out.println(stu.show()); 135 } 136 if(!rs.next()) { 137 System.out.println("error"); 138 } 139 rs.close(); 140 pstmt.close(); 141 } catch (SQLException e) { 142 e.printStackTrace(); 143 } 144 } 145
146
147
148
149
150
151 public void sort(String course){ 152 String sql = "select * from students"; 153 try { 154 Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); 155 ResultSet rs = stmt.executeQuery(sql);// executeQuery会返回结果的集合，否则返回空值
156 rs.last();//移动到最后一行
157 int total=rs.getRow();//得到总行数
158 Student[] stus=new Student[total]; 159 int index=0; 160 rs.beforeFirst();//游标指向第一行前
161 while (rs.next()) { 162 long id1=rs.getLong("id"); 163 String name1=rs.getString("name"); 164 String major=rs.getString("major"); 165 String clas=rs.getString("class"); 166 double mathScore=rs.getDouble("math"); 167 double osScore=rs.getDouble("os"); 168 double javaScore=rs.getDouble("java"); 169 Student stu=new Student(); 170 stu.setId(id1);stu.setName(name1); 171 stu.setMajor(major);stu.setClas(clas); 172 stu.setMathScore(mathScore);stu.setOsScore(osScore);stu.setJavaScore(javaScore); 173 stus[index]=stu; 174 index++; 175 } 176 courseChoose(stus,course); 177 System.out.println("学号\t 姓名 专业 班级:\tmath:\t os:\tjava:"); 178 for(int m=0;m<stus.length;m++){ 179 System.out.println(stus[m].getId() + "\t" + stus[m].getName()+"\t" + stus[m].getMajor() + "\t" + stus[m].getClas()+"\t" +stus[m].getMathScore()+ "\t" + stus[m].getOsScore()+"\t"+stus[m].getJavaScore());// 入如果返回的是int类型可以用getInt()
180 } 181 rs.close(); 182 stmt.close(); 183 } catch (SQLException e) { 184 e.printStackTrace(); 185 } 186 } 187
188
189
190
191 public void courseChoose(Student[] stus,String course){ 192 if(course.equals("math")){ 193 for(int i = 0 ; i <stus.length-1 ; i++){ 194 for(int j = i+1 ; j < stus.length ; j++){ 195 Student stu=new Student(); 196 if(stus[i].getMathScore() <stus[j].getMathScore()){ 197 stu = stus[i]; 198 stus[i] = stus[j]; 199 stus[j] = stu; 200 } 201 } 202 } 203 } 204 else if(course.equals("os")){ 205 for(int i = 0 ; i <stus.length-1 ; i++){ 206 for(int j = i+1 ; j < stus.length ; j++){ 207 Student stu=new Student(); 208 if(stus[i].getOsScore() <stus[j].getOsScore()){ 209 stu = stus[i]; 210 stus[i] = stus[j]; 211 stus[j] = stu; 212 } 213 } 214 } 215
216 } 217 else if(course.equals("java")){ 218 for(int i = 0 ; i <stus.length-1 ; i++){ 219 for(int j = i+1 ; j < stus.length ; j++){ 220 Student stu=new Student(); 221 if(stus[i].getJavaScore()<stus[j].getJavaScore()){ 222 stu = stus[i]; 223 stus[i] = stus[j]; 224 stus[j] = stu; 225 } 226 } 227 } 228 } 229 } 230
231
232
233
234 public void dispAll(){ 235 String sql = "select * from students"; 236 try { 237 Statement stmt = conn.createStatement(); 238 ResultSet rs = stmt.executeQuery(sql);// executeQuery会返回结果的集合，否则返回空值
239 System.out.println("学号\t 姓名 专业 班级:\tmath:\t os:\tjava:"); 240 while (rs.next()) { 241 System.out 242 .println(rs.getLong(1) + "\t" + rs.getString(2)+"\t"
243 + rs.getString(3) + "\t" + rs.getString(4)+"\t" + rs.getDouble(5) 244 + "\t" + rs.getDouble(6)+"\t"+rs.getDouble(7));// 如果返回的是int类型可以用getInt()
245 } 246 rs.close(); 247 stmt.close(); 248 } catch (SQLException e) { 249 e.printStackTrace(); 250 } 251 } 252
253
254
255
256 public void menu(){ 257 Long id; 258 String name; 259 String major; 260 String clas; 261 double mathScore; 262 double osScore; 263 double javaScore; 264 Scanner scanner=new Scanner(System.in); 265 System.out.println("1.显示学生信息"); 266 System.out.println("2.录入学生信息"); 267 System.out.println("3.学号查找学生"); 268 System.out.println("4.姓名查找学生"); 269 System.out.println("5.删除学生信息"); 270 System.out.println("6.排序"); 271 System.out.println("0.退出"); 272 System.out.print("输入选择（0-6）:"); 273 switch(scanner.nextInt()){ 274 case 1: 275 dispAll(); 276 menu(); 277 break; 278 case 2: 279 System.out.print("请输入学号："); 280 id=scanner.nextLong(); 281 System.out.print("请输入姓名："); 282 name=scanner.next(); 283 System.out.print("请输入专业："); 284 major=scanner.next(); 285 System.out.print("请输入班级："); 286 clas=scanner.next(); 287 System.out.print("请输入mathScore："); 288 mathScore=scanner.nextDouble(); 289 System.out.print("请输入osScore："); 290 osScore=scanner.nextDouble(); 291 System.out.print("请输入javaScore："); 292 javaScore=scanner.nextDouble(); 293 Student stu=new Student(); 294 stu.setId(id);stu.setName(name); 295 stu.setMajor(major);stu.setClas(clas); 296 stu.setMathScore(mathScore);stu.setOsScore(osScore);stu.setJavaScore(javaScore); 297 add(stu); 298 System.out.println("添加学生信息成功！"); 299 menu(); 300 break; 301 case 3: 302 System.out.print("请输入你要查找的学生的学号："); 303 id=scanner.nextLong(); 304 findById(id); 305 menu(); 306 break; 307 case 4: 308 System.out.print("请输入你要查找的学生的姓名："); 309 name=scanner.next(); 310 findByName(name); 311 menu(); 312 break; 313 case 5: 314 System.out.print("请输入你要删除的学生的学号："); 315 id=scanner.nextLong(); 316 delById(id); 317 System.out.println("删除学生信息成功！"); 318 menu(); 319 break; 320 case 6: 321 System.out.println("1 math 2 os 3 java"); 322 System.out.println("*************************************************"); 323 System.out.print("请输入选择(1-3):"); 324 int choice=scanner.nextInt(); 325 if(choice==1){ 326 sort("math"); 327 } 328 else if(choice==2){ 329 sort("os"); 330 } 331 else if(choice==3){ 332 sort("java"); 333 } 334 else{ 335 System.out.println("Input error!"); 336 } 337 menu(); 338 break; 339 case 0: 340 scanner.close(); 341 break; 342 default: 343 System.out.println("Input error!"); 344 menu(); 345 } 346 } 347
348
349
350
351
352 public static void main(String[] args) throws Exception { 353 StuInfoManage stuinfomanage=new StuInfoManage(); 354 stuinfomanage.menu(); 355 stuinfomanage.close(); 356 } 357 }