数据库以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());
}
}
}