pom引入以下依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.3</version>
</dependency>
<!-- mysql8以下版本用这个-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<!-- mysql8版本用这个-->
<!-- <dependency>-->
<!-- <groupId>mysql</groupId>-->
<!-- <artifactId>mysql-connector-java</artifactId>-->
<!-- <version>8.0.11</version>-->
<!-- </dependency>-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependency>
application.properties添加数据库配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/wangshengdb??characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=wangsheng
创建JdbcTemplate工具类
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
/**
* 创 建 人 : wangsheng 创建日期:2019年10月9日
*/
@Service
public class JDBC {
@Autowired
JdbcTemplate jdbcTemplate;
//增加修改删除都用这个
public void insertUpdate(String sql, Object[] obj) {
jdbcTemplate.update(sql, obj);
}
//查询返回对象集合
public <T> List selectListPojo(String sql, Class<T> t, Object[] obj) {
List<Object> list = jdbcTemplate.query(sql, obj, (RowMapper<Object>) new BeanPropertyRowMapper<T>(t));
return (List) list;
}
//查询返回对象
public <T> T selectpojo(String sql, Class<T> t, Object[] obj) throws ClassNotFoundException {
Object object = jdbcTemplate.queryForObject(sql, obj, new BeanPropertyRowMapper<T>(t));
return (T) object;
}
//查询返回List<Map<String, Object>>
public <T> List<Map<String, Object>> selectListMap(String sql, Object[] obj) {
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, obj);
return (List) list;
}
//查询返回integer
public int selectInteger(String sql, Object[] obj) {
return jdbcTemplate.queryForObject(sql, Integer.class, obj);
}
//查询返回String
public String selectString(String sql, Object[] obj) {
return jdbcTemplate.queryForObject(sql, String.class, obj);
}
//查询返回List< String>
public <T> List selectListString(String sql, Object[] obj) {
List<String> list = jdbcTemplate.queryForList(sql, String.class, obj);
return (List) list;
}
//查询返回List< Integer>
public <T> List selectListObject(String sql, Object[] obj) {
List<Integer> list = jdbcTemplate.queryForList(sql, Integer.class, obj);
return (List) list;
}
}
创建实体类
/**
* 创 建 人 : wangsheng 创建日期:2019年10月9日
*/
public class Score {
public Integer id;
public String smart_card;
public Integer score;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getSmart_card() {
return smart_card;
}
public void setSmart_card(String smart_card) {
this.smart_card = smart_card;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
}
测试
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 java.util.List;
import java.util.Map;
/**
* 创 建 人 : wangsheng 创建日期:2019年10月9日
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class controller {
@Autowired
JDBC jdbc;
@Test
public void selectpojo() throws ClassNotFoundException {
String sql = "select * from dtv_score where id =?";
Object[] obj = new Object[]{1};
score score = jdbc.selectpojo(sql, score.class,obj);
System.out.println(score);
}
@Test
public void selectListpojo() {
String sql = "select * from dtv_score where id between ? and ?";
Object[] obj = new Object[]{1,4};
List<score> list = jdbc.selectListPojo(sql,score.class,obj);
System.out.println(list.get(0).getSmart_card());
}
@Test
public void add() {
String sql = "insert into dtv_score(smart_card,score) values(?,?)";
Object[] obj = new Object[]{"safsad",10};
jdbc.insertUpdate(sql,obj);
}
@Test
public void selectListMap() {
String sql = "select * from dtv_score where id between ? and ?";
Object[] obj = new Object[]{1, 4};
List<Map<String, Object>> list = jdbc.selectListMap(sql, obj);
Map<String, Object> map = list.get(0);
System.out.println( list);
}
}