使用spring框架中的JdbcTemplate 来连接处理数据库, JdbcTemplate接收来自 druid 的 DataSource
环境配置 spring的包
libs/commons-logging-1.2.jar
libs/druid-1.1.20.jar
libs/hamcrest-core-1.3.jar //junit的依赖
libs/junit-4.12.jar
libs/mysql-connector-java-8.0.21.jar
libs/spring-beans-5.2.5.RELEASE.jar
libs/spring-context-5.2.5.RELEASE.jar
libs/spring-core-5.2.5.RELEASE.jar
libs/spring-expression-5.2.5.RELEASE.jar
libs/spring-jdbc-5.2.5.RELEASE.jar //jdbcTemple所在包
libs/spring-tx-5.2.5.RELEASE.jar
.queryForMap
/*
* .queryForMap
* 只能查询结果为1 条,否则报错 org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2
*/
@Test
public void Test() throws Exception {
Properties properties = new Properties();
properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql="select id,NameSpace from NameSpace where id between ? and ?";
Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql,1,1);
System.out.println(stringObjectMap);
}
.queryForList
/*
* .queryForList
* 可以查出多个结果 list包含map
*/
@Test
public void Test2() throws Exception {
Properties properties = new Properties();
properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql="select id,NameSpace from NameSpace where id between ? and ?";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, 2, 7);
System.out.println(maps);
}
.queryForObject 聚合查询
/*
* .queryForObject
* 查询出聚合操作
*/
@Test
public void Test3() throws Exception {
Properties properties = new Properties();
properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql="select count(1) from NameSpace";
Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(integer);
}
.query 查询结果封装成对象
/*
* .query
* 查询出的结果 封装成对象 需要重写new RowMapper<>() 自定义RowMapper<>
*/
@Test
public void Test4() throws Exception {
Properties properties = new Properties();
properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql="select id,NameSpace from NameSpace where id between ? and ?";
//jdbcTemplate.queryForObject()
List<NameSpace> query = jdbcTemplate.query(sql,new Integer[]{2,5} , new RowMapper<NameSpace>() {
@Override
public NameSpace mapRow(ResultSet resultSet, int i) throws SQLException {
Integer id = resultSet.getInt("id");
String nameSpace1 = resultSet.getString("NameSpace");
NameSpace nameSpace = new NameSpace(id,nameSpace1);
return nameSpace;
}
});
System.out.println(query);
}
.query 查询结果封装成对象 BeanPropertyRowMapper<>
/*
* .query
* 查询出的结果 封装成对象 需要重写new RowMapper<>(),
* 使用new BeanPropertyRowMapper<>(NameSpace.class) 代替 RowMapper
* 其中分装的对象类 必须要有 误参构造方法 否则报错
* No default constructor found; nested exception is java.lang.NoSuchMethodException:
*/
@Test
public void Test5() throws Exception {
Properties properties = new Properties();
properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql="select id,NameSpace from NameSpace where id between ? and ?";
List<NameSpace> query=jdbcTemplate.query(sql,new Integer[]{2,5},new BeanPropertyRowMapper<>(NameSpace.class));
System.out.println(query);
}