我的项目结构:
配置文件pom.xml,sqlConfig.xml
pom.xml内容
<properties>
<lombok.version>1.18.8</lombok.version>
<druid.version>1.1.16</druid.version>
<mysql.version>8.0.18</mysql.version>
<spring-jdbc.version>5.3.21</spring-jdbc.version>
<mysql.version>8.0.18</mysql.version>
<junit.version>4.12</junit.version>
</properties>
<dependencies>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring-jdbc.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring-jdbc.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring-jdbc.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring-jdbc.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring-jdbc.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring-jdbc.version}</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
sqlConfig.xml内容
注意换成自己的文件位置 比如class=“practice.service.StudentClassService”
<?xml version="1.0" encoding="utf-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/user_jdbcTemplate?serverTimezone=GMT%2B8" />
<property name="username" value="root" /> <!-- 用户名 -->
<property name="password" value="1234" /> <!-- 密码 -->
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
</bean>
<!-- JdbcTemplate对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入dataSource属性-->
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 关于class-->
<bean id="studentClassService" class="practice.service.StudentClassService">
<property name="classDao" ref="studentClassDao"></property>
</bean>
<bean id="studentClassDao" class="practice.dao.daoImpl.StudentClassDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<!-- 组件扫描 -->
<context:component-scan base-package="practice"></context:component-scan>
</beans>
StudentClass表设计
StudentClass实体
@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentClass {
private Integer id;
private Integer cid;
private String cname;
public StudentClass(Integer cid, String cname) {
this.cid = cid;
this.cname = cname;
}
service
@Autowired
private StudentClassDao classDao;
public void setClassDao(StudentClassDao classDao) {
this.classDao = classDao;
}
// 单个操作
public void addStuClass(StudentClass c){
classDao.addStuClass(c);
}
public void addStuClassIgnoreAuto(StudentClass c){ classDao.addClassIngoreAuto(c);
}
public void updateStuClass(StudentClass c){
classDao.updateStuClass(c);
}
public void updateStuClassById(StudentClass c){ classDao.updateClassById(c);
}
public void deleteStuClass(Integer cid){
classDao.deleteStuClass(cid);
}
//根据cid查询
public StudentClass findStuClass(Integer cid){ return classDao.findById(cid); }
// 批量操作
public void addBatchClass(List<Object[]> classList){
classDao.addBatchClass(classList);
}
public void deleteBatchClass(List<Object[]> cidList){
classDao.deleteBatchClass(cidList);
}
public List<StudentClass> findAllStuClass(){ return classDao.findAllClass();}
dao
public interface StudentClassDao {
//自动增加
void addStuClass(StudentClass c);
void addClassIngoreAuto(StudentClass c);
//根据cid
void updateStuClass(StudentClass c);
void updateClassById(StudentClass c);
void deleteStuClass(Integer cid);
void addBatchClass(List<Object[]> classlist);
void deleteBatchClass(List<Object[]> cidlist);
StudentClass findById(Integer cid);
List<StudentClass> findAllClass();
}
daoImpl
public class StudentClassDaoImpl implements StudentClassDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void addStuClass(StudentClass c) {
String sql="INSERT INTO class(cid,cname) VALUES(?,?)";
int i = jdbcTemplate.update(sql, new Object[]{c.getCid(), c.getCname()});
System.out.println(i);
}
public void addClassIngoreAuto(StudentClass c) {
String sql="INSERT INTO class(id,cid,cname) VALUES(?,?,?)";
int i = jdbcTemplate.update(sql, new Object[]{c.getId(),c.getCid(), c.getCname()});
System.out.println(i);
}
public void updateStuClass(StudentClass c) {
String sql="UPDATE class SET cname=? WHERE cid=? ";
int i = jdbcTemplate.update(sql, new Object[]{c.getCname(),c.getCid()});
System.out.println(i);
}
public void updateClassById(StudentClass c) {
String sql="UPDATE class SET cid=? , cname=? WHERE id=? ";
int i = jdbcTemplate.update(sql, new Object[]{c.getCid(),c.getCname(),c.getId()});
System.out.println(i);
}
public void deleteStuClass(Integer cid) {
String sql="DELETE FROM class WHERE cid=? ";
int i = jdbcTemplate.update(sql,cid);
System.out.println(i);
}
public void addBatchClass(List<Object[]> classlist) {
String sql="INSERT INTO class(cid,cname) VALUES(?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, classlist);
System.out.println(ints);
}
public void deleteBatchClass(List<Object[]> cidlist) {
String sql="DELETE FROM class WHERE cid=?";
int[] ints=jdbcTemplate.batchUpdate(sql, cidlist);
System.out.println(ints);
}
public StudentClass findById(Integer cid) {
String sql="SELECT FROM class WHERE id=?";
StudentClass ints=jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<StudentClass>(StudentClass.class),cid);
System.out.println(ints);
return ints;
}
public List<StudentClass> findAllClass() {
String sql="SELECT * FROM class";
List<StudentClass> ints=jdbcTemplate.query(sql,new BeanPropertyRowMapper<StudentClass>(StudentClass.class));
System.out.println(ints);
return ints;
}
}
增加操作
如果ID不同 自动增加和手动插入 不冲突 并且下次自动增加按照最大号继续增加
@Test
//id自动增加
public void testAddStuClass(){
ApplicationContext context = new ClassPathXmlApplicationContext("sqlConfig.xml");
StudentClassService studentClassService=context.getBean("studentClassService", StudentClassService.class);
studentClassService.addStuClass(new StudentClass(001,"一班"));
}
@Test
//手动插入id
public void testAddStuClassIgnoreAuto(){
ApplicationContext context = new ClassPathXmlApplicationContext("sqlConfig.xml");
StudentClassService studentClassService=context.getBean("studentClassService", StudentClassService.class);
studentClassService.addStuClassIgnoreAuto(new StudentClass(5,001,"一班"));
}
执行流程:2次testAddStuClass–>1次 testAddStuClassIgnoreAuto->1次testAddStuClass
当手动添加中间数ID 也可以成功
共同点:这两种方式可以共存
删除id=5—>自动增加—>手动添加
如果自动增加和手动增加 id相同(因为主键的唯一性 自然不能重复添加)
修改操作
@Test
//根据cid修改cname
public void testUpdateClass(){
ApplicationContext context = new ClassPathXmlApplicationContext("sqlConfig.xml");
StudentClassService studentClassService=context.getBean("studentClassService", StudentClassService.class);
studentClassService.updateStuClass(new StudentClass(002,"二班"));
}
@Test
//根据Id修改cid
public void testUpdate(){
ApplicationContext context = new ClassPathXmlApplicationContext("sqlConfig.xml");
StudentClassService studentClassService=context.getBean("studentClassService", StudentClassService.class);
studentClassService.updateStuClassById(new StudentClass(5,003,"一班"));
}
删除操作
确定选用什么字段来删除,避免误删
@Test
//根据cid删除
public void testDeleteClass(){
ApplicationContext context = new ClassPathXmlApplicationContext("sqlConfig.xml");
StudentClassService studentClassService=context.getBean("studentClassService", StudentClassService.class);
studentClassService.deleteStuClass(1);
}
批量操作
@Test
//批量增加
public void testAddBatchStuClass(){
ApplicationContext context = new ClassPathXmlApplicationContext("sqlConfig.xml");
StudentClassService studentClassService=context.getBean("studentClassService", StudentClassService.class);
List<Object[]> list=new ArrayList<Object[]>();
Object[] object1 = {2, "二班"};
Object[] object2 = {3, "三班"};
Object[] object3 = {4, "四班"};
Object[] object4 = {5, "五班"};
Object[] object5 = {6, "六班"};
Object[] object6 = {7, "七班"};
Object[] object7 = {8, "八班"};
list.add(object1);
list.add(object2);
list.add(object3);
list.add(object4);
list.add(object5);
list.add(object6);
list.add(object7);
studentClassService.addBatchClass(list);
}
@Test
//批量删除
public void testDeleteBatchStuClass(){
ApplicationContext context = new ClassPathXmlApplicationContext("sqlConfig.xml");
StudentClassService studentClassService=context.getBean("studentClassService", StudentClassService.class);
List<Object[]> list=new ArrayList<Object[]>();
Object[] object1 = {2};
Object[] object2 = {3};
Object[] object3 = {4};
list.add(object1);
list.add(object2);
list.add(object3);
studentClassService.deleteBatchClass(list);
}
@Test
//批量查询
public void testFindAllStuClass(){
ApplicationContext context = new ClassPathXmlApplicationContext("sqlConfig.xml");
StudentClassService studentClassService=context.getBean("studentClassService", StudentClassService.class);
List<StudentClass> list=studentClassService.findAllStuClass();
System.out.println(list);
}
打印两遍是因为在daoImpl也打印
提示:避免使用关键词class 基本功不扎实—一扎头就是班级class 考虑的少
许多错误是因为sql语句,或缺失逗号,或缺少型号 或缺少分号
多动手,眼睛要看,手要练