一. 接口说明
1.Statement和PrepareStatement都是执行sql 的接口
2.PrepareStatement可以预防sql注入,实现原理是将SQL特殊的符号加反斜杠,使其转换为普通的字符串,而不是sql命令,就是我们常说的预编译过程,而Statement的没有这样的功能的
2.接口说明和使用实列
1.获取对象和执行更新操作sql
/**
* @author zeng
*/
public class Main {
private static final String username = "root";
private static final String password = "root";
private static final String url = "jdbc:mysql://bar-mysql:3306/bar_baruser?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai";
public static void main(String[] args) throws Exception {
final Connection connection = DriverManager.getConnection(url, username, password);
// 获取一个 Statement对象,用于将SQL语句发送到数据库。
Statement statement = connection.createStatement();
String sqlStatement="update bar_user set username='zhangsan' where user_id=1";
// 返回影响的行数
final int isUpdate = statement.executeUpdate(sqlStatement);
System.out.println(isUpdate);//1
// PreparedStatement通过 ? 预留参数 在preparedStatement调用set 设置值,索引从1开始
String sqlPreparedStatement=
"update bar_user set username = ? where user_id = ? ";
// 获取一个 PreparedStatement对象
final PreparedStatement preparedStatement
= connection.prepareStatement(sqlPreparedStatement);
// 设置username 的值
preparedStatement.setString(1,"lishi");
// 设置user_id 的值
preparedStatement.setInt(2,1);
final int isPreparedStatementUpdate = preparedStatement.executeUpdate();
System.out.println(1);
// 关闭资源
statement.close();
connection.close();
preparedStatement.close();
}
}
2.Statement事务
1.setAutoCommit(false); 设置手动事务
2. connection.commit();提交事务
3.connection.rollback(); 回滚事务
示例代码
import java.sql.*;
/**
* @author zeng
*/
public class Main {
private static final String username = "root";
private static final String password = "root";
private static final String url = "jdbc:mysql://bar-mysql:3306/bar_baruser?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai";
public static void main(String[] args) throws Exception {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, username, password);
// 获取一个 Statement对象,用于将SQL语句发送到数据库。
Statement statement = connection.createStatement();
// 设置手动提交事务
connection.setAutoCommit(false);
String sqlStatement = "update bar_user set username='admin' where user_id=1";
// 返回影响的行数
final int isUpdate = statement.executeUpdate(sqlStatement);
// 假如给一个异常
int i=1/0;
connection.commit();
}catch (Exception e){
if (connection!=null){
// 回滚数据
connection.rollback();
}
e.printStackTrace();
}finally {
if (connection!=null){
// 关闭资源
connection.close();
}
}
}
}