⼀、引言
如何操作数据库
使用客户端⼯具访问数据库,需要⼿⼯建⽴连接,输⼊⽤户名和密码登录,编写 SQL 语句,点击执⾏,查看操作结果(结果集或受影响⾏数)。
实际开发中,会采用客户端操作数据库吗?
在实际开发中,当⽤户的数据发⽣改变时,不可能通过客户端操作执⾏ SQL 语句,因为操作量过⼤,⽆法保证效率和正确性。
⼆、JDBC(Java Database Connectivity)
什么是 JDBC?
JDBC(Java Database Connectivity) Java 连接数据库的规范(标准),可以使⽤ Java 语⾔连接数据库完成 CRUD (CREATE READ UPDATE DELETE)操作。
JDBC 核心思想
Java 中定义了访问数据库的接⼝,可以为多种关系型数据库提供统⼀的访问⽅式。由数据库⼚商提供驱动实现类(Driver 数据库驱动)。
MySQL 数据库驱动
mysql-connector-java-5.1.X 适⽤于 5.X 版本
mysql-connector-java-8.0.X 适⽤于 8.X版本
JDBC API
JDBC 是由多个接⼝和类进行功能实现。
类型 | 权限定名 | 简介 |
class | java.sql.DriverManager | 管理多个数据库驱动类,提供了获取数据库连接的方法 |
interface | java.sql.Connection | 代表⼀个数据库连接(当connection不是null时,表示已连接数据库) |
interface | java.sql.Statement | 发送SQL语句到数据库⼯具 |
interface | java.sql.ResultSet | 保存SQL查询语句的结果数据(结果集) |
class | java.sql.SQLException | 处理数据库应用程序时所发⽣的异常 |
三、JDBC 开发步骤
注册驱动
使⽤ Class.forName("com.mysql.jdbc.Driver");⼿动加载字节码⽂件到 JVM 中。
Class.forName("com.mysql.jdbc.Driver");//加载驱动
连接数据库
通过 DriverManager.getConnection(url,user,password) 获取数据库连接对象
URL:jdbc:mysql://localhost:3306/database
username:root
password:1234
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8", "root","1234");
URL(Uniform Resource Locator) 统⼀资源定位符:由协议、IP、端⼝、SID(程序实例名称)组成
获取发送 SQL 的对象
通过 Connection 对象获得 Statement 对象,⽤于对数据库进⾏通⽤访问。
Statement statement = conn.createStatement();
执行SQL 语句
执⾏ SQL 语句并接收执⾏结果。
String sql ="INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES('JAVA_Le','JAVA_Lecturer',4000,10000);";
int result = statement.executeUpdate(sql);//执⾏SQL语句并接收结果
注意:在编写 DML 语句时,⼀定要注意字符串参数的符号是单引号 '值'
DML 语句:增删改时,返回受影响⾏数(int 类型)。
DQL 语句:查询时,返回结果数据(ResultSet 结果集)。
处理结果
接受处理操作结果。
if(result == 1){
System.out.println("Success");
}
受影响⾏数:逻辑判断、⽅法返回。
查询结果集:迭代、依次获取。
释放资源
遵循先开后关原则,释放所使⽤到的资源对象。
statement.close();
conn.close();
综合案例
整合以上核心六步,实现向数据库表中插⼊⼀条数据。
public class JDBCDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1,注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2,连接数据库
String url = "jdbc:mysql://localhost:3306/database";//连接数据库的url
String username = "root";//连接数据库的用户名
String password = "root";//连接数据库的密码
Connection conn = DriverManager.getConnection(url, username, password);
//3,获取执行SQL语句的对象
Statement stmt = conn.createStatement();
//4,定义执行SQL 语句
String sql = "INSERT INTO person(NAME,age,sex) VALUES('zhoujielun',33,'男')";
//String sql = "update person set age =100 where id = 4";
//String sql = "delete from person where id=4";
int row = stmt.executeUpdate(sql);//执行完会返回受影响的行数
//5,处理结果
if(row>=0){
System.out.println("执行成功");
}
//6,释放资源
stmt.close();
conn.close();
}
}
四、ResultSet(结果集)
ResultSet常用方法
boolean next() | 将游标从当前位置向下移动一行 |
boolean previous() | 游标从当前位置向上移动一行 |
void close() | 关闭ResultSet 对象 |
int getInt(int colIndex) | 以int形式获取结果集当前行指定列号值 |
int getInt(String colLabel) | 以int形式获取结果集当前行指定列名值 |
float getFloat(int colIndex) | 以float形式获取结果集当前行指定列号值 |
float getFloat(String colLabel) | 以float形式获取结果集当前行指定列名值 |
String getString(int colIndex) | 以String 形式获取结果集当前行指定列号值 |
String getString(String colLabel) | 以String形式获取结果集当前行指定列名值 |
接收结果集
ResultSet rs = statement.executeQuery(sql);
ResultSet rs= statement.executeQuery("SELECT * FROM t_employees;");
遍历 ResultSet 中的数据
ResultSet 以表(table)结构进⾏临时结果的存储,需要通过 JDBC API 将其中数据进⾏依次获取。
数据⾏指针:初始位置在第⼀⾏数据前,每调⽤⼀次 boolean next()⽅法ResultSet 的指针向下
移动⼀⾏,结果为 true,表示当前⾏有数据。
rs.getXxx(整数);代表根据列的编号顺序获得,从 1 开始。
rs.getXxx("列名");代表根据列名获得。
boolean next() throws SQLException //判断 rs 结果集中下⼀⾏是否存在数据
综合案例
public class JDBCDemo {
public static void main(String[] args) {
try {
//1,注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2,连接数据库
String url = "jdbc:mysql://localhost:3306/testjdbc";
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, username, password);
//3,获取执行SQL的对象
Statement stmt = connection.createStatement();
//4,执行SQL
String sql = "SELECT id,name FROM person";
//执行后的结果集
ResultSet resultSet = stmt.executeQuery(sql);
//5,处理结果
while(resultSet.next()){
//一列一列获取
// int id = resultSet.getInt("id");
int id = resultSet.getInt(1);//从1开始
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String sex = resultSet.getString("sex");
System.out.println(id+"-"+name+"-"+age+"-"+sex);
}
//6,释放资源
resultSet.close();
stmt.close();
connection.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
五、 常见错误
java.lang.ClassNotFoundException:找不到类(类名书写错误、没有导⼊jar包)
java.sql.SQLException:与sql语句相关的错误 (约束错误、表名列名书写错误) 建议:在客户 端⼯具中测试SQL语句之后再粘贴在代码中
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 原因: 列值Sting类型没有加单引号
Duplicate entry '1' for key 'PRIMARY' 原因,主键值已存在或混乱,更改主键值或清空表
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'password' in 原因:可能输⼊的值的类型不对,确定是否插⼊的元素时对应的值的类型正确
六、SQL注入问题
什么是 SQL 注入
⽤户输⼊的数据中有 SQL 关键字或语法并且参与了 SQL 语句的编译,导致 SQL 语句编译后的条件
含义为 true,⼀直得到正确的结果。这种现象称为 SQL 注⼊。
需求:实现登录功能,需要通过传⼊的登录名和密码查询数据库表中的⽤户是否存在。
如果使⽤的是Statement语句来执⾏sql语句。
String sql="select * from account where username='"+loginName+"' and password='"+loginPwd+"'";
如果应⽤中传⼊:
》请输⼊⽤户名:
test1
》请输⼊密码:
abc' or '1'='1
username="test1"
password="abc' or '1'='1"
那么当上⾯的sql拼接变量值之后的sql语句为:
select * from account where username='test1' and password='abc' or '1'='1'
那么将登录成功。
如何避免 SQL 注入
由于编写的 SQL 语句是在⽤户输⼊数据,整合后再进⾏编译。所以为了避免 SQL 注⼊的问题,我们要使 SQL 语句在⽤户输⼊数据前就已进⾏编译成完整的 SQL 语句,再进⾏填充数据。
七、PreparedStatement
PreparedStatement 继承了 Statement 接⼝,执⾏ SQL 语句的⽅法⽆异。
PreparedStatement的应⽤
作用:
预编译SQL 语句,效率⾼。
什么是预编译?
预编译语句PreparedStatement是java.sql中的⼀个接⼝,它是Statement的⼦接⼝。通过Statement对象执⾏sql语句时,需要将sql语句发送给DBMS,由DBMS⾸先进⾏编译再执⾏(在创建通道的时候并不进⾏sql的编译⼯作,事实上也⽆法进⾏编译)。⽽通过PreparedStatement不同,在创建PreparedStatement对象时就指定了sql语句,该语句⽴即发送给DBMS进⾏编译,当该语句被执⾏时,DBMS直接运⾏编译后的sql语句,⽽不需要像其他sql语句那样⾸先将被编译。⼀般在考虑反复使⽤⼀个sql语句时才使⽤预编译,预编译语句常常放在⼀个循环中使⽤(在这种情况下预编译的优势就很明显了),通过反复设置参数从⽽达到多次使⽤该语句;
安全,避免SQL注⼊ 。
可以动态的填充数据,执⾏多个同构的 SQL 语句。
参数标记
//1.预编译 SQL 语句
PreparedStatement pstmt = conn.prepareStatement("select * from user where username=? and password=?");
注意:JDBC中的所有参数都由 ?符号占位,这被称为参数标记。在执⾏SQL语句之前,必须为每个参数提供值。
简化SQL语句的编写,提⾼SQL语句执⾏效率,提⾼安全性。
动态参数绑定
pstmt.setXxx(下标,值) 参数下标从 1 开始,为指定参数下标绑定值
//1.预编译 SQL 语句
PreparedStatement pstmt = conn.prepareStatement("select * from user where
username=? and password=?");
//2.为参数下标赋值
pstmt.setString(1,username);
pstmt.setString(2,password);
八、封装⼯具类
在实际JDBC的使⽤中,存在着⼤量的重复代码:例如连接数据库、关闭数据库等这些操作!
我们需要把传统的JDBC代码进⾏重构,抽取出通⽤的JDBC⼯具类!以后连接任何数据库、释放 资源都可以使⽤这个⼯具类。
在src ⽬录下新建 db.properties ⽂件。
driver=com.mysql.cj.jdbc.Driver
#在和mysql传递数据的过程中,使用unicode编码格式,字符集设置为utf-8,并根据需要设置时区
url=jdbc:mysql://127.0.0.1:3306/testjdbc?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
user=root
password=root
通过单例模式读取配置文件
package cn.jdbc.config;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* Description:通过单例模式读取配置文件
* @version:1.0
*/
public class ConfigManager {
private static ConfigManager configManager;
private static Properties properties;
/**
* 私有构造器-读取数据库配置文件
*/
private ConfigManager(){
String configFile = "database.properties";
properties = new Properties();
InputStream is = ConfigManager.class.getClassLoader().getResourceAsStream(configFile);
try {
properties.load(is);
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 对外提供的获取ConfigManager实例的方法
* 懒汉式:双重检查锁方式
*/
public static ConfigManager getInstance(){
//第一次判断,如果 configManager 的值不为null,不需要抢占锁,直接返回对象
if(configManager == null){
synchronized (ConfigManager.class){
//第二次判断
if(configManager == null) {
configManager = new ConfigManager();
}
}
}
/*configManager = ConfigManagerHelper.CONFIG_MANAGER;*/
return configManager;
}
/**
* 静态内部类
*/
/*private static class ConfigManagerHelper{
private static final ConfigManager CONFIG_MANAGER = new ConfigManager();
}*/
public String getValue(String key){
return properties.getProperty(key);
}
}
工具类的封装
package cn.jdbc.utils;
import cn.jdbc.config.ConfigManager;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 数据库连接与关闭工具类。
*/
public class DatabaseUtil {
// 数据库驱动字符串
private static String driver = ConfigManager.getInstance().getValue("driver");
// 连接URL字符串
private static String url = ConfigManager.getInstance().getValue("url");
// 数据库用户名
private static String user = ConfigManager.getInstance().getValue("user");
// 用户密码
private static String password = ConfigManager.getInstance().getValue("password");
// 加载驱动
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接对象。
*/
public static Connection getConnection() {
// 获取连接并捕获异常
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;// 返回连接对象
}
/**
* 关闭数据库连接。
* @param conn 数据库连接
* @param stmt Statement对象
* @param rs 结果集
*/
public static void closeAll(Connection conn, Statement stmt, ResultSet rs) {
// 若结果集对象不为空,则关闭
try {
if (rs != null && !rs.isClosed())
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
// 若Statement对象不为空,则关闭
try {
if (stmt != null && !stmt.isClosed())
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
// 若数据库连接对象不为空,则关闭
try {
if (conn != null && !conn.isClosed())
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}