jdbc主要的4个api分别是Connection,PreparedStatement,Statement,ResultSet.其中PreparedStatement与Statement的效果几乎一致,在平常的开发当中建议使用PreparedStatement原因不只是因为PreparedStatement比Statement进行批量操作时的效率高还是有在程序安全性上PreparedStatement较Statement安全。如果我们在执行的sql中需要传入参数,使用Statement时我们需要拼接sql这样就容易让人利用sql注入攻击我们的程序而使用PreparedStatement时可以使用"?"占位符从而避免该问题,下面来看一个例子:
Statement:
String sql = "select * from tb_name where name= '"+varname+"' and passwd='"+varpasswd+"'";
如果我们把[' or '1' = '1]作为varpasswd传入进来.用户名随意,看看会成为什么?
select * from tb_name = '随意' and passwd = '' or '1' = '1';
因为'1'='1'肯定成立,所以可以任何通过验证.更有甚者:
把[';drop table tb_name;]作为varpasswd传入进来,则:
select * from tb_name = '随意' and passwd = '';drop table tb_name;有些数据库是不会让你成功的,但也有很多数据库就可以使这些语句得到执行.
下面是jdbc的一个查询例子其中Page是工具类中的分页工具类代码如下:
private Connection conn ;
private PreparedStatement pstmt ;
private ResultSet rs ;
@Override
public Page<CompanyPhotoVo> loadCompanyPhotoList(int pageNo, int pageSize) throws Exception {
List<CompanyPhotoVo> list = new ArrayList<CompanyPhotoVo>(0);
String sql = "select t.id, t.b_path, t.s_path, t.description from cp_photo t";
//获取总记录数
Integer totalCount = getTotalCount();
Page<CompanyPhotoVo> page = new Page<CompanyPhotoVo>(pageNo, pageSize, totalCount);
//分页sql
sql += " limit " + page.getFirstResult() + "," + pageSize;
System.out.println("*****" + sql);
conn = DBConn.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
CompanyPhotoVo vo = new CompanyPhotoVo();
vo.setId(rs.getInt("id"));
vo.setBigPath(rs.getString("b_path"));
vo.setSmallPath(rs.getString("s_path"));
vo.setDescription(rs.getString("description"));
list.add(vo);
}
page.setList(list);
return page;
}
下面是一个insert的例子,注意我们在使用PreparedStatement set参数时 下标是从1开始的,代码如下:
private Connection conn ;
private PreparedStatement pstmt ;
//private ResultSet rs ;
@Override
public void addCustomerReq(CustomerReqVo customerReqVo) throws Exception {
String sql = "INSERT INTO cp_customer_req(customer_name, customer_phone, customer_email, " +
"customer_company, customer_req, submit_time)VALUES(?,?,?,?,?,now());";
conn = DBConn.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, customerReqVo.getCustomerName());
pstmt.setString(2, customerReqVo.getCustomerPhone());
pstmt.setString(3, customerReqVo.getCustomerEmail());
pstmt.setString(4, customerReqVo.getCustomerCompany());
pstmt.setString(5, customerReqVo.getCustomerReq());
System.out.println("*****" + sql);
int result = pstmt.executeUpdate();
System.out.println("添加结果(1表示成功):"+result);
}