Posted by: tonyteaching | July 14, 2009

to Display Data MySQL using JAVA

Remember:

to enable Java working with a database, we need to have Java DataBase Connectivity (JDBC) which can be downloaded at  http://dev.mysql.com/downloads/connector/j/5.1.html

specifically we need to copy/put  “mysql-connector-java-5.1.7-bin.jar ” file into \jre\lib\ext\ or alternatively define the location of the file into CLASSPATH of the system

—–

Now coming to script,What statements we need in our Java program to connect to dabatase and to query it?

1. Firstly, we need to import the Java packet for working with database

import java.sql.*;

2. Define the name of the class (the name of the file), for example “JavaDatabase” ..so this file will be saved as “JavaDatabase.java”

public class JavaDatabase {


}

3. Starting the main method and inside the block { ….  } is all statement for connecting and working/query with database:

public static void main(String[] args)

{

try block for sql exceptions

try
{

3.a. create the driver for working with database first

Class.forName(“com.mysql.jdbc.Driver”).newInstance();

3.b. detail of username and password for accessing the database

String username = “username”;
String password = “password”;

3.c. connect to our database, can be in the same server (localhost) or other URL

String dbURL = “jdbc:mysql://localhost/obm?user=”
+ username + “&password=” + password;

java.sql.Connection myConnection =
DriverManager.getConnection(dbURL);

3.d. create statement handle for executing queries

Statement stat = myConnection.createStatement();

3.e. Typing the query, for example query to select all of the data from a table:

String selectQuery = “Select * from namatable”;

3.f. Get the result and assign a variable for the result

ResultSet results = stat.executeQuery(selectQuery);

3.g. Output/display the results, for example in here I want to display the data in field of “mobilenumber”, “D”, “A”, “K”, “T”, and “N”.

while (results.next())
{
System.out.println(“mobile number: ” + results.getString(“mobilenumber”));
System.out.println(“Darwin: ” + results.getString(“D”));
System.out.println(“Alice Springs: ” + results.getString(“A”));
System.out.println(“Katherine: ” + results.getString(“K”));
System.out.println(“Tennant Creek: ” + results.getString(“T”));
System.out.println(“Katherine: ” + results.getString(“K”));
System.out.println(“———————- “);
}

3.h. close all connections

results.close();
stat.close();
myConnection.close();

}
catch( Exception E )
{ System.out.println( E.getMessage() );    }
}

}

——————————————————————-

Complete the script of “JavaDatabase.java” :

import java.sql.*;

public class JavaDatabase {

public static void main(String[] args)

{
//try block for sql exceptions
try
{
//create driver – ALTER TO SUIT YOUR DRIVER/ DBMS

Class.forName(“com.mysql.jdbc.Driver”).newInstance();

//database connection code – ENTER YOUR DETAILS

String username = “username”;
String password = “password”;

//URL – ALTER TO CONNECT TO YOUR DATABASE

String dbURL = “jdbc:mysql://localhost/obm?user=”
+ username + “&password=” + password;

//create the connection – ALTER FOR YOUR DBMS
java.sql.Connection myConnection =
DriverManager.getConnection(dbURL);

//create statement handle for executing queries
Statement stat = myConnection.createStatement();

//query to select all of the data from a table
String selectQuery = “Select * from clients”;

//get the results
ResultSet results = stat.executeQuery(selectQuery);

//output the results
while (results.next())
{
//example – column is called ‘firstname’
System.out.println(“mobile number: ” +
results.getString(“mobilenumber”));
System.out.println(“Darwin: ” +
results.getString(“D”));
System.out.println(“Alice Springs: ” +
results.getString(“A”));

System.out.println(“Katherine: ” +
results.getString(“K”));
System.out.println(“Tennant Creek: ” +
results.getString(“T”));
System.out.println(“Katherine: ” +
results.getString(“K”));
System.out.println(“———————- “);

}

results.close();
stat.close();
myConnection.close();

}
catch( Exception E )
{ System.out.println( E.getMessage() );    }
}

}

———————————————————–

Alternatively we can do Updating or Insert Data:

You can use the statement handle (the ’stat’ variable in the code above) to execute other SQL statements on your data, such as updates and inserts. For updates, use the syntax:

//example update statement
String updateStatement =
	"Update MyTable set SomeColumn=192 where OtherColumn=12";
	//int return indicates success or failure
int updateSuccess = stat.executeUpdate(updateStatement);
System.out.println("success? "+updateSuccess);

For inserts the syntax is the same:

//example insert statement
String insertStatement =
	"Insert into MyTable (col1, col2, col3)
	values (12, 'bla', 127)";
	//int return indicates success or failure
int insertSuccess = stat.executeUpdate(insertStatement);
System.out.println("success? "+insertSuccess);

You can also use the Result Set to get information about metadata:

//get the metadata from a ResultSet
ResultSetMetaData mData = results.getMetaData();

The ResultSetMetaData object provides methods to ascertain details of the data such as table names within the database, column names within tables etc.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: