十九、Spring Boot使用JdbcTemplate

1、添加數(shù)據(jù)源

既然要使用JdbcTemplate，就需要添加jdbc的依賴。

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

2、連接數(shù)據(jù)源，以mysql為例：

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

3、在src/main/resources/application.properties中配置數(shù)據(jù)源信息

注意：其中driver-class可以不寫，Spring Boot會(huì)自動(dòng)從url中解析使用的數(shù)據(jù)源類

Spring Boot默認(rèn)采用tomcat-jdbc連接池，如果需要C3P0，DBCP，Druid等作為連接池，需要加入相關(guān)依賴以及配置，這里不作說(shuō)明，采用默認(rèn)配置即可。

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=...
spring.datasource.password=...

底層默認(rèn)使用tomcat-jdbc連接池，所以在默認(rèn)情況下會(huì)創(chuàng)建一個(gè)基于Tomcat連接池的DataSource，并注入到Spring IOC容器中。

abstract class DataSourceConfiguration {

    @SuppressWarnings("unchecked")
    protected <T> T createDataSource(DataSourceProperties properties,
            Class<? extends DataSource> type) {
        return (T) properties.initializeDataSourceBuilder().type(type).build();
    }

    /**
     * Tomcat Pool DataSource configuration.
     */
    @ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource", matchIfMissing = true)
    static class Tomcat extends DataSourceConfiguration {

        //創(chuàng)建一個(gè)基于Tomcat連接池的DataSource的Bean
        //由于底層使用Tomcat連接池，在不引入外部數(shù)據(jù)連接池的jar包之前，默認(rèn)使用Tomcat的DataSource
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.tomcat")
        public org.apache.tomcat.jdbc.pool.DataSource dataSource(
                DataSourceProperties properties) {
            org.apache.tomcat.jdbc.pool.DataSource dataSource = createDataSource(
                    properties, org.apache.tomcat.jdbc.pool.DataSource.class);
            DatabaseDriver databaseDriver = DatabaseDriver
                    .fromJdbcUrl(properties.determineUrl());
            String validationQuery = databaseDriver.getValidationQuery();
            if (validationQuery != null) {
                dataSource.setTestOnBorrow(true);
                dataSource.setValidationQuery(validationQuery);
            }
            return dataSource;
        }

    }

    /**
     * Hikari DataSource configuration.
     */
    @ConditionalOnClass(HikariDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true)
    static class Hikari extends DataSourceConfiguration {

        //創(chuàng)建基于hikari的DataSource的Bean
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.hikari")
        public HikariDataSource dataSource(DataSourceProperties properties) {
            return createDataSource(properties, HikariDataSource.class);
        }

    }

    /**
     * DBCP DataSource configuration.
     *
     * @deprecated as of 1.5 in favor of DBCP2
     */
    @ConditionalOnClass(org.apache.commons.dbcp.BasicDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp.BasicDataSource", matchIfMissing = true)
    @Deprecated
    static class Dbcp extends DataSourceConfiguration {

        //創(chuàng)建基于dbcp的DataSource的Bean
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.dbcp")
        public org.apache.commons.dbcp.BasicDataSource dataSource(
                DataSourceProperties properties) {
            org.apache.commons.dbcp.BasicDataSource dataSource = createDataSource(
                    properties, org.apache.commons.dbcp.BasicDataSource.class);
            DatabaseDriver databaseDriver = DatabaseDriver
                    .fromJdbcUrl(properties.determineUrl());
            String validationQuery = databaseDriver.getValidationQuery();
            if (validationQuery != null) {
                dataSource.setTestOnBorrow(true);
                dataSource.setValidationQuery(validationQuery);
            }
            return dataSource;
        }

    }

    /**
     * DBCP DataSource configuration.
     */
    @ConditionalOnClass(org.apache.commons.dbcp2.BasicDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp2.BasicDataSource", matchIfMissing = true)
    static class Dbcp2 extends DataSourceConfiguration {

        //創(chuàng)建基于dbcp2的DataSource的Bean
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.dbcp2")
        public org.apache.commons.dbcp2.BasicDataSource dataSource(
                DataSourceProperties properties) {
            return createDataSource(properties,
                    org.apache.commons.dbcp2.BasicDataSource.class);
        }
    }
    
    /**
     * Generic DataSource configuration.
     */
    @ConditionalOnMissingBean(DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type")
    static class Generic {

        //可以在配置文件中通過(guò)spring.datasource.type指定外部的dataSource類型，并創(chuàng)建基于該type的DataSource的Bean
        @Bean
        public DataSource dataSource(DataSourceProperties properties) {
            return properties.initializeDataSourceBuilder().build();
        }

    }
}

4、使用JdbcTemplate操作數(shù)據(jù)庫(kù)

SpringBoot中的 JdbcTemplate 是自動(dòng)配置的，可以直接使用 @Autowired 或者 @Resource 來(lái)注入到需要的類中。

JdbcTemplateAutoConfiguration

@Configuration
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
//在DataSourceAutoConfiguration自動(dòng)配置類完成后再進(jìn)行自動(dòng)配置該類JdbcTemplateAutoConfiguration
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
public class JdbcTemplateAutoConfiguration {

    private final DataSource dataSource;

    public JdbcTemplateAutoConfiguration(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    //創(chuàng)建一個(gè)JdbcTemplate的Bean
    @Bean
    @Primary
    @ConditionalOnMissingBean(JdbcOperations.class)
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(this.dataSource);
    }

    @Bean
    @Primary
    @ConditionalOnMissingBean(NamedParameterJdbcOperations.class)
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
        return new NamedParameterJdbcTemplate(this.dataSource);
    }

}

在JdbcTemplateAutoConfiguration類中會(huì)創(chuàng)建一個(gè)JdbcTemplate的Bean，所以在使用的時(shí)候可以直接注入。

DataSourceAutoConfiguration

@Configuration
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
//啟動(dòng)DataSourceProperties配置類
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ Registrar.class, DataSourcePoolMetadataProvidersConfiguration.class })
public class DataSourceAutoConfiguration {

    private static final Log logger = LogFactory
            .getLog(DataSourceAutoConfiguration.class);

    @Bean
    @ConditionalOnMissingBean
    public DataSourceInitializer dataSourceInitializer(DataSourceProperties properties,
            ApplicationContext applicationContext) {
        //根據(jù)配置文件屬性創(chuàng)建DataSource的初始化器
        return new DataSourceInitializer(properties, applicationContext);
    }
    
    //...
}

[圖片上傳失敗...(image-febd30-1556178324854)]

DataSourceInitializer

class DataSourceInitializer implements ApplicationListener<DataSourceInitializedEvent> {
    private final DataSourceProperties properties;
    private final ApplicationContext applicationContext;
    private DataSource dataSource;
    private boolean initialized = false;
    DataSourceInitializer(DataSourceProperties properties,
            ApplicationContext applicationContext) {
        this.properties = properties;
        this.applicationContext = applicationContext;
    }
    @PostConstruct
    public void init() {
        //當(dāng)DataSourceProperties初始化完成后
        if (!this.properties.isInitialize()) {
            logger.debug("Initialization disabled (not running DDL scripts)");
            return;
        }
        if (this.applicationContext.getBeanNamesForType(DataSource.class, false,
                false).length > 0) {
            //獲取DataSource的bean
            this.dataSource = this.applicationContext.getBean(DataSource.class);
        }
        if (this.dataSource == null) {
            logger.debug("No DataSource found so not initializing");
            return;
        }
        //執(zhí)行schema腳本
        runSchemaScripts();
    }


    private void runSchemaScripts() {
        //查看全局配置文件中是否有spring.datasource.schema
        List<Resource> scripts = getScripts("spring.datasource.schema",
                this.properties.getSchema(), "schema");
        if (!scripts.isEmpty()) {
            String username = this.properties.getSchemaUsername();
            String password = this.properties.getSchemaPassword();
            runScripts(scripts, username, password);
            try {
                this.applicationContext
                        .publishEvent(new DataSourceInitializedEvent(this.dataSource));
                // The listener might not be registered yet, so don't rely on it.
                if (!this.initialized) {
                    //執(zhí)行數(shù)據(jù)插入腳本
                    runDataScripts();
                    this.initialized = true;
                }
            }
            catch (IllegalStateException ex) {
                logger.warn("Could not send event to complete DataSource initialization ("
                        + ex.getMessage() + ")");
            }
        }
    }


    @Override
    public void onApplicationEvent(DataSourceInitializedEvent event) {
        if (!this.properties.isInitialize()) {
            logger.debug("Initialization disabled (not running data scripts)");
            return;
        }
        // NOTE the event can happen more than once and
        // the event datasource is not used here
        //如果初始化完成后，執(zhí)行數(shù)據(jù)插入
        if (!this.initialized) {
            runDataScripts();
            this.initialized = true;
        }
    }


    private void runDataScripts() {
        List<Resource> scripts = getScripts("spring.datasource.data",
                this.properties.getData(), "data");
        String username = this.properties.getDataUsername();
        String password = this.properties.getDataPassword();
        runScripts(scripts, username, password);
    }


    private List<Resource> getScripts(String propertyName, List<String> resources,
            String fallback) {
        if (resources != null) {
            //如果配置了，則從指定的資源文件中加載腳本
            return getResources(propertyName, resources, true);
        }
        //如果未配置，則使用classpath*:schema-all.sql或者classpath*：schema.sql作為腳本名，其中platform默認(rèn)為all
        String platform = this.properties.getPlatform();
        List<String> fallbackResources = new ArrayList<String>();
        fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
        fallbackResources.add("classpath*:" + fallback + ".sql");
        return getResources(propertyName, fallbackResources, false);
    }


    private List<Resource> getResources(String propertyName, List<String> locations,
            boolean validate) {
        List<Resource> resources = new ArrayList<Resource>();
        for (String location : locations) {
            for (Resource resource : doGetResources(location)) {
                if (resource.exists()) {
                    resources.add(resource);
                }
                else if (validate) {
                    throw new ResourceNotFoundException(propertyName, resource);
                }
            }
        }
        return resources;
    }


    private Resource[] doGetResources(String location) {
        try {
            SortedResourcesFactoryBean factory = new SortedResourcesFactoryBean(
                    this.applicationContext, Collections.singletonList(location));
            factory.afterPropertiesSet();
            return factory.getObject();
        }
        catch (Exception ex) {
            throw new IllegalStateException("Unable to load resources from " + location,
                    ex);
        }
    }


    private void runScripts(List<Resource> resources, String username, String password) {
        if (resources.isEmpty()) {
            return;
        }
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.setContinueOnError(this.properties.isContinueOnError());
        populator.setSeparator(this.properties.getSeparator());
        if (this.properties.getSqlScriptEncoding() != null) {
            populator.setSqlScriptEncoding(this.properties.getSqlScriptEncoding().name());
        }
        for (Resource resource : resources) {
            populator.addScript(resource);
        }
        DataSource dataSource = this.dataSource;
        if (StringUtils.hasText(username) && StringUtils.hasText(password)) {
            dataSource = DataSourceBuilder.create(this.properties.getClassLoader())
                    .driverClassName(this.properties.determineDriverClassName())
                    .url(this.properties.determineUrl()).username(username)
                    .password(password).build();
        }
        DatabasePopulatorUtils.execute(populator, dataSource);
    }
}       List<Resource> scripts = getScripts("spring.datasource.schema",
                this.properties.getSchema(), "schema");
        if (!scripts.isEmpty()) {
            String username = this.properties.getSchemaUsername();
            String password = this.properties.getSchemaPassword();
            runScripts(scripts, username, password);
            try {
                this.applicationContext
                        .publishEvent(new DataSourceInitializedEvent(this.dataSource));
                // The listener might not be registered yet, so don't rely on it.
                if (!this.initialized) {
                    //執(zhí)行數(shù)據(jù)插入腳本
                    runDataScripts();
                    this.initialized = true;
                }
            }
            catch (IllegalStateException ex) {
                logger.warn("Could not send event to complete DataSource initialization ("
                        + ex.getMessage() + ")");
            }
        }
    }


    @Override
    public void onApplicationEvent(DataSourceInitializedEvent event) {
        if (!this.properties.isInitialize()) {
            logger.debug("Initialization disabled (not running data scripts)");
            return;
        }
        // NOTE the event can happen more than once and
        // the event datasource is not used here
        //如果初始化完成后，執(zhí)行數(shù)據(jù)插入
        if (!this.initialized) {
            runDataScripts();
            this.initialized = true;
        }
    }


    private void runDataScripts() {
        List<Resource> scripts = getScripts("spring.datasource.data",
                this.properties.getData(), "data");
        String username = this.properties.getDataUsername();
        String password = this.properties.getDataPassword();
        runScripts(scripts, username, password);
    }


    private List<Resource> getScripts(String propertyName, List<String> resources,
            String fallback) {
        if (resources != null) {
            //如果配置了，則從指定的資源文件中加載腳本
            return getResources(propertyName, resources, true);
        }
        //如果未配置，則使用classpath*:schema-all.sql或者classpath*：schema.sql作為腳本名，其中platform默認(rèn)為all
        String platform = this.properties.getPlatform();
        List<String> fallbackResources = new ArrayList<String>();
        fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
        fallbackResources.add("classpath*:" + fallback + ".sql");
        return getResources(propertyName, fallbackResources, false);
    }


    private List<Resource> getResources(String propertyName, List<String> locations,
            boolean validate) {
        List<Resource> resources = new ArrayList<Resource>();
        for (String location : locations) {
            for (Resource resource : doGetResources(location)) {
                if (resource.exists()) {
                    resources.add(resource);
                }
                else if (validate) {
                    throw new ResourceNotFoundException(propertyName, resource);
                }
            }
        }
        return resources;
    }


    private Resource[] doGetResources(String location) {
        try {
            SortedResourcesFactoryBean factory = new SortedResourcesFactoryBean(
                    this.applicationContext, Collections.singletonList(location));
            factory.afterPropertiesSet();
            return factory.getObject();
        }
        catch (Exception ex) {
            throw new IllegalStateException("Unable to load resources from " + location,
                    ex);
        }
    }


    private void runScripts(List<Resource> resources, String username, String password) {
        if (resources.isEmpty()) {
            return;
        }
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.setContinueOnError(this.properties.isContinueOnError());
        populator.setSeparator(this.properties.getSeparator());
        if (this.properties.getSqlScriptEncoding() != null) {
            populator.setSqlScriptEncoding(this.properties.getSqlScriptEncoding().name());
        }
        for (Resource resource : resources) {
            populator.addScript(resource);
        }
        DataSource dataSource = this.dataSource;
        if (StringUtils.hasText(username) && StringUtils.hasText(password)) {
            dataSource = DataSourceBuilder.create(this.properties.getClassLoader())
                    .driverClassName(this.properties.determineDriverClassName())
                    .url(this.properties.determineUrl()).username(username)
                    .password(password).build();
        }
        DatabasePopulatorUtils.execute(populator, dataSource);
    }
}

DataSourceConfiguration

? 底層默認(rèn)使用tomcat-jdbc連接池，所以在默認(rèn)情況下會(huì)創(chuàng)建一個(gè)基于Tomcat連接池的DataSource，并注入到Spring IOC容器中。

abstract class DataSourceConfiguration {
    @SuppressWarnings("unchecked")
    protected <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
        return (T) properties.initializeDataSourceBuilder().type(type).build();
    }
    /**
     * Tomcat Pool DataSource configuration.
     */
    @ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource", matchIfMissing = true)
    static class Tomcat extends DataSourceConfiguration {
        //創(chuàng)建一個(gè)基于Tomcat連接池的DataSource的Bean
        //由于底層使用Tomcat連接池，在不引入外部數(shù)據(jù)連接池的jar包之前，默認(rèn)使用Tomcat的DataSource
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.tomcat")
        public org.apache.tomcat.jdbc.pool.DataSource dataSource(DataSourceProperties properties) {
            org.apache.tomcat.jdbc.pool.DataSource dataSource = createDataSource(properties, org.apache.tomcat.jdbc.pool.DataSource.class);
            DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());
            String validationQuery = databaseDriver.getValidationQuery();
            if (validationQuery != null) {
                dataSource.setTestOnBorrow(true);
                dataSource.setValidationQuery(validationQuery);
            }
            return dataSource;
        }
    }
    /**
     * Hikari DataSource configuration.
     */
    @ConditionalOnClass(HikariDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true)
    static class Hikari extends DataSourceConfiguration {
        //創(chuàng)建基于hikari的DataSource的Bean
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.hikari")
        public HikariDataSource dataSource(DataSourceProperties properties) {
            return createDataSource(properties, HikariDataSource.class);
        }
    }
    /**
     * DBCP DataSource configuration.
     *
     * @deprecated as of 1.5 in favor of DBCP2
     */
    @ConditionalOnClass(org.apache.commons.dbcp.BasicDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp.BasicDataSource", matchIfMissing = true)
    @Deprecated
    static class Dbcp extends DataSourceConfiguration {
        //創(chuàng)建基于dbcp的DataSource的Bean
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.dbcp")
        public org.apache.commons.dbcp.BasicDataSource dataSource(DataSourceProperties properties) {
            org.apache.commons.dbcp.BasicDataSource dataSource = createDataSource(properties, org.apache.commons.dbcp.BasicDataSource.class);
            DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());
            String validationQuery = databaseDriver.getValidationQuery();
            if (validationQuery != null) {
                dataSource.setTestOnBorrow(true);
                dataSource.setValidationQuery(validationQuery);
            }
            return dataSource;
        }
    }
    /**
     * DBCP DataSource configuration.
     */
    @ConditionalOnClass(org.apache.commons.dbcp2.BasicDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp2.BasicDataSource", matchIfMissing = true)
    static class Dbcp2 extends DataSourceConfiguration {
        //創(chuàng)建基于dbcp2的DataSource的Bean
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.dbcp2")
        public org.apache.commons.dbcp2.BasicDataSource dataSource(DataSourceProperties properties) {
            return createDataSource(properties,org.apache.commons.dbcp2.BasicDataSource.class);
        }
    }
    /**
     * Generic DataSource configuration.
     */
    @ConditionalOnMissingBean(DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type")
    static class Generic {
        //可以在配置文件中通過(guò)spring.datasource.type指定外部的dataSource類型，并創(chuàng)建基于該type的DataSource的Bean
        @Bean
        public DataSource dataSource(DataSourceProperties properties) {
            return properties.initializeDataSourceBuilder().build();
        }
    }
}

SpringBoot默認(rèn)支持：

  • org.apache.tomcat.jdbc.pool.DataSource
  • com.zaxxer.hikari.HikariDataSource
  • org.apache.commons.dbcp.BasicDataSource
  • org.apache.commons.dbcp2.BasicDataSource

上面講到DataSourceInitializer是ApplicationListener類型的監(jiān)聽器

它的作用主要有：

? i）、runSchemaScripts();運(yùn)行建表語(yǔ)句

? ii）、runDataScripts();運(yùn)行插入數(shù)據(jù)的sql語(yǔ)句

默認(rèn)只需要將文件命名為：

? schema-*.sql 用于建表操作

? data-*.sql 用于數(shù)據(jù)操作

? 默認(rèn)規(guī)則：schema.sql或者schema-all.sql

自定義規(guī)則：spring.datasource.schema=[classpath:department.sql]

最后使用JdbcTemplate操作數(shù)據(jù)庫(kù)，就類似在Spring框架中使用JdbcTemplate一樣，這里就不再累贅了。

附上代碼：

User實(shí)體類：

public class User implements Serializable {

    private Integer uid;
    private String uname;
    private int uage;

    //getter和setter
}

User控制器：

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private IUserService userService;

    @PostMapping("/")
    public String insertUser(User user){
        int count = userService.insertUser(user);
        if(count != 1){
            return "error";
        }else{
            return "success";
        }
    }

    @GetMapping("/{uid}")
    public User getUserById(@PathVariable Integer uid){
        User user = userService.getUserById(uid);

        return user;
    }

    @GetMapping("/")
    public List<User> getAllUsers(){
        return userService.getAllUsers();
    }

    @PutMapping("/{uid}")
    public String updateUserById(@PathVariable Integer uid, User user){
        return userService.updateUserById(uid, user) == 1 ? "success" : "error";
    }

    @DeleteMapping("/{uid}")
    public String deleteUserById(@PathVariable Integer uid){
        return userService.deleteUserById(uid) == 1 ? "success" : "error";
    }
}

User業(yè)務(wù)層接口：

public interface IUserService {

    int insertUser(User user);

    User getUserById(Integer id);

    List<User> getAllUsers();

    int updateUserById(Integer uid, User user);

    int deleteUserById(Integer uid);
}

User業(yè)務(wù)層接口實(shí)現(xiàn)類：

@Service
public class UserServiceImpl implements IUserService {

    @Autowired
    private IUserDao userDao;

    @Override
    public int insertUser(User user) {
        return userDao.insertUser(user);
    }

    @Override
    public User getUserById(Integer id) {
        return userDao.getUserById(id);
    }

    @Override
    public List<User> getAllUsers() {
        return userDao.getAllUsers();
    }

    @Override
    public int updateUserById(Integer uid, User user) {
        return userDao.updateUserById(uid, user);
    }

    @Override
    public int deleteUserById(Integer uid) {
        return userDao.deleteUserById(uid);
    }
}

User持久層接口：

public interface IUserDao {

    int insertUser(User user);

    User getUserById(Integer id);

    List<User> getAllUsers();

    int updateUserById(Integer uid, User user);

    int deleteUserById(Integer uid);
}

User持久層接口實(shí)現(xiàn)類：

@Repository
public class UserDaoImpl implements IUserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insertUser(User user) {
        return jdbcTemplate.update("insert into user values(null, ?, ?)", user.getUname(), user.getUage());
    }

    @Override
    public User getUserById(Integer id) {
        Object[] params = {id};
        return jdbcTemplate.queryForObject("select * from user where uid=?", params, new UserRowMapper());
    }

    @Override
    public List<User> getAllUsers() {
        return jdbcTemplate.query("select * from user", new UserRowMapper());
    }

    @Override
    public int updateUserById(Integer uid, User user) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        boolean isUpdate = false;

        if (user != null) {
            sb.append("update user set ");

            if (user.getUname() != null && !"".equals(StringUtils.trimWhitespace(user.getUname()))) {
                isUpdate = true;
                sb.append(" uname = ?,");
                params.add(StringUtils.trimWhitespace(user.getUname()));
            }
            if (user.getUage() != 0) {
                isUpdate = true;
                sb.append(" uage = ?,");
                params.add(user.getUage());
            }
        }

        if (isUpdate) {
            sb.deleteCharAt(sb.length() - 1);

            sb.append(" where uid = ? ");
            params.add(user.getUid());
            return jdbcTemplate.update(sb.toString(), params.toArray());
        } else {
            return 0;
        }

    }

    @Override
    public int deleteUserById(Integer uid) {
        return jdbcTemplate.update("delete from user where uid = ?", uid);
    }
}

User數(shù)據(jù)映射類：

public class UserRowMapper implements RowMapper<User> {
    @Override
    public User mapRow(ResultSet resultSet, int i) throws SQLException {
        User user = new User();
        user.setUid(resultSet.getInt("uid"));
        user.setUname(resultSet.getString("uname"));
        user.setUage(resultSet.getInt("uage"));
        return user;
    }
}

本節(jié)使用Restful風(fēng)格進(jìn)行編寫，大概知道Restful格式即可，此處不多講述。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容