在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
开源软件名称:sqlaction开源软件地址:https://gitee.com/calvinwilliams/sqlaction开源软件介绍:sqlaction - 自动生成JDBC代码的数据库持久层工具1. 概述厌烦了
能否造一个更好的轮子? 于是,我结合之前在C技术栈中的产品经验,结合JAVA特点,写了sqlaction。
2. 一个DEMO放一个DEMO感受一下: 2.1. 建表DDL以MySQL为例
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 2.2. 新建JAVA项目
建立包目录,在包目录或上级某一级目录中创建数据库连接配置文件 { "driver" : "com.mysql.jdbc.Driver" , "url" : "jdbc:mysql://127.0.0.1:3306/calvindb?serverTimezone=GMT" , "user" : "calvin" , "pwd" : "calvin"} 在包目录或上级某一级目录中创建SQL动作配置文件 { "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"} 2.3. 在包目录中执行sqlaction工具此示例中我把执行命令行包装成批处理文件执行,欢迎懂
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.9.0\sqlaction-0.2.9.0.jar" xyz.calvinwilliams.sqlaction.SqlActionGencodepause 注意:使用 执行 ///////////////////////////////////////////////////////////////////////////////// sqlaction v0.2.9.0/// Copyright by calvin<[email protected],[email protected]>//////////////////////////////////////////////////////////////////////////////--- 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!!! 如果没有出现
// This file generated by sqlaction v0.2.9.0// WARN : DON'T MODIFY THIS FILEpackage 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; }}
// This file generated by sqlaction v0.2.9.0package 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 {} 2.4. 到目前为止,一行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. 执行SqlactionDemoSAU.DELETE_FROM_sqlaction_demo_WHERE_name_E_ ok , rows[1] effectedSqlactionDemoSAU.INSERT_INTO_sqlaction_demo okSqlactionDemoSAU.UPDATE_sqlaction_demo_SET_address_E_WHERE_name_E_ ok , rows[1] effectedSqlactionDemoSAO.SELECT_ALL_FROM_sqlaction_demo ok id[20] name[Calvin] address[My address 2] 总结: 对表的增删改查只需调用前面自动生成的SAU类中的方法即可,底层执行代码完全基于JDBC,可随时查看,没有什么秘密,没有什么高深的技术。 工具 3. 使用参考3.1. 开发流程 sqlactiondbserver.conf.json、sqlaction.conf.json ---------> XxxSao.java、XxxSau.java --\ ---> App.jar App.java --/
SAO类 SAU类 3.2. 配置文件dbserver.conf.json{ "driver" : "com.mysql.jdbc.Driver" , "url" : "jdbc:mysql://127.0.0.1:3306/calvindb?serverTimezone=GMT" , "user" : "root" , "pwd" : "root" , "userDefineDataTypes" : [ { "source":"decimal,*,12,2" , "redefine":"double,*,14,*" } ]} 数据库连接配置文件
数据库字段类型与sqlaction的JAVA变量类型映射表:
注意:数据库连接配置文件 注意:读取JSON配置文件使用到了我的另一个开源项目:
3.3. 配置文件sqlaction.conf.json{ "database" : "calvindb" , "tables" : [ { "table" : "user_base" , "sqlactions" : [ "SELECT * FROM user_base" , "SELECT * FROM user_base WHERE name=?" , "SELECT name,address FROM user_base WHERE age<=? AND gender=?" , "SELECT * FROM user_base ORDER BY name DESC" , "SELECT gender,count(*) FROM user_base GROUP BY gender" , "INSERT INTO user_base @@SELECTSEQ(user_base_seq_id) @@SELECTKEY(id)" , "UPDATE user_base SET lvl=?" , "UPDATE user_base SET address='calvin address',lvl=10 WHERE name='Calvin'" , "UPDATE user_base SET lvl=? WHERE age>? AND gender=?" , "DELETE FROM user_base" , "DELETE FROM user_base WHERE name='Calvin'" , "DELETE FROM user_base WHERE age<>? AND gender<>?" ] } , { "table" : "user_order" , "sqlactions" : [ "SELECT /* blablabla~ */ * FROM user_order @@STATEMENT_INTERCEPTOR()" , "SELECT * FROM user_order WHERE user_id=?" , "SELECT * FROM user_order @@PAGEKEY(id)" , "SELECT * FROM user_order WHERE item_name<>'' @@PAGEKEY(id) @@PAGESORT(DESC)" , "SELECT user_base.name,user_base.address,user_order.item_name,user_order.amount,user_order.total_price FROM user_base,user_order WHERE user_base.name=? AND user_base.id=user_order.user_id @@METHOD(queryUserAndOrderByName)" , "SELECT u.name,u.address,o.* FROM user_base u,user_order o WHERE u.name=? AND u.id=o.user_id @@STATEMENT_INTERCEPTOR(statementInterceptorForQueryUserAndOrderByName)" , "SELECT o.* #{UserOrderSAU.*} FROM user_order o #{user_order} @@ADVANCEDMODE" , "SELECT MIN(total_price) #{SqlActionTest.minTotalPrice:double}, MAX(total_price) #{SqlActionTest.maxTotalPrice:double}, COUNT(*) #{UserOrderSAU._count_} FROM user_order #{user_order} @@ADVANCEDMODE" , "SELECT user_base.name #{UserBaseSAU.name} ,user_order.item_name #{UserOrderSAU.itemName} ,SUM(user_order.amount) #{UserOrderSAU.amount} ,SUM(user_order.total_price) #{UserOrderSAU.totalPrice} FROM user_base #{user_base} ,user_order #{user_order} WHERE user_order.user_id IN ( SELECT id FROM user_base WHERE id>=? #{UserOrderSAU.id} ) AND user_order.user_id=user_base.id GROUP BY user_base.name ORDER BY user_base.name @@ADVANCEDMODE @@METHOD(statUsersAmountAndTotalPrice)" , "INSERT INTO user_order @@SELECTSEQ(user_order_seq_id) @@SELECTKEY(id)" , "UPDATE user_order SET total_price=? WHERE user_id=?" , "DELETE FROM user_order" , "DELETE FROM user_order WHERE user_id=? #{UserOrderSAU.userId} @@ADVANCEDMODE @@METHOD(removeUserOrder)" ] } ] , "javaPackage" : "xyz.calvinwilliams.test"} SQL动作配置文件
目前 查询 SQL动作语法: SELECT [*|[table_name.|table_alias_name.][column_name|*][,...][,COUNT(*)]] [ /* hint */ ] FROM table_name [table_alias_name],... [ WHERE [table_name.|table_alias_name.]column_name [=|<>|>|>=|<|<=] [?|const|[table_name2.|table_alias_name2.]column_name2] [AND ...] ] [ GROUP BY [table_name.|table_alias_name.]column[,[table_name2.|table_alias_name.]column2][,...] ] [ HAVING ... ] [ ORDER BY column[,...] [ASC|DESC] ] ... SQL动作语法示例: { "database" : "calvindb" , "tables" : [ { "table" : "user_base" , "sqlactions" : [ "SELECT * FROM user_base" , "SELECT * FROM user_base WHERE name=?" , "SELECT name,address FROM user_base WHERE age<=? AND gender=?" , "SELECT * FROM user_base ORDER BY name DESC" , "SELECT gender,count(*) FROM user_base GROUP BY gender" , 自动生成JDBC代码: // SELECT * FROM user_base public static int SELECT_ALL_FROM_user_base( Connection conn, List<UserBaseSAU> userBaseListForSelectOutput ) throws Exception { Statement stmt = conn.createStatement() ; ResultSet rs = stmt.executeQuery( "SELECT * FROM user_base" ) ; while( rs.next() ) { UserBaseSAU userBase = new UserBaseSAU() ; userBase.id = rs.getInt( 1 ) ; userBase.name = rs.getString( 2 ) ; userBase.gender = rs.getString( 3 ) ; userBase.age = rs.getShort( 4 ) ; userBase.address = rs.getString( 5 ) ; userBase.lvl = rs.getInt( 6 ) ; userBaseListForSelectOutput.add(userBase) ; } rs.close(); stmt.close(); return userBaseListForSelectOutput.size(); } // SELECT * FROM user_base WHERE name=? public static int SELECT_ALL_FROM_user_base_WHERE_name_E_( Connection conn, List<UserBaseSAU> userBaseListForSelectOutput, String _1_UserBaseSAU_name ) throws Exception { PreparedStatement prestmt = conn.prepareStatement( "SELECT * FROM user_base WHERE name=?" ) ; prestmt.setString( 1, _1_UserBaseSAU_name ); ResultSet rs = prestmt.executeQuery() ; while( rs.next() ) { UserBaseSAU userBase = new UserBaseSAU() ; userBase.id = rs.getInt( 1 ) ; userBase.name = rs.getString( 2 ) ; userBase.gender = rs.getString( 3 ) ; userBase.age = rs.getShort( 4 ) ; userBase.address = rs.getString( 5 ) ; userBase.lvl = rs.getInt( 6 ) ; userBaseListForSelectOutput.add(userBase) ; } rs.close(); prestmt.close(); return userBaseListForSelectOutput.size(); } // SELECT name,address FROM user_base WHERE age<=? AND gender=? public static int SELECT_name_j_address_FROM_user_base_WHERE_age_LE_AND_gender_E_( Connection conn, List<UserBaseSAU> userBaseListForSelectOutput, short _1_UserBaseSAU_age, String _2_UserBaseSAU_gender ) throws Exception { PreparedStatement prestmt = conn.prepareStatement( "SELECT name,address FROM user_base WHERE age<=? AND gender=?" ) ; prestmt.setShort( 1, _1_UserBaseSAU_age ); prestmt.setString( 2, _2_UserBaseSAU_gender ); ResultSet rs = prestmt.executeQuery() ; while( rs.next() ) { UserBaseSAU userBase = new UserBaseSAU() ; userBase.name = rs.getString( 1 ) ; userBase.address = rs.getString( 2 ) ; userBaseListForSelectOutput.add(userBase) ; } rs.close(); prestmt.close(); return userBaseListForSelectOutput.size(); } // SELECT * FROM user_base ORDER BY name DESC public static int SELECT_ALL_FROM_user_base_ORDER_BY_name_DESC( Connection conn, List<UserBaseSAU> userBaseListForSelectOutput ) throws Exception { Statement stmt = conn.createStatement() ; ResultSet rs = stmt.executeQuery( "SELECT * FROM user_base ORDER BY name DESC" ) ; while( rs.next() ) { UserBaseSAU userBase = new UserBaseSAU() ; userBase.id = rs.getInt( 1 ) ; userBase.name = rs.getString( 2 ) ; userBase.gender = rs.getString( 3 ) ; userBase.age = rs.getShort( 4 ) ; userBase.address = rs.getString( 5 ) ; userBase.lvl = rs.getInt( 6 ) ; userBaseListForSelectOutput.add(userBase) ; } rs.close(); stmt.close(); return userBaseListForSelectOutput.size(); } // SELECT gender,count(*) FROM user_base GROUP BY gender public static int SELECT_gender_j_count_ALL_FROM_user_base_GROUP_BY_gender( Connection conn, List<UserBaseSAU> userBaseListForSelectOutput ) throws Exception { Statement stmt = conn.createStatement() ; ResultSet rs = stmt.executeQuery( "SELECT gender,count(*) FROM user_base GROUP BY gender" ) ; while( rs.next() ) { UserBaseSAU userBase = new UserBaseSAU() ; userBase.gender = rs.getString( 1 ) ; userBase._count_ = rs.getInt( 2 ) ; userBaseListForSelectOutput.add(userBase) ; } rs.close(); stmt.close(); return userBaseListForSelectOutput.size(); } 插入 SQL动作语法: INSERT INTO table_name SQL动作语法示例: { "database" : "calvindb" , "tables" : [ { "table" : "user_base" , "sqlactions" : [ "INSERT INTO user_base @@SELECTSEQ(user_base_seq_id) @@SELECTKEY(id)" , 自动生成JDBC代码: // INSERT INTO user_base @@SELECTSEQ(user_base_seq_id) @@SELECTKEY(id) public static int INSERT_INTO_user_base( Connection conn, UserBaseSAU userBase ) throws Exception { PreparedStatement prestmt ; Statement stmt ; ResultSet rs ; prestmt = conn.prepareStatement( "INSERT INTO user_base (name,gender,age,address,lvl) VALUES (?,?,?,?,?)" ) ; prestmt.setString( 1, userBase.name ); prestmt.setString( 2, userBase.gender ); prestmt.setShort( 3, userBase.age ); prestmt.setString( 4, userBase.address ); prestmt.setInt( 5, userBase.lvl ); int count = prestmt.executeUpdate() ; prestmt.close(); if( count != 1 ) return count; stmt = conn.createStatement() ; rs = stmt.executeQuery( "SELECT LAST_INSERT_ID()" ) ; rs.next(); userBase.id = rs.getInt( 1 ) ; rs.close(); stmt.close(); return count; } 更新 SQL动作语法: UPDATE table_name SET column_name = [?|const|column_name2] [,...] [ WHERE column_name [=|<>|>|>=|<|<=] [const|column_name2] [AND ...] ] SQL动作语法示例: { "database" : "calvindb" , "tables" : [ { "table" : "user_base" , "sqlactions" : [ "UPDATE user_base SET lvl=?" , "UPDATE user_base SET address='calvin address',lvl=10 WHERE name='Calvin'" , "UPDATE user_base SET lvl=? WHERE age>? AND gender=?" , 自动生成JDBC代码: // UPDATE user_base SET lvl=? public static int UPDATE_user_base_SET_lvl_E_( Connection conn, int _1_lvl_ForSetInput ) throws Exception { PreparedStatement prestmt = conn.prepareStatement( "UPDATE user_base SET lvl=?" ) ; prestmt.setInt( 1, _1_lvl_ForSetInput ); int count = prestmt.executeUpdate() ; prestmt.close(); return count; } // UPDATE user_base SET address='calvin address',lvl=10 WHERE name='Calvin' public static int UPDATE_user_base_SET_address_E_calvin_address_j_lvl_E_10_WHERE_name_E_Calvin_( Connection conn ) throws Exception { PreparedStatement prestmt = conn.prepareStatement( "UPDATE user_base SET address='calvin address',lvl=10 WHERE name='Calvin'" ) ; int count = prestmt.executeUpdate() ; prestmt.close(); return count; } // UPDATE user_base SET lvl=? WHERE age>? AND gender=? public static int UPDATE_user_base_SET_lvl_E_WHERE_age_GT_AND_gender_E_( Connection conn, int _1_lvl_ForSetInput, short _1_age_ForWhereInput, String _2_gender_ForWhereInput ) throws Exception { PreparedStatement prestmt = conn.prepareStatement( "UPDATE user_base SET lvl=? WHERE age>? AND gender=?" ) ; prestmt.setInt( 1, _1_lvl_ForSetInput ); prestmt.setShort( 2, _1_age_ForWhereInput ); prestmt.setString( 3, _2_gender_ForWhereInput ); int count = prestmt.executeUpdate() ; prestmt.close(); return count; } 删除 SQL动作语法: DELETE FROM table_name [ WHERE column_name [=|<>|>|>=|<|<=] [const|column_name2] [AND ...] ] SQL动作语法示例: { "database" : "calvindb" , "tables" : [ { "table" : "user_base" , "sqlactions" : [ "DELETE FROM user_base" , "DELETE FROM user_base WHERE name='Calvin'" , "DELETE FROM user_base WHERE age<>? AND gender<>?" 自动生成JDBC代码: // DELETE FROM user_base public static int DELETE_FROM_user_base( Connection conn ) throws Exception { PreparedStatement prestmt = conn.prepareStatement( "DELETE FROM user_base" ) ; int count = prestmt.executeUpdate() ; prestmt.close(); return count; } // DELETE FROM user_base WHERE name='Calvin' public static int DELETE_FROM_user_base_WHERE_name_E_Calvin_( Connection conn ) throws Exception { PreparedStatement prestmt = conn.prepareStatement( "DELETE FROM user_base WHERE name='Calvin'" ) ; int count = prestmt.executeUpdate() ; prestmt.close(); return count; } // DELETE FROM user_base WHERE age<>? AND gender<>? public static int DELETE_FROM_user_base_WHERE_age_NE_AND_gender_NE_( Connection conn, short _1_age, String _2_gender ) throws Exception { PreparedStatement prestmt = conn.prepareStatement( "DELETE FROM user_base WHERE age<>? AND gender<>?" ) ; prestmt.setShort( 1, _1_age ); prestmt.setString( 2, _2_gender ); int count = prestmt.executeUpdate() ; prestmt.close(); return count; } 注意:数据库连接配置文件 3.4. 自动生成JDBC代码的规则工具 数据库表字段映射属性由数据库中的表结构元信息映射生成,转换规则见前面的数据库字段类型与sqlaction的JAVA变量类型映射表。如果DDL中有comment,则在表实体类的对应属性后面加注释。 SQL动作对应缺省方法名为SQL转换而来,具体算法为所有非字母数字字符都转换为'_',合并多个'_'为一个。 方法前的注释是原SQL,以便于对照和定位。 方法的第一个参数是数据库连接对象,可以和连接池框架结合使用。 如果SQL动作涉及输出,自动生成的代码在SQL执行后,根据解析出来的输出项(SELECT)自动生成getString等代码,方法参数中也要求给予以便于输出,按表实体类列表对象排列,SQL JOIN多表对应多个表实体类列表对象。 如果SQL动作涉及输入,自动生成的代码将使用JDBC的prepareStatement,并根据解析出来的输入项(SET、WHERE)自动生成setString等代码,方法参数中也要求给予以便于输入,按字段名排列。如果没有输入则使用createStatement。 如果是查询SQL,JAVA方法返回表实体类列表大小。如果是插入、更新、删除SQL,JAVA方法返回受影响记录条数。 表实体类属性列表中额外有"int count ;",用于查询COUNT(*)时存储输出结果用。 插入方法中会自动识别忽略自增类型。 就这么简单! 3.5. 配置元SQL动作的最后面可追加一些以"@@"开头的配置元以实现一些额外的功能。 3.5.1. 自定义SQL动作方法名允许自定义SQL动作方法名,在SQL动作配置中追加元信息"@@METHOD(自定义方法名)",如: "SELECT user_base.name,user_base.address,user_order.item_name,user_order.amount,user_order.total_price FROM user_base,user_order WHERE user_base.name=? AND user_base.id=user_order.user_id @@METHOD(queryUserAndOrderByName)" , 自动生成代码如下: public static int queryUserAndOrderByName( Connection conn, List<UserBaseSAU> userBaseListForSelectOutput, List<UserOrderSAU> userOrderListForSelectOutput, String _1_UserBaseSAU_name ) throws Exception { ... return userBaseListForSelectOutput.size(); } 3.5.2. 抽象统一了自增字段和序列两大数据库阵营对主键值的赋值在INSERT时对主键值的赋值,一些拥有自增类型的数据库如MySQL只要在DDL里指明 示例: { "database" : "calvindb" , "tables" : [ { "table" : "user" , "sqlactions" : [ ... "INSERT INTO user_base @@SELECTKEY(id) @@SELECTSEQ(user_base_seq_id)" , 当在MySQL等有自增功能的DBMS中, public static int INSERT_INTO_user_base( Connection conn, UserBaseSAU userBase ) throws Exception { PreparedStatement prestmt ; Statement stmt ; ResultSet rs ; prestmt = conn.prepareStatement( "INSERT INTO user_base (name,gender,age,address,lvl) VALUES (?,?,?,?,?)" ) ; prestmt.setString( 1, userBase.name ); prestmt.setString( 2, userBase.gender ); prestmt.setShort( 3, userBase.age ); prestmt.setString( 4, userBase.address ); prestmt.setInt( 5, userBase.lvl ); int count = prestmt.executeUpdate() ; prestmt.close(); if( count != 1 ) return count; stmt = conn.createStatement() ;
全部评论
专题导读
上一篇:mybatis-plus-join: mybatis-plus的多表插件完全按照mybats plus的用法来做,支持一对 ...发布时间:2022-03-24下一篇:easy-jdbc: 一个简单、快速,支持高并发的jdbc,目前只支持MySQL数据库。 ...发布时间:2022-03-24热门推荐
热门话题
阅读排行榜
|
请发表评论