PerparedStatement接口详解:
一、jdbc工具类扩展:
//close()方法重载
public static void close(Connection conn,Statement stmt,ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
二、PreparedStatement执行增删改的sql语句:
package sram.jdbc.connection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import sram.util.jdbc.JdbcUtil;
/**
* PreparedStatement执行sql语句
*/
public class Demo4 {
/**
* 插入、修改、删除
*/
@Test
public void test(){
Connection conn = null;
PreparedStatement stmt = null;
try {
//1.获取连接
conn = JdbcUtil.getConnection();
//2.准备预编译的sql
//插入:String sql = "INSERT INTO dept(NAME,BossName) VALUES(?,?)";//?表示一个参数的占位符
//修改:String sql = "UPDATE dept SET BossName=? WHERE id=?";
//删除:
String sql = "DELETE FROM dept WHERE id=?";
//3.执行预编译sql语句(检查语法)
stmt = conn.prepareStatement(sql);
//4.设置参数值
/**
* 参数一: 参数位置 从1开始
*/
/*插入:
stmt.setString(1, "财务部");
stmt.setString(2, "孙五");
*/
/*修改
stmt.setString(1, "钱八");
stmt.setInt(2, 7);
*/
//删除:
stmt.setInt(1, 7);
//5.发送参数,执行sql。不同于Statement接口的该方法,这里()不带sql语句。
int count = stmt.executeUpdate();
System.out.println("影响了"+count+"行");
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, stmt);
}
}
}
三、PreparedStatement执行查询的sql语句:
package sram.jdbc.connection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import sram.util.jdbc.JdbcUtil;
/**
* 查询
*/
public class Demo5 {
@Test
public void test(){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JdbcUtil.getConnection();
//2.准备预编译的sql
String sql = "SELECT * FROM dept";
//3.预编译
stmt = conn.prepareStatement(sql);
//4.执行sql
rs = stmt.executeQuery();
//5.遍历rs
while(rs.next()){
int id =rs.getInt("id");
String name =rs.getString("NAME");
String bossName = rs.getString("BossName");
System.out.println(id+","+name+","+bossName);
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
//关闭资源
JdbcUtil.close(conn, stmt, rs);
}
}
}