今天加上昨天将MySQL的基础知识学完了,这个是第二部分,上一部分在这。
5、MySQL 函数
5.1、常用函数
--数学
SELECT ABS(-55) -- 绝对值
SELECT CEILING(22.995) -- 向上取整
SELECT FLOOR(9.8) -- 向下取整
SELECT RAND () --随机数(0-1)
SELECT SIGN(99) -- 返回参数的符号
--字串
SELECT CHAR_LENGTH('蜜雪冰城甜蜜蜜') --字符串长度
SELECT CONCAT('l','b','w') -- 拼接字符串
SELECT INSERT ('蜜雪宾臣',3,2,'冰城') -- 从某个位置开始替换某个长度
SELECT LOWER('DDWAFGAWEFA') -- 转换成小写
SELECT UPPER('sadasdasd')-- 转换成大写
SELECT INSTR ('awdawdasnwdaw','n') --返回第一次出现字串的索引
SELECT REPLACE('蜜雪冰城甜蜜蜜','冰城','火山') -- 替换出现的指定字符串
SELECT SUBSTR('你爱我呀我爱你',2,3) --返回指定的字符串(源字符串 截取的位置 截取的长度)
-- 将表中刘同学都改成牛同学
SELECT REPLACE(`name`,'刘','牛') FROM `student`
WHERE `name` LIKE ('刘%')
---时间
SELECT CURDATE() --获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() --本地时间
SELECT SYSDATE() --系统时间
SELECT YEAR(NOW())
SELECT USER()
SELECT VERSION() --版本信息
5.2、聚合函数
函数名称 | 描述 |
COUNT() | 返回满足Select条件的记录总和数,如 select count(*) 【不建议使用 *,效率低】 |
SUM() | 返回数字字段或表达式列作统计,返回一列的总和。 |
AVG() | 通常为数值字段或表达列作统计,返回一列的平均值 |
MAX() | 可以为数值字段,字符字段或表达式列作统计,返回最大的值 |
MIN() | 可以为数值字段,字符字段或表达式列作统计,返回最小的值。 |
/*COUNT:非空的*/
SELECT COUNT(studentname) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student; /*推荐*/
-- 从含义上讲,count(1) 与 count(*) 都表示对全部数据行的查询。
-- count(字段) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即不统计字段为null 的记录。
-- count(*) 包括了所有的列,相当于行数,在统计结果的时候,包含字段为null 的记录;
-- count(1) 用1代表代码行,在统计结果的时候,包含字段为null 的记录 。
/*
很多人认为count(1)执行的效率会比count(*)高,原因是count(*)会存在全表扫描,而count(1)可以针对一个字段进行查询。其实不然,count(1)和count(*)都会对全表进行扫描,统计所有记录的条数,包括那些为null的记录,因此,它们的效率可以说是相差无几。而count(字段)则与前两者不同,它会统计该字段不为null的记录条数。
下面它们之间的一些对比:
1)在表没有主键时,count(1)比count(*)快
2)有主键时,主键作为计算条件,count(主键)效率最高;
3)若表格只有一个字段,则count(*)效率较高。
*/
SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;
5.3、数据库级别的MD5加密(扩展)
5.3.1、MD5简介
MD5即Message-Digest Algorithm 5(信息-摘要算法5),用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一(又译摘要算法、哈希算法),主流编程语言普遍已有MD5实现。将数据(如汉字)运算为另一固定长度值,是杂凑算法的基础原理,MD5的前身有MD2、MD3和MD4。
5.3.2、实现密码加密
--创建一个表格
CREATE TABLE `testmd5` (
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
--插入数据
INSERT INTO testmd5 VALUES(1,'Tony','123456'),(2,'Martin','456789')
-- 对'Tony' 的密码进行加密
update testmd5 set pwd = md5(pwd) where name = 'Tony';
-- 插入数据的时候自动加密
INSERT INTO testmd5 VALUES(4,'Peter',md5('123456'));
--查询登录用户信息(md5对比使用,查看用户输入加密后的密码进行比对)
SELECT * FROM testmd5 WHERE `name`='Sam' AND pwd=MD5('123456');
6、事务
6.1、什么是事务
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
6.2、事务的ACID原则
详细说明大家可以看这篇博客,这里简单的说明一下。
原子性(Atomic):
要么都成功,要么都失败,不可能存在完成一半的情况。如果事务在执行的过程中出错,则会回滚到事务开始的状态。
一致性(Consist):
事务前后的数据完整性要保持一致。以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性。
隔离性(Isolated):
事务的隔离性是多个用户并发访问数据库时,数据库为每个用户开启的事务,不能被其他事务的操作数据干扰。为了防止事务操作间的混淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据。
持久性(Durable):
事务一旦提交,不可逆,被保存在数据库中,并不会被回滚.
隔离所导致的一些问题
脏读:
指一个事务读取了另一个事务未提交的数据。
不可重复读:
在一个事务读取表中的某一行数据,多次读取结果不同。(这个不一定时错误,只是某些场合不对)。
幻读:
是指一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
执行事务
-- mysql是默认开启事务自动提交的
SET autocommit=0 --关闭
SET autocommit=1 --开启
-- 手动处理事务
SET autocommit=0 --关闭自动提交
-- 事务开始
START TRANSACTION --标记一个事务的开始 从这个之后的sql都在同一个事务之内
INSERT xx
InSERT xx
--提交; 持续化(成功!)
COMMIT
--回滚;回到原来的样子(失败)
ROLLBACK
--事务结束
SET autocommit=1 --开启自动提交
--了解
SAVEPOINT 保存点名 --设置一个事务的保存点
ROLLBACK TO SAVEPOINT --回滚到保存点
RELEASE SAVEPOINT --撤销保存点
可能有些人还是不清楚为什么执行一个事务要这么麻烦,不是默认自动提交的么?为什么要关闭再开启?
分析: 我们一个事务里面不只是包含一个语句,这些语句根据原子性要没都完成,要么都不完成。如果没有关闭自动提交,每一个语句就相当于一个事务,就违背了之前的原子性。下面是一个例子
模拟场景(银行转账)
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交
7、索引
MySQL官方对索引的定义:索引(index)是帮助MySQL 高效获取的数据结构
提取句子主干,就可以得到索引的本质:索引是数据结构。
7.1、索引的作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
7.2、索引的分类
- 主键索引 PRIMARY KEY
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 确保数据记录的唯一性
- 唯一索引 (UNIQUE KEY)
- 避免同一个表中某数据列中的值重复,唯一索引可以重复,多个列都可以标识为唯一索引
- 常规索引 (KEY/INDEX)
- 默认的,用index 或者 key关键字来设置
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
- 全文索引 (FULLTEXT)
- 在特定的数据库引擎下才有,快速定位数据
- 快速定位数据
- 适合大型数据集
基础语法:
-- 索引的使用
-- 1. 在创建表的时候给字段增加索引
-- 2. 创建完毕后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
--增加一个全文索引(索引名) 列名
ALTER TABLE school.student ADD FULLTEXT `studentName` (`studentName`) ;
-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT *FROM student;-- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH (studentName) AGAINST ('刘'); --全文索引
7.3、测试索引
为了测试索引的作用,我们批量加入有100w条数据
先建表
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别(0:男;1:女)',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
插入数据:
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
VALUES(CONCAT('用户', i), '666666666@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
-- 查询第9999个用户(没有索引)
SELECT * FROM app_user WHERE name = '用户9999';
-- 分析这个sql语句
EXPLAIN SELECT * FROM app_user WHERE `name` ='用户9999';
可以看到我们用了一秒钟多,并且查询了99w的数据
CREATE INDEX id_app_user_name ON app_user(`name`);
我们创建了索引之后再执行这个查询语句,可以看到我们就只用了0.01秒,并且直接定位到了这条数据。
7.4、索引原则
- 索引不是越多越好
- 不要对经常变动的数据添加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
大家可以看看这篇文章,对索引的底层原理讲的特别好。
8、权限管理和备份
8.1、用户管理
SQL yog 可视化管理
我们可以在这个窗口里面添加一个新用户,并且给它权限,也可以删除一个用户。
SQL 命令
本质:对mysql.user (系统用户表) 这张表进行增删改
-- 创建用户
CREATE USER Tony IDENTIFIED BY '123456'
-- 修改密码(当前账户)
SET PASSWORD =PASSWORD('123456')
-- 修改密码(指定账户)
SET PASSWORD FOR Tony =PASSWORD('123456')
-- 重命名
RENAME USER Tony To Sam
-- 用户授权 授予全部权限 除了给别人授权的权限
GRANT ALL PRIVLEGES ON *.* TO Tony
-- 查看权限
SHOW GRANTS FOR Tony
SHOW GRANTS FOR root@localhost
-- 撤销权限
REOMVE ALL PRIVILEGES ON *.* FROM Tony
-- 删除用户
DROP USER Tony
8.2、数据库备份
为什么要备份:
- 保证重要数据不丢失
- 数据转移
MySQL 数据库备份的方式
- 拷贝物理文件 data
- 在SQLyog 可视化工具上手动导出,在想要导出的数据库或者表右键
- 使用命令行导出 mysqldump
- cmd导出:
#cmd上 mysqldump -h 主机 -u用户名 -p密码 数据库 > 物理位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student1 >D:/文件/a.sql
#这里最后一定要加上你要保存成什么名字+文件后缀
cmd导入:
#导入 在登陆的情况下 切换到指定数据库 source 备份文件
source d:/a.sql
#没有登录的情况下
mysql -u用户名 -p密码 库名< 备份文件
9、规范数据库设计
9.1、为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库:
- 数据冗余,浪费空间
- 数据库的插入和删除都会麻烦,【屏蔽使用物理外键】
- 程序的性能差
良好的数据库:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库设计
- 分析需求:分析业务
- 概要设计,设计关系图
设计数据库的步骤:(个人博客)
- 收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章的分类,谁创建的)
- 文章表(文章的信息)
- 评论表
- 友链表(友情连接信息)
- 自定义表(系统信息,某个关键字,或某些主字段)
- 说说表(发表心情…id…content…create_time)
- 标识实体(把需求落实到每个字段)
- 标识实体之间的关系
- 写博客 user–>user
- 创建分类: user–>category
- 关注: user–>user
- 友链:links
- 评论: user-user-blog
9.2、三大范式
为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法显示正常信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式
原子性:保证每一列不可再分
第二范范式
前提:满足第一范式的前提下,每张表只表示一个信息
第三范式
前提:满足第一第二范式
确保数据表的每一列数据都和主键直接相关,而不能见解相关
如果我们都按照三大范式来操作,那么一张表可能被我们拆成了十几张表,这样子性能就会很差
规范性 和 性能的问题
关联查询的表不得超过三张
- 考虑商业化的需求和目标,(成本,用户体验) 数据库的性能更加重要
- 在规范性能的问题的时候,需要适当的考虑一下 规范性
- 故意给某些表增加一些冗余的字段(从多表查询变成单表查询)
- 故意增加一些计算列(从大数据降低为小数据的查询:索引)
10、JDBC(重点)
10.1、数据库驱动
驱动:声卡,显卡,数据库
我们的程序不能直接操作数据库,所以我们需要驱动。应用程序对驱动操作,驱动对数据库进行操作。
10.2、JDBC
SUN公司为了简化开发人员的操作,提供了一个(Java操作数据库的)规范,俗称JDBC 这些规范的实现由具体的厂商去做
对于开发人员来说,我们只需要掌握JDBC的接口即可!
10.3、JDBC程序
10.3.1创建一个数据库
CREATE DATABASE `jdbcstudy`
CREATE TABLE users
(
`id` INT (4) PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
`birthday` DATE
);
INSERT INTO `users` (`id`,`name`,`password`,`birthday`)
VALUES ('1','张三','123456','1999-02-02'),('2','李四','1234556','1999-08-02'),('3','王五','1288556','1998-02-15')
,('4','赵六','752156','1999-12-12')
10.3.2 导入驱动
这里我们从MAVEN上下载对应版本的jar包:网站。
在ide上创建好一个项目并且创建一个lib目录,并将我们下载好的jar包粘贴进去,这时候我们还没有导入好。
点击添加为库,这时候我们就导入成功,可以点开这个包了。
10.3.3 测试代码
package com.yubao;
import com.mysql.cj.protocol.Resultset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//我的第一个JDBC程序
public class JdbcFirst {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动 如果是8.0新版本的话有cj 老版本的话没有
Class.forName("com.mysql.cj.jdbc.Driver");// 固定写法,加载驱动
//2. 用户信息 新版本需要加个时区就是最后面的那个 如果在报错的话将SSL关掉,我这里就是因为报错所以关了
String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
String username = "root";
String password = "228934";
//3.连接成功, 数据库对象 Connection 代表数据库
Connection connection= DriverManager.getConnection(url,username,password);
//4.执行SQL对象
Statement statement=connection.createStatement();
//5.执行SQL的对象 去执行SQL, 可能存在返回对象, 查看返回结果
String sql="SELECT* FROM users";
ResultSet resultSet =statement.executeQuery(sql);//返回结果集
while(resultSet.next()){
System.out.println("id"+resultSet.getObject("id"));
System.out.println("name"+resultSet.getObject("name"));
System.out.println("password"+resultSet.getObject("password"));
System.out.println("birthday"+resultSet.getObject("birthday"));
}
//6.释放连接
resultSet.close();;
statement.close();
connection.close();
}
}
步骤总结:
- 加载驱动
- 连接数据库
- 获得执行sql的对象 Statement
- 获得返回的值
- 释放连接
10.3.4 详细讲解
1. DriverManager
Class.forName("com.mysql.cj.jdbc.Driver");
//固定写法
2. URL
String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC"
//mysql默认3306
//协议://主机地址:端口号/数据库名?参数1&参数2&参数3
3. Statement 是执行SQL的对象 PrepareStatement 执行的SQL对象
String sql="sekect * from users";
statement.executeQuery();//查询
statement.execute();//执行任何SQL
statement.executeUpdate();//更新,插入,删除
4. ResultSet 查询的结果集:封装了所有的查询结果
// 获取指定的数据类型
resultset.getint();
resultset.getobject();
resultset.getDate();
...
//指针
resultset.beforFirst();//移动到第一个
resultset.afterLast();//移动到最后面
resultset.absolute(row)//移动到指定类
resultset.next();//移动到下一个数据
5.释放资源
resultSet.close();
statement.close();
connection.close();
10.4、statement对象
Jdbc中的statement对象用于想数据库发送sql语句,想完成对数据库的增删改查,只需要通过这个对象向数据库中发送增删改查的语句即可
Statement 对象的executeUpdate方法,用于向数据库中发送增、删、改的sql语句,executeUpdate执行完后,将返回一个整数(即影响的行数)。
Statement.executeQuery 方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象
10.4.1CRUD操作
配置文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=flase&serverTimezone=UTC
username=root
password=123456
##最好写在src目录文件下,注意看写在什么地方
提取工具类
package com.yubao.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static{
try{
InputStream in=JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties=new Properties();
properties.load(in);
driver =properties.getProperty("driver");
url =properties.getProperty("url");
username =properties.getProperty("username");
password =properties.getProperty("password");
Class.forName(driver);
//驱动只加载一次
}catch(IOException e){
e.printStackTrace();
}catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException
{
return DriverManager.getConnection(url,username,password);
}
//释放连接资源
public static void release(Connection conn,Statement st, ResultSet rs) throws SQLException
{
if(rs!=null)
{
rs.close();
}
if(st!=null)
{
st.close();
}
if(conn!=null)
{
conn.close();
}
}
}
增删改 代码
增删改使用的都是executeUpdate, 所以这里演示了Insert的用法
package com.yubao.lesson02;
import com.yubao.lesson02.utils.JdbcUtils;
import javax.swing.plaf.nimbus.State;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn=JdbcUtils.getConnection();
st=conn.createStatement();
String sql="INSERT INTO users (id,`name`,`password`,`birthday`) VALUE (5,'Tony','12121','1988-2-5')" ; //增删改只需要改变这里的代码即可
int i=st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
查 代码
package com.yubao.lesson02;
import com.yubao.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestQuery {
public static void main(String[] args) throws SQLException {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn= JdbcUtils.getConnection();
st=conn.createStatement();
String sql="SELECT * FROM users where id=1";
rs=st.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getObject("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
10.4.2 SQL注入
sql存在漏洞,会被攻击,通过SQL语句,实现无账号登录,甚至篡改数据库。
这里不进行详解,只是作为一个了解
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class sql {
public static void main(String[] args) {
sql.login(" 'or '1=1", "'or '1=1");
}
public static void login(String username,String password)
{
Connection conn=null;
Statement sta=null;
ResultSet re=null;
try {
conn= jdbcutils.getConnection();
sta=conn.createStatement();
String sql="SELECT * FROM `users` WHERE `name`='"+username+"' AND `password`='"+password+"'";
re=sta.executeQuery(sql);
while(re.next())
{
System.out.print(re.getString("name")+" ");
System.out.println(re.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
上述代码是怎么实现SQL注入的呢?
将username 和password 拼接起来我们得到
’ ’ or’1=1’ 这是个恒成立的语句,所以我们的数据库就可以通过这个直接获取全部信息。
10.5、PreParedStatement对象
这个对象能够防止SQL注入,并且效率比Statement更高
下面是PrepareStatement 执行增删改查的代码
还是根据之前的配置文件和工具类,增删改基本一样这里还是展示增加的代码
10.5.1 增删改
import com.yubao.lesson02.utils.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestInsert02{
public static void main(String[] args) throws SQLException {
Connection conn=null;
PreparedStatement pst=null;
ResultSet rs=null;
try {
conn= JdbcUtils.getConnection();
String sql="INSERT INTO users (id,`name`,`password`,`birthday`)" +
" VALUE (?,?,?,?)";
//区别 使用?占位符
pst=conn.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
//这个时候没有执行
//手动给参数赋值
pst.setInt(1,7);//参数下标 具体的值
pst.setString(2,"James");
pst.setString(3,"123456");
pst.setDate(4,new java.sql.Date(new Date().getTime()));
//注意点: sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime() 获得时间戳
//执行
int i=pst.executeUpdate();
if(i>0){
System.out.println("执行成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
10.5.2 查询
package com.yubao.lesson02;
import com.yubao.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestQuery02 {
public static void main(String[] args) throws SQLException {
Connection conn= null;
PreparedStatement pst=null;
ResultSet rs =null;
try {
conn= JdbcUtils.getConnection();
String sql="select * from users where id=?";
pst=conn.prepareStatement(sql);
pst.setInt(1,1);
rs=pst.executeQuery();
if(rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
10.5.3 防止SQL注入
- PreparedStatement 防止SQL 注入的本质 :把传递过来的参数当作字符
- 假设其中存在转义字符 ,比如说 ’ 会被直接转义
package com.yubao.lesson02;
import com.yubao.lesson02.utils.JdbcUtils;
import java.sql.*;
public class SQL {
public static void main(String[] args) throws SQLException {
SQL.Login(" 'or '1=1", "'or '1=1");
}
public static void Login(String username,String password) throws SQLException {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn= JdbcUtils.getConnection();
//PreparedStatement 防止SQL 注入的本质 :把传递过来的参数当作字符
// 假设其中存在转义字符 ,比如说 ' 会被直接转义
String sql="SELECT * FROM `users` WHERE `name`=? AND `password`=?";
st=conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs=st.executeQuery();
while(rs.next())
{
System.out.print(rs.getString("name")+" ");
System.out.println(rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.6 、IDEA链接数据库
如图所示
点完之后的界面就很熟悉,输入账号密码之后,可能还是不能Apply,因为没有驱动,最新版本的IDEA可以直接在线下载驱动,点击Problems就会有下载的页面。也可以点击左侧的Drivers选择MySQL下载对应版本的驱动。
从这里可以调出SQL的编辑器
在这里可以切换数据库
IDEAyyds 还有很多功能大家自己探索吧。
10.7、JDBC操作事务
import com.mysql.cj.jdbc.JdbcConnection;
import com.yubao.lesson02.utils.JdbcUtils;
import javafx.application.Application;
import javafx.stage.Stage;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) throws SQLException {
Connection conn=null;
PreparedStatement pst =null;
ResultSet rs =null;
try {
conn= JdbcUtils.getConnection();
// 关闭数据库的自动提交
conn.setAutoCommit(false);
String sql1 ="update account set money =money -100 where name ='A'";
pst =conn.prepareStatement(sql1);
pst.executeQuery();
// 使事务失败 int x= 1/0;
String sql2 ="update account set money =money -100 where name ='B'";
pst =conn.prepareStatement(sql2);
pst.executeQuery();
//业务完毕 ,提交事务
conn.commit();
System.out.println("成功!");
} catch (SQLException throwables) {
//如果失败 ,则会默认回滚
// try {
// conn.rollback();
// } catch (SQLException e) {
// e.printStackTrace();
// }
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}