一、数据库的三大范式
第一范式(1NF):数据表中的每一列(每个字段)必须是不可拆分的最小单元,也就是确保每一列的原子性。
第二范式(2NF):满足1NF后,要求表中的所有列,都必须依赖于主键,而不能有任何一列与主键没有关系,也就是说一个表只描述一件事情。
第三范式(3NF):必须先满足第二范式(2NF),要求:表中的每一列只与主键直接相关而不是间接相关,(表中的每一列只能依赖于主键)。
二、JDBC
通过使用JDBC,就可以使用同一种API访问不同的数据库系统。换言之,有了JDBC API,就不必为访问Oracle数据库学习一组API,为访问DB2数据库又学习一组API…开发人员面向JDBC API编写应用程序,然后根据不同的数据库,使用不同的数据库驱动程序即可。
彩蛋:
最早的时候,Sun公司希望自己开发一组Java API,程序员通过这组Java API即可操作所有的数据库系统,但后来Sun发现这个目标具有不可实现性一因为数据库系统太多了,而且各数据库系统的内部特性又各不相同。后来Su就制定了一组标准的API,它们只是接口,没有提供实现类一这些实现类由各数据库厂商提供实现,这些实现类就是驱动程序。而程序员使用JDBC时只要面向标准的JDBC API编程即可,当需要在数据库之间切换时,只要更换不同的实现类(即更换数据库驱动程序)就行,这是面向接口编程的典型应用。
Java语言的各种跨平台特性,都采用相似的结构,因为它们都需要让相同的程序在不同的平台上运行,所以都需要中间的转换程序(为了实现Java程序的跨平台性,Java为不同的操作系统提供了不同的虚拟机)。同样,为了使DBC程序可以跨平台,则需要不同的数据库厂商提供相应的驱动程序。上图显示了JDBC驱动示意图。正是通过JDBC驱动的转换,才使得使用相同JDBC API编写的程序,在不同的数据库系统上运行良好。
Sun提供的JDBC可以完成以下三个基本工作:
1、建立与数据库的连接。
2、执行SQL语句。
3、获得SQL语句的执行结果。
通过JDBC的这三个功能,应用程序即可访问、操作数据库系统。
三、第一个JDBC程序
首先我们在数据库中执行以下SQL
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`code` int(11) NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `code`(`code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, 1);
INSERT INTO `test` VALUES (5, 5);
INSERT INTO `test` VALUES (10, 10);
SET FOREIGN_KEY_CHECKS = 1;
然后我们在maven官方仓库下载这两个jar包
maven官方仓库网址
编写一个第一个JDBC程序
package com.imperfect.lession01;
import java.sql.*;
/**
* @author : Imperfect(lxm)
* @Des:
* @date : 2022/12/13 11:15
*/
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver"); //加载驱动
//2、用户信息和url userUnicode=true&characterEncoding=utf8&userSSL=true
String url = "jdbc:mysql://数据库地址与端口/数据库名?userUnicode=true&characterEncoding=utf8&userSSL=true";
String username = "账号";
String password = "密码";
//3、连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4、执行SQL的对象Statement执行sql的对象
Statement statement = connection.createStatement();
//5、执行SQL的对象去执行SQL
String sql = "select * from test";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("code=" + resultSet.getObject("code"));
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
执行结果:
四、SQL注入问题
首先导入测试user表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'imperfect', '123456');
INSERT INTO `user` VALUES (2, 'sam', '1234');
SET FOREIGN_KEY_CHECKS = 1;
这里我们试图模拟一下正常用户的登录流程:
package com.imperfect.lession03;
import java.sql.*;
public class sqlInjection {
public static void main(String[] args) throws Exception {
login("imperfect","123456");
}
public static void login(String username,String password)throws Exception{
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver"); //加载驱动
//2、用户信息和url userUnicode=true&characterEncoding=utf8&userSSL=true
String url = "jdbc:mysql://数据库地址以及端口/数据库名?userUnicode=true&characterEncoding=utf8&userSSL=true";
//3、连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, "账号", "密码");
//4、执行SQL的对象Statement执行sql的对象
Statement statement = connection.createStatement();
//5、执行SQL的对象去执行SQL
String sql = "select * from user where name ='"+username+"'"+"and password='"+password+"'";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("name=" + resultSet.getObject("name"));
System.out.println("password=" + resultSet.getObject("password"));
System.out.println("========================================================");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
成功登录后返回对应的用户名和密码:
然后这里我们模拟一下SQL注入的场景:
package com.imperfect.lession03;
import java.sql.*;
public class sqlInjection {
public static void main(String[] args) throws Exception {
login(" 'or '1=1"," 'or '1=1"); //注意:这里我们修改了一下查询的条件为永真
}
public static void login(String username,String password)throws Exception{
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver"); //加载驱动
//2、用户信息和url userUnicode=true&characterEncoding=utf8&userSSL=true
String url = "jdbc:mysql://数据库地址以及端口/数据库名?userUnicode=true&characterEncoding=utf8&userSSL=true";
//3、连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, "账号", "密码");
//4、执行SQL的对象Statement执行sql的对象
Statement statement = connection.createStatement();
//5、执行SQL的对象去执行SQL
String sql = "select * from user where name ='"+username+"'"+"and password='"+password+"'";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("name=" + resultSet.getObject("name"));
System.out.println("password=" + resultSet.getObject("password"));
System.out.println("========================================================");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
执行结果如下:
这里把我们数据库中user表中所有的数据都查出来了
五、PreparedStatement执行SQL语句
如果经常需要反复执行一条结构相似的SQL语句,例如如下两条SQL语句
insert into student_table values(null,'张三',1);
insert into student_table values(null,'李四',2);
对于这两条SQL语句而言,她们的结构基本上相似,只是执行的值有所不同而已。对于这种情况,可以使用带占位符(?)参数的SQL语句来代替它
insert into student_table values(null,?,?);
但Statement执行SQL语句时不允许使用问号占位符参数,而且这个问号占位符参数必须获得值后才可以执行。为了满足这种功能,JDBC提供了PreparedStatement接口,它是Statement接口的子接口,它可以预编译SQL语句,预编译后的SQL语句被存储在PreparedStatement对象中,然后可以使用该对象多次高效地执行该语句。简而言之,使用PreparedStatement比使用Statement的效率要高。创建PreparedStatement对象使用Connection的prepareStatement()方法,该方法需要传入一个SQL字符串,该SQL字符串可以包含占位符参数。如下代码所示:
//创建一个PrepareStatement对象
pstmt=conn.prepareStatement("insert into student_table values(null,?,1)");
PreparedStatement也提供了execute()、executeUpdate()、executeQuery()三个方法来执行SQL语句,不过这三个方法无须参数,因为PreparedStatement已存储了预编译的SQL语句。使用PreparedStatement预编译SQL语句时,该SQL语句可以带占位符参数,因此在执行SQL语句之前必须为这些参数传入参数值,PreparedStatement提供了一系列的setXxx(int index,Xxx value)方法
来传入参数值。
注意:
如果程序很清楚PreparedStatement预编译SQL语句中各参数的类型,则使用相应的setXxx()方法来传入参数即可;如果程序不清楚预编译SQL语句中各参数的类型,则可以使用setObject()方法来传入参数,由PreparedStatement来负责类型转换。
下面示范一下使用Statement和PreparedStatement分别插入100条记录的对比。使用statement需要传入100条SQL语句,而使用preparedStatement则只需要插入1条预编译的SQL语句,然后100次为改PreparedStatement参数设值即可。
请看一下以下例子:
示例数据库SQL:
DROP TABLE IF EXISTS `test_student`;
CREATE TABLE `test_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`number` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 301 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
示例测试代码:
package com.imperfect.lession04;
import java.sql.*;
public class PreParedStatementTest {
public static void main(String[] args) throws Exception {
insert();
}
public static void insert()throws Exception{
//1、加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); //加载驱动
//2、用户信息和url userUnicode=true&characterEncoding=utf8&userSSL=true
String url = "jdbc:mysql://数据库地址端口/数据库名字?userUnicode=true&characterEncoding=utf8&userSSL=true";
//3、连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, "账号", "密码");
//4、执行SQL的对象Statement执行sql的对象
Statement statement = connection.createStatement();
//5、执行statement的对象去执行SQL
Long start=System.currentTimeMillis();
for(int i=1;i<=100;i++){
statement.executeUpdate("INSERT INTO test_student values(" +i+ ",'"+"姓名"+i+"'"+","+i+")");
}
System.out.println("使用statement耗时:"+(System.currentTimeMillis()-start));
//6、执行preparedStatement的对象去执行SQL
Long start1=System.currentTimeMillis();
PreparedStatement preparedStatement = connection.prepareStatement("insert into test_student values(?,?,?)");
for(int i=101;i<=200;i++){
preparedStatement.setInt(1,i);
preparedStatement.setString(2,"姓名"+i);
preparedStatement.setInt(3,i);
preparedStatement.executeUpdate();
}
System.out.println("使用preparedStatement耗时:"+(System.currentTimeMillis()-start1));
//6、释放连接
preparedStatement.close();
statement.close();
connection.close();
}
}
执行结果:
多次运行上面程序,可以发现使用PreparedStatement插入l00条记录所用的时间比使用Statement插入IO0条记录所用的时间少,这表明PreparedStatement的执行效率比Statement的执行效率高。除此之外,使用PreparedStatement还有一个优势一当SQL语句中要使用参数时,无须“拼接”SQL字符串。而使用Statement则要“拼接”SQL字符串,如上程序中粗体字代码所示,这是相当容易出现错误的一注意粗体字代码中的单引号,这是因为$QL语句中的字符串必须用单引号引起来。尤其是当SQL语句中有多个字符串参数时,“拼接”这条SQL语句时就更容易出错了。使用PreparedStatement则只需要使用问号占位符来代替这些参数即可,降低了编程复杂度。
使用PreparedStatement还有一个很好的作用一用于防止SQL注入,使用PreparedStatement会把对应传入的参数全部转换成字符串,从而避免了SQL注入的问题。
总体来看,使用PreparedStatement比使用Statement多了如下三个好处:
PreparedStatement预编译SQL语句,性能更好,
PreparedStatement无须“拼接”SQL语句,编程更简单。
PreparedStatement可以防止SQL注入,安全性更好。
基于以上三点,通常推荐避免使用Statement来执行SQL语句,改为使用PreparedStatement执SQL语句。
六、Druid连接池
数据库连接的建立及关闭是极耗费系统资源的操作,在多层结构的应用环境中,这种资源的耗费对系统性能影响尤为明显。通过前面介绍的方式(通过DriverManager获取连接)获得的数据库连接,个数据库连接对象均对应一个物理数据库连接,每次操作都打开一个物理连接,使用完后立即关闭连接。频繁地打开、关闭连接将造成系统性能低下。数据库连接池的解决方案是:当应用程序启动时,系统主动建立足够的数据库连接,并将这些连接组成一个连接池。每次应用程序请求数据库连接时,无须重新打开连接,而是从连接池中取出已有的连接使用,使用完后不再关闭数据库连接,而是直接将连接归还给连接池。通过使用连接池,将大大提高程序的运行效率。对于共享资源的情况,有一个通用的设计模式:资源池(Resource Pool),用于解决资源的频繁请求、释放所造成的性能下降。为了解决数据库连接的频繁请求、释放,JDBC2.0规范引入了数据库连接池技术。数据库连接池是Connection对象的工厂。数据库连接池的常用参数如下。
1、数据库的初始连接数。
2、连接池的最大连接数。
3、连接池的最小连接数。
4、连接池每次增加的容量。
首先我们在maven的官方网址下载需要准备的druid的jar包
测试数据库的SQL
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`code` int(11) NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `code`(`code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, 1);
INSERT INTO `test` VALUES (5, 5);
INSERT INTO `test` VALUES (10, 10);
SET FOREIGN_KEY_CHECKS = 1;
然后编写好对应的db.properties配置文件
#datasource 配置
db-type=com.alibaba.druid.pool.DruidDataSource
#mysql-connector-java6.0及以上使用com.mysql.cj.jdbc.Driver;5.0使用com.mysql.jdbc.Driver。这也对应这mysql的版本
driverClassName=com.mysql.cj.jdbc.Driver
#连接地址
url=jdbc:mysql://数据库地址/数据库名?userUnicode=true&characterEncoding=utf8&userSSL=true
#数据库连接账户
username=数据库连接账户
#连接密码
password=连接密码
# 下面为连接池的补充设置,应用到上面所有数据源中
# 初始化大小,最小,最大
initial-size=5
min-idle=5
max-active=20
# 配置获取连接等待超时的时间
max-wait=60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis=300000
validation-query=SELECT 1 FROM DUAL
test-while-idle=true
test-on-borrow=false
test-on-return=false
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements=true
max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filter.commons-log.connection-logger-name=stat,wall,log4j
filter.stat.log-slow-sql=true
filter.stat.slow-sql-millis=2000
# 合并多个DruidDataSource的监控数据
use-global-data-source-stat=true
这里我们可以手动编写一个用于获取druid连接池连接的工具类JdbcUtils_Druid
package com.imperfect.lession02.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author : Imperfect(lxm)
* @Des:
* @date : 2022/12/15 11:36
*/
public class JdbcUtils_Druid {
private static DataSource dataSource = null;
static {
//加载配置文件
InputStream in = JdbcUtils_Druid.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
try {
properties.load(in);
//通过配置文件创建数据源
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//提供外部获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//释放资源
public static void release(Connection con, Statement st, ResultSet rs) throws SQLException {
con.close();
st.close();
rs.close();
}
}
编写测试类JdbcSecondDemo:
package com.imperfect.lession02;
import com.imperfect.lession02.utils.JdbcUtils_Druid;
import java.sql.*;
/**
* @author : Imperfect(lxm)
* @Des:
* @date : 2022/12/13 11:15
*/
public class JdbcSecondDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection connection = JdbcUtils_Druid.getConnection();
String sql="select * from test";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
System.out.println(resultSet.getString("id"));
}
JdbcUtils_Druid.release(connection,preparedStatement,resultSet);
}
}
查询结果
特别鸣谢:
《疯狂Java讲义(第3版)》2014年7月电子工业出版社出版