6 Replies - 431 Views - Last Post: 26 May 2012 - 08:42 PM Rate Topic: -----

#1 raihan26  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 47
  • Joined: 04-February 10

Represent a SQL Block in java

Posted 26 May 2012 - 05:58 PM

I have this sql block that I want to write in java using something like this

public static final String create_sql = "Below sql Block"


DECLARE
  t_count INTEGER;
  v_sql VARCHAR2(1000) := 'create table TEST (
            id number NOT NULL PRIMARY KEY,  
            url varchar(1000) NOT NULL,  
            urlHash varchar(255) NOT NULL,  
            contentHash varchar(255),  
            modDate varchar(30),  
            contentLocation varchar(100),  
            status integer,  
            lastCrawlDate varchar(30))';
BEGIN
  SELECT COUNT(*)
    INTO t_count
    FROM user_tables
   WHERE table_name = 'TEST';

  IF t_count = 0 THEN
    EXECUTE IMMEDIATE v_sql;
  END IF;
END;
/


Can anyone help me with this. Any help will be appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: Represent a SQL Block in java

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10397
  • View blog
  • Posts: 38,478
  • Joined: 27-December 08

Re: Represent a SQL Block in java

Posted 26 May 2012 - 06:10 PM

You can use PreparedStatements to execute your SQL statements. However, your Java application should only be responsible for SELECT, DELETE, UPDATE, and INSERT commands. It really shouldn't be creating tables in the database.
Was This Post Helpful? 0
  • +
  • -

#3 raihan26  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 47
  • Joined: 04-February 10

Re: Represent a SQL Block in java

Posted 26 May 2012 - 06:20 PM

View Postmacosxnerd101, on 26 May 2012 - 06:10 PM, said:

You can use PreparedStatements to execute your SQL statements. However, your Java application should only be responsible for SELECT, DELETE, UPDATE, and INSERT commands. It really shouldn't be creating tables in the database.


Yes I know but my question is- How can I represent that whole query as a String in java.
And also I want to create a table if it doesn't exists in the oracle database, so that is the reason I wrote that query, so it will check in the database if the table is already there, then it will not create it, that is the reason I wanted to represent that query as a String

This post has been edited by raihan26: 26 May 2012 - 06:23 PM

Was This Post Helpful? 0
  • +
  • -

#4 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10397
  • View blog
  • Posts: 38,478
  • Joined: 27-December 08

Re: Represent a SQL Block in java

Posted 26 May 2012 - 06:21 PM

Surround it with double quotes.
Was This Post Helpful? 0
  • +
  • -

#5 raihan26  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 47
  • Joined: 04-February 10

Re: Represent a SQL Block in java

Posted 26 May 2012 - 06:24 PM

View Postmacosxnerd101, on 26 May 2012 - 06:21 PM, said:

Surround it with double quotes.


That double quotes is making me more confused as I am not sure how should I do that. Any help will be appreciated.


	public static final String sql = "DECLARE " +
	"t_count INTEGER; " +
	"v_sql VARCHAR2(1000) := 'create table TEST ( " +
	            "id number NOT NULL PRIMARY KEY, " +
	            "url varchar(1000) NOT NULL, " +
	            "urlHash varchar(255) NOT NULL, " +
	            "contentHash varchar(255),  " +
	            "modDate varchar(30),  " +
	            "contentLocation varchar(100),  " +
	            "status integer,  " +
	            "lastCrawlDate varchar(30))';" +
	"BEGIN " +
	"SELECT COUNT(*) " +
	"INTO t_count " +
	"FROM user_tables " +
	"WHERE table_name = 'TEST'; " +

	"IF t_count = 0 THEN " +
	"EXECUTE IMMEDIATE v_sql; " +
	"END IF; " +
	"END; " +
	"/";

This post has been edited by raihan26: 26 May 2012 - 06:30 PM

Was This Post Helpful? 0
  • +
  • -

#6 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10397
  • View blog
  • Posts: 38,478
  • Joined: 27-December 08

Re: Represent a SQL Block in java

Posted 26 May 2012 - 06:30 PM

This is how you create Strings:
String s = "This is a String";
String query = "SELECT * FROM table_name";



And always compile your code. That is the easiest way to determine if you are doing something wrong.
Was This Post Helpful? 1
  • +
  • -

#7 x68zeppelin80x  Icon User is offline

  • D.I.C Addict

Reputation: 130
  • View blog
  • Posts: 576
  • Joined: 07-March 09

Re: Represent a SQL Block in java

Posted 26 May 2012 - 08:42 PM

Why don't you just save the query as a text file i.e. test.sql and read it in using the java.util.Scanner class?

Scanner Read File

Quote

import java.io.File;
import java.util.Scanner;

public class Exercise {
    public static void main(String[] args)  throws Exception {
	// Indicate that you are planning to opena file
	File fleExample = new File("Example.xpl");
        // Prepare a Scanner that will "scan" the document
        Scanner opnScanner = new Scanner(fleExample);

	// Read each line in the file
        while( opnScanner.hasNext() ) {
            // Read each line and display its value
	    System.out.println("First Name:    " + opnScanner.nextLine());
	    System.out.println("Last Name:     " + opnScanner.nextLine());
	    System.out.println("Hourly Salary: " + opnScanner.nextLine());
	    System.out.println("Is Full Time?: " + opnScanner.nextLine());
	}
            
    	// De-allocate the memory that was used by the scanner
        opnScanner.close();
    }
}



Instead of printing each line in the while... just:
String sql = "";
while( opnScanner.hasNext() ) {
  sql += opnScanner.nextLine();
}


This post has been edited by x68zeppelin80x: 26 May 2012 - 09:00 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1