Configure the MySQL data source and pre-configuration
1. Introduce dependencies
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<! -- Introducing JDBC support -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<! Connect to MySQL database -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
Copy the code2. Use MySQL as the data source
spring:
datasource:
username: root
password: root123
url: JDBC: mysql: / / 127.0.0.1:3309 / test? useUnicode=true&characterEncoding=utf8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
Copy the codeThe Driver class com.mysql.cj.jdbc.driver is used when using newer versions of the MySQL Driver. Some older drivers connect to the mysql5.x Driver com.mysql.jdbc.driver.
By default, SpringBoot uses the Hikari connection pool, which is the highest performance of any connection pool. Of course, Ali’s Druid connection pool is popular in China. We use the default Hikari connection pool and default connection pool configuration for SpringBoot.
3. Write configuration classes to detect database connection pool types
@Configuration
@Slf4j
public class DatasourceType implements ApplicationContextAware {
@Override
public void setApplicationContext(ApplicationContext context) throws BeansException {
DataSource dataSource = context.getBean(DataSource.class);
log.info("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -");
log.info("using datasource {}", dataSource.getClass().getName());
log.info("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -"); }}Copy the codeIn the above class, the interface ApplicationContextAware is implemented. The setApplicationContext method is used to manipulate the Spring context, so we can get the injected data source in the context and print it in the log. The @slf4J annotation was introduced to rely on lombok’s functionality, which is not covered here.
Student table 4
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `t_student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int NULL DEFAULT NULL.PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
Copy the code5. Create entity classes
@Data
public class Student {
private Long id;
private String name;
private String gender;
private Integer age;
}
Copy the code6. Write the SpringBoot boot class
@SpringBootApplication
public class JdbcApplication {
public static void main(String[] args) { SpringApplication.run(JdbcApplication.class, args); }}Copy the codeMySQL > select * from JdbcTemplate
At the beginning of Spring learning, we operate the database are native JDBC to operate, those steps will be very troublesome, query results after processing is also very tedious, let alone transaction processing. Spring encapsulates JDBC as a JdbcTemplate. Some common operations can be performed by JdbcTemplate. There are other templates in Spring such as RestTemplate that encapsulate an Http client. This is consistent with SpringBoot’s philosophy of minimizing programmer configuration, and then using the JdbcTemplate directly.
1. Write the service result interface
public interface IStudentService {
Student findStudentById(Long id);
List<Student> findUsers(String name);
int insertStudent(Student student);
int updateStudent(Student student);
int deleteStudent(Long id);
}
Copy the codeIStudentService implementation class
@Service
public class StudentServiceImpl implements IStudentService {
@Autowired
private JdbcTemplate jdbcTemplate;
/** * get the mapping **@return* /
private RowMapper<Student> getStudentMapper(a) {
return (resultSet, i) -> {
Student student = new Student();
student.setId(resultSet.getLong("id"));
student.setName(resultSet.getString("name"));
student.setAge(resultSet.getInt("age"));
student.setGender(resultSet.getString("gender"));
return student;
};
}
@Override
public Student findStudentById(Long id) {
String sql = "select id,name,gender,age from t_student where id = ?";
Object[] param = new Object[]{id};
return jdbcTemplate.queryForObject(sql, param, getStudentMapper());
}
@Override
public List<Student> findUsers(String name) {
String sql = "select id,name,gender,age from t_student " +
"where name like concat('%',? , '%')";
Object[] param = new Object[]{name};
return jdbcTemplate.query(sql, param, getStudentMapper());
}
@Override
public int insertStudent(Student student) {
String sql = "insert into t_student(name,gender,age) value(? ,? ,?) ";
return jdbcTemplate.update(sql, student.getName(), student.getGender(), student.getAge());
}
@Override
public int updateStudent(Student student) {
String sql = "update t_student set name=? ,gender=? ,age=? where id=?";
return jdbcTemplate.update(sql, student.getName(), student.getGender(), student.getAge(), student.getId());
}
@Override
public int deleteStudent(Long id) {
String sql = "delete from t_student where id = ?";
returnjdbcTemplate.update(sql, id); }}Copy the codeThe mapping of the JdbcTemplate requires the developer to implement the RowMapper interface, so that the relational mapping of the table to the entity class can be completed. Lambda expressions are used for RowMapper above, and Java8 or later is required.
SQL > alter table student; SQL > alter table student; SQL > alter table student;
3. Write unit tests
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestApplication {
@Autowired
private IStudentService studentService;
@Test
public void test1(a) {
Student student = new Student();
student.setName("tom");
student.setGender("Male");
student.setAge(18);
studentService.insertStudent(student);
student.setAge(20);
studentService.insertStudent(student);
student.setAge(22);
studentService.insertStudent(student);
}
@Test
public void test2(a) {
Student student = studentService.findStudentById(1L);
System.out.println(student.toString());
List<Student> all = studentService.findAll();
all.forEach(e -> System.out.println(e.toString()));
}
@Test
public void test3(a) {
Student student = new Student();
student.setId(1L);
student.setName("jack");
student.setGender("Male");
student.setAge(18);
studentService.updateStudent(student);
Student db = studentService.findStudentById(1L);
System.out.println(db.toString());
}
@Test
public void test4(a) {
List<Student> tom = studentService.findUsers("tom");
tom.forEach(e -> System.out.println(e.toString()));
}
@Test
public void test5(a) {
studentService.deleteStudent(3L); List<Student> all = studentService.findAll(); all.forEach(student -> System.out.println(student.toString())); }}Copy the codeIn the test3 method above, two SQL statements are executed, initiated by the following two methods. On the surface, these two SQL statements are executed in the same method, but they are executed using different database connections. When JdbcTemplate executes the following update method, it takes a connection from the database connection pool to execute the SQL, and then closes the database connection. When executed to queryForObject, a connection is retrieved from the database connection pool to execute the SQL.
This is obviously a waste of resources, so we definitely want to execute multiple SQL in a single database connection. Callbacks can be implemented using either the StatementCallback or ConnectionCallback interfaces.
jdbcTemplate.update(sql, student.getName(), student.getGender(), student.getAge(), student.getId());
jdbcTemplate.queryForObject(sql, param, getStudentMapper());
Copy the codeUsing StatementCallback:
public Student findStudentById2(Long id) {
return jdbcTemplate.execute(new StatementCallback<Student>() {
@Override
public Student doInStatement(Statement statement) throws SQLException, DataAccessException {
String sql1 = "select count(*) total from t_student where id = " + id;
ResultSet resultSet1 = statement.executeQuery(sql1);
while (resultSet1.next()) {
int total = resultSet1.getInt("total");
log.info("Total: {}", total);
}
String sql2 = "select id,name,gender,age from t_student where id = " + id;
ResultSet resultSet2 = statement.executeQuery(sql2);
Student student = new Student();
while (resultSet2.next()) {
int row = resultSet2.getRow();
student = getStudentMapper().mapRow(resultSet2, row);
}
returnstudent; }}); }Copy the codeUse ConnectionCallback
public Student findStudentById3(Long id) {
return jdbcTemplate.execute(new ConnectionCallback<Student>() {
@Override
public Student doInConnection(Connection connection) throws SQLException, DataAccessException {
String sql1 = "select count(*) total from t_student where id = ?";
PreparedStatement statement1 = connection.prepareStatement(sql1);
statement1.setLong(1, id);
ResultSet resultSet1 = statement1.executeQuery();
while (resultSet1.next()) {
int total = resultSet1.getInt("total");
log.info("Total: {}", total);
}
String sql2 = "select id,name,gender,age from t_student where id = ?";
PreparedStatement statement2 = connection.prepareStatement(sql2);
statement2.setLong(1, id);
ResultSet resultSet2 = statement2.executeQuery();
Student student = new Student();
while (resultSet2.next()) {
int row = resultSet2.getRow();
student = getStudentMapper().mapRow(resultSet2, row);
}
returnstudent; }}); }Copy the code3. Use JPA to operate database
Java Persistence API (JPA) Java Persistence API. ORM is a set of official Java ORM solutions. Object Relational Mapping (ORM) associates data tables with entity classes before operating databases. Then through the entity class object operation (add, delete, change, check) database table; So, ORM is a design idea that implements the use of objects to manipulate databases. At present, the mainstream ORM frameworks include Hibernate (JBoos), EclipseTop (Eclipse community), OpenJPA (Apache Foundation), And Hibernate is the best among many implementers.
This is enhanced by the Spring community’s integration with Hibernate, on which JPA is implemented. Developers using JPA can manipulate the entity class without writing SQL, which is used through a Persistence Context. This context contains three parts:
- Object relational mapping (ORM) description, JPA supports annotations or XML in both forms of description, in Spring Boot mainly through annotations.
- Entity operation API, realization of entity object CRUD operation, to complete the object persistence and query.
- The Java Persistent Query Language (JPQL) is an object-oriented Query Language. Through this relationship, flexible queries can be realized.
3.1 Configuration of MySQL data source and JPA
spring:
datasource:
username: root
password: root123
url: JDBC: mysql: / / 127.0.0.1:3306 / test? useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
jpa:
show-sql: true
hibernate:
ddl-auto: update
naming:
# Hump named mapping
physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
Copy the codeSpring.jpa. show-sql=true Outputs SQL to log display when executing SQL using JPA. Spring.jpa.hibernate. DDL -auto=update If the table corresponding to the entity class does not exist, the table will be created automatically.
None: disables DDL processing
Validate: Verifies the schema without making any changes to the database
Update: created when the table does not exist
Create: The table is deleted and recreated each time it is started
Create-drop: Deletes the table after each session
Spring.jpa.naming is the mapping of entity class naming and database fields. There is physical-strategy and implicit-strategy.
- The first step: if we don’t use @ Table or @ Column specifies the name of the Table or fields, by SpringImplicitNamingStrategy implicit processing for us, the Table name implicit processing for the name of the class, the Column name implicit processing for the field name. If you specify the table name, SpringImplicitNamingStrategy doesn’t work.
- Step 2: Resolve the logical names processed above into physical names. No matter whether in the entity specified table name, SpringPhysicalNamingStrategy will be invoked.
So if we want to customize our naming strategy, we can inherit both according to our own needs, And by the spring in the configuration file. The jpa. Hibernate. Naming. Implicit – strategy or spring. Jpa, hibernate. Naming. Physical – strategy Do your own policy (for example, add a specified prefix to the table name).
3.2 JPA development actual combat
1. Write entity classes
@Data
@Entity
@Table(name = "t_student")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@Convert(converter = SexConverter.class)
private SexEnum gender;
private Integer age;
}
Copy the code@data generates getters, setters, and toString methods at compile time. Entity declares that this class is an Entity class. @table (name = “t_student”) declares the Table associated with this class. @ID declares the primary key, and @GeneratedValue(Strategy = GenerationType.identity) declares that the generation strategy for the primary key is to use the database increment Id.
Gender enumeration class
@getter Public enum GenderEnum {/** * 1 GenderEnum */ FEMALE(2," FEMALE "); /** * 2 GenderEnum {/** * 2 GenderEnum */ FEMALE(2," FEMALE "); private Integer id; private String name; GenderEnum(Integer id, String name) { this.id = id; this.name = name; } public static GenderEnum getSexById(int id){ for (GenderEnum genderEnum : GenderEnum.values()) { if (genderEnum.getId()==id){ return genderEnum; } } return null; }}Copy the codeCustom gender converter
public class GengerConverter implements AttributeConverter<GenderEnum.Integer> {
@Override
public Integer convertToDatabaseColumn(GenderEnum genderEnum) {
return genderEnum.getId();
}
@Override
public GenderEnum convertToEntityAttribute(Integer id) {
returnGenderEnum.getSexById(id); }}Copy the code2. Define the JPA interface
Repository is the top interface of the Spring Data project. It does not define methods. Its subinterface CrudRepository defines the basic methods of adding, deleting, and modifying entities. PagingAndSortingRepository and extending the CrudRepository function provides the function of paging and sorting, Finally JpaRepository integrates PagingAndSortingRepository and QueryByExampleExecutor at the same time, has the function of according to the example of the query. Generally we just need to extend the JpaRepository interface.
To define an interface that operates on the Student entity class, you simply inherit from JpaRepository. So we can use the methods that Spring Data Jpa helped me implement.
@Repository
public interface StudentRepository extends JpaRepository<Student.Long> {
/** * Name query: fuzzy query ** by name attribute@paramName Student name *@returnQuery result */
List<Student> findByNameLike(String name);
/** * JQL query *@param name
* @return* /
@Query("from Student where name like concat('%',? 1, the '%')"
List<Student> getUsers(String name);
}
Copy the code3. Write business methods
IStudentService interface
public interface IStudentService {
Student findStudentById(Long id);
List<Student> findUsers(String name);
Student insertStudent(Student student);
Student updateStudent(Student student);
void deleteStudent(Long id);
List<Student> findAll(a);
}
Copy the codeIStudentService Implementation class StudentServiceImpl
@Service
public class StudentServiceImpl implements IStudentService {
@Autowired
private StudentRepository studentRepository;
@Override
public Student findStudentById(Long id) {
return studentRepository.getOne(id);
}
@Override
public List<Student> findUsers(String name) {
return studentRepository.findByNameLike(name);
}
@Transactional(rollbackFor = RuntimeException.class)
@Override
public Student insertStudent(Student student) {
return studentRepository.save(student);
}
@Transactional(rollbackFor = RuntimeException.class)
@Override
public Student updateStudent(Student student) {
return studentRepository.save(student);
}
@Transactional(rollbackFor = RuntimeException.class)
@Override
public void deleteStudent(Long id) {
studentRepository.deleteById(id);
}
@Override
public List<Student> findAll(a) {
returnstudentRepository.findAll(); }}Copy the codeIn the above code, manipulating data through Jap is very easy. Much of the basic CRUD is already implemented, just passing in parameters. Transactional management via annotations @Transactional(rollbackFor = runtimeException.class) executes multiple SQL within a single database connection, avoiding disadvantages such as using JdbcTemplate.
3.3 Using JPA
/** * JQL query *@param name
* @return* /
@Query("from Student where name like concat('%',? 1, the '%')"
List<Student> getUsers(String name);
Copy the codeJQL is the main language, the main operation is the entity class and its attributes, not to manipulate the table. You can also use native SQL queries by adding an attribute nativeQuery=true to @query.
3.4 Using named Queries
/** * Name query: fuzzy query ** by name attribute@paramName Student name *@returnQuery result */
List<Student> findByNameLike(String name);
Copy the codeThe named query verb is find/get to start, by identifies which attribute of the entity class to use as a condition, Like is a fuzzy query for that attribute, type, descending order and so on. Multiple attribute conditions can be handled using And Or Or logic. JPA will automatically generate the corresponding SQL statement for us.
Four,
This is the beginning of SpringBoot manipulation data, and more advanced manipulation may be explained later.