dbcommunicator.java

Symbian OS Socket任务管理器服务器源代码 可以学习一下

/*
 * Copyright 2005 Nokia. All rights reserved.
 */
package com.forum.nokia.taskmanager.database;

import java.sql.*;
import java.io.IOException;
import java.util.Properties;

import com.forum.nokia.taskmanager.*;

/**
 * This class handles all communications with the MySQL server, including
 * opening the connection. We use the same connection for all transactions
 * needed in handling a single client message.
 *  
 */
public class DBCommunicator
{
    private Connection conn = null;
    
    /**
     * Constructor loads the MySQL driver and opens a connection to the server.
     * @param databaseUrl Url to the location of the MySQL server.
     * @throws Exception Thrown if driver loading or connecting to server fails.
     */
    public DBCommunicator( String databaseUrl, String username, String password )
                                                                   throws Exception
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch( Exception e )
        { // Can be thrown when loading MySQL-driver
            System.err.println("MySQL-driver loading failed.");
            e.printStackTrace();
            throw new Exception();
        }
        System.out.println("Driver loaded.");
        
        Properties prop = new Properties();
        prop.put( "charSet", "ISO-8859-15" );
        prop.put( "characterEncoding", "ISO-8859-15" );
        prop.put( "useUnicode", "true" );
        prop.put( "user", username) ;
        prop.put( "password", password );
        
        try
        {
            conn = DriverManager.getConnection( databaseUrl, prop );
        }
        catch( SQLException e )
        {
            System.err.println("Error connecting to MySQL server");
            e.printStackTrace();
            throw new Exception();
        }
        System.out.println("Connected to mysql.");
    }
    
    /**
     * This function takes a parameter ClientPackage that houses the information
     * needed for a query to the MySQL server. It builds the query based on that
     * information. After that it creates a ServerPackage based on the results
     * received from the MySQL server, and returns the ServerPackage.
     * 
     * @param cp ClientPackage describing the client's command to the server.
     * @return A ServerPackage describing the server's response.
     * @throws IOException
     */
    public ServerPackage processClientPackage( ClientPackage cp ) throws IOException
    {
        // Authenticate the client
        String query = "SELECT id FROM user WHERE " +
                       "login = '" + cp.getUsername() + "' AND " +
                       "password = Password('" + cp.getPassword() + "')";

        ResultSet rs = null;
        ServerPackage sp = null; // the serverpackage to be returned.
        
        try
        {
            Statement statement = conn.createStatement();
            rs = statement.executeQuery( query );
            
            if( !rs.next() )
            { // Empty ResultSet -> user was not found from the database.
                return new ServerPackage("Invalid username or password");
            }
            
            // New ServerPackage created to match the request from the client.
            sp = new ServerPackage( cp.getCommandType() );
            
            // Id of the user is retrieved from the ResultSet.
            int userId = rs.getInt("id");
            
            switch( cp.getCommandType() )
            {
                case ClientPackage.FETCH:
                {
                    // Fetch the user's tasks
                    query = "SELECT id, description FROM task WHERE " +
                            "owner = " + userId + " AND " +
                            "state = 'NOT_DONE'";
                    
                    rs = statement.executeQuery( query );
                    
                    // Add the tasks to the response package
                    while( rs.next() )
                    {
                        int taskId = rs.getInt( "id" );
                        String desc = rs.getString( "description" );
                        
                        sp.addTask( new Task( taskId, desc ) );
                    }
                    break;
                }
                case ClientPackage.MARK_DONE:
                {
                    // Update the database by marking the task as 'DONE'
                    query = "UPDATE task SET state = 'DONE' WHERE id = " +
                            cp.getMessageId() + " AND owner = " + userId;
                    int rows = statement.executeUpdate( query );
                    
                    if( rows == 0 )
                    { // If zero rows were affected, the task id was not found
                      // from the database.
                        return new ServerPackage("Unknown task id");
                    }
                    break;
                }
            }
            
        }
        catch( SQLException e )
        { // If an exception was thrown, the error is relayed back to the client
          // via ServerPackage.
            System.err.println("Error while communicating with the MySQL server");
            e.printStackTrace();
            sp = new ServerPackage("MySQL server error");
        }
        catch( IllegalArgumentException e )
        { // This is thrown by ServerPackage if we're trying to add tasks to a
          // "mark task done" response message.
            System.err.println(e.getMessage());
            sp = new ServerPackage("Server internal error");
        }
        
        return sp;
    }
    
    /**
     * Function closes the connection to the database.
     * 
     */
    public void closeDBConnection()
    {
        try
        {
            conn.close();
        }
        catch( SQLException e )
        {
            System.err.println("Error while trying to cut connection to database.");
            e.printStackTrace();
        }
    }
}