Sample JDBC Program

Download mysql-connector-java-5.1.12-bin.jar. Include mysql jar in your project. (Go to Eclipse Menu 'Project -> Properties -> Java Build Path -> Libraries -> 'Add External JARs' add downloaded jar)

package testJdbcPackage;

import java.sql.*;

public class TestJdbc {
 
 Connection conn;
 
 public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
  new TestJdbc();
 }
 
 public TestJdbc() throws InstantiationException, IllegalAccessException, ClassNotFoundException {
  String url = "jdbc:mysql://localhost/test";
  String user = "root";
  String password = "mysql";
  try {
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   conn = DriverManager.getConnection(url, user, password);
   doInsertTest();
   doDeleteTest();
   doInsertTest();
   doSelectTest();
  } catch (SQLException e) {
   e.printStackTrace();
  }  
 }
 
 private void doDeleteTest() {
  try {
   Statement statement = conn.createStatement();
   //Get the count
   ResultSet rs = statement.executeQuery("SELECT COUNT(1) FROM student");
   int recordCount = 0;
   while(rs.next()) {
    recordCount = rs.getInt("COUNT(1)");
   }
   rs.close();
   if (recordCount > 1) {
    recordCount -= 1 ;
   }
   boolean defaultAutoCommit = conn.getAutoCommit();
   conn.setAutoCommit(false);
   try {
    statement.executeUpdate("DELETE from student LIMIT " + recordCount);
    conn.commit();
   } catch (Throwable e) {
    conn.rollback();
   } finally {
    conn.setAutoCommit(defaultAutoCommit);
   }   
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }  
 }

 private void doSelectTest() {
  Statement statement;
  try {
   statement = conn.createStatement();
   ResultSet rs = statement.executeQuery("SELECT * from student");
   while(rs.next()) {
    int columnCount  = rs.getMetaData().getColumnCount();
    for (int i = 1; i <= columnCount; i++) {
     System.out.println(rs.getObject(i));
    }
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }    
 }

 private void doInsertTest() {
  Statement statement;
  try {
   statement = conn.createStatement();
   statement.executeUpdate("INSERT INTO student(name, status, deleted) VALUES('Gubs', 'active', 0)");
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }    
 }
}

SQL Server Commands

SQL Server commands for date :

convert(date, start_date) != convert(date, GETDATE())

update [THIRD_PARTY_DELIVERY].[dbo].[Integration_FTP]
set ftp_url_path = REPLACE(ftp_url_path, '/pre_processed','')
where network_name = 'thomsonreuters'

MySQL Caps first

DELIMITER $$


DROP FUNCTION IF EXISTS `CAP_FIRST `$$


CREATE FUNCTION CAP_FIRST (input VARCHAR(255))


RETURNS VARCHAR(255)


DETERMINISTIC


BEGIN

DECLARE len INT;

DECLARE i INT;


SET len = CHAR_LENGTH(input);

SET input = LOWER(input);

SET i = 0;


WHILE (i < len) DO

IF (MID(input,i,1) = ' ' OR i = 0) THEN

IF (i < len) THEN

SET input = CONCAT(

LEFT(input,i),

UPPER(MID(input,i + 1,1)),

RIGHT(input,len - i - 1)

);

END IF;

END IF;

SET i = i + 1;

END WHILE;


RETURN input;

END$$


DELIMITER ;

select CAP_FIRST(item_name) from cms_menu_items;

Java - Date Formats

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
String dt = sdf.format(payloadAccountRequest.getLastModifiedTime());
query.append(dt.replace(" ", "T") + "Z");

import java.sql.TimeStamp;
Calendar calendar = Calendar.getInstance();
calendar.add(calendar.YEAR, -5);
Timestamp lastModifiedTime = new Timestamp(calendar.getTimeInMillis());

public void compareTimeStamp() {
String sfdcTimeStampString = "2010-10-29T15:01:28.000Z";
sfdcTimeStampString = sfdcTimeStampString.replace("T", " ");
sfdcTimeStampString = sfdcTimeStampString.replace("Z", " ");

Calendar calendar = Calendar.getInstance();

Timestamp sdktimeStamp = new Timestamp(calendar.getTimeInMillis());

Timestamp sfdcTimeStamp = Timestamp.valueOf(sfdcTimeStampString);

if (sdktimeStamp.after(sfdcTimeStamp)) {
System.out.println("SDK Wins");
} else {
System.out.println("SFDC Wins");
}
}

setDate(new Date(System.currentTimeMillis()))

Java - Conversion Tips

Covert Long to String with Format


format : Returns a formatted string using the specified format string and arguments.


Long customerId = new Long(12);
System.out.println("Output..." + String.format("%04d", customerId));

Convert Double to String

Double doubleValue = new Double(40.5508995056152);
        double doubleVal = doubleValue;
        System.out.println("Double value.." + Double.toString(doubleVal));



String destinationDataType = destinationClass.toString();
String sourceValue = sourceFieldValue.toString();

SimplePropertyValueTO destinationFieldProperty = new SimplePropertyValueTO();
destinationFieldProperty.setPropertyName(param);
destinationFieldProperty.setPropertyType(destinationDataType);

if (destinationDataType.equalsIgnoreCase("string")) {
destinationFieldProperty.setPropertyStringValue(sourceValue);
} else if (destinationDataType.equalsIgnoreCase("short") || destinationDataType.equalsIgnoreCase("int")
|| destinationDataType.equalsIgnoreCase("long")) {

Long longDestinationValue = null;
longDestinationValue = longDestinationValue.parseLong(sourceValue);
destinationFieldProperty.setPropertyLongValue(longDestinationValue);
} else if (destinationDataType.equalsIgnoreCase("double")) {
Double doubleDestinationValue = null;
doubleDestinationValue = doubleDestinationValue.parseDouble(sourceValue);
} else if (destinationDataType.equalsIgnoreCase("boolean")) {
Boolean booleanDestinationValue = false;
booleanDestinationValue = booleanDestinationValue.parseBoolean(sourceValue);
} else if (destinationDataType.equalsIgnoreCase("Timestamp")) {
Timestamp timestampDestinationValue = null;
timestampDestinationValue = Timestamp.valueOf(sourceValue);
destinationFieldProperty.setPropertyDateValue(timestampDestinationValue);
}

Convert longblob data into Object
request = (Request) ByteConverter.getObject(requestMessage.getRequest());

public static byte[] getBytes(Object obj) throws java.io.IOException {
if (obj == null) {
return null;
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(bos);
oos.writeObject(obj);
oos.flush();
oos.close();
bos.close();
byte[] data = bos.toByteArray();
return data;
}
public static Object getObject(byte[] objectData) throws IOException, ClassNotFoundException {
Object object = null;

if (objectData != null) {
ObjectInputStream in = null;
ByteArrayInputStream bin = new ByteArrayInputStream(objectData);
BufferedInputStream bufin = new BufferedInputStream(bin);

in = new ObjectInputStream(bufin);
object = in.readObject();
if (in != null) {
in.close();
}
}
return object;
}

snippet : writing-objects-to-file-with-objectoutputstream

ObjectOutputStream outputStream = null;

try {

//Construct the LineNumberReader object
outputStream = new ObjectOutputStream(new FileOutputStream(filename));

Person person = new Person();
person.setFirstName("James");
person.setLastName("Ryan");
person.setAge(19);

outputStream.writeObject(person);

person = new Person();

person.setFirstName("Obi-wan");
person.setLastName("Kenobi");
person.setAge(30);

outputStream.writeObject(person);


} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} finally {
//Close the ObjectOutputStream
try {
if (outputStream != null) {
outputStream.flush();
outputStream.close();
}
} catch (IOException ex) {
ex.printStackTrace();
}
}

How to connect to FTP without any software

Steps

1. Open your browser
2. In browser type : ftp://@ (eg : ftp://dowgeneric@ftp.operative.com) Press enter
3. Enter ftp password.

You will redirect to home ftp directory.