本文介绍spring jdbc的使用
目录结构
pom配置
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
properties配置
spring.datasource.url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
server.tomcat.uri-encoding=UTF-8
server.port=8888
model层User类
package com.springlearn.learn.model;
public class User{
private Integer id;
private String name;
private Integer age;
private String sex;
public User(Integer id, String name, Integer age, String sex) {
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
}
public Integer getId() {
return id;
}
public String getName() {
return name;
}
public Integer getAge() {
return age;
}
public String getSex() {
return sex;
}
public void setId(Integer id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setAge(Integer age) {
this.age = age;
}
public void setSex(String sex) {
this.sex = sex;
}
}
Dao层QueryForListDao
/**
* 第一种用法
* List<String> list = this.getJdbcTemplate().queryForList(sql, String.class)
*
* 第二种用法
* List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
* for (Map<String, Object> item : users) {
* System.out.println("UserName: " + item.get("name") + "Age: " + item.get("age") + "Sex: " + item.get("sex"));
* }
*
* 第三种用法
* SqlRowSet rowset = this.getJdbcTemplate().queryForRowSet(sql, new Object[]{3},new int[]{Types.INTEGER});
*
* SqlRowSet rowset = listdao.getUsersList();
* while(rowset.next()) {
* System.out.println("UserName: " + rowset.getString("name") + "Age: " + rowset.getInt("age") + "Sex: " + rowset.getString("sex"));
* }
*
* 第四种方式
* rowmapper的使用，在我的文章 https://www.cnblogs.com/ye-hcj/p/9618588.html#mapper%E5%B1%82 已经讲过
*
* 第五种方式
* String sql = "select * from test where id=?;";
*
* RowCallbackHandler handler = new RowCallbackHandler(){
*
* @Override
* public void processRow(ResultSet rs) throws SQLException {
* System.out.println("id：" + rs.getInt("id") + "UserName: " + rs.getString("name") + "Age: " + rs.getInt("age") + "Sex: " + rs.getString("sex"));
* }
* };
*
* this.getJdbcTemplate().query(sql, handler, 3);
*
* listdao.getUsersList();
*
* 第六种方式，如下
*
* 第七中方式，queryForObject 用法和上面类似
*/
package com.springlearn.learn.Dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import com.springlearn.learn.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;
@Repository
public class QueryForListDao extends JdbcDaoSupport{
@Autowired
public QueryForListDao(DataSource dataSource) {
this.setDataSource(dataSource);
}
class ListResultSetExtractor implements ResultSetExtractor<List<User>>{
@Override
public List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {
List<User> list = new ArrayList<User>();
while(rs.next()) {
list.add(new User(rs.getInt("id"), rs.getString("name"), rs.getInt("age"), rs.getString("sex")));
}
return list;
}
}
public List<User> getUsersList() {
String sql = "select * from test where id=?;";
ListResultSetExtractor ls = new ListResultSetExtractor();
List<User> list = this.getJdbcTemplate().query(sql, ls, 2);
return list;
}
}
config层AppConfiguration
package com.springlearn.learn.config;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
@Configuration
@ComponentScan(basePackages = "com.springlearn.learn.*")
@PropertySource("classpath:application.properties")
public class AppConfiguration{
@Autowired
private Environment env;
@Primary
@Bean(name="dataSource")
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
dataSource.setUrl(env.getProperty("spring.datasource.url"));
dataSource.setUsername(env.getProperty("spring.datasource.username"));
dataSource.setPassword(env.getProperty("spring.datasource.password"));
return dataSource;
}
}
程序入口DemoApplication
package com.springlearn.learn;
import java.util.List;
import java.util.Map;
import com.springlearn.learn.Dao.QueryForListDao;
import com.springlearn.learn.config.AppConfiguration;
import com.springlearn.learn.model.User;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.jdbc.support.rowset.SqlRowSet;
public class DemoApplication {
public static void main(String[] args) {
ApplicationContext context = new AnnotationConfigApplicationContext(AppConfiguration.class);
QueryForListDao listdao = (QueryForListDao)context.getBean(QueryForListDao.class);
List<User> list = listdao.getUsersList();
for (User user : list) {
System.out.println("id：" + user.getId()+ "UserName: " + user.getName() + "Age: " + user.getAge() + "Sex: " + user.getSex());
}
}
}