什么是 JdbcTemplate
Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作
环境
在Spring配置文件中配置数据库连接池
<!--引入外部属性文件--> <context:property-placeholder location="classpath:jdbc.properties"/>
<!--配置连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${prop.driverClass}"/>
<property name="url" value="${prop.url}"/>
<property name="username" value="${prop.userName}"/>
<property name="password" value="${prop.password}"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
prop.driverClass=com.mysql.cj.jdbc.Driver
prop.url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
prop.userName=root
prop.password=123456
#jdbc.properties配置文件
示例
//表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`userId` int(0) NOT NULL AUTO_INCREMENT,
`userName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`userId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
package com.fly.spring5.entity;
public class User {
private int userId;
private String userName;
public int getUserId() {
return userId;
}
@Override
public String toString() {
return "User{" +
"userId=" + userId +
", userName='" + userName + '\'' +
'}';
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public User() {
}
public User(int userId, String userName) {
this.userId = userId;
this.userName = userName;
}
}
添加
package com.fly.spring5.dao;
import com.fly.spring5.entity.User;
public interface UserDao {
//添加方法
public void add(User user);
}
package com.fly.spring5.dao.impl;
import com.fly.spring5.dao.UserDao;
import com.fly.spring5.entity.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
@Repository
public class UserDaoImpl implements UserDao {
@Resource
private JdbcTemplate jdbcTemplate;
@Override
public void add(User user) {
String sql = "insert into user values(null,?)";
jdbcTemplate.update(sql,user.getUserName());
}
}
package com.fly.spring5.service;
import com.fly.spring5.entity.User;
public interface UserService {
public void add(User user);
}
package com.fly.spring5.service.impl;
import com.fly.spring5.dao.UserDao;
import com.fly.spring5.entity.User;
import com.fly.spring5.service.UserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@Service
public class UserServiceImpl implements UserService {
@Resource
private UserDao userDao;
@Override
public void add(User user) {
userDao.add(user);
}
}
测试方法
@Test
public void test1() {
ApplicationContext context =
new ClassPathXmlApplicationContext("ApplicationContent.xml");
UserServiceImpl userServiceImpl = context.getBean("userServiceImpl", UserServiceImpl.class);
User user = new User();
user.setUserName("张三");
userServiceImpl.add(user);
}
删除
//UserDao
public void deleteById(Integer userId);
@Override
public void deleteById(Integer userId) {
//UserDaoImpl
String sql = "delete from user where userId = ?";
jdbcTemplate.update(sql,userId);
}
//UserService
void deleteById(Integer userId);
@Override
public void deleteById(Integer userId) {
//UserServiceImpl
userDao.deleteById(userId);
}
@Test
public void test2() {
ApplicationContext context =
new ClassPathXmlApplicationContext("ApplicationContent.xml");
UserServiceImpl userServiceImpl = context.getBean("userServiceImpl", UserServiceImpl.class);
userServiceImpl.deleteById(1);
}
修改
//UserDao
void update(User user);
@Override
public void update(User user) {
//UserDaoImpl
String sql = "update user set userName = ? where userId = ?";
jdbcTemplate.update(sql,user.getUserName(),user.getUserId());
}
//UserService
void update(User user);
@Override
public void update(User user) {
//UserServiceImpl
userDao.update(user);
}
@Test
public void test3() {
ApplicationContext context =
new ClassPathXmlApplicationContext("ApplicationContent.xml");
UserServiceImpl userServiceImpl = context.getBean("userServiceImpl", UserServiceImpl.class);
//数据库现在没数据
User user = new User();
user.setUserId(3);
user.setUserName("张三2");
userServiceImpl.update(user);
}
简单查询
//UserDao
int count();
@Override
public int count() {
//UserDaoImpl
String sql = "select count(*) from user";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
//UserService
int count();
@Override
public int count() {
//UserServiceImpl
return userDao.count();
}
@Test
public void test4() {
ApplicationContext context =
new ClassPathXmlApplicationContext("ApplicationContent.xml");
UserServiceImpl userServiceImpl = context.getBean("userServiceImpl", UserServiceImpl.class);
int count = userServiceImpl.count();
System.out.println("count = " + count);
}
查询返回对象
//UserDao
User selectById(Integer userId);
@Override
public User selectById(Integer userId) {
//UserDaoImpl
String sql = "select * from user where userId = ?";
User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), userId);
return user;
}
//UserService
User selectById(Integer userId);
@Override
public User selectById(Integer userId) {
//UserServiceImpl
return userDao.selectById(userId);
}
@Test
public void test5() {
ApplicationContext context =
new ClassPathXmlApplicationContext("ApplicationContent.xml");
UserServiceImpl userServiceImpl = context.getBean("userServiceImpl", UserServiceImpl.class);
User user = userServiceImpl.selectById(3);
System.out.println("user = " + user);
}
查询返回集合
//UserDao
List<User> getUsers();
@Override
public List<User> getUsers() {
//UserDaoImpl
String sql = "select * from user";
List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
return userList;
}
//UserService
List<User> getUsers();
@Override
public List<User> getUsers() {
//UserServiceImpl
return userDao.getUsers();
}
@Test
public void test6() {
ApplicationContext context =
new ClassPathXmlApplicationContext("ApplicationContent.xml");
UserServiceImpl userServiceImpl = context.getBean("userServiceImpl", UserServiceImpl.class);
List<User> users = userServiceImpl.getUsers();
System.out.println("users = " + users);
}