MySQL数据库（5）--JDBC

@ljn

已于 2022-05-16 09:29:31 修改

阅读量557

点赞数

文章标签：数据库 mysql intellij-idea

于 2022-05-09 13:47:00 首次发布

本文链接：https://blog.csdn.net/m0_62567916/article/details/124664423

版权

1 JDBC入门

1.1 JDBC的概念

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它是由一组用Java语言编写的类和接口组成的。

1.2jdbc的本质

其实就是java官方提供的一套规范(接口)。用于帮助开发人员快速实现不同关系型数据库的连接！

1.3 jdbc快速入门程序

(1)导入jar包（idea建MAVEN项目在pom.xml下）

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>数据库版本号</version>
</dependency>

PS：所有的<dependency> </dependency>在俩个<dependencies></dependencies>之间

(2)注册驱动

Class.forName("com.mysql.jdbc.Driver");

注意：mysql5之后的驱动jar包可以省略注册驱动的步骤。在jar包中，存在一个java.sql.Driver配置文件，文件中指定了com.mysql.jdbc.Driver

(3)获取连接

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "root");

(4)获取执行者对象

Statement stat = con.createStatement();

(5)执行sql语句，并接收返回结果

String sql = "SELECT * FROM user";
ResultSet rs = stat.executeQuery(sql);

(6)处理结果

while(rs.next()) {
    System.out.println(rs.getInt("id") + "\t" + rs.getString("name"));
}

(7)释放资源

con.close();
stat.close();
rs.close();

2 jdbc 功能实现

2.1 DriverManager：驱动管理对象

(1)作用：注册驱动(告诉程序该使用哪一个数据库驱动)

(2)真正写代码能使用：Class.forName("com.mysql.jdbc.Driver");

(3)拓展展示源码：

PS：

返回值：Connection数据库连接对象

参数：

url：指定连接的路径。语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
user：用户名
password：密码

2.2 Connection：数据库连接对象

2.3 Statement：执行sql语句的对象

2.4 ResultSet：结果集对象

3 案例

数据准备

-- 创建db14数据库
CREATE DATABASE db14;

-- 使用db14数据库
USE db14;

-- 创建student表
CREATE TABLE student(
        sid INT PRIMARY KEY AUTO_INCREMENT,        -- 学生id
        NAME VARCHAR(20),                                        -- 学生姓名
        age INT,                                                        -- 学生年龄
        birthday DATE                                                -- 学生生日
);

-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'1999-09-23'),(NULL,'李四',24,'1998-08-10'),(NULL,'王五',25,'1996-06-06'),(NULL,'赵六',26,'1994-10-20');

/*    
        实体类
Student类，成员变量对应表中的列
注意：所有的基本数据类型需要使用包装类，以防null值无法赋值
*/
package com.bukaedu02.domain;

import java.util.Date;

public class Student {
    private Integer sid;
    private String name;
    private Integer age;
    private Date birthday;

    public Student() {
    }

    public Student(Integer sid, String name, Integer age, Date birthday) {
        this.sid = sid;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                '}';
    }
}

3.1 查询全部数据内容

dao 层

public class dao extends Service {
    public static ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        try{

            Class.forName("com.mysql.jdbc.Driver");  //C是大写的

            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
//后两个为数据库账号和密码
            stat = con.createStatement();

            String sql = "SELECT * FROM student";
            rs = stat.executeQuery(sql);

            while(rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");


                Student stu = new Student(sid,name,age,birthday);


                list.add(stu);
            }

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

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

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

}

service层

public class Service {
    public static ArrayList<Student> findAll() {
        return dao.findAll();
    }
}

controller层

public class Controller {
    public static void main(String[] args) {
        ArrayList<Student> list = Service.findAll();
        for(Student stu : list) {
            System.out.println(stu);
        }
}
}

3.2 条件查询

Dao层

public class Dao {
     static Student FindById(Integer cid){
        Student student =new Student();
        Connection con =null;
        Statement stat = null;
        ResultSet rs =null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
            stat = con.createStatement();
            String sql ="SELECT * FROM student WHERE sid ='" + cid  + "'";  //字符串的拼接
            rs=stat.executeQuery(sql);
            while(rs.next()){
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                student.setSid(sid);
                student.setName(name);
                student.setAge(age);
                student.setBirthday(birthday);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
            finally{
            try {
                con.close();
                stat.close();
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return student;
    }
}

Service层

public class Service {

    public static Student FindById(Integer cid) {
        
        return Dao.FindById(cid);
    };
}

Controller层
public class Controller {
    public static void main(String[] args) {
        Student stu =Service.FindById(3);
        System.out.println(stu);
    }
}

3.3 新增数据

Dao 层

public class Dao {
    public static int insert (Student stu){
        Connection con =null;
        Statement stat =null;
        int result =0;
        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
            //获取执行者对象
            stat= con.createStatement();
            //执行SQL语句并且接收返回的结果集
            Date d =stu.getBirthday();
            SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyy-MM-dd");
            String birthday =simpleDateFormat.format(d);
            String sql ="INSERT INTO student VALUES ('" +stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
            result =stat.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                con.close();
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return result;
    }
}

Service层

public class Service {
    public static int insert (Student stu){     
        return Dao.insert(stu);
    }
}

Controller层
public class Controller {
    public static void main(String[] args) {
        Student student =new Student(6,"钱老爷",88,new Date());
        int result =Service.insert(student);
        if(result != 0) {
            System.out.println("新增成功");
        }else {
            System.out.println("新增失败");
        }
    }
}

3.4 修改数据

Dao 层

public class Dao {
    public static int update(Student student){
        Connection con =null;
        Statement  stat =null;
        int result =0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
            stat = con.createStatement();
            Date d = student.getBirthday();
            SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyy-MM-dd");
            String birthday =simpleDateFormat.format(d);
            String sql = "UPDATE student SET sid='"+student.getSid()+"',name='"+student.getName()+"',age='"+student.getAge()+"',birthday='"+birthday+"' WHERE sid='"+student.getSid()+"'";
            result = stat.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                con.close();
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return result;
    }
}

Service层
public class Service {
    public static int update(Student student){
        return Dao.update(student);
    }
}

Controller层

public class Controller {
    public static void main(String[] args) {
        Student stu = JDBC1.Demo2.Service.FindById(5);
        stu.setName("周七");
        int result = Service.update(stu);
        if(result != 0) {
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
    }
}

3.5 删除数据

Dao层

public class Dao {
    public static int delete(Integer id){
        Connection con =null;
        Statement stat =null;
        int result = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
            stat = con.createStatement();
            String sql ="DELETE FROM student WHERE sid='"+id+"'";
            result = stat.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                con.close();
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
}

Service层

public class Service {
    public static int delete(Integer id){
        return Dao.delete(id);
    }
}

Controller层

public class Controller {
    public static void main(String[] args) {
        int result = Service.delete(6);

        if(result != 0) {
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }
}

4 JDBC工具类

工具类的作用：把一个通用的功能包装在一个类中，让它可以在不同的地方重用，这样子我们在编程时可以简化书写，避免重复太多代码。

4.1 配置文件（用于连接数据库）配置文件(在src下创建config.properties)

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db14
username=root
password=8Gd_GFI=*jo&


PS：数据库账号（username）密码（password）根据自己的数据库的账号密码

4.2JDBC工具类

/*
    JDBC工具类
 */
public class JDBCUtils {
    //1.私有构造方法
    private JDBCUtils(){};

    //2.声明配置信息变量
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;
    private static Connection con;

    //3.静态代码块中实现加载配置文件和注册驱动
    static{
        try{
            //通过类加载器返回配置文件的字节流
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");

            //创建Properties集合，加载流对象的信息
            Properties prop = new Properties();
            prop.load(is);

            //获取信息为变量赋值
            driverClass = prop.getProperty("driverClass");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");

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

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

    //4.获取数据库连接的方法
    public static Connection getConnection() {
        try {
            con = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return con;
    }

    //5.释放资源的方法
    public static void close(Connection con, Statement stat, ResultSet rs) {
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

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

    public static void close(Connection con, Statement stat) {
        close(con,stat,null);
    }
}

4.3 使用工具类优化student表的CRUD（增删改查）

Student类

public class Student {
    private Integer sid;
    private String name;
    private Integer age;
    private Date birthday;

    public Student() {
    }

    public Student(Integer sid, String name, Integer age, Date birthday) {
        this.sid = sid;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                '}';
    }
}

JDBCUtils工具类

public class JDBCUtils {
    //1.私有构造方法
    private JDBCUtils(){};
    //2.声明配置信息变量
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;
    private static Connection con;
    //3.通过静态代码块中实现加载配置文件和注册驱动
    static {
        try {
            //通过类加载器返回配置文件的字节流。
            InputStream is =JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
            //创建properties集合，加载流对象信息。
            Properties prop =new Properties();
            prop.load(is);
            //获取信息为变量赋值
            driverClass = prop.getProperty("driverClass");
            url = prop.getProperty("url");
            username=prop.getProperty("username");
            password=prop.getProperty("password");
            //注册驱动
            Class.forName(driverClass);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //4.获取数据库连接方式
    public static Connection getConnection(){
        try {
            con = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    //释放资源的方式
    public static void close(Connection con, Statement stat, ResultSet rs){
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(Connection con, Statement stat) {
        close(con,stat,null);
    }
}

DAO 层
/*
查询所有学生信息
*/
public class Dao {

    public static ArrayList<Student> findAll() {
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        ArrayList<Student> list = new ArrayList<>();
        try {
            //1.获取连接
            con = JDBCUtils.getConnection();

            //2.获取执行者对象
            stat = con.createStatement();


            //3.执行sql语句，并接收结果
            String sql = "SELECT * FROM student";
            rs = stat.executeQuery(sql);

            //4.处理结果，将每条记录封装成一个Student对象。将多个Student对象保存到集合中
            while(rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                Student stu = new Student(sid,name,age,birthday);

                list.add(stu);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.close(con,stat,rs);
        }

        return list;
    }

    /*
        条件查询，根据id查询学生信息
     */

    public static Student findById(Integer id) {
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        Student stu = new Student();
        try {
            //1.获取连接
            con = JDBCUtils.getConnection();

            //2.获取执行者对象
            stat = con.createStatement();

            //3.执行sql语句，并接收结果
            String sql = "SELECT * FROM student WHERE sid='"+id+"'";
            rs = stat.executeQuery(sql);

            //4.处理结果，将记录封装成一个Student对象。
            if(rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                stu.setSid(sid);
                stu.setName(name);
                stu.setAge(age);
                stu.setBirthday(birthday);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.close(con,stat,rs);
        }

        return stu;
    }

    /*
        新增学生信息
     */

    public static int insert(Student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try{
            //1.获取连接
            con = JDBCUtils.getConnection();

            //2.获取执行者对象
            stat = con.createStatement();

            //3.执行sql语句，并接收结果
            Date date = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(date);
            String sql = "INSERT INTO student VALUES (null,'"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
            result = stat.executeUpdate(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4.释放资源
            JDBCUtils.close(con,stat);
        }

        return result;
    }

    /*
        修改学生信息
     */

    public static int update(Student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try{
            //1.获取连接
            con = JDBCUtils.getConnection();

            //2.获取执行者对象
            stat = con.createStatement();

            //3.执行sql语句，并接收结果
            Date date = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(date);
            String sql = "UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
            result = stat.executeUpdate(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4.释放资源
            JDBCUtils.close(con,stat);
        }

        return result;
    }

    /*
        删除学生信息
     */

    public static int delete(Integer id) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try{
            //1.获取连接
            con = JDBCUtils.getConnection();

            //2.获取执行者对象
            stat = con.createStatement();

            //3.执行sql语句，并接收结果
            String sql = "DELETE FROM student WHERE sid='"+id+"'";
            result = stat.executeUpdate(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }  finally {
            //4.释放资源
            JDBCUtils.close(con,stat);
        }

        return result;
    }

}

Service层
public class Service {

    public static ArrayList<Student> findAll() {
        return Dao.findAll();
    }


    public  static Student findById(Integer id)  {
        return Dao.findById(id);
    }


    public static int insert(Student stu)  {
        return Dao.insert(stu);
    }


    public static int update(Student stu) {
        return Dao.update(stu);
    }


    public static int delete(Integer id)  {
        return Dao.delete(id);
    }
}

Controller层
PS：只列举了查。
public class Controller {
        public static void main(String[] args) {
            ArrayList<Student> list = Service.findAll();
            for (Student stu:list){
                System.out.println(stu);
            }
        }

}