SpringBoot数据库操作

最新推荐文章于 2022-03-20 19:18:53 发布

舊夢傾城

最新推荐文章于 2022-03-20 19:18:53 发布

阅读量359

点赞数 1

文章标签： SpringBoot 数据库操作

本文链接：https://blog.csdn.net/qq_41967396/article/details/100771989

版权

SpringBoot数据库操作

摘要

SpringBoot JDBC
- SpringBoot集成JDBC
- JdbcTemplate的CRUD
SpringBoot Data JPA
- SpringBoot集成JPA
- JPA的CRUD
SpringBoot Data Mybatis
- SpringBoot集成MyBatis
- MyBatis的CRUD

SpringBoot JDBC

SpringBoot集成JDBC

在pom.xml中引入依赖

	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-jdbc</artifactId>
	</dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<scope>runtime</scope>
   	</dependency>

在application.yml或application.properties中配置数据库连接

spring:
	datasource:
    	url: jdbc:mysql://localhost:3306/users?serverTimezone=UTC
		username: root
		password: root
		driver-class-name: com.mysql.jdbc.Driver

JdbcTemplate的CRUD

public class UserServiceImpl implements UserService {
	@Resource
	private JdbcTemplate jdbcTemplate;
    
	//JDBC写入数据
	@Override
	public int saveUser(){
		//初始化属性参数
		String username = "张三";
		String password = "123456";
		//SQL语句
		String sql = "INSERT INTO tb_user (username,password) VALUES (?,?)";
		//执行写入操作
		int row = jdbcTemplate.update(sql, "李四", "123");
		//返回结果
		return row;
	}

	//JDBC查询数据
	@Override
	public List<User> queryAllUser(){
		//SQL语句
		String sql = "SELECT * FROM tb_user WHERE is_delete=0";
		//执行查询操作
		List<User> list = jdbcTemplate.query(sql, new RowMapper<User>() {
			@Override
			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getInt("ID"));
				user.setUsername(rs.getString("USERNAME"));
				user.setPassword(rs.getString("PASSWORD"));
				return user;
			}
		});
		//返回结果
		retrun list;
	}

	//JDBC更新数据
	@Override
	public int updateUser(User user){
		//SQL语句
		String sql = "UPDATE tb_user set username = ?,password = ? where id = ?";
		//执行更新操作
		int row = jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, user.getUsername());
				ps.setString(2, user.getPassword());
				ps.setInt(3, user.getId());
			}
		});
		//返回结果
		return row;
	}

	//JDBC删除数据
	@Override
	public int removeUser(Integer id){
		//SQL语句
		String sql = "UPDATE tb_user SET is_delete=1 WHERE id=?";
		//执行删除操作
		int row = jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setInt(1, id);
			}
		});
		//返回结果
		return row;
		}
}

SpringBoot Data JPA

SpringBoot集成JPA

在pom.xml中引入依赖

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

在application.yml或application.properties中配置数据库连接以及JPA

spring:
	datasource:
		url: jdbc:mysql://localhost:3306/users?characterEncoding=utf-8&useSSL=false
		username: root
		password: root
		driver-class-name: com.mysql.jdbc.Driver
	jpa:
		show-sql: true
		hibernate:
			ddl-auto: update

JPA的CRUD

创建实体类

@Table(name = "tb_user")
@Entity
@Data
public class User {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	@Column
	private String username;
	@Column
	private String password;
}

创建DAO接口

/**	
*	JpaRepository接口中封装了创建（save）、更新（save）、删除（delete）、查询（findAll、	
*	getOne）等基本操作的函数
*	若需要复杂的SQL可以自己书写
*/
@Repository
public interface UserRepository extends JpaRepository<User, Integer> {}

单元测试

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = Application.class)
public class JpaTest {
	@Autowired
	private UserRepository userRepository;
            
	/**
	*	增加用户操作
	*	第一次增加用户时，会创建表
	*	第二次增加用户时，会更新表
	*/
	@Test
	public void addUser() throws Exception {
		User user1 = new User();
		user1.setUsername("张三");
		user1.setPassword("123456");
		userRepository.save(user1);

		User user2 = new User();
		user2.setUsername("李四");
		user2.setPassword("123");
		userRepository.save(user2);
	}

	/**
	*	更新（修改）用户操作
	*/
	@Test
	public void updateUser() throws Exception {
		User user = new User();
		user.setId(1);
		user.setUsername("张三");
		user.setPassword("123");
		userRepository.save(user);
	}

	/**
	*	删除用户操作（物理删除）
	*/
	@Test
	public void deleteUser() throws Exception {
		User user = new User();
		user.setId(1);
		userRepository.delete(user);
	}
            
	/**
	*	查询用户操作
	*/
	@Test
	public void queryUser() throws Exception {
		User user1 = userRepository.findByPassword("123");
		System.out.println(user1.getUsername());
        
		User user2 = userRepository.findByUsernameAndPassword("李四", "123");
		System.out.println(user2.getUsername());
        
		User user3 = userRepository.findUser("李四");
		System.out.println(user3.getUsername());
	}
            
	/**
	*	查询所有用户操作
	*/
	@Test
	public void queryAllUser() throws Exception {
		List<User> list = userRepository.findAll();
		for(User user : list) {
			System.out.println(user.getUsername());
		}
	}
}

SpringBoot Data Mybatis

SpringBoot集成MyBatis

在pom.xml中引入依赖

<dependency>  
	<groupId>org.mybatis.spring.boot</groupId>  
	<artifactId>mybatis-spring-boot-starter</artifactId>  
	<version>1.3.0</version>  
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

在application.yml或application.properties中配置数据库连接以及Mybatis

spring:
	datasource:
		url: jdbc:mysql://localhost:3306/users?serverTimezone=UTC
		username: root
		password: root
		driver-class-name: com.mysql.jdbc.Driver
		#公共配置与profiles选择无关 mapperLocations指的路径是src/main/resources
mybatis:
	typeAliasesPackage: com.byx.model
	mapperLocations: classpath:mapper/*.xml

MyBatis的CRUD

注释方式（适用于简单、较短的SQL语句）

	//插入数据
	@Insert("insert into t_student values(null,#{name},#{age})")
	public int insertStudent(Student student);

	//更新数据
	@Update("update t_student set name=#{name},age=#{age} where id=#{id}")
	public int updateStudent(Student student);
		
	//删除数据（物理）
	@Delete("delete from t_student where id=#{id}")
	public int deleteStudent(int id);

	//查询数据
	@Select("select * from t_student where id=#{id}")
	public Student getStudentById(Integer id);

	//查询多条数据
	@Select("select * from t_student")
	@Results({
		@Result(id=true,column="id",property="id"),
		@Result(column="name",property="name"),
		@Result(column="age",property="age")
	})
	public List<Student> findStudents();

xml文件方式（适用于复杂、较长的SQL语句）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.UsersMapper">
	<resultMap id="BaseResultMap" type="com.model.Users">
    	<id column="id" jdbcType="INTEGER" property="id" />
    	<result column="username" jdbcType="VARCHAR" property="username" />
    	<result column="password" jdbcType="VARCHAR" property="password" />
 	 </resultMap>
  	<sql id="Base_Column_List">id, username, password</sql>

	<select id="selectByUserNameAndPassword" parameterType="com.model.Users" resultMap="BaseResultMap">
		select <include refid="Base_Column_List"/> from tb_users where username = #{username} and password = #{password}
	</select>
	
	<select id="selectUser" resultMap="BaseResultMap">
		select <include refid="Base_Column_List"/> from tb_users
	</select>
	
	<insert id="insertUser" parameterType="com.model.Users">
		insert into tb_users(username, password) values (#{username}, #{password})
	</insert>
</mapper>