DriverManager(驱动管理类)作用:
1.注册驱动
2.获取数据库连接
1.注册驱动
Class.forName(“com.mysql。jdbc.Driver”)
2.获取连接
static Connectioon getConnection(String url,String user,String password)
(1)url:连接路径
(2)user:用户名
(3)password:密码
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
String url = "jdbc:mysql://localhost:3306/db?useSSL=false&serverTimezone=CST";
String username = " ";
String password = " ";
Connection conn = DriverManager.getConnection(url, username,password);
Connection(数据库连接对象)作用:
1.获取执行SQL的对象
2.管理事务
1.获取执行SQL对象
(1)普通执行SQL对象
Statement createStatement()
(2)预编辑SQL的执行SQL对象:防止SQL注入
PreparedStatement prepareStatement(sql)
(3)执行存储过程的对象
CallableStatement prepareCall(sql)
//定义sql
String sql1 = "update account set money = 4000 where id =1";
String sql2 = "update account set money = 4000 where id =2";
//获取执行sql的对象Statement
Statement stmt = conn.createStatement();
2.事务管理
开启事务:setAutoCommit(boolean autoCommit):true为自动提交事务;false为手动提交事务,即为开启事务
提交事务:commit()
回滚事务:rollback()
try {
//开启事务
conn.setAutoCommit(false);
//执行sql
int count1 = stmt.executeUpdate(sql1);//受影响的行数
//处理结果
System.out.println(count1);
//执行sql
int count2 = stmt.executeUpdate(sql2);//受影响的行数
//处理结果
System.out.println(count2);
//提交事务
conn.commit();
} catch (Exception throwables) {
// TODO Auto-generated catch block
//回滚事务
conn.rollback();
throwables.printStackTrace();
}
Statement作用:
执行SQL语句
执行SQL语句
int executeUpdate(sql):执行DML、DDL语句
返回值:(1)DML语句影响的行数(2)DDL语句执行后,执行成功也可能返回0
ResultSet executeQuery(sql):执行DQL语句
返回值:ResultSet结果集对象
ResultSet(结果集对象)作用:
==封装了SQL查询语句的结果==
@Test
public void testResultSet2() throws Exception {
//1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接:
String url = "jdbc:mysql:///db?useSSL=false";
String username = "";
String password = "";
Connection conn = DriverManager.getConnection(url, username, password);
//3. 定义sql
String sql = "select * from account";
//4. 获取statement对象
Statement stmt = conn.createStatement();
//5. 执行sql
ResultSet rs = stmt.executeQuery(sql);
// 创建集合
List<Account> list = new ArrayList<>();
// 6.1 光标向下移动一行,并且判断当前行是否有数据
while (rs.next()){
Account account = new Account();
//6.2 获取数据 getXxx()
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
//赋值
account.setId(id);
account.setName(name);
account.setMoney(money);
// 存入集合
list.add(account);
}
System.out.println(list);
//7. 释放资源
rs.close();
stmt.close();
conn.close();
}
PreparedStatement 作用:预编译 SQL 语句并执行:预防 SQL 注入问题
// 接收用户输入 用户名和密码
String name = "qweqweq";
String pwd = "' or '1' = '1";
String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
System.out.println(sql);
// 获取stmt对象
Statement stmt = conn.createStatement();
// 执行sql
ResultSet rs = stmt.executeQuery(sql);
// 判断登录是否成功
if(rs.next()){
System.out.println("登录成功~");
}else{
System.out.println("登录失败~");
}
@Test
public void testPreparedStatement() throws
Exception {
//2. 获取连接:
String url = "jdbc:mysql:///db?useSSL=false";
String username = "";
String password = "";
Connection conn = DriverManager.getConnection(url, username,password);
// 接收用户输入 用户名和密码
String name = "zhangsan";
String pwd = "' or '1' = '1";
// 定义sql
String sql = "select * from tb_user where username = ? and password = ?";
// 获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
// 设置?的值
pstmt.setString(1,name);
pstmt.setString(2,pwd);
// 执行sql
ResultSet rs = pstmt.executeQuery();
// 判断登录是否成功
if(rs.next()){
System.out.println("登录成功~");
}else{
System.out.println("登录失败~");
}
//7. 释放资源
rs.close();
pstmt.close();
conn.close();
}
PreparedStatement 好处:预编译 SQL ,性能更高防止 SQL 注入: == 将敏感字符进行转义 ==
转义的SQL如下:
在获取PreparedStatement对象时,将sql语句发送给mysql服务器进行检查,编译(这些步骤很耗时) 执行时就不用再进行这些步骤了,速度更快。