JdbcTemplate增删改查案例

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("删除成功");
    }

}