MySQL database is a very popular and highly recommended for web programming and for programming. Despite being free, is very stable and very practical to work with different applications. In this case, the idea is to make a very basic example working for a SELECT query the database MySQL Java yourself.

Java allows a connection to MySQL calls with a few simple lines. To create the connection between the database and we will need the java conectot that offers mysql, can be downloaded here. Assuming you already have your connection in the library caperta your java project, and then installed MySLQ started to work.

1. Establishing the connection
2. Select Query
3. Extract information ResulSet
4. Other Select

Establishing the connection

When you install mysql, during installation we have compiled a series of data that are of utmost importance. talking about the username and password for the database, besides that, we must also have the name of the database with which we work. In summary we need:

1. User
2. Password
3. name of the database
4. host (if the database is in our pc then the hostname is localhost)
5. port (if mysql is installed in the conventional way then the port number 3306 unless you have changed manually)

Whether we make the connection in a separate class the main one, the connection is created in the same way as follows:

Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/wc_data_base", "user", "password"); 

If you notice, the getConnection method that takes a String containing the data mentioned above and turn it into a valid URL to create the connection. If all goes well then you only have to prepare and execute the query.

Select Query

Let’s start with creating a method called wc_select_query is going to have to create a select statement to the database. It is the part where we enter the most serious, and there are certain aspects to be clear before you run SQL commands from java. Assuming you already have basic knowledge in SQL query to differentiate when a query only extracts information or if a query modifies the database, depending on these rules we will use two different methods, to better illustrate:

• SELECT: Use the executeQuery method in java to run the query, because it does not alter the database, only extracts information
• INSERT, UPDATE and DELETE: Use executeUpdate method in java to run the query, because these consultations if altered information in the database.

By now a little confusing, but henceforth will be more clear. Before continuing let’s assume that our database has the tables countries and person who have the information that we are going to work.

person table

country table:

The tables are very basic, just a detail, note that there is a foreign key from the table Person (country) to the country table (country_id).

So, our method is to wc_select_query order to generate the query “select all persons are called danny thomas.” Prepare the query:

Statement query = (Statement) c.createStatement();
ResultSet resultado = query.executeQuery("select * from person where name = 'danny thomas'");

C with the object that created the Statement object that will be responsible for executing the query, inside the SQL statement. The result of this query stored in an object of type ResulSet, this object behaves like a virtual table that contains the information out of the ruling of the select, ie the value of result is:

To make an overview of how the method was wc_select_query have something like this:

public void wc_select_query() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_data_base", "user", "password");
Statement query = (Statement) c.createStatement();
ResultSet result = query.executeQuery("select * from person where name = 'danny thomas'");

} catch (SQLException ex) {
System.out.println("There was an error with the SQL commands");
} catch (ClassNotFoundException ex2){
System.out.println("Could not load the driver");
}
}

Extracting information ResulSet

Using the above method and now only need to extract the information stored outcome variable. This variable is the name of each column as the result showed that the query also has a number of rows in each record. Then, we proceed to extract the data:

int column_person_id = result.findColumn("person_id");
int column_name = result.findColumn("name");
int column_age = result.findColumn("age");
int column_country = result.findColumn("country");

boolean full = result.next();

while (full){
int id = result.getInt(column_student_id);
String name = result.getString(column_name);
int age = result.getInt(column_age);
int country = result.getInt(column_country);
full = result.next();

System.out.println("id: " + id + "n" +
"name: " + name + "n" +
"age: " + age + "n"+
"country: " + country);
}

The code above is very different methods, their functionality depends on the type of information contained in each column.

• findColumn (column_name): search the index of the column. By knowing the index of the column know that we can extract data accurately. For example, column 1 is the column person_id, which we already know that this column will only have numeric data types. Similarly for column 2, which is responsible for having the names of people, in other words, data String
• next (): This method tells us if the result (ResulSet) is empty or not, ie, it returns false. If it is empty is because the SELECT query did not generate any results, because no records meet all conditions. On the other hand, has returned true record, the variables used by iterating the cycle to go between each record
• getInt (column_index), GetString (column_index): These methods, and extract information from the result, depending on the type of data that will be the specific method call. For the example of the Person table only has two types of data, numeric and string, but the API ResulSet gives us if we use other methods such as double data types, date, long, etc.

Finally, it would be complete method …

public void wc_select_query() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_data_base", "user", "password");
Statement query = (Statement) c.createStatement();
ResultSet result = query.executeQuery("select * from person where name = 'danny thomas'");

int column_person_id = result.findColumn("person_id");
int column_name = result.findColumn("name");
int column_age = result.findColumn("age");
int column_country = result.findColumn("country");

boolean full = result.next();

while (full){
int id = result.getInt(column_student_id);
String name = result.getString(column_name);
int age = result.getInt(column_age);
int country = result.getInt(column_country);
full = result.next();

System.out.println("id: " + id + "n" +
"name: " + name + "n" +
"age: " + age + "n"+
"country: " + country);
}

} catch (SQLException ex) {
System.out.println("There was an error with the SQL commands");
} catch (ClassNotFoundException ex2){
System.out.println("Could not load the driver");
}
}

The output sample would be:

id: 4
Name: danny thomas
Age: 25
country: 3

Another way to make the shortest method is to remove the indexes of the columns, and metods GetString and getInt is pass the name or the index of the column directly, such as:

//like this:
String name = result.getString("name");

//or like this:
int edad = result.getInt(4);

Other Select

Sometimes when we work with several tables, most often avoids a relationship between them, this natural relationship is through foreign keys (as recommended), ie in order to have a better design of the database .
Using the previous example of the tables of people and countries we can see that their relationship is over and the country country_id respectively. So supogamos we all want the names of people and the country to which they belong. Here the trap is that the column name of Pserson and the column name the countries are called equal, then we as specified everything …

Now, here is a conflict of names of columns, how to differentiate ResulSet not refer to any column by name when you speak of columns with two columns has the same name, ie name. In this case we must get the column data by column number. The count of columns in the ResulSet starts from 1 not from 0 as is usuarial, then, to refer to names of persons and names of countries would be …

String name_person = result.getString(1); // 1 = first column
String name_country = result.getString(2); // 2 = second column

Happy Programming!!!

Share on Facebook

This entry was posted on Wednesday, August 12th, 2009 and is filed under Uncategorized.