Call stored procedure from inside Java class and write to console?

Question

I want to be able to call a stored procedure from a Microsoft SQL Server database and list the results to the console (that is to start with). At someone I want to put all that data either to a JTable or to the web but first I will be happy if I can display it to the Console.

I think that the problem is that I cannot (or probably do not know how) to establish a connection to the database.

How can I connect to my Microsoft SQL Server database? (I have seen it done before and I think that it was 4 lines of code. I want this to work on my machine so I can start testing out some of the stored procedure.

Java Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLServerTest {
    private ResultSet[] yourResult;

    private void showSuppliers(ResultSet[] rs) throws SQLException {
            Connection con =
                                DriverManager.getConnection("jdbc:default:connection");
            Statement stmt = null;

            String query = "select * from PeopleOne";

            stmt = con.createStatement();
            rs[0] = stmt.executeQuery(query);
        }

     private void ShowResultsToConsole(ResultSet[] rs) throws SQLException {
      for (ResultSet r : rs) {
        int c = 0; 
        r.getString(c);
      }
    }

    public static void main(String[] p) throws SQLException {
         SQLServerTest temp = new  SQLServerTest();
         temp.showSuppliers(temp.yourResult);
             temp.ShowResultsToConsole(temp.yourResult);
    }

}

whats stopping you from calling a stored procedure if the procedure contains select statement it can be called using execute query and for the part of connecting to sql server from database i say what is stopping from using google for and i'll suggest http://stackoverflow.com/questions/19047414/java-program-to-connect-to-sql-server-and-running-the-sample-query-from-eclipse — codefreaK, May 29 '14 at 17:56

score 2 · Answer 1 · edited May 23 '17 at 12:24

Try something like this.

public class DbTest {

static final String DRIVER_CLASS = "net.sourceforge.jtds.jdbc.Driver";
static final String DB_URL = "jdbc:jtds:sqlserver://127.0.0.1:1433/mydb";
static final String DB_USER = "username";
static final String DB_PASS = "password";

public static void testQuery() throws ClassNotFoundException, SQLException {

    Class.forName(DRIVER_CLASS);
    Connection jdbcConnection = DriverManager.getConnection(DB_URL,
            DB_USER, DB_PASS);

    ResultSet rs = jdbcConnection.createStatement().executeQuery(
            "SELECT TOP 10 field_name FROM table_name");

    while (rs.next()) {
        System.out.println(rs.getString("field_name"));
    }

}

public static void main(String args[]) throws Exception {
    testQuery();
}

This assumes you have jTDS JDBC Driver in your classpath.

If you need to use Microsoft JDBC Driver instead you should have it in your classpath and change the following in the code.

static final String DRIVER_CLASS = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
static final String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=mydb";

You should always close your resources properly as described in Java / JDBC: Best design pattern to close database connection when exception occurs

I think that it is in the class path but how do I make certain that it is? This is something in Java that I don't have to do to often. — Doug Hauf, May 29 '14 at 20:51
on the password part all I see is the login which would look something like this. WY/abcd12345 and no password. Does that make a difference? — Doug Hauf, May 29 '14 at 21:06
Are you getting a particular error or stack trace when you run the code? If so, can you post it? To set the classpath see http://stackoverflow.com/questions/6434213/adding-jdbc-driver-to-classpath. Can you connect to your database using an external database client like http://dbeaver.jkiss.org/ for instance? — cjungel, May 29 '14 at 21:50

Call stored procedure from inside Java class and write to console?

1 Answers1