创建工程
- 采用IDEA创建一个空项目,在File/Project Structure里,配置SDK信息
- 在Modules下创建一个模块
- 导入驱动jar包(推荐:在工程下创建一个lib文件夹,将jar包拷进去,右键添加到library)
用表account
创建一个account表,用于后续的分析。
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
money INT
);
INSERT INTO account VALUES(NULL, '张三',1000);
INSERT INTO account VALUES(NULL, '李四',1000);
INSERT INTO account VALUES(NULL, '王五',1000);
SELECT * FROM account;
代码流程
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class demo {
public static void main(String[] args) throws Exception {
// 1.注册驱动(可以不写)
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获取链接
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
// 3.定义SQL语句
String sql = "update account set money = 3000 where id = 1";
// 4.获取执行sql的对象
Statement stmt = conn.createStatement();
// 5.执行sql
int count = stmt.executeUpdate(sql);
// 6.处理结果
System.out.println(count);
// 7.释放资源
stmt.close();
conn.close();
}
}
DriverManager(API)
这是jdbc的驱动管理类,主要作用有两个:
①注册驱动
②获取数据库连接
getConnection参数 | 注解 |
url | jdbc:mysql://IP地址(或域名):端口号/库名?参数 本机的IP地址(域名)可用127.0.0.1(localhost) 编码参数:?characterEncoding=UTF-8 |
user | 用户名:root(或其他账户) |
password | 密码 |
Connection(API)
数据库连接对象,主要作用有两个:
①获取执行SQL的对象
※普通SQL执行对象:Statement createStatement()
※预编译SQL的SQL执行对象:PreparedStatement prepareStatement(sql)
※执行储存过程的对象:CallableStatement prepareCall(sql)
②管理事务
※MySQL事务管理
开启事务:BEGIN; START TRANSACTION;
提交事务:COMMIT;
回滚事务:ROLLBACK;
MySQL默认自动提交事务
※JDBC事务管理
开启事务:setAutoCommit(boolean autoCommit); ture为自动提交事务,false为手动提交事务,即开启事务
提交事务:commit();
回滚事务:rollback();
jdbc执行事务案例(采用try-catch)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class ConnectionTest {
public static void main(String[] args) throws Exception {
// 连接SQL
String url = "jdbc:mysql:///db1";
String username = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
// 编写SQL语句
String sql01 = "update account set money = 6000 where id = 1";
String sql02 = "update account set money = 8000 where id = 2";
// 获取执行sql的对象
Statement stmt = conn.createStatement();
// 事务
try {
// 开启事务
conn.setAutoCommit(false);
// 执行内容
int count01 = stmt.executeUpdate(sql01);
int count02 = stmt.executeUpdate(sql02);
System.out.println(count01);
System.out.println(count02);
// 提交事务
conn.commit();
} catch (Exception throwables) {
//回滚事务
conn.rollback();
throwables.printStackTrace();
}
// 释放资源
stmt.close();
conn.close();
}
}
// Tips: Ctrl+Alt+T 可以快速选择代码进行包裹
Statement(API)
作用:执行SQL语句
①执行SQL语句:
执行DML、DDL语句:int executeUpdate(sql);
返回结果:1)影响行数 2)DDL语句执行后,执行成功也可能返回0
执行DQL语句:ResultSet executeQuery(sql)
返回结果:ResultSet结果集对象
DML和DDL的单元测试
import org.testng.annotations.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class StatementTest {
@Test // 单元测试
// 执行DML语句的单元测试
public void testDML() throws Exception {
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "update account set money = 6666 where id = 2";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
if (count >0){System.out.println("修改成功!");}else {System.out.println("修改失败!");}
stmt.close();
conn.close();
}
@Test
// 执行DDL语句的单元测试
public void testDDL() throws Exception {
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "CREATE TABLE account2(id INT primary key auto_increment, name varchar(20), money int)";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
System.out.println(count);
stmt.close();
conn.close();
}
}
ResultSet(API)
作用:封装了DQL查询语句的结果
执行DQL语句,返回ResultSet对象:ResultSet stmt.excuteQuery(sql);
方法:①next()
1)将光标向下移动一行 2)判断当前行是否为有效行
// 通常用法
while(re.next()){
// 获取数据
rs.getXxx(参数); // 参数一般是列名
}
②xxx getXxx(参数)
获取数据
ResultSet使用案例
import org.testng.annotations.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ResultSetTest {
@Test
public void testDQL() throws Exception {
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
// 定义DQL
String sql = "SELECT * FROM account";
// 获取执行对象
Statement stmt = conn.createStatement();
// 执行sql,获取结果
ResultSet rs = stmt.executeQuery(sql);
// 处理结果,遍历rs中所有数据
// 光标向下移动一行,并判断当前行是否有数据
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
Double money = rs.getDouble("money");
System.out.print(id+" ");System.out.print(name+" ");System.out.println(money+" ");
System.out.println("-------------------------");
}
rs.close();
stmt.close();
conn.close();
}
}
案例
需求:查询account账户表数据,封装为Account对象中,并且存储到ArrayList集合中。
步骤:1. 定义实体类Account
2. 查询数据,封装到Account对象中
3. 将Account对象存入ArrayList集合中
// 创建一个Account类
public class Account {
private int id;
private String name;
private double money;
public int getId() { return id; }
public void setId(int id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public double getMoney() { return money; }
public void setMoney(double money) { this.money = money; }
@Override
public String toString() {
return "Account{" + "id=" + id + ", name='" + name + '\'' + ", money=" + money + '}';
}
}
// Tips:一定要写toString,不然打印出来的是地址
package com.jdbc;
import com.pojo.Account;
import org.testng.annotations.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class ResultSetTest {
// 需求:查询account账户表数据,封装为Account对象中,并且存储到ArrayList集合中。
@Test
public void testDQL2() throws Exception {
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "SELECT * FROM account";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
List<Account> list = new ArrayList<>();
while (rs.next()) {
// 获取值
int id = rs.getInt(1);
String name = rs.getString(2);
double money = rs.getDouble("money");
// 对象
Account account = new Account();
account.setId(id);account.setName(name);account.setMoney(money);
// 存入List
list.add(account);
}
System.out.println(list);
rs.close();
stmt.close();
conn.close();
}
}
PreparedStatement(API)
作用 :预编译SQL语句并执行,预防SQL注入问题(SQL注入:通过操作输入来修改事先定义好的SQL语句,用以达到执行代码对服务器进行攻击的方法)
// 创建一个表用于后续内容
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
password VARCHAR(20)
);
INSERT INTO tb_user VALUES(NULL, 'zhangsan', '123'), (NULL, 'lisi', '123');
SQL注入演示
public class UserLoginTest {
@Test
public void user_login() throws Exception {
// 连接SQL
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
// 接受用户输入的用户名和密码
String user_name = "zhangsan";
String pwd = "123";
String sql = "select * from tb_user where username = '" + user_name + "' and password = '" + pwd + "'";
// 获取执行SQL的对象,并执行sql
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// 判断登陆是否成功
if (rs.next()) { System.out.println("登陆成功!");}else { System.out.println("登陆失败!"); }
// 7.释放资源
rs.close();
stmt.close();
conn.close();
}
// 演示SQL注入
@Test
public void user_login_inject()throws Exception{
// 连接SQL
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
// 接受用户输入的用户名和密码 (注入)
String user_name = "dddasda";
String pwd = "' or '1' = '1";
String sql = "select * from tb_user where username = '" + user_name + "' and password = '" + pwd + "'";
System.out.println(sql);
// 获取执行SQL的对象,并执行sql
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// 判断登陆是否成功
if (rs.next()) { System.out.println("登陆成功!");}else { System.out.println("登陆失败!"); }
// 7.释放资源
rs.close();
stmt.close();
conn.close();
}
}
PreparedStatement流程
①获取PreparedStatement对象
// sql中的参数值使用?来占位代替
String sql = "select * from user where username = ? and password = ?";
// 通过conn来获取预编译对象,并传入对应的sql语句
PreparedStatement pstmt = conn.prepareStatement(sql);
②设置参数值
给参数赋值:setXxx(参数1,参数2)
③执行SQL
不需要再传递一次sql语句,因为上面已经传过了
executeUpdate(); executeQuery();
演示PreparedStatement解决注入问题
@Test
public void PrepareStatement() throws Exception {
// 连接SQL
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
// 接受用户输入的用户名和密码
String user_name = "zhangsan";
String pwd = "123";
String sql = "select * from tb_user where username = ? and password = ?";
// 获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
// 设置?对应的值
pstmt.setString(1,user_name);
pstmt.setString(2,pwd);
// 执行sql
ResultSet rs = pstmt.executeQuery();
if(rs.next()){ System.out.println("登陆成功!"); }else{ System.out.println("登陆失败!"); }
// 释放资源
rs.close();
pstmt.close();
conn.close();
}
// Tips:因为他会对密码中的'或一些关键字自动采取转译操作,所以避免了注入(Inject)
PreparedStatement好处
1)预编译SQL,可以使性能更高
2)防止SQL注入,将敏感字符转译