Spring框架-thiteen(Spring-JdbcTemplate)
13-【掌握】Spring-JdbcTemplate
1、Spring对不同的持久化支持
Spring为各种支持的持久化技术，都提供了简单操作的模板和回调
其实Spring的JDBCTemplate有点像DBUtils，但是有时候还没有DBUitls好用。这里来学习一下使用Spring的JDBCTemplate来玩一下CRUD。
2、使用JdbcTemplate需要的jar包
在这里使用Spring的JDBCTemplate的时候先要把轮子拿过来
除此之外，在Java中操作数据库怎么能不要对应的驱动包呢：
3、准备数据库
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 50528
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50528
File Encoding : 65001
Date: 2019-03-08 17:44:00
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`user_name` varchar(255) DEFAULT NULL COMMENT '用户姓名',
`user_address` varchar(255) DEFAULT NULL COMMENT '用户地址',
`user_birthday` date DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES ('1', '小明1', '武汉1', '2019-01-03');
INSERT INTO `sys_user` VALUES ('2', '小明2', '武汉2', '2019-01-03');
INSERT INTO `sys_user` VALUES ('3', '小明3', '武汉3', '2019-01-03');
INSERT INTO `sys_user` VALUES ('4', '小明4', '武汉4', '2019-01-03');
INSERT INTO `sys_user` VALUES ('5', '小明5', '武汉5', '2019-01-03');
INSERT INTO `sys_user` VALUES ('6', '小明6', '武汉6', '2019-01-03');
INSERT INTO `sys_user` VALUES ('7', '小明7', '武汉7', '2019-01-03');
INSERT INTO `sys_user` VALUES ('8', '小明8', '武汉8', '2019-01-03');
INSERT INTO `sys_user` VALUES ('9', '小明9', '武汉9', '2019-01-03');
INSERT INTO `sys_user` VALUES ('10', '小明10', '武汉10', '2019-01-03');
4、使用JdbcTemplate连接数据库
5、上面代码的说明
以上的代码所有的对象都是new 出来的
6、使用xml的方式去配置上面的的代码
创建User
创建UserDao
创建UserDaoImpl
创建UserService
创建UserServiceImpl
创建applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
">
<!-- 声明数据源 -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!-- 注入数据库的连接属性 -->
<property name="driverClassName" value="com.mysql.jdbc.Driver">
</property>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test">
</property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<!-- 声明jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 注入数据源 -->
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 声明dao -->
<bean id="userDao" class="com.sxt.dao.impl.UserDaoImpl">
<!-- 注入jdbcTemplate -->
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<!-- 声明service -->
<bean id="userService" class="com.sxt.service.impl.UserServiceImpl">
<property name="userDao" ref="userDao"></property>
</bean>
</beans>
测试
7、使用注解的方式去配置上面的的代码
修改UserDaoImpl
修改UserServiceImpl
修改applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
">
<!-- 扫描 -->
<context:component-scan base-package="com.sxt.dao.impl">
</context:component-scan>
<context:component-scan base-package="com.sxt.service.impl">
</context:component-scan>
<!-- 声明数据源 -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!-- 注入数据库的连接属性 -->
<property name="driverClassName" value="com.mysql.jdbc.Driver">
</property>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test">
</property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<!-- 声明jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 注入数据源 -->
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
8、jdbcTemplate的API用法
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public Integer queryUserCount() {
String sql="select count(1) from sys_user";
return this.jdbcTemplate.queryForObject(sql, Integer.class);
}
@Override
public void addUser(User user) {
String sql="insert into sys_user(user_name,user_address,user_birthday)
values(?,?,?)";
Object[] args=
{user.getUserName(),user.getUserAddress(),user.getUserBirthday()};
jdbcTemplate.update(sql, args);
}
@Override
public void updateUser(User user) {
String sql="update sys_user set user_name=?,user_address=?,user_birthday=?
where user_id=?";
Object[] args={user.getUserName(),user.getUserAddress(),
user.getUserBirthday(),user.getUserId()};
jdbcTemplate.update(sql, args);
}
@Override
public void deleteUser(Integer userId) {
String sql="delete from sys_user where user_id=?";
Object[] args={userId};
jdbcTemplate.update(sql, args);
}
@Override
public User queryUserById(Integer userId) {
String sql="select * from sys_user where user_id=?";
// return jdbcTemplate.queryForObject(sql,new Object[]{userId}, User.class);
这种写法只能支持基本数据类型包装类和String
return jdbcTemplate.queryForObject(sql,new Object[]{userId},new
RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
Integer id=rs.getInt("user_id");
String userName=rs.getString("user_name");
String userAddress=rs.getString("user_address");
Date userBirthday=rs.getDate("user_birthday");
User user=new User(id, userName, userAddress, userBirthday);
return user;
}
});
}
@Override
public List<User> queryAllUser() {
String sql="select * from sys_user";
return jdbcTemplate.query(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
Integer id=rs.getInt("user_id");
String userName=rs.getString("user_name");
String userAddress=rs.getString("user_address");
Date userBirthday=rs.getDate("user_birthday");
User user=new User(id, userName, userAddress, userBirthday);
return user;
}
});
}
@Override
public List<User> queryUserForPage(Integer currentPage, Integer pageSize) {
String sql="select * from sys_user limit ?,?";
Object [] args={(currentPage-1)*pageSize,pageSize};
return jdbcTemplate.query(sql, args,new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
Integer id=rs.getInt("user_id");
String userName=rs.getString("user_name");
String userAddress=rs.getString("user_address");
Date userBirthday=rs.getDate("user_birthday");
User user=new User(id, userName, userAddress, userBirthday);
return user;
}
});
}
}