代码的漏洞
通过1 'or ' 1 '=' 1让password判断永久为true从而登录到系统
-
PreparedStatement的使用
目录
一.用一个preparedStatement存入5万次 访问了5万次io效率低下
二.用pstmt的addBatch();批处理 暂存一定的数据统计发送给数据库
- public interface PreparedSteatment extends Stetement
- 称为预编译的statement对象
- 和statement区别
- 对sql语句不使用拼接的方式,而是用问号代替,占位
- 在获取preparedStatemment对象时就要求执行
- 在数据库服务器上只要执行一次就会被缓存下来 preparedStatemment的优势
public static void main(String[] args) throws Exception{ Scanner sc = new Scanner(System.in); String next1 = sc.next(); String next2 = sc.next(); Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/companydb", "root", "root"); Class.forName("com.mysql.jdbc.Driver");//mysql的 DriverManager.getConnection("jdbc:mysql:///companydb", "root", "root"); String sql = "SELECT ?,? FROM User "; PreparedStatement pstatement = (PreparedStatement) connection.prepareStatement(sql); pstatement.setString(1,next1); pstatement.setString(2,next2); pstatement.executeQuery(); System.out.println("完成"); pstatement.close(); connection.close(); }
批量数据添加
- 实际开发中,用preparedStatemment
-
一.用一个preparedStatement存入5万次 访问了5万次io效率低下
-
二.用pstmt的addBatch();批处理 暂存一定的数据统计发送给数据库
示例代码:
public static void main(String[] args) throws Exception{ Class.forName("com.mysql.jdbc.Driver");//mysql的 //注册驱动 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root"); //获取连接 DriverManager.getConnection("jdbc:mysql:///test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true", "root", "root"); //打开批处理 String sql ="insert into account values(null ,?,?) "; //sql语句 PreparedStatement pstatement = (PreparedStatement) connection.prepareStatement(sql); //获取PreparedStatement对象执行sql for (int i =1;i<50000;i++) { pstatement.setString(1,"account"+i); pstatement.setInt(2,i); pstatement.addBatch(); if (i%1000==0)//一次处理1000条数据 { pstatement.executeBatch(); pstatement.clearBatch(); } }
手动提交事务
- 批量删除TRUNCATE:删除磁盘文件,新建一个空表
- 默认情况下,mysql是不支持批处理,在jdbc批处理参数
- 打开批处理开关characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
- public interface PreparedSteatment extends Stetement
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.jdbc.Driver");
Connection connection = (Connection) DriverManager.getConnection
("jdbc:mysql:///test", "root", "root");
connection.setAutoCommit(false);
String sql1="update account set accounts=? where card_id=1";
String sql2="update account set accounts=? where card_id=2";
PreparedStatement pst = (PreparedStatement) connection.prepareStatement(sql1);
PreparedStatement pst1 = (PreparedStatement) connection.prepareStatement(sql2);
pst.setInt(1,1500);
pst1.setInt(1,500);
pst.executeUpdate();
pst1.executeUpdate();
connection.commit();
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
connection.close();
}
}