在编写JDBC时,用于执行SQL语句时主要涉及到两个接口。下面我通过执行对数据库内容的增删改查来介绍这两个接口。

Statement接口用于执行静态 SQL 语句并返回它所生成结果的对象。

在默认情况下,同一时间每个 Statement 对象在只能打开一个 ResultSet 对象。因此,如果读取一个 ResultSet 对象与读取另一个交叉,则这两个对象必须是由不同的 Statement 对象生成的。如果存在某个语句的打开的当前 ResultSet 对象,则 Statement 接口中的所有执行方法都会隐式关闭它。

package com.TinySK;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestStatement {
    // DML数据操作语句--CRUD:create、retrive、update、delete
    // 插入
    public static void testInsert() {
        // DDL数据定义语句
        Connection conn = new ConnectionUtil().openConnection();
        String sql = "insert into CustomerTbl(name,email) values('Michael','michael@java.com')";
        try {
            Statement stmt = conn.createStatement();
            // 执行SQL语句
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    conn = null;
                    e.printStackTrace();
                }
        }
    }

    // 更新
    public static void testUpdate() {
        // DDL数据定义语句
        Connection conn = new ConnectionUtil().openConnection();
        String sql = "update CustomerTbl set name='Redking' where id=1";
        try {
            Statement stmt = conn.createStatement();
            // 执行SQL语句
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    conn = null;
                    e.printStackTrace();
                }
        }
    }

    // 删除
    public static void testDelete() {
        // DDL数据定义语句
        Connection conn = new ConnectionUtil().openConnection();
        String sql = "delete from CustomerTbl";
        try {
            Statement stmt = conn.createStatement();
            // 执行SQL语句
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    conn = null;
                    e.printStackTrace();
                }
        }
    }

    // 查询
    public static void testQuery() {
        // DDL数据定义语句
        Connection conn = new ConnectionUtil().openConnection();
        String sql = "select * from CustomerTbl";
        try {
            Statement stmt = conn.createStatement();
            // 执行SQL语句
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                // 可以通过列索引
                int id = rs.getInt(1);
                // 可以通过列名称
                String name = rs.getString("name");
                String email = rs.getString(3);
                System.out.println(id + ":" + name + ":" + email);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    conn = null;
                    e.printStackTrace();
                }
        }
    }
}

PreparedStatement表示预编译的 SQL 语句的对象。

SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。

注:用于设置 IN 参数值的设置方法(setShortsetString 等等)必须指定与输入参数的已定义 SQL 类型兼容的类型。例如,如果 IN 参数具有 SQL 类型 INTEGER,那么应该使用 setInt 方法。

如果需要任意参数类型转换,使用 setObject 方法时应该将目标 SQL 类型作为其参数。

package com.TinySK;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class TestPrepareStatement {
    //查询
    public static List<Customer> query(String name){
         Connection conn = new ConnectionUtil().openConnection();
         String sql = "select * from CustomerTbl where name = ?";
         try{
             PreparedStatement pstmt = conn.prepareStatement(sql);
             pstmt.setString(1, name);
             ResultSet rs = pstmt.executeQuery();
             List<Customer> list = new ArrayList<Customer>();
             while(rs.next()){
                 int id = rs.getInt(1);
                 String email = rs.getString(3);
                 Customer c = new Customer();
                 c.setId(id);
                 c.setName(name);
                 c.setEmail(email);
                 list.add(c);
             }
             System.out.println(list.size());
             return list;
         }catch (SQLException e){
             e.printStackTrace();
         }
       return null;
     }
    //添加
    public static void add(Customer c){
        Connection conn = new ConnectionUtil().openConnection();
        String sql = "insert into CustomerTbl(name,email) values(?,?)";
        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, c.getName());
            pstmt.setString(2, c.getEmail());
            pstmt.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
    //删除
    public static void delete(int id){
        Connection conn = new ConnectionUtil().openConnection();
        String sql = "delete from CustomerTbl where Id = ?";
        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
    //更新
    public static void update(String name1,String name2){
        Connection conn = new ConnectionUtil().openConnection();
        String sql = "update CustomerTbl set name = ? where name = ?";
        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name1);
            pstmt.setString(2, name2);
            pstmt.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
  }
}

有关Statement与PreparedStatement接口中的方法还有很多种,随程序功能的不同使用它们的地方也不同,但总的来说,PreparedStatement比较灵活,建议大家查询JDK-API,对PreparedStatement接口所涉及的方法多加留意,这样在使用时会给编程带来不少的便利。