2020-08-02 Mysql数据库索引初识、备份、设计原则、JDBC连接、SQL注入、PreparedStatement对象使用、事务处理、连接池

------------------------索引----------------------

定义：帮助MYSQL高效获取数据的数据结构
----------主键索引----------primary key [有唯一标识]
----------唯一索引----------unique key [避免重复的列出现，值可重复]
----------常规索引----------index/key [默认的，用index、key来设置]
----------全文索引----------fulltext [特定数据库引擎下才有，快速定位数据]

在创建表的时候，索引都是放在建表语句最下面

SHOW INDEX FROM TableName

已有表，增加索引：
alter table school.tablename ADD fulltext index indexname(studentname)

索引原则
索引不是越多越好
不要对经常变动的数据加数据
小数据量不需要加索引
索引一般加在常用查询的字段

索引的数据结构
https://blog.codinglabs.org/articles/theory-of-mysql-index.html

-----------------------Mysql的权限管理和备份--------------------

mysql.user表
修改密码：
当前:set PASSWORD = PASSWORD(‘123456’);
选择: set FOR wangzheng = PASSWORD(‘123456’);

数据库备份
备份方式：
一)、拷贝物理文件：data文件夹导出
二)、可视化工具中，手动导出
三)、mysqldump命令行导出：
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

navicat怎么把建表语句、数据插入语句全部导出
--------------------------- 转储SQL文件+结构与数据 -------------------------

-----------------------数据库的设计---------------------

三大范式：[信息重复、更新异常、插入异常、删除异常]
每一列都不可分
满足不可分的前提下，每张表只描述一个事情
每一列与主键直接相关，不能间接相关

----------------------------JDBC---------------------------

JAVA操作数据库的规范
java.sql
javax.sql
导入数据库驱动

第一个JDBC
导入驱动
增加lib
add as a library

package com.wang.lesson01;

import javax.sql.StatementEvent;
import java.sql.*;

public class JDBCFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动,固定语法
        Class.forName("com.mysql.jdbc.Driver");
        //用户信息和URL
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        //这里要注意：userSLL如果用的true，系统会报错
        String username = "root";
        String password = "123456";
        //连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url,username,password);
        //创建执行SQL的对象
        Statement statement = connection.createStatement();
        //执行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("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
            System.out.println("=======================================");
        }
        //释放连接

        resultSet.close();
        statement.close();
        connection.close();
    }
}

connection代表了数据库对象
数据库自动自动提交
事务提交/回滚。这些功能都可以通过connection.commit/rollback等直接实现

statement就是执行sql对象的类
PrapareStatement也是执行sql对象的类

Statement statement = connection.createStatement();
statement.executeQuery("");
statement.execute("");
statement.executeUpdate("");

ResultSet就是封装了查询结果

resultSet.getObject(""); 		//如果不知道查询结果是何种类型的情况下使用

resultSet.getString("");		//知道返回结果的情况下使用
resultSet.getInt("");
resultSet.getDate("");

遍历，next()

---------------单独写工具类来进行对JDBC的公共参数的封装----------

工具类代码：

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 (Exception e) {
            e.printStackTrace();
        }

    }

    //获取连接
    public static Connection getConnect() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    //释放连接资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=******

测试类

public class TestUtils {
    public static void main(String[] args) {
        Connection connection = null ;
        Statement statement = null ;
        ResultSet resultSet = null ;

        try {
             connection = JdbcUtils.getConnect();
             statement = connection.createStatement();
             String sql = "insert into users (`id`,`NAME`,`PASSWORD`,email,birthday) values (5,'梁志斌','20030707','12345678@qq.com','2020-08-02');";
             int i = statement.executeUpdate(sql);
             if (i>0){
                 System.out.println("插入成功");
             }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

SQL注入的问题：
以用户登录为例：在username和password进行适当的SQL语句分析，试图改变原本SQL逻辑，以’or 1=1’等方式强制使SQL执行并返回结果得以实现后续登录效果等

PreparedStatement对象
可以防止SQL注入，并且效率更高

public class TestDemo {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null ;
        ResultSet resultSet = null ;

        try {
            connection = JdbcUtils.getConnect();
            //使用问号占位符
            String sql = "insert into users (`id`,`NAME`,`PASSWORD`,email,birthday) values (?,?,?,?,?);";
            preparedStatement = connection.prepareStatement(sql);//预编译sql，不执行
            //手动给占位符赋值
            preparedStatement.setInt(1,6);
            preparedStatement.setString(2,"卢崛");
            preparedStatement.setString(3,"123321");
            preparedStatement.setString(4,"12312312@qq.com");
            System.out.println((new Date().getTime())/2+"--------------------------");
            System.out.println(new Date().UTC(2019,12,5,12,1,3));
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));

            int i = 0;
            i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("preparedstatement应用成功，数据已插入");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

------------------------怎么用IDEA连接数据库--------------------------

修改mysql的时区，记得flush privileges;否则不会生效

进入后，记得在Schemas中修改一下所查看的表，这样才能看到具体的业务表

后续加紧使用联系

--------------------------------------事务--------------------------

原子、一致、隔离、持久
脏读、不可重复读、虚读(幻读)

public class TestTransFormation {
    public static void main(String[] args) {
        Connection connection = null ;
        PreparedStatement preparedStatement = null ;
        ResultSet resultSet = null ;

        try {
            connection = JdbcUtils.getConnect();
            //关闭MYSQL自动提交,意味着事务的开始第一步
            connection.setAutoCommit(false);
            //这里我没有建表，所以SQL暂时不执行了
            String sql1 = "" ;
            preparedStatement = connection.prepareStatement(sql1);
            String sql2 = "" ;
            preparedStatement = connection.prepareStatement(sql2);

            connection.commit();
            //这里注意，当使用了commit命令提交事务之后，数据库会自己恢复到自动提交的状态，不用刻意去开启自动提交了，同理回滚也是
        } catch (SQLException e) {
            try {
                //如果失败就回滚事务，其实这里不写也可以，事务中出现异常会自动回滚
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            //关闭JDBC各连接，防止占用内存
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

-----------------------数据库连接池----------------------

创建连接–使用–释放十分浪费内存资源，因此提前准备好一些连接，需要使用时直接取用

实现类：DataSource

工具类TestUtiles：

public class JdbcUtils_DBCP {
    private static DataSource  dataSource= null ;
    static {
        try{
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);
            //创建数据源
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnect() throws SQLException {
        return dataSource.getConnection();   //从数据源中获取连接
    }

    //释放连接资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

实现类：

public class TestUtils {
    public static void main(String[] args) {
        Connection connection = null ;
        Statement statement = null ;
        ResultSet resultSet = null ;

        try {
             connection = JdbcUtils_DBCP.getConnect();
             statement = connection.createStatement();
             String sql = "insert into users (`id`,`NAME`,`PASSWORD`,email,birthday) values (5,'梁志斌','20030707','12345678@qq.com','2020-08-02');";
             int i = statement.executeUpdate(sql);
             if (i>0){
                 System.out.println("插入成功");
             }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            JdbcUtils_DBCP.release(connection,statement,resultSet);
        }
    }
}

=====区别只在于数据库连接方式的不同=