MySQL数据库基础——用户和权限管理＆JDBC

文章目录用户和权限管理权限是什么MySQL 用户的完整身份核心操作语法JDBC什么是JDBC 使用JDBC用户和权限管理权限是什么数据库服务安装成功后默认有一个root用户可以新建和操纵数据库服务中管理的所有数据库。在真实的使用过程中通常每个应用对应着一个数据库我们只希望某个用户只能操纵和管理当前应用对应的那个数据库而不能操纵和管理其他应用的数据库这时就可以添加一个用户并指定用户的权限刚创建的新用户默认几乎没有任何权限只能登录数据库看不到业务库也操作不了表。​权限需要 root 管理员手动授予。MySQL 用户的完整身份MySQL 里的账号不是只看用户名而是 「用户名 允许登录的主机host」合在一起才是一个唯一的账号。标准格式用户名主机地址​host 本质是登录白名单控制这个账号允许从哪里连接数据库​localhost只能从数据库本机登录%允许从任意地址登录​host 还可以写具体IP、网段属于运维精细化配置开发不用深究​所以devlocalhost和dev%是两个完全独立的账号密码、权限各算各的。用户相关信息都存在系统库mysql的user表里核心操作语法创建用户定义「谁、从哪登录」createuser用户名主机identifiedby密码;例如-- 创建一个只能本机登录的 dev 账号createuserdevlocalhostidentifiedbydev123456;修改密码-- 标准写法修改指定用户密码alteruser用户名主机identifiedby新密码;删除用户dropuser用户名主机;下面的几个操作语法知道有这么回事就行不用怎么看是运维干的授权给用户「能做什么」的权限grant权限类型on权限范围to用户名主机;查看用户有哪些权限showgrantsfor用户名主机;回收权限revoke权限类型on权限范围from用户名主机;总结本地开发基本都直接用 root 账号不用自己建用户、分权限图方便。生产环境一般由运维/DBA 建好账号、分配好对应库的权限给你你只管用来连接数据库写业务代码。JDBC什么是JDBC 之前学的都是操作数据库的技术——SQL那实际的业务是由Java程序来处理的所以该怎么使用程序来操作数据库呢JDBC (Java Data Base Connectivity——Java数据库连接是Java程序和数据库之间的桥梁包含了一套Java定义的用于执行SQL语句的接口使开发者能够编写数据库的程序。JDBC的主要作用是与数据库建立连接、发送SQL语句、处理数据库执行结果。正常来说访问数据库需要经过5和步骤确定数据库服务器的地址、端口号——确定数据源建立连接、用户名、密码——数据库连接发送要执行的SQL——执行对象处理返回的结果集——接收结果最后再关闭数据库连接——释放资源、关闭连接对于Java程序来说不同数据库底层通信协议、内部数据处理逻辑各不相同比如数据的编码、解码协议存在很多差异。JDBC 定义了一套统一的接口与方法规范不写底层实现由 MySQL、Oracle… 这些数据库厂商按照这套 JDBC 接口规范写好自家数据库的实现类厂商把这一堆实现类打包在一起发给我们这个压缩包就是驱动jar包。我们写Java代码只调用这套标准接口不用关心底层各个数据库的差别从而屏蔽不同数据库的底层差异。这样一来想换其他数据库只需要换掉jar包、改url和驱动类业务代码基本不用改动。我们使用Maven来自动下载驱动jar包方便Maven 是 Java 的项目构建 依赖管理工具它可以①自动管理 jar 包不用手动下载、复制jar在pom.xml写依赖它自动下载。​②统一项目目录结构、管理JDK版本、编译代码③它的核心配置文件 pom.xml Maven 就是读取这个文件按照里面的配置去干活下面干三件事具体怎么做不多阐述这主要是一些配置问题更换Maven国内镜像源Maven默认从国外服务器下载jar包国内网络访问不了或是速度慢换成阿里云镜像下载稳定快速新建Maven项目去官网中查找到相关依赖然后在idea中新建一个Maven项目就可以把依赖写入到项目里的pom.xml配置文件中了引入MySQL驱动依赖在pom.xml配置文件中写入依赖后Maven就会根据依赖的描述去自动下载驱动jar包了之后Java程序就有能力和MySQL数据库通信了可以尝试写JDBC代码了。使用JDBCJDBC的使用过程可以概括为加载数据库厂商的驱动包、建立连接、创建Statement、执行SQL、处理结果集、释放资源和关闭连接。下面就通过这几个步骤来进行JDBC的代码实现demo01版本一步步演示加载驱动jar包通过完全限定名加载指定的类到JVM// 1. 加载数据库厂商提供的驱动Class.forName(com.mysql.cj.jdbc.Driver);建立数据库连接使用驱动管理类 DriverManager 调用 getConnection()方法来获取数据库连接Connection connectionDriverManager.getConnection(URL,用户名,密码);其中getConnection()的三个参数为连接地址url、账号user、密码passworduser就是你的MySQL登录账号一般本地默认 rootpassword你自己安装MySQL时设置的密码url最难MySQL8的固定模板jdbc:mysql://localhost:3306/数据库名 ?useSSLfalseserverTimezoneAsia/Shanghai然后课堂学习场景下推荐使用下面这个版本自己跑代码稳不容易报错jdbc:mysql://127.0.0.1:3306/数据库名 ?characterEncodingutf8 allowPublicKeyRetrievaltrueuseSSLfalse serverTimezoneAsia/Shanghai释义看关键需要的改的那部分就行比如数据库名、密码啥的jdbc:mysql://固定前缀代表用JDBC连接mysql127.0.0.1和localhost等效代表本机数据库如果数据库在别的电脑改成对方IP3306MySQL默认端口号一般不用改characterEncodingutf8防止中文乱码加上​allowPublicKeyRetrievaltrue作用允许客户端拿到服务端的公钥做加密登录。本地学习必加避开公钥报错但是这个参数只适合本地开发学习生产环境不要开不安全​useSSLfalse本地练习关闭SSL少报错serverTimezoneAsia/ShanghaiMySQL8强制要求不加大概率抛时区异常创建Statement用来接收SQL语句并执行SQL// 3创建Statement对象Statement statementconnection.createStatement();补充使用了createStatement()方法它有个返回值我们手动写了Statement statement来接收返回值我们可以在方法后面使用类型推断.var如connection.createStatement().varidea就可以帮我们自动补全前面完整的接收类型Statement statement connection.createStatement();只要方法有返回值不是void就可以用这个后缀补全这是idea的编辑器快捷键不属于Java语法像之前的输出快捷键.sout一样定义SQL语句并执行SQL获得结果集//先处理一下输入System.out.println(请输入学生姓名);Scanner scannernew Scanner(System.in);// 接收用户的输入String namescanner.next();//定义SQL并执行SQL语句SQL会以字符串的形式传入参数Stringsqlselect student_id, sn, name, mail, class_id from student where name name;//执行SQL获取查询结果ResultSet resultSetstatement.executeQuery(sql);//注意//executeQuery()用来执行select查询语句的返回值ResultSet—结果集//executeUpdate()用来执行update、delete、 insert这些修改语句的返回值int代表受影响的行数多少条记录被改动//execute()通用方法任何SQL都能跑返回值boolean//true这条SQL返回了结果集是select查询SQL//false没有结果集增删改返回受影响行数你需要额外调用getUpdateCount()方法拿行数/* 不推荐一直只用execute()代码可读性差 无法显式反映SQL类型 它也不像其他两个有异常校验 不确定SQL类型动态SQL才选用万能的execute() */解释一下SQL语句的构成由三个字符串构成select...name 和namename是个变量最终会是一个字符串和剩下的一个单引号这三个字符串在Java语法里被双引号包着我们用加号把这三个字符串拼接起来了最终才会变成一个总的字符串SQL语句select student_id, sn, name, mail, class_id from student where name 张三执行查询SQL之后就会得到相应结果集处理结果集对结果集进行遍历获取数据可以看到上面的结果集是一行一行的数据我们可以使用ResultSet的next()方法来遍历一个个的数据行resultSet.next()和迭代器 Iterator的hasNext()类似resultSet.next()指针先向下移动一行然后判断移动之后这一行有没有数据有就返回 true 没有就返回 falseiterator.hasNext() 只判断指针下面一行有没有数据不移动指针需要再调用iterator.next() 来拿数据while(resultSet.next()){//获取这一行的学生信息long stuIdresultSet.getLong(1);String stuSnresultSet.getString(2);String stuNameresultSet.getString(3);String stuMailresultSet.getString(4);long classIdresultSet.getLong(5);System.out.println(MessageFormat.format(学生编号{0}, 学号{1}, 学生姓名{2}, 邮箱{3}, 班级编号{4},stuName,stuMail,classId));}ResultSet的get方法ResultSet的get方法有两种形式的参数①填序号也就是上面代码中的用法指定列的序号且列的序号从 1 开始不是从0开始了为了便捷可以使用该中形式的参数②填字符串select里写的列别名/列原名如resultSet.getInt(student_id);指定列推荐可读性好MessageFormat.format()MessageFormat.format()可以把一些参数进行格式化输出为一个字符串把该字符串返回String 结果MessageFormat.format(字符串模板,参数1,参数2,参数3...);字符串模板中包含了{0}、{1}、{2}…这样形式的序号分别代表了参数1、参数2、参数3通过这样的方式把参数替换到给定的字符串模板中达到将这些任意类型的参数格式化输出为字符串的目的依次释放资源关闭连接以上的使用这些方法其实都会抛异常在idea里用快捷方式自动生成try-catch语块处理一下就行了不过这样一来前面的代码就都自然会放到try{...}语块里了那像Statement statement...这样的定义就不要放到try{...}语块里了放在外面定义在里面使用不然像 statement 这样的对象的作用域就只局限于try{...}语块里了在finally{}语块里就没法去释放这些对象占用的资源了在finally语块中按照逆序的顺序释放资源关闭连接上面的这个demo01版本依然存在一些问题还不具备工业级写法的雏形下面将使用demo02版本来逐个解决最核心的「连接性能」和「SQL安全」两大问题也是后续 MyBatis、连接池等技术的底层基础问题①DriverManager 直连数据库性能差、难维护改进引入 DataSource数据源 模式demo01 的问题每次调用DriverManager.getConnection()都会和数据库新建一条物理连接一条TCP网络连接用完就销毁频繁创建销毁非常耗性能连接参数url、账号、密码散落在代码里多处使用就要重复写修改维护麻烦​没有连接复用、连接数控制的能力原生 DriverManager.getConnection() 每次都新建TCP物理连接用完直接关闭不会保存起来重复使用也没法限制最多同时开多少条连接所以用它只能做简单演示企业开发完全不用生产环境要用连接池HikariCP等把多个连接维护到一个池子里有需要就捞几个出来用用完放回去下次需要还可以用解决这两个问题demo02 的解决方法MysqlDataSource 统一配置连接参数先创建 MySQL 提供的数据源实现类对象把 url、用户名、密码集中配置进去MysqlDataSource mysqlDataSourcenew MysqlDataSource();mysqlDataSource.setURL(...);mysqlDataSource.setUser(root);mysqlDataSource.setPassword(123456);面向标准接口 DataSource用 JDBC 标准接口 DataSource 接收对象DataSource dataSourcemysqlDataSource;然后再从数据源获取连接connectiondataSource.getConnection();用法和 DriverManager 一致但连接由数据源统一管理、支持复用是企业开发的标准写法而且还顺带省略了 Class.forName()JDBC 4.0 之后驱动 jar 支持 SPI 自动注册机制DataSource 会自动发现并加载驱动不用手动写 Class.forName 加载驱动了。问题②Statement 拼接SQL用加号把SQL和变量拼接为一个完整字符串有注入漏洞且效率低改进替换为 PreparedStatement预编译语句demo01 的问题SQL注入漏洞最致命直接用字符串拼接用户输入用户输入 or 11就能篡改SQL逻辑泄露全表数据攻击者只要输入 or 11按之前拼接SQL方法整个SQL就会变成select * from user where name or 11 or 11 11 永远是true or 代表只要一边成立整个where条件就成立。这样就相当于 where 真 数据库直接返回整张表所有用户数据直接绕过登录执行效率低普通 Statement 每次执行都要让数据库重新编译SQL、生成执行计划重复执行相似SQL时浪费资源demo02 的解决方法?占位符写法不拼接字符串SQL 里用?代替参数位置提前写死SQL结构从根源避免拼接篡改Stringsqlselect student_id, sn, name, mail, class_id from student where name ?;预编译SQL创建对象时就把SQL发给数据库预编译、缓存执行计划statementconnection.prepareStatement(sql);使用Statement的set方法来为占位符?赋值用 setString、setLong 等方法给占位符赋值占位符序号从1开始statement.setString(1,name);底层会自动转义特殊字符彻底杜绝SQL注入同时自动处理单引号不会出现拼接导致的语法错误执行时就不用再传SQL了resultSetstatement.executeQuery();因为SQL已经预编译存在对象里了直接执行即可以上就是demo02的完整优化最后其实本章虽然学习了JDBC的底层使用但业务代码确实几乎没人手写原生JDBC企业开发用框架MyBatis、MyBatis-Plus、HikariCP连接池框架帮我们把这些重复、繁琐、容易出错的JDBC样板代码全部封装藏起来了。我们写MyBatis的时候只写SQL不用自己写 Connection 、 PreparedStatement 、关闭资源等等…那为什么还要学看懂框架底层这样出问题了我们能排查且这些底层原理也是面试高频框架只是封装底层和数据库通信依然是JDBC这套API排查问题还得靠对底层实现的理解本节学的DataSource数据源、连接池、预编译、SQL注入、事务、ResultSet、驱动… 这些概念全部源自JDBC规范。那么后面学Spring事务、连接池全部复用同一套概念所以先学JDBC后面学框架会更顺滑