JDBC帮助类
package work;
import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBHelp {
/**
* 建立与数据库连接
* 1.加载驱动
* 2.建立连接
*/
public static Connection lianjie(){
//驱动
final String Driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//数据库url jdbc:sqlserver://localhost:1433;DataBase=Pet",
String url = "jdbc:sqlserver://localhost:1433;Database=work";
//数据库用户名
String name = "sa";
//数据库密码
String pas = "123456";
Connection con = null;
try{
Class.forName(Driver);
con = DriverManager.getConnection(url,name,pas);
}catch(SQLException s){
s.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return con;
}
}
Student类
package work;
public class Student {
private String StudentNo;
private String name;
private String ClassName;
private int age;
private String Address;
//封装入口出口
public String getStudentNo() {
return StudentNo;
}
public void setStudentNo(String studentNo) {
StudentNo = studentNo;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getClassName() {
return ClassName;
}
public void setClassName(String className) {
ClassName = className;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return Address;
}
public void setAddress(String address) {
Address = address;
}
}
Work类】
package work;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;
public class work {
public static List<Student> getAll() throws SQLException{
//创建ArrayList集合
List<Student> list = new ArrayList<Student>();
Connection con = DBHelp.lianjie();
Statement st = con.createStatement();
//sql语句
String sql = "select s.StudentID,s.StudentName,c.ClassName,s.Age,S.Address from Student as s inner join Class as c on s.ClassID = c.ClassID";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//实例化一个学生
Student stu = new Student();
stu.setStudentNo(rs.getString(1));
stu.setName(rs.getString(2));
stu.setClassName(rs.getString(3));
stu.setAge(rs.getInt(4));
stu.setAddress(rs.getString(5));
list.add(stu);
}
rs.close();
st.cancel();
con.close();
return list;
}
public static Map<String, Student> getClassInfo() throws SQLException{
//创建Map集合
Map<String, Student> map = new HashMap<String, Student>();
//获取与数据库连接
Connection con = DBHelp.lianjie();
//sql
String sql = "select s.StudentID,s.StudentName,c.ClassName,s.Age,S.Address from Student as s inner join Class as c on s.ClassID = c.ClassID";
//建立起程序和数据库的命令桥梁 预编译
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()){
Student stu = new Student();
stu.setStudentNo(rs.getString(1));
stu.setName(rs.getString(2));
stu.setClassName(rs.getString(3));
stu.setAge(rs.getInt(4));
stu.setAddress(rs.getString(5));
map.put(rs.getString(1), stu);
}
rs.close();
ps.close();
con.close();
return map;
}
//打印方法
public static void show() throws SQLException{
System.out.println("欢迎进入学生查询系统：");
System.out.println("1.查询学生所有信息（使用ArrayList）\t2.查询学生所有信息（使用HashMap）");
Scanner input = new Scanner(System.in);
System.out.println("请选择：");
if(input.nextInt()==1){
List<Student> list = getAll();
//迭代器遍历
Iterator<Student> i = list.iterator();
System.out.println("一共有："+list.size()+"个学生！");
System.out.println("学号\t名字\t班级\t年龄\t地址");
while(i.hasNext()){
Student stu = i.next();
System.out.println(stu.getStudentNo()+"\t"+stu.getName()+"\t"+stu.getClassName()+"\t"+stu.getAge()+"\t"+stu.getAddress());
}
return;
}
Map<String, Student> map = getClassInfo();
System.out.println("一共有："+map.size()+"个学生！");
System.out.println("学号\t名字\t班级\t年龄\t地址");
Set<String> set = map.keySet();
Iterator<String> i2 = set.iterator();
while(i2.hasNext()){
String temp = i2.next();
Student stu = map.get(temp);
System.out.println(stu.getStudentNo()+"\t"+stu.getName()+"\t"+stu.getClassName()+"\t"+stu.getAge()+"\t"+stu.getAddress());
}
}
public static void main(String[] args) {
try {
show();
} catch (SQLException e) {
e.printStackTrace();
}
}
}