MySQL函数和JDBC

f相关函数

• 1.if(expr1,expr2,expr3) 如果 expr1 是TRUE，则 IF()的返回值为expr2; 否则返回值则为 expr3。if() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

  • 需求: 查询姓名,年龄,性别,如果性别为1,就显示1,否则就显示0

    select uname,age,if(sex=1,1,0) from t_user;
    select uname,age,if(sex,1,0) from t_user;

• 2.ifnull(expr1,expr2) 假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。ifnull()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

  • 需求: 查询姓名,年龄,性别,如果性别为null,就显示2

    select uname,age,ifnull(sex,2) from t_user;

• 字符串函数

  ​​​​​​

  1.concat(str1, str2, ...)   字符串连接函数，可以将多个字符串进行连接

  2.concat_ws(separator, str1, str2, ...) 可以指定间隔符将多个字符串进行连接；

  3.upper(str) 得到str的大写形式

  4.lower(str)  得到str的小写形式

  5.trim(str)  将str两边的空白符移除

  6.substr(str,pos)、substring(str,pos)  ,substr(str,pos,len)、substring(str,pos,len) 截取字符串

  时间日期函数

  current_date()  获取当前日期，如 2019-10-18
  current_time()  获取当前时：分：秒，如：15:36:11
  now()  获取当前的日期和时间，如：2019-10-18 15:37:17

      数值函数

      abs(x) 获取数值x的绝对值
      ceil(x)  向上取整，获取不小于x的整数值
      floor(x) 向下取整，获取不大于x的整数值
       pow(x, y) 获取x的y次幂
       rand()  获取一个0-1之间的随机浮点数

JDBC入门

JDBC(java database connectivity): sun公司为了简化和统一java连接数据库,定义的一套规范(类,接口).

API-Drivermanager类

public static void registerDriver(Driver driver) ;注册驱动

com.mysql.jdbc.Driver类:
static {
    try {
        java.sql.DriverManager.registerDriver(new Driver());
    } catch (SQLException E) {
        throw new RuntimeException("Can't register driver!");
    }
}

<br class="Apple-interchange-newline"><div></div>

Class.forName("com.mysql.jdbc.Driver");  // 当获取Driver类的字节码对象,就会加载Driver类,执行静态代码块,执行静态代码块就会注册驱动

API-Connection接口

Statement createStatement();创建执行sql语句对象
    
PreparedStatement prepareStatement(String sql) 预编译sql语句,返回预编译sql语句对象
PreparedStatement继承statement接口
    
void setAutoCommit(boolean autoCommit) false--手动开启事务(start transaction)
void commit();提交事务
void rollback();回滚事务

API-Statement接口

1. 概述

   接口的实现在数据库驱动中. 用来==操作sql语句(增删改查)==，并返回sql语句的结果

2. 作用

   ResultSet executeQuery(String sql) 根据查询语句返回结果集。只能执行select语句。

   int executeUpdate(String sql) 根据执行的DML（insert update delete）语句，返回受影响的行数。

   boolean execute(String sql) 此方法可以执行任意sql语句。返回boolean值. 【了解】

   true: 执行select语句

   false: 执行insert, delete,update语句

1. • boolean next();判断游标后面是否有记录可以取出来,并且将光标从当前位置向下移动一行

   • int getInt(int colIndex)以int形式获取ResultSet结果集当前行指定列号值

   • int getInt(String colLabel)以int形式获取ResultSet结果集当前行指定列名值

   • float getFloat(int colIndex)以float形式获取ResultSet结果集当前行指定列号值

   • float getFloat(String colLabel)以float形式获取ResultSet结果集当前行指定列名值

   • String getString(int colIndex)以String 形式获取ResultSet结果集当前行指定列号值

   • String getString(String colLabel)以String形式获取ResultSet结果集当前行指定列名值

   • Date getDate(int columnIndex); 以Date 形式获取ResultSet结果集当前行指定列号值

   • Date getDate(String columnName);以Date形式获取ResultSet结果集当前行指定列名值

   • Object getObject(String columnLabel)以Object形式获取ResultSet结果集当前行指定列名值

   • ...

   • void close()关闭ResultSet 对象

API-总结

1. DriverManager:驱动管理器

   • 注册驱动

   • 获得连接

2. Connection: 代表连接对象

   • 创建执行sql语句对象

   • 创建预编译sql语句对象

   • 事务操作

3. Statement: 执行sql语句对象

   • 执行查询 Result executeQuery(String sql) 返回结果集

   • 执行增删改 int excuteUpdate(String sql) 返回受影响的行数

4. ResultSet: 结果集

   • boolen next() 每调用一次, 光标就向下移动一行; 这个行有数据, 返回true; 没有数据, 返回false

   • get类型(String 列名); 根据列名 获得当前列的数据

JDBC操作数据库练习

需求一：查询所有用户信息

public class Test1_查询所有 {
    public static void main(String[] args) throws Exception{
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2.获得连接
        String url = "jdbc:mysql://localhost:3306/day19_1";
        Connection connection = DriverManager.getConnection(url, "root", "root");

        // 3.创建执行sql语句对象
        Statement statement = connection.createStatement();

        // 4.执行sql语句,处理结果
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
        // 创建ArrayList集合,限制元素的类型为User
        ArrayList<User> list = new ArrayList<>();
        while (resultSet.next()) {
            // 取值
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");
            // 创建User对象,封装数据
            User user = new User(id,username,password,nickname);
            // 添加对象
            list.add(user);
        }
        // 5.释放资源
        resultSet.close();
        statement.close();
        connection.close();

        for (User user : list) {
            System.out.println(user);
        }
    }
}

需求二：根据id查询用户信息

public class Test2_根据id查询用户信息 {
    public static void main(String[] args) throws Exception{
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2.获得连接
        String url = "jdbc:mysql://localhost:3306/day19_1";
        Connection connection = DriverManager.getConnection(url, "root", "root");

        // 3.创建执行sql语句对象
        Statement statement = connection.createStatement();

        // 4.执行sql语句,处理结果
        String sql = "select * from user where id = 1";
        ResultSet resultSet = statement.executeQuery(sql);
        // 定义User变量
        User user = null;
        // 循环取值
        while (resultSet.next()) {
            // 取值
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");

            // 创建User对象
            user = new User(id,username,password,nickname);
        }

        // 5.释放资源
        resultSet.close();
        statement.close();
        connection.close();

        if (user ==null){
            System.out.println("没查到");
        }else{
            System.out.println(user);
        }
    }
}

需求三：新增用户信息

public class Test3_新增用户信息 {
    public static void main(String[] args) throws Exception {
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2.获得连接
        String url = "jdbc:mysql://localhost:3306/day19_1";
        Connection connection = DriverManager.getConnection(url, "root", "root");

        // 3.创建执行sql语句对象
        Statement statement = connection.createStatement();

        // 4.执行sql语句,处理结果
        String sql = "insert into user values(null,'zl','123456','老赵')";
        int rows = statement.executeUpdate(sql);
        System.out.println("受影响的行数:" + rows);

        // 5.释放资源
        statement.close();
        connection.close();
    }
}

需求四：修改用户信息

/**
 * Created by PengZhiLin on 2021/8/23 10:50
 */
public class Test4_修改用户信息 {
    public static void main(String[] args) throws Exception {
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2.获得连接
        String url = "jdbc:mysql://localhost:3306/day19_1";
        Connection connection = DriverManager.getConnection(url, "root", "root");

        // 3.创建执行sql语句对象
        Statement statement = connection.createStatement();

        // 4.执行sql语句,处理结果
        String sql = "update user set password = '123456' where id = 3";
        int rows = statement.executeUpdate(sql);
        System.out.println("受影响的行数:" + rows);

        // 5.释放资源
        statement.close();
        connection.close();
    }
}

需求五：删除用户信息

public class Test5_删除用户信息 {
    public static void main(String[] args) throws Exception {
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2.获得连接
        String url = "jdbc:mysql://localhost:3306/day19_1";
        Connection connection = DriverManager.getConnection(url, "root", "root");

        // 3.创建执行sql语句对象
        Statement statement = connection.createStatement();

        // 4.执行sql语句,处理结果
        String sql = "delete from user where username = 'wangwu'";
        int rows = statement.executeUpdate(sql);
        System.out.println("受影响的行数:" + rows);

        // 5.释放资源
        statement.close();
        connection.close();
    }
}
JDBC工具类抽取

配置文件

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day19_1
username=root
password=root

工具类

public class JDBCUtils {

    public static String driverClass;
    public static String url;
    public static String username;
    public static String password;

    static {
        try {
            // 1.创建Properties对象
            Properties pro = new Properties();

            // 2.调用load方法加载配置文件
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            pro.load(is);

            // 3.取值
             driverClass = pro.getProperty("driverClass");
             url = pro.getProperty("url");
             username = pro.getProperty("username");
             password = pro.getProperty("password");

            // 4.注册驱动
            Class.forName(driverClass);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获得连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        // 获得连接
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    /**
     * 释放资源
     *
     * @param resultSet
     * @param statement
     * @param connection
     */
    public static void release(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
测试工具类:

public class Test1_查询所有 {
    public static void main(String[] args) throws Exception {
        // 1.注册驱动
        // 2.获得连接
        Connection connection = JDBCUtils.getConnection();

        // 3.创建执行sql语句对象
        Statement statement = connection.createStatement();

        // 4.执行sql语句,处理结果
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
        // 创建ArrayList集合,限制元素的类型为User
        ArrayList<User> list = new ArrayList<>();
        while (resultSet.next()) {
            // 取值
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");
            // 创建User对象,封装数据
            User user = new User(id, username, password, nickname);
            // 添加对象
            list.add(user);
        }
        // 5.释放资源
        JDBCUtils.release(resultSet,statement,connection);

        for (User user : list) {
            System.out.println(user);
        }
    }
}

登录案例

1.需求

public class Test {
    public static void main(String[] args) throws Exception {
        //- 用户输入用户名和密码
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = sc.nextLine();

        System.out.println("请输入密码:");
        String password = sc.nextLine();

        //- 注册驱动,获得连接
        Connection connection = JDBCUtils.getConnection();

        //- 创建执行sql语句对象
        Statement statement = connection.createStatement();

        //- 执行sql语句,处理结果(判断是否登录成功,其实就是判断User对象是否为null)
        String sql = "select * from user where username = '" + username + "' and password = '" + password + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        // 定义一个User变量
        User user = null;
        // 循环取值
        while (resultSet.next()) {
            // 创建对象,并封装结果
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setNickname(resultSet.getString("nickname"));
        }

        //- 释放资源
        JDBCUtils.release(resultSet, statement, connection);

        // 判断是否登录成功
        if (user == null) {
            System.out.println("登录失败!");
        } else {
            System.out.println("登录成功!");
        }
    }
}

演示SQL注入攻击

• 输入的用户名是对的,但输入的密码为: 123' or '1=1 发现直接登录成功---->sql注入漏洞

• 输入的用户名是不对的,但输入的密码为: 123' or '1=1 发现直接登录成功---->sql注入漏洞

登录中SQL注入问题分析和解决

输入的用户名: zs
输入的密码: 123' or '1=1
sql语句:
String sql = "select * from user where username = '" + username + "' and password = '" + password + "'";

sql拼接后分析:
select * from user where username = 'zs' and password = '123' or '1=1';
select * from user where username = 'zs' and password = '123' or true;
select * from user where true;
select * from user;

分析:
拼接之前sql语句的结构:  select * from user where username = '用户名' and password = '密码' 
拼接之后sql语句的结构:  select * from user where username = '用户名' and password = '密码'  or '1=1';

原因: 
    sql语句是做一个简单的字符串拼接,所以会把用户输入的密码中的or关键字注入到sql语句中,从而改变了sql语句的结构
        
解决方式: 
    固定sql语句的格式---->使用预编译sql语句对象,对sql语句的格式进行固定,哪怕后期拼接的内容里面含有关键字,也不会改变sql语句的格式

PreparedStatement接口: 继承Statement接口

• SQL语句中的参数使用?作为占位符 eg:select * from user where username = ? and password = ?

• 获得预编译sql语句对象: 使用Connection的方法

  • PreparedStatement prepareStatement(String sql) 预编译sql语句,得到预编译sql语句对象

• 为?占位符赋值的方法：setXXX(参数1,参数2);

  • XXX代表：数据类型----->列的类型

  • 参数1：?的位置编号(编号从1开始)

  • 参数2: 具体的值

• 执行sql语句:

  • 执行查询语句:

    • ResultSet executeQuery()根据查询语句返回结果集。只能执行select语句。

  • 执行增删改语句:

    • int executeUpdate() 根据执行的DML（insert update delete）语句，返回受影响的行数。

  • 注意: 预编译sql语句对象执行sql语句的方法不需要传入sql语句

使用PreparedStatement对象解决登录案例中的sql注入漏洞问题:

public class Test {
    public static void main(String[] args) throws Exception {
        //- 用户输入用户名和密码
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = sc.nextLine();

        System.out.println("请输入密码:");
        String password = sc.nextLine();

        //- 注册驱动,获得连接
        Connection connection = JDBCUtils.getConnection();

        //- 预编译sql语句,得到预编译对象
        String sql = "select * from user where username = ? and password = ?";
        PreparedStatement ps = connection.prepareStatement(sql);

        // 设置sql语句的参数
        ps.setString(1,username);
        ps.setString(2,password);

        // 执行sql语句,处理结果
        ResultSet resultSet = ps.executeQuery();

        // 定义一个User变量
        User user = null;
        // 循环取值
        while (resultSet.next()) {
            // 创建对象,并封装结果
            user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setNickname(resultSet.getString("nickname"));
        }

        //- 释放资源
        JDBCUtils.release(resultSet, ps, connection);

        // 判断是否登录成功
        if (user == null) {
            System.out.println("登录失败!");
        } else {
            System.out.println("登录成功!");
        }
    }
}

使用preparedStatement完成CRUD

public class Test1_增 {
    public static void main(String[] args)throws Exception {
        // 1.注册驱动,获得连接
        Connection connection = JDBCUtils.getConnection();

        // 2.预编译sql语句,得到预编译对象
        String sql = "insert into user values(null,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);

        // 3.设置sql语句参数
        ps.setString(1,"ww");
        ps.setString(2,"123456");
        ps.setString(3,"老王");

        // 4.执行sql语句,处理结果
        int rows = ps.executeUpdate();
        System.out.println("受影响的行数:"+rows);

        // 5.释放资源
        JDBCUtils.release(null,ps,connection);
    }
}

删

public class Test2_删 {
    public static void main(String[] args)throws Exception {
        // 1.注册驱动,获得连接
        Connection connection = JDBCUtils.getConnection();

        // 2.预编译sql语句,得到预编译对象
        String sql = "delete from user where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);

        // 3.设置sql语句参数
        ps.setInt(1,1);

        // 4.执行sql语句,处理结果
        int rows = ps.executeUpdate();
        System.out.println("受影响的行数:"+rows);

        // 5.释放资源
        JDBCUtils.release(null,ps,connection);
    }
}

改

public class Test3_改 {
    public static void main(String[] args)throws Exception {
        // 1.注册驱动,获得连接
        Connection connection = JDBCUtils.getConnection();

        // 2.预编译sql语句,得到预编译对象
        String sql = "update user set password = ? where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);

        // 3.设置sql语句参数
        ps.setString(1,"abcdef");
        ps.setInt(2,4);

        // 4.执行sql语句,处理结果
        int rows = ps.executeUpdate();
        System.out.println("受影响的行数:"+rows);

        // 5.释放资源
        JDBCUtils.release(null,ps,connection);
    }
}

查

public class Test4_查询所有记录 {
    public static void main(String[] args) throws Exception{
        // 1.注册驱动,获得连接
        Connection connection = JDBCUtils.getConnection();

        // 2.预编译sql语句,得到预编译对象
        String sql = "select * from user";
        PreparedStatement ps = connection.prepareStatement(sql);

        // 3.设置sql语句参数
        // 4.执行sql语句,处理结果
        ResultSet resultSet = ps.executeQuery();
        // 创建ArrayList集合,限制元素类型为User
        ArrayList<User> list = new ArrayList<>();
        // 取值
        while (resultSet.next()) {
            // 取值
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");
            // 创建User对象,封装数据
            User user = new User(id,username,password,nickname);
            // 添加对象
            list.add(user);
        }

        // 5.释放资源
        JDBCUtils.release(resultSet,ps,connection);

        for (User user : list) {
            System.out.println(user);
        }
    }
}

查询一条记录---->id为2的记录

public class Test5_查询一条记录 {
    public static void main(String[] args) throws Exception{
        // 1.注册驱动,获得连接
        Connection connection = JDBCUtils.getConnection();

        // 2.预编译sql语句,得到预编译对象
        String sql = "select * from user where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);

        // 3.设置sql语句参数
        ps.setInt(1,2);

        // 4.执行sql语句,处理结果
        ResultSet resultSet = ps.executeQuery();
        // 定义User变量
        User user = null;

        // 取值
        while (resultSet.next()) {
            // 取值
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String nickname = resultSet.getString("nickname");
            // 创建User对象,封装数据
            user = new User(id,username,password,nickname);

        }

        // 5.释放资源
        JDBCUtils.release(resultSet,ps,connection);

        if (user == null){
            System.out.println("没有查到");
        }else{
            System.out.println(user);
        }
    }
}

JDBC事务介绍

• 开启事务：setAutoCommit(boolean autoCommit); 参数为false，则开启事务。

• 提交事务：commit();

• 回滚事务：rollback();

public class Test {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            // 1.注册驱动,获得连接
            connection = JDBCUtils.getConnection();

            // 2.手动开启事务
            connection.setAutoCommit(false);

            // 3.预编译sql语句,得到预编译对象
            String sql = "update user set password = ? where nickname = ?";
            ps = connection.prepareStatement(sql);

            // 4.为sql语句设置参数
            ps.setString(1, "abcdef");
            ps.setString(2, "老王");

            // 5.执行sql语句,处理结果
            int rows = ps.executeUpdate();
            System.out.println("受影响的行数:" + rows);

            // 发生了异常
            int num = 1/0;

            // 6.没有异常,就提交事务
            connection.commit();

        } catch (Exception e) {
            // 6.有异常,就回滚事务
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            // 7.释放资源
            JDBCUtils.release(null, ps, connection);
        }
    }
}

转账案例

create table account(
    id int primary key auto_increment,
    name varchar(20),
    money double
);

insert into account values (null,'zs',1000);
insert into account values (null,'ls',1000);
insert into account values (null,'ww',1000);

分析

• 1.注册驱动,获得连接

• 2.开启事务

• 3.预编译sql语句,得到预编译对象

• 4.设置sql语句参数

• 5.执行sql语句,处理结果

• 6.提交事务或者回滚事务

• 7.释放资源

public class Test {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            // 1.注册驱动,获得连接
            connection = JDBCUtils.getConnection();

            // 2.开启事务
            connection.setAutoCommit(false);

            // 3.预编译sql语句,得到预编译对象
            String sql1 = "update account set money = money - ? where name = ?";
            String sql2 = "update account set money = money + ? where name = ?";
            ps1 = connection.prepareStatement(sql1);
            ps2 = connection.prepareStatement(sql2);

            // 4.设置sql语句参数
            ps1.setDouble(1, 100);
            ps1.setString(2, "zs");
            ps2.setDouble(1, 100);
            ps2.setString(2, "ls");

            // 5.执行sql语句,处理结果
            int rows1 = ps1.executeUpdate();
            int rows2 = ps2.executeUpdate();

            // 异常
            int num = 1/0;

            // 6.没有异常就提交事务
            if (rows1 > 0 && rows2 > 0){
                connection.commit();
            }else{
                connection.rollback();
            }

        } catch (Exception e) {
            // 6.有异常就回滚事务
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            // 7.释放资源
            JDBCUtils.release(null, ps1, connection);
            JDBCUtils.release(null, ps2, null);
        }

    }
}