How to run SQL(MYSQL) stored procedure in JAVA jdbc?

Question

I'm a MySQL user and I have been using following statements in MySQL Workbench : (these statements are based on Select column names whose entries are not null)

SET group_concat_max_len = 4294967295;

SELECT GROUP_CONCAT(
 ' SELECT ',QUOTE(COLUMN_NAME),
 ' FROM   ( select * from table_name where s3_01 = ', coloumn1,' ) abc',
 ' WHERE `',REPLACE(COLUMN_NAME, '`', '``'),'` IS NOT NULL',
 ' HAVING COUNT(*)'
SEPARATOR ' UNION ALL ')
INTO   @sql
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'table_name';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Although it work in my workbench, I do not know how to make it work in java. for example, I made following code:

String sql1 = "SET group_concat_max_len = 4294967295;";
String sql2 = " SELECT GROUP_CONCAT(' SELECT ',QUOTE(COLUMN_NAME), ' FROM   ( select * from ptc_weight where s3_01 = ',column1,' ) abc', ' WHERE `',REPLACE(COLUMN_NAME, '`', '``'),'` IS NOT NULL', ' HAVING COUNT(*)' SEPARATOR ' UNION ALL ') INTO   @sql FROM   INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ptc_weight'; ";
String sql3 = " PREPARE stmt FROM @sql; ";
String sql4 = " EXECUTE stmt;"; 
String sql5 = " DEALLOCATE PREPARE stmt;";

String[] result = getResult(sql1+sql2+sql3+sql4+sql5);

public static String[][] getResult(String sql) {
        System.out.println(sql);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String[][] resultTable = null;
        try {
            con = getCon();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData result = rs.getMetaData();
            int rowNum=0;
            // Go to the last row 
            rs.last(); 
            rowNum = rs.getRow(); 
        // Reset row before iterating to get data 
            rs.beforeFirst();
            int colNum = result.getColumnCount();

            resultTable = new String[rowNum][colNum];
            for(int itr1=0; itr1<rowNum; itr1++){
                rs.next();
                for(int itr2=0; itr2<colNum; itr2++){
                    resultTable[itr1][itr2] =     rs.getObject(itr2+1).toString();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            dbclose(con, ps, rs);
        }// finally
        return resultTable;
    }

However, it does not work. I guess I made a wrong code for utilizing stored procedure, but I don't have any idea to deal with this problem.

user3145373 ツ · Accepted Answer · 2014-04-28 12:18:50Z

CallableStatement callableStatement = null;

String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
callableStatement.setInt(1, 10);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4, java.sql.Types.DATE);

// execute getDBUSERByUserId store procedure
callableStatement.executeUpdate();

String userName = callableStatement.getString(2);
String createdBy = callableStatement.getString(3);
Date createdDate = callableStatement.getDate(4);

System.out.println("UserName : " + userName);
System.out.println("CreatedBy : " + createdBy);
System.out.println("CreatedDate : " + createdDate);

Here Is Full Example. You can modify your code as you need.

Simple one with less argument and with resultset :

CallableStatement cstmt = con.prepareCall("{call getEmployeeDetails(?, ?)}");
cstmt.setInt("employeeId", 123);
cstmt.setInt("companyId", 456);
ResultSet rs = cstmt.executeQuery();

Collectives™ on Stack Overflow

How to run SQL(MYSQL) stored procedure in JAVA jdbc?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related