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); } }
