小白历尽千辛万苦终于搞定,运行过程中出现了很多错误,查了很多资料才解决,谢谢各路大神!
下面的过程中会提到我走过的坑,跟大家分享一下~
新建一个项目,选择spring initializer
选择依赖包,idea会自动添加相应的jar包
修改项目名,finish
目录结构如下:
其中spring生成的CaozuomysqlApplication这个启动类要和controller、mapper、pojo、service在同一层
1.创建User实体类
package com.example.caozuomysql.pojo;
import java.io.Serializable;
public class User implements Serializable{
private Integer id;
private String name;
private String gender;
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 getGender() {
return gender;
}
public void setGender(String gender) {
this.gender=gender;
}
}
2.写配置文件,连接mysql
这里也有很多用application.yml的,多次尝试后发现application.properties和application.yml是一样的,选其一就好
spring.datasource.url=jdbc:mysql://localhost:3306/db1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
这里我出现过这个报错:
java.sql.SQLException: The server time zone value ‘�й���ʱ��’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the ‘serverTimezone’ configuration property) to use a more specifc time zone value if you want to utilize time zone support.
原因是MySQL版本过高,解决方法:
在url路径的问号后面加useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
3.写mapper层(dao层)
UserMapper
package com.example.caozuomysql.mapper;
import com.example.caozuomysql.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper
@Component
@Repository
public interface UserMapper {
/**
* 根据id查询用户
* @param id
* @return
*/
@Select("select * from user where id=#{id}")
User selectById(int id);
/**
* 查询所有用户
* @return
*/
@Select("select * from user")
List<User> selectAll();
/**
* 添加用户
* @param user
*/
@Insert("insert into user(name,gender) values(#{name},#{gender})")
void addUser(User user);
/**
* 根据id删除用户
* @param id
*/
@Delete("delete from user where id=#{id}")
void deleteUser(int id);
/**
* 根据id修改用户信息
* @param id
* @param name
* @param gender
*/
@Update("update user set name=#{name},gender=#{gender} where id=#{id}")
void updateUser(int id,String name,String gender);
}
UserMapper.xml
这个文件一定要加,一开始没加怎么都运行不出来,有类似这样的报错:
Failed to obtain JDBC Connection;nested exception is java.sql.SQLException: Connections could not…
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exception
<?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="org.sang.mybatis.mapper.UserMapper">
<select id="selectUserById" resultType="org.sang.mybatis,model.User">
select * from user where id=#{id};
</select>
<select id="findAll" resultType="org.sang.mybatis.model.User">
select * from user;
</select>
<insert id="add" parameterType="org.sang.mybatis.model.User">
insert into user(name,gender) values (#{name},#{gender});
</insert>
<update id="updateUserById" parameterType="org.sang.mybatis.model.User">
update user set name=#{name},gender=#{gender} where id=#{id}
</update>
<delete id="deleteUserById">
delete from user where id=#{id}
</delete>
</mapper>
4.Service接口
UserService
package com.example.caozuomysql.service;
import com.example.caozuomysql.pojo.User;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Transactional
public interface UserService {
/**
* 根据id查询用户
* @param id
* @return
*/
User selectById(int id);
/**
* 查询所有用户
* @return
*/
List<User> selectAll();
/**
* 添加用户
* @param user
*/
void addUser(User user);
/**
* 根据id删除用户
* @param id
*/
void deleteUser(int id);
/**
* 根据id修改用户信息
* @param id
* @param name
* @param gender
*/
void updateUser(int id,String name,String gender);
}
UserserviceImpl
package com.example.caozuomysql.service.impl;
import com.example.caozuomysql.mapper.UserMapper;
import com.example.caozuomysql.pojo.User;
import com.example.caozuomysql.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserserviceImpl implements UserService{
@Autowired
private UserMapper userMapper;
@Override
public User selectById(int id){
return userMapper.selectById(id);
}
@Override
public List<User> selectAll(){
return userMapper.selectAll();
}
@Override
public void addUser(User user){
userMapper.addUser(user);
}
@Override
public void deleteUser(int id){
userMapper.deleteUser(id);
}
@Override
public void updateUser(int id,String name,String gender){
userMapper.updateUser(id,name,gender);
}
}
5.Controller层
这里注意,添加删除修改操作最好返回User类型的对象(其他层里不需要用User类型,否则可能会出现报错:Mapper method ‘xxx’ has an unsupported return type),这样通过连接在网页上操作之后能直接显示修改过后的所有用户信息
package com.example.caozuomysql.controller;
import com.example.caozuomysql.pojo.User;
import com.example.caozuomysql.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/selectUserById") //根据id查询用户
public User selectById(int id){
return userService.selectById(id);
}
@RequestMapping("/findAll") //查询所有用户
public List<User> getAll(){
return userService.selectAll();
}
@RequestMapping("/add") //添加用户
public List<User> addUser(User user){
userService.addUser(user);
return userService.selectAll();
}
@RequestMapping("/deleteById") //根据id删除
public List<User> deleteUser(int id){
userService.deleteUser(id);
return userService.selectAll();
}
@RequestMapping("/updateUserById") //修改用户信息
public List<User> updateUser(int id,String name,String gender){
userService.updateUser(id,name,gender);
return userService.selectAll();
}
}
6.接下来测试一下各个功能吧~
启动成功后,打开浏览器,输入:http://localhost:8080/findAll
输入:http://localhost:8080/selectUserById?id=1
输入:http://localhost:8080/add?name=Jack&&gender=Female
这里我的数据库用了id自增,就不用输入id了
输入http://localhost:8080/deleteById?id=3
输入:http://localhost:8080/updateUserById?id=2&&name=Sunny&&gender=Male
ok啦,第一次弄这个项目,可能还有很多问题,如果发现的话欢迎大家一起讨论~~有些地方有什么改进方法也欢迎指正!谢谢!