JDBC中的PreparedStatement对象

本文主要是介绍JDBC中的PreparedStatement对象，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

提取工具类

public class JdbcUtils {private static String driver = null;private static String url = null;private static String username = null;private static String password = null;static{try{InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");Properties properties = new Properties();properties.load(in);driver = properties.getProperty("driver");url = properties.getProperty("url");username = properties.getProperty("username");password = properties.getProperty("password");//驱动只用加载一次Class.forName(driver);} catch (Exception e) {e.printStackTrace();}}//获得连接public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url,username,password);}//释放连接资源public static void release(Connection conn, PreparedStatement ps, ResultSet rs){if(rs != null){try {rs.close();}catch(SQLException e){e.printStackTrace();}}if(ps != null){try {ps.close();}catch(SQLException e){e.printStackTrace();}}if(conn != null){try {conn.close();}catch(SQLException e){e.printStackTrace();}}}
}

insert添加

public class TestInsert {public static void main(String[] args) {Connection conn = null;PreparedStatement ps = null;try {conn = JdbcUtils.getConnection();//使用?占位符代替参数String sql = "insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";ps = conn.prepareStatement(sql);//给参数手动赋值ps.setInt(1,5);ps.setString(2,"zhaoliu");ps.setString(3,"123456");ps.setString(4,"123456@qq.com");ps.setDate(5,new java.sql.Date(new Date().getTime()));//执行int i = ps.executeUpdate();if(i>0){System.out.println("插入成功");}} catch (SQLException e) {e.printStackTrace();}finally {JdbcUtils.release(conn,ps,null);}}
}

delete删除

public class TestDelete {public static void main(String[] args) {Connection conn = null;PreparedStatement ps = null;try {conn = JdbcUtils.getConnection();String sql = "delete from users where id = ?";ps = conn.prepareStatement(sql);ps.setInt(1,5);int i = ps.executeUpdate();if(i>0){System.out.println("删除成功");}} catch (SQLException e) {e.printStackTrace();}finally {JdbcUtils.release(conn,ps,null);}}
}

update修改

public class TestUpdate {public static void main(String[] args) {Connection conn = null;PreparedStatement ps = null;try {conn = JdbcUtils.getConnection();String sql = "update users set name = ? where id = ?";ps = conn.prepareStatement(sql);ps.setString(1,"张三");ps.setInt(2,1);int i = ps.executeUpdate();if(i>0){System.out.println("修改成功");}} catch (SQLException e) {e.printStackTrace();}finally {JdbcUtils.release(conn,ps,null);}}
}

select查询

public class TestSelect {public static void main(String[] args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = JdbcUtils.getConnection();String sql = "select `name`,`password` from users where `id` = ?";ps = conn.prepareStatement(sql);ps.setInt(1,1);rs = ps.executeQuery();while (rs.next()){System.out.println("name=" + rs.getString("name"));System.out.println("password=" +rs.getString("password"));}} catch (SQLException e) {e.printStackTrace();}finally {JdbcUtils.release(conn,ps,rs);}}
}

防止SQL注入

PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符，假设其中存在转义字符，比如’'就会直接忽略

public class SQL {public static void main(String[] args) {//正常登录login("张三","123456");//SQL注入没用//login("'' or 1=1","123456");}//登录业务public static void login(String username,String password){Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = JdbcUtils.getConnection();String sql = "select * from users where `name` = ? and `password` = ?";ps = conn.prepareStatement(sql);ps.setString(1,username);ps.setString(2,password);rs = ps.executeQuery();while (rs.next()){System.out.println("name="+rs.getString("name"));System.out.println("password="+rs.getString("password"));}} catch (SQLException e) {e.printStackTrace();}finally {JdbcUtils.release(conn,ps,rs);}}
}