1. JdbcTemplate 操作数据库(添加)
1. 创建对应的实体类
package com.demo.study_spring.stu_jdbctemplate.entity;
public class User {
private int id;
private String username;
private int stste;
private String phone;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getStste() {
return stste;
}
public void setStste(int stste) {
this.stste = stste;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
2. 编写service和dao
service
package com.demo.study_spring.stu_jdbctemplate.service;
import com.demo.study_spring.stu_jdbctemplate.dao.MyDao;
import com.demo.study_spring.stu_jdbctemplate.entity.User;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@Service
public class MyserviceImpl implements MyService{
@Resource
private MyDao myDao;
@Override
public void add(User user) {
myDao.addUser(user);
}
}
1. 在dao进行数据库添加操作
package com.demo.study_spring.stu_jdbctemplate.dao;
import com.demo.study_spring.stu_jdbctemplate.entity.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
@Repository
public class MyDaoImpl implements MyDao {
@Resource
private JdbcTemplate jdbcTemplate;
@Override
public void addUser(User user) {
// 创建Sql语句
String sql = "instert into user (username, state, phone) values(?,?,?)";
// 调用方法实现
int update = jdbcTemplate.update(sql, user.getUsername(), user.getState(), user.getPhone());
System.out.println(update);
}
}
2. 调用JdbcTempalte 对象里面 update 方法实现。
有两个参数,第一个参数:sql语句;第二个参数为可变参数,设置sql语句的值。
3. 再贴下配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
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/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<!--开启组件扫描-->
<context:component-scan base-package="com.demo.study_spring"></context:component-scan>
<!--数据库连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://39.105.161.73:3306/test" />
<property name="username" value="root" />
<property name="password" value="root" />
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
</bean>
<!--创建JdbcTemplate 对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入dataSource-->
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
4. 测试方法:
@Test
public void textUser() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
MyserviceImpl myServiceImpl = context.getBean("myserviceImpl", MyserviceImpl.class);
com.demo.study_spring.stu_jdbctemplate.entity.User user = new com.demo.study_spring.stu_jdbctemplate.entity.User();
user.setUsername("zhangsan");
user.setState(1);
user.setPhone("18366665786");
myServiceImpl.add(user);
}
2. JdbcTemplate 操作数据库(修改和删除)
service
package com.demo.study_spring.stu_jdbctemplate.service;
import com.demo.study_spring.stu_jdbctemplate.entity.User;
public interface MyService {
void add(User user);
void update(User user);
void delete(int id);
}
serviceimpl
package com.demo.study_spring.stu_jdbctemplate.service;
import com.demo.study_spring.stu_jdbctemplate.dao.MyDao;
import com.demo.study_spring.stu_jdbctemplate.entity.User;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@Service
public class MyserviceImpl implements MyService{
@Resource
private MyDao myDao;
@Override
public void add(User user) {
myDao.addUser(user);
}
@Override
public void update(User user) {
myDao.updateUser(user);
}
@Override
public void delete(int id) {
myDao.delete(id);
}
}
dao
package com.demo.study_spring.stu_jdbctemplate.dao;
import com.demo.study_spring.stu_jdbctemplate.entity.User;
public interface MyDao {
void addUser(User user);
void updateUser(User user);
void delete(int id);
}
daoimpl
package com.demo.study_spring.stu_jdbctemplate.dao;
import com.demo.study_spring.stu_jdbctemplate.entity.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
@Repository
public class MyDaoImpl implements MyDao {
@Resource
private JdbcTemplate jdbcTemplate;
@Override
public void addUser(User user) {
// 创建Sql语句
String sql = "insert into user (username, state, phone) values(?,?,?)";
// 调用方法实现
int update = jdbcTemplate.update(sql, user.getUsername(), user.getState(), user.getPhone());
System.out.println(update);
}
@Override
public void updateUser(User user) {
String sql = "update user set username=?,state =?,phone =? where id = ?";
int update = jdbcTemplate.update(sql, user.getUsername(), user.getState(), user.getPhone(), user.getId());
System.out.println(update);
}
@Override
public void delete(int id) {
String sql = "delete from user where id= ?";
int update = jdbcTemplate.update(sql, id);
System.out.println(update);
}
}
测试方法:
@Test
public void testUserUpdate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
MyserviceImpl myServiceImpl = context.getBean("myserviceImpl", MyserviceImpl.class);
com.demo.study_spring.stu_jdbctemplate.entity.User user1 = new com.demo.study_spring.stu_jdbctemplate.entity.User();
user1.setId(1);
user1.setPhone("18588888888");
user1.setState(1);
user1.setUsername("zhangsan");
myServiceImpl.update(user1);
}
@Test
public void testUserDelete() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
MyserviceImpl myServiceImpl = context.getBean("myserviceImpl", MyserviceImpl.class);
myServiceImpl.delete(3);
}
3. JdbcTemplate 操作数据库 (查询)
1. 查询返回某个值
1.查询表里有多少条记录,返回是某个值
2. 使用JdbcTemplate实现查询返回某个值
service
/**
* 查询表中记录数
*/
int getCount();
serviceImpl
@Override
public int getCount() {
int i = myDao.selectCount();
return i;
}
dao
int selectCount();
daoImpl
@Override
public int selectCount() {
String sql = "select count(id) from user";
Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
return integer;
}
测试类
@Test
public void testSelectCount() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
MyserviceImpl myServiceImpl = context.getBean("myserviceImpl", MyserviceImpl.class);
int count = myServiceImpl.getCount();
System.out.println(count);
}
2. 查询返回某个对象
第一个参数:sql语句
第二个参数:RowMapper,是接口,返回不同类型的数据,使用这个接口可以实现完成数据的封装
第三个参数:sql语句的值
service
User getOne(int id);
serviceImpl
@Override
public User getOne(int id) {
return myDao.selectOne(id);
}
dao
User selectOne(int id);
daoImpl
@Override
public User selectOne(int id) {
String sql = "select * from user where id = ?";
User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
return user;
}
测试类
@Test
public void testGetOne() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
MyserviceImpl myServiceImpl = context.getBean("myserviceImpl", MyserviceImpl.class);
com.demo.study_spring.stu_jdbctemplate.entity.User one = myServiceImpl.getOne(1);
System.out.println(one);
}
3,查询返回集合
第一个参数:sql语句
第二个参数: RowMapper,是接口,返回不同类型的数据,使用这个接口可以实现完成数据的封装
第三个参数:sql语句的值
service
List<User> getList();
serviceImpl
@Override
public List<User> getList() {
return myDao.selectList();
}
dao
List<User> selectList();
daoImpl
@Override
public List<User> selectList() {
String sql = "select * from user";
List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
return userList;
}
测试类
@Test
public void testGetList() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
MyserviceImpl myServiceImpl = context.getBean("myserviceImpl", MyserviceImpl.class);
List<com.demo.study_spring.stu_jdbctemplate.entity.User> list = myServiceImpl.getList();
System.out.println(list);
}
4. JdbcTemplate 批量增加
第一个参数:sql
第二个参数:List集合,添加多条记录数据
service
void batchAdd(List<Object []> users);
serviceImpl
@Override
public void batchAdd(List<Object []> users) {
myDao.batchAdd(users);
}
dao
void batchAdd(List<Object []> users);
daoImpl
@Override
public void batchAdd(List<Object []> users) {
String sql = "insert into user (username, state, phone) values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, users);
System.out.println(Arrays.toString(ints));
}
}
测试方法
@Test
public void testBatchSave() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
MyserviceImpl myServiceImpl = context.getBean("myserviceImpl", MyserviceImpl.class);
Object [] o1 = {"lisi", "1", "18233334444"};
Object [] o2 = {"wangwu", "0", "18233335555"};
List<Object []> list = new ArrayList<>();
list.add(o1);
list.add(o2);
myServiceImpl.batchAdd(list);
System.out.println(list);
}
5. JdbcTemplate 批量修改和批量删除
service
void bactchUpdate(List<Object []> users);
void bactchDelete(List<Object []> users);
serviceImpl
@Override
public void bactchUpdate(List<Object[]> users) {
myDao.batchUpdate(users);
}
@Override
public void bactchDelete(List<Object[]> users) {
myDao.batchDelete(users);
}
dao
void batchUpdate(List<Object []> users);
void batchDelete(List<Object []> users);
daoImpl
@Override
public void batchUpdate(List<Object[]> users) {
String sql = "update user set username=?,state =?,phone =? where id = ?";
int[] ints = jdbcTemplate.batchUpdate(sql, users);
System.out.println(Arrays.toString(ints));
}
@Override
public void batchDelete(List<Object[]> users) {
String sql = "delete from user where id= ?";
int[] ints = jdbcTemplate.batchUpdate(sql, users);
System.out.println(Arrays.toString(ints));
}
测试类
@Test
public void testBatchUpdate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
MyserviceImpl myServiceImpl = context.getBean("myserviceImpl", MyserviceImpl.class);
Object [] o1 = {"zhaoliu", "1", "18233334444", 2};
Object [] o2 = {"wangwu", "1", "18233335555", 5};
List<Object []> list = new ArrayList<>();
list.add(o1);
list.add(o2);
myServiceImpl.bactchUpdate(list);
System.out.println(list);
}
@Test
public void testBatchDelete() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean3.xml");
MyserviceImpl myServiceImpl = context.getBean("myserviceImpl", MyserviceImpl.class);
Object [] o1 = {4};
Object [] o2 = {5};
List<Object []> list = new ArrayList<>();
list.add(o1);
list.add(o2);
myServiceImpl.bactchDelete(list);
System.out.println(list);
}