JDBC实例
JDBC实例
package jdbc2; public class ExamStudent { int flowid; int type; String idcard; String examCard; String studentname; String location; int grade; public ExamStudent() { super(); // TODO Auto-generated constructor stub } @Override public String toString() { return "ExamStudent [flowid=" + flowid + ", type=" + type + ", idcard=" + idcard + ", examCard=" + examCard + ", studentname=" + studentname + ", location=" + location + ", grade=" + grade + "]"; } public int getFlowid() { return flowid; } public void setFlowid(int flowid) { this.flowid = flowid; } public int getType() { return type; } public void setType(int type) { this.type = type; } public String getIdcard() { return idcard; } public void setIdcard(String idcard) { this.idcard = idcard; } public String getExamCard() { return examCard; } public void setExamCard(String examCard) { this.examCard = examCard; } public String getStudentname() { return studentname; } public void setStudentname(String studentname) { this.studentname = studentname; } public String getLocation() { return location; } public void setLocation(String location) { this.location = location; } public int getGrade() { return grade; } public void setGrade(int grade) { this.grade = grade; } public ExamStudent(int flowid, int type, String idcard, String examCard, String studentname, String location, int grade) { super(); this.flowid = flowid; this.type = type; this.idcard = idcard; this.examCard = examCard; this.studentname = studentname; this.location = location; this.grade = grade; } }
1、获取数据库连接封装的方法
public static Connection getConnection() throws Exception{ String driverClass = null; String url = null; String user = null; String password = null; //读取properties配置文件 InputStream is = TestJDBC.class.getClassLoader().getResourceAsStream("jdbc.properties"); Properties properties = new Properties(); properties.load(is); driverClass = properties.getProperty("driverClass"); url = properties.getProperty("url"); user = properties.getProperty("user"); password = properties.getProperty("password"); //加载数据库驱动(对应的Driver实现类中有注册驱动的静态代码块) Class.forName(driverClass); Properties info = new Properties(); info.put("user", user); info.put("password", password); //通过DriverManager的getConnection()方法获取数据库连接 return DriverManager.getConnection(url, user, password); }
2、释放资源封装的方法
//释放资源 public static void release(Statement statement,Connection conn,ResultSet rs){ if(rs != null){ try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(statement!=null){ try { statement.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
3、简单的学生登录封装的方法
//登录 private int getSearchTypeFormConsole(){ System.out.println("请输入登录类型:1:身份证登录 2.准考证号登陆"); Scanner sc = new Scanner(System.in); int type = sc.nextInt(); if(type != 1 && type != 2){ throw new RuntimeException("输入类型有误"); } return type; }
4、创建查询语句
public String SearchStudent(int type){ ExamStudent es = new ExamStudent(); Scanner sc = new Scanner(System.in); String sql = "select * from ExamStudent where idcard = " ; if(type == 1){ System.out.println("请输入身份证号"); String idcard = sc.next(); es.setIdcard(idcard); sql = sql + es.getIdcard(); }else{ System.out.println("请输入准考证号"); String examcard = sc.next(); es.setExamCard(examcard); sql = sql + es.getExamCard(); } return sql; }
5、查询学生
public ExamStudent getStudent(String sql){ Connection conn = null; Statement statement = null; ResultSet rs = null; ExamStudent es = null; try { conn = getConnection(); statement = conn.createStatement(); rs = statement.executeQuery(sql); while(rs.next()){ es = new ExamStudent( rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getInt(7)); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ release(statement, conn, rs); } return es; }
6、输出学生
public void printStudent(ExamStudent es){ if(es !=null){ System.out.println(es); }else{ System.out.println("查无此人!!!"); } }
7、测试
@Test public void test1() throws Exception{ //1.登录 int type = getSearchTypeFormConsole(); //获取信息 String sql = SearchStudent(type); //查询 ExamStudent es = getStudent(sql); //输出 printStudent(es); }
8、从控制台输入ExamStudent对象
//从控制台输入ExamStudent对象 public ExamStudent getExamStudentFromConsole(){ ExamStudent es = new ExamStudent(); Scanner sc = new Scanner(System.in); System.out.println("学生号: "); es.flowid = sc.nextInt(); System.out.println("考试类型: "); es.type = sc.nextInt(); System.out.println("身份证: "); es.idcard = sc.next(); System.out.println("考号: "); es.examCard = sc.next(); System.out.println("学生名: "); es.studentname = sc.next(); System.out.println("学生地址: "); es.location = sc.next(); System.out.println("学生班级: "); es.grade = sc.nextInt(); return es; }
9、封装的sql语句修改、插入、删除方法
public void update(String sql){ Connection conn = null; Statement statement = null; ResultSet rs = null; try { conn = getConnection(); statement = conn.createStatement(); statement.executeUpdate(sql); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ TestJDBC.release(statement, conn,rs); } }
10、添加学生
//添加学生到数据库 public void addStudent(){ ExamStudent stu = getExamStudentFromConsole(); String sql = "insert into examstudent values(" + stu.getFlowid() +"," + stu.getType() + ",'" + stu.getIdcard() + "','" + stu.getExamCard() + "','" + stu.getStudentname() + "','" + stu.getLocation() + "'," + stu.getGrade() + ")"; update(sql); System.out.println("插入成功!!!!"); }
1、从控制台得到需要插入的学生信息
//从控制台输入ExamStudent对象 public ExamStudent getExamStudentFromConsole(){ ExamStudent es = new ExamStudent(); Scanner sc = new Scanner(System.in); System.out.println("学生号: "); es.flowid = sc.nextInt(); System.out.println("考试类型: "); es.type = sc.nextInt(); System.out.println("身份证: "); es.idcard = sc.next(); System.out.println("考号: "); es.examCard = sc.next(); System.out.println("学生名: "); es.studentname = sc.next(); System.out.println("学生地址: "); es.location = sc.next(); System.out.println("学生班级: "); es.grade = sc.nextInt(); return es; }
2、获取数据库连接
//获取数据库连接方法 public static Connection getConnection() throws Exception{ String driverClass = null; String url = null; String user = null; String password = null; //读取properties配置文件 InputStream is = TestJDBC.class.getClassLoader().getResourceAsStream("jdbc.properties"); Properties properties = new Properties(); properties.load(is); driverClass = properties.getProperty("driverClass"); url = properties.getProperty("url"); user = properties.getProperty("user"); password = properties.getProperty("password"); //加载数据库驱动(对应的Driver实现类中有注册驱动的静态代码块) Class.forName(driverClass); Properties info = new Properties(); info.put("user", user); info.put("password", password); //通过DriverManager的getConnection()方法获取数据库连接 return DriverManager.getConnection(url, user, password); }
3、通过prestatement进行数据插入
//重写update方法 public void update(String sql,Object...args){ Connection conn = null; PreparedStatement preparedStatement = null; try { conn = getConnection(); preparedStatement = conn.prepareStatement(sql); for(int i=0;i<args.length;i++){ preparedStatement.setObject(i+1, args[i]); } int result = preparedStatement.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }
4、添加学生
//添加学生 public void addStudent2(ExamStudent es){ String sql = "insert into examstudent(flowid,type,idcard,examcard,studentname,location,grade) values(?,?,?,?,?,?,?)"; update(sql,es.getFlowid(),es.getType(),es.getIdcard(),es.getExamCard(),es.getStudentname(),es.getLocation(),es.getGrade()); }
5、进行测试
@Test public void test3(){ ExamStudent es = getExamStudentFromConsole(); addStudent2(es); }