Spring学习笔记（十三）JdbcTemplate工具类的使用
JdbcTemplate就是Spring框架给我们封装好的简单对数据库操作的工具类，返回Map数据，返回List_Map数据，返回一个值，返回一个对象，返回一个List<对象>，添加修改删除
源码获取github
1.介绍JdbcTemplate工具类
Spring是一个IoC和AOP的容器框架，并且它是一站式开源框架
2.sql文件
SET FOREIGN_KEY_CHECKS=0;
------
-- Table structure for oa_user
------
DROP TABLE IF EXISTS `oa_user`;
CREATE TABLE `oa_user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(255) DEFAULT NULL, `sex` varchar(255) DEFAULT NULL, `money` decimal(7,2) DEFAULT NULL, `birthday` date DEFAULT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;
------
-- Records of oa_user
------
INSERT INTO `oa_user` VALUES ('1', '悟空', '男', '888.00', null);
INSERT INTO `oa_user` VALUES ('2', '八戒', '男', '999.00', null);
INSERT INTO `oa_user` VALUES ('3', '天蓬元帅', '女', '1000.00', null);
INSERT INTO `oa_user` VALUES ('4', '悟能', '男', '666.00', null);
INSERT INTO `oa_user` VALUES ('5', '法悟法', '女', '544.00', null);
INSERT INTO `oa_user` VALUES ('6', '悟', '女', '562.00', null);
INSERT INTO `oa_user` VALUES ('7', '悟', '女', '562.00', null);
INSERT INTO `oa_user` VALUES ('14', '嫦娥', '女', '1213.00', null);
INSERT INTO `oa_user` VALUES ('15', '月兔', '女', '2113.00', null);
INSERT INTO `oa_user` VALUES ('16', '和尚', '男', '455.00', '2018-08-05');
INSERT INTO `oa_user` VALUES ('18', '沙僧', '男', '455.00', '2018-08-05');
INSERT INTO `oa_user` VALUES ('19', '唐僧', '男', '220.00', '2018-08-09');
INSERT INTO `oa_user` VALUES ('20', '唐僧', '男', '220.00', '2018-08-09');
------
-- Table structure for skill
------
DROP TABLE IF EXISTS `skill`;
CREATE TABLE `skill` ( `skill_id` int(11) NOT NULL AUTO_INCREMENT, `skill_name` varchar(255) DEFAULT NULL, `num` int(11) DEFAULT NULL, `fk_teacher_id` int(11) DEFAULT NULL, PRIMARY KEY (`skill_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
------
-- Records of skill
------
INSERT INTO `skill` VALUES ('2', 'JS', '75', null);
INSERT INTO `skill` VALUES ('7', 'CSS', '66', null);
INSERT INTO `skill` VALUES ('8', 'java', '66', null);
INSERT INTO `skill` VALUES ('9', 'vue', '56', '9999');
------
-- Table structure for sys_role
------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` ( `role_id` int(11) NOT NULL AUTO_INCREMENT, `role_name` varchar(32) DEFAULT NULL, `role_key` varchar(32) DEFAULT NULL, `status` int(1) DEFAULT '1' COMMENT '1可用 -1禁用', PRIMARY KEY (`role_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
------
-- Records of sys_role
------
INSERT INTO `sys_role` VALUES ('-100', '超级管理员', 'ADMIN', '1');
------
-- Table structure for sys_user
------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `account` varchar(32) NOT NULL, `password` varchar(128) NOT NULL, `user_name` varchar(32) DEFAULT '', `status` int(1) NOT NULL DEFAULT '1' COMMENT '1可用 -1删除 2禁用', `login_time` datetime DEFAULT CURRENT_TIMESTAMP, `ip` varchar(128) DEFAULT NULL, `fk_role_id` int(11) DEFAULT NULL, PRIMARY KEY (`user_id`), KEY `fk_role_id` (`fk_role_id`), CONSTRAINT `sys_user_ibfk_1` FOREIGN KEY (`fk_role_id`) REFERENCES `sys_role` (`role_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
------
-- Records of sys_user
------
INSERT INTO `sys_user` VALUES ('-999', 'super', 'super', '唐僧', '1', '2018-07-28 18:47:39', null, '-100');
INSERT INTO `sys_user` VALUES ('-888', 'admin', 'admin', '悟空', '1', '2018-07-03 18:48:21', null, '-100');3.实例化该工具类
<bean class="org.springframework.jdbc.core.JdbcTemplate" >
<property name="dataSource" 赋值/>
------
</bean>4.配置数据源
数据源就是连接数据，而以后工作当中我们推荐使用“连接池”，
什么是连接池？
我的理解为预先建立好一堆Connection，使用时候从池中获取，不适应不是销毁而是把连接再次放回到池中
连接池的工作原理？
Java代码如何实现–>Map/List 存Connection
开源的连接池有哪些？
C3p0、dbcp、德鲁伊druid(阿里巴巴的)
<!-- destroy-method="close"销毁的方法-->
<!--4.配置数据源-->
<bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/cy42_mss"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</bean>
<!--5.配置工具类JdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--setter注入方式-->
<property name="dataSource" ref="druidDataSource"/>
<!--构造函数赋值-->
<!--<constructor-arg name="dataSource" ref="druidDataSource"/>-->
</bean>5.User.java
package com.hs.model;
import java.math.BigDecimal;
import java.util.Date;
public class User {
private Integer user_id;
private String user_name;
private String sex;
//钱用BigDecimal
private BigDecimal money;
private Date birthday;
public BigDecimal getMoney() {
return money;
}
public void setMoney(BigDecimal money) {
this.money = money;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "User{" +
"user_id=" + user_id +
", user_name='" + user_name + '\'' +
", sex='" + sex + '\'' +
", money=" + money +
", birthday=" + birthday +
'}';
}
}6.查询操作(预处理)
1.返回Map数据
package com.hs.test;
import com.hs.model.User;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;
public class JdbcTemplateTest {
private ApplicationContext ac;
private JdbcTemplate jdbcTemplate;
@Before
public void init() {
ac = new ClassPathXmlApplicationContext("beans.xml");
jdbcTemplate = ac.getBean("jdbcTemplate", JdbcTemplate.class);
}
/** * 返回Map数据 */
@Test
public void test01() {
/*String sql = "select * from oa_user where user_id = 3"; Map<String, Object> map = this.jdbcTemplate.queryForMap(sql);*/
String sql = "select * from oa_user where user_id = ?";
Map<String, Object> map = jdbcTemplate.queryForMap(sql, 2);
System.out.println(map);
}
}2.返回List_Map数据
/** * 返回List_Map数据 */
@Test
public void test02() {
String sql = "select * from oa_user";
System.out.println(jdbcTemplate.queryForList(sql));
}3.返回一个值
/** * 返回一个值 */
@Test
public void test03() {
String sql = "select count(*) from oa_user";
System.out.println(jdbcTemplate.queryForObject(sql, Integer.class));
}4.返回一个对象
/** * 返回一个对象 */
@Test
public void test04() {
String sql = "select * from oa_user where user_id = ?";
User user = jdbcTemplate.queryForObject(sql, /*匿名内部类*/new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setUser_id(rs.getInt("user_id"));
user.setUser_name(rs.getString("User_name"));
user.setSex(rs.getString("sex"));
user.setMoney(rs.getBigDecimal("money"));
user.setBirthday((Date) rs.getObject("birthday"));
return user;
}
}, 2);
System.out.println(user);
}5.返回一个List<对象>
/** * 返回一个List<对象> */
@Test
public void test05() {
String sql = "select * from oa_user where user_name like concat('%',?,'%')";
List<User> userList = jdbcTemplate.query(sql,/*处理一条一条的数据*/new UserRowMapper(), "沙僧");
System.out.println(userList);
}把匿名内部类提出来
/** * 对于外面那个类来说，这是一个内部类，外面那个类对于这个类来说，是外部类 * 百度什么是内部类和特种？ * private使这个类只能被这个类使用 */
private class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
System.out.println("---------");
User user = new User();
user.setUser_id(rs.getInt("user_id"));
user.setUser_name(rs.getString("User_name"));
user.setSex(rs.getString("sex"));
user.setMoney(rs.getBigDecimal("money"));
user.setBirthday((Date) rs.getObject("birthday"));
return user;
}
}6.返回一个List<对象>——自动映射，类属性和查询的字段名字一样
/** * 返回一个List<对象>——自动映射，类属性和查询的字段名字一样 */
@Test
public void test06() {
String sql = "select * from oa_user where user_name like concat('%',?,'%')";
List<User> userList = jdbcTemplate.query(sql,/*处理一条一条的数据，，，<User>可以不用写*/new BeanPropertyRowMapper<User>(User.class), "沙僧");
System.out.println(userList);
}7.添加修改删除
/** * 添加修改删除 */
@Test
public void test07() {
String sql1 = "insert into oa_user(user_name,sex,money,birthday) values(?,?,?,?)";
String sql2 = "update oa_user set user_name=?,sex=? where user_id = ?";
String sql3 = "delete from oa_user where user_id = ?";
int num1 = jdbcTemplate.update(sql1, "唐僧", "男", 220.00, "2018-8-9");
int num2 = jdbcTemplate.update(sql2, "和尚", "男", 16);
int num3 = jdbcTemplate.update(sql3, 17);
System.out.println(num1+"--"+num2+"---"+num3);
}
