目录
在Statement基础上的改进:PreparedStatement
必考面试题:PreparedStatement与Statement区别
必考面试题:executeQuery与executeUpdata区别
Statement的不足之处
1.Statement键盘输入账号密码时,拼接字符串太过于麻烦,可读性降低
2.sql注入:
面试题:什么是SQL注入
一个bug----通过字符串的拼接,可以得到一个恒等的sql语句,可以跳过某些判断。
public static void main(String[] args) {
// SQL注入bug,任意输入即可登录
login("zxcvzxcvzxcv","b' or '1' = '1");
}
public static void login(String username,String password) {
Scanner sc = new Scanner(System.in);
// 1.获取连接
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
stmt = conn.createStatement();
// 用String拼接字符串
String sql = "select * from user where username = '"
+ username + "' and password = '" + password + "'";
// 用StringBuilder拼接字符串
// StringBuilder strb = new StringBuilder("select * from user where username = ");
// strb.append("'").append(username).append("'").append(" and password = '")
// .append(password).append("'");
// String sql = strb.toString();
System.out.println("sql:" + sql);
rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println("登录成功,欢迎回来:" + username);
}else {
System.out.println("账号或密码错误!");
}
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.close(conn,stmt,rs);
}
}
在Statement基础上的改进:PreparedStatement
用PreparedStatement:预编译(预加载)
PreparedStatement优点
1.通过conn获取的对象
2.是Statement接口的子接口
3.sql语句中可以传参,用?(占位符)进行占位,再通过set xxxx方法给?赋值
避免了麻烦的字符串连接
4.提高性能
5.避免sql注入
必考面试题:PreparedStatement与Statement区别
1.Statement执行不带问号的简单sql语句,并返回结果对象,每次执行时都要进行编译
2.PreparedStatement执行带问号参数的预编译的sql语句,动态传参,优点有效率高、可读性高、安全性高,防止sql注入
必考面试题:executeQuery与executeUpdata区别
1.executeQuery用于查询,返回结果集
2.executeUpdata用于增删改,返回更改的行数
@Test
public void test01() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = JDBCUtil.getConnection();
String sql = "update ee set cname = ? where id = ?";
// 预编译
pstmt = conn.prepareStatement(sql);
// 给占位符赋值,根据位置
pstmt.setString(1,"JJ");
pstmt.setInt(2,4);
// 正式执行sql
int i = pstmt.executeUpdate();
System.out.println(i);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.close(conn,pstmt);
}
}
@Test
public void test02() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from ee where id = ?";
// 预编译
pstmt = conn.prepareStatement(sql);
// 给占位符赋值,根据位置
pstmt.setInt(1,4);
rs = pstmt.executeQuery();
while (rs.next()){
String id = rs.getString("id");
String name = rs.getString("cname");
int age = rs.getInt("age");
System.out.println("id:" + id + "cname:" + name + "age" + age);
}
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.close(conn,pstmt,rs);
}
}
@Test
public void test03() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from user where username = ? and password = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"aaa");
pstmt.setString(2,"b' or '1' = '1");
rs = pstmt.executeQuery();
if(rs.next()) {
System.out.println("登录成功...");
}else {
System.out.println("账号或密码错误...");
}
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.close(conn,pstmt,rs);
}
}
连接数据库,关闭资源封装类:
public class JDBCUtil {
public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
Properties properties = new Properties();
Connection connection = null;
properties.load(JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
String url = properties.getProperty("mysql.url");
String driverName = properties.getProperty("mysql.driverName");
String username = properties.getProperty("mysql.username");
String password = properties.getProperty("mysql.password");
Class.forName(driverName);
return DriverManager.getConnection(url, username, password);
}
public static void close(Connection c, Statement s){
if (Objects.nonNull(s)){
try {
s.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (Objects.nonNull(c)){
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection c, Statement s, ResultSet r){
if (Objects.nonNull(s)){
try {
s.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (Objects.nonNull(c)){
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (Objects.nonNull(r)){
try {
r.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
获取元数据
概念;
元数据:表格本身的数据
例如:
表格的列名,结果集的列名
@Test
public void test03() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from ee";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
// 获取元数据
/*
元数据:表格本身的数据
表格的列名,结果集的列名
*/
ResultSetMetaData metaData = rs.getMetaData();
// System.out.println(metaData.getColumnName(1));
// System.out.println(metaData.getColumnName(2));
// System.out.println(metaData.getColumnCount());
// metaData.get
for (int i = 1; i <= metaData.getColumnCount() ; i++) {
metaData.getColumnName(i);
}
while (rs.next()) {
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
System.out.println("id:" + id + ",username:" + username + ",password:" + password);
}
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.close(conn,pstmt,rs);
}
}
数据库事务:是数据库的特性
MySQL的数据库引擎
1.在MySQL中,只有使用了Innodb引擎的数据库才支持事务。
2.事务处理可以用来维护数据的完整性。保证sql语句要么全执行,要么全不执行
3.发生在Dml中,增删改。
事务:
当做出增删改的操作之后,把发生的变化保存在内存中,提交事务,才会真正提交给数据库,从而更改数据库的值
事务的四大特征:
1.原子性
一个事务,要么全完成,要么全部不完成
2.一致性
在事务开始之前和事务结束之后,数据库的完整性没有被破坏
3.隔离性
数据库允许多个事务同时对数据进行处理,每个事务之间相互隔离
4.持久性
事务结束以后,对数据的增删改是永久性的
术语:提交事务、回滚事务(事务回滚)
事务回滚
在出异常的时候进行事务回滚:将已经完成的事务撤销
1.事务一旦提交,就不可能回滚
2.当一个连接被创建时,默认情况下自动提交事务
3.关闭连接时,数据会自动提交事务
操作事务的步骤:
1.关闭事务的自动提交(开启一个事务)
2.提交事务(一个事务完成)
什么时候会发生事务?
经典案例:银行转账
事务是数据库的,不是Java的,只是用idea控制事务。
@Test
public void test01() {
Connection conn = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
try {
conn = JDBCUtil.getConnection();
// 关闭事务的自动提交
// true:开启(默认) false:关闭
// 开启一个事务
conn.setAutoCommit(false);
// 把id为1的账户余额-1000
String sql1 = "update bank set balance = balance - 1000 where id = 1";
pstmt1 = conn.prepareStatement(sql1);
pstmt1.executeUpdate();
String sql2 = "update bank set balance = balance + 1000 where id = 2";
pstmt2 = conn.prepareStatement(sql2);
pstmt2.executeUpdate();
int i = 10 / 0;
// 提交事务
conn.commit();
System.out.println("转账成功...");
} catch (Exception e) {
try {
// 事务回滚
conn.rollback();
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
throw new RuntimeException(e);
} finally {
JDBCUtil.close(conn,pstmt1);
JDBCUtil.close(null,pstmt2);
}
}