一、概念
-
什么是JdbcTemplate
- Spring框架对JDBC进行封装,使用JdbcTemplate方便实现对数据库操作
-
准备工作
-
引入相关jar包
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CsTHYEo1-1621684944155)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210522160544643.png)]
-
在spring配置文件配置数据库连接池
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql:///user_db"></property> <property name="username" value="root"/> <property name="password" value="root"/> </bean>
-
配置JdbcTemplate对象,注入DataSource
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean>
-
创建service类,创建dao类,在dao类注入JdbcTemplate
@Service public class BookService { @Autowired private BookDao bookDao; } @Repository public class BookDaoImpl implements BookDao { @Autowired private JdbcTemplate jdbcTemplate; }
-
二、JdbcTemplate操作数据库(添加)
-
对应数据库创建实体类
package com.gdcp.entity; public class User { private String UserId; private String UserName; private String UserStatus; public String getUserId() { return UserId; } public void setUserId(String userId) { UserId = userId; } public String getUserName() { return UserName; } public void setUserName(String userName) { UserName = userName; } public String getUserStatus() { return UserStatus; } public void setUserStatus(String userStatus) { UserStatus = userStatus; } }
-
编写service和dao
-
在dao进行数据库添加操作
@Repository public class BookDaoImpl implements BookDao { @Autowired private JdbcTemplate jdbcTemplate; @Override public void add(User user) { String sql = "insert into tb_user values(?,?,?)"; int update = jdbcTemplate.update(sql, user.getUserId(), user.getUserName(), user.getUserStatus()); System.out.println(update); } }
-
-
测试类
@Test public void add() { ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); BookService bookService = context.getBean("bookService", BookService.class); User user = new User(); user.setUserId("1"); user.setUserName("admin"); user.setUserStatus("super"); bookService.add(user); }
三、JdbcTemplate操作数据库(修改和删除)
-
//修改 @Override public void update(User user) { String sql = "update tb_user set userName = ? ,userStatus = ? where userId = ?"; int update = jdbcTemplate.update(sql, user.getUserName(), user.getUserStatus(), user.getUserId()); System.out.println(update); } //删除 @Override public void delete(String id) { String sql = "delete from tb_user where userId = ?"; int update = jdbcTemplate.update(sql, id); System.out.println(update); }
四、JdbcTemplate操作数据库(查询返回某个值)
-
查询表中有多少条记录,返回是某个值
-
使用JdbcTemplate实现查询返回某个值代码
@Override public int selectCount() { String sql = "select count(*) from tb_user"; Integer count = jdbcTemplate.queryForObject(sql, Integer.class); return count; }
五、JdbcTemplate操作数据库(查询返回对象)
-
场景:查询图书详情
-
JdbcTemplate实现查询返回对象
@Override public User queryForOneById(String id) { String sql = "select * from tb_user where userId = ?"; User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id); return user; }
六、JdbcTemplate操作数据库(查询返回集合)
-
场景:查询所有记录
-
JdbcTemplate实现查询返回集合
@Override public List<User> queryForAll() { String sql = "select * from tb_user"; List<User> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class)); return query; }
七、JdbcTemplate操作数据库(批量操作)
-
批量操作:操作表里面多条记录
-
JdbcTemolate实现批量添加操作
-
使用batchUpdate(String sql,List<Object[]> batchArgs);
参数一:sql语句
参数二:list集合,添加多条记录数据
-
实现批量添加代码
@Override public void insertForList(List<Object[]> users) { String sql = "insert into tb_user values(?,?,?)"; int[] ints = jdbcTemplate.batchUpdate(sql,users); System.out.println(Arrays.toString(ints)); }
-
-
JdbcTemolate实现批量修改操作
@Override public void updateForList(List<Object[]> users) { String sql = "update tb_user set userName = ? ,userStatus = ? where userId = ?"; int[] ints = jdbcTemplate.batchUpdate(sql,users); System.out.println(Arrays.toString(ints)); }
-
JdbcTemolate实现批量删除操作
@Override public void deleteForList(List<Object[]> users) { String sql = "delete from tb_user where userId = ?"; int[] ints = jdbcTemplate.batchUpdate(sql,users); System.out.println(Arrays.toString(ints)); }
public void deleteForList(List<Object[]> users) {
String sql = “delete from tb_user where userId = ?”;
int[] ints = jdbcTemplate.batchUpdate(sql,users);
System.out.println(Arrays.toString(ints));
}