How To - Creating An Auto SQL Query Class In Java

This class was developed to eliminate the need to type SQL statements, in any java application where databases are used. This class needs 'AutoDBConnect' class to work properly. it will able to add, edit and delete images from a DB.

/*
This class was developed to eliminate the need to type SQL statements, because even a small error in typeing can cause a error.
it was written to mySQL.
you can ADD,update(edit),delete and even files can be added such as images
###VERY IMPORTANT #######This class needs 'AutoDBConnect' class to work properly
Author -Dinushka95@yahoo.com
3/3/2017
*/
package MainSystem;
import static MainSystem.AutoDB_Connect.DB_PreparedStatement;
import static MainSystem.AutoDB_Connect.DB_connection;
import Sales.SalesDesignInquiry;
import java.awt.Image;
import java.awt.Toolkit;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.ImageIcon;
/**
*
* @author Dinushka
*/
//###VERY IMPORTANT #######This class needs 'AutoDBConnect' class to work properly
public class AutoSQLQuery extends AutoDB_Connect{
private static AutoDB_Connect db_con;
String DB_Name;
public AutoSQLQuery(String DatabaseName) {
db_con= new AutoDB_Connect();
db_con.connect();
DB_Name=DatabaseName;
}
// used to return value from a search query
// returns all values
public ResultSet executeAutoViewAll(String TableName)
{
return db_con.executeQuery("SELECT * FROM "+DB_Name+"."+TableName+";");
}
// used to return value from a search query
// returns only selected values
public ResultSet executeAutoViewSelected(String SQL_String[],String TableName)
{
int length=SQL_String.length;
String ArraySelect="";
int count=0;
while(count<length){
if(ArraySelect.equals(""))
{ArraySelect= ArraySelect+"`"+SQL_String[count]+"`";}
else
{ArraySelect= ArraySelect+",`"+SQL_String[count]+"`";}
count++;
}
ArraySelect="select "+ArraySelect+" FROM `"+DB_Name+"`.`"+TableName+"`";
return db_con.executeQuery(ArraySelect);
}
// used to return value from a search query with a key
// returns all values
public ResultSet executeAutoSearchAll(String TableName,String KeyName,String Key)
{
String ArraySelect="";
ArraySelect="SELECT * FROM "+DB_Name+"."+TableName+" where "+KeyName+" = '"+Key+"'";
return db_con.executeQuery(ArraySelect);
}
// used to return value from a search query with a key
// returns all values
public ResultSet executeAutoSearchSelected(String SQL_String[],String TableName,String KeyName,String Key)
{
int length=SQL_String.length;
String ArraySelect="";
int count=0;
while(count<length){
if(ArraySelect.equals(""))
{ArraySelect= ArraySelect+"`"+SQL_String[count]+"`";}
else
{ArraySelect= ArraySelect+",`"+SQL_String[count]+"`";}
count++;
}
ArraySelect="select "+ArraySelect+" FROM `"+DB_Name+"`.`"+TableName+"`where "+KeyName+" = '"+Key+"'";
return db_con.executeQuery(ArraySelect);
}
//auto search data ranges
public ResultSet executeAutoSearchDateRange(String TableName,String KeyName,String DateFrom,String DateTo)
{
String ArraySelect="";
ArraySelect="SELECT * FROM "+DB_Name+"."+TableName+" WHERE "+KeyName+" >= '"+DateFrom+"' AND "+KeyName+" <= '"+DateTo+"'" ;
return db_con.executeQuery(ArraySelect);
}
//auto search data ranges
public ResultSet executeAutoSearchDate(String TableName,String KeyName,String Date)
{
String ArraySelect="";
ArraySelect="SELECT * FROM "+DB_Name+"."+TableName+" WHERE "+KeyName+" = '"+Date+"'" ;
return db_con.executeQuery(ArraySelect);
}
// used to ADD values
public boolean executeAutoADD(String SQL_String[],String TableName)
{
int length=SQL_String.length;
String ArraySelect="";
String ArraySelect1[]= new String[length];
String ArraySelect2[]=new String[length];
String ArraySelect11="";
String ArraySelect22="";
int count=0;
while(count<length){
String tem=SQL_String[count];
int x=tem.indexOf("=");
ArraySelect1[count]=tem.substring(0,x);
ArraySelect2[count]=tem.substring(x+1);
// System.out.println(ArraySelect1);
// System.out.println(tem.substring(x+1));
count++;
}
count=0;
while(count<length){
if(ArraySelect11.equals(""))
{ArraySelect11= ArraySelect11+"`"+ArraySelect1[count]+"`";}
else
{ArraySelect11= ArraySelect11+",`"+ArraySelect1[count]+"`";}
count++;
}
count=0;
while(count<length){
if(ArraySelect22.equals(""))
{ArraySelect22= ArraySelect22+"'"+ArraySelect2[count]+"'";}
else
{ArraySelect22= ArraySelect22+",'"+ArraySelect2[count]+"'";}
count++;
}
//System.out.println(ArraySelect);
ArraySelect="INSERT INTO `"+DB_Name+"`.`"+TableName+"`("+ArraySelect11+") VALUES ("+ArraySelect22+")";
return db_con.execute(ArraySelect);
}
// this method is used to edit values
public boolean executeAutoEdit(String SQL_String[],String TableName,String KeyName,String Key)
{
int length=SQL_String.length;
String ArraySelect="";
String ArraySelect1[]= new String[length];
String ArraySelect2[]=new String[length];
String ArraySelect11="";
int count=0;
while(count<length){
String tem=SQL_String[count];
int x=tem.indexOf("=");
ArraySelect1[count]=tem.substring(0,x);
ArraySelect2[count]=tem.substring(x+1);
count++;
}
count=0;
while(count<length){
if(ArraySelect11.equals(""))
{ArraySelect11= ArraySelect11+"`"+ArraySelect1[count]+"` ='"+ArraySelect2[count]+"'";}
else
{ArraySelect11= ArraySelect11+",`"+ArraySelect1[count]+"` ='"+ArraySelect2[count]+"'";}
count++;
}
//System.out.println(ArraySelect);
ArraySelect="UPDATE `"+DB_Name+"`.`"+TableName+"` SET "+ArraySelect11+" WHERE "+KeyName+" = '"+Key+"'";
return db_con.execute(ArraySelect);
}
// used to delete values from the table
public boolean executeAutoDelete(String TableName,String KeyName,String Key)
{
String ArraySelect="DELETE FROM `"+DB_Name+"`.`"+TableName+"` WHERE "+KeyName+" = '"+Key+"'";
return db_con.execute(ArraySelect);
}
// used to add Images into the DB (.jpe)
public boolean executeAutoAddImage(String FileNameId,File ImageFile)
{
FileInputStream filedata = null;
try {
filedata = new FileInputStream(ImageFile);
} catch (FileNotFoundException ex) {
Logger.getLogger(AutoSQLQuery.class.getName()).log(Level.SEVERE, null, ex);
}
try {
DB_PreparedStatement =DB_connection.prepareStatement("insert into image_Table(img_title, img_data) values(?,?)");
DB_PreparedStatement.setString(1,FileNameId);
DB_PreparedStatement.setBinaryStream(2, (InputStream) filedata, (int)(ImageFile.length()));
DB_PreparedStatement.executeUpdate();
}
catch (SQLException ex)
{
Logger.getLogger(SalesDesignInquiry.class.getName()).log(Level.SEVERE, null, ex);
return false;
}
return true;
}
//used to retrive images from the DB
public ImageIcon executeAutoGetImage(String FileNameId)
{
ImageIcon icon = null;
AutoDB_Connect.DB_ResultSet = db_con.executeQuery("SELECT `image_Table`.`img_data`\n" +"FROM `"+DB_Name+"`.`image_Table` where `image_Table`.`img_title` ='"+FileNameId+"';");
try {
AutoDB_Connect.DB_ResultSet.next();
Blob imageBlob=AutoDB_Connect.DB_ResultSet.getBlob("img_data");
InputStream binaryStream = imageBlob.getBinaryStream(1, imageBlob.length());
Image myImage;
try (ByteArrayOutputStream output = new ByteArrayOutputStream()) {
int a1 = binaryStream.read();
while (a1 >= 0){
output.write((char) a1);
a1 = binaryStream.read();
} myImage = Toolkit.getDefaultToolkit().createImage(output.toByteArray());
}
icon = new ImageIcon(myImage);
} catch (SQLException | IOException ex) {
Logger.getLogger(SalesDesignInquiry.class.getName()).log(Level.SEVERE, null, ex);
}
return icon;
}
}
view raw AutoSQLQuery.java hosted with ❤ by GitHub

Comments

Post a Comment

Popular posts from this blog

Homemade water pump

Image
It all started when i wanted to water-cool my PC, i search for a water- pumps on Sri-lankan market and  didn't find any good one but i manage to find a fish tank clening pump but it was expensive and noise. so i started to search on E-bay but they were expensive to ship to Sri lanka. so i did some research on the internet about home build water pumps and got some idea on centrifugal water pumps and how they work... when i was a small boy i saw a plumber repairing household water pump  and wondered how it work. Because at that time i couldn't understand how a spinning wheel could pump water. Wikipedia about Centrifugal pump http://en.wikipedia.org/wiki/Centrifugal_pump The challenge was to make a water pump, from what every i can get my hands on; around the house so that  i could keep my cost to minimum and have fun enjoying building it  .... Motor inside a coke...
Read more

Best Internet Connection In Sri Lanka For a Youth (2015 April)

Image
Definition of youth = downloading  movies, watch music video , TV shows , you tube and tonne of web browsing. (Minimum 100GB per month)  First internet connection i ever had was SLT prepaid dial up connection. Now in 2015 the internet has change, people has change(market) and also sri lankan ISP has change. * LankaBell 4G is connection fee of  7500, monthly rental of 1350 with out tax for 35GB @(speed 4Mbps downlaod & 1Mbps. Also you can get Unlimited connection but at very slow speed. *I Tested Mobitel,Etisalat,Airtel  but not include in the review because it was a waste of time and those packages does not make sense for any human or animal to use.  So here is what is the best for you... Dialog 4G SLT-ADSL Dialog Prepaid Time-base # CHEAPEST INTERNET CONNECTION PER GB (DOWNLOADING) WINNER = #Dialog prepaid I test all other isp but non of then were unlimited, they had rental (commitment) except Lankbell But the ...
Read more