新建一个springData项目
需要的依赖:
配置数据库
新建application.yml并输入spring.datasource.username:
打开数据源连接数据库,
填入用户名与密码选择有cj的mysql,这样可以不用配置时区
创建一个数据库
此时查看架构会发现:
可以在测试类中测试是否连接成功
@SpringBootTest
class Demo03ApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
//查看默认的数据源
System.out.println(dataSource.getClass());
//获得数据库连接
Connection connection = dataSource.getConnection();
System.out.println(connection);
//关闭
connection.close();
}
}
查询
@RestController
public class JDBCController {
@Autowired
JdbcTemplate jdbcTemplate;
//查询数据库的所有信息并显示在网页上
@GetMapping("/userList")
public List<Map<String,Object>> userList(){
String sql="select * from mybatis.user";
List<Map<String,Object>> list_maps=jdbcTemplate.queryForList(sql);
return list_maps;
}
}
注意JdbcController一定要在Demo03同级目录下
访问:localhost:8081/userList
根据id修改用户
@GetMapping("/updateUser/{id}")
public String updateUser(@PathVariable("id") int id){
String sql ="update mybatis.user set uname=? where id="+id;
Object[] objects=new Object[1];
objects[0]="wood";
jdbcTemplate.update(sql,objects);
return "update-OK";
}
访问:http://localhost:8081/updateUser/1
根据id删除用户
@GetMapping("/delUser/{id}")
public String deleteUser(@PathVariable("id") int id){
String sql="delete from mybatis.user where id=?";
jdbcTemplate.update(sql,id);
return "delete-OK";
}
访问:http://localhost:8081/delUser/1
整合druid数据源
在application.yml中配置文件,注意空格必须对齐
type: com.alibaba.druid.pool.DruidDataSource
# 配置数据源
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
#配置监控统计拦截的filters,stat:监控统计、log4j:日志记录、wall:防御sql注入
#如果允许报错,java.lang.ClassNotFoundException: org.apache.Log4j.Properity
#则导入log4j 依赖就行
#SpringBoot默认是不注入这些的,需要自己绑定 #druid数据源专有配置
在Test中将druid数据源与数据库进行连接
@SpringBootTest
class Demo03ApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
//查看默认的数据源
System.out.println(dataSource.getClass());
//获得数据库连接
Connection connection = dataSource.getConnection();
System.out.println(connection);
//关闭
connection.close();
}
}
创建Config文件夹并创建类DruidConfig
@Configuration
public class DruidConfig {
//与yml进行绑定
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource(){
return new DruidDataSource();
}
//后台监控功能
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean<StatViewServlet> bean=
new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
//后台登录账号密码控制
HashMap<String,String> initParameters=new HashMap<>();
//增加配置
initParameters.put("loginUsername","admin");//参数固定
initParameters.put("loginPassword","123456");
//允许谁可以访问
initParameters.put("allow","");//如果为空代表所有人都可以访问
//禁止谁能访问
// initParameters.put("sheepbotany","168.18.24");
bean.setInitParameters(initParameters);//设置初始化参数
return bean;
}
//filter
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
//对请求进行过滤
Map<String,String> initParameters=new HashMap<>();
//不进行统计
initParameters.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParameters);
return bean;
}
}
此时登录localhost:8081/druid
输入在前面设置的账号与密码登录Druid
此时进行数据库操作
http://localhost:8081/delUser/2
在druid的页面可以检测到:
整合mybatis框架以及数据库的增删改查
新建spring项目
选取依赖
添加依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
application.properties配置如下
server.port=8081
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis?severTimezone=UTF&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
mysql的连接参考上面的博客,在Application的Test中测试连接
新建User类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
private int id;
private String uname;
private String pwd;
}
使用接口
@Mapper//注解表示这是一个mybatis的mapper类
@Repository//Dao层标识,将数据教给spring管理
public interface UserMapper {
List<User> queryUserList();
User queryUserById(int id);
int addUser(User user);
int updateUser(User user);
int deleteUser(int id);
}
类与接口与mapper的xml文件位置的位置如下
在application.properties中绑定xml文件
#整合mybatis
mybatis.type-aliases-package=com.example.demo04.pojo
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
需要在UserMapper.xml中 进行配置增删改查操作
<?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.example.demo04.mapper.UserMapper">
<select id="queryUserList" resultType="User">
select * from mybatis.user
</select>
<select id="queryUserById" resultType="User">
select * from mybatis.user where id=#{id}
</select>
<insert id="addUser" parameterMap="User">
insert into mybatis.user(id,uname,pwd)values(#{id},#{name},#{pwd})
</insert>
<update id="updateUser" parameterType="User">
update mybatis.user set uname=#{name},pwd=#{pwd} where id=#{id}
</update>
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id=#{id}
</delete>
</mapper>
配置问题可以查看mybatis官网:
https://mybatis.net.cn/getting-started.html
xml与Userdao层配置一一对应
Controller类:
@RestController
public class UserController {
@Autowired
private UMapper uMapper;
@GetMapping("/queryUserList")
public List<User> queryUserList(){
List<User> userList= uMapper.queryUserList();
for(User user:userList){
System.out.println(user);
}
return userList;
}
}
运行输出类的内容:
根据id查找用户与根据id来删除用户
@GetMapping("/queryUserById/{id}")
public User queryUserById(@PathVariable("id") int id){
User user = uMapper.queryUserById(id);
System.out.println(user);
return user;
}
@GetMapping("/deleteUser/{id}")
public List<User> deleteUser(@PathVariable("id") int id){
uMapper.deleteUser(id);
List<User> userList=uMapper.queryUserList();
for (User user:userList) {
System.out.println(user);
}
return userList;
}
运行结果: