使用Spring JdbcTemplate实现学生信息的新增、修改、删除、查询单个、查询所有功能,并实现MySQL数据库的操作。
学生信息,有主键、姓名、年龄信息。
1. 数据初始化
- 创建student数据库
create database student default charset utf8 collate utf8_general_ci;
- 创建t_student表
createtable t_student ( id varchar(32)notnull, name varchar(255), age int);
- 初始化数据
insertinto t_student values("1","lucy",19);insertinto t_student values("2","lili",20);
2. 创建项目
创建Java项目,项目名称为spring-jdbc。
3. 导入Spring相关jar包
spring-jdbc项目下创建lib目录,在lib目录下导入Jar包:
- 核心包
spring-core-5.3.25.jar、
spring-beans-5.3.25.jar、
spring-context-5.3.25.jar、
spring-expression-5.3.25.jar
- AOP包
spring-aop-5.3.25.jar
- 数据库包
spring-jdbc-5.3.25.jar
spring-tx-5.3.25.jar
mysql-connector-java-8.0.27.jar
druid-1.2.8.jar
- 测试包
junit-4.6.jar
- 依赖包
commons-logging-1.2.jar
4. 创建Spring配置文件
src下创建applicationContext.xml文件。
<beansxmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd"><!--开启注解--><context:component-scanbase-package="com.wfit"/><!--配置数据源--><beanid="dataSource"class="com.alibaba.druid.pool.DruidDataSource"><!--mysql驱动--><propertyname="driverClassName"value="com.mysql.cj.jdbc.Driver"/><!--连接数据库的url--><propertyname="url"value="jdbc:mysql://localhost:3306/student"/><!--用户名--><propertyname="username"value="root"/><!--密码--><propertyname="password"value="123456"/></bean><!--配置JdbcTemplate--><beanid="jdbcTemplate"class="org.springframework.jdbc.core.JdbcTemplate"><!--注入dataSource--><propertyname="dataSource"ref="dataSource"/></bean></beans>
5. 创建Student实体类
src目录下创建com.wfit.entity包,此包目录下目录下创建Student.java类。
publicclassStudent { //主键idprivateStringid; //姓名privateStringname; //年龄privateintage; publicStringgetId() { returnid; } publicvoidsetId(Stringid) { this.id=id; } publicStringgetName() { returnname; } publicvoidsetName(Stringname) { this.name=name; } publicintgetAge() { returnage; } publicvoidsetAge(intage) { this.age=age; } publicStringtoString() { return"Student{"+"id='"+id+'\''+", name='"+name+'\''+", age="+age+'}'; } }
6. 创建StudentDao类
src目录下创建com.wfit.dao包,此包目录下目录下创建StudentDao.java类。
publicclassStudentDao { /*注入jdbcTemplate*/privateJdbcTemplatejdbcTemplate; /*** 新增* @param student*/publicvoidaddStudent(Studentstudent){ Stringsql="insert into t_student values(?,?,?)"; Object[] args= {student.getId(), student.getName(), student.getAge()}; jdbcTemplate.update(sql,args); System.out.println("新增数据成功!"); } /*** 修改* @param student*/publicvoidupdateStudent(Studentstudent){ Stringsql="update t_student set name = ?,age = ? where id = ?"; Object[] args= {student.getName(),student.getAge(),student.getId()}; jdbcTemplate.update(sql,args); System.out.println("修改数据成功!"); } /*** 删除* @param id*/publicvoiddelStudent(Stringid){ Stringsql="delete from t_student where id = ?"; jdbcTemplate.update(sql,id); System.out.println("删除数据成功!"); } /*** 查询单个* @param id* @return*/publicStudentqueryStudent(Stringid){ Stringsql="select * from t_student where id = ?"; Studentstudent=null; try{ student=jdbcTemplate.queryForObject(sql, newBeanPropertyRowMapper<Student>(Student.class), id); }catch(Exceptione){ System.out.println("查询异常:"+e.getMessage()); } returnstudent; } /*** 查询所有* @return*/publicList<Student>queryStudentAll(){ Stringsql="select * from t_student"; List<Student>studentList=jdbcTemplate.query(sql, newBeanPropertyRowMapper<Student>(Student.class)); returnstudentList; } }
7. 创建StudentService接口
src目录下创建com.wfit.service包,此包目录下创建StudentService.java接口。
publicinterfaceStudentService { publicvoidaddStudent(Studentstudent); publicvoidupdateStudent(Studentstudent); publicvoiddelStudent(Stringid); publicStudentqueryStudent(Stringid); publicList<Student>queryStudentAll(); }
8. 创建StudentServiceImpl类
src目录下创建com.wfit.service.impl包,此包目录下创建StudentServiceImpl.java类实现StudentService接口。
publicclassStudentServiceImplimplementsStudentService { privateStudentDaostudentDao; /*** 新增* @param student*/publicvoidaddStudent(Studentstudent) { studentDao.addStudent(student); } /*** 修改* @param student*/publicvoidupdateStudent(Studentstudent) { studentDao.updateStudent(student); } /*** 删除* @param id*/publicvoiddelStudent(Stringid) { studentDao.delStudent(id); } /*** 查询单个* @param id* @return*/publicStudentqueryStudent(Stringid) { returnstudentDao.queryStudent(id); } /*** 查询所有* @return*/publicList<Student>queryStudentAll() { returnstudentDao.queryStudentAll(); } }
9. 编写测试类
src目录下创建com.wfit.test包,此包目录下创建StudentTest.java类。
publicclassStudentTest { privateStudentServicestudentService; publicvoidinit(){ ApplicationContextcontext=newClassPathXmlApplicationContext("applicationContext.xml"); studentService=context.getBean("studentServiceImpl", StudentService.class); } /*** 测试新增*/publicvoidtestAdd(){ Studentstudent=newStudent(); student.setId("3"); student.setName("zhangsan"); student.setAge(20); studentService.addStudent(student); } /*** 测试修改*/publicvoidtestUpdate(){ Studentstudent=newStudent(); student.setId("3"); student.setName("lisi"); student.setAge(19); studentService.updateStudent(student); } /*** 测试删除*/publicvoidtestDel(){ Stringid="3"; studentService.delStudent("3"); } /*** 测试查询*/publicvoidtestQuery(){ Studentstudent=studentService.queryStudent("1"); System.out.println(student); } /*** 测试查询所有*/publicvoidtestQueryAll(){ List<Student>studentList=studentService.queryStudentAll(); System.out.println(studentList); } }
10. 执行结果
- 执行testAdd结果
- 执行testUpdate结果
- 执行testDel结果
- 执行testQuery结果
- 执行testQueryAll结果