增删改查方法
先在创建spring数据库,并且创建user表,如下
创建文件夹,层级关系如下
User类
package com.example.demo.entity;
import java.math.BigDecimal;
public class User {
private Integer id;
private String username;
private BigDecimal money;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public BigDecimal getMoney() {
return money;
}
public void setMoney(BigDecimal money) {
this.money = money;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", money=" + money +
'}';
}
}
bean1.xml
<?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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
https://www.springframework.org/schema/context/spring-context.xsd">
<!--组件扫描-->
<context:component-scan base-package="com.example.demo"/>
<!--引入配置文件-->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!--数据库连接池-->
<bean id="datasource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${user}"/>
<property name="password" value="${password}"/>
</bean>
<!--配置jdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="datasource"/>
</bean>
</beans>
jdbc.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/spring?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8
user=root
password=123456
添加数据
单个数据添加
UserDao
package com.example.demo.dao;
import com.example.demo.entity.User;
public interface UserDao {
void add(User user);
}
UserDaoImpl
package com.example.demo.dao;
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
@Component
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void add(User user) {
String sql = "insert into user values (?, ?, ?)";
int i = jdbcTemplate.update(sql, user.getId(), user.getUsername(), user.getMoney());
System.out.println("成功了 " + i + " 条");
}
}
UserService
package com.example.demo.service;
import com.example.demo.dao.UserDao;
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService{
@Autowired
private UserDao userDao;
public void add(User user) {
userDao.add(user);
}
}
MyTest
package com.example.demo;
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.math.BigDecimal;
public class MyTest {
@Test
public void testJdbc() {
User user = new User();
user.setId(1);
user.setUsername("小明");
user.setMoney(BigDecimal.valueOf(500));
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService service = context.getBean("userService", UserService.class);
service.add(user);
}
}
执行结果
批量添加
UserDao
package com.example.demo.dao;
import java.util.List;
public interface UserDao {
void intsertMany(List<Object[]> args);
}
UserDaoImpl
package com.example.demo.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void intsertMany(List<Object[]> args) {
String sql = "insert into user values (?, ?, ?)";
int[] ints = jdbcTemplate.batchUpdate(sql, args);
for (int i = 0; i < ints.length; i++) {
if (ints[i] == 1) {
System.out.println("第 " + (i+1) + " 条成功");
continue;
}
System.out.println("第 " + (i+1) + " 条失败");
}
}
}
UserService
package com.example.demo.service;
import com.example.demo.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService{
@Autowired
private UserDao userDao;
public void insertMany(List<Object[]> args) {
userDao.intsertMany(args);
}
}
MyTest
package com.example.demo;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
public class MyTest {
@Test
public void testJdbc() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService service = context.getBean("userService", UserService.class);
List<Object[]> args = new ArrayList<>();
Object[] obj1 = {2, "小华", BigDecimal.valueOf(500)};
Object[] obj2 = {3, "小刚", BigDecimal.valueOf(500)};
Object[] obj3 = {4, "小红", BigDecimal.valueOf(500)};
args.add(obj1);
args.add(obj2);
args.add(obj3);
service.insertMany(args);
}
}
执行结果
查询数据
返回多个属性值
UserDao
package com.example.demo.dao;
import com.example.demo.entity.User;
public interface UserDao {
void findById(Integer id);
}
UserDaoImpl
package com.example.demo.dao;
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
@Component
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void findById(Integer id) {
String sql = "select * from user where id = ?";
//调用queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
//使用RowMapper接口的实现类BeanPropertyRowMapper实现数据封装
User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), 1);
System.out.println(user);
}
}
UserService
package com.example.demo.service;
import com.example.demo.dao.UserDao;
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService{
@Autowired
private UserDao userDao;
public void findById(Integer id) {
userDao.findById(id);
}
}
MyTest
package com.example.demo;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MyTest {
@Test
public void testJdbc() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService service = context.getBean("userService", UserService.class);
service.findById(1);
}
}
执行结果
返回某个值
UserDao
package com.example.demo.dao;
import com.example.demo.entity.User;
public interface UserDao {
void selectCount();
}
UserDaoImpl
package com.example.demo.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void selectCount() {
String sql = "select count(id) from user";
int i = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println("一共有 " + i + " 条数据");
}
}
UserService
package com.example.demo.service;
import com.example.demo.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService{
@Autowired
private UserDao userDao;
public void selectCount() {
userDao.selectCount();
}
}
MyTest
package com.example.demo;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MyTest {
@Test
public void testJdbc() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService service = context.getBean("userService", UserService.class);
service.selectCount();
}
}
执行结果
修改数据
单行数据修改
UserDao
package com.example.demo.dao;
import com.example.demo.entity.User;
public interface UserDao {
void updateById(User user);
}
UserDaoImpl
package com.example.demo.dao;
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
@Component
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void updateById(User user) {
String sql = "update user set username = ? where id = ?";
int i = jdbcTemplate.update(sql, user.getUsername(), user.getId());
System.out.println("更新了 " + i + " 条数据");
}
}
UserService
package com.example.demo.service;
import com.example.demo.dao.UserDao;
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService{
@Autowired
private UserDao userDao;
public void updateById(User user) {
userDao.updateById(user);
}
}
MyTest
package com.example.demo;
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MyTest {
@Test
public void testJdbc() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService service = context.getBean("userService", UserService.class);
User user = new User();
user.setId(1);
user.setUsername("小华");
service.updateById(user);
}
}
执行结果
批量修改
UserDao
package com.example.demo.dao;
import java.util.List;
public interface UserDao {
void updateMany(List<Object[]> args);
}
UserDaoImpl
package com.example.demo.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void updateMany(List<Object[]> args) {
String sql = "update user set username = ? where id = ?";
int[] ints = jdbcTemplate.batchUpdate(sql, args);
for (int i = 0; i < ints.length; i++) {
if (ints[i] == 1) {
System.out.println("第 " + (i+1) + " 条成功");
continue;
}
System.out.println("第 " + (i+1) + " 条失败");
}
}
}
UserService
package com.example.demo.service;
import com.example.demo.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService{
@Autowired
private UserDao userDao;
public void updateMany(List<Object[]> args) {
userDao.updateMany(args);
}
}
MyTest
package com.example.demo;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class MyTest {
@Test
public void testJdbc() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService service = context.getBean("userService", UserService.class);
List<Object[]> args = new ArrayList<>();
//参数要按顺序赋值
Object[] obj1 = {"小华2", 2};
Object[] obj2 = {"小刚2", 3};
args.add(obj1);
args.add(obj2);
service.updateMany(args);
}
}
执行结果
删除数据
单行删除
UserDao
package com.example.demo.dao;
import com.example.demo.entity.User;
public interface UserDao {
void deleteById(Integer id);
}
UserDaoImpl
package com.example.demo.dao;
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
@Component
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void deleteById(Integer id) {
String sql = "delete from user where id = ?";
int i = jdbcTemplate.update(sql, id);
System.out.println("删除了 " + i + " 条数据");
}
}
UserService
package com.example.demo.service;
import com.example.demo.dao.UserDao;
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService{
@Autowired
private UserDao userDao;
public void deleteById(Integer id) {
userDao.deleteById(id);
}
}
MyTest
package com.example.demo;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MyTest {
@Test
public void testJdbc() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService service = context.getBean("userService", UserService.class);
service.deleteById(1);
}
}
执行结果
批量删除
UserDao
package com.example.demo.dao;
import java.util.List;
public interface UserDao {
void deleteMany(List<Object[]> args);
}
UserDaoImpl
package com.example.demo.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void deleteMany(List<Object[]> args) {
String sql = "delete from user where id = ?";
int[] ints = jdbcTemplate.batchUpdate(sql, args);
for (int i = 0; i < ints.length; i++) {
if (ints[i] == 1) {
System.out.println("第 " + (i+1) + " 条成功");
continue;
}
System.out.println("第 " + (i+1) + " 条失败");
}
}
}
UserService
package com.example.demo.service;
import com.example.demo.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService{
@Autowired
private UserDao userDao;
public void deleteMany(List<Object[]> args) {
userDao.deleteMany(args);
}
}
MyTest
package com.example.demo;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class MyTest {
@Test
public void testJdbc() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService service = context.getBean("userService", UserService.class);
List<Object[]> args = new ArrayList<>();
Object[] obj1 = {2};
Object[] obj2 = {3};
args.add(obj1);
args.add(obj2);
service.deleteMany(args);
}
}
执行结果