一、什么是JdbcTemplate
- Spring框架对jdbc进行封装,使用JdbcTemplate方便实现对数据库操作
二、如何使用
1. 导入相关jar包
2. 配置数据库连接池
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="url" value="${jdbc.url}"></property>
<property name="driverClassName" value="${jdbc.driver}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.pass}"></property>
</bean>
- 这里需要引入外部properties文件,需要引入相关配置
<context:property-placeholder location="classpath:prop.properties"></context:property-placeholder>
- prop.properties文件中写入数据库配置
jdbc.url=jdbc:mysql://localhost:3306/workspace
jdbc.driver=com.mysql.jdbc.Driver
jdbc.username=root
jdbc.pass=123456
3. 配置JdbcTemplate对象,注入DataSource
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
4.创建service、dao并注入jdbcTemplate对象
- 首先组件扫描
<context:component-scan base-package="com.github"></context:component-scan>
- 创建User类
public class User {
private Integer id;
private String username;
private String gender;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", gender='" + gender + '\'' +
'}';
}
}
- 在数据库创建对应的数据表
create table user(
id int primary key auto_increment,
username varchar(20),
gender varchar(5)
);
- 创建dao接口,以及实现类
public interface IUserDao {
void add(User user);
}
@Repository
public class UserDaoImpl implements IUserDao {
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public void add(User user) {
String sql = "insert into user(username,gender) values(?,?)";
int i = jdbcTemplate.update(sql,user.getUsername(),user.getGender());
System.out.println(i);
}
}
- 创建Service
@Service
public class UserService {
@Autowired
IUserDao iUserDao;
public void add(User user){
iUserDao.add(user);
}
}
- 这时我们调用service就可以与数据库进行交互
5. CRUD操作
5.1 插入
- 上面的dao中我们编写了插入放法,编写测试类进行调用
@Test
public void test(){
ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
UserService userService = context.getBean(UserService.class);
User user = new User();
user.setUsername("李斯");
user.setGender("男");
userService.add(user);
}
- 运行我们查看数据库,可以发现已经插入了该数据
5.2 修改
- 在dao中新增方法
@Override
public void update(User user) {
String sql = "update user set username=? ,gender=? where id = ?";
int res = jdbcTemplate.update(sql,user.getUsername(),user.getGender(),user.getId());
System.out.println(res);
}
- 重新编写测试方法,运行测试类
5.3 删除
- 在dao新增方法
@Override
public void delect(int id) {
String sql = "delete from user where id = ?";
int res = jdbcTemplate.update(sql,id);
System.out.println(res);
}
- 运行结果
5.4 查找
5.4.1 返回一个值
- queryForObject(String sql,Class requiredType)
第一个参数:SQL语句
第二个参数:返回类型Class
- 新增方法
@Override
public int selectcount() {
String sql = "select count(1) from user";
return jdbcTemplate.queryForObject(sql,Integer.class);
}
- 在数据库添加三条模拟数据
5.4.2 返回对象
- queryForObject(String sql, RowMapper rowMapper, Object … args)
第一个参数:sql语句
第二个参数:RowMapper接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
第三个参数:SQL语句中的值
- 新增方法
@Override
public User selectbyid(int id) {
String sql = "select * from user where id=?";
User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);
return user;
}
5.4.3 返回一个集合
- query(String sql, RowMapper rowMapper, object … args)
第一个参数:sql语句
第二个参数:RowMapper接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
第三个参数:SQL语句中的值
- 新增方法
@Override
public List<User> select() {
String sql= "select * from user";
List<User> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class));
return list;
}
5.5 批量插入
- batchUpdate(String sql, List<Object[]> batcgArgs)
第一个参数:sql语句
第二个参数:List集合,添加多条记录
- 测试类
List<Object[]> list = new ArrayList();
Object[] objects = {"malo","男"};
Object[] objects1 = {"lucy","女"};
list.add(objects);
list.add(objects1);
userService.batchadd(list);
- 结果
-批量删除和修改与插入相同,都是调用batchUpdate方法
- 下面将所有代码放在下面
- IUserDao接口
public interface IUserDao {
void add(User user);
void delect(int id);
void update(User user);
int selectcount();
User selectbyid(int id);
List<User> select();
void batchadd(List<Object[]> list);
}
- UserDaoImpl
@Repository
public class UserDaoImpl implements IUserDao {
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public void add(User user) {
String sql = "insert into user(username,gender) values(?,?)";
int res = jdbcTemplate.update(sql,user.getUsername(),user.getGender());
System.out.println(res);
}
@Override
public void delect(int id) {
String sql = "delete from user where id = ?";
int res = jdbcTemplate.update(sql,id);
System.out.println(res);
}
@Override
public void update(User user) {
String sql = "update user set username=? ,gender=? where id = ?";
int res = jdbcTemplate.update(sql,user.getUsername(),user.getGender(),user.getId());
System.out.println(res);
}
@Override
public int selectcount() {
String sql = "select count(1) from user";
return jdbcTemplate.queryForObject(sql,Integer.class);
}
@Override
public User selectbyid(int id) {
String sql = "select * from user where id=?";
User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);
return user;
}
@Override
public List<User> select() {
String sql= "select * from user";
List<User> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class));
return list;
}
@Override
public void batchadd(List<Object[]> list) {
String sql="insert into user(username,gender) values(?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql,list);
System.out.println(ints);
}
}
- UserService
@Service
public class UserService {
@Autowired
IUserDao iUserDao;
public void add(User user){
iUserDao.add(user);
}
public void delect(int id){
iUserDao.delect(id);
}
public void update(User user){
iUserDao.update(user);
}
public int selectcount(){
return iUserDao.selectcount();
}
public User selectbyid(int id){
User user = iUserDao.selectbyid(id);
return user;
}
public List<User> select(){
List<User> list = iUserDao.select();
return list;
}
public void batchadd(List<Object[]> list){
iUserDao.batchadd(list);
}
}
- 测试方法
public class UserServiceTest {
@Test
public void test(){
ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
UserService userService = context.getBean(UserService.class);
// User user = new User();
// user.setUsername("李斯");
// user.setGender("男");
// userService.add(user);
// User user = new User();
// user.setId(1);
// user.setUsername("张三");
// user.setGender("女");
// userService.update(user);
// userService.delect(1);
// System.out.println(userService.selectcount());
// User user = userService.selectbyid(2);
// System.out.println(user);
// ArrayList list = (ArrayList) userService.select();
// System.out.println(list);
List<Object[]> list = new ArrayList();
Object[] objects = {"malo","男"};
Object[] objects1 = {"lucy","女"};
list.add(objects);
list.add(objects1);
userService.batchadd(list);
}
}