leejeok

The Remarkable Everyday

Archive for the ‘MySQL’ Category

Remote Connect to MySQL On OpenSUSE From Different Host

Posted by leejeok on October 16, 2008

Unlike to Ubuntu 8, OpenSUSE 11 is equipped with Firewall and ON by default. You may need to grant the permission to allow remote access to the MySQL server from the client.

 

Computer à YaST (at system menu) à Security and Users à Firewall

 

At the firewall configuration panel à Allowed Services à At Service to Allow, select MySQL Remote Access à Click Add button à Click Next button.

 

Be sure to start the mysql services. If not you can go to the terminal and type this command:

sudo /etc/init.d/mysql start

 

Also, make sure the user had the privilege to access the MySQL database from different host. If not you add the user using MySQL command:

GRANT ALL PRIVILEGES ON *.* TO ‘some_user’@’%’ IDENTIFIED BY ‘some_password’ WITH GRANT OPTION;

Advertisements

Posted in MySQL, OpenSUSE | Leave a Comment »

Install Java, Tomcat and MySQL on OpenSuse 11

Posted by leejeok on October 16, 2008

We will install the JRE, Tomcat and MySQL for hosting JSP web application with database backend. The installation are simple and will be done through the YaST management tools.

 

Launch YaST

Computer à YaST (at system menu) à enter root password à Software (at YaST control center) à Software Management.

 

Install JRE

1. We will require JRE in order to run Tomcat later. If you wish to do a development using Java programming, you may install both JDK and JRE together. At the filter field, type jdk.

 

2. Select java-1_6_0-openjdk for JRE (or java-1_6_0-openjdk-devel for JDK – for development). Next, click the Install button. Click Apply to download and install.

 

 

Install Tomcat

1. Download the tomcat 5.5 from http://tomcat.apache.org/ and saved it to desktop. Here, apache-tomcat-5.5.26.tar.gz been used. Right click on the file and click on Extract Here.

 

2. Rename the folder to tomcat for easier to remember the folder name. It is good idea to move the tomcat to /usr/local directory.

 

3. Type the command sudo mv tomcat-on-your-desktop-path /usr/local

 

4. You need to tell the tomcat where the JRE has been installed and to do this you need to define the JAVA_HOME environment variable in .bashrc file.  At the terminal, type the command: gedit ~/.bashrc

 

5. At the end of the file type export JAVA_HOME=your/java/home and save the file.

 

6. Start the tomcat with the command: sh /usr/loca/tomcat/bin/startup.sh

 

7. Open the Mozilla Firefox browser and browse to http://localhost:8080 and should see the Tomcat welcome screen.

 

8. To stop the tomcat type: sh /usr/loca/tomcat/bin/shutdown.sh

 

Install MySQL

1. Launch YaST. At the filters field type mysql and select the mysql package to install.

 

2. You may prompt to insert the openSuse-DVD 11. Insert the DVD and click retry to continue.

 

3. The installation process will take place.

 

 

 

Posted in Java, JSP, MySQL, OpenSUSE, Tomcat | Leave a Comment »

Setup Java, Tomcat, MySQL, on Ubuntu Linux Machine for JSP hosting

Posted by leejeok on August 14, 2008

** If you had downloaded and installed Ubuntu 8.04 LTS Desktop Edition, you can skip the step below and goto step 8.

 

Install Ubuntu Ubuntu 8.04 LTS Server

 

  1. Download the Ubuntu Server from http://www.ubuntu.com/
  2. Burn the ISO file into CD and boot your machine from CD.
  3. Follow the installation step closely and it is easy to do it.
  4. Once install successfully, you will require to login.
  5. After login, perform the necessary updates. Make sure you have the internet connections ready. Type sudo apt-get update to do the update.

 

Install the GNOME desktop (mostly used within this tutorial)

 

  1. Type sudo apt-get  install ubuntu-desktop to download and install.
  2. Type startx to start the GNOME


** You can install KDE desktop instead of GNOME.

To install KDE, type the command sudo apt-get install kubuntu-desktop

 

Install JDK 6 and JRE (Java)

 

  1. Within the GNOME, open the terminal. Applications à Accessories à Terminal
  2. Type sudo apt-get install sun-java6-jdk
  3. After successful install, you can do some verification to find out the installed JDK and JRE version. At the terminal, type javac –version for JDK and java –version for JRE.

 

Install Apache Tomcat 5.5

 

**Before installing the Tomcat 5.5, you will need to ensure that you had installed JDK and JRE.

 

  1. Download the tomcat 5.5 from http://tomcat.apache.org/ and saved it to desktop. Here, apache-tomcat-5.5.26.tar.gz been used.
  2. Right click apache-tomcat-5.5.26.tar.gz and click on Extract Here
  3. After extract, rename the folder to tomcat for easier to remember the folder name.
  4. Is good idea to move the tomcat to /usr/local directory. Type the command sudo mv “tomcat on desktop” to “/usr/local”

 

You need to tell the tomcat where the JDK has been installed and to do this you need to define the JAVA_HOME environment variable in .bashrc file.

 

  1. You are about to edit the .bashrc file. Please backup the file first before make any changes. The .bashrc is a hidden file and is located in your home directory.
  2. Open the terminal and type the command gedit ~/.bashrc (GNOME) or kate ~/.bashrc (KDE)
  3. At the end of the file type export JAVA_HOME=/usr/lib/jvm/java-6-sun-1.6.0.06 and save the file.

 

Start and Stop tomcat

 

  1. At terminal, type sh /usr/local/tomcat/bin/startup.sh
  2. You can verify the success installation by open the Mozilla Firefox browser and browse to http://localhost:8080
  3. To shutdown tomcat – at terminal type sh /usr/local/tomcat/bin/shutdown.sh

 

Install the MySQL Server 5.0

 

  1. At terminal, type sudo apt-get install mysql-server

 

Install MySQL Query Browser.

 

  1. At terminal, type sudo apt-get install mysql-query-browser

 

Install MySQL Administrator

 

  1. Open the terminal and type sudo apt-get install mysql-admin

Here are the screen capture

 

Posted in Java, JSP, MySQL, Tomcat, Ubuntu | 4 Comments »

Simple JNDI Accessing MySQL Datasource

Posted by leejeok on May 27, 2008

Base on the previous post, I have used the struts-config.xml to define the datasource to access to the MySQL database. The datasource class is code within the Action class (controller layer) and map to the struts-config.xml.

 

In order to improve the programming pratice, the datasource can be created via JNDI and the java code should be sit within the model layer. As a result, business logic will separate from the controller layer and making the Action class without knowing any relation with business class at all.

 

I am using Apache Tomcat 5.x and so, I will define the datasource resource via JNDI within the context.xml.

 

<Context path=”/bookstore”>

<ResourceLink global=”jdbc/bookstoreDB” name=”jdbc/bookstoreDB” type=”javax.sql.DataSource”/>

</Context>

 

The java code to make the database connection accessing the JNDI would be look like this:

 

    Connection cn(){

        DataSource ds = null;

       

        try {

            Context ctx = new InitialContext();

            if ( ctx == null ) {

                throw new RuntimeException(“JNDI Context could not be found.”);

            }

            ds = (DataSource)ctx.lookup(“java:comp/env/jdbc/bookstorDB”);

           

            if ( ds == null ) {

                throw new RuntimeException(“DataSource could not be found.”);

            }

            return ds.getConnection();

            // Handle any JNDI errors

        } catch (NamingException ne) {

            throw new RuntimeException(“A JNDI error occured. ” + ne.getMessage());

           

        }catch(Exception e){

            System.err.println(“Boom – No Context”);

        }

        return null;

}

 

Alternative, you can use JSP Standard Tag Library (JSTL) without embedding any Java code. I am using JSTL SQL tag here and may look like this:

 

<%@ taglib prefix=”sql” uri=”http://java.sun.com/jsp/jstl/sql&#8221; %>

<%@ taglib prefix=”c” uri=”http://java.sun.com/jsp/jstl/core&#8221; %>

 

<sql:setDataSource dataSource=”jdbc/bookstoreDB” />

 

<sql:query var=”qryBooks” >

    SELECT bookID, bookTitle, bookAuthor, bookPublisher FROM book

</sql:query>

 

<table>

    <c:forEach var=”row” items=”${qryBooks.rows}”>

        <tr>

            <td>${row.bookID}</td>

            <td>${row.bookTitle}</td>

            <td>${row.bookAuthor}</td>

            <td>${row.bookPublisher}</td>

        </tr>

    </c:forEach>

</table>

Posted in JDBC, JSTL, MySQL, Struts | 1 Comment »

Java MySQL – Mass Update Using Batch Updating

Posted by leejeok on May 21, 2008

//Some sample of the code to perform the mass update of database.

 

//The ideas behind of this code is to

//improve the database performance

//by reduce the cost of query executions.

//This is refer to the INSERT, UPDATE and DELETE

//of any rows within the database.

//

//Some developer may execute the query statement within

//the WHILE or FOR loop condition.

//

//With batch updating, a set of SQL query can be writen and execute together

//to improve the performance.

 

 

import java.sql.*;

 

public class Main {

   

    public static void main(String[] args) {

        Connection conn = null;

        PreparedStatement stat = null;

       

        int result = 0;

        int totalRowUpdate = 0;

        boolean updateAll = false;

        boolean updateEmpty = false;

        boolean updateFail = false;

       

        try {

            // Load the JDBC driver

            String driver = “org.gjt.mm.mysql.Driver”;

            Class.forName(driver);

           

            // Create a connection to the database

            String serverName = “localhost”;

            String database = “navigation”;

            String url = “jdbc:mysql://” + serverName +  “/” + database;

            String username = “someroot”;

            String password = “somepassword”;

           

            conn = DriverManager.getConnection(url, username, password);

           

            //Disabled the auto commit. By default, it is always true.

            conn.setAutoCommit(false);

            

            //Create the prepared statement

            String updateSQL = “UPDATE customer SET country = ? WHERE id = ?”;

            stat = conn.prepareStatement(updateSQL);

           

            //Insert the country and hardcoded id.

            for(int i = 0; i <= 5; i++){

                stat.setString(1, “Malaysia”);

                stat.setInt(2, i);

                stat.addBatch();

            }

           

            //Execute the batch.

            int [] updateCounts = stat.executeBatch();

           

            //Validate update.

            //If the row value in the updateCounts array is 0 or greater,

            //the update was successfully executed.

            //A value of SUCCESS_NO_INFO means update was successfully executed

            //but MySQL server unable to determind the number of rows affected.

            //A value of EXECUTE_FAILED means MySQL server rejected the query for error.

            for (int i=1; i<updateCounts.length; i++) {

                if (updateCounts[i] >= 1) {

                    totalRowUpdate = totalRowUpdate + 1;

                    updateAll = true;

                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {

                    updateEmpty = true;

                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {

                    updateFail = true;

                }

            }

           

            //Error found. Rollback.

            //No error, Commit the batch

            if(updateEmpty || updateFail){

                conn.rollback();

            } else if (updateAll){

                conn.commit();

                System.out.println(“Total Row Update: “+totalRowUpdate);

            }

           

            //Once the batch is executed, it is a good pratice to clear the batch

            //before adding any new query.

            stat.clearBatch();

           

            // Throw BatchUpdateException for any queries fail.

            // Throw ClassNotFoundException for database driver error.

            // Throw SQLException for database error.

        } catch (BatchUpdateException  e) {

            System.err.println(e);

        } catch (ClassNotFoundException e) {

            System.err.println(e);

        } catch (SQLException e) {

            System.err.println(e);

        }

    }

}

Posted in Java, JDBC, MySQL | 1 Comment »

MySQL Workbench

Posted by leejeok on May 7, 2008

One of the tools which I like to use to design my database is known as MySQL Workbench. I have used it to design the Entity Relationship Diagram and export the design into PDF or PING image. Once you complete the design, you can generate the physical database which included with the forward engineering capabilities or exporting it to SQL text file. This easy tool also assists you to document the database design into text and html form.

The MySQL Workbench deliver in 2 editions.
The Standard Edition will provide you all the needs which I mentioned above.
If you looking for a simple and free tools for you visualization and design of database, or you want to export your design to PDF, PNG or SQL query, perhaps you may want to try the Community Edition – this might be the one that you looking for.

Posted in MySQL | Leave a Comment »