SpringBoot学习笔记-使用jdbcTemplate访问mysql

配置maven依赖

使用jdbcTemplate访问jdbc,需要在pom中添加mysql和spring-boot-start-jdbc依赖

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

配置数据源

在application.properties中配置数据库地址，账号密码等，springboot会配置一个DataSource，自动注入到jdbcTemplate中,驱动可以不配置,springboot可以通过url推测出来

spring.datasource.url=jdbc:mysql://localhost:3306/world
spring.datasource.username=root
spring.datasource.password=1234

测试 

 

 

完整程序

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.endless</groupId>
  <artifactId>springboot</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springboot</name>
  <url>http://maven.apache.org</url>
  
  <parent>
  	<groupId>org.springframework.boot</groupId>
  	<artifactId>spring-boot-starter-parent</artifactId>
  	<version>1.4.0.RELEASE</version>
  </parent>
  
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
  
  		<dependency>
  			<groupId>org.springframework.boot</groupId>	
  			<artifactId>spring-boot-starter-web</artifactId>
  		</dependency>
  		
  		<dependency>
       	 	<groupId>mysql</groupId>
        	<artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
        	<groupId>org.springframework.boot</groupId>
       		<artifactId>spring-boot-starter-jdbc</artifactId>
       	</dependency>
    
  	  	<dependency>
      		<groupId>junit</groupId>
      		<artifactId>junit</artifactId>
     	 	<scope>test</scope>
    	</dependency>
    	
  </dependencies>
 
  <build>
  	<plugins>
  		<plugin>
  			<groupId>org.springframework.boot</groupId>
  			<artifactId>spring-boot-maven-plugin</artifactId>
  		</plugin>
  	</plugins>
  </build>

</project>

WorldService

package com.endless.springboot.jdbc.services;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.endless.springboot.jdbc.entity.City;


@Service
public class WorldService {

    @Autowired
    private JdbcTemplate jdbcTemplate;
   
    public List<City> listCities(){
        String sql = "select * from city where countryCode='CHN' ";

        List<City> cities = jdbcTemplate.query(sql, new RowMapper<City>() {
            @Override
            public City mapRow(ResultSet resultSet, int i) throws SQLException {
                City city = new City();
                	city.setId(resultSet.getInt("ID"));
                	city.setName(resultSet.getString("Name"));
                	city.setCountryCode(resultSet.getString("CountryCode"));
                	city.setDistrict(resultSet.getString("District"));
                	city.setPopulation(resultSet.getInt("Population"));
                return city;
            }
        });

        return cities;
    }

}

App

package com.endless.springboot.jdbc;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.endless.springboot.jdbc.entity.City;
import com.endless.springboot.jdbc.services.WorldService;


@RestController
@SpringBootApplication
@RequestMapping("jdbc")
public class App {
	
	@Autowired
	private WorldService service;
	
	@RequestMapping("cities")
	public List<City> sayHello(){
		return service.listCities();
	}
    public static void main( String[] args ){
    	SpringApplication.run(App.class,args);
    }
}

 

转载于:https://my.oschina.net/Endless2010/blog/1542559