Statement接口封装了JDBC执行SQL语句的方法,可以完成Java程序执行SQL语句的操作,但在实际开发中,SQL往往需要将程序中的变量做为查询条件参数等,使用Statement接口进行操作过于繁琐,而且存在安全方面的缺陷,针对这一问题,JDBC API中封装了Statement的扩展PreparedStatement对象,它可以将Statement中的执行方法改用占位符?来代替。
一、增、删、改示例:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.PreparedStatement;
public class InsertDemo {
public static void main(String[] args) throws ClassNotFoundException {
try {
Class.forName("com.mysql.jdbc.Driver");
String uri = "jdbc:mysql://localhost:3306/wtyy";
String username = "root";
String pwd = "wtyy";
Connection conn = DriverManager.getConnection(uri, username, pwd);
//增
String sql="insert into student(name,age) values(?,?)";
PreparedStatement ps=(PreparedStatement) conn.prepareStatement(sql);
ps.setString(1, "张三");//对sql语句的第一个参数赋值
ps.setInt(2, 18);//对sql语句的第二个参数赋值
int row=ps.executeUpdate();//执行
if(row>0){
System.out.println("成功添加了"+row+"条数据");
}
ps.close();
//删
String sql1="delete from student where name=? and age=?";
PreparedStatement ps1=(PreparedStatement) conn.prepareStatement(sql1);
ps1.setString(1, "张三");
ps1.setInt(2, 18);
ps1.execute();
ps1.close();
//改
String sql2="update student set age=20 where name=?";
PreparedStatement ps2=(PreparedStatement) conn.prepareStatement(sql2);
ps2.setString(1, "张三");
ps2.execute();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
二、批量操作:
一次性需要添加或者更新或者删除很多条数据时,这个数据有可能会达到几万条,这样在每次操作时都进行创建数据库的连接会开销很大的。JDBC可以进行批量的执行SQL语句,
例:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.PreparedStatement;
public class MuchUpdate {
public static void main(String[] args) throws ClassNotFoundException {
try {
Class.forName("com.mysql.jdbc.Driver");
String uri = "jdbc:mysql://localhost:3306/wtyy";
String username = "root";
String pwd = "wtyy";
Connection conn = DriverManager.getConnection(uri, username, pwd);
//批量增
String sql="insert into student(name,age) values(?,?)";
PreparedStatement ps=(PreparedStatement) conn.prepareStatement(sql);
ps.setString(1, "李四");
ps.setInt(2, 19);
ps.addBatch();
ps.setString(1, "王五");
ps.setInt(2, 30);
ps.addBatch();
ps.executeBatch();
//批量改
String sql1="update student set age=28 where name=?";
PreparedStatement ps1=(PreparedStatement) conn.prepareStatement(sql1);
ps1.setString(1, "张三");
ps1.addBatch();
ps1.setString(1, "王五");
ps1.addBatch();
ps1.setString(1, "李四");
ps1.addBatch();
ps1.executeBatch();
//批量删
String sql2="delete from student where name=?";
PreparedStatement ps2=(PreparedStatement) conn.prepareStatement(sql2);
ps2.setString(1, "张三");
ps2.addBatch();
ps2.setString(1, "王五");
ps2.addBatch();
ps2.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
}
}
}