JDBC：以面向对象的思想编写JDBC程序

题目简述:

向数据表中添加学生信息，并且可以通过身份证号准考证来查询学生信息。

Name        Type         Nullable Default Comments 
----------- ------------ -------- ------- -------- 
IDCARD      NUMBER(10)   Y                         
EXAMCARD    NUMBER(10)   Y                         
STUDENTNAME VARCHAR2(20) Y                         
LACATION    VARCHAR2(20) Y                         
GRADE       NUMBER(3)    Y       

测试类

package xuezaipiao3;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Scanner;import javax.sql.rowset.JdbcRowSet;import xuezaipiao1.JDBC_Tools;/** * 思考： * 向数据表中添加一条学生信息记录，那么学生信息就可以创建一个学生类来储存信息 * 步骤： * 1. * 1) Student成员变量 对应studnt数据表  * 2) 创建一个方法addStudent(Student student) *  3) 方法中执行相应的 SQL 操作 * 2.使用PreparedStatement Statement的子接口，可以传入带占位符的SQL语句，并且提供了补充占位符的 * 的方法 * 1) String sql = "INSERT INTO student values(?,?,?,?,?)"; * PreparedStatement ps = conn.preparedStatement(sql); * 2) 调用PreparedStatement 的setXxx(int index,object val) 设置占位符的值 * 3) 执行executeUpdate() 或executeQuery() 就不需要再传入SQL语句了 * 4) PreparedStatement 可以防止SQL注入攻击 * @author Kevy * */public class thinkInJDBC {public static void main(String[] args) {//Operation op = new Operation();//Student s = op.getStudentFromConsole();//op.addStudent(s);//op.QueryStudent();}}

Operation类

package xuezaipiao3;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;import java.util.Scanner;import xuezaipiao1.JDBC_Tools;/** * 进行SQL操作的类 * @author Kevy * */public class Operation {public void QueryStudent(){int searchType = getSearchTypeFromConsole();Student student = searchStudent(searchType);printStudent(student);}/** * 打印学生信息 * @param student */private void printStudent(Student student) {if(student!=null){System.out.println(student);}else{System.out.println("查无此人!");}}/** *  * @param searchType 1 And 2 * @return */private Student searchStudent(int searchType) {String sql = "SELECT * FROM student "+ "where ";Scanner scanner = new Scanner(System.in);if(searchType==1){System.out.print("请输入身份证:");int id = scanner.nextInt();sql = sql + "IDCARD = " +id;}else{System.out.print("请输入准考证:");int id = scanner.nextInt();sql = sql + "EXAMCARD = " +id;}Student student = getStudent(sql);return student;}/** * 根据传入的sql返回Student对象 * @param sql * @return */private Student getStudent(String sql) {Connection connection = null;Statement statement = null;ResultSet rs = null;Student stu = null;try {connection = JDBC_Tools.getConnection();statement = connection.createStatement();rs = statement.executeQuery(sql);if(rs.next()){stu = new Student(rs.getInt("IDCARD"),rs.getInt("EXAMCARD"),rs.getString("STUDENTNAME"),rs.getString("LACATION"),rs.getInt("GRADE"));}} catch (Exception e) {e.printStackTrace();}finally{JDBC_Tools.relaseSource(rs, connection, statement);}return stu;}/** *  * @return 1 用身份证查询 ， 2 用准考证号查询  其他无效 */@SuppressWarnings("resource")private int getSearchTypeFromConsole() {System.out.println("请输入查询类型:1.身份证查询   2.准考证查询");System.out.print("你的选择:");Scanner scanner = new Scanner(System.in);int type = scanner.nextInt();if(type!=1 && type!=2){System.out.println("输入有误,请重新输入");throw new RuntimeException();}return type;}/** * 从控制台获取信息 并创建学生对象 * @return */public Student getStudentFromConsole() {Scanner scanner = new Scanner(System.in);Student student = new Student();System.out.print("IDCard:");student.setIDCard(scanner.nextInt());System.out.print("ExamID:");student.setExamID(scanner.nextInt());System.out.print("StudentName:");student.setStudentName(scanner.next());System.out.print("Llocation:");student.setLacation(scanner.next());System.out.print("Grade:");student.setGrade(scanner.nextInt());scanner.close();return student;}/** * 添加学生信息 * @param student */public void addStudent(Student student){/* * 使用普通方法 * String sql = "INSERT INTO STUDENT "+ "VALUES("+student.getIDCard()+","+student.getExamID()+",'"+student.getStudentName()+"','"+student.getLacation()+"',"+student.getGrade()+")"; JDBC_Tools.update(sql);*//** * 使用PreparedStatement */String sql = "INSERT INTO student values(?,?,?,?,?)";newUpdate(sql,student.getIDCard(),student.getExamID(),student.getStudentName(),student.getLacation(),student.getGrade());}/** * 新的修改方法 * @param sql * @param objs :可变参数 */private void newUpdate(String sql,Object...objs){Connection conn = null;PreparedStatement ps = null; try {conn = JDBC_Tools.getConnection();ps = conn.prepareStatement(sql);for(int i = 0;i<objs.length ; i++ ){ps.setObject(i+1, objs[i]);}ps.executeUpdate(); //注意这里就不需要再添加sql参数了} catch (Exception e) {e.printStackTrace();}finally{JDBC_Tools.relaseSource(conn, ps);}}}

Student类

public class Student {private int IDCard;private int ExamID;private String StudentName;private String Lacation;private int Grade;public Student(int iDCard, int examID, String studentName, String lacation,int grade) {super();IDCard = iDCard;ExamID = examID;StudentName = studentName;Lacation = lacation;Grade = grade;}@Overridepublic String toString() {return "Student [IDCard=" + IDCard + ", ExamID=" + ExamID+ ", StudentName=" + StudentName + ", Lacation=" + Lacation+ ", Grade=" + Grade + "]";}public Student() {super();}public int getIDCard() {return IDCard;}public void setIDCard(int iDCard) {IDCard = iDCard;}public int getExamID() {return ExamID;}public void setExamID(int examID) {ExamID = examID;}public String getStudentName() {return StudentName;}public void setStudentName(String studentName) {StudentName = studentName;}public String getLacation() {return Lacation;}public void setLacation(String lacation) {Lacation = lacation;}public double getGrade() {return Grade;}public void setGrade(int grade) {Grade = grade;}}

JDBC工具类

package xuezaipiao1;/** * JDBC工具类 * 封装一些简单的JDBC操作方法 * version 1 */import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JDBC_Tools {/** * 用来执行 SQL 的方法，包括INSRT , UPDATE , DELETE,不包含SELECT * 参数 String SQL语句 * @return int 执行了几条记录 */public static int update(String sql){Connection conn = null;Statement statement = null;int num = 0;try {try {conn = JDBC_Tools.getConnection();} catch (Exception e) {e.printStackTrace();}statement = conn.createStatement();num = statement.executeUpdate(sql);} catch (SQLException e) {e.printStackTrace();}finally{JDBC_Tools.relaseSource(conn, statement);}return num;}/** * 用来执行 SQL 的SELECT 方法 */public static void query(String sql){Connection conn = null;Statement statement = null;ResultSet rs = null;try {conn = JDBC_Tools.getConnection();statement = conn.createStatement();rs = statement.executeQuery(sql);while(rs.next()){System.out.println(rs.getInt("id"));System.out.println(rs.getString(2));System.out.println(rs.getString("email"));}} catch (Exception e) {e.printStackTrace();}finally{JDBC_Tools.relaseSource(rs, conn, statement);}}/** * 用来释放资源，参数是 Connection 、 Statement * @param conn * @param statement */public static void relaseSource(ResultSet rs,Connection conn ,Statement statement){if(rs != null){try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}JDBC_Tools.relaseSource(conn, statement);}public static void relaseSource(Connection conn ,Statement statement){if(statement!=null){try {statement.close();} catch (SQLException e) {e.printStackTrace();}}//使用两个 if ，这样即使中间出现异常，程序还是继续执行下去if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}/** *  * @return * @throws Exception */public static Connection getConnection() throws Exception {Properties properties = new Properties();try {//InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.properties");//properties.load(in);properties.load(new FileInputStream("D://LearnJava//learnJDBC//Lesson2_UseStatementAndResultSet//src//jdbc.properties"));} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}String user = properties.getProperty("user");String password = properties.getProperty("password");String jdbcUrl = properties.getProperty("jdbcUrl");String dirverName = properties.getProperty("driver");try {Class.forName(dirverName);} catch (ClassNotFoundException e1) {e1.printStackTrace();}Connection connection = null;try {connection = DriverManager.getConnection(jdbcUrl, user, password);} catch (SQLException e) {e.printStackTrace();}return connection;}}

SQL注入攻击:

SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令，从而利用系统的 SQL 引擎完成恶意行为的做法

对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement 取代 Statement 就可以了

SQL注入问题:

create table SQLAttack(              user varchar2(20),       password varchar2(10))SQL> INSERT INTO SQLATTACK (USERNAME,PSW)  2  VALUES('tom','cat');

public static void SQLInjection(){//String userName = "tom";//String psw = "cat";String userName = "a' or psw = ";String psw = "or '1' ='1";String sql = "Select * from SQLATTACK WHERE userName = '"+userName+"' AND "+"PSW = '"+ psw +"'";//利用sql的拼写漏洞 System.out.println(sql);Connection conn = null;Statement statement = null;ResultSet rs = null;try {conn = JDBC_Tools.getConnection();statement = conn.createStatement();rs = statement.executeQuery(sql);if(rs.next()){System.out.println("登录成功");}else{System.out.println("账号、密码错误!");}} catch (Exception e) {e.printStackTrace();}}

使用PreparedStatement就没有上述问题

public static void SQLInjection(){//String userName = "tom";//String psw = "cat";String userName = "a' or psw = ";String psw = "or '1' ='1";String sql = "Select * from SQLATTACK WHERE userName = ?"+"AND PSW = ?";System.out.println(sql);Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = JDBC_Tools.getConnection();ps = conn.prepareStatement(sql);ps.setString(1,userName);ps.setString(2, psw);/** * PreparedStatement 就是在用 userName 和 psw 去匹配，所以不会出现问题 */rs = ps.executeQuery();if(rs.next()){System.out.println("登录成功");}else{System.out.println("账号、密码错误!");}} catch (Exception e) {e.printStackTrace();}}

PreparedStatement VS Statement

PreparedStatement 能最大可能提高性能：
1)DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
2)在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存.这样每执行一次都要对传入的语句编译一次.  
(语法检查，语义检查，翻译成二进制命令，缓存)
3)PreparedStatement 可以防止 SQL 注入