随笔- 228 文章- 0 评论- 1 阅读- 20301

使用PreparedStatement实现CRUD操作

1.实体类

package com.lili.entity;

import java.sql.Date;

/**
 * @author: QiJingJing
 * @create: 2021/7/27
 */
public class Student {
    /**
     * 编号
     */
    private int id;
    /**
     * 学号
     */
    private String studentId;
    /**
     * 学生姓名
     */
    private String studentName;
    /**
     *  性别
     */
    private String sex;
    /**
     * 民族
     */
    private String group;
    /**
     * 出生日期
     */
    private Date bornDate;
    /**
     * 家庭地址
     */
    private String address;
    /**
     * 身份证号
     */
    private String idNumber;

    public Student() {
    }

    public Student(int id, String studentId, String studentName, String sex, String group, Date bornDate, String address, String idNumber) {
        this.id = id;
        this.studentId = studentId;
        this.studentName = studentName;
        this.sex = sex;
        this.group = group;
        this.bornDate = bornDate;
        this.address = address;
        this.idNumber = idNumber;
    }

    public int getId() {
        return id;
    }

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

    public String getStudentId() {
        return studentId;
    }

    public void setStudentId(String studentId) {
        this.studentId = studentId;
    }

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getGroup() {
        return group;
    }

    public void setGroup(String group) {
        this.group = group;
    }

    public Date getBornDate() {
        return bornDate;
    }

    public void setBornDate(Date bornDate) {
        this.bornDate = bornDate;
    }

    public String getAddress() {
        return address;
    }

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

    public String getIdNumber() {
        return idNumber;
    }

    public void setIdNumber(String idNumber) {
        this.idNumber = idNumber;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", studentId='" + studentId + '\'' +
                ", studentName='" + studentName + '\'' +
                ", sex='" + sex + '\'' +
                ", group='" + group + '\'' +
                ", bornDate=" + bornDate +
                ", address='" + address + '\'' +
                ", idNumber='" + idNumber + '\'' +
                '}';
    }
}

2.db.properties

url = jdbc:mysql://localhost:3306/jdbc
username = root
password = root
classDriver = com.mysql.jdbc.Driver

3.BaseDao类

package com.lili.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;

/**
 * @author: QiJingJing
 * @create: 2021/7/27
 */
public class BaseDao {
    private static String url;
    private static String username;
    private static String password;
    private static String classDriver;

    /**
     * 加载配置文件
     */
    static {
        InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("com/lili/db.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
        classDriver = properties.getProperty("classDriver");
    }

    /**
     * 加载驱动
     */
    static {
        try {
            Class.forName(classDriver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取链接
     */
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 为占位符赋值
     */
    public static void setPrepareStatement(PreparedStatement pre, Object... args) {
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                try {
                    pre.setObject(i + 1, args[i]);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 统一的增删改操作
     */
    public static void update(String sql, Object... args) {
         try (Connection connection = getConnection();PreparedStatement pre = connection.prepareStatement(sql)){
            setPrepareStatement(pre, args);
            pre.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询操作
     */
    public static <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) {
       try(Connection connection = getConnection();PreparedStatement pre = connection.prepareStatement(sql)){
            setPrepareStatement(pre, args);
            ResultSet resultSet = pre.executeQuery();
            return rowMapper.query(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

4.RowMapper接口（用来返回查询对象的list集合）

package com.lili.util;

import java.sql.ResultSet;
import java.util.List;

/**
 * @author: QiJingJing
 * @create: 2021/7/27
 */
public interface RowMapper<T> {
    /**
     * 查询结果集
     */
    List<T> query(ResultSet set) throws Exception;
}

5.StudentImpl（具体实现类）

package com.lili.rowMapperImpl;

import com.lili.entity.Student;
import com.lili.util.RowMapper;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author: QiJingJing
 * @create: 2021/7/27
 */
public class StudentImpl implements RowMapper<Student> {
    @Override
    public List<Student> query(ResultSet set) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<Student> students = new ArrayList<>();
        while (set.next()) {
            Student student = new Student();
            student.setId(Integer.parseInt(set.getString("id")));
            student.setStudentId(set.getString("studentId"));
            student.setStudentName(set.getString("studentName"));
            student.setSex(set.getString("sex"));
            student.setGroup(set.getString("group"));
            Date bornDate = sdf.parse(set.getString("bornDate"));
            student.setBornDate(new java.sql.Date(bornDate.getTime()));
            student.setAddress(set.getString("address"));
            student.setIdNumber(set.getString("idNumber"));
            students.add(student);
        }
        return students;
    }
}

6.测试类

package com.lili.test;

import com.lili.entity.Student;
import com.lili.rowMapperImpl.StudentImpl;
import com.lili.util.BaseDao;
import org.junit.Test;

import java.util.List;

/**
 * @author: QiJingJing
 * @create: 2021/7/27
 */
public class Test02 {
    /**
     * 查询所有学生
     */
    @Test
    public void test01() {
        String sql = "select * from students";
        List<Student> studentList = BaseDao.query(sql, new StudentImpl());
        if (studentList != null) {
            studentList.forEach(System.out::println);
        }
    }

    /**
     * 查询id为1,学号为180700080101的学生
     */
    @Test
    public void test02() {
        String sql = "select * from students where id = ? and studentId=?";
        List<Student> studentList = BaseDao.query(sql, new StudentImpl(), 1, "180700080101");
        if (studentList != null) {
            studentList.forEach(System.out::println);
        }
    }

    /**
     * 添加一个学生信息
     */
    @Test
    public void test03() {
        String sql = "insert into students values(?,?,?,?,?,?,?,?)";
        BaseDao.update(sql, 943, "180700080200", "上官婉儿", "女", "汉族", "1999-12-10", "王者荣耀", "21212121");
    }

    /**
     * 删除id为943的学生信息
     */
    @Test
    public void test04() {
        String sql = "delete from students where id = ?";
        BaseDao.update(sql, 943);
    }

    /**
     * 修改id为55的学生的性别为女
     */
    @Test
    public void test05() {
        String sql = "update students set `sex` = ? where id = ?";
        BaseDao.update(sql, "女", 55);
    }
}