Examples of org.apache.tapestry.contrib.jdbc.StatementAssembly

Package org.apache.tapestry.contrib.jdbc

Examples of org.apache.tapestry.contrib.jdbc.StatementAssembly

org.apache.tapestry.contrib.jdbc.StatementAssembly
Class for creating and executing JDBC statements. Allows statements to be assembled incrementally (like a {@link StringBuffer}), but also tracks parameters, shielding the developer from the differences between constructing and using a JDBC {@link java.sql.Statement} and a JDBC {@link java.sql.PreparedStatement}. This class is somewhat skewed towards Oracle, which works more efficiently with prepared staments than simple SQL.
In addition, implements {@link #toString()} in a useful way (you can see theSQL and parameters), which is invaluable when debugging.
{@link #addParameter(int)} (and all overloaded versions of it for scalar types)adds a "?" to the statement and records the parameter value.
{@link #addParameter(Integer)} (and all overloaded version of it for wrappertypes) does the same ... unless the value is null, in which case "NULL" is inserted into the statement.
{@link #addParameterList(int[],String)} (and all overloaded versions of it)simply invokes the appropriate {@link #addParameter(int)}, adding the separator in between parameters. @version $Id: StatementAssembly.java 243791 2004-02-19 17:38:13Z hlship $ @author Howard Lewis Ship


        try
        {
            connection = getConnection();


            StatementAssembly assembly = buildBasePersonQuery();
            assembly.newLine("WHERE ");
            assembly.add("PERSON_ID = ");
            assembly.addParameter(personId);
            assembly.newLine("ORDER BY LAST_NAME, FIRST_NAME");


            statement = assembly.createStatement(connection);


            set = statement.executeQuery();


            if (!set.next())
                throw new FinderException("Person #" + personId

View Full Code Here


        try
        {
            connection = getConnection();


            StatementAssembly assembly = buildBaseBookQuery();
            assembly.addSep(" AND ");
            assembly.add("book.BOOK_ID = ");
            assembly.addParameter(bookId);


            statement = assembly.createStatement(connection);


            set = statement.executeQuery();


            if (!set.next())
                throw new FinderException("Book " + bookId + " does not exist.");

View Full Code Here

     */


    private void returnBooksFromDeletedPersons(Integer[] deletedPersonIds)
        throws RemoveException
    {
        StatementAssembly assembly = new StatementAssembly();


        assembly.add("UPDATE BOOK");
        assembly.newLine("SET HOLDER_ID = OWNER_ID");
        assembly.newLine("WHERE HOLDER_ID IN (");
        assembly.addParameterList(deletedPersonIds, ", ");
        assembly.add(")");


        executeUpdate(assembly);
    }

View Full Code Here


    private void moveBooksFromDeletedPersons(Integer[] deletedPersonIds,
            Integer adminId)
        throws RemoveException
    {
        StatementAssembly assembly = new StatementAssembly();


        assembly.add("UPDATE BOOK");
        assembly.newLine("SET OWNER_ID = ");
        assembly.addParameter(adminId);
        assembly.newLine("WHERE OWNER_ID IN (");
        assembly.addParameterList(deletedPersonIds, ", ");
        assembly.add(")");


        executeUpdate(assembly);


    }

View Full Code Here

        return firstName + " " + lastName;
    }


    protected StatementAssembly buildBaseBookQuery()
    {
        StatementAssembly result = new StatementAssembly();


        result.newLine("SELECT ");
        result.addList(BOOK_SELECT_COLUMNS, ", ");


        result.newLine("FROM ");
        result.addList(BOOK_ALIAS_COLUMNS, ", ");


        result.newLine("WHERE ");
        result.addList(BOOK_JOINS, " AND ");


        return result;
    }

View Full Code Here

        String title = parameters.getTitle();
        String author = parameters.getAuthor();
        Integer publisherId = parameters.getPublisherId();
        Integer ownerId = parameters.getOwnerId();


        StatementAssembly assembly = buildBaseBookQuery();


        addSubstringSearch(assembly, "book.TITLE", title);
        addSubstringSearch(assembly, "book.AUTHOR", author);


        // Hide books that are not visible to the master query.


        assembly.addSep(" AND ");
        assembly.add("book.HIDDEN = 0");


        if (publisherId != null)
        {
            assembly.addSep(" AND ");
            assembly.add("book.PUBLISHER_ID = ");
            assembly.addParameter(publisherId);
        }


        if (ownerId != null)
        {
            assembly.addSep(" AND ");
            assembly.add("book.OWNER_ID = ");
            assembly.addParameter(ownerId);
        }


        addSortOrdering(assembly, ordering);


        return assembly.createStatement(connection);
    }

View Full Code Here

    }


    private IStatement buildPersonQuery(Connection connection, String personColumn,
            Integer personId, SortOrdering sortOrdering) throws SQLException
    {
        StatementAssembly assembly = buildBaseBookQuery();


        assembly.addSep(" AND ");
        assembly.add(personColumn);
        assembly.add(" = ");
        assembly.addParameter(personId);


        addSortOrdering(assembly, sortOrdering);


        return assembly.createStatement(connection);
    }

View Full Code Here

    }


    private IStatement buildBorrowerQuery(Connection connection, Integer borrowerId,
            SortOrdering sortOrdering) throws SQLException
    {
        StatementAssembly assembly = buildBaseBookQuery();


        // Get books held by the borrower but not owned by the borrower.


        assembly.addSep(" AND ");
        assembly.add("book.HOLDER_ID = ");
        assembly.addParameter(borrowerId);
        assembly.addSep(" AND ");
        assembly.add("book.HOLDER_ID <> book.OWNER_ID");


        addSortOrdering(assembly, sortOrdering);


        return assembly.createStatement(connection);
    }

View Full Code Here

        }
    }


    protected StatementAssembly buildBasePersonQuery()
    {
        StatementAssembly result;


        result = new StatementAssembly();


        result.newLine("SELECT PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL, ");
        result.newLine("   LOCKED_OUT, ADMIN, LAST_ACCESS");
        result.newLine("FROM PERSON");


        return result;
    }

View Full Code Here


        try
        {
            connection = getConnection();


            StatementAssembly assembly = new StatementAssembly();
            assembly.newLine("SELECT PERSON_ID");
            assembly.newLine("FROM PERSON");
            assembly.newLine("WHERE ");


            assembly.add("LOWER(EMAIL) = ");
            assembly.addParameter(trimmedEmail);


            statement = assembly.createStatement(connection);
            set = statement.executeQuery();


            if (set.next())
                throw new RegistrationException(
                        "Email address is already in use by another user.");


            close(null, statement, set);


            assembly = new StatementAssembly();
            assembly.newLine("SELECT PERSON_ID");
            assembly.newLine("FROM PERSON");
            assembly.newLine("WHERE ");


            assembly.add("LOWER(FIRST_NAME) = ");
            assembly.addParameter(trimmedFirstName);
            assembly.addSep(" AND ");
            assembly.add("LOWER(LAST_NAME) = ");
            assembly.addParameter(trimmedLastName);


            statement = assembly.createStatement(connection);
            set = statement.executeQuery();


            if (set.next())
                throw new RegistrationException(
                        "Name provided is already in use by another user.");

View Full Code Here

0 1 2 3 4 5 6 7 8

TOP

Related Classes of org.apache.tapestry.contrib.jdbc.StatementAssembly

org.apache.tapestry.vlib.ejb.impl.BookQueryBean

org.apache.tapestry.vlib.ejb.impl.OperationsBean

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.