jdbc基础运用(实现连接数据库的增删改查)

最新推荐文章于 2022-12-07 17:00:00 发布

阅读量222

点赞数

分类专栏： java 文章标签：数据库 sql java

本文链接：https://blog.csdn.net/m0_70725839/article/details/124900965

版权

java 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

工具类

package com.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

@SuppressWarnings("all")
public class DruidJdbcUtils {

    //成员变量位置,模拟线程ThreadLocal<Connection>
    private static ThreadLocal<Connection> t1 = new ThreadLocal<>();

    //声明 DateSource 类型的变量
    private static DataSource ds;

    //无参私有化:目的是外界不能new对象
    private DruidJdbcUtils(){}

    //静态代码块
    static {

        try {
            //创建属性集合列表
            Properties prop = new Properties();

            //读取连接池配置文件
            InputStream inputStream = DruidJdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");

            //将字节输入流的内容加载至属性列表中
            prop.load(inputStream);

            //DruidDateSourceFactroy工厂类获取数据源对象
            ds = DruidDataSourceFactory.createDataSource(prop);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //封装一个获取数据源的功能
    public static DataSource getDataSource(){
        return ds;
    }

    //封装一个功能:获取数据库的连接对象
    public static Connection getConnection(){
        try {
            //从当前线程中获取连接对象
            Connection conn = t1.get();
            if (conn == null){
                //从数据连接池获取连接对象
                conn = ds.getConnection();
                //将当前连接对象绑定到自己的线程中
                t1.set(conn);
            }
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //封装释放资源
    public static void close(ResultSet rs, PreparedStatement ps,Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
                //需要将自己线程中的连接对象解绑
                t1.remove();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(PreparedStatement ps,Connection conn){
        close(null,ps,conn);
    }

    //测试能否获取到连接对象
    public static void main(String[] args) {
        Connection conn = DruidJdbcUtils.getConnection();
        System.out.println(conn);
    }
}

配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/my2203
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000

访问数据库的接口

package com.dao;

import com.pojo.Student;

import java.sql.SQLException;
import java.util.List;

public interface StudentDao {

    //查询所有
    List<Student> findAll() throws SQLException;

    //添加
    void add(Student student) throws SQLException;

    //删除
    void delete(int id) throws SQLException;

    //修改
    void update(Student student) throws SQLException;

    //通过id查询
    Student find(int id) throws SQLException;

    //获取总记录数
    int getCount() throws SQLException;
}

访问数据库接口的实现类

package com.dao.impl;

import com.dao.StudentDao;
import com.pojo.Student;
import com.utils.DruidJdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    @Override
    public List<Student> findAll() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
        String sql = "select * from exam";
        List<Student> list = qr.query(sql,new BeanListHandler<Student>(Student.class));
//        Connection conn = DruidJdbcUtils.getConnection();
//        String sql = "select * from exam limit ?";
//        PreparedStatement ps = conn.prepareStatement(sql);
//        ps.setInt(1,id);
//        ResultSet rs = ps.executeQuery();
//        Student s = null;
//        ArrayList<Student> list = new ArrayList<>();
//        while (rs.next()){
//            s = new Student();
//            s.setId(rs.getInt("id"));
//            s.setName(rs.getString("name"));
//            s.setMath(rs.getDouble("math"));
//            s.setChinese(rs.getDouble("chinese"));
//            s.setEnglish(rs.getDouble("english"));
//            list.add(s);
//        }
//        DruidJdbcUtils.close(rs,ps,conn);
        return list;
    }

    @Override
    public void add(Student student) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
        String sql = "insert into exam(name,math,chinese,english) values (?,?,?,?)";
        int count = qr.update(sql,student.getName(),student.getMath(),student.getChinese(),student.getEnglish());
        System.out.println("影响了"+count+"行");
    }

    @Override
    public void delete(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
        String sql = "delete from exam where id=?";
        int i = qr.update(sql, id);
        System.out.println("影响了"+i+"行");
    }

    @Override
    public void update(Student student) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
        String sql = "update exam set name=?,math=?,chinese=?,english=? where id=?";
        int i = qr.update(sql, student.getName(), student.getMath(), student.getChinese(), student.getEnglish(),student.getId());
        System.out.println("影响了"+i+"行");
    }

    @Override
    public Student find(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
        String sql = "select * from exam where id=?";
        Student student = qr.query(sql,new BeanHandler<Student>(Student.class),id);
        System.out.println(student);
        return student;
    }

    @Override
    public int getCount() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
        String sql = "select count(id) from exam";
        Object obj = qr.query(sql, new ScalarHandler<>());
        String s = String.valueOf(obj);
        int i = Integer.parseInt(s);
        return i;
    }
}

实体类

package com.pojo;


public class Student {
    private int id;
    private String name;
    private double math;
    private double chinese;
    private double english;

    public Student() {
    }

    public Student( String name, double math, double chinese, double english) {
        this.id = id;
        this.name = name;
        this.math = math;
        this.chinese = chinese;
        this.english = english;
    }

    public Student(int id, String name, double math, double chinese, double english) {
        this.id = id;
        this.name = name;
        this.math = math;
        this.chinese = chinese;
        this.english = english;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public double getMath() {
        return math;
    }

    public void setMath(double math) {
        this.math = math;
    }

    public double getChinese() {
        return chinese;
    }

    public void setChinese(double chinese) {
        this.chinese = chinese;
    }

    public double getEnglish() {
        return english;
    }

    public void setEnglish(double english) {
        this.english = english;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", math=" + math +
                ", chinese=" + chinese +
                ", english=" + english +
                '}';
    }
}

测试类

package com.examText;

import com.dao.StudentDao;
import com.dao.impl.StudentDaoImpl;
import com.pojo.Student;
import org.junit.Before;
import org.junit.Test;

import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;

@SuppressWarnings("all")
public class StudentText {
    public static void main(String[] args) throws Exception {

        while (true){
            look();
        }

    }

    public static void look() throws SQLException {
        System.out.println("-----------欢迎进入学员管理系统----------");
        Scanner scanner = new Scanner(System.in);
        System.out.println("1.查看 , 2.添加 , 3.删除 , 4.修改");
        System.out.println("请输入要操作的业务:");
        int i = scanner.nextInt();
        switch (i){
            case 1:
                findAllText();
                break;
            case 2:
                addText();
                break;
            case 3:
                deleteText();
                break;
            case 4:
                updateText();
                break;
             default:
                 System.out.println("输入有误");
        }
    }
    private static StudentDao studentDao;

    @Before
    public static void creDaoObject(){
        studentDao = new StudentDaoImpl();
    }


    @Test
    public static void findAllText() throws SQLException {
//        List<Student> list = studentDao.findAll();
        List<Student> all = new StudentDaoImpl().findAll();
        if (all != null){
            for (Student student : all) {
                System.out.println(student);
            }
        }
    }

    @Test
    public static void addText() throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入姓名:");
        String n = scanner.next();
        System.out.println("请输入数学成绩:");
        double m = scanner.nextDouble();
        System.out.println("请输入语文成绩:");
        double c = scanner.nextDouble();
        System.out.println("请输入英语成绩:");
        double e = scanner.nextDouble();
        Student s = new Student(n, m, c, e);
//        studentDao.add(s);
        new StudentDaoImpl().add(s);
    }

    @Test
    public static void deleteText() throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要删除的学生编号");
        int i = scanner.nextInt();
//        studentDao.delete(i);
        new StudentDaoImpl().delete(i);
    }

    @Test
    public static void updateText() throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入学生编号");
        int i = scanner.nextInt();
        System.out.println("请输入姓名:");
        String n = scanner.next();
        System.out.println("请输入数学成绩:");
        double m = scanner.nextDouble();
        System.out.println("请输入语文成绩:");
        double c = scanner.nextDouble();
        System.out.println("请输入英语成绩:");
        double e = scanner.nextDouble();
        Student s = new Student(i, n, m, c, e);
//        studentDao.update(s);
        new StudentDaoImpl().update(s);
    }

    @Test
    public static void findText() throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要查看的学生编号");
        int i = scanner.nextInt();
//        studentDao.find(i);
        new StudentDaoImpl().find(i);
    }

    @Test
    public static void getCountText() throws SQLException {
//        System.out.println("总记录是为:" + studentDao.getCount());
        System.out.println("总记录是为:" + new StudentDaoImpl().getCount());
    }




}