spring Jdbc

--------------------------------SpringJdbc--------------------------------

创建数据库

DROP DATABASE IF EXISTS studb;

CREATE DATABASE studb;

USE studb;

CREATE TABLE student (

id INT PRIMARY KEY,

NAME VARCHAR(50) NOT NULL,

gender VARCHAR(10) NOT NULL,

age INT,

classes VARCHAR(50)

);

INSERT INTO student (id, NAME, gender, age, classes)

VALUES

(1, '张三', '男', 20, '高中一班'),

(2, '李四', '男', 19, '高中二班'),

(3, '王五', '女', 18, '高中一班'),

(4, '赵六', '女', 20, '高中三班'),

(5, '刘七', '男', 19, '高中二班'),

(6, '陈八', '女', 18, '高中一班'),

(7, '杨九', '男', 20, '高中三班'),

(8, '吴十', '男', 19, '高中二班');

2. 创建项目使用SpringJdbc完成学生增删改查实现步骤

导入springjdbc相关坐标

<dependencies>
    
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>6.0.6</version>
    </dependency>
    
    <dependency>
        <groupId>jakarta.annotation</groupId>
        <artifactId>jakarta.annotation-api</artifactId>
        <version>2.1.1</version>
    </dependency>
    
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.26</version>
    </dependency>
    
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>6.0.6</version>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.25</version>
    </dependency>
    
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.8</version>
    </dependency>
    
    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter-api</artifactId>
        <version>5.3.1</version>
        <scope>test</scope>
    </dependency>


    <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring-test</artifactId>
       <version>6.0.6</version>
    </dependency>


    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-classic</artifactId>
        <version>1.2.3</version>
    </dependency>
</dependencies>

2. 引入logback.xml 和jdbc.properties文件到resources目录下

jdbc.url=jdbc:mysql://localhost:3306/studb?serverTimezone=UTC
jdbc.userName=root
jdbc.userPwd=
jdbc.driverClass=com.mysql.cj.jdbc.Driver

3. 注解+xml配置文件或注解+配置类的方式实现相关配置

3.1 注解+xml配置文件方式

创建spring配置文件applicationContext.xml，JdbcTemplate模板对象配置

<?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"
       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-scan base-package="com.bdqn.service.impl,com.bdqn.dao.impl"/>

    
    <context:property-placeholder location="jdbc.properties"></context:property-placeholder>

    
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="url" value="${jdbc.url}"></property>
        <property name="driverClassName" value="${jdbc.driverClass}"></property>
        <property name="username" value="${jdbc.userName}"></property>
        <property name="password" value="${jdbc.userPwd}"></property>
    </bean>

    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

2. 测试类中获取配置文件

@SpringJUnitConfig(locations= {"classpath:applicationContext.xml"})

3.2 注解+配置类的实现方式

1. 创建一个配置类，创建JdbcTemplate模板对象

package com.bdqn.config;
//配置类
@Configuration

//开启注解扫描
@ComponentScan(basePackages = {"com.bdqn.dao.impl","com.bdqn.service.impl"})

//加载读取配置文件
@PropertySource("classpath:jdbc.properties")
public class SpringConfig {
    //@Value 通常用于注入外部化属性
    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.driverClass}")
    private String driver;
    @Value("${jdbc.userName}")
    private String username;
    @Value("${jdbc.userPwd}")
    private String password;

    /**
     * 创建数据源对象
     * @Bean 注释用于指示方法实例化、配置和初始化要由 Spring IoC 容器管理的新对象。
     * 对于那些熟悉 Spring 的 <beans/> XML 配置的人来说， @Bean 注释与 <bean/> 元素起着相同的作用。
     */
    @Bean
    public DataSource dataSource(){
        DruidDataSource dataSource=new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setDriverClassName(driver);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }

    /**
     * jdbcTemplate:jdbc模版对象
     */
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource){
        JdbcTemplate jdbcTemplate=new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource);
        return jdbcTemplate;
    }
}

2. 测试类中获取配置类

@SpringJUnitConfig(classes = SpringConfig.class)

4. 学生实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Student {
    private String id;
    private String name;
    private String gender;
    private int age;
    private String classes;
}

5. dao层使用jdbcTemplate实现增加、删除、修改、查询的操作

1. 增加、删除、修改调用int jdbcTemplate.update(sql,Object...args),返回受影响的行数

2. 查询返回基本/String类型结果，

调用jdbcTemplate.queryForObject(sql,Object...args,Class returnType)

3. 查询操作，返回一行记录的结果，封装成一个对象

调用jdbcTemplate.queryForObject(sql,new RowMapper(),Object...args)

注意：BeanPropertyRowMapper要求对象属性名必须和表的字段名完全一致

4. 查询操作，返回多行记录的结果集，封装成一个对象的集合

调用jdbcTemplate.query(sql,new RowMapper(),Object...args)

dao接口

public interface StudentDao {
    public int addStudent(Student student);
    public int updateStudent(Student student);
    public int deleteStudent(int id);
    public int selectCount();
    public Student selectById(int id);
    public List<Student> selectAll();
}

dao实现类

@Repository("studentDao")
public class StudentDaoImpl implements StudentDao {
    @Autowired
    JdbcTemplate jdbcTemplate;

    /**
     * 添加学生信息
     */
    @Override
    public int addStudent(Student student) {
        String sql="INSERT INTO student (id, NAME, gender, age, classes) VALUES (?, ?, ?, ?, ?)";
        int row=jdbcTemplate.update(sql,student.getId(),student.getName(),student.getGender(),student.getAge(),student.getClasses());
        return row;
    }

    /**
     * 修改学生信息
     */
    @Override
    public int updateStudent(Student student) {
        String sql="update student set NAME=?,gender=?,age=?,classes=? where id=?";
        int row=jdbcTemplate.update(sql,student.getName(),student.getGender(),student.getAge(),student.getClasses(),student.getId());
        return row;
    }

    /**
     * 删除学生信息
     */
    @Override
    public int deleteStudent(int id) {
        String sql="delete from student where id=?";
        int row=jdbcTemplate.update(sql,id);
        return row;
    }

    /**
     * 查询学生的人数
     */
    @Override
    public int selectCount() {
        String sql="select count(*) from student";
        int count=jdbcTemplate.queryForObject(sql,Integer.class);
        return count;
    }

    /**
     * 根据编号查询一个学生信息
     */
    @Override
    public Student selectById(int id) {
        String sql="select * from student where id=?";
        /*
            //根据id查询
            Student student = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
                //自己处理结果映射
                Student stu = new Student();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
                stu.setGender(rs.getString("gender"));
                stu.setClasses(rs.getString("classes"));
            return stu;
            }, 2);
         * BeanPropertyRowMapper:指定java对象和row记录的映射关系
         * 根据：表字段名-->对象属性名进行映射
         * dname--->dname 或者max_age--->maxAge
         */
        Student stu=jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Student.class),id);
        return stu;
    }

    /**
    * 查询所有学生信息
    */
    @Override
    public List<Student> selectAll() {
        String sql="select * from student";
        List<Student> list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Student.class));
        return list;
    }
}

6. Service层

接口

public interface StudentService {
    public int addStudent(Student student);
    public int updateStudent(Student student);
    public int deleteStudent(int id);
    public int selectCount();
    public Student selectById(int id);
    public List<Student> getStudents();
}

实现类

@Service
public class StudentServiceImpl implements StudentService {
    //@Autowired
    @Resource
    StudentDao studentDao;

    @Override
    public int addStudent(Student student) {
        return studentDao.addStudent(student);
    }

    @Override
    public int updateStudent(Student student) {
        return studentDao.updateStudent(student);
    }

    @Override
    public int deleteStudent(int id) {
        return studentDao.deleteStudent(id);
    }

    @Override
    public int selectCount() {
        return studentDao.selectCount();
    }

    @Override
    public Student selectById(int id) {
        return studentDao.selectById(id);
    }

    @Override
    public List<Student> getStudents() {
        return studentDao.selectAll();
    }
}

7. 测试类

//@SpringJUnitConfig(locations = "classpath:applicationContext.xml")

@SpringJUnitConfig(classes = SpringConfig.class)
public class TestDemo1 {
    @Autowired
    StudentService studentService;
    /*添加学生信息*/
    @Test
    public void test01(){
        Student stu=new Student(9,"吴王", "男", 29, "高中二班");
        int row=studentService.addStudent(stu);
        System.out.println(row>0?"添加成功":"添加失败");
    }
    /*修改学生信息*/
    @Test
    public void test02(){
        Student stu=new Student(9,"吴小王", "女", 19, "高中一班");
        int row=studentService.updateStudent(stu);
        System.out.println(row>0?"修改成功":"修改失败");
    }
    /*删除学生信息*/
    @Test
    public void test03(){
        int row=studentService.deleteStudent(9);
        System.out.println(row>0?"删除成功":"删除失败");
    }

    /*查询学生人数*/
    @Test
    public void test04(){
        int count=studentService.selectCount();
        System.out.println("学生人数："+count);
    }
    /*查询单个学生信息*/
    @Test
    public void test05(){
        Student stu=studentService.selectById(8);
        System.out.println(stu);
    }

    /**
     * 查询所有学生信息
     */
    @Test
    public void test06(){
        List<Student> list=studentService.getStudents();
        for (Student stu:list) {
            System.out.println(stu);
        }

    }
}