> 文档中心 > java开发工程师入门第二阶段(04-mysql数据库)

java开发工程师入门第二阶段(04-mysql数据库)

文章目录

        • 一.视图View
          • 1.概念
          • 2.测试
        • 二.数据库设计的三范式
          • 1.概述
          • 2.分类
          • 3.总结
        • 三.SQL优化
        • 四:JDBC
          • 1.概念
          • 2.使用步骤
          • 3.idea 创建项目并导入jar包
          • 4.案例
          • 5.SQL攻击注入
          • 6.SQL注入的解决方案
          • 7.优化: 提供jdbc的工具类
          • 8.使用工具类(用新的传输器新增)

一.视图View

1.概念

可视化的表,视图当做是一个特殊的表,是指,把sql执行的结果,直接缓存到了视图中。
下次还要发起相同的sql,直接查视图。现在用的少,了解即可.
使用: 1,创建视图 2,使用视图

2.测试
create view 视图名 as  SQL语句;select * from 视图名;#视图:就是一个特殊的表,缓存上次的查询结果#好处是提高了SQL的复用率,坏处是占内存无法被优化 #1.创建视图CREATE VIEW emp_view ASSELECT * FROM emp WHERE ename LIKE '%a%' #模糊查询,名字里包含a的#2.使用视图SELECT * FROM emp_view

二.数据库设计的三范式

1.概述

简言之就是,数据库设计对数据的存储性能,还有开发人员对数据的操作都有莫大的关系。所以建立科学的,规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式,也是作为数据库 设计的一些规则.
关系型数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。范式越高,冗余最低,一般到三范式,再往上,表越多,可能导致查询效率下降。所以有时为了提高运行效率,可以让数据冗余.

2.分类

1NF的定义为:符合1NF的关系中的每个属性都不可再分

2NF在1NF的基础之上,消除了非主属性对于码的部分函数依赖,也就是说,表里的每个字段都要依赖于主键

3NF在2NF的基础之上,消除了非主属性对于码的传递函数依赖

3.总结

​ 三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求跟性能,需求>性能>表结构。所以不能一味的去追求范式建立数据库。

三.SQL优化

  • 查询SQL尽量不要使用select *,而是具体字段
  • 避免在where子句中使用or来连接条件
  • 使用varchar代替char
  • 尽量使用数值替代字符串类型
  • 创建name字段的索引
  • 索引不宜太多,一般5个以内
  • 索引不适合建在有大量重复数据的字段上
  • 避免在where子句中使用!=或操作符
  • 批量插入性能提升
  • 复合索引最左特性
  • 不要有超过5个以上的表连接
  • inner join 、left join、right join,优先使用inner join

四:JDBC

1.概念

我们学习了数据库,数据库实现了数据的持久化,但我们最终要在程序里处理数据啊,那java代码中怎么去访问数据库读写数据呢?

这就要用到sun公司设定的一套数据库标准了,这套标准就是JDBC(Java Database Connectivity)。但它只是规范,不做具体实现。于是数据库厂商又根据JDBC标准,实现自家的驱动Driver。如:mysql驱动com.mysql.cj.jdbc.Driver,Oracle的驱动oracle.jdbc.OracleDriver。有了这套解决方案,java就可以访问数据库中的数据了。

Java中提倡面向接口开发,而最经典的接口设计莫过于JDBC数据库接口。

2.使用步骤

导入jar包(丰富的工具类)

获取和数据库的连接(用户名、密码)

通过程序执行SQL

通过程序处理结果

3.idea 创建项目并导入jar包
  • 创建stage2 Java工程
  • 创建lib目录,拷贝驱动objbc6-11.1.0.7.0到lib目录下
  • 项目引用这个外部jar包
4.案例
package cn.tedu.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;//利用jdbc,操作数据库//需求,查询部门表的所有数据public class Test1 {    public static void main(String[] args) throws Exception { //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取数据库的连接 /*String url="协议://数据库的服务器的IP地址:端口号/数据库名*/ String url= "jdbc:mysql://localhost:3306/cgb211101"; Connection c = DriverManager.getConnection(url,"root","root"); //3.获取传输器 Statement s = c.createStatement(); //4.执行SQL ResultSet r = s.executeQuery("select * from dept");//执行查询的SQL System.out.println("java程序与数据库连接成功!!!"); //5.处理结果--遍历结果 //next()从来判断,只要r里有数据就返回true,没有数据就返回false while (r.next()){     //获取数据getXxx()     int d1= r.getInt(1);//获取第一列的一个整数值     String d2= r.getString(2);//获取第二列的一个整数值     String d3= r.getString(3);//列的一个整数值     System.out.println(d1+d2+d3); } //6.关闭资源 r.close();//关闭结果集ResultSet s.close();//关闭传输器Statement c.close();//关闭连接Connection    }}
5.SQL攻击注入
/*自己准备user2表(id/name/password),准备数据    CREATE TABLE `user2` (   `id` int(11)  PRIMARY KEY  auto_increment,   `name` varchar(10) default NULL,   `password` varchar(10) default NULL    ) ; *///需求:利用jdbc,根据用户名和密码查询cgb2104库里的user表//SQL注入攻击问题private static void login() {    try{ Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql:///cgb2104?characterEncoding=utf8"; Connection conn = DriverManager.getConnection(url, "root", "root"); Statement st = conn.createStatement();// String sql ="select * from user2 where name='jack' and password='123456'";//写死了 String user = new Scanner(System.in).nextLine();//用户输入jack'# String pwd = new Scanner(System.in).nextLine(); //SQL注入攻击问题:本质上是因为SQL语句中出现了特殊符号#,改变了SQL语义String sql ="select * from user2 where name='"+user+"' and password='"+pwd+"'"; ResultSet rs = st.executeQuery(sql);//执行查询的SQL,返回结果集 if(rs.next()){     System.out.println("登录成功~"); }else{     System.out.println("登录失败~"); } st.close(); conn.close();    }catch(Exception e){ e.printStackTrace();//有异常,直接打印异常信息 //System.out.println("执行失败。。。");//上线    }}
6.SQL注入的解决方案
package cn.tedu.jdbc;import java.sql.*;import java.util.Scanner;public class Test7 {    public static void main(String[] args) throws Exception { Class.forName("com.mysql.jdbc.Driver"); String url= "jdbc:mysql://localhost:3306/cgb211101"; Connection c = DriverManager.getConnection(url,"root","root"); System.out.println("请您输入账号:"); String s1 = new Scanner(System.in).nextLine(); System.out.println("请您输入密码:"); String s2 = new Scanner(System.in).nextLine(); //Statement s = c.createStatement(); //String sql="select * from user where name='"+s1+"' and pwd='"+s2+"'"; //动态拼接SQL语句 //新的传输器,执行的SQL有新写法--sql骨架 String sql= "select * from user where name=? and pwd=?"; //获取新的传输器--安全/高效 PreparedStatement s = c.prepareStatement(sql); s.setString(1,s1);//给第一个问号?,设置s1的值 s.setString(2,s2);//给第二个问号?,设置s2的值 ResultSet r = s.executeQuery(); //处理结果集(根据用户名和密码去查,只会查出来一条记录) if (r.next()){//如果查到了数据,才表示数据库里面有这个用户信息     System.out.println("登录成功"); }else {//如果没有查到数据信息     System.out.println("请重新输入..."); } //释放资源 r.close(); s.close(); c.close(); //问题:当用户输入特殊值:小张'#时,,甚至不需要密码也能登录 //原因:#在SQL中表示注释的意思,相当于后面的条件被注释掉了 //select * from user where name='jack'#' and pad='123456' //现象叫SQL攻击/SQL注入,本质上就是因为SQL语句中出现了特殊符号# //导致了.SQL语义发生改变 //createStatement传输器不安全,低效,改为prepareStatement:把特殊符号当做普通的文本符号在使用,没有注释的意思了    }}
7.优化: 提供jdbc的工具类
package cn.tedu.jdbc;import java.sql.*;//充当了jdbc的工具类,抽取一些共性代码public class JDBCUtils {    /**     * 释放资源     * @param r 结果集     * @param s 传输器     * @param c 连接器     */    static public void close(ResultSet r, PreparedStatement s,Connection c){ if(r != null){//防止了空指针异常     try {  r.close();     } catch (SQLException throwables) {  throwables.printStackTrace();     }finally {//就是怕close()执行失败导致发生了异常,进行了catch  r = null;//手动置空,等着GC进行垃圾回收     } } if(s != null) {//防止空指针异常     try {  s.close();     } catch (SQLException throwables) {  throwables.printStackTrace();     }finally {//就是怕close()执行失败导致发生了异常,进行了catch  s = null;//手动置空,等着GC进行垃圾回收     } } if(c != null) {//防止空指针异常     try {  c.close();     } catch (SQLException throwables) {  throwables.printStackTrace();     }finally {//就是怕close()执行失败导致发生了异常,进行了catch  c = null;//手动置空,等着GC进行垃圾回收     } }    }    /**     * 获取数据库的连接     * @return 将给调用者返回一个和数据库连接的对象Connection     * @throws Exception     * static:保证资源在内存中,贮存的时间长.只会加载一次节省内存     * public:工具类可以被所有人使用,最大的访问权限方便调用来调用     */    static public Connection get() throws Exception{ //1,注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2,获取连接 String url="jdbc:mysql://localhost:3306/cgb211101?characterEncoding=utf8"; Connection c = DriverManager.getConnection(url, "root", "root"); //把获取到的数据库的连接,返回给调用者 return c;    }}
8.使用工具类(用新的传输器新增)
package cn.tedu.jdbc;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;//利用新的传输器结合着工具类,新增一个用户信息public class Test6 {    public static void main(String[] args) {//扩大变量的作用范围:为了让try  catch  finally都能用Connection c = null;PreparedStatement p = null;try {     //1,利用工具类,来获取数据库的连接     c = JDBCUtils.get();     //2,获取传输器,执行SQL     String sql="insert into user values(null,?,?)";     p = c.prepareStatement(sql);     //给SQL绑定参数     p.setObject(1,"jerry");     p.setObject(2,"123");     //3,执行SQL     p.executeUpdate();//执行增删改的SQL,返回一个影响行数(通常不处理)}catch (Exception e){    System.out.println("数据插入失败!!");}finally {//保证一定会被执行的代码    //利用工具类close完成释放资源(新增业务,没有结果集,传入null就可以了)    JDBCUtils.close(null,p,c);}    }}