创建数据库 springboot-database
创建user表
CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
一,连接jdbcTemplete
pom文件中添加对jdbc和mysql的依赖
<!--spring-boot-starter-jdbc的依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--引入mysql连接类和连接池:-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.29</version>
</dependency>
添加对web和test的依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
编写application.yml配置文件配置数据库
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/springboot-database
username: root
password: root
编写UserDao接口
public interface UserDao {
public int save(User user);
public int delete(Integer id);
public int update(User user);
public User getUser(Integer id);
public List<User> getAll();
}
编写UserDaoImpl实现类
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int save(User user) {
String sql = "insert into USER (id,name,password) values (?,?,?)";
return jdbcTemplate.update(sql,new Object[]{user.getId(),user.getName(),user.getPassword()});
}
@Override
public int delete(Integer id) {
String sql = "delete from USER where id = ?";
return jdbcTemplate.update(sql,new Object[]{id});
}
@Override
public int update(User user) {
String sql = "update USER set name=?,password=?";
return jdbcTemplate.update(sql,new Object[]{user.getName(),user.getPassword()});
}
@Override
public User getUser(Integer id) {
String sql = "select * from user where id=?";
List<User> list = jdbcTemplate.query(sql,new Object[]{id},new BeanPropertyRowMapper(User.class));
if(!CollectionUtils.isEmpty(list)){
return list.get(0);
}else{
return null;
}
}
@Override
public List<User> getAll() {
String sql = "select * from user";
List<User> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper(User.class));
return list;
}
}
编写测试类test
service 和 dao层一样加如下3个注解即可测试
@RunWith(SpringRunner.class)
@SpringBootTest
//由于是Web项目,Junit需要模拟ServletContext,因此我们需要给我们的测试类加上@WebAppConfiguration。
@WebAppConfiguration
public class Test01 {
@Autowired
private UserDao userDao;
@Test
public void insert(){
User user = new User();
user.setId(1);
user.setName("test02");
user.setPassword("234");
userDao.save(user);
}
@Test
public void update(){
User user = new User();
user.setId(1);
user.setName("test03");
user.setPassword("789");
userDao.update(user);
}
@Test
public void getById(){
User user = userDao.getUser(1);
System.out.println(user.getName()+"--"+user.getPassword());
}
@Test
public void getAll(){
List<User> list = userDao.getAll();
for (User user:list) {
System.out.println(user.getName()+"--"+user.getPassword());
}
}
@Test
public void delete(){
userDao.delete(1);
}
}
二、连接spring data jpa
pom文件中添加jpa 和数据库依赖
<!--spring-boot-starter-data-jpa的依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!--引入mysql连接类-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
编写application.yml配置文件配置数据库 和 jpa
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/springboot-database?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
username: root
password: root
jpa:
hibernate:
ddl-auto: update # 第一次简表create 后面用update
show-sql: true
如果通过jpa在数据库中建表,将jpa.hibernate,ddl-auto改为create,建完表之后,要改为update,要不然每次重启工程会删除表并新建。
编写User实体
@Entity
public class User {
@javax.persistence.Id
private Integer id;
private String name;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
通过@Entity 表明是一个映射的实体类, @Id表明id, @GeneratedValue 字段自动生成
创建UserDao接口 并继承 JpaRepository,继承自 JpaRepository 的接口就能完成数据访问,其中包含了几本的单表查询的方法
注意这里的User不是表名 而是对象名 Interger是主键的类型,一般为Integer或者Long
继承了JpaRepository dao层实现类不要再实现接口 否则会要求重写所有方法
public interface UserDao extends JpaRepository<User,Integer> {
}
测试类Test 直接注入userDao接口即可使用jpa的方法
@RunWith(SpringRunner.class)
@SpringBootTest
//由于是Web项目,Junit需要模拟ServletContext,因此我们需要给我们的测试类加上@WebAppConfiguration。
@WebAppConfiguration
public class Test03 {
@Autowired
private UserDao userDao;
@Test
public void save(){
User user = new User();
user.setId(6);
user.setName("jpa1");
userDao.save(user);
}
}
三、连接mybatis
创建数据库
CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`user_name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
pom文件中添加mybatis依赖
<!--spring-boot-starter-mabatis的依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!--引入mysql连接类-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.25</version>
<scope>runtime</scope>
</dependency>
数据库连接配置
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/springboot-database?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
username: root
password: root
指定日志输出位置和mybaits中打印sql语句 只需要指定mapper所在包的日志级别即可
logging.level.org.springframework=WARN
logging.level.com.xuxu.mapper=debug
logging.file=logs/spring-boot-logging.log
创建User实体类 这里特意和数据库中不一致name在数据库中的列为user_name
public class User {
private Integer id;
private String name;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
1、使用注解形式
在启动类上加包扫描注解@MapperScan 或者直接在Mapper类上面添加注解@Mapper
,建议使用上面那种,不然每个mapper加个注解也挺麻烦的
@SpringBootApplication
@MapperScan("com.xuxu.mapper")
public class SpringbootDatabaseApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootDatabaseApplication.class, args);
}
}
public interface UserMapper {
@Insert("insert into USER (id,user_name,password) values (#{id},#{name},#{password})")
public int save(User user);
@Update("update USER set id=#{id},user_name=#{name},password=#{password}")
public int update(User user);
@Delete("delete from USER where id=#{id}")
public int delete(Integer id);
@Select("select * from USER where id=#{id}")
@Results({
@Result(property = "name",column = "user_name")
})
public User getById(Integer id);
@Select("select * from USER")
@Results({
@Result(property = "name",column = "user_name")
})
public List<User> getAll();
}
@Select 是查询类的注解,所有的查询均使用这个
@Result 修饰返回的结果集,关联实体类属性和数据库字段一一对应,如果实体类属性和数据库属性名保持一致,就不需要这个属性来修饰。
@Insert 插入数据库使用,直接传入实体类会自动解析属性到对应的值
@Update 负责修改,也可以直接传入对象
@delete 负责删除
测试
@SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection")
@RunWith(SpringRunner.class)
@SpringBootTest
//由于是Web项目,Junit需要模拟ServletContext,因此我们需要给我们的测试类加上@WebAppConfiguration。
@WebAppConfiguration
public class Test03 {
@Autowired
private UserMapper userMapper;
@Test
public void save(){
User user = new User();
user.setId(1);
user.setName("test01");
userMapper.save(user);
}
@Test
public void getAll(){
userMapper.getAll();
}
}
2、使用mapper.xml配置形式
其他配置和上面注解形式一致,接口中sql移入mapper文件
配置文件application.properties中添加mybaits
mybatis.config-locations=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
config文件
<configuration>
<typeAliases>
<typeAlias alias="Integer" type="java.lang.Integer" />
<typeAlias alias="Long" type="java.lang.Long" />
<typeAlias alias="HashMap" type="java.util.HashMap" />
<typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
<typeAlias alias="ArrayList" type="java.util.ArrayList" />
<typeAlias alias="LinkedList" type="java.util.LinkedList" />
</typeAliases>
</configuration>
mapper.xml文件如下
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xuxu.mapper.UserMapper" >
<resultMap id="BaseResultMap" type="com.xuxu.pojo.User" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="user_name" property="name" jdbcType="VARCHAR" />
<result column="pass_word" property="passWord" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, user_name, passWord
</sql>
<select id="getAll" resultMap="BaseResultMap" >
SELECT
<include refid="Base_Column_List" />
FROM USER
</select>
<select id="getById" parameterType="java.lang.Integer" resultMap="BaseResultMap" >
SELECT
<include refid="Base_Column_List" />
FROM USER
WHERE id = #{id}
</select>
<insert id="save" parameterType="com.xuxu.mapper.UserMapper" >
INSERT INTO
USER
(user_name,passWord)
VALUES
(#{name}, #{password})
</insert>
<update id="update" parameterType="com.xuxu.mapper.UserMapper" >
UPDATE
USER
SET
<if test="name != null">user_name = #{name}</if>
<if test="password != null">passWord = #{password}</if>
WHERE
id = #{id}
</update>
<delete id="delete" parameterType="java.lang.Integer" >
DELETE FROM
USER
WHERE
id =#{id}
</delete>
</mapper>
测试
@SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection")
@RunWith(SpringRunner.class)
@SpringBootTest
//由于是Web项目,Junit需要模拟ServletContext,因此我们需要给我们的测试类加上@WebAppConfiguration。
@WebAppConfiguration
public class Test03 {
@Autowired
private UserMapper userMapper;
@Test
public void save(){
User user = new User();
user.setId(1);
user.setName("test01");
userMapper.save(user);
}
@Test
public void getAll(){
userMapper.getAll();
}
@Test
public void getById(){
User user =userMapper.getById(1);
}
@Test
public void update(){
User user =new User();
user.setId(1);
user.setName("testttttt");
userMapper.update(user);
}
}