PreparedStatement对象
- 第一步:连接数据库(在工具类中)
- 第二步:编写SQL语句
- 第三步:预编译SQL,但不执行
- 第四步:手动为参数赋值
- 第五步:执行
注意:PreparedStatement可以避免SQL注入,因为他把专递进来的参数当做字符处理。若其中存在转义字符,例如 ’ 会被直接转义
使用方法(增删改)
-
插入
import com.lb.lesson02.utils.JdbcUtils; import java.sql.*; public class TestInsert { public static void main(String[] args) { Connection conn = null; PreparedStatement pre = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //使用占位符”?“代替参数 String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)"; pre= conn.prepareStatement(sql); //预编译SQL,先写sql,但不执行 //手动为参数赋值 pre.setInt(1,4); pre.setString(2,"华为"); pre.setString(3,"123456"); pre.setString(4,"hua@qq.com"); //new Date().getTime():获得时间戳 注意:导入Date()时要注意区分sql.Date()(数据库)和util.Date()(Java) pre.setDate(5,new java.sql.Date(new java.util.Date().getTime())); //执行 int i = pre.executeUpdate(); if (i > 0) { System.out.println("插入成功!"); } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn, pre, rs); } } }
-
删除
import com.lb.lesson02.utils.JdbcUtils; import java.sql.*; public class TestDelete { public static void main(String[] args) { Connection conn = null; PreparedStatement prs = null; try { conn = JdbcUtils.getConnection(); String sql = "delete from users where id = ? "; prs = conn.prepareStatement(sql); //预编译 //手动赋值 prs.setInt(1,5); //执行 int i = prs.executeUpdate(); if (i > 0) { System.out.println("删除成功!"); } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn, prs, null); //未创建ResultSet所以为空 } } }
-
修改
import com.lb.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestUpdate { public static void main(String[] args) { Connection conn = null; PreparedStatement prs = null; ResultSet re = null; try { conn = JdbcUtils.getConnection(); String sql = "update users set `NAME`=? where id = ?"; prs = conn.prepareStatement(sql); prs.setString(1,"三星"); prs.setInt(2,3); int i = prs.executeUpdate(); if (i > 0) { System.out.println("更改成功!"); } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn, prs, re); } } }
使用方法(查询)
import com.lb.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement prs = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "SELECT * FROM `users` where id = ?";
prs = conn.prepareStatement(sql);
prs.setInt(1,2);
rs = prs.executeQuery();
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, prs, rs);
}
}
}
注意:配置文件(连接数据库)及工具类(JdbcUtils)在Statement对象中;
链接地址:配置文件及工具类