JdbcTemplate增删改查案例
架包
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.2.0.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency>
实体类
public class Student implements Serializable { private Integer stuId; private String stuName; private String stuAddress; public Integer getStuId() { return stuId; } public void setStuId(Integer stuId) { this.stuId = stuId; } public String getStuName() { return stuName; } public void setStuName(String stuName) { this.stuName = stuName; } public String getStuAddress() { return stuAddress; } public void setStuAddress(String stuAddress) { this.stuAddress = stuAddress; } }
创建jdbc.properties文件
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/smbms?useUniCode=true&characterEncoding=utf-8 jdbc.username=root jdbc.password=123
dao层接口
public interface IStudentDao { //查询 public List<Student> getAllStudent(); //添加 public int addStudent(Student student); //修改 public int updateStudent(Student student); //删除 public int dStudent(Integer stuid); }
dao层实现类
@Repository public class IStudentDaoImpl implements IStudentDao { @Resource private JdbcTemplate jdbcTemplate; @Override public List<Student> getAllStudent() { //获取JDBC模板对象 String sql="select * from student"; //执行查询操作 /*List<Student> stuList = jdbcTemplate.query(sql, new RowMapper<Student>() { *//** * * @param rs 结果集 * @param rowNum 当前的记录行 * @return 方法返回值,返回泛型 * @throws SQLException *//* @Override public Student mapRow(ResultSet rs, int rowNum) throws SQLException { Student stu = new Student(); stu.setStuId(rs.getInt("stuid")); stu.setStuName(rs.getString("stuname")); stu.setStuAddress(rs.getString("stuaddress")); return stu; } });*/ RowMapper<Student> rowMapper=new BeanPropertyRowMapper<>(Student.class); List<Student> stuList = jdbcTemplate.query(sql, rowMapper); return stuList; } @Override public int addStudent(Student student) { //添加 String sql="insert student values(default,?,?)"; int i = jdbcTemplate.update(sql, student.getStuName(),student.getStuAddress()); return i; } @Override public int updateStudent(Student student) { //修改 String sql="update student set stuname=?,stuaddress=? where stuid=?"; int update = jdbcTemplate.update(sql, student.getStuName(), student.getStuAddress(),student.getStuId()); return update; } @Override public int dStudent(Integer stuid) { //删除 String sql="delete from student where stuid=6"; int count = jdbcTemplate.update(sql); return count; } }
service层接口
public interface IStudentService { //查询 public List<Student> getAllStudent(); //添加 public Integer addStudent(Student student); //修改 int updateStudent(Student student); //删除 public int dStudent(Integer stuid); }
service层实现类
@Service("iStudentService") public class IStudentServiceImpl implements IStudentService { //植入Dao层对象 @Resource private IStudentDao iStudentDao; @Override public List<Student> getAllStudent() { return iStudentDao.getAllStudent(); } @Override public Integer addStudent(Student student) { return iStudentDao.addStudent(student); } @Override public int updateStudent(Student student) { return iStudentDao.updateStudent(student); } @Override public int dStudent(Integer stuid) { return iStudentDao.dStudent(stuid); } public IStudentDao getiStudentDao() { return iStudentDao; } public void setiStudentDao(IStudentDao iStudentDao) { this.iStudentDao = iStudentDao; } }
创建applicationContext.xml文件
<!--扫描注解--> <context:component-scan base-package="com.spring"/> <!--加载配置文件--> <context:property-placeholder location="classpath:jdbc.properties"/> <!--DataSource供模板调用 DriverManagerDataSource:spring提供管理数据源的 c3p0数据源 dbcp数据源 --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <!--植入JDBCTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean>
测试类
public class Testss { @Test public void getAllStudent() { ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); IStudentService iStudentService = (IStudentService) ctx.getBean("iStudentService"); List<Student> allStudent = iStudentService.getAllStudent(); for (Student stu : allStudent) { System.out.println(stu.getStuName()); } } @Test public void getaddStudent() { ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); IStudentService iStudentService = (IStudentService) ctx.getBean("iStudentService"); Student student = new Student(); student.setStuName("张三"); student.setStuAddress("北京市海淀区"); int addStudent = iStudentService.addStudent(student); System.out.println("添加成功"); } @Test public void getupdateStudent() { ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); IStudentService iStudentService = (IStudentService) ctx.getBean("iStudentService"); Student student = new Student(); student.setStuName("李四"); student.setStuAddress("北京市海淀区"); student.setStuId(5); int updateStudent = iStudentService.updateStudent(student); System.out.println("修改成功"); } @Test public void getdStudent() { ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); IStudentService iStudentService = (IStudentService) ctx.getBean("iStudentService"); int delaccount = iStudentService.dStudent(6); System.out.println("删除成功"); } }