SpringBoot+C3P0数据源属性测试

最新推荐文章于 2022-12-02 19:45:25 发布

这是---

最新推荐文章于 2022-12-02 19:45:25 发布

阅读量220

点赞数

分类专栏： spring 文章标签：数据库

本文链接：https://blog.csdn.net/qq_44694485/article/details/108295445

版权

spring 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

SpingBoot+maven+Oracle

导入依赖pom.xml

<properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.3.0.RELEASE</spring-boot.version>
    </properties>
	
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
		<!--druid-->
		<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc6 -->
		<dependency>
		    <groupId>com.oracle.database.jdbc</groupId>
		    <artifactId>ojdbc6</artifactId>
		    <version>11.2.0.4</version>
		</dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.0</version>
        </dependency>
        <dependency>
        	 <groupId>org.projectlombok</groupId>
       		 <artifactId>lombok</artifactId>
        </dependency>
        
        
        <dependency>
     		 <groupId>c3p0</groupId>
     		 <artifactId>c3p0</artifactId>
     		 <version>0.9.1.2</version>
		</dependency>
        <dependency>
        	<groupId>org.springframework.boot</groupId>
        	<artifactId>spring-boot-configuration-processor</artifactId>
        	<optional>true</optional>
        </dependency>
        <!--热部署-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<optional>true</optional>
		</dependency>
        
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.3.0.RELEASE</version>
                <configuration>
                    <mainClass>com.example.demo.MysqlC3p0Application</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

application.properties配置

spring.application.name=mysql-c3p0

server.port=8888

mybatis.mapperLocations=classpath:mapper/*Mapper.xml

#spring.datasource.username: ts_sj
#spring.datasource.password: ts
#spring.datasource.url: jdbc:oracle:thin:@172.16.100.66:1521:db11
#spring.datasource.driver-class-name: oracle.jdbc.driver.OracleDriver

spring.datasource.type: com.mchange.v2.c3p0.ComboPooledDataSource  
#数据库jdbc:oracle,路径端口thin:@ip:port[1521],
#服务名:sid[db11]  
c3p0.jdbcUrl= jdbc:oracle:thin:@172.16.100.66:1521:db11
c3p0.user= ts_sj
c3p0.password= ts
c3p0.driverClass= oracle.jdbc.driver.OracleDriver

#连接池中可以保留的最小连接数，也可以为0
c3p0.minPoolSize=5
#
#最大连接数
c3p0.maxPoolSize=120
#
#最大空闲时间，多少秒内未使用则连接被丢弃
c3p0.maxIdleTime=60
#
#当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3
c3p0.acquireIncrement=20
#
#最大缓存数
c3p0.maxStatements=0

#初始化时获取连接数取值应在minPoolSize与maxPoolSize之间
c3p0.initialPoolSize=100
#

@Configuration
public class DataSourceConfig {
	@Bean
	public SysUserService sysUserService(@Autowired SysUserServiceImpl bean) {
		return bean;
	}

	
	@Bean(name = "dataSource")
	@Qualifier(value = "dataSource")
	@Primary
	@ConfigurationProperties(prefix = "c3p0")
	public DataSource dataSource(){
		System.out.println("spring.datasource.ds1");
	    return DataSourceBuilder.create().type(com.mchange.v2.c3p0.ComboPooledDataSource.class).build();
	} 
}

Mapper接口

Orecal测试语句直接复制过来防止出错

@Mapper
public interface UserMapper {
	//使用注解一定要把字段写完全
	//用xml就直接使用映射集，*查询
	@Select("SELECT \"sys_user\".\"id\", \"sys_user\".\"username\", \"sys_user\".\"password\" FROM \"sys_user\" ")
	List<SysUser> findAllSysUser();
	
	//@Select(" SELECT \"sys_user\".\"id\", \"sys_user\".\"username\", \"sys_user\".\"password\" FROM \"sys_user\" ")
	@Select(" SELECT \"sys_user\".\"id\", \"sys_user\".\"username\", \"sys_user\".\"password\" FROM \"sys_user\"\r\n" + 
			"WHERE 1=#{id} ")
	SysUser findById(@Param("id")Long id);
}

连接对象线程生成

public class ThreadConnection extends Thread{
	
	private DataSource dataSource;
	
	private String name;
	//设置conn数组
    private ArrayList<Integer> connArray= new ArrayList<>();
	
    public ThreadConnection(DataSource dataSource,String name) {
    	this.dataSource=dataSource;
    	this.name = name;
    }
	//创建线程conn
	public void run() {
		while(true) {
			try {
				  
				  Connection conn = dataSource.getConnection("ts_sj", "ts");
				  
				  Statement statement= conn.createStatement();
	              //缓存statement单个
				  ResultSet rs= statement.executeQuery("SELECT \"sys_user\".\"id\", \"sys_user\".\"username\", \"sys_user\".\"password\" FROM \"sys_user\" ");
	              if(rs.next()==true){
	                 connArray.add(1);
	                 System.out.println("name::"+name+"::size::"+connArray.size());
	              }
	              try {
					Thread.sleep(61000);
				} catch (InterruptedException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	            ComboPooledDataSource pool = (ComboPooledDataSource) dataSource;
	  			PooledDataSource pds = (PooledDataSource) pool; 
	  			 if(null != pds){  
	  		            try {  
	  		                System.out.println("------------ThreadRUN里面c3p0连接池链接状态--------------");  
	  		                System.out.println("c3p0连接池中 【 总共 】 连接数量："+pds.getNumConnectionsDefaultUser());  
	  		                System.out.println("c3p0连接池中 【  忙  】 连接数量："+pds.getNumBusyConnectionsDefaultUser());  
	  		                System.out.println("c3p0连接池中 【 空闲 】 连接数量："+pds.getNumIdleConnectionsDefaultUser());  
	  		                System.out.println("c3p0连接池中 【未关闭】 连接数量："+pds.getNumUnclosedOrphanedConnectionsAllUsers());  
	  		            } catch (SQLException e) {  
	  		                System.out.println("c3p0连接池异常！");  
	  		            }  
	  		        }  
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

@RestController
@Slf4j
public class C3p0Controller {
	@Autowired
	private SysUserService sysUserService;
	
	@Autowired
	private DataSource dataSource;
	
	@RequestMapping("data")
	public List<?> getList() {
		new ThreadConnection(dataSource,"tc1").start();
		new ThreadConnection(dataSource,"tc2").start();
		new ThreadConnection(dataSource,"tc3").start();
		return sysUserService.findAllSysUser();
	}
	
	@RequestMapping("user/{id}")
	public SysUser getSysUser(@PathVariable("id") Long id) {
		log.info("id::"+id);
		
	      //设置conn数组
	      ArrayList<Integer> connArray= new ArrayList<>();
	      //创建并发连接数
	      for (int i=0; i<123;i++){
	          try {
	              Connection conn = dataSource.getConnection("ts_sj", "ts");
	              Statement statement= conn.createStatement();
	              ResultSet rs= statement.executeQuery("SELECT \"sys_user\".\"id\", \"sys_user\".\"username\", \"sys_user\".\"password\" FROM \"sys_user\" ");
	              if(rs.next()==true){
	                 connArray.add(i);
	                 System.out.println("id：："+connArray.size());
	              }
	          } catch (SQLException e) {
	              e.printStackTrace();
	          }
	      }
	      System.out.println(connArray.size());
		return sysUserService.findById(id);
	}
	@RequestMapping("thread/{id}")
	public SysUser getThread(@PathVariable("id") Long id) {
		new ThreadConnection(dataSource,"tc1").start();
		new ThreadConnection(dataSource,"tc2").start();
		new ThreadConnection(dataSource,"tc3").start();
		return sysUserService.findById(id);
	}
	
	@RequestMapping("time")
	public List<?> getTime(){
		new ThreadConnection(dataSource,"tc1").start();
		try {
			Connection conn = dataSource.getConnection("ts_sj", "ts");
			Statement statement= conn.createStatement();
            ResultSet rs= statement.executeQuery("SELECT \"sys_user\".\"id\", \"sys_user\".\"username\", \"sys_user\".\"password\" FROM \"sys_user\" ");
            if(rs.next()==true){
                
                System.out.println("id：："+rs.getString(1));
            }
			
			ComboPooledDataSource pool = (ComboPooledDataSource) dataSource;
			PooledDataSource pds = (PooledDataSource) pool; 
			 if(null != pds){  
		            try {  
		                System.out.println("------------c3p0连接池链接状态--------------");  
		                System.out.println("c3p0连接池中 【 总共 】 连接数量："+pds.getNumConnectionsDefaultUser());  
		                System.out.println("c3p0连接池中 【  忙  】 连接数量："+pds.getNumBusyConnectionsDefaultUser());  
		                System.out.println("c3p0连接池中 【 空闲 】 连接数量："+pds.getNumIdleConnectionsDefaultUser());  
		                System.out.println("c3p0连接池中 【未关闭】 连接数量："+pds.getNumUnclosedOrphanedConnectionsAllUsers());  
		            } catch (SQLException e) {  
		                System.out.println("c3p0连接池异常！");  
		            }  
		        }  
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sysUserService.findAllSysUser();
	}
}

这是---

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot+C3P0数据源属性测试

SpingBoot+maven+Oracle导入依赖pom.xml<properties> <java.version>1.8</java.version> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</proj
复制链接

扫一扫