Making SQL Queries

This article is to summarize how do we make SQL queries using different languages and their methods. Making connection to the database will not be covered here.

In the examples, we will be querying “SELECT * FROM employees where eid =and dept =”

It is not recommended to use SQL statements without placeholders in order to reduce the risk of SQL injection.

Java

In Java, we can use JDBC, Hibernate, or some other database frameworks to interact with databases. Generally, I would prefer to use methods that allow me to insert “values” into an sql query. With JDBC, we can use PreparedStatement method. There is also createStatement method, where you insert user-supplied values into the query directly.

Basically the flow looks like this:

  1. Get a “Connection” object, with DriverManager
  2. From “Connection” object, we create a “Statement” object with sql statement.
  3. From the statement object, we generate “ResultSet”.
int employeeId = 512 // we will query for employee with id 512
int deptId = 206 // from department 206

Connection connection = DriverManager.getConnection(...); // please fill in
PreparedStatement statement = connection.prepareStatement("SELECT * FROM employees WHERE eid = ? AND dept = ?");
statement.setInt(1, employeeId); // use setString if the argument is a String
statement.setInt(2, deptId);
ResultSet rs = statement.executeQuery();
while (rs.next()) { // Iterate the result set
// ...
}

PHP

PHP has more methods to interact with database. It also depends on the module the php interpreter is built with. We can use MySQL extension, PostgreSQL extension, or ADODB or PDO as generic abstraction interfaces.

With mysqli extension

$db = new mysqli('localhost', 'user', 'password', 'world');
$statement = $db->prepare('SELECT * FROM employees WHERE eid = ? AND dept = ?');
$statement->bind_param('dd', $employeeId, $deptId); // types and variables

$employeeId = 512;
$deptId = 206;

$statement->execute();

NOTE: Why mysqli instead of mysql command? If we are using MySQL v4.1.3 and above, PHP manual recommends to use mysqli which is an improved version. Reference.

With PostgreSQL extension

$db = pg_connect("dbname=world");

$employeeId = 512;
$deptId = 206;

$result = pg_query_params($db, 'SELECT * FROM employees WHERE eid = ? AND dept = ?', array($employeeId, $deptId));

With ADODB

$db = NewADOConnection('mysql');
$db->Connect('localhost', 'user', 'password', 'world');
// Can also use $db = NewADOConnection('mysql://$user:$pwd@$server/$db?persist")
$rs = $db->execute('SELECT * FROM employees WHERE eid = ? AND dept = ?', array($employeeId, $deptId));
while($array = $rs->FetchRow()) {
//...
}

With PDO

$db = new PDO('mysql:host=$host;dbname=$dbname', $user, $pass);
$employeeId = 512;
$deptId = 206;

// We will use unamed placeholders.
$statement = $db->prepare('SELECT * FROM employees WHERE eid = ? AND dept = ?');
$statement->bindParam(1, $employeeId);
$statement->bindParam(2, $deptId);
$statement->execute();
// ...

Total Eclipse in Gentoo

Gentoo does really have Eclipse SDK in its portage. I once used it a long time ago and I gave up soon after I learned that it’s more difficult to update the portage version of Eclipse (at least to me).

Now I decided to install Eclipse Europa, and grabbed myself a Gtk version of Europa and extracted in /opt.

When when /opt/eclipse/eclipse was started, Eclipse stopped the execution, giving me the following error message.

 JVM terminated. Exit code=1/usr/bin/run-java-tool 
-Xms40m 
-Xmx256m 
-Dosgi.bundlefile.limit=100 
-jar /opt/eclipse/plugins/org.eclipse.equinox.launcher_1.0.0.v20070606.jar 
-os linux 
-ws gtk 
-arch x86 
-showsplash 
-launcher /opt/eclipse/eclipse 
-name Eclipse 
--launcher.library /opt/eclipse/plugins/org.eclipse.equinox.launcher.gtk.linux.x86_1.0.0.v20070606/eclipse_1017a.so 
-startup /opt/eclipse/plugins/org.eclipse.equinox.launcher_1.0.0.v20070606.jar 
-exitdata d5801b 
-vm /usr/bin/run-java-tool 
-vmargs 
-Xms40m 
-Xmx256m 
-Dosgi.bundlefile.limit=100 
-jar /opt/eclipse/plugins/org.eclipse.equinox.launcher_1.0.0.v20070606.jar

It seemed like vanilla Eclipse didn’t really want to work with Gentoo’s Java environment.

So I added a path to Java VM when starting Eclipse, and it began to work.
[code lang=”bash”]./eclipse -vm `java-config-2 –java`[/code]
It was because eclipse was running Gentoo’s run-java-tool instead of java, and this made $tool variable in line 15 of run-java-tool to become “run-java-tool” instead of “java”. Then it leads to wrong interpretation of JVM path.

It can also be fixed by adding
[code lang=”bash”][[ $tool = “run-java-tool” ]] && tool=”java”[/code]

(replace & with actual ampersand symbol)
after line 15,
[code lang=”bash”]tool=$(basname $O)[/code]

Or alternatively, you can directly call Equinox (Eclipse Launcher) by using the examples described in Eclipse Wiki. Here’s the sample bash script:

[code lang="bash"]#!/bin/bash# set path to eclipse folder. If local folder, use '.'; otherwise, use /path/to/eclipse/ 
eclipsehome="."; 
# get path to equinox jar inside $eclipsehome folder 
cp=$(find $eclipsehome -name "org.eclipse.equinox.launcher_*.jar" | sort | tail -1); 
# start Eclipse w/ java 
/opt/java50/bin/java -cp $cp org.eclipse.equinox.launcher.Main ...[/code]

Red Hat, Exadel and JBoss

Red Hat has merged with JBoss for some time, and now it’s aiming high again. Exadel is well-known for its Eclipse plug-in and Ajax tools, but it’s good products were mostly commercial. Now Exadel is open-sourcing its products to JBoss, in partnership with Red Hat. It also includes Exadel’s Ajax4jsf. Red Hat is planning to release LGPL’ed version of Red Hat Developer Studio by this summer. So it’s worth noting that LGPL is not as flexible as Eclipse License. I will just have to wait and decide if I have to renew my MyEclipse subscription in any time soon.

exadel partnership

The Rise Of Mustang and JUnit

Tiger (Java SE 5) has been out for a while. Mustang (Java SE 6) was started a year ago. Sun has been releasing weekly source and binary snapshots. So what does Mustang comprise? Its features were governed by JSR-270, which is an umbrella JSR for specific JSRs. Some major components include JDBC 4.0, JAP 1.1, JAXB 2.0, JAX-WS 2.0. Mustang will also feature Linux GTK Look And Feel (some screenshots here). To be able to interact with host OS’s default file type handlers, new Desktop API will now be incorporated, and the article about it is here. Best of all, Mustang is due fall of this year (yes, it’s approximately six months from now). Dolphin (Java SE 7) is scheduled to be shipped in the year 2008.

JUnit 4.0 has been released, supporting Java 1.5 annotations. We will no longer need to be tagging test classes by subclassing junit.framework.TestCase. Instead we can use @Test annotation. I guess it’s much simpler. Or maybe not 😉 This is an IBM’s early look into JUnit 4.

Better Than Flowers On Valentine’s Day

Here comes Valentine’s Gift from Yahoo! For our everyday’s HTML and AJAX needs, Yahoo! has just given us Yahoo! User Interface Library, and all the components are released under Open Source License (BSD License). Better than flowers, huh?

Yeah, we’ve got libraries and that pushes us further into endless possibilities of creating useful applications. To make best use of those tools to create applications, there’s got to be some guidelines and documentations. Again, Yahoo! provides us Design Pattern Libarary. To go further, there’s a blog for everything we need to know about those libraries and design patterns.

Feeding RSS

RSS is just an XML file that follows XML 1.0 specification. RSS simply allows us to syndicate a website contents into another website. If you want to add RSS feeds into your website, I guess we will need the read the followings:

Feed Parsers parse the feeding XML into distinctive parts without sweat. Cool, isn’t it? Currently I’m into the following parsers.

Someday, I’ll post some tutorials about those API’s and Libraries.

Sun’s Giving Away IDEs

Sun has decided to give away its famous IDEs for free with full-license. The IDEs include Java Studio Creator and Java Studio Enterprise. All you need to do to download them is to be a member of Sun Developer’s Network, which is also free to register. Sun Java Studio Creator 2 is soon to be out. It might be the one of the reasons.

Sun

But Sun isn’t the first to give free downloads. Microsoft also gives its Visual Studio Express Editions, which are free to use for 1 year.

Java GUI Builder

JBuilder is a power tool to create great java applications, as it gives tools to design GUI in an easy manner. If you are not a fan of JBuilder, and want to write applications using simple editors, you might want to have a GUI Builder for Java. Of course, you can hand-code your GUI, but does it worth giving time?

I have come across AbaGUIBuilder while in search for those designers. It’s a product of Abacus OpenSource Software Foundation.

The Abacus GUI Builder is a tool designed to aid the application developer in delivering their applications faster to the market by removing the Java layout complexity with a WYSIWYG and a simple XY layout.
Abacus Research developed the GUI Builder to aid their 50+ application programmers eliminate frustrating hours of complex layout user interface logic and thus simplifying each screen to an XY coordinate plane.

The Java GUI Builder is a WYSIWYG tool that allows the application developer to place UI Java swing components on the canvas and have it render exactly as you see on the screen.

Due to the various Java layouts, other Java GUI Builders are cumbersome and complicated, our goal was to simpifly the UI development task and help the application developer speed up UI creation.

V 1.1 a set of JDBC database aware components was added to the class pallette.