spring mysql 配置_SpringBoot连接数据库相关配置

1.创建springboot项目 选中web jdbc mysql mybatis启动器

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.3

2.引入数据源

com.alibaba

druid

1.1.12

3.创建application.yml配置数据源

spring:

datasource:

url: jdbc:mysql://127.0.0.1:3306/db1

username: root

password: 123456

driver-class-name: com.mysql.cj.jdbc.Driver

type: com.alibaba.druid.pool.DruidDataSource

# 数据源其他配置

initialSize : 5

minIdle: 5

maxActive: 20

maxWait: 60000

timeBetweenEvictionRunsMillis: 60000

minEvictableIdleTimeMillis: 300000

validaionQuery: SELECT 1 FROM DUAL

testWhileIdle: true

testOnBorrow: false

testOnReturn: false

poolPreparedStatements: true

# 配置监控统计拦截的filters 去掉后监控界面sql无法统计，‘wall’用于防火墙

filters: stat,wall,log4j

maxPoolPreparedStatementPerConnectionSize: 20

useGlobalDataSourceStat: true

connectionProperties: druid.stat.mergeSql=true,druid.stat.slowSqlMills=500

4.创建DruidConfig配置druid数据源

@Configuration

public class DruidConfig {

@Bean

@ConfigurationProperties(prefix = "spring.datasource")

public DataSource druid(){

return new DruidDataSource();

}

// 配置servlet

@Bean

public ServletRegistrationBean myservlet(){

ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");

Map initParams = new HashMap();

initParams.put("loginUsername","admin");

initParams.put("loginPassword","123456");

initParams.put("allow","");// 如果不写就是默认允许所有

initParams.put("deny","127.0.0.1");

//是否能够重置数据.

bean.addInitParameter("resetEnable","false");

bean.setInitParameters(initParams);

return bean;

}

// 配置filter

@Bean

public FilterRegistrationBean myfilter(){

FilterRegistrationBean bean = new FilterRegistrationBean(new WebStatFilter());

bean.setFilter(new WebStatFilter());

Map initParams = new HashMap<>();

initParams.put("exclusions","*.js,*.css,/druid/*");

bean.setInitParameters(initParams);

bean.addUrlPatterns("/*");

return bean;

}

}

然后可以启动服务器在浏览器地址栏数据localhost:端口号:/druid进行测试

5.创建mapper文件夹 创建mapper文件

public interface UserMapper {

@Select("select * from user where username = #{username}")

public User findUserByUsername(String username);

@Select("select password from user where username = #{username}")

public String findPwdByUsername(String username);

@Select("select password from user where userId = #{userId}")

public String findPwdByUserId(Integer userId);

@Insert("insert into user(username,password,gender,address,birthday,user_avatar,user_desc,create_user,update_user) values(#{username}," +

"#{password},#{gender},#{address},#{birthday},#{user_avatar},#{user_desc},#{create_user},#{update_user})")

public int addUser(String username,String password,Integer gender,String address ,String birthday,String user_avatar,

String user_desc,String create_user,String update_user);

@Update("update user set username=#{username} , birthday = #{birthday} , gender = #{gender} , address = #{address}, user_desc = #{user_desc} where userId = #{userId}")

public int updateuserinfo(Integer userId ,String username,String birthday,Integer gender ,String address , String user_desc);

@Update("update user set password = #{newpassword} where userId = #{userId}")

public int updateuserpwd(Integer userId , String newpassword);

/*

以下是测试manager表的sql语句

*/

@Select("select * from manager")

public List findAllManager();

// 启用

@Update("update manager set status =0 where userId = #{userId}")

public int updateUserCanStatus(String userId);

// 停用

@Update("update manager set status =1 where userId = #{userId}")

public int updateUserNotCanStatus(String userId);

// 删除

@Delete("delete from manager where userId = #{userId}")

public int deleteManger(String userId);

}

这里可以再mapper文件上面添加注解进行扫包 也可以在主程序上添加注解进行扫包

@MapperScan(value = "com.springboot.demo.mapper")