SpringBoot学习（四）操作数据库JdbcTemplate

数据库以MySql为例

一、JDBCTemplate

依赖项

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
</dependency>

配置项

//连接的地址：端口/数据库 jdbc:mysql://127.0.0.1:3306/test
spring.datasource.url=jdbc:mysql://serverIpAddress:yourPortNum/yourDatabase
//用户名
spring.datasource.username=yourUserName
//密码
spring.datasource.password=yourPassword
//驱动
spring.datasource.dirver-class-name=com.mysql.jdbc.Driver

新建一个实体类

这一点又体现了编译型语言必须事先定义的特性。需要把对象和数据关联起来。这个在脚本语言里面可是从来不管的，直接用就完了。

新建实体类Student

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class Student implements RowMapper<Student> {
    private int id;
    private String name;
    private int grand;
    @Override
    public Student mapRow(ResultSet resultSet,int i) throws SQLException{
        Student student = new Student();
        student.setId(resultSet.getInt(id));
        student.setName(resultSet.getString(name));
        student.setGrand(resultSet.getInt(grand));
        return student;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getGrand() {
        return grand;
    }

    public void setGrand(int grand) {
        this.grand = grand;
    }
}

实体类需要继承RowMapper类，需要重写mapRow方法。

数据操作代码，用test写的。

@SpringBootTest
@RunWith(SpringRunner.class)
public class StudentControllerTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Test
    public void createStudentTable() throws Exception{
        String sql = "create table student(" +
                "id int," +
                "name varchar(50) not null," +
                "grand int not null" +
                ")";
        jdbcTemplate.execute(sql);
    }
    @Test
    public void addStudent() throws Exception{
        String sql = "insert into student values(1,'zhangsan',1)";
        jdbcTemplate.update(sql);
    }
    @Test
    public void showAllStudent() throws Exception{
        String sql = "select * from student";
        List<Student> result = jdbcTemplate.query(sql,new BeanPropertyRowMapper(Student.class));
        for(Student student: result){
            System.out.println(student.toString());
        }
    }
    @Test
    public void showStudent() throws Exception{
        String sql = "select * from student where name = ?";
        String name = "zhangsan";
        List<Student> result = jdbcTemplate.query(sql,new Student(),new Object[]{name});
        for(Student student: result){
            System.out.println(student.toString());
        }
    }
}