JDBC中的PreparedStatement
使用Statement需要进行拼写SQL语句，很麻烦而且容易出错，这就用到了PreparedStatement。PreparedStatement是Statement的子接口，可以传入带占位符的SQL语句，并且提供了补充占位符变量的方法。
1.使用PreparedStatement
1.1 创建PreparedStatement；
String sql="INSERT INTO EXAMSTUDENT VALUES(?,?,?,?,?,?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
1.2 调动PreparedStatement的setXxx(int index,Object val)设置占位符的值；
1.3 执行 SQL语句：executeQuery()或executeUpdate()。注意：执行时不再需要传入SQL语句。
示例代码：
@Test
public void testPreparedStatement(){
Connection connection=null;
PreparedStatement preparedstatement=null;
try{
String sql="INSERT INTO EXAMSTUDENT VALUES(?,?,?,?,?,?,?)";
connection=JDBCTools.getConnection();
preparedstatement=connection.prepareStatement(sql);
preparedstatement.setInt(1, 3);
preparedstatement.setInt(2, 434);
preparedstatement.setString(3, "198312");
preparedstatement.setString(4, "342");
preparedstatement.setString(5, "Peter");
preparedstatement.setString(6, "上海");
preparedstatement.setInt(7, 332);
preparedstatement.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCTools.release(preparedstatement,connection);
}
}
2.使用PreparedStatement向数据表中添加学生信息
Student.java
package com.test.jdbc;
public class Student {
private int flowId;
private int type;
private String idCard;
private String examCard;
private String studentName;
private String location;
private int 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 Student(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;
}
public Student(){ }
@Override
public String toString() {
return "student [flowId=" + flowId + ", type=" + type + ", idCard=" + idCard + ", examCard=" + examCard
+ ", studentName=" + studentName + ", location=" + location + ", grade=" + grade + "]";
}
}
工具类JDBCTools.java
package com.test.jdbc;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.junit.Test;
public class JDBCTools {
//添加数据并更新
public static void update2(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.release(preparedstatement, connection);
}
}
//获取数据库的连接
public static Connection getConnection() throws Exception{
String driverClass=null;
String jdbcUrl=null;
String user=null;
String password=null;
InputStream in=JDBCTools.class.getResourceAsStream("/jdbc.properties");
Properties properties=new Properties();
properties.load(in);
driverClass=properties.getProperty("driver");
jdbcUrl=properties.getProperty("jdbcUrl");
user=properties.getProperty("user");
password=properties.getProperty("password");
Class.forName(driverClass);
Connection connection=DriverManager.getConnection(jdbcUrl,user,password);
return connection;
}
@Test
public void testGetConnection() throws Exception{
getConnection();
}
//数据库释放
public static void release(Statement statement,Connection connection){
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
功能实现类：JDBCTest.java
package com.test.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
import org.junit.Test;
public class JDBCTest {
@Test
public void testAddNewStudent2(){
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 VALUES(?,?,?,?,?,?,?)";
JDBCTools.update2(sql, student.getFlowId(),student.getType(),student.getIdCard(),
student.getExamCard(),student.getStudentName(),student.getLocation(),student.getGrade());
}
}
wx搜索“程序员考拉”，专注java领域，一个伴你成长的公众号！
