/*
* Copyright (c) 2011, Rickard Öberg. All Rights Reserved.
* Copyright (c) 2012, Paul Merlin. All Rights Reserved.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
*/
package org.qi4j.library.sql.common;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.qi4j.functional.Visitor;
import org.qi4j.io.Input;
import org.qi4j.io.Output;
import org.qi4j.io.Receiver;
import org.qi4j.io.Sender;
/**
* Utility methods for performing SQL calls wrapping a given DataSource.
*/
public class Databases
{
DataSource source;
/**
* Create a new Databases wrapper for a given DataSource.
*/
public Databases( DataSource source )
{
this.source = source;
}
/**
* Perform SQL update statement.
*/
public int update( String sql )
throws SQLException
{
Connection connection = null;
PreparedStatement stmt = null;
try {
connection = source.getConnection();
stmt = connection.prepareStatement( sql );
return stmt.executeUpdate();
} finally {
SQLUtil.closeQuietly( stmt );
SQLUtil.closeQuietly( connection );
}
}
/**
* Perform SQL update statement.
*
* If the SQL string contains ? placeholders, use the StatementVisitor to
* update the PreparedStatement with actual values.
*/
public int update( String sql, StatementVisitor visitor )
throws SQLException
{
Connection connection = null;
PreparedStatement stmt = null;
try {
connection = source.getConnection();
stmt = connection.prepareStatement( sql );
visitor.visit( stmt );
return stmt.executeUpdate();
} finally {
SQLUtil.closeQuietly( stmt );
SQLUtil.closeQuietly( connection );
}
}
/**
* Perform SQL query and let visitor handle results.
*/
public void query( String sql, ResultSetVisitor visitor )
throws SQLException
{
query( sql, null, visitor );
}
/**
* Perform SQL query and let visitor handle results.
*
* If the SQL string contains ? placeholders, use the StatementVisitor to
* update the PreparedStatement with actual values.
*/
public void query( String sql, StatementVisitor statement, ResultSetVisitor resultsetVisitor )
throws SQLException
{
Connection connection = null;
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
connection = source.getConnection();
stmt = connection.prepareStatement( sql );
if ( statement != null ) {
statement.visit( stmt );
}
resultSet = stmt.executeQuery();
while ( resultSet.next() ) {
if ( !resultsetVisitor.visit( resultSet ) ) {
return;
}
}
resultSet.close();
} finally {
SQLUtil.closeQuietly( resultSet );
SQLUtil.closeQuietly( stmt );
SQLUtil.closeQuietly( connection );
}
}
/**
* Perform SQL query and provide results as an Input.
*
* This makes it possible to combine SQL data with the I/O API.
*/
public Input<ResultSet, SQLException> query( final String sql )
{
return new Input<ResultSet, SQLException>()
{
@Override
public <ReceiverThrowableType extends Throwable> void transferTo( Output<? super ResultSet, ReceiverThrowableType> output )
throws SQLException, ReceiverThrowableType
{
output.receiveFrom( new Sender<ResultSet, SQLException>()
{
@Override
public <ReceiverThrowableType extends Throwable> void sendTo( final Receiver<? super ResultSet, ReceiverThrowableType> receiver )
throws ReceiverThrowableType, SQLException
{
query( sql, new ResultSetVisitor()
{
@Override
public boolean visit( ResultSet visited )
throws SQLException
{
try {
receiver.receive( visited );
} catch ( Throwable receiverThrowableType ) {
throw new SQLException( receiverThrowableType );
}
return true;
}
} );
}
} );
}
};
}
/**
* Perform SQL query and provide results as an Input.
*
* This makes it possible to combine SQL data with the I/O API. If the SQL
* string contains ? placeholders, use the StatementVisitor to update the
* PreparedStatement with actual values.
*/
public Input<ResultSet, SQLException> query( final String sql, final StatementVisitor visitor )
{
return new Input<ResultSet, SQLException>()
{
@Override
public <ReceiverThrowableType extends Throwable> void transferTo( Output<? super ResultSet, ReceiverThrowableType> output )
throws SQLException, ReceiverThrowableType
{
output.receiveFrom( new Sender<ResultSet, SQLException>()
{
@Override
public <ReceiverThrowableType extends Throwable> void sendTo( final Receiver<? super ResultSet, ReceiverThrowableType> receiver )
throws ReceiverThrowableType, SQLException
{
query( sql, visitor, new ResultSetVisitor()
{
@Override
public boolean visit( ResultSet visited )
throws SQLException
{
try {
receiver.receive( visited );
} catch ( Throwable receiverThrowableType ) {
throw new SQLException( receiverThrowableType );
}
return true;
}
} );
}
} );
}
};
}
/**
* Visitor for PreparedStatements.
*
* These are created when the SQL statements contain ? placeholders.
*/
public interface StatementVisitor
{
void visit( PreparedStatement preparedStatement )
throws SQLException;
}
/**
* Visitor for the ResultSet.
*
* Only access data from the given ResultSet, as the iteration will be done
* by this API.
*/
public interface ResultSetVisitor
extends Visitor<ResultSet, SQLException>
{
}
}