Two database connection modes and two annotations (annotation version usermapper, file version)

1, Two ways to connect database

Method 1:

springboot is configured by default (DataSource and sqlsessionfactory)

Direct configuration file application.yml

Write inside

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/o2odb?serverTimezone=UTC&characterEncoding=utf-8&useSSL=true
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: root

Mode two:

Similar to spring MVC, configure datasource and sessionfactory, and then write a bean to join the container.

The application.properties are as follows

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url= jdbc:mysql://localhost:3306/o2odb?useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=root

Then create two new config

package com.hy.oa.config;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.beans.PropertyVetoException;

@Configuration
@MapperScan("com.hy.oa.dao")

public class DataSourceConfiguration {

    @Value("${jdbc.driver}")
    private String jdbcDriver;

    @Value("${jdbc.url}")
    private String jdbcUrl;

    @Value("${jdbc.username}")
    private String jdbcUsername;

    @Value("${jdbc.password}")
    private String jdbcPassword;

    @Bean(name = "dataSource")
    public ComboPooledDataSource createDataSource() throws PropertyVetoException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        //dirver
        dataSource.setDriverClass(jdbcDriver);
        //url
        dataSource.setJdbcUrl(jdbcUrl);
        //username
        dataSource.setUser(jdbcUsername);
        //password
        dataSource.setPassword(jdbcPassword);

        //Do not automatically Commit after closing the connection
        dataSource.setAutoCommitOnClose(false);

        //  <! -- when the connection in the connection pool is exhausted, c3p0 gets the number of connections at one time. Defau lt: 3 -->
        dataSource.setAcquireIncrement(5);

        // <! -- defines the number of times a new connection has failed to get from the database. Defau lt: 30 -->

        dataSource.setAcquireRetryAttempts(30);

        //	<! -- interval between two connections, in milliseconds. Defau lt: 1000 -->
        dataSource.setAcquireRetryDelay(1000);

        // <! -- when the connection pool is used up, the client calls getConnection() and waits for a new connection. After the timeout, SQLException will be thrown
        dataSource.setCheckoutTimeout(10000);

        dataSource.setMaxStatements(0);
        //<! -- get three connections during initialization, the value should be between minPoolSize and maxPoolSize. Defau lt: 3 -->
        dataSource.setInitialPoolSize(10);

        dataSource.setMinPoolSize(5);

        //   	<! -- the maximum number of connections remaining in the connection pool. Defau lt: 15 -->
        dataSource.setMaxPoolSize(200);
        //	<! -- the maximum idle time. If it is not used within 60 seconds, the connection will be discarded. If it is 0, it will never be discarded. Defau lt: 0 -->
        dataSource.setMaxIdleTime(60);
        //<!--How long to hang on to excess unused connections after traffic spike -->
        dataSource.setMaxIdleTimeExcessConnections(600);

        return dataSource;
    }


}
package com.hy.oa.config;


import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.io.IOException;

@Configuration
public class SessionFactoryConfiguration {
    @Autowired
    private DataSource dataSource;

    @Bean(name="sqlSessionFactory")
    public SqlSessionFactoryBean createSqlSessionFactoryBean() throws IOException
    {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

        //Load the main configuration file mybatis-config.xml
        bean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        //Mapper scan path
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        String packageSearchPath = "classpath*:/mapper/**.xml";
        bean.setMapperLocations(resolver.getResources(packageSearchPath));
        //Configure package for entity
        bean.setTypeAliasesPackage("com.hy.oa.model");
        //dataSource
        bean.setDataSource(dataSource);

        return bean;
    }

}

Start up.

 

2, Two kinds of dealing with database

@RestController
public class wenjianversioncontroller {

   @Autowired
    private UserDao userDao;

    @RequestMapping("/wenjianfindall")
    public String wenjianfindall(){
        List<User> users = userDao.searchAllUsers();
        for (User user : users) {
            System.out.println(user.getId()+"--"+user.getUsername()+""+user.getPassword());

        }
    userDao.deleteUserById(2);
        return "wenjianfindall";


    }
public interface UserDao {
    //increase
    int addUser(User user);
    //Delete
    //int deletUser(User user);
    int deleteUserById(int id);
    //change
    int updateUser(User user);
    //check

    List<User> searchAllUsers();
   User searchUserById(int id);
}

And then in the mapper in the resource

<?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">

<!-- namespace:Mapper Corresponding DAO -->
<mapper namespace="com.hy.oa.dao.UserDao">

    <!-- 	select:query clause
            id:Corresponding method name
            resultType: Type returned-->
    <select id="searchAllUsers" resultType="com.hy.oa.model.User">
		select * from user
	</select>

    <select id="searchUserById" resultType="com.hy.oa.model.User">
		select * from user where id = #{id}
	</select>

    <!-- userGeneratedKeys:If the execution is successful, the value of the primary key is returned, parameterType:Category of ginseng
    <insert id="addUser1" useGeneratedKeys="true">
		insert into user(username,password) values(#{username},#{password})
	</insert>-->

    <insert id="addUser" useGeneratedKeys="true" keyProperty="id" keyColumn="id" parameterType="com.hy.oa.model.User">
		insert into user values(#{id},#{username},#{password})
	</insert>

    <update id="updateUser" parameterType="com.hy.oa.model.User">
		update user set username=#{username},password=#{password} where id=#{id}
	</update>

    <delete id="deleteUserById">
		delete from user where id = #{id}
	</delete>

    <!--<delete id="deleteUserById2">
		delete from user where id = #{Lain}
	</delete>-->


</mapper>

----------------------------------

Mode two:

dao is annotated directly, and then the sql statement is written in the annotation.

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
@Mapper
public interface UserMapper {

    @Select("select * from user")
    List<User> findAll();
}
public class TestController {

	@Autowired
	private UserMapper userMapper;

	@RequestMapping("/findAll")
	public String findall(){
		List<User> userList = userMapper.findAll();
		for(User user : userList){
			System.out.println(user.getId()+""+user.getUsername()+""+user.getPassword());

		}
		return "hello";
	}

}

 

154 original articles published, praised 8, visited 80000+
Private letter follow

Tags: JDBC Mybatis Spring MySQL

Posted on Sat, 08 Feb 2020 06:42:52 -0800 by haku87