PreparedStatement可以防止SQL注入,效率更高。
1. 增
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = jdbcUtils.getConnection();
// 区别
// 使用问号占位符代替参数
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
ps = conn.prepareStatement(sql); // 预编译SQl,先写sql,然后不执行
// 手动给参数赋值
ps.setInt(1,5);
ps.setString(2,"haha");
ps.setString(3,"55555");
ps.setString(4,"5555@qq.com");
// 注意点:sql.Date:数据库用 util.Date:Java用 Date().getTime():获得时间戳 java.sql.Date():转换
ps.setDate(5, new java.sql.Date(new Date().getTime()));
// 执行
int i = ps.executeUpdate();
if (i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
jdbcUtils.release(conn,ps,null);
}
}
}
2. 删
public class TestDelete {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = jdbcUtils.getConnection();
// 区别
// 使用问号占位符代替参数
String sql = "delete from users where id = ?";
ps = conn.prepareStatement(sql); // 预编译SQl,先写sql,然后不执行
// 手动给参数赋值
ps.setInt(1,5);
// 执行
int i = ps.executeUpdate();
if (i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
jdbcUtils.release(conn,ps,null);
}
}
}
3. 改
public class TestUpdate {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = jdbcUtils.getConnection();
// 区别
// 使用问号占位符代替参数
String sql = "update users set `NAME`=? where id=?";
ps = conn.prepareStatement(sql); // 预编译SQl,先写sql,然后不执行
// 手动给参数赋值
ps.setString(1,"hahaha");
ps.setInt(2,5);
// 执行
int i = ps.executeUpdate();
if (i>0){
System.out.println("修改成功!");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
jdbcUtils.release(conn,ps,null);
}
}
}
4. 查
public class TestSelect {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = jdbcUtils.getConnection();
String sql = "select * from users where id=?"; // 编写SQL
ps = conn.prepareStatement(sql); // 预编译
ps.setInt(1,4); // 传递参数
rs = ps.executeQuery(); // 执行
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
jdbcUtils.release(conn,ps,rs);
}
}
}
5. 防止SQL注入
public class 防止SQL注入 {
public static void main(String[] args) throws SQLException {
login("chenyang8","123456"); //正常登录
//login(" ' 'or 1=1","123456"); // 技巧
}
// 登录业务
public static void login(String username, String password) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = jdbcUtils.getConnection();
// PreparedStatement 防止SQl注入的本质,把传递进来的参数当作字符
// 假设其中存在转义字符,比如说 ' 会被直接转义
String sql = "select * from users where `NAME`=? and `PASSWORD`=?";
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery(); //查询完毕会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}