mybatis研究：select性能對比，mybatis性能對比
![]()
1 package sss.mybatis_1;
2
3 import java.io.InputStream;
4 import java.security.Principal;
5 import java.util.ArrayList;
6 import java.util.List;
7 import java.util.Properties;
8 import java.sql.*;
9
10 import com.mysql.jdbc.ConnectionImpl;
11 import com.mysql.jdbc.JDBC4Connection;
12 import com.mysql.jdbc.JDBC4MySQLConnection;
13 //import org.apache.commons.lang.time.StopWatch;
14 import com.sun.javafx.Logging;
15 import org.apache.commons.dbcp.BasicDataSource;
16 import org.apache.commons.dbcp.DataSourceConnectionFactory;
17 import org.apache.commons.lang3.time.StopWatch;
18 import org.apache.ibatis.io.Resources;
19 import org.apache.ibatis.session.Configuration;
20 import org.apache.ibatis.session.SqlSession;
21 import org.apache.ibatis.session.SqlSessionFactory;
22 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
23
24 import org.apache.ibatis.transaction.TransactionFactory;
25 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
26
27 import javax.sql.DataSource;
28
29 /**
30 * Created by fenggq on 2016/3/2.
31 */
32 public class PerformanceTest {
33
34
35 public class Record {
36
37 public int Times;
38
39 public long OpenSessionEveryTime_XMLMapper;
40 public long OpenSessionOneTime_XMLMapper;
41 public long OpenSessionOneTime_AnnotionMapper;
42 public long OpenSessionEveryTime_AnnotionMapper;
43 public long JDBCOneTime;
44 public long JDBCEveryTime;
45 public long BDCPOneTime;
46
47
48 }
49
50
51 public static void main(String[] args) throws Exception {
52 PerformanceTest performanceTest = new PerformanceTest();
53 performanceTest.Test();
54 }
55
56 private void Test() throws Exception {
57 {
58
59
60 //org.apache.ibatis.logging.LogFactory.useLog4J2Logging();
61 // org.apache.ibatis.logging.LogFactory.useJdkLogging();
62
63
64
65 InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
66
67 SqlSessionFactory sqlSessionFactory = null;
68 SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
69 sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
70
71 is.close();
72 StopWatch sw1 = new StopWatch();
73
74
75 fgq.mybatis_1.UI ui;
76 UI2 ui2;
77
78 List<?> result;
79
80 SqlSession sqlSession = null;
81
82
83 //region 為了公平，SessionFactory預先加載
84 sqlSession = sqlSessionFactory.openSession();
85 ui = sqlSession.selectOne("selectbutton", "buttonAdd");
86 UI2Mapper ui2Mapper = sqlSession.getMapper(UI2Mapper.class);
87 sqlSession.close();
88 //endregion
89
90
91 Integer times = 10;
92 ArrayList<Integer> AllTime = new ArrayList<Integer>();
93 ArrayList<Record> records = new ArrayList<Record>();
94 Record record;
95
96 AllTime.add(1);
97 AllTime.add(2);
98 AllTime.add(10);
99 AllTime.add(100);
100 AllTime.add(200);
101 AllTime.add(1000);
102
103
104 for (int ii = 0; ii < AllTime.size(); ii++) {
105
106 times = AllTime.get(ii);
107 record = new Record();
108 records.add(record);
109 record.Times = times;
110
111
112 ///////////////////////////////////////////////
113
114 sw1.start();
115 for (Integer i = 0; i < times; i++) {
116 sqlSession = sqlSessionFactory.openSession();
117 ui = sqlSession.selectOne("selectbutton", "buttonAdd" + i);
118 sqlSession.close();
119 }
120 record.OpenSessionEveryTime_XMLMapper = sw1.getTime();
121 sw1.stop();
122 sw1.reset();
123
124 //////////////////////////////////////////
125 sw1.start();
126 sqlSession = sqlSessionFactory.openSession();
127 for (Integer i = 0; i < times; i++) {
128
129 ui = sqlSession.selectOne("selectbutton", "buttonAdd" + i);
130
131 }
132 sqlSession.close();
133
134 record.OpenSessionOneTime_XMLMapper = sw1.getTime();
135 sw1.stop();
136 sw1.reset();
137
138 //////////////////////////////////////////
139
140 sw1.start();
141 sqlSession = sqlSessionFactory.openSession();
142 ui2Mapper = sqlSession.getMapper(UI2Mapper.class);
143
144 for (Integer i = 0; i < times; i++) {
145 ui2 = ui2Mapper.SelectOne("buttonAdd" + i);
146 }
147 sqlSession.close();
148 //System.out.println("OpenSessionOneTime_AnnotionMapper:" + sw1.getTime());
149 record.OpenSessionOneTime_AnnotionMapper = sw1.getTime();
150 sw1.stop();
151 sw1.reset();
152 //////////////////////////////////////////
153
154 sw1.start();
155
156 for (Integer i = 0; i < times; i++) {
157 sqlSession = sqlSessionFactory.openSession();
158 ui2Mapper = sqlSession.getMapper(UI2Mapper.class);
159 ui2 = ui2Mapper.SelectOne("buttonAdd" + i);
160 sqlSession.close();
161 }
162
163 //System.out.println("OpenSessionEveryTime_AnnotionMapper:" + sw1.getTime());
164 record.OpenSessionEveryTime_AnnotionMapper = sw1.getTime();
165 sw1.stop();
166 sw1.reset();
167 //////////////////////////////////////////
168
169
170 // 驅動程序名
171 String driver = "com.mysql.jdbc.Driver";
172 String uri = "jdbc:mysql://*******:*********/*********";
173
174
175 Properties p = new Properties();
176
177 p.setProperty("username", "root");
178 p.setProperty("password", "oracle");
179
180 Connection connection;
181
182
183 sw1.reset();
184 sw1.start();
185 connection = DriverManager.getConnection(uri, "root", "oracle");
186 for (Integer i = 0; i < times; i++) {
187
188 PreparedStatement s = connection.prepareStatement("select e.button_id,e.text_value\n" +
189 " from kingkoo_wms.ui_button e\n" +
190 " WHERE e.button_id=?");
191
192 s.setString(1, "buttonAdd" + i);
193 ResultSet rs = s.executeQuery();
194
195
196 while (rs.next()) {
197
198 ui = new fgq.mybatis_1.UI();
199 ui.setButton_id(rs.getString("button_id"));
200 ui.setText_value(rs.getString("text_value"));
201
202 }
203
204 }
205 connection.close();
206
207 record.JDBCOneTime = sw1.getTime();
208 sw1.stop();
209 sw1.reset();
210
211
212 //////////////////////////////////////////////////////////////////
213
214
215 sw1.start();
216
217 for (Integer i = 0; i < times; i++) {
218 connection = DriverManager.getConnection(uri, "root", "oracle");
219
220 PreparedStatement s = connection.prepareStatement("select e.button_id,e.text_value\n" +
221 " from kingkoo_wms.ui_button e\n" +
222 " WHERE e.button_id=?");
223
224 s.setString(1, "buttonAdd" + i);
225 ResultSet rs = s.executeQuery();
226
227
228 while (rs.next()) {
229
230 ui = new fgq.mybatis_1.UI();
231 ui.setButton_id(rs.getString("button_id"));
232 ui.setText_value(rs.getString("text_value"));
233
234 }
235 connection.close();
236 }
237
238
239
240 record.JDBCEveryTime = sw1.getTime();
241 sw1.stop();
242 sw1.reset();
243
244
245 //////////////////////////////////////////////////////////////////
246
247
248 org.apache.commons.dbcp.BasicDataSource dataSource = new BasicDataSource();
249 dataSource.setDriverClassName("com.mysql.jdbc.Driver");
250 dataSource.setUrl("jdbc:mysql://*******:*****/*******");
251 dataSource.setUsername("root");
252 dataSource.setPassword("oracle");
253
254
255 org.apache.commons.dbcp.DataSourceConnectionFactory dataSourceConnectionFactory = null;
256
257
258 dataSourceConnectionFactory = new DataSourceConnectionFactory(dataSource);
259 connection = dataSourceConnectionFactory.createConnection();
260
261 sw1.start();
262 for (Integer i = 0; i < times; i++) {
263
264 PreparedStatement s = connection.prepareStatement("select e.button_id,e.text_value\n" +
265 " from kingkoo_wms.ui_button e\n" +
266 " WHERE e.button_id=?");
267
268 s.setString(1, "buttonAdd" + i);
269 ResultSet rs = s.executeQuery();
270
271
272 while (rs.next()) {
273
274 ui = new fgq.mybatis_1.UI();
275 ui.setButton_id(rs.getString("button_id"));
276 ui.setText_value(rs.getString("text_value"));
277
278 }
279
280 }
281
282 connection.close();
283
284 record.BDCPOneTime = sw1.getTime();
285
286 sw1.stop();
287 sw1.reset();
288
289
290 }
291
292 System.out.println(String.format("%s %s %s %s %s %s %s %s", "Times", "BDCPOneTime", "JDBCEveryTime", "JDBCOneTime", "OpenSessionEveryTime_AnnotionMapper", "OpenSessionEveryTime_XMLMapper"
293 , "OpenSessionOneTime_AnnotionMapper", "OpenSessionOneTime_XMLMapper"
294 ));
295
296 for (int i = 0; i < records.size(); i++) {
297 record = records.get(i);
298
299
300 System.out.println(String.format("%d %d %d %d %d %d %d %d"
301 , record.Times, record.BDCPOneTime, record.JDBCEveryTime, record.JDBCOneTime, record.OpenSessionEveryTime_AnnotionMapper, record.OpenSessionEveryTime_XMLMapper
302 , record.OpenSessionOneTime_AnnotionMapper, record.OpenSessionOneTime_XMLMapper
303 ));
304
305 }
306
307 }
308
309
310 }
311 }
312
View Code
![]()
<mapper namespace="org.mybatis.example.BlogMapper">
<resultMap id="BlogMapper" type="fgq.mybatis_1.UI">
<id column="button_id" property="button_id" ></id>
</resultMap>
<select id="selectbutton" resultType="fgq.mybatis_1.UI" useCache="false">
select e.button_id,e.text_value
from mysql_test.ui_button e
WHERE e.button_id=#{id}
</select>
</mapper>
xml mapper
public interface UI2Mapper {
@Select("select e.button_id,e.text_value from mysql_test.ui_button e WHERE e.button_id=#{id}")
@Options( useCache = false)
UI2 SelectOne(String id);
}
從結果看，mybatis方式的select比原生態的代碼，速度慢兩倍，相比dapper而言差距還是很大的。 (https://github.com/StackExchange/dapper-dot-net)
不過兩者可比性不大，畢竟不是同一種東西，功能也不一樣。
