JDBC(3)：PreparedStatement对象介绍

一，PreparedStatement介绍

• PreperedStatement是Statement的子类，它的实例对象可以通过Connection.preparedStatement()方法获得，相对于Statement对象而言：PreperedStatement可以避免SQL注入的问题
• Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写

二，使用PreparedStatement对象完成对数据库的CRUD操作

注意：编写测试代码时要提前搭建好实验环境我的试验环境已经在我的博客【JDBC(二)---使用JDBC对数据库进行CRUD】中搭建完毕

测试代码

import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class DemoTest1 {



<span class="hljs-meta">@Test</span>  <span class="hljs-comment">//插入</span>




public void insert(){

Connection connection=null;

PreparedStatement preparedStatement=null;

try {

//获取一个数据库连接

connection= Demo.getConnection();

//要执行的SQL命令，SQL中的参数使用?作为占位符

String sql="insert into abc(id,name,password) values(?,?,?)";

//通过conn对象获取负责执行SQL命令的prepareStatement对象

preparedStatement = connection.prepareStatement(sql);

//为SQL语句中的参数赋值

preparedStatement.setInt(1,1);//插入id=1

preparedStatement.setString(2,"钢铁侠");//插入name=钢铁侠

preparedStatement.setString(3,"123");//插入password=123

//执行插入操作，executeUpdate方法返回成功的条数

int i = preparedStatement.executeUpdate();

if(i>0){

System.out.println("插入成功！！！");

}

} catch (SQLException e) {

e.printStackTrace();

}finally {

Demo.release(connection,preparedStatement,null);

}

}



<span class="hljs-meta">@Test</span> <span class="hljs-comment">//删除</span>
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">delete</span><span class="hljs-params">()</span></span>{
    Connection connection=<span class="hljs-keyword">null</span>;
    PreparedStatement preparedStatement=<span class="hljs-keyword">null</span>;
    <span class="hljs-keyword">try</span> {
        connection = Demo.getConnection();
        <span class="hljs-comment">//当id=？时 删除这一行</span>
        String sql=<span class="hljs-string">"delete from abc where id=?"</span>;
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);
        <span class="hljs-keyword">int</span> i = preparedStatement.executeUpdate();
        <span class="hljs-keyword">if</span>(i&gt;<span class="hljs-number">0</span>){
            System.out.println(<span class="hljs-string">"删除成功!!!"</span>);
        }
    } <span class="hljs-keyword">catch</span> (SQLException e) {
        e.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        Demo.release(connection,preparedStatement,<span class="hljs-keyword">null</span>);
    }
}

<span class="hljs-meta">@Test</span> <span class="hljs-comment">//更新</span>
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">update</span><span class="hljs-params">()</span></span>{
    Connection connection=<span class="hljs-keyword">null</span>;
    PreparedStatement preparedStatement=<span class="hljs-keyword">null</span>;
    ResultSet resultSet=<span class="hljs-keyword">null</span>;
    <span class="hljs-keyword">try</span> {
        connection= Demo.getConnection();
        <span class="hljs-comment">//当id=?时 更新name和password的数据</span>
        String sql=<span class="hljs-string">"update abc set name=?,password=? where id=?"</span>;
        preparedStatement= connection.prepareStatement(sql);
        preparedStatement.setString(<span class="hljs-number">1</span>,<span class="hljs-string">"蜘蛛侠"</span>);
        preparedStatement.setString(<span class="hljs-number">2</span>,<span class="hljs-string">"567"</span>);
        preparedStatement.setInt(<span class="hljs-number">3</span>,<span class="hljs-number">1</span>);
        <span class="hljs-keyword">int</span> i = preparedStatement.executeUpdate();
        <span class="hljs-keyword">if</span>(i&gt;<span class="hljs-number">0</span>){
            System.out.println(<span class="hljs-string">"更新成功"</span>);
        }
    } <span class="hljs-keyword">catch</span> (SQLException e) {
        e.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        Demo.release(connection,preparedStatement,<span class="hljs-keyword">null</span>);
    }
}

<span class="hljs-meta">@Test</span> <span class="hljs-comment">//查找</span>
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">find</span><span class="hljs-params">()</span></span>{
    Connection connection=<span class="hljs-keyword">null</span>;
    PreparedStatement preparedStatement=<span class="hljs-keyword">null</span>;
    ResultSet resultSet=<span class="hljs-keyword">null</span>;
    <span class="hljs-keyword">try</span> {
        connection= Demo.getConnection();
        <span class="hljs-comment">//当id=?时 查询这个一列</span>
        String sql=<span class="hljs-string">"select * from abc where id=?"</span>;
        preparedStatement= connection.prepareStatement(sql);
        preparedStatement.setInt(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);
        resultSet = preparedStatement.executeQuery();
        <span class="hljs-keyword">if</span>(resultSet.next()){
            <span class="hljs-comment">//查询这一列的name </span>
            System.out.println(resultSet.getString(<span class="hljs-string">"name"</span>));
        }
    } <span class="hljs-keyword">catch</span> (SQLException e) {
        e.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        Demo.release(connection,preparedStatement,resultSet);
    }

}




}


<span class="hljs-meta">@Test</span>  <span class="hljs-comment">//插入</span>
<span class="hljs-meta">@Test</span> <span class="hljs-comment">//删除</span>
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">delete</span><span class="hljs-params">()</span></span>{
    Connection connection=<span class="hljs-keyword">null</span>;
    PreparedStatement preparedStatement=<span class="hljs-keyword">null</span>;
    <span class="hljs-keyword">try</span> {
        connection = Demo.getConnection();
        <span class="hljs-comment">//当id=？时 删除这一行</span>
        String sql=<span class="hljs-string">"delete from abc where id=?"</span>;
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);
        <span class="hljs-keyword">int</span> i = preparedStatement.executeUpdate();
        <span class="hljs-keyword">if</span>(i&gt;<span class="hljs-number">0</span>){
            System.out.println(<span class="hljs-string">"删除成功!!!"</span>);
        }
    } <span class="hljs-keyword">catch</span> (SQLException e) {
        e.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        Demo.release(connection,preparedStatement,<span class="hljs-keyword">null</span>);
    }
}

<span class="hljs-meta">@Test</span> <span class="hljs-comment">//更新</span>
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">update</span><span class="hljs-params">()</span></span>{
    Connection connection=<span class="hljs-keyword">null</span>;
    PreparedStatement preparedStatement=<span class="hljs-keyword">null</span>;
    ResultSet resultSet=<span class="hljs-keyword">null</span>;
    <span class="hljs-keyword">try</span> {
        connection= Demo.getConnection();
        <span class="hljs-comment">//当id=?时 更新name和password的数据</span>
        String sql=<span class="hljs-string">"update abc set name=?,password=? where id=?"</span>;
        preparedStatement= connection.prepareStatement(sql);
        preparedStatement.setString(<span class="hljs-number">1</span>,<span class="hljs-string">"蜘蛛侠"</span>);
        preparedStatement.setString(<span class="hljs-number">2</span>,<span class="hljs-string">"567"</span>);
        preparedStatement.setInt(<span class="hljs-number">3</span>,<span class="hljs-number">1</span>);
        <span class="hljs-keyword">int</span> i = preparedStatement.executeUpdate();
        <span class="hljs-keyword">if</span>(i&gt;<span class="hljs-number">0</span>){
            System.out.println(<span class="hljs-string">"更新成功"</span>);
        }
    } <span class="hljs-keyword">catch</span> (SQLException e) {
        e.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        Demo.release(connection,preparedStatement,<span class="hljs-keyword">null</span>);
    }
}

<span class="hljs-meta">@Test</span> <span class="hljs-comment">//查找</span>
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">find</span><span class="hljs-params">()</span></span>{
    Connection connection=<span class="hljs-keyword">null</span>;
    PreparedStatement preparedStatement=<span class="hljs-keyword">null</span>;
    ResultSet resultSet=<span class="hljs-keyword">null</span>;
    <span class="hljs-keyword">try</span> {
        connection= Demo.getConnection();
        <span class="hljs-comment">//当id=?时 查询这个一列</span>
        String sql=<span class="hljs-string">"select * from abc where id=?"</span>;
        preparedStatement= connection.prepareStatement(sql);
        preparedStatement.setInt(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);
        resultSet = preparedStatement.executeQuery();
        <span class="hljs-keyword">if</span>(resultSet.next()){
            <span class="hljs-comment">//查询这一列的name </span>
            System.out.println(resultSet.getString(<span class="hljs-string">"name"</span>));
        }
    } <span class="hljs-keyword">catch</span> (SQLException e) {
        e.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        Demo.release(connection,preparedStatement,resultSet);
    }

}