Spring jdbc
- 概念
Spring框架对JDBC的简单封装，提供了一个JDBCTemplate对象简化JDBC的开发
- 步骤
- 导入jar包
- 创建对象JdbcTemplate，依赖于数据源DataSource(数据库连接池)
JdbcTemplate template = new JdbcTemplate(datasource)
- 调用JdbcTemplate的方法来完成CRUD的操作
①update()：执行DML（增删改）
②queryForMap()：查询结果将结果封装为map集合
注意：这个方法查询到结果集长度只能为1，将列名作为key，将值作为value
③queryForList()：查询结果将结果封装为list集合
注意：将每条记录封装为map集合，再将Map集合装载到List集合中
④query()：查询结果，将结果封装为JavaBean对象
注意：query的参数RowMapper：一般我们使用 new BeanPropertyRowMapper<类>(类.class).可以完成数据到javabean的自动封装;还可以使用new RowMapper(){}重写mapRow方法
⑤queryForObject()：查询结果，将结果封装为对象
注意：一般用于集合函数的查询。queryForObject(sql, 值的类型.class);
package it.heima;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import javax.sql.DataSource;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;
public class springJdbcTest {
public static JdbcTemplate getjdbcTemplate(){
//1.获得datasource
DataSource dataSource = null;
Properties properties = new Properties();
ClassLoader classLoader = springJdbcTest.class.getClassLoader();
URL springJdbcTest = classLoader.getResource("druid.properties");
String path = springJdbcTest.getPath();
try {
properties.load(new FileReader(path));
} catch (IOException e) {
e.printStackTrace();
}
try {
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
//2.创建JDBCTemplate对象
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate;
}
@org.junit.Test
public void update(){
JdbcTemplate jdbcTemplate = getjdbcTemplate();
//更新数据
String sql = "update student set name='duyanju' where id = ?";
int update = jdbcTemplate.update(sql, 3);
System.out.println(update);
}
@Test
public void insert(){
JdbcTemplate jdbcTemplate = getjdbcTemplate();
String sql = "insert into student (id,name,cid) values(?,?,?)";
int xiaoming = jdbcTemplate.update(sql, 4, "xiaoming", 1);
System.out.println(xiaoming);
}
@Test
public void delete(){
JdbcTemplate jdbcTemplate = getjdbcTemplate();
String sql = "delete from student where id = ?";
int update = jdbcTemplate.update(sql,2);
System.out.println(update);
}
/*
*queryFormap这个方法查询的结果长度只能为1
*/
@Test
public void queryFormap(){
JdbcTemplate jdbcTemplate = getjdbcTemplate();
String sql = "select t1.id,t1.name,t2.name as cname from student t1,class t2 where t1.cid=t2.cid and t1.id=1";
Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql);
System.out.println(stringObjectMap);
//{id=1, name=小明, cname=高二1班}
}
/*
将每条记录封装为map集合，再将Map集合装载到List集合中
*/
@Test
public void queryForlist(){
JdbcTemplate jdbcTemplate = getjdbcTemplate();
String sql = "select t1.id,t1.name,t2.name as cname from student t1,class t2 where t1.cid=t2.cid";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
System.out.println(maps);
//[{id=1, name=小明, cname=高二1班}, {id=3, name=duyanju, cname=高一2班}, {id=4, name=xiaoming, cname=高二1班}]
}
/*
* 将其封装为对象，再装载到list集合中
* */
@Test
public void query(){
JdbcTemplate jdbcTemplate = getjdbcTemplate();
String sql = "select t1.id,t1.name,t2.name as cname from student t1,class t2 where t1.cid=t2.cid";
//匿名内部类
List<Student> list = jdbcTemplate.query(sql, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet resultSet, int i) throws SQLException {
Student student = new Student();
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String cname = resultSet.getString("cname");
student.setId(id);
student.setName(name);
student.setClassname(cname);
return student;
}
});
System.out.println(list);
//[Student{id=1, name='小明', classname='高二1班'}, Student{id=3, name='duyanju', classname='高一2班'}, Student{id=4, name='xiaoming', classname='高二1班'}]
}
@Test
public void query1(){
JdbcTemplate jdbcTemplate = getjdbcTemplate();
String sql = "select t1.id,t1.name,t2.name as cname from student t1,class t2 where t1.cid=t2.cid";
//匿名内部类
List<Student> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
System.out.println(list);
//[Student{id=1, name='小明', classname='高二1班'}, Student{id=3, name='duyanju', classname='高一2班'}, Student{id=4, name='xiaoming', classname='高二1班'}]
}
/*
*查询总记录数
*/
@Test
public void queryForObject(){
JdbcTemplate jdbcTemplate = getjdbcTemplate();
String sql = "select count(id) from student";
Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(integer);
//3
}
}
工具类
