你的位置:首页 > Java教程

[Java教程]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_ 40     public long OpenSessionOneTime_ 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.); 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_ 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_ 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_293           , "OpenSessionOneTime_AnnotionMapper", "OpenSessionOneTime_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_302             , record.OpenSessionOneTime_AnnotionMapper, record.OpenSessionOneTime_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>


 

 

 

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)

不过两者可比性不大,毕竟不是同一种东西,功能也不一样。