Sample for Supported SQL Functions
The Zoho Analytics JDBC Driver enables seamless integration with your Java applications, allowing you to execute SQL queries on your Zoho Analytics data programmatically. This section provides ready-to-use code samples for supported SQL operations, helping you quickly connect, query, and manage your data.
Each section below includes Java code examples demonstrating how to perform these operations using Zoho Analytics JDBC driver.
Samples for supported SQL Operations
- Fetch Data: Retrieve data from tables and query tables.
- Insert Data: Add rows to a table.
- Update Data: Modify existing rows in a table.
- Delete Data: Remove rows from a table.
- Create Table: Define a new table.
- Create Query Table: Define a new query table.
- Rename Table: Change the name of an existing table.
- Delete Table: Remove a table.
- Add Column: Introduce a new column to a table.
- Rename Column: Change the name of an existing column.
- Delete Column: Remove a column from a table.
- Add Lookup: Establish a lookup relationship between tables.
Fetch Data - Sample Code
Copiedpublic static void fetchDataUsingSQL(Connection con) throws Exception
{
Statement stmt = null;
ResultSet rs = null;
try
{
stmt = con.createStatement();
String sql ="select * from Sales where Region='East'";
rs = stmt.executeQuery(sql);
while (rs.next())
{
String name = rs.getString("Customer Name");
Date date = rs.getDate("Date");
System.out.println(name + " purchased on " + date.toString());
}
}
catch (Exception e)
{
throw e;
}
finally
{
if(rs != null)
{
try
{
rs.close();
}
catch(SQLException sqlEx) { }
}
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void insertRow(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "insert into \"Sales\" (\"Region\",\"Date\",\"Customer Name\") values('West', 'November 24, 2014', 'Mark Ruffalo')";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void updateRows(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "update Sales set \"Region\"='East' where \"Customer Name\"='Mark Ruffalo'";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void deleteRows(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "delete from Sales where \"Customer Name\"='Mark Ruffalo'";
//String sql = "Truncate Sales";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void createTable(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "CREATE TABLE \"Sales_JDBC\" ("
+ "\"Region\" PLAIN NOT NULL DEFAULT 'East'," // Plain text
+ "\"Customer Name\" PLAIN NOT NULL," // Plain text
+ "\"Remarks\" MULTI_LINE NOT NULL," // Multi-line text
+ "\"Sales\" DECIMAL_NUMBER DEFAULT 0.00," // Decimal number type
+ "\"Date\" DATE, " // Date type
+ "\"Is Active\" BOOLEAN DEFAULT Yes" // Boolean type
+ ");";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void createQueryTable(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "CREATE VIEW SalesQuery AS\n" +
"SELECT 'Customer Name', Region, Date FROM Sales;";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void renameTable(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "ALTER TABLE \"SalesData\" RENAME TO \"Sales\"";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void deleteTable(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "Drop Table \"Sales_JDBC\"";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void addColumn(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "alter table \"Sales\" ADD COLUMN \"Product\" PLAIN";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void renameColumn(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "alter table \"Sales\" RENAME COLUMN \"Region\" to \"Street\"";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void deleteColumn(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "alter table \"Sales\" DROP COLUMN \"Cost\"";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}
Copiedpublic static void addLookup(Connection con) throws Exception
{
Statement stmt = null;
try
{
stmt = con.createStatement();
String sql = "ALTER TABLE \"Sales\" ADD CONSTRAINT fk_sales_region\n" +
"FOREIGN KEY ('Region') REFERENCES Customer(Location)";
stmt.execute(sql);
}
catch (Exception e)
{
throw e;
}
finally
{
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { }
}
}
}