MySQL（六）数据库用户管理及在IDEA中使用JDBC连接数据库（编写数据库工具类实现增删改查）

1.数据库用户管理

-- ================权限管理
-- 创建用户
CREATE USER xyj IDENTIFIED BY '123456'

-- 修改密码(修改当前用户密码)
SET PASSWORD =PASSWORD('123456')

-- 修改密码
SET PASSWORD FOR xyj = PASSWORD('123')

-- 重命名
RENAME USER xyj TO xyj1

-- 用户授权 all privileges 全部权限 库 表
-- all privileges 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO xyj1

-- 查询权限
SHOW GRANTS FOR xyj1  -- 查看指定用户的权限

SHOW GRANTS FOR root@localhost -- 查看root用户的权限

-- 撤销权限 
REVOKE ALL PRIVILEGES ON *.* FROM xyj1


-- 删除用户
DROP USER xyj1

授权注意点：GRANT ALL PRIVILEGES ON . TO xyj1

2.JDBC连接数据库

package com.xyj;

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=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2b8";
        String username = "root";
        String password = "123456";
        //连接成功 数据库对象 --connection 代表数据库(设置数据库自动提交 事物回滚)
        Connection connection = DriverManager.getConnection(url, username, password);

        //执行sql的对象 Statement(执行类) 执行sql的对象
        Statement statement = connection.createStatement();
        String sql = "SELECT * FROM users";
        ResultSet rs = statement.executeQuery(sql);
        //如果存在下一数据，就执行
        while (rs.next()){
            System.out.println("id="+rs.getObject("id")); //返回结果集rs.getObject，结果集中封装了我们全部查询出来的结果
            System.out.println("name="+rs.getObject("name"));
            System.out.println("password="+rs.getObject("password"));
            System.out.println("email="+rs.getObject("email"));
            System.out.println("birth="+rs.getObject("birthday"));
            System.out.println("------------------");
        }
        //先开放的 先释放
        rs.close();
        statement.close();
        connection.close();

    }
}

步骤：

加载驱动
连接数据库DriverManager
获取执行sql的对象statement
获得返回的结果集
释放连接

statement 执行sql的对象

        statement.executeQuery();  // 查询
        statement.execute(); //执行任何sql（响应慢）
        statement.executeUpdate(); //更新 插入 删除都用这个，返回一个受影响行数

ResultSet 查询结果集,封装了所有的查询结果

     //和数据库中的类型一一匹配
        resultSet.getObject(); // 不知道列类型的情况下使用
        // 如果知道列的类型就使用指定的类型
        resultSet.getString(); 
        resultSet.getDate();
        resultSet.getInt();
        resultSet.getDouble();

遍历指针

    resultSet.beforeFirst(); //移动到最前面
    resultSet.afterLast();  //移动到最后面
    resultSet.next()  // 移动到下一个
    resultSet.previous(); // 移动到上一个
    resultSet.absolute(); // 移动到指定行

3.编写数据库工具类实现增删改查

存放文件目录

1.编写工具类jdbcutils

package com.xyj;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    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 getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //释放连接资源
    public static void  release(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if(conn!=null) conn.close();

        if(st!=null) st.close();

        if(rs!=null) rs.close();
    }

}

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2b8
username=root
password=123456

2.增

package com.xyj;


import com.xyj.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {


    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        Statement statement=null;
        try {
            connection = JdbcUtils.getConnection(); //获取数据库链接
            statement = connection.createStatement();//获取sql执行对象
            String sql="INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES" +
                    "(4,'张三','123456','11111@qq.com','2020-01-01')";
            int i = statement.executeUpdate(sql);
            if(i>0) System.out.println("插入成功");

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

        }finally {
            JdbcUtils.release(connection,statement,null);
        }
    }
}

3.删

package com.xyj;

import com.xyj.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class testdelete {


    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        Statement statement=null;
        try {
            connection = JdbcUtils.getConnection(); //获取数据库链接
            statement = connection.createStatement();//获取sql执行对象
            String sql="DELETE from `users` WHERE id = 4";
            int i = statement.executeUpdate(sql);
            if(i>0) System.out.println("删除成功");

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

        }finally {
            JdbcUtils.release(connection,statement,null);
        }
    }
}

4.改

package com.xyj;
import com.xyj.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class testupdata {


    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        Statement statement=null;
        try {
            connection = JdbcUtils.getConnection(); //获取数据库链接
            statement = connection.createStatement();//获取sql执行对象
            String sql="UPDATE `users` SET `NAME`='李四',`PASSWORD`='4321',`email`='123456@qq.com' \n" +
                    "WHERE id>1  ";
            int i = statement.executeUpdate(sql);
            if(i>0) System.out.println("修改成功");

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

        }finally {
            JdbcUtils.release(connection,statement,null);
        }
    }
}

5.查

package com.xyj;


import com.xyj.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class testselect {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        Statement statement=null;
        ResultSet rs=null;
        try {
            //获取连接
            connection = JdbcUtils.getConnection();
            //获取sql对象
            statement = connection.createStatement();
            //sql
            String sql="select * from users where id=1";
            //查询获取返回集合
            ResultSet query = statement.executeQuery(sql);
            //遍历
            while (query.next()){
                System.out.println(query.getObject("NAME"));
                System.out.println(query.getObject("PASSWORD"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,rs);
        }
    }
}