mybatis研究：select性能对比
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
<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>
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)
不过两者可比性不大，毕竟不是同一种东西，功能也不一样。
