数据库连接池

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
public class SelectTest {
    public static void main(String[] args){    	
    	  
        Connection conn = null;
        try {
        //四种办法获取连接
        	//从c3p0获取
            //conn = C3p0Factory1.getConnection();
            //conn = C3p0Factory2.getConnection();
            
            //从Druid获取
            //conn = DruidFactory1.getConnection();
            conn = DruidFactory2.getConnection();
            
            //构建数据库执行者
            Statement stmt = conn.createStatement(); 
            System.out.println("创建Statement成功！");      
            
            //执行SQL语句并返回结果到ResultSet
            ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
                        
            //开始遍历ResultSet数据
            while(rs.next())
            {
            	System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
            }
            
            rs.close();
            stmt.close();
            
        } catch (Exception e){
            e.printStackTrace();
        } finally {
        	try	{
        		if(null != conn) {
            		conn.close();
            	}
        	} catch (SQLException e){
                e.printStackTrace();
        	}        	
        }
    }
}



public class C3p0Factory1 {
	
	private static ComboPooledDataSource dataSource = null;

	public static void init() throws Exception {
		
		dataSource = new ComboPooledDataSource();
		dataSource.setDriverClass( "com.mysql.jdbc.Driver" );            
		dataSource.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
		dataSource.setUser("root");                                  
		dataSource.setPassword("123456");                                  
			
		// the settings below are optional -- c3p0 can work with defaults
		dataSource.setMinPoolSize(5);                                     
		dataSource.setAcquireIncrement(5);
		dataSource.setMaxPoolSize(20);
			
		// The DataSource dataSource is now a fully configured and usable pooled DataSource

	}
	
	public static Connection getConnection() throws Exception {
		if(null == dataSource)
		{
			init();
		}
        return dataSource.getConnection();
    }

}

public class C3p0Factory2 {
	
	private static ComboPooledDataSource dataSource = null;

	public static void init() throws Exception {
		
		dataSource = new ComboPooledDataSource();
		//dataSource 自动加载c3p0-config.xml文件	
		
		// The DataSource dataSource is now a fully configured and usable pooled DataSource

	}
	
	public static Connection getConnection() throws Exception {
		if(null == dataSource)
		{
			init();
		}
        return dataSource.getConnection();
    }
}
c3p0-config.xml文件	
<?xml version="1.0" encoding="UTF-8"?>
-<c3p0-config>
-<default-config>
<!-- 默认配置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>

<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>

<property name="user">root</property>

<property name="password">123456</property>

<property name="initialPoolSize">5</property>

<property name="maxPoolSize">20</property>

</default-config>

</c3p0-config>


public class DruidFactory1 {
	private static DruidDataSource dataSource = null;

	public static void init() throws Exception {
		
		dataSource = new DruidDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 
		dataSource.setUsername("root");
		dataSource.setPassword("123456");
		dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test"); 
		dataSource.setInitialSize(5);
		dataSource.setMinIdle(1); 
		dataSource.setMaxActive(10); 
		// 启用监控统计功能 dataSource.setFilters("stat");// 
	}
	
	public static Connection getConnection() throws Exception {
		if(null == dataSource)
		{
			init();
		}
        return dataSource.getConnection();
    }
}


public class DruidFactory2 {
	private static DruidDataSource dataSource = null;

	public static void init() throws Exception {
		Properties properties = new Properties();
		
		InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");  
		properties.load(in); 		
		dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);		
		
		in.close();
	}
	
	public static Connection getConnection() throws Exception {
		if(null == dataSource)
		{
			init();
		}
        return dataSource.getConnection();
    }
}