java JDBC(PrepareStatement)。
PreparedStatement：
package com.shellway.jdbc;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCTools {
/**
* 执行 SQL 语句, 使用 PreparedStatement
* @param sql
* @param args: 填写 SQL 占位符的可变参数
*/
public static void update(String sql, Object … args){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
preparedStatement.setObject(i + 1, args[i]);
}
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
/**
* 执行 SQL 的方法
*
* @param sql: insert, update 或 delete。 而不包含 select
*/
public static void update(String sql) {
Connection connection = null;
Statement statement = null;
try {
// 1. 获取数据库连接
connection = getConnection();
// 2. 调用 Connection 对象的 createStatement() 方法获取 Statement 对象
statement = connection.createStatement();
// 4. 发送 SQL 语句: 调用 Statement 对象的 executeUpdate(sql) 方法
statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5. 关闭数据库资源: 由里向外关闭.
releaseDB(null, statement, connection);
}
}
/**
* 释放数据库资源的方法
*
* @param resultSet
* @param statement
* @param connection
*/
public static void releaseDB(ResultSet resultSet, Statement statement,
Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取数据库连接的方法
*/
public static Connection getConnection() throws IOException,
ClassNotFoundException, SQLException {
// 0. 读取 jdbc.properties
/**
* 1). 属性文件对应 Java 中的 Properties 类 2). 可以使用类加载器加载 bin 目录(类路径下)的文件
*/
Properties properties = new Properties();
InputStream inStream = ReviewTest.class.getClassLoader()
.getResourceAsStream(“jdbc.properties”);
properties.load(inStream);
// 1. 准备获取连接的 4 个字符串: user, password, jdbcUrl, driverClass
String user = properties.getProperty(“user”);
String password = properties.getProperty(“password”);
String jdbcUrl = properties.getProperty(“jdbcUrl”);
String driverClass = properties.getProperty(“driverClass”);
// 2. 加载驱动: Class.forName(driverClass)
Class.forName(driverClass);
// 3. 调用
// DriverManager.getConnection(jdbcUrl, user, password)
// 获取数据库连接
Connection connection = DriverManager.getConnection(jdbcUrl, user,
password);
return connection;
}
}
JDBCTools.java
/**
* 使用 PreparedStatement 将有效的解决 SQL 注入问题.
*/
@Test
public void testSQLInjection2() {
String username = “a’ OR PASSWORD = “;
String password = ” OR ’1′=’1″;
String sql = “SELECT * FROM users WHERE username = ? ”
+ “AND password = ?”;
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println(“登录成功!”);
} else {
System.out.println(“用户名和密码不匹配或用户名不存在. “);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(resultSet, preparedStatement, connection);
}
}
/**
* SQL 注入.
*/
@Test
public void testSQLInjection() {
String username = “a’ OR PASSWORD = “;
String password = ” OR ’1′=’1″;
String sql = “SELECT * FROM users WHERE username = ‘” + username
+ “‘ AND ” + “password = ‘” + password + “‘”;
System.out.println(sql);
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
System.out.println(“登录成功!”);
} else {
System.out.println(“用户名和密码不匹配或用户名不存在. “);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(resultSet, statement, connection);
}
}
使用 PreparedStatement 将有效的解决 SQL 注入问题
练习：
1)请输入考生的详细信息
Type:
IDCard:
ExamCard:
StudentName:
Location:
Grade:
提示：信息录入成功!
2).在 eclipse 中建立 java 程序：输入身份证号或准考证号可以查询到学生的基本信息。结果如下：
@Test
public void testGetStudent() {
// 1. 得到查询的类型
int searchType = getSearchTypeFromConsole();
// 2. 具体查询学生信息
Student student = searchStudent(searchType);
// 3. 打印学生信息
printStudent(student);
}
/**
* 打印学生信息: 若学生存在则打印其具体信息. 若不存在: 打印查无此人.
*
* @param student
*/
private void printStudent(Student student) {
if (student != null) {
System.out.println(student);
} else {
System.out.println(“查无此人!”);
}
}
/**
* 具体查询学生信息的. 返回一个 Student 对象. 若不存在, 则返回 null
*
* @param searchType
* : 1 或 2.
* @return
*/
private Student searchStudent(int searchType) {
String sql = “SELECT flowid, type, idcard, examcard,”
+ “studentname, location, grade ” + “FROM examstudent ”
+ “WHERE “;
Scanner scanner = new Scanner(System.in);
// 1. 根据输入的 searchType, 提示用户输入信息:
// 1.1 若 searchType 为 1, 提示: 请输入身份证号. 若为 2 提示: 请输入准考证号
// 2. 根据 searchType 确定 SQL
if (searchType == 1) {
System.out.print(“请输入准考证号:”);
String examCard = scanner.next();
sql = sql + “examcard = ‘” + examCard + “‘”;
} else {
System.out.print(“请输入身份证号:”);
String examCard = scanner.next();
sql = sql + “idcard = ‘” + examCard + “‘”;
}
// 3. 执行查询
Student student = getStudent(sql);
// 4. 若存在查询结果, 把查询结果封装为一个 Student 对象
return student;
}
/**
* 根据传入的 SQL 返回 Student 对象
*
* @param sql
* @return
*/
private Student getStudent(String sql) {
Student stu = null;
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
stu = new Student(resultSet.getInt(1), resultSet.getInt(2),
resultSet.getString(3), resultSet.getString(4),
resultSet.getString(5), resultSet.getString(6),
resultSet.getInt(7));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(resultSet, statement, connection);
}
return stu;
}
/**
* 从控制台读入一个整数, 确定要查询的类型
*
* @return: 1. 用身份证查询. 2. 用准考证号查询 其他的无效. 并提示请用户重新输入.
*/
private int getSearchTypeFromConsole() {
System.out.print(“请输入查询类型: 1. 用身份证查询. 2. 用准考证号查询 “);
Scanner scanner = new Scanner(System.in);
int type = scanner.nextInt();
if (type != 1 && type != 2) {
System.out.println(“输入有误请重新输入!”);
throw new RuntimeException();
}
return type;
}
@Test
public void testAddNewStudent() {
Student student = getStudentFromConsole();
addNewStudent2(student);
}
/**
* 从控制台输入学生的信息
*/
private Student getStudentFromConsole() {
Scanner scanner = new Scanner(System.in);
Student student = new Student();
System.out.print(“FlowId:”);
student.setFlowId(scanner.nextInt());
System.out.print(“Type: “);
student.setType(scanner.nextInt());
System.out.print(“IdCard:”);
student.setIdCard(scanner.next());
System.out.print(“ExamCard:”);
student.setExamCard(scanner.next());
System.out.print(“StudentName:”);
student.setStudentName(scanner.next());
System.out.print(“Location:”);
student.setLocation(scanner.next());
System.out.print(“Grade:”);
student.setGrade(scanner.nextInt());
return student;
}
public void addNewStudent2(Student student) {
String sql = “INSERT INTO examstudent(flowid, type, idcard, ”
+ “examcard, studentname, location, grade) ”
+ “VALUES(?,?,?,?,?,?,?)”;
JDBCTools.update(sql, student.getFlowId(), student.getType(),
student.getIdCard(), student.getExamCard(),
student.getStudentName(), student.getLocation(),
student.getGrade());
}
public void addNewStudent(Student student) {
// 1. 准备一条 SQL 语句:
String sql = “INSERT INTO examstudent VALUES(” + student.getFlowId()
+ “,” + student.getType() + “,’” + student.getIdCard() + “‘,’”
+ student.getExamCard() + “‘,’” + student.getStudentName()
+ “‘,’” + student.getLocation() + “‘,” + student.getGrade()
+ “)”;
System.out.println(sql);
// 2. 调用 JDBCTools 类的 update(sql) 方法执行插入操作.
JDBCTools.update(sql);
}
}
练习代码
在练习中要注意的问题：
1、在执行这一语句ps = conn.prepareStatement(sql);的时候，如果提示要强制转换成prepareStatement类型，
则不要转换，只需要把包导入就行：import java.sql.PreparedStatement;
2、遇到插入日期语句的时候，比如：ps.setDate(3, new Date(new java.util.Date().getTime()));
前面的new Date（）是要声明为java.sql包中的，里面的new Date（）则要声明为java.util包中的。
在使用Preparement的时候，最好在主程序中写函数然后调用工具类JDBCTools.java中的更新方法：
public void addStudent(Student stu) throws Exception {
String sql = "insert into examstudent values(?,?,?,?,?,?,?)";
JDBCTools.testUpdate(sql, stu.getFlowID(), stu.getType(),
stu.getIdCard(), stu.getExamCard(), stu.getStudentName(),
stu.getLocation(), stu.getGrade());
}
在 工具类JDBCTools.java中写更新方法，传入 sql 和 可变参数args
public static void testUpdate(String sql, Object… args) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCTools.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
System.out.println(sql);
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(null, ps, conn);
}
}
工具类JDBCTools.java中的更新方法
下面写个简单而小巧的PrepareStatement例子：
@Test
public void testPreparedStatement() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
String sql = “INSERT INTO customers (name, email, birth) ”
+ “VALUES(?,?,?)”;
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, “shellway”);
preparedStatement.setString(2, “shellway@163.com”);
preparedStatement.setDate(3,
new Date(new java.util.Date().getTime()));
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
PrepareStatement例子
http://www.cnblogs.com/shellway/p/3933403.html