Spring Boot consolidated jdbctemplate Access database

This article is about Spring boot integration JdbcTemplate, configuring the data source to access the database.

Add spring-boot-starter-jdbc and MySQL dependencies to the pom file.

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

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.21</version>
</dependency>

Configure Data Source: Configure Data source information in Src/main/resources/application.properties
```
Spring.datasource.url=jdbc:mysql://localhost:3306/springboot_demo
Spring.datasource.username=root
Spring.datasource.password=root
Spring.datasource.driver-class-name=com.mysql.jdbc.driver
```

Build table:
```
DROP TABLE IF EXISTS Student;

CREATE TABLE Student (
ID Int (5) is not NULL,
Name varchar (not NULL),
Age Int (2) is not NULL,
Score double (5,2) not NULL,
PRIMARY KEY (ID)
) ;

Startup class:

Package Cn.yideng;

Import org.springframework.boot.SpringApplication;
Import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
Import org.springframework.boot.autoconfigure.SpringBootApplication;
Import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication (exclude = {Datasourceautoconfiguration.class})
@EnableAutoConfiguration
public class DemoApplication {

public static void Main (string[] args) {
Springapplication.run (Demoapplication.class, args);
}
}

Entity class:

public class Student {

private int id;
private String name;
private int age;
private double score;

Public Student () {}

public Student (int ID, String name, Int. age, double score) {
This.id = ID;
THIS.name = name;
This.age = age;
This.score = score;
}

Omit Getter/setter, toString
}

Service

Public interface Studentservice {

New
void Create (Student Student);

Querying All records
List<student> GetAll ();

Delete all
void Deleteallusers ();

Modify by ID
void Updatebyid (Student s);

Query by ID
Student Getonebyid (int id);

}

Service Implementation class:

Import org.springframework.beans.factory.annotation.Autowired;
Import Org.springframework.jdbc.core.BeanPropertyRowMapper;
Import Org.springframework.jdbc.core.JdbcTemplate;
Import Org.springframework.stereotype.Service;

@Service (value= "Studentservice")
public class Studentserviceimpl implements studentservice{

@Autowired
Private JdbcTemplate JdbcTemplate;

@Override
public void Create (Student s) {
String sql = "INSERT into STUDENT (ID, NAME, age, score) VALUES (?,?,?,?)";
Jdbctemplate.update (SQL, S.getid (), S.getname (), S.getage (), S.getscore ());
}

@Override
Public list<student> GetAll () {
String sql = "SELECT * from STUDENT";
return jdbctemplate.query (SQL, New Beanpropertyrowmapper (Student.class));
}

@Override
public void Deleteallusers () {
String sql = "Delete from STUDENT";
Jdbctemplate.execute (SQL);
}

@Override
public void Updatebyid (Student s) {
String sql = "Update STUDENT set name=?", age=?, score=? where id=? ";
Jdbctemplate.update (SQL, New Object[]{s.getname (), S.getage (), S.getscore (), S.getid ()});
}

@Override
Public Student Getonebyid (int id) {
String sql = "SELECT * from STUDENT where id =?";
return jdbctemplate.queryforobject (SQL, New Object[]{id}, new Beanpropertyrowmapper<> (Student.class));
}

}

Using JUnit testing, add dependencies to the Pom file first

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>

Write the test class,

Import java.util.List;
Import Org.junit.Test;
Import Org.junit.runner.RunWith;
Import org.springframework.beans.factory.annotation.Autowired;
Import Org.springframework.boot.test.context.SpringBootTest;
Import Org.springframework.test.context.junit4.SpringRunner;
Import org.springframework.test.context.web.WebAppConfiguration;

@RunWith (Springrunner.class)
@SpringBootTest (Classes=demoapplication.class)
@WebAppConfiguration
public class Jdbctest {

@Autowired
Private Studentservice Studentservice;

@Test
public void Test () throws Exception {

Delete all data first
Studentservice.deleteallusers ();

Student S1 = new Student (1001, "Andy Lau", 11, 80.00);
Student s2 = new Student (1002, "Lucy 1", 12, 85.00);
Student s3 = new Student (1003, "Lily", 13, 75.00);
Student S4 = new Student (1004, "Tom KK", 14, 94.00);
Studentservice.create (S1);
Studentservice.create (S2);
Studentservice.create (S3);
Studentservice.create (S4);

Query all
list<student> stulist = Studentservice.getall ();
for (Student stu:stulist) {
System.out.println (Stu);
}

Query by ID
Student stu = Studentservice.getonebyid (1003);
SYSTEM.OUT.PRINTLN ("-------before update" + stu);
Stu.setname ("Lily Green");
Stu.setscore (90.00);
Modify by ID
Studentservice.updatebyid (Stu);
SYSTEM.OUT.PRINTLN ("-------after Update" + stu);

}
}

Spring Boot consolidated jdbctemplate Access database