A brief introduction to JdbcTemplate

1. About JdbcTemplate

JdbcTemplate is the most basic Spring JDBC template. This template supports simple JDBC database access functions and queries based on index parameters.

Spring Data Access Template: During database operations, there is a large part of repeated work, such as transaction control, resource management, and exception handling. Spring's template classes handle these fixed parts. At the same time, application-related data access is handled in the callback implementation, including statements, binding parameters, and sorting results. In this way, we only need to care about our own data access logic.

Spring's JDBC framework takes on the work of resource management and exception handling, thus simplifying the JDBC code. We only need to write the necessary code to read and write data from the database and everything will be fine. .

2. Spring JdbcTemplate Example

Our learning goal is to write a demo to implement CRUD operations on Category.

1. Create a table

mysql to create a new database store, and then execute the following sql:

create table Category (
Id int not null,
Name varchar(80) null,constraint pk_category primary key (Id)
);INSERT INTO category(id,Name) VALUES (1,'女装');INSERT INTO category(id,Name) VALUES (2,'美妆');INSERT INTO category(id,Name) VALUES (3,'书籍');

db_store. sql

2. The IDE I use is IdeaIU, I build the project through maven and configure spring through xml. The completed code structure is:

3. Create the entity class Category

public class Category{
    private int cateId;

    private String cateName;

    public int getCateId() {
        return cateId;
    }

    public void setCateId(int cateId) {
        this.cateId = cateId;
    }

    public String getCateName() {
        return cateName;
    }

    public void setCateName(String cateName) {
        this.cateName = cateName;
    }

    @Override
    public String toString() {
        return "id="+cateId+" name="+cateName;
    }
}

　

4. Modify pom.xml, introduce related dependencies.

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

        <!-- Mysql数据库链接jar包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

　

5. Configure applicationContext.xml

You need to configure dataSource as the data source of jdbcTemplate. Then configure the CategoryDao bean and construct the jdbcTemplate object. The complete applicationContext.xml is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans ">
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/store"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    
    <bean id="categoryDao" class="CategoryDao">
        <constructor-arg ref="jdbcTemplate"></constructor-arg>
    </bean>
</beans>

　

6. Data access implementation class CategoryDao

The CategoryDao constructor contains the parameter jdbcTemplate, and then implements Commonly used data access operations. As you can see, we only need to pay attention to the specific sql statement. In addition, lambda syntax is used in the getById() and getAll() methods.

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by 陈敬 on 2017/6/6.
 */
public class CategoryDao {
    private JdbcTemplate jdbcTemplate;

    public CategoryDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int add(Category category) {
        String sql = "INSERT INTO category(id,name)VALUES(?,?)";
        return jdbcTemplate.update(sql, category.getCateId(), category.getCateName());
    }

    public int update(Category category) {
        String sql = "UPDATE Category SET Name=? WHERE Id=?";
        return jdbcTemplate.update(sql, category.getCateName(), category.getCateId());
    }

    public int delete(int id) {
        String sql = "DELETE FROM Category WHERE Id=?";
        return jdbcTemplate.update(sql, id);
    }

    public int count(){
        String sql="SELECT COUNT(0) FROM Category";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    public Category getById(int id) {
        String sql = "SELECT Id,Name FROM Category WHERE Id=?";
        return jdbcTemplate.queryForObject(sql, (ResultSet rs, int rowNumber) -> {
            Category category = new Category();
            category.setCateId(rs.getInt("Id"));
            category.setCateName(rs.getString("Name"));
            return category;
        }, id);
    }

    public List<Category> getAll(){
        String sql="SELECT Id,Name FROM Category";

        List<Category> result=jdbcTemplate.query(sql, (resultSet, i) -> {
            Category category = new Category();
            category.setCateId(resultSet.getInt("Id"));
            category.setCateName(resultSet.getString("Name"));
            return category;
        });

        return result;
    }
}

　

7. Test

@ContextConfiguration(locations = "classpath:applicationContext.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class testCategoryDao {
    @Autowired
    private CategoryDao categoryDao;

    @Test
    public void testAdd() {
        Category category = new Category();
        category.setCateId(4);
        category.setCateName("母婴");

        int result = categoryDao.add(category);
        System.out.println(result);
    }

    @Test
    public void testUpdate() {
        Category category = new Category();
        category.setCateId(4);
        category.setCateName("男装");

        int result = categoryDao.update(category);
        System.out.println(result);
    }


    @Test
    public void testGetById() {
        int id = 4;
        Category category = categoryDao.getById(id);
        System.out.println(category.toString());
    }

    @Test
    public void testGetAll() {
        List<Category> categories = categoryDao.getAll();
        for (Category item : categories) {
            System.out.println(item);
        }
    }

    @Test
    public void testCount() {
        int count = categoryDao.count();
        System.out.println(count);
    }

    @Test
    public void testDelete() {
        int id = 4;
        int result = categoryDao.delete(id);
        System.out.println(result);
    }
}

The above is the detailed content of A brief introduction to JdbcTemplate. For more information, please follow other related articles on the PHP Chinese website!