JDBC学习
Java Database Connectivity，简称JDBC
是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法
一个学生管理系统demo : JDBC.zip
一 . 基础
1 . 初识
jdbc的本质就是 ：java官方提供的一套规范接口，用于帮助程序员开发者操作不同的关系型数据库(mysql/Oracle/SQLServer)
数据库中 varchar 类型对应 Java 中 string
数据库中 int 类型对应 Java 中 Integer
2 . 实体类
package com.entity;
public class StudentEntity {
private Long id;
private String name;
private Long phone;
private String address;
private String pwd;
/**
* 获取所有
* @param id
* @param name
* @param phone
* @param address
*/
public StudentEntity(Long id, String name, Long phone, String address) {
this.id = id;
this.name = name;
this.phone = phone;
this.address = address;
}
/**
* 获取指定信息
* @param name
* @param phone
* @param address
*/
public StudentEntity(String name, Long phone, String address) {
this.name = name;
this.phone = phone;
this.address = address;
}
/**
* 修改信息
* @param name
* @param phone
* @param address
* @param pwd
*/
public StudentEntity(String name, Long phone, String address, String pwd) {
this.name = name;
this.phone = phone;
this.address = address;
this.pwd = pwd;
}
/**
* 返回修改信息
* @param id
* @param name
* @param phone
* @param address
* @param pwd
*/
public StudentEntity(Long id, String name, Long phone, String address, String pwd) {
this.id = id;
this.name = name;
this.phone = phone;
this.address = address;
this.pwd = pwd;
}
public StudentEntity(Long id, Long phone, String address, String pwd) {
this.id = id;
this.phone = phone;
this.address = address;
this.pwd = pwd;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Long getPhone() {
return phone;
}
public void setPhone(Long phone) {
this.phone = phone;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "{" +
" id = " + id +
" name = " + name +
" phone = " + phone +
" address = " + address +
" }";
}
}3 . 数据库访问层
allStudent() 没有进行封装
其他都进行了连接和释放的封装
package com.dao;
import com.entity.StudentEntity;
import java.sql.*;
import java.util.ArrayList;
public class StudentDao {
public ArrayList<StudentEntity> allStudent() throws SQLException {
Connection connection =null;
Statement statement = null;
ResultSet resultSet = null;
//导入jar包
//注册驱动
try {
Class.forName("com.mysql.cj.jdbc.Driver");
//数据库连接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atanycosts?serverTimezone=UTC&useSSL=false", "root", "root");
//获取执行者对象
statement = connection.createStatement();
//执行SQL语句
resultSet = statement.executeQuery("select * from user");
//处理结果
ArrayList<StudentEntity> studentEntities = new ArrayList<>();
while (resultSet.next()) {
Long id = resultSet.getLong("id");
String name = resultSet.getString("name");
Integer age = resultSet.getInt("age");
String address = resultSet.getString("address");
StudentEntity studentEntity = new StudentEntity(id, name, age, address);
studentEntities.add(studentEntity);
}
return studentEntities;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
}finally {
//释放
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 根据学生id 查询学生信息 学生的id
*/
public StudentEntity getByIdStudent(Long stuId) {
/**
* 判断用户是否传递学生id的值
*/
if (stuId == null) {
return null;
}
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//调用封装的连接类
connection = JdbcUtils.getConnection();
// 获取执行者对象
statement = connection.createStatement();
// 执行sql语句并获取返回结果 自己拼接 查询sql语句
resultSet = statement.executeQuery("select * from user where id=" + stuId);
boolean result = resultSet.next(); // 查询不到数据 false
// 判断如果查询不到数据 则不会取值
if (!result) {
return null;
}
// 对结果进行处理
// 获取该行数据的第一列 id
Long id = resultSet.getLong("id");
// 获取该行数据的第二列 name
String name = resultSet.getString("name");
// 获取该行数据的第三列 age
Long phone = resultSet.getLong("phone");
// 获取该行数据的第四列 address
String address = resultSet.getString("address");
// 将db中查询到数据封装成java学生对象
StudentEntity studentEntity = new StudentEntity(id, name, phone, address);
return studentEntity;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
// 释放jdbc资源
JdbcUtils.closeConnection(resultSet, statement, connection);
}
}
/**
* 插入学生
*/
public int insertStudent(StudentEntity stu) {
Connection connection = null;
Statement statement = null;
try {
//调用封装的连接类
connection = JdbcUtils.getConnection();
// 获取执行者对象
statement = connection.createStatement();
// 执行sql语句并获取返回结果 executeUpdate执行 insert sql语句
String insertStudentSql = "INSERT INTO user values(null,'" + stu.getName() + "'," + stu.getPhone() + ",'" + stu.getAddress() + "','" + stu.getPwd() + "')";
System.out.println("insertStudentSql:" + insertStudentSql);
int result = statement.executeUpdate(insertStudentSql);
// 执行该sql语句 影响行数
return result;
} catch (Exception e) {
e.printStackTrace();
return 0;
} finally {
// 释放jdbc资源
JdbcUtils.closeConnection(statement, connection);
}
}
/**
* 修改学生的信息
*/
public int updateStudent(StudentEntity stu) {
Connection connection = null;
Statement statement = null;
try {
//调用封装的连接类
connection = JdbcUtils.getConnection();
// 获取执行者对象
statement = connection.createStatement();
//5. 执行sql语句并获取返回结果 executeUpdate执行 update sql语句
String updateStudentSql = "update user set name='" + stu.getName() + "' ,phone=" + stu.getPhone() + "," +
"address='" + stu.getAddress() + "' where id=" + stu.getId() + "";System.out.println("updateStudentSql:" + updateStudentSql);
int result = statement.executeUpdate(updateStudentSql);
// 执行该sql语句 影响行数
return result;
} catch (Exception e) {
e.printStackTrace();
return 0;
} finally {
// 释放jdbc资源
JdbcUtils.closeConnection(statement, connection);
}
}
/**
* 根据主键id删除学生信息
*/
public int delStudent(Long id) {
// 判断id是否为null
if (id == null) {
return 0;
}
Connection connection = null;
Statement statement = null;
try {
//调用封装的连接类
connection = JdbcUtils.getConnection();
// 获取执行者对象
statement = connection.createStatement();
// 执行sql语句并获取返回结果 executeUpdate执行 delete sql语句
String delSQL = "delete from user where id=" + id;
String resetID="ALTER TABLE user AUTO_INCREMENT = 1";
System.out.println("delSql:" + delSQL);
int result = statement.executeUpdate(delSQL);
statement.executeUpdate(resetID);
// 执行该sql语句 影响行数
return result;
} catch (Exception e) {
e.printStackTrace();
return 0;
} finally {
// 释放jdbc资源
JdbcUtils.closeConnection(statement, connection);
}
}
}4 . 业务逻辑层
package com.serivce;
import com.dao.StudentDao;
import com.entity.StudentEntity;
import java.sql.SQLException;
import java.util.ArrayList;
//业务逻辑层
public class StudentService {
/**
* new 学生对象dao层
*/
private StudentDao studentDao = new StudentDao();
/**
* 查询所有成员
*/
public ArrayList<StudentEntity> allStudent() throws SQLException {
// ArrayList<StudentEntity> studentEntities = studentDao.allStudent();
return studentDao.allStudent();
}
/**
* 根据ID查询
*/
public StudentEntity getByIdStudent(Long stuId) {
return studentDao.getByIdStudent(stuId);
}
/**
* 插入
*/
public int insertStudent(StudentEntity stu) {
return studentDao.insertStudent(stu);
}
/**
* 修改学生的信息
*/
public int updateStudent(StudentEntity stu) {
return studentDao.updateStudent(stu);
}
/**
* 根据ID删除
*/
public int delStudent(Long id) {
return studentDao.delStudent(id);
}
}
5 . 控制层
后面实现功能
package com.controller;
import com.entity.StudentEntity;
import com.serivce.StudentService;
import java.sql.SQLException;
import java.util.ArrayList;
public class StudentController {
public static void main(String[] args) throws SQLException {
StudentService studentService = new StudentService();
ArrayList<StudentEntity> studentEntities = studentService.allStudent();
for (StudentEntity studentEntity : studentEntities) {
System.out.println(studentEntity);
}
}
}
二 . 封装
创建 JdbcUtils 封装类,将 StudentDao 层的 连接数据 和 释放资源 封装
查询调用方法
public static void closeConnection(ResultSet resultSet, Statement statement, Connection connection) {
// 1. 查询时释放 resultSet statement connection
try {
if (resultSet != null)
resultSet.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}增删改调用方法
public static void closeConnection(Statement statement, Connection connection) {
// 2. 增删改时释放 statement connection
closeConnection(null, statement, connection);
}整体代码
package com.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
//封装
public class JdbcUtils {
public static void main(String[] args) {
System.out.println(JdbcUtils.driverClass);
}
/**
* 1.需要将我们的构造方法私有化 ---工具类 不需要 new出来 是通过类名称.方法名称访问
*/
private JdbcUtils() {
}
/**
* 2.定义工具类 需要 声明 变量
*/
private static String driverClass;
private static String url;
private static String user;
private static String password;
/**
*3.使用静态代码快 来给我们声明好 jdbc变量赋值（读取config.properties）
*/
static {
try {
// 1. 读取config.properties 相对路径
InputStream resource = JdbcUtils.class.getClassLoader().getResourceAsStream("config.properties");
// 2.赋值给我们声明好的变量
Properties properties = new Properties();
properties.load(resource);
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
// 3.注册驱动类
Class.forName(driverClass);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 4.封装连接方法
*/
public static Connection getConnection() {
try {
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* 5.封装释放连接方法 (重载)
*/
public static void closeConnection(ResultSet resultSet, Statement statement, Connection connection) {
// 1. 查询时释放 resultSet statement connection
try {
if (resultSet != null)
resultSet.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void closeConnection(Statement statement, Connection connection) {
// 2. 增删改时释放 statement connection
closeConnection(null, statement, connection);
}
}
三 . 功能实现
登录与注册
直接在 StudentController 控制层写
package com.controller;
import com.entity.StudentEntity;
import com.serivce.StudentService;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;
public class StudentController {
private static StudentService studentService = new StudentService();
public static void main(String[] args) throws SQLException {
mainMenu();
}
/**
* 1.需要定义主菜单程序的入口
*/
public static void mainMenu() throws SQLException {
while (true) {
// 1.提示语
System.out.println("欢迎来到学生管理系统");
System.out.println("1. 查询所有学生");
System.out.println("2. 根据id查询学生");
System.out.println("3. 注册新的学生");
System.out.println("4. 根据id修改学生");
System.out.println("5. 根据id删除学生");
System.out.println("6. 退出系统");
System.out.println("请选择 : ");
Scanner scanner = new Scanner(System.in);
int result = scanner.nextInt();
// 3.在根据用户选择的序号 判断
switch (result) {
case 1:
showAllStudent();
break;
case 2:
findByIdStudent();
break;
case 3:
insertStudent();
break;
case 4:
updateIdStudent();
break;
case 5:
delIdStudent();
break;
case 6:
System.out.println("已退出");
return;
}
}
}
/**
* 查询所有学生信息
*/
public static void showAllStudent() throws SQLException {
System.out.println("所有学生信息 : ");
ArrayList<StudentEntity> studentEntities = studentService.allStudent();
for (StudentEntity stu : studentEntities) {
System.out.println(stu );
}
}
/**
* 根据学生id查询学生信息
*/
public static void findByIdStudent() {
System.out.println("请输入学生的id：");
Scanner scanner = new Scanner(System.in);
Long stuId = scanner.nextLong();
// 根据用户输入的学生id查询学生信息
// 根据用户输入的学生id查询学生信息 查询不到 查询得到
StudentEntity student = studentService.getByIdStudent(stuId);
if (student == null) {
System.out.println("该学生id" + student + ",不存在的");
return;
}
// 查询到了学生信息
System.out.println("学生信息：" + student + "\n");
}
/**
* 根据学生id删除学生信息
*/
public static void delIdStudent() {
System.out.println("请输入学生的id：");
Scanner scanner = new Scanner(System.in);
Long stuId = scanner.nextLong();
int result = studentService.delStudent(stuId);
if (result > 0) {
System.out.println("删除成功" + "\n");
} else {
System.out.println("删除失败" + "\n");
}
}
/**
* 插入我们的学生信息
*/
public static void insertStudent() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入名字：");
String name = scanner.nextLine();
System.out.println("请输入手机号：");
Long phone = scanner.nextLong();
scanner.nextLine(); // 跳过
System.out.println("请输入地址：");
String address = scanner.nextLine();
System.out.println("请输入密码：");
String pwd = scanner.nextLine();
StudentEntity studentEntity = new StudentEntity(name, phone, address, pwd);
int result = studentService.insertStudent(studentEntity);
if (result > 0) {
System.out.println("插入学生信息成功" + "\n");
} else {
System.out.println("插入学生信息失败" + "\n");
}
}
/**
* 根据学生的id修改该学生信息
*/
public static void updateIdStudent() {
// 需要先根据学生的id查询该学生信息 如果查询得到的情况下 才会修改学生信息
Scanner scanner = new Scanner(System.in);
System.out.println("请输入学生的id：");
Long stuId = scanner.nextLong();
// 根据学生id查询学生信息
StudentEntity student = studentService.getByIdStudent(stuId);
// 查询不到的情况下 就不会修改学生信息
if (student == null) {
System.out.println("没有查询该学生的id对应学生信息" + "\n");
return;
}
scanner.nextLine();
System.out.println("请输入名字：");
String name = scanner.nextLine();
System.out.println("请输入手机号：");
Long phone = scanner.nextLong();
scanner.nextLine(); // 跳过
System.out.println("请输入地址：");
String address = scanner.nextLine();
System.out.println("请输入密码：");
String pwd = scanner.nextLine();
// 封装接受的参数 变成学生对象
StudentEntity studentEntity = new StudentEntity(stuId, name, phone, address, pwd);
int result = studentService.updateStudent(studentEntity);
if (result > 0) {
System.out.println("修改成功" + "\n");
} else {
System.out.println("修改失败" + "\n");
}
}
}
四 . SQL注入
就是SQL语句拼接问题
select * from user where phone='' and pwd='' or 1='1';
' or 1='1如何解决?
使用JDBC的 预编译执行者对象 PreparedStatemnet 通过站位符 ? 写死SQL语句
//需要将 statement 改为 PreparedStatemnet 类型
String Sql = "select * from user where phone=? and pwd=?;";
statement = connection.prepareStatement(Sql);
statement.setString(1, xxx.getPhone());
statement.setString(2, xxx.getPwd());五 . 事务
用到深入学习