xyz.calvinwilliams:sqlaction
Advanced tools
sqlaction is a Database persistence layer tool based auto-gen JDBC code.
sqlaction core advantage :
For example using MySQL
ddl.sql
CREATE TABLE `sqlaction_demo` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '±',
`name` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT 'Ļؖ',
`address` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'µٖ·',
PRIMARY KEY (`id`),
KEY `sqlaction_demo` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
Add jar "mysql-connector-java-X.Y.Z.jar".
Create dbserver.conf.json in Java package folder.
{
"driver" : "com.mysql.jdbc.Driver" ,
"url" : "jdbc:mysql://127.0.0.1:3306/calvindb?serverTimezone=GMT" ,
"user" : "calvin" ,
"pwd" : "calvin"
}
Create sqlaction.conf.json in Java package folder.
{
"database" : "calvindb" ,
"tables" : [
{
"table" : "sqlaction_demo" ,
"sqlactions" : [
"SELECT * FROM sqlaction_demo" ,
"SELECT * FROM sqlaction_demo WHERE name=?" ,
"INSERT INTO sqlaction_demo" ,
"UPDATE sqlaction_demo SET address=? WHERE name=?" ,
"DELETE FROM sqlaction_demo WHERE name=?"
]
}
] ,
"javaPackage" : "xyz.calvinwilliams.sqlaction"
}
pp.bat
java -Dfile.encoding=UTF-8 -classpath "D:\Work\mysql-connector-java-8.0.15\mysql-connector-java-8.0.15.jar;%USERPROFILE%\.m2\repository\xyz\calvinwilliams\okjson\0.0.9.0\okjson-0.0.9.0.jar;%USERPROFILE%\.m2\repository\xyz\calvinwilliams\sqlaction\0.2.7.0\sqlaction-0.2.7.0.jar" xyz.calvinwilliams.sqlaction.SqlActionGencode
pause
Executing pp.bat
//////////////////////////////////////////////////////////////////////////////
/// sqlaction v0.2.9.0
/// Copyright by calvin<calvinwilliams@163.com,calvinwilliams@gmail.com>
//////////////////////////////////////////////////////////////////////////////
--- dbserverConf ---
dbms[mysql]
driver[com.mysql.jdbc.Driver]
url[jdbc:mysql://127.0.0.1:3306/calvindb?serverTimezone=GMT]
user[calvin]
pwd[calvin]
--- sqlactionConf ---
database[calvindb]
table[sqlaction_demo]
sqlaction[SELECT * FROM sqlaction_demo]
sqlaction[SELECT * FROM sqlaction_demo WHERE name=?]
sqlaction[INSERT INTO sqlaction_demo]
sqlaction[UPDATE sqlaction_demo SET address=? WHERE name=? @@METHOD(updateAddressByName)]
sqlaction[DELETE FROM sqlaction_demo WHERE name=?]
SqlActionTable.getTableInDatabase[sqlaction_demo] ...
...
*** NOTICE : Write SqlactionDemoSAO.java completed!!!
Auto-gen code to
SqlactionDemoSAO.java
// This file generated by sqlaction v0.2.9.0
// WARN : DON'T MODIFY THIS FILE
package xyz.calvinwilliams.sqlaction;
import java.math.*;
import java.util.*;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class SqlactionDemoSAO {
int id ; // ± // PRIMARY KEY
String name ; // Ļؖ
String address ; // µٖ·
int _count_ ; // defining for 'SELECT COUNT(*)'
// SELECT * FROM sqlaction_demo
public static int SELECT_ALL_FROM_sqlaction_demo( Connection conn, List<SqlactionDemoSAU> sqlactionDemoListForSelectOutput ) throws Exception {
Statement stmt = conn.createStatement() ;
ResultSet rs = stmt.executeQuery( "SELECT * FROM sqlaction_demo" ) ;
while( rs.next() ) {
SqlactionDemoSAU sqlactionDemo = new SqlactionDemoSAU() ;
sqlactionDemo.id = rs.getInt( 1 ) ;
sqlactionDemo.name = rs.getString( 2 ) ;
sqlactionDemo.address = rs.getString( 3 ) ;
sqlactionDemoListForSelectOutput.add(sqlactionDemo) ;
}
rs.close();
stmt.close();
return sqlactionDemoListForSelectOutput.size();
}
// SELECT * FROM sqlaction_demo WHERE name=?
public static int SELECT_ALL_FROM_sqlaction_demo_WHERE_name_E_( Connection conn, List<SqlactionDemoSAU> sqlactionDemoListForSelectOutput, String _1_SqlactionDemoSAU_name ) throws Exception {
PreparedStatement prestmt = conn.prepareStatement( "SELECT * FROM sqlaction_demo WHERE name=?" ) ;
prestmt.setString( 1, _1_SqlactionDemoSAU_name );
ResultSet rs = prestmt.executeQuery() ;
while( rs.next() ) {
SqlactionDemoSAU sqlactionDemo = new SqlactionDemoSAU() ;
sqlactionDemo.id = rs.getInt( 1 ) ;
sqlactionDemo.name = rs.getString( 2 ) ;
sqlactionDemo.address = rs.getString( 3 ) ;
sqlactionDemoListForSelectOutput.add(sqlactionDemo) ;
}
rs.close();
prestmt.close();
return sqlactionDemoListForSelectOutput.size();
}
// INSERT INTO sqlaction_demo
public static int INSERT_INTO_sqlaction_demo( Connection conn, SqlactionDemoSAU sqlactionDemo ) throws Exception {
PreparedStatement prestmt ;
Statement stmt ;
ResultSet rs ;
prestmt = conn.prepareStatement( "INSERT INTO sqlaction_demo (name,address) VALUES (?,?)" ) ;
prestmt.setString( 1, sqlactionDemo.name );
prestmt.setString( 2, sqlactionDemo.address );
int count = prestmt.executeUpdate() ;
prestmt.close();
return count;
}
// UPDATE sqlaction_demo SET address=? WHERE name=?
public static int UPDATE_sqlaction_demo_SET_address_E_WHERE_name_E_( Connection conn, String _1_address_ForSetInput, String _1_name_ForWhereInput ) throws Exception {
PreparedStatement prestmt = conn.prepareStatement( "UPDATE sqlaction_demo SET address=? WHERE name=?" ) ;
prestmt.setString( 1, _1_address_ForSetInput );
prestmt.setString( 2, _1_name_ForWhereInput );
int count = prestmt.executeUpdate() ;
prestmt.close();
return count;
}
// DELETE FROM sqlaction_demo WHERE name=?
public static int DELETE_FROM_sqlaction_demo_WHERE_name_E_( Connection conn, String _1_name ) throws Exception {
PreparedStatement prestmt = conn.prepareStatement( "DELETE FROM sqlaction_demo WHERE name=?" ) ;
prestmt.setString( 1, _1_name );
int count = prestmt.executeUpdate() ;
prestmt.close();
return count;
}
}
SqlactionDemoSAU.java
// This file generated by sqlaction v0.2.9.0
package xyz.calvinwilliams.sqlaction;
import java.math.*;
import java.util.*;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class SqlactionDemoSAU extends SqlactionDemoSAO {
}
Demo.java
`Demo.java`
package xyz.calvinwilliams.sqlaction;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.LinkedList; import java.util.List;
public class Demo {
public static void main(String[] args) {
Connection conn = null ;
List<SqlactionDemoSAU> sqlactionDemoList = null ;
SqlactionDemoSAU sqlactionDemo = null ;
int nret = 0 ;
// Connect to database
try {
Class.forName( "com.mysql.jdbc.Driver" );
conn = DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/calvindb?serverTimezone=GMT", "calvin", "calvin" ) ;
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.setAutoCommit(false);
// Delete records with name
nret = SqlactionDemoSAU.DELETE_FROM_sqlaction_demo_WHERE_name_E_( conn, "Calvin" ) ;
if( nret < 0 ) {
System.out.println( "SqlactionDemoSAU.DELETE_FROM_sqlaction_demo_WHERE_name_E_ failed["+nret+"]" );
conn.rollback();
return;
} else {
System.out.println( "SqlactionDemoSAU.DELETE_FROM_sqlaction_demo_WHERE_name_E_ ok , rows["+nret+"] effected" );
}
// Insert record
sqlactionDemo = new SqlactionDemoSAU() ;
sqlactionDemo.name = "Calvin" ;
sqlactionDemo.address = "My address" ;
nret = SqlactionDemoSAU.INSERT_INTO_sqlaction_demo( conn, sqlactionDemo ) ;
if( nret < 0 ) {
System.out.println( "SqlactionDemoSAU.INSERT_INTO_sqlaction_demo failed["+nret+"]" );
conn.rollback();
return;
} else {
System.out.println( "SqlactionDemoSAU.INSERT_INTO_sqlaction_demo ok" );
}
// Update record with name
nret = SqlactionDemoSAU.UPDATE_sqlaction_demo_SET_address_E_WHERE_name_E_( conn, "My address 2", "Calvin" ) ;
if( nret < 0 ) {
System.out.println( "SqlactionDemoSAU.UPDATE_sqlaction_demo_SET_address_E_WHERE_name_E_ failed["+nret+"]" );
conn.rollback();
return;
} else {
System.out.println( "SqlactionDemoSAU.UPDATE_sqlaction_demo_SET_address_E_WHERE_name_E_ ok , rows["+nret+"] effected" );
}
// Query records
sqlactionDemoList = new LinkedList<SqlactionDemoSAU>() ;
nret = SqlactionDemoSAU.SELECT_ALL_FROM_sqlaction_demo( conn, sqlactionDemoList ) ;
if( nret < 0 ) {
System.out.println( "SqlactionDemoSAO.SELECT_ALL_FROM_sqlaction_demo failed["+nret+"]" );
conn.rollback();
return;
} else {
System.out.println( "SqlactionDemoSAO.SELECT_ALL_FROM_sqlaction_demo ok" );
for( SqlactionDemoSAU r : sqlactionDemoList ) {
System.out.println( " id["+r.id+"] name["+r.name+"] address["+r.address+"]" );
}
}
conn.commit();
} catch(Exception e) {
try {
conn.rollback();
} catch (Exception e2) {
return;
}
}
return;
}
}
## 2.5. Executing your application
SqlactionDemoSAU.DELETE_FROM_sqlaction_demo_WHERE_name_E_ ok , rows[1] effected SqlactionDemoSAU.INSERT_INTO_sqlaction_demo ok SqlactionDemoSAU.UPDATE_sqlaction_demo_SET_address_E_WHERE_name_E_ ok , rows[1] effected SqlactionDemoSAO.SELECT_ALL_FROM_sqlaction_demo ok id[20] name[Calvin] address[My address 2]
# 3. Reference
## 3.1. Process flow
sqlaction
dbserver.conf.json¡¢sqlaction.conf.json ---------> XxxSao.java¡¢XxxSau.java --
---> App.jar
App.java --/
# 4. Workload difference MyBatis and sqlaction
<table>
<tr>
<td align="center">MyBatis</td>
<td align="center">sqlaction</td>
</tr>
<tr>
<td colspan="2" align="center">Configure project once<td>
</tr>
<tr>
<td>Databaes conntion config<img src="mybatis-config.xml.png" /></td>
<td>Databaes conntion config<img src="dbserver.conf.json.png" /></td>
</tr>
<tr>
<td colspan="2" align="center">Configure every table<td>
</tr>
<tr>
<td>table mapper config<img src="mybatis-mapper.xml.png" /></td>
<td>table action config<img src="sqlaction.conf.json.png" /></td>
</tr>
<tr>
<td>table entity class<img src="SqlactionBenchmarkSAO.java.png" /></td>
<td>sqlaction auto-gen</td>
</tr>
<tr>
<td>table interface class<img src="SqlactionBenchmarkSAOMapper.java.png" /></td>
<td>Don't need</td>
</tr>
<tr>
<td>Don't need</td>
<td>sqlaction execute command?<br />java -Dfile.encoding=UTF-8 -classpath "D:\Work\sqlaction\sqlaction.jar;D:\Work\mysql-connector-java-8.0.15\mysql-connector-java-8.0.15.jar" xyz.calvinwilliams.sqlaction.gencode.SqlActionGencode</td>
</tr>
</table>
# 5. Benchmark difference MyBatis and sqlaction
CPU : Intel Core i5-7500 3.4GHz 3.4GHz
Momey : 16GB
OS : WINDOWS 10
JAVA IDE : Eclipse 2018-12
Database : MySQL 8.0.15 Community Server
Database connect-address : 127.0.0.1:3306
DDL
CREATE TABLE sqlaction_benchmark (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
name varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '英文厄1¤71¤71?71¤7',
name_cn varchar(128) COLLATE utf8mb4_bin NOT NULL COMMENT '䶿文各1¤71¤71?71¤7',
salary decimal(12,2) NOT NULL COMMENT '蔿殿',
birthday date NOT NULL COMMENT '生日',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=42332 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
## 5.1. Prepare sqlaction
Create `dbserver.conf.json`
{ "driver" : "com.mysql.jdbc.Driver" , "url" : "jdbc:mysql://127.0.0.1:3306/calvindb?serverTimezone=GMT" , "user" : "calvin" , "pwd" : "calvin" }
Create `sqlaction.conf.json`
{ "database" : "calvindb" , "tables" : [ { "table" : "sqlaction_benchmark" , "sqlactions" : [ "INSERT INTO sqlaction_benchmark" , "UPDATE sqlaction_benchmark SET salary=? WHERE name=?" , "SELECT * FROM sqlaction_benchmark WHERE name=?" , "SELECT * FROM sqlaction_benchmark" , "DELETE FROM sqlaction_benchmark WHERE name=?" , "DELETE FROM sqlaction_benchmark" ] } ] , "javaPackage" : "xyz.calvinwilliams.sqlaction.benchmark" }
Executing `sqlaction`, auto-gen `SqlactionBenchmarkSAO.java`
Create `SqlActionBenchmarkCrud.java`
/*
package xyz.calvinwilliams.sqlaction.benchmark;
import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.LinkedList; import java.util.List;
public class SqlActionBenchmarkCrud {
public static void main(String[] args) {
Connection conn = null ;
SqlactionBenchmarkSAO sqlactionBenchmark ;
List<SqlactionBenchmarkSAO> sqlactionBenchmarkList ;
long beginMillisSecondstamp ;
long endMillisSecondstamp ;
double elpaseSecond ;
long i , j , k ;
long count , count2 , count3 ;
int rows = 0 ;
// connect to database
try {
Class.forName( "com.mysql.jdbc.Driver" );
conn = DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/calvindb?serverTimezone=GMT", "calvin", "calvin" ) ;
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.setAutoCommit(false);
sqlactionBenchmark = new SqlactionBenchmarkSAO() ;
sqlactionBenchmark.name = "Calvin" ;
sqlactionBenchmark.nameCn = "卡尔攄1¤71¤71?71¤7" ;
sqlactionBenchmark.salary = new BigDecimal(0) ;
long time = System.currentTimeMillis() ;
sqlactionBenchmark.birthday = new java.sql.Date(time) ;
count = 500 ;
count2 = 5 ;
count3 = 1000 ;
rows = SqlactionBenchmarkSAO.DELETE_FROM_sqlaction_benchmark( conn ) ;
conn.commit();
// benchmark for INSERT
beginMillisSecondstamp = System.currentTimeMillis() ;
for( i = 0 ; i < count ; i++ ) {
sqlactionBenchmark.name = "Calvin"+i ;
sqlactionBenchmark.nameCn = "卡尔攄1¤71¤71?71¤7"+i ;
rows = SqlactionBenchmarkSAO.INSERT_INTO_sqlaction_benchmark( conn, sqlactionBenchmark ) ;
if( rows != 1 ) {
System.out.println( "SqlactionBenchmarkSAO.INSERT_INTO_sqlaction_benchmark failed["+rows+"]" );
return;
}
if( i % 10 == 0 ) {
conn.commit();
}
}
conn.commit();
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All sqlaction INSERT done , count["+count+"] elapse["+elpaseSecond+"]s" );
// benchmark for UPDATE
beginMillisSecondstamp = System.currentTimeMillis() ;
for( i = 0 ; i < count ; i++ ) {
rows = SqlactionBenchmarkSAO.UPDATE_sqlaction_benchmark_SET_salary_E_WHERE_name_E_( conn, new BigDecimal(i), "Calvin"+i ) ;
if( rows != 1 ) {
System.out.println( "SqlactionBenchmarkSAO.UPDATE_sqlaction_benchmark_SET_salary_E_WHERE_name_E_ failed["+rows+"]" );
return;
}
if( i % 10 == 0 ) {
conn.commit();
}
}
conn.commit();
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All sqlaction UPDATE WHERE done , count["+count+"] elapse["+elpaseSecond+"]s" );
// benchmark for SELECT ... WHERE ...
beginMillisSecondstamp = System.currentTimeMillis() ;
for( j = 0 ; j < count2 ; j++ ) {
for( i = 0 ; i < count ; i++ ) {
sqlactionBenchmarkList = new LinkedList<SqlactionBenchmarkSAO>() ;
rows = SqlactionBenchmarkSAO.SELECT_ALL_FROM_sqlaction_benchmark_WHERE_name_E_( conn, sqlactionBenchmarkList, "Calvin"+i ) ;
if( rows != 1 ) {
System.out.println( "SqlactionBenchmarkSAO.SELECT_ALL_FROM_sqlaction_benchmark_WHERE_name_E_ failed["+rows+"]" );
return;
}
}
}
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All sqlaction SELECT WHERE done , count2["+count2+"] count["+count+"] elapse["+elpaseSecond+"]s" );
// benchmark for SELECT
beginMillisSecondstamp = System.currentTimeMillis() ;
for( k = 0 ; k < count3 ; k++ ) {
sqlactionBenchmarkList = new LinkedList<SqlactionBenchmarkSAO>() ;
rows = SqlactionBenchmarkSAO.SELECT_ALL_FROM_sqlaction_benchmark( conn, sqlactionBenchmarkList ) ;
if( rows != count ) {
System.out.println( "SqlactionBenchmarkSAO.SELECT_ALL_FROM_sqlaction_benchmark failed["+rows+"]" );
return;
}
}
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All sqlaction SELECT to LIST done , count3["+count3+"] elapse["+elpaseSecond+"]s" );
// benchmark for DELETE
beginMillisSecondstamp = System.currentTimeMillis() ;
for( i = 0 ; i < count ; i++ ) {
rows = SqlactionBenchmarkSAO.DELETE_FROM_sqlaction_benchmark_WHERE_name_E_( conn, "Calvin"+i ) ;
if( rows != 1 ) {
System.out.println( "SqlactionBenchmarkSAO.DELETE_FROM_sqlaction_benchmark_WHERE_name_E_ failed["+rows+"]" );
return;
}
if( i % 10 == 0 ) {
conn.commit();
}
}
conn.commit();
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All sqlaction DELETE WHERE done , count["+count+"] elapse["+elpaseSecond+"]s" );
} catch(Exception e) {
e.printStackTrace();
try {
conn.rollback();
} catch (Exception e2) {
e.printStackTrace();
return;
}
} finally {
try {
conn.close();
} catch (Exception e2) {
e2.printStackTrace();
return;
}
}
return;
}
}
## 5.2. Prepare MyBatis
Create `mybatis-config.xml`
Create mybatis-mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="xyz.calvinwilliams.mybatis.benchmark.SqlactionBenchmarkSAOMapper">
<insert id="insertOne" parameterType="xyz.calvinwilliams.mybatis.benchmark.SqlactionBenchmarkSAO">
INSERT INTO sqlaction_benchmark (name,name_cn,salary,birthday) VALUES( #{name}, #{name_cn}, #{salary}, #{birthday} )
</insert>
<update id="updateOneByName" parameterType="xyz.calvinwilliams.mybatis.benchmark.SqlactionBenchmarkSAO">
UPDATE sqlaction_benchmark SET salary=#{salary} WHERE name=#{name}
</update>
<select id="selectOneByName" parameterType="java.lang.String" resultType="xyz.calvinwilliams.mybatis.benchmark.SqlactionBenchmarkSAO" flushCache="true" useCache="false">
SELECT * FROM sqlaction_benchmark WHERE name=#{name}
</select>
<select id="selectAll" resultType="xyz.calvinwilliams.mybatis.benchmark.SqlactionBenchmarkSAO" flushCache="true" useCache="false">
SELECT * FROM sqlaction_benchmark
</select>
<delete id="deleteOneByName" parameterType="java.lang.String">
DELETE FROM sqlaction_benchmark WHERE name=#{name}
</delete>
<delete id="deleteAll">
DELETE FROM sqlaction_benchmark
</delete>
</mapper>
Create SqlactionBenchmarkSAO.java
package xyz.calvinwilliams.mybatis.benchmark;
import java.math.*;
public class SqlactionBenchmarkSAO {
int id ;
String name ;
String name_cn ;
BigDecimal salary ;
java.sql.Date birthday ;
int _count_ ; // defining for 'SELECT COUNT(*)'
}
Create SqlactionBenchmarkSAOMapper.java
package xyz.calvinwilliams.mybatis.benchmark;
import java.util.*;
public interface SqlactionBenchmarkSAOMapper {
public void insertOne(SqlactionBenchmarkSAO sqlactionBenchmark);
public void updateOneByName(SqlactionBenchmarkSAO sqlactionBenchmark);
public SqlactionBenchmarkSAO selectOneByName(String name);
public List<SqlactionBenchmarkSAO> selectAll();
public void deleteOneByName(String name);
public void deleteAll();
}
Create MyBatisBenchmarkCrud.java
/*
* sqlaction - SQL action object auto-gencode tool based JDBC for Java
* author : calvin
* email : calvinwilliams@163.com
*
* See the file LICENSE in base directory.
*/
package xyz.calvinwilliams.mybatis.benchmark;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import xyz.calvinwilliams.mybatis.benchmark.SqlactionBenchmarkSAO;
import xyz.calvinwilliams.mybatis.benchmark.SqlactionBenchmarkSAOMapper;
public class MyBatisBenchmarkCrud {
public static void main(String[] args) {
SqlSession session = null ;
SqlactionBenchmarkSAOMapper mapper ;
List<SqlactionBenchmarkSAO> sqlactionBenchmarkList ;
long beginMillisSecondstamp ;
long endMillisSecondstamp ;
double elpaseSecond ;
long i , j , k ;
long count , count2 , count3 ;
try {
FileInputStream in = new FileInputStream("src/main/java/mybatis-config.xml");
session = new SqlSessionFactoryBuilder().build(in).openSession();
SqlactionBenchmarkSAO sqlactionBenchmark = new SqlactionBenchmarkSAO() ;
sqlactionBenchmark.id = 1 ;
sqlactionBenchmark.name = "Calvin" ;
sqlactionBenchmark.name_cn = "卡尔攄1¤71¤71?71¤7" ;
sqlactionBenchmark.salary = new BigDecimal(0) ;
long time = System.currentTimeMillis() ;
sqlactionBenchmark.birthday = new java.sql.Date(time) ;
count = 500 ;
count2 = 5 ;
count3 = 1000 ;
mapper = session.getMapper(SqlactionBenchmarkSAOMapper.class) ;
mapper.deleteAll();
session.commit();
// benchmark for INSERT
beginMillisSecondstamp = System.currentTimeMillis() ;
for( i = 0 ; i < count ; i++ ) {
sqlactionBenchmark.name = "Calvin"+i ;
sqlactionBenchmark.name_cn = "卡尔攄1¤71¤71?71¤7"+i ;
mapper.insertOne(sqlactionBenchmark);
if( i % 10 == 0 ) {
session.commit();
}
}
session.commit();
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All mybatis INSERT done , count["+count+"] elapse["+elpaseSecond+"]s" );
// benchmark for UPDATE
beginMillisSecondstamp = System.currentTimeMillis() ;
for( i = 0 ; i < count ; i++ ) {
sqlactionBenchmark.name = "Calvin"+i ;
sqlactionBenchmark.salary = new BigDecimal(i) ;
mapper.updateOneByName(sqlactionBenchmark);
if( i % 10 == 0 ) {
session.commit();
}
}
session.commit();
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All mybatis UPDATE done , count["+count+"] elapse["+elpaseSecond+"]s" );
// benchmark for SELECT ... WHERE ...
beginMillisSecondstamp = System.currentTimeMillis() ;
for( j = 0 ; j < count2 ; j++ ) {
for( i = 0 ; i < count ; i++ ) {
sqlactionBenchmark = mapper.selectOneByName(sqlactionBenchmark.name) ;
if( sqlactionBenchmark == null ) {
System.out.println( "mapper.selectOneByName failed" );
return;
}
}
}
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All mybatis SELECT WHERE done , count2["+count2+"] count["+count+"] elapse["+elpaseSecond+"]s" );
// benchmark for SELECT
beginMillisSecondstamp = System.currentTimeMillis() ;
for( k = 0 ; k < count3 ; k++ ) {
sqlactionBenchmarkList = mapper.selectAll() ;
if( sqlactionBenchmarkList == null ) {
System.out.println( "mapper.selectAll failed" );
return;
}
}
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All mybatis SELECT to List done , count3["+count3+"] elapse["+elpaseSecond+"]s" );
// benchmark for DELETE
beginMillisSecondstamp = System.currentTimeMillis() ;
for( i = 0 ; i < count ; i++ ) {
sqlactionBenchmark.name = "Calvin"+i ;
mapper.deleteOneByName(sqlactionBenchmark.name);
if( i % 10 == 0 ) {
session.commit();
}
}
session.commit();
endMillisSecondstamp = System.currentTimeMillis() ;
elpaseSecond = (endMillisSecondstamp-beginMillisSecondstamp)/1000.0 ;
System.out.println( "All mybatis DELETE done , count["+count+"] elapse["+elpaseSecond+"]s" );
} catch (Exception e) {
e.printStackTrace();
}
}
}
INSERT table for 500 records UPDATE table for 500 records SELECT table for 500*5 records SELECT table to List for 1000 records DELETE table for 500 records
All sqlaction INSERT done , count[500] elapse[4.742]s
All sqlaction UPDATE WHERE done , count[500] elapse[5.912]s
All sqlaction SELECT WHERE done , count2[5] count[500] elapse[0.985]s
All sqlaction SELECT to LIST done , count3[1000] elapse[1.172]s
All sqlaction DELETE WHERE done , count[500] elapse[5.001]s
All mybatis INSERT done , count[500] elapse[5.869]s
All mybatis UPDATE WHERE done , count[500] elapse[6.921]s
All mybatis SELECT WHERE done , count2[5] count[500] elapse[1.239]s
All mybatis SELECT to List done , count3[1000] elapse[1.792]s
All mybatis DELETE WHERE done , count[500] elapse[5.382]s
All sqlaction INSERT done , count[500] elapse[5.392]s
All sqlaction UPDATE WHERE done , count[500] elapse[5.821]s
All sqlaction SELECT WHERE done , count2[5] count[500] elapse[0.952]s
All sqlaction SELECT to LIST done , count3[1000] elapse[1.15]s
All sqlaction DELETE WHERE done , count[500] elapse[5.509]s
All mybatis INSERT done , count[500] elapse[6.066]s
All mybatis UPDATE WHERE done , count[500] elapse[6.946]s
All mybatis SELECT WHERE done , count2[5] count[500] elapse[1.183]s
All mybatis SELECT to List done , count3[1000] elapse[1.804]s
All mybatis DELETE WHERE done , count[500] elapse[5.958]s
All sqlaction INSERT done , count[500] elapse[5.236]s
All sqlaction UPDATE WHERE done , count[500] elapse[5.84]s
All sqlaction SELECT WHERE done , count2[5] count[500] elapse[0.985]s
All sqlaction SELECT to LIST done , count3[1000] elapse[1.222]s
All sqlaction DELETE WHERE done , count[500] elapse[4.91]s
All mybatis INSERT done , count[500] elapse[5.448]s
All mybatis UPDATE WHERE done , count[500] elapse[7.287]s
All mybatis SELECT WHERE done , count2[5] count[500] elapse[1.149]s
All mybatis SELECT to List done , count3[1000] elapse[1.873]s
All mybatis DELETE WHERE done , count[500] elapse[6.035]s
sqlaction's performance faster 20% than MyBatis
Download source at : gitee,github
Apache Maven
<dependency>
<groupId>xyz.calvinwilliams</groupId>
<artifactId>sqlaction</artifactId>
<version>0.2.9.0</version>
</dependency>
FAQs
Unknown package
We found that xyz.calvinwilliams:sqlaction demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Ransomware payment rates hit an all-time low in 2024 as law enforcement crackdowns, stronger defenses, and shifting policies make attacks riskier and less profitable.
Security News
require(esm) backported to Node.js 20, easing the transition to ESM-only packages and reducing complexity for developers as Node 18 nears end-of-life.
Security News
PyPI now supports iOS and Android wheels, making it easier for Python developers to distribute mobile packages.