JAVA连接JDBC,编写简单的学生管理系统1

0.创建springboot项目

https://editor.csdn.net/md/?articleId=129443511

1.插入数据库

CREATE TABLE student (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` tinyint DEFAULT '0',
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3;

INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('1', 'may', '18', '武汉市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('2', 'me', '23', '上海市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('3', '李思', '12', '重庆市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('4', '刘流', '27', '武汉市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('5', '王麻子', '12', '成都市');
INSERT INTO student(`id`, `name`, `age`, `address`) VALUES ('6', 'KKK', '11', '成都');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('7', 'key', '22', '成都市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('8', '999', '33', '上海市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('9', 'wang', '55', '成都市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('10', 'lisi', '38', '成都市');

分层架构
entity—实体类----创建实体类与数据库表结构字段一一对应的
dao----数据库访问层----和数据库打交道
serivce—业务逻辑层
controller—控制层

2. 创建实体类entity

package JDBC.entity;

public class StudentEntity {
    private Long id;
    private String name;
    private Integer age;
    private String address;

    public StudentEntity(Long id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public StudentEntity(Long id, String name, Integer age, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.address = address;
    }

    public void setId(Long id) {
        this.id = id;
    }

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

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

    public void setAddress(String address) {
        this.address = address;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public Integer getAge() {
        return age;
    }

    public String getAddress() {
        return address;
    }

    @Override
    public String toString() {
        return "StudentEntity{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                '}';
    }
}

3. 创建dao层

java通过jdbc数据库的具体操作链接

package JDBC.dao;
import JDBC.entity.StudentEntity;
import com.mysql.cj.jdbc.Driver; //1.导入mysql驱动包

import java.sql.*;
import java.util.ArrayList;

public class StudentDao {
    /**
     * 学生对象数据库访问层
     */

    /**
     * 查询所有的学生信息
     *
     * @return
     */
    public ArrayList<StudentEntity> allStudent() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果
            resultSet = statement.executeQuery("select  * from student");
            ArrayList<StudentEntity> studentEntities = new ArrayList<>();
            //6. 对结果进行处理
            while (resultSet.next()) { // 如果false结束该循环
                // 获取该行数据的第一列 id
                Long id = resultSet.getLong("id");
                // 获取该行数据的第二列 name
                String name = resultSet.getString("name");
                // 获取该行数据的第三列 age
                Integer age = resultSet.getInt("age");
                // 获取该行数据的第四列 address
                String address = resultSet.getString("address");
                // 将db中查询到数据封装成java学生对象
                StudentEntity studentEntity = new StudentEntity(id, name, age, address);
                // 将该对象存入到集合中
                studentEntities.add(studentEntity);
            }
            return studentEntities;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (resultSet != null)
                    resultSet.close();
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }

    }

    /**
     * 根据学生id 查询学生信息 学生的id
     *
     * @return
     */
    public StudentEntity getByIdStudent(Long stuId) {
        /**
         * 判断用户是否传递学生id的值
         */
        if (stuId == null) {
            return null;
        }
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 自己拼接 查询sql语句
            resultSet = statement.executeQuery("select  * from student where id=" + stuId);
            boolean result = resultSet.next(); // 查询不到数据 false
            // 判断如果查询不到数据 则不会取值
            if (!result) {
                return null;
            }
            //6. 对结果进行处理
            // 获取该行数据的第一列 id
            Long id = resultSet.getLong("id");
            // 获取该行数据的第二列 name
            String name = resultSet.getString("name");
            // 获取该行数据的第三列 age
            Integer age = resultSet.getInt("age");
            // 获取该行数据的第四列 address
            String address = resultSet.getString("address");
            // 将db中查询到数据封装成java学生对象
            StudentEntity studentEntity = new StudentEntity(id, name, age, address);
            return studentEntity;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (resultSet != null)
                    resultSet.close();
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }

    }

    /**
     * 插入我们的学生
     *
     * @param stu
     * @return
     */
    public int insertStudent(StudentEntity stu) {
        Connection connection = null;
        Statement statement = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 executeUpdate执行 insert sql语句
            String insertStudentSql = "INSERT INTO student values(null,'" + stu.getName() + "'," + stu.getAge() + ",'" + stu.getAddress() + "')";
            System.out.println("insertStudentSql:" + insertStudentSql);
            // log输出
            int result = statement.executeUpdate(insertStudentSql);
            // 执行该sql语句 影响行数
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }
    }

    /**
     * 修改学生的信息
     *
     * @param stu
     * @return
     */
    public int updateStudent(StudentEntity stu) {
        Connection connection = null;
        Statement statement = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 executeUpdate执行 update sql语句
            String updateStudentSql = "update student  set name='" + stu.getName() + "' ,age=" + stu.getAge() + "," +
                    "address='" + stu.getAddress() + "' where id=" + stu.getId() + "";
            // log输出
            System.out.println("updateStudentSql:" + updateStudentSql);
            int result = statement.executeUpdate(updateStudentSql);
            // 执行该sql语句 影响行数
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }
    }

    /**
     * 根据主键id删除学生信息
     *
     * @param id
     * @return
     */
    public int delStudent(Long id) {
        // 判断id是否为null
        if (id == null) {
            return 0;
        }
        Connection connection = null;
        Statement statement = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 executeUpdate执行 delete sql语句
            String delSQL = "delete from  student where id=" + id;
            System.out.println("delSql:" + delSQL);
            // log输出
            int result = statement.executeUpdate(delSQL);
            // 执行该sql语句 影响行数
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }
    }

}

4. 创建service层

package JDBC.service;

import JDBC.dao.StudentDao;
import JDBC.entity.StudentEntity;

import java.util.ArrayList;

public class StudentService {
    private StudentDao studentDao = new StudentDao();
    public ArrayList<StudentEntity> allStudent(){
        //通过业务逻辑层调用dao层代码
        ArrayList<StudentEntity> studentEntities=studentDao.allStudent();
        return studentEntities;
    }
    public StudentEntity getByIdStudent(Long stuId) {
        return studentDao.getByIdStudent(stuId);
    }

    public int insertStudent(StudentEntity stu) {
        return studentDao.insertStudent(stu);
    }

    public int updateStudent(StudentEntity stu) {
        return studentDao.updateStudent(stu);
    }

    public int delStudent(Long id) {
        return studentDao.delStudent(id);
    }

}