Nutz calls stored procedure

Nutz calls stored procedure

Recently, a colleague asked me how Nutz calls stored procedures. Originally, Nutz's documents are super mature, so reading documents can solve the problem,,,
Nutz is an alternative to SSH for Java programmers. Of course, it's open source and completely free.
Light - the latest version, 910kB in total for the entire jar file - for JDBC's thin package, no cache.
Full - provides Dao(ORM, SQL Management), Ioc, Aop, Mvc, Json parsing and other necessary functions.
Live - each part can be used independently, such as using Nutz.Dao in Spring, Hibernate in Nutz.Ioc, etc.
Integer - it does not rely on third-party jar files for all its functions. This means: if a Web application, you only need to put a nutz.jar under WEB-INF/lib. Of course, if you want to use other functions such as connection pool, database driver, print PDF support, you need to add jar package by yourself.
These are all extraneous remarks. Now let's talk about how Nutz calls stored procedures,
1. Return a single cursor

public List<Record> get_invoice_header(Long org_id, Long invoice_id) {
        final List<Record> list = new ArrayList<Record>();
        dao().run(new ConnCallback() {
            public void invoke(Connection conn) throws Exception {
                CallableStatement cs = null;
                ResultSet rs = null;
                String procedure = "Call nc_longmars_om_pkg.get_invoice_header_info(?,?,?)";
                cs = conn.prepareCall(procedure);
                cs.setLong(1, invoice_id);
                cs.setLong(2, org_id);
                cs.registerOutParameter(3, OracleTypes.CURSOR);
                cs.execute();
                rs = (ResultSet) cs.getObject(3);// Get the value of the cursor row
                while (rs.next()) {
                    list.add(dao().getEntity(Record.class).getObject(rs, null, ""));
                }
            }
        });
        return list;
    }

2. Return multiple, including basic types

public Record get_invoice_list(final Integer page, final Integer pageSize, final Map<String, String> param) {
        final Record record = new Record();
        final List<Record> list = new ArrayList<Record>();
        String customer = param.get("customer") + "";
        String invoice_number = param.get("invoice_number") + "";
        dao().run(new ConnCallback() {
            public void invoke(Connection conn) throws Exception {
                CallableStatement cs = null;
                ResultSet rs = null;
                String procedure = "Call nc_longmars_om_pkg.get_invoice_list(?,?,?,?,?,?,?)";
                cs = conn.prepareCall(procedure);
                if (dataUtil.checkIsNull(customer)) {
                    cs.setNull(1, OracleTypes.VARCHAR);
                } else {
                    cs.setString(1, customer);
                }
                cs.setLong(2, dataUtil.getLong(param.get("org_id")));
                if (dataUtil.checkIsNull(invoice_number)) {
                    cs.setNull(3, OracleTypes.VARCHAR);
                } else {
                    cs.setString(3, invoice_number);
                }
                cs.setInt(4, pageSize);
                cs.setInt(5, page);
                cs.registerOutParameter(6, OracleTypes.NUMBER);
                cs.registerOutParameter(7, OracleTypes.CURSOR);
                cs.execute();

                Long total = dataUtil.getLong(cs.getObject(6));
                record.set("total", total);

                rs = (ResultSet) cs.getObject(7);// Get the value of the cursor row
                while (rs.next()) {
                    list.add(dao().getEntity(Record.class).getObject(rs, null, ""));
                }
            }
        });
        record.set("list", list);
        return record;
    }

3. use Sql

public Record syn2erp(Record record) {
        // P & U header & ID middle table header & ID
        Sql sql = Sqls.create(
                "CALL nc_edi_lexar_pkg.so_synchronization_to_erp(@p_header_id, @OUTx_status,@OUTx_message,@OUTx_erp_order_number)");
        sql.params().set("p_header_id", record.get("HEADER_ID")); // Setup parameter
        sql.params().set("OUTx_status", OracleTypes.VARCHAR); // Set the parameter type,
        sql.params().set("OUTx_message", OracleTypes.VARCHAR); // Set the parameter type,
        sql.params().set("OUTx_erp_order_number", OracleTypes.NUMBER); // Set the parameter type,
        dao().execute(sql);
        /**
         * x_status out varchar2, --Output status flag, value 'S' indicates creation succeeded, value' E 'indicates unsuccessful 
         * x_message out varchar2,--Output exception information
         * 
         */
        Record re = sql.getOutParams();
        return re;
    }

In fact, it should be 2 kinds. The above 1 and 2 belong to the same kind. 3 uses Sql, which is very convenient to use...

Tags: SQL Stored Procedure ssh Java

Posted on Tue, 31 Mar 2020 22:59:39 -0700 by RickChase