JDBC——idea连接阿里云的mysql进行增删改查操作

1、mysql修改权限

终端登录修改权限

1.1进入数据库
mysql -u"root" -p"你的密码"

1.2进入mysql
use mysql;

1.3查看权限
select host,user from user;
root的host原本是localhost将它更改为 %
update user set host =‘%’ where user =‘root’ and host =‘localhost’;
1.4刷新一下权限
flush privileges;
select host,user from user;

忘记MySQL密码

1、打开宝塔面板mysql的配置修改 添加
skip-grant-tables

2、重启mysql

3、修改密码
mysql -uroot -p"你的密码"
update mysql.user set password=password(‘12456’) where user=‘root’;

如果出现这个提示后

password改authentication_string
update mysql.user set authentication_string=PASSWORD(‘12456’) where User=‘root’;

修改密码成功
去掉 skip-grant-tables，在服务里重启mysql

2、idea进行增删改查

导入jar包
将jar包拖入到项目目录下
右键点击 Add as Library

右键点击OK

创建个ConnectionTest.java
创建连接

  private static Connection createConnection() {
        try {
            return DriverManager.getConnection(
                    "jdbc:mysql://120.78.240.231:3306/demo01?rewriteBatchedStatements=true",
                    "root",
                    "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

.
提前加载驱动类
Class.forName(“com.mysql.cj.jdbc.Driver”);

查询数据库

List<Object[]> result = show("show databases;");-----这句加在main方法里
 private static List<Object[]> show(String sql, Object... args) {
        //获取连接
        	 Connection connection = createConnection();
      		  if (connection == null) return null;
      		  PreparedStatement statement = null;
       		 ResultSet resultSet = null;
        try {
            // 执行查询语句
            statement = connection.prepareStatement(sql);
            for (int i = args.length; i > 0; ) statement.setObject(i, args[--i]);
            resultSet = statement.executeQuery();
            int columnCount = resultSet.getMetaData().getColumnCount();
            //读取数据
            List<Object[]> result = new ArrayList<>();
            while (resultSet.next()) {
                Object[] row = new Object[columnCount];
                for (int i = 0; i < columnCount; ) row[i] = resultSet.getObject(++i); 
                result.add(row);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(statement);
            close(connection);
        }
        return null;
    }

关闭

  private static void close(AutoCloseable obj) {
        if (obj != null) try {
            obj.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

三种遍历方式显示---------------写在main里

        //迭代器遍历
        Iterator<Object[]> iterator = result.iterator();
        while (iterator.hasNext()) System.out.println(Arrays.toString(iterator.next()));
        //for-in遍历
        for (Object[] e : result) System.out.println(Arrays.toString(e));
        //for遍历
        for (int i = 0; i < result.size(); i++) System.out.println(Arrays.toString(result.get(i)));

查询表

    private static List<Object[]> query(String sql, Object... args) {
        // 获取连接
        Connection connection = createConnection();
        if (connection == null) return null;

        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            // 执行查询语句
            statement = connection.prepareStatement(sql);
            for (int i = args.length; i > 0; ) statement.setObject(i, args[--i]);
            resultSet = statement.executeQuery();
            int columnCount = resultSet.getMetaData().getColumnCount();

            // 读取数据
            List<Object[]> result = new ArrayList<>();
            while (resultSet.next()) {
                Object[] row = new Object[columnCount];
                for (int i = 0; i < columnCount; ) row[i] = resultSet.getObject(++i); // [0] = (1),
                result.add(row);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(statement);
            close(connection);
        }
        return null;
    }



    

处理更新和删除

    private static void execute(String sql, Object... args) {
        // 获取连接
        Connection connection = createConnection();
        if (connection == null) return;

        PreparedStatement statement = null;
        try {
            // 执行语句
            statement = connection.prepareStatement(sql);
            for (int i = args.length; i > 0; ) statement.setObject(i, args[--i]);
            statement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(statement);
            close(connection);
        }
    }

插入数据

    // 插入
    private static Object insert(String sql, Object... args) {
        // 获取连接
        Connection connection = createConnection();
        if (connection == null) return null;

        PreparedStatement statement = null;
        try {
            // 执行插入语句
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = args.length; i > 0; ) statement.setObject(i, args[--i]);
            statement.execute();

            // 获取生成的主键
            ResultSet resultSet = statement.getGeneratedKeys();
            if (resultSet.next()) return resultSet.getObject(1);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(statement);
            close(connection);
        }
        return null;
    }

创建表

   private static void creat() {
        // 获取连接
        Connection connection = createConnection();
        if (connection == null) return;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String sql = "create table student(sno int(20),cno int(4),grade int(4),primary key(sno,cno))";
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            //更新
            statement.execute();
            System.out.println("创建表成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(statement);
            close(connection);
        }
    }