14. JDBC
14.1 相关 API 介绍
JDBC: Java Database Connection
API: Application Programming Interface (应用程序编程接口或规范)
SUM公司制定了JDBC规范:DriverManager 类
一套接口规范:Connection 连接、Statement 命令接口、ResultSet 结果集接口
14.2 Statement 命令对象
将相关的 myslq-connector 的 jar 包添加到当前工程中
定义实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private String stuno;
private String stuname;
private String classno;
private String sex;
private Integer age;
}
编写 JdbcUtil 工具类
public class JdbcUtil {
/**
* 使用配置文件加载数据库连接信息
*/
public static String driver;
public static String url;
public static String username;
public static String password;
/**
* 静态代码块加载驱动
*/
static {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
driver = bundle.getString("jdbc.driver");
url = bundle.getString("jdbc.url");
username = bundle.getString("jdbc.user");
password = bundle.getString("jdbc.password");
try {
// 加载驱动
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 得到连接
*/
public static Connection getConn() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
}
编写 StudentDao 用于访问 Student 数据库表
/**
* 查询所有学生
*/
@Override
public List<Student> findAll() throws SQLException {
List<Student> students = new ArrayList<>();
Statement statement = null;
ResultSet resultSet = null;
// 得到连接对象
try (Connection conn = JdbcUtil.getConn()) {
// 得到命令对象
statement = conn.createStatement();
// 执行命令,得到结果集对象
String sql = "select * from student";
resultSet = statement.executeQuery(sql);
// 将结果集转换为集合
while (resultSet != null && resultSet.next()) {
// 从结果集中根据列名获取对应列的数据
String stuno = resultSet.getString("stuno");
String stuname = resultSet.getString("stuname");
String classno = resultSet.getString("classno");
String sex = resultSet.getString("sex");
int age = resultSet.getInt("age");
// 构造学生对象
Student student = new Student(stuno, stuname, classno, sex, age);
// 将学生对象放到集合中
students.add(student);
}
} finally {
// 关闭资源
resultSet.close();
statement.close();
}
return students;
}
测试 StudentDao
/**
* 查询所有学生
*/
@Test
public void test01() throws SQLException {
List<Student> students = studentDAO.findAll();
students.forEach(System.out::println);
}
14.3 PreparedStatement 命令对象
编写 StudentDao
/**
* 根据学号查询学生
*/
@Override
public Student findByStuno(String stuno) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
Student student = null;
// 获取连接对象
try (Connection conn = JdbcUtil.getConn()) {
// 得到命令对象
String sql = "select * from student where stuno = ?";
ps = conn.prepareStatement(sql);
// 给占位符赋值
ps.setString(1, stuno);
// 执行命令,得到结果集
rs = ps.executeQuery();
// 将结果集转换为Student对象
if (rs != null && rs.next()) {
String stuname = rs.getString("stuname");
String classno = rs.getString("classno");
String sex = rs.getString("sex");
int age = rs.getInt("age");
// 构造学生对象
student = new Student(stuno, stuname, classno, sex, age);
}
} finally {
// 关闭资源
rs.close();
ps.close();
}
return student;
}
/**
* 添加学生
*/
@Override
public boolean add(Student student) throws SQLException {
PreparedStatement ps = null;
int i = 0;
// 获取连接对象
try (final Connection conn = JdbcUtil.getConn()) {
// 得到命令对象
String sql = "insert into student value (?,?,?,?,?)";
ps = conn.prepareStatement(sql);
// 给占位符赋值
ps.setString(1, student.getStuno());
ps.setString(2, student.getStuname());
ps.setString(3, student.getClassno());
ps.setString(4, student.getSex());
ps.setInt(5, student.getAge());
// 执行命令
i = ps.executeUpdate();
} finally {
// 关闭资源
ps.close();
}
return i > 0;
}
/**
* 修改学生
*/
@Override
public boolean update(Student student) throws SQLException {
PreparedStatement ps = null;
int i = 0;
// 获取连接对象
try (Connection conn = JdbcUtil.getConn()) {
// 得到命令对象
String sql = "update student set stuname = ?,classno = ?,sex = ?,age = ? where stuno = ?";
ps = conn.prepareStatement(sql);
// 给占位符赋值
ps.setString(1, student.getStuname());
ps.setString(2, student.getClassno());
ps.setString(3, student.getSex());
ps.setInt(4, student.getAge());
ps.setString(5, student.getStuno());
// 执行命令
i = ps.executeUpdate();
} finally {
// 关闭资源
ps.close();
}
return i > 0;
}
/**
* 删除学生
*/
@Override
public boolean deleteByStuno(String stuno) throws SQLException {
PreparedStatement ps = null;
int i = 0;
// 获取连接对象
try (final Connection conn = JdbcUtil.getConn()) {
// 得到命令对象
String sql = "delete from student where stuno = ?";
ps = conn.prepareStatement(sql);
// 给占位符赋值
ps.setString(1, stuno);
// 执行命令
i = ps.executeUpdate();
} finally {
// 关闭资源
ps.close();
}
return i > 0;
}
测试 StudentDao
/**
* 根据学号查询学生
*/
@Test
public void test02() throws SQLException {
String stuno = "20080102";
Student student = studentDAO.findByStuno(stuno);
System.out.println(student);
}
/**
* 添加学生
*/
@Test
public void test03() throws SQLException {
Student student = new Student("20080202","小六子","200802","F",23);
boolean b = studentDAO.add(student);
if (b){
System.out.println("添加成功!");
}else {
System.out.println("添加失败!");
}
}
/**
* 修改学生
*/
@Test
public void test04() throws SQLException {
// 根据传入的学号查询出对应学生信息
String stuno = "20080202";
Student student = studentDAO.findByStuno(stuno);
// 修改字段内容
student.setStuname("王二麻子");
student.setAge(28);
// 修改学生
boolean b = studentDAO.update(student);
if (b){
System.out.println("修改成功!");
}else {
System.out.println("修改失败!");
}
}
/**
* 删除学生
*/
@Test
public void test05() throws SQLException {
// 根据传入的学号删除对应学生信息
String stuno = "20080202";
boolean b = studentDAO.deleteByStuno(stuno);
if (b){
System.out.println("删除成功!");
}else {
System.out.println("删除失败!");
}
}
14.4 CallableStatement 命令对象
编写 StudentDao
/**
* 访问无参的过程
* 案例:查询所有的学生信息。
*/
@Override
public List<Student> findStudentsByProc() throws SQLException {
CallableStatement ps = null;
ResultSet rs = null;
List<Student> students = new ArrayList<>();
try(Connection conn = JdbcUtil.getConn()){
// 6.1 得到命令对象
ps = conn.prepareCall("call proc_1");
// 6.2 执行命令返回结果集
rs = ps.executeQuery();
// 6.3 将结果集转换为集合
while(rs != null && rs.next()){
// 从结果集中根据列名获取对应列的数据
String stuno = rs.getString("stuno");
String stuname = rs.getString("stuname");
String classno = rs.getString("classno");
String sex = rs.getString("sex");
int age = rs.getInt("age");
// 构造学生对象
Student student = new Student(stuno, stuname, classno, sex, age);
// 将学生对象放到集合中
students.add(student);
}
}finally {
ps.close();
rs.close();
}
return students;
}
/**
* 带有输入参数的过程
* 案例:查询指定性别的学生信息。
*/
@Override
public List<Student> findStudentsBySex(String sex) throws SQLException {
CallableStatement ps = null;
ResultSet rs = null;
List<Student> students = new ArrayList<>();
try(Connection conn = JdbcUtil.getConn()){
// 7.1 得到命令对象
ps = conn.prepareCall("call proc_2(?)");
// 7.2 为占位符赋值
ps.setString(1,sex);
// 7.3 执行命令
rs = ps.executeQuery();
// 7.4 将结果集转换为集合
while(rs != null && rs.next()){
// 从结果集中根据列名获取对应列的数据
String stuno = rs.getString("stuno");
String stuname = rs.getString("stuname");
String classno = rs.getString("classno");
int age = rs.getInt("age");
// 构造学生对象
Student student = new Student(stuno, stuname, classno, sex, age);
// 将学生对象放到集合中
students.add(student);
}
}finally {
rs.close();
ps.close();
}
return students;
}
/**
* 带有输入和输出参数的过程
* 案例:根据性别查询性别的人数。
*/
@Override
public int findCountBySex(String sex) throws SQLException {
CallableStatement ps = null;
int count = 0;
try(Connection conn = JdbcUtil.getConn()){
// 8.1 得到命令对象
ps = conn.prepareCall("call proc_3(?,?)");
// 8.2 为占位符赋值(只为输入参数赋值)
ps.setString(1,sex);
// 8.3 为输出参数注册类型
ps.registerOutParameter(2,Types.INTEGER);
// 8.4 执行命令
ps.execute();
// 8.5 得到返回值
count = ps.getInt(2);
}finally {
ps.close();
}
return count;
}
测试 StudentDao
/**
* 访问无参的过程
* 案例:查询所有的学生信息。
*/
@Test
public void test06() throws SQLException {
List<Student> students = studentDAO.findStudentsByProc();
students.forEach(System.out::println);
}
/**
* 带有输入参数的过程
* 案例:查询指定性别的学生信息。
*/
@Test
public void test07() throws SQLException {
String sex = "女";
List<Student> students = studentDAO.findStudentsBySex(sex);
students.forEach(System.out::println);
}
/**
* 带有输入和输出参数的过程
* 案例:根据性别查询性别的人数。
*/
@Test
public void test08() throws SQLException {
String sex = "男";
int count = studentDAO.findCountBySex(sex);
System.out.println(sex + "生共有" + count + "人!");
}