山西农业大学20240912

Jia_dongyao

已于 2024-09-13 10:43:22 修改

阅读量191

点赞数 1

分类专栏：山西农业大学文章标签： java

于 2024-09-12 14:31:18 首次发布

本文链接：https://blog.csdn.net/2401_86991268/article/details/142165446

版权

山西农业大学专栏收录该内容

30 篇文章 2 订阅

订阅专栏

12-数据库之JDBC2

- 一. JDBC使用

一. JDBC使用

1. JDBC执行DML语句 - UPDATE

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/**
 * 演示DML语句 - UPDATE
 * 修改密码功能
 */
public class JDBCDemo3 {
    public static void main(String[] args) {

        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建数据库连接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true",
                    "root", "root");
            // 3. 创建执行SQL语句的对象
            Statement statement = connection.createStatement();
            // 编写sql语句
//            String sql = "UPDATE userinfo SET password = '111333' WHERE username = '赵丽颖';";
            // 接收用户输入 -- 修改密码
            Scanner scanner = new Scanner(System.in);
            System.out.println("修改密码");
            System.out.println("请输入用户名:");
            String username = scanner.nextLine();
            System.out.println("请输入新密码");
            String password = scanner.nextLine();
            String sql = "UPDATE userinfo SET password = '"+password+"' WHERE username = '"+username+"';";
            // 4.执行SQL语句
            int num = statement.executeUpdate(sql);
            // 5.处理SQL执行的结果
            if (num>0){
                System.out.println("密码修改成功");
            }else{
                System.out.println("密码修改失败");
            }
            // 6. 关闭连接
            connection.close();

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

    }
}

2. 封装DBUtil

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * 数据库连接工具
 */
public class DBUtil {
    // 静态块, 类加载时只执行一次
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    // 创建数据库连接 - 单独定义一个静态方法
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true",
                "root", "root"
        );
    }

}

3. JDBCDemo4 - DELETE

package jdbc;


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

/**
 * 执行DML语句, DELETE
 * 删除执行用户,让用户输入用户名,将该用户删除(userinfo)
 * DELETE FROM userinfo WHERE username='xxx';
 */
public class JDBCDemo4 {
    public static void main(String[] args) {
        try(Connection connection = DBUtil.getConnection();) {
            Statement statement = connection.createStatement();
            // 接收用户输入
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户名:");
            String username = scanner.nextLine();
            // 执行SQL语句
            String sql = "DELETE FROM userinfo WHERE username='"+username+"';";
            int num = statement.executeUpdate(sql);
            if (num>0){
                System.out.println("用户删除成功");
            }else{
                System.out.println("用户删除失败");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

4. JDBCDemo5 - DQL语句

package jdbc;

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

/**
 * JDBC执行DQL语句: SELECT
 * 需求: 查询tedu.teacher: 老师的id,name,salary,title
 */
public class JDBCDemo5PM {
    public static void main(String[] args) {
        try (Connection connection = DBUtil.getConnection();){
            Statement statement = connection.createStatement();
            String sql = "SELECT id,name,salary,title FROM teacher";
            // 执行DQL语句: 调用 executeQuery(sql)
            // 查询语句返回数据多, 要将数据存在ReaultSet集合中
            ResultSet rs = statement.executeQuery(sql);
            System.out.println("ResultSet:"+rs);// 地址:集合
            /*
                ResultSet重要方法:
                next(): 返回boolean
                可以让结果集向下移动一行,返回值为 true| false
             */
            // 对于获取结果的处理
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int salary = rs.getInt("salary");
                String title = rs.getString("title");
                System.out.println(" "+id+","+name+","+salary+","+title);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

4.1 练习

package jdbc;

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

/**
 * 练习: 输入老师的姓名, 查询该老师的姓名, 年龄, 薪资, 职称
 */
public class JDBCDemo6PM {
    public static void main(String[] args) {
        try (Connection connection = DBUtil.getConnection();){
            // 创建执行SQL语句对象
            Statement statement = connection.createStatement();
            // 接收用户输入
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入老师姓名:");
            String name = scanner.nextLine();
            String sql="SELECT name,age,salary,title FROM teacher WHERE name='"+name+"'";
            // 执行SQL语句
            ResultSet rs = statement.executeQuery(sql);
            // 获取查询结果
            while (rs.next()){
                int age = rs.getInt("age");
                int salary = rs.getInt("salary");
                String title = rs.getString("title");
                System.out.println(name+","+age+","+salary+","+title);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

4.2 练习

package jdbc;

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

/**
 * 查询每个老师所带班级各有多少人?列出老师的姓名, 班级名称, 班级人数
 *
 * -- 关联查询
 * SELECT t.name,c.name,COUNT(*) number
 * FROM teacher t,class c, student s
 * WHERE t.id = c.teacher_id
 * AND c.id = s.class_id
 * GROUP BY t.name,c.name;
 *
 * -- 内连接
 * SELECT t.name,c.name,COUNT(*) number
 * FROM teacher t
 * JOIN class c ON t.id = c.teacher_id
 * JOIN student s ON c.id = s.class_id
 * GROUP BY t.name,c.name;
 */
public class JDBCDemo7PM {
    public static void main(String[] args) {
        try (Connection connection = DBUtil.getConnection();){
            // 创建执行SQL语句的对象
            Statement statement = connection.createStatement();
            String sql = "SELECT t.name,c.name,COUNT(*) number " +
                    "FROM teacher t " +
                    "JOIN class c ON t.id = c.teacher_id " +
                    "JOIN student s ON c.id = s.class_id " +
                    "GROUP BY t.name,c.name;";
            ResultSet rs = statement.executeQuery(sql);
            // 获取结果
            while (rs.next()){
                String tname = rs.getString("t.name");
                String cname = rs.getString("c.name");
                int number = rs.getInt("number");
                System.out.println(tname+","+cname+","+number);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

/*
    练习:
    1.查看王克晶老师所带班级的信息, 列出老师的姓名,老师性别,班级名称,所在楼层
    2.查看1年级1班共有多少人?
 */

4.3 练习

package jdbc;

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

/**
 * 1.查看王克晶老师所带班级的信息, 列出老师的姓名,老师性别,班级名称,所在楼层
 * SELECT t.name,t.gender,c.name,c.floor
 * FROM teacher t
 * JOIN class c ON t.id = c.teacher_id
 * WHERE t.name='王克晶';
 */
public class JDBCTest1 {
    public static void main(String[] args) {
        try (Connection connection = DBUtil.getConnection();){
            Statement statement = connection.createStatement();
            String sql = "SELECT t.name,t.gender,c.name,c.floor " +
                    "FROM teacher t " +
                    "JOIN class c ON t.id = c.teacher_id " +
                    "WHERE t.name='王克晶'";
            ResultSet rs = statement.executeQuery(sql);
            // 获取查询结果
            while (rs.next()){
                String tname = rs.getString("t.name");
                String gender = rs.getString("t.gender");
                String cname = rs.getString("c.name");
                int floor = rs.getInt("floor");
                System.out.println(tname+","+gender+","+cname+","+floor);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

4.4 练习

package jdbc;

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

/**
 *    2.查看1年级1班共有多少人?
 *    SELECT c.name,COUNT(*) number
 *    FROM class c
 *    JOIN student s ON s.class_id = c.id
 *    WHERE c.name='1年级1班';
 */
public class JDBCTest2 {
    public static void main(String[] args) {
        try (Connection connection = DBUtil.getConnection();){
            Statement statement = connection.createStatement();
            String sql = "SELECT c.name,COUNT(*) number " +
                    "FROM class c  " +
                    "JOIN student s ON s.class_id = c.id " +
                    "WHERE c.name='1年级1班';";
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()){
                String cname = rs.getString("c.name");
                int number = rs.getInt("number");
                System.out.println(cname+"人数:"+number);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}