How to run .sql file using pl*sql developer?

Question

I am trying to connect pl*sql developer tool using Java, where I want to run SQL queries. I know how to run sql queries in Oracle database using Java, but not able to run using pl*sql developer. My Oracle database is installed on another server.

Below is my code for database connectivity using Java.

import java.sql.*;
import java.util.*;


public class DatabaseConnectivity {

private static final String DEFAULT_DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String DEFAULT_URL = "jdbc:oracle:thin:@localhost:1521/XE";
private static final String DEFAULT_USERNAME = "appuser";
private static final String DEFAULT_PASSWORD = "vivek123";


public static void main(String[] args)
{
    long begTime = System.currentTimeMillis();

    String driver = ((args.length > 0) ? args[0] : DEFAULT_DRIVER);
    String url = ((args.length > 1) ? args[1] : DEFAULT_URL);
    String username = ((args.length > 2) ? args[2] : DEFAULT_USERNAME);
    String password = ((args.length > 3) ? args[3] : DEFAULT_PASSWORD);

    Connection connection = null;

    try
    {
        connection = createConnection(driver, url, username, password);
        DatabaseMetaData meta = connection.getMetaData();
        System.out.println(meta.getDatabaseProductName());
        System.out.println(meta.getDatabaseProductVersion()); 

        String sqlQuery = "select * from student";
        connection.commit();
        
        System.out.println(query(connection, sqlQuery, Collections.EMPTY_LIST));
    }
    catch (Exception e)
    {
        rollback(connection);
        e.printStackTrace();
    }
    finally
    {
        close(connection);
        long endTime = System.currentTimeMillis();
     //   System.out.println("wall time: " + (endTime - begTime) + " ms");
    }
}
public static Connection createConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException
{
    Class.forName(driver);

    if ((username == null) || (password == null) || (username.trim().length() == 0) || (password.trim().length() == 0))
    {
        return DriverManager.getConnection(url);
    }
    else
    {
        return DriverManager.getConnection(url, username, password);
    }
}
public static void close(Connection connection)
{
    try
    {
        if (connection != null)
        {
            connection.close();
        }
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
}

public static void close(Statement st)
{
    try
    {
        if (st != null)
        {
            st.close();
        }
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
}

public static void close(ResultSet rs)
{
    try
    {
        if (rs != null)
        {
            rs.close();
        }
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
}

public static void rollback(Connection connection)
{
    try
    {
        if (connection != null)
        {
            connection.rollback();
        }
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
}

public static List<Map<String, Object>> map(ResultSet rs) throws SQLException
{
    List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();

    try
    {
        if (rs != null)
        {
            ResultSetMetaData meta = rs.getMetaData();
            int numColumns = meta.getColumnCount();
            while (rs.next())
            {
                Map<String, Object> row = new HashMap<String, Object>();
                for (int i = 1; i <= numColumns; ++i)
                {
                    String name = meta.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(name, value);
                }
                results.add(row);
            }
        }
    }
    finally
    {
        close(rs);
    }

    return results;
}

public static List<Map<String, Object>> query(Connection connection, String sql, List<Object> parameters) throws SQLException
{
    List<Map<String, Object>> results = null;

    PreparedStatement ps = null;
    ResultSet rs = null;

    try
    {
        ps = connection.prepareStatement(sql);

        int i = 0;
        for (Object parameter : parameters)
        {
            ps.setObject(++i, parameter);
        }

        rs = ps.executeQuery();
        results = map(rs);
    }
    finally
    {
        close(rs);
        close(ps);
    }

    return results;
}

public static int update(Connection connection, String sql, List<Object> parameters) throws SQLException
{
    int numRowsUpdated = 0;

    PreparedStatement ps = null;

    try
    {
        ps = connection.prepareStatement(sql);

        int i = 0;
        for (Object parameter : parameters)
        {
            ps.setObject(++i, parameter);
        }

        numRowsUpdated = ps.executeUpdate();
    }
    finally    
    {    
        close(ps);    
    }    

    return numRowsUpdated;    
}
}

Answer 1

You have mentioned "oracle database in on another server" then why there is localhost in Driver Url?

Answer 2

Basically you want to run query on an remote oracle database. For that you just need to pass database connection details like ip address/hostname, port , DB name and user/pwd.

Still not sure what do you mean by 'connecting plsql developer from java code'? Plsql developer also uses JDBC code to connect to any oracle DB just like you would do from your JDBC code.

Answer 3

You should use same credentials in java code which you use in pl*sql developer. Java code can be generic for all databases if you are using JDBC.

I dont think there is any backend api's provided for pl*sql which can be called thru java code.