JDBC
Java database connectivity
是java语言操作数据库的 api (应用程序编程接口)
java.sql.*
接口
Connection 连接 代表了java和数据之间的通道,桥梁
Statement 语句 可以用来执行 insert, update, delete , select …
ResultSet 结果集 代表的是查询的结果
类
DriverManager 工具类,获取连接对象
SQLException 异常对象 是 Exception的子类型,属于检查异常
操作步骤顺序
- 加载驱动 (Driver) jdbc的驱动就是一个连接工厂,生成的产品是连接对象
com.mysql.jdbc.Driver 是Driver的mysql实现类
oracle.jdbc.Driver 是Driver的oracle实现类
Class.forName("驱动类名");
例如:
Class.forName("com.mysql.jdbc.Driver");
jdbc 3.0 以上版本都可以省略加载驱动这一步
- 获取连接对象
DriverManager.getConnection(url, 用户名, 密码); // 内部调用了 Driver 对象获取数据库连接
url 的格式 :
jdbc:mysql://ip地址:端口号/数据库名?参数
例如:mysql数据库
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
- 创建语句
Statement stmt = conn.createStatement();
- 执行sql
int rows = stmt.executeUpdate(); // 用来执行 insert , update, delete , DDL , 返回值代表影响行数
// delete from student where sid = 1008; 返回影响行数是0;
ResultSet rs = stmt.executeQuery(); // 用来执行 select
boolean moreRows = rs.next() 取得下一条记录,
moreRows 是true ,表示有下一条记录, false 表示没有了
while(rs.next()) {
}
// 迭代器模式
- 释放资源 ( 先打开的资源后关闭 )
rs.close();
stmt.close();
conn.close();
PreparedStatement 预编译语句对象
Statement stmt = conn.createStatement();
stmt.executeUpdate(String sql);
// 1. 需要预先提供sql语句
PreparedStatement psmt = conn.prepareStatement(String sql);
// 2. 可以在sql中用?占位某个值
insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)
// 3. 给?赋值
使用PreparedStatement中一系列以 set开头的方法
setString(?的位置, 值)
setInt(?的位置, 值)
setDate(?的位置, 值)
psmt.setString(1, "李四");
psmt.setString(2, "1999-9-7");
// 4. 执行sql
psmt.executeUpdate();
注意: ?能够占位的只有值, 不能是表名、列名、关键字
练习:
- 编写一个方法,以学生的姓名作为条件查询,返回集合
- 编写一个方法,按照学生的编号查询,返回学生对象
- 编写一个方法,查询每个部门的员工人数
SQL注入攻击
create table user (
username varchar(20) primary key,
password varchar(20)
);
insert into user(username, password) values('zhangsan','123');
防范方法:
- 对参数内存做检查,内部不能有sql关键字例如:or
- PreparedStatement
获取自增的主键值
package day25;
import java.sql.*;
import java.sql.Date;
import java.util.*;
public class TestJdbc3 {
public static void test() {
try (Connection conn = JdbcUtils.getConnection()) {
String sql = "insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)";
// ctrl+p Statement.RETURN_GENERATED_KEYS 是一个选项,表示要返回自增的主键值
try (PreparedStatement psmt
= conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS) ) {
psmt.setString(1, "aaa");
psmt.setString(2, "1999-1-1");
psmt.setString(3, "男");
int rows = psmt.executeUpdate();
System.out.println("影响行数:" + rows);
// 返回一个结果集,结果集中存储了刚刚生成的主键值
/**
rs ->
1011
*/
ResultSet rs = psmt.getGeneratedKeys();
// 就一行一列
rs.next();
int id = rs.getInt(1);
System.out.println("主键值是:" + id);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 登录操作,检查用户名,密码是否正确
public static boolean login(String username, String password) {
try (Connection conn = JdbcUtils.getConnection()) {
/*Statement stmt = conn.createStatement();
String sql = "select * from user where username='"+ username +"' and password='"+password+"'";
System.out.println(sql);
ResultSet rs = stmt.executeQuery(sql);
*/
PreparedStatement stmt = conn.prepareStatement("select * from user where username=? and password=?");
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();
if(rs.next()) {
return true;
} else {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 编写一个方法,查询每个部门的员工人数
public static Map<Integer, Integer> countDept() {
try(Connection conn = JdbcUtils.getConnection()) {
try(PreparedStatement psmt = conn.prepareStatement("select count(*) c, deptno from emp group by deptno order by deptno")){
ResultSet rs = psmt.executeQuery();
Map<Integer, Integer> map = new LinkedHashMap<>();
while(rs.next()) {
// rs.getInt("c"); // rs.getInt(1);
// rs.getInt("deptno");// rs.getInt(2);
map.put(rs.getInt("deptno"), rs.getInt("c"));
}
return map;
}
} catch (SQLException e) {
e.printStackTrace();
return Collections.emptyMap();
}
}
// 编写一个方法,按照学生的编号查询,返回学生对象
public static Student findById(int sid) {
try(Connection conn = JdbcUtils.getConnection()) {
try (PreparedStatement psmt = conn.prepareStatement("select * from student where sid = ?")) {
psmt.setInt(1, sid);
ResultSet rs = psmt.executeQuery();
// 查询结果要么1条,要么是0条
if(rs.next()) { // 找到了
String sname = rs.getString("sname");
Date birthday = rs.getDate("birthday");
String sex = rs.getString("sex");
Student student = new Student(sid, sname, birthday, sex);
return student;
} else { // 没找到
return null;
}
}
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
// 按照学生姓名为条件进行查询
// %张%
public static List<Student> findByName(String name) {
try(Connection conn = JdbcUtils.getConnection()) {
try(PreparedStatement psmt = conn.prepareStatement("select * from student where sname like ?")){
psmt.setString(1, "%"+name+"%");
ResultSet rs = psmt.executeQuery();
List<Student> list = new ArrayList<>();
while(rs.next()) {
int sid = rs.getInt("sid");
String sname = rs.getString("sname");
Date birthday = rs.getDate("birthday");
String sex = rs.getString("sex");
Student student = new Student(sid, sname, birthday, sex);
list.add(student);
}
return list;
}
} catch (SQLException e) {
e.printStackTrace();
return Collections.emptyList();
}
}
// 查询所有学生
public static List<Student> findAll() {
try (Connection conn = JdbcUtils.getConnection()) {
try (PreparedStatement psmt = conn.prepareStatement("select * from student")) {
List<Student> list = new ArrayList<>();
try (ResultSet rs = psmt.executeQuery()) {
while (rs.next()) {
// 每循环一次,rs会指向一行记录
// 把这条记录的每一列的值取出来
int sid = rs.getInt("sid");
String sname = rs.getString("sname");
Date birthday = rs.getDate("birthday");
String sex = rs.getString("sex");
// 组装了一个学生对象
Student stu = new Student(sid, sname, birthday, sex);
// 多个学生对象,保存入list集合
list.add(stu);
}
return list;
}
}
} catch (SQLException e) {
e.printStackTrace();
return Collections.emptyList();
}
}
// 新增学生
public static void insert(String name, String birthday, String sex) {
try (Connection conn = JdbcUtils.getConnection()) {
try (Statement stmt = conn.createStatement()) {
String sql = "insert into student(sid,sname,birthday,sex) values(null, '" + name + "', '" + birthday + "', '" + sex + "')";
int rows = stmt.executeUpdate(sql);
System.out.println("影响行数是:" + rows);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 新增学生
public static void insert2(String name, String birthday, String sex) {
try (Connection conn = JdbcUtils.getConnection()) {
try (PreparedStatement psmt
= conn.prepareStatement("insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)")) {
psmt.setString(1, name);
psmt.setString(2, birthday);
psmt.setString(3, sex);
int rows = psmt.executeUpdate();
System.out.println("影响行数:" + rows);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
/*Scanner scanner = new Scanner(System.in);
System.out.println("请输入姓名:");
String name = scanner.nextLine();
System.out.println("请输入生日:");
String birthday = scanner.nextLine();
System.out.println("请输入性别:");
String sex = scanner.nextLine();
insert2(name, birthday, sex);*/
/*Map<Integer, Integer> map = countDept();
map.forEach( (k,v) -> {
System.out.println("部门编号:" + k + " 部门人数: " + v);
} );*/
// String sql = "select * from user where username='"+ username +"' and password='"+password+"'";
// boolean isLogin = login("zhangsan", "' or '1'='1");
// System.out.println(isLogin);
test();
}
}