PreparedStatement接口详解

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);
        }
    }
}