PreparedStatement、Statement、CallableStatement

分别介绍：

①、PreparedStatement：

PreparedStatement是用来执行SQL查询语句的API之一，用于执行参数化查询；是java.sql包下面的一个接口，用来执行SQL语句查询，通过调用connection.preparedStatement(sql)方法可以获得PreparedStatment对象。数据库系统会对sql语句进行预编译处理（如果JDBC驱动支持的话），预处理语句将被预先编译好，这条预编译的sql查询语句能在将来的查询中重用，这样一来，它比Statement对象生成的查询速度更快。下面是一个例子：

public class PreparedStmtExample {
 
    public static void main(String args[]) throws SQLException {
        Connection conn = DriverManager.getConnection("数据库地址", "名字", "密码");
        PreparedStatement preStatement = conn.prepareStatement("select distinct loan_type from loan where bank=?");
        preStatement.setString(1, "Citibank");
 
        ResultSet result = preStatement.executeQuery();
 
        while(result.next()){
            System.out.println("Loan Type: " + result.getString("loan_type"));
        }       
    }
}
②、Statement：

用于通用查询，不可以传递动态参数，（不建议使用）下面是一个例子：

Connection connection = DriverManager.getConnection("数据库地址", "名字", "密码");

//检查数据是否存
String strSql1 = "SELECT COUNT(*) count FROM user_authentication WHERE user_id = 1  AND status = 3";
Statement statement =connection.createStatement();
ResultSet resultSet = statement.executeQuery(strSql1);
int row = 0;
 f(resultSet.next())    
 {    
    row = resultSet.getInt("count");
 }

③、CallableStatement：

是用于存储过程，下面是一个例子：(未验证)

Connection connection = DriverManager.getConnection("数据库地址", "名字", "密码");

CallableStatement cstmt = connection.prepareCall("{call getTestData(?, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);
cstmt.executeQuery();
byte x = cstmt.getByte(1);
java.math.BigDecimal n = cstmt.getBigDecimal(2, 3);

上面是网上的案例，我一般是这么调用的：

ZpmsStoredProcedure storedProcedure = new ZpmsStoredProcedure(getDataSource());
 storedProcedure.setSql("ypl_ht_review_realname");//存储过程名字

storedProcedure.declareParameter(new SqlParameter("userId",Types.INTEGER));//输入参数
storedProcedure.declareParameter(new SqlParameter("stateCode",Types.INTEGER));

storedProcedure.declareParameter(new SqlOutParameter("result", Types.INTEGER));//输出参数
Map<String, Object> out = storedProcedure.execute(map);//执行

int result = Integer.parseInt(String.valueOf(out.get("result")));//得到输出参数

----------------------------------------------下面是上面用的封装类------------------------------------------------------------

ZpmsStoredProcedure：

import javax.sql.DataSource;
import org.springframework.jdbc.object.StoredProcedure;

public class ZpmsStoredProcedure extends StoredProcedure {
    public ZpmsStoredProcedure(DataSource dataSource){
        super();
        super.setDataSource(dataSource);
    }
}

getDataSource()由来：

import javax.sql.DataSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.simple.SimpleJdbcCallOperations;

public abstract class AbstractDao {
    private DataSource dataSource;
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    public DataSource getDataSource() {
        return dataSource;
    }
    protected SimpleJdbcCallOperations getSimpleJdbcCall() {
        return new SimpleJdbcCall(dataSource);
    }
}