Tag: Java

Getting SQL Replication Info Programmatically from Java

winterWisteria Wisteria leaves in winter.

References

Details

SQL Server has a process called replication, whereby you can automatically copy records from one database to another, and keep them in-sync.  One use for this is to have one database as the transactional database, and another to report from, with the reporting database having indexes and constraints tailored more toward faster reporting (reads) than fast input.

Sometimes replication gets a little behind, and if you are running reports on the database that is a little behind, you might like to know.

The stored procedure sp_replcounters will tell you the count of transactions awaiting transfer to the distribution database (which lies between the transaction database and the destination database in a replication process).  You can call this stored procedure, but you want it to return the result set to Java.

One way to achieve this is to wrap your own stored  procedure around it, put the records into a temp table and query the temp table to return the result set.  This only works if you specify SET NOCOUNT  ON, otherwise the Java Persistence API (JPA) gets confused by the multiple queries and you don’t get the result set in your Java code.

CREATE PROCEDURE GetReplicationLatency

AS

--This is important.  Results from multiple queries within stored procedures throw JPA off unless SET NOCOUNT ON is used.
SET NOCOUNT ON

declare @db varchar(50)
set @db = db_name()


create table #tempTable ([database] varchar(50), [replicated transactions] int, [replication rate trans/sec] int,
 [replication latency (sec)] decimal(16,6), replbeginlsn varchar(40), replnextlsn varchar(40))
insert into #tempTable
exec sp_replcounters
select * from #tempTable where [database] = @db


drop table #tempTable

 

Then your Java code looks like this:

private static final String REPLICATION_LATENCY_QUERY = "exec GetReplicationLatency ";Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
List<Object[]> results = query.getResultList();

 

 

Advertisements

Hibernate Dynamic Update

ThorAndMe  That’s a pic of me with Thor.

I had a situation come up where every time a particular table was updated, an indexed view was also getting updated.  That view was intended to speed up a report, but it made the table update really horrible.  The view would only get updated if one particular column in the table was being updated.  That column never got updated through the application, because once the record was created that column’s input field got grayed out.  But Hibernate’s default behavior is to update all columns rather than to check which ones changed and only update the changed columns.

There is a performance trade-off.  Usually you probably want the default behavior.  But in this case, definitely not.

But Hibernate has an option to allow for updating only the columns that changed.  This option is called dynamic-update.  Different versions of Hibernate implement it differently.  In the example in this blog post, hibernate-annotations version 3.4.0.GA was the dependency brought in.

Because Hibernate’s org.hibernate.annotations.Entity class is not a replacement, but rather a supplementation of javax.persistence.Entity, you have to implement it like this:

import javax.persistence.Entity;
.
.
.
@Entity
@org.hibernate.annotations.Entity(dynamicUpdate = true, selectBeforeUpdate = true)
@Table(name = "blah")
public class Blah...

The selectBeforeUpdate option is not required with the dynamicUpdate.  It basically states that before doing an update, do a select to make sure you have the most recent copy of the table, then the dynamicUpdate option says only update changed columns.

UTC date/time string to Java date in local time zone

mushroom

Reference: https://www.w3.org/TR/NOTE-datetime

Uses joda-time (because yes, I still work in Java 1.6).  There is probably a simpler way.  But this works. This assumes all parts of the string are supplied.

public static Date convertUTCStringToLocalDateTime(String utcDate)
{
    DateTime utcDateTime = new DateTime(utcDate);
    Date localDateTime = utcDateTime.toDate();
    return localDateTime;
}

XML Parser

barn-owl-landing-lisa-twede

References:

Needed to parse an XML import file in a Java application.  I liked what I read about JAXB here, so this is the XML parser I used.  What I liked about it is that I didn’t need to write code to deal with the XML file line-by-line.  With JAXB, you do a little prep work to create a schema for the XML file, and at run-time the JAXB methods just need to know the location of the XML file and the location of the class definitions and it creates your Java objects.  Then you just deal with these objects in your Java code however you please. Not a lot of fuss involved.

Creating the schema

First step is to create a schema from your XML file.  I used a free online schema generator.  I just uploaded my XML file and it created an XSD schema for me.

Creating classes (called binding the schema)

The Java SDK comes with a tool for this.  You don’t need to download or install anything that you don’t already have.  The tool is xjc, and it’s in Java’s bin folder.  You can type xjc -help to learn about all the options.  But the main syntax you need is this:

xjc -d <directory to create Java class files in> -p <package name to use in the class files> <schema created in previous step with a .xsd file extension>

Java code

You need these imports:

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;
import java.io.File;

The code:

 JAXBContext jaxbContext = JAXBContext.newInstance(location of classes you created using the xjc tool);
 Unmarshaller unmarshaller = jaxbContext.createUnmarshaller();
 Object o = unmarshaller.unmarshal(new File(location and name of XML file to parse));

You can cast the output to the object type immediately if you’re only using this code for one type of XML file and you know what type of object it will always generate.  Or leave it as an Object if you want this to handle any XML file, and then later check the type of object with instanceof .

Hibernate ProjectionList and ResultTransformer to solve problem of massive queries with endless joins

Hibernate ProjectionList and ResultTransformer to solve problem of massive queries with endless joins

gardenoftwedenprofileHibernate can make query building and entity mapping easy, but if you let it take too much control you can also end up with huge queries that create a big performance drag.

If you know you only need data from a few specific columns of your table or tables, you can use a ProjectionList to target just those columns, and use a ResultTransformer to form the raw results into the sparsely populated entity.

To illustrate the point, let’s take an example of an invoice and its line items.  We have a one-to-many relationship between the invoice table and the line item table.  From the invoice table, we want the invoice_id, invoice_number, invoice_date columns.  The invoice table is linked to a vendor table, and from that table we want the vendor_name column.  From the invoice line items, we want the line_item_number, amount and description columns.

Criteria criteria = session.createCriteria(invoice.class);
criteria.createAlias("vendor", "v");
criteria.createAlias("lineItems", "li");
criteria.setProjection(Projections.projectionList()
    .add(Property.forName("id"))
    .add(Property.forName("invoiceNumber"))
    .add(Property.forName("invoiceDate"))
    .add(Property.forName("v.vendorName"))
    .add(Property.forName("li.lineItemNumber"))
    .add(Property.forName("li.amount"))
    .add(Property.forName("li.description)));

You need to specify aliases for “vendor” and “lineItems” in order to be able to specify the properties from those related entities.  If you had a situation where there wasn’t always a vendor, but you wanted information from invoices that didn’t have vendors, you would specify the alias like this:

criteria.createAlias("vendor", "v", Criteria.LEFT_JOIN);

Since you have a one-to-many relationship from invoice to lineItem, you will get a separate object from this query for every line item.  You will get the same invoice information repeated, with different line item information.  In other words, if you have an invoice that has two line items in it, the raw data returned from the query will look like this:

o = {java.lang.Object[7]}
[0] = 1234   -- the internal ID for the invoice
[1] = "LAP-12355" -- the invoice number
[2] = "12/31/2016" -- the invoice date
[3] = "George's Great Grill" -- the vendor name
[4] = "1" -- the invoice line item number
[5] = 400.23 -- the invoice line item amount
[6] = "ribs" -- the invoice line item description

And then you might have a second object returned with the same information exactly in elements 0 through 3, but with the following for elements 4, 5, and 6:

[4] = "2" -- the invoice line item number
[5] = 20.00 -- the invoice line item amount
[6] = "delivery charge" -- the invoice line item description

This is where the ResultTransformer comes in.  The ResultTransformer is a method that transforms the raw results returned from the SQL query into the entity you use in your Java code.  There are built-in ResultTransformers you can use.  For this example we will write our own, to illustrate how it works.

Specify the ResultTransformer on your criteria like this:

criteria.setResultTransformer(new ResultTransformer()
{
    @Override
    public Object transformTuple(Object[] o, String[] strings)
    {
        return transformObjectToInvoice(o);
    }

    @Override
    public List transformList(List list)
    {
        return consolidateInvoices(list);
    }
});

Then you write a private method transformObjectToInvoice that takes an Object[] and returns an Invoice.  Every invoice it will return will have one line item.  And you write a private method consolidateInvoices that takes a List and returns a List. But the incoming list will have invoices with only one line item, and the outgoing list will have fewer invoices, and the invoices will have 1 to n line items apiece.

So your  transformObjectToInvoice will look something like this:

private Invoice transformObjectToInvoice(Object[] o)
{
    Invoice invoice = new Invoice();
    invoice.setId((Integer) o[0]);
    invoice.setInvoiceNumber((String) o[1]);
    etc.
    return invoice;
}

And your consolidateInvoices will look something like this:

private List consolidateInvoices(List list)
{
    List consolidatedInvoices = new ArrayList<~>();
    Map<Integer, Invoice> invoices = new HashMap<~>();
    For (Invoice oneLineItemInvoice : list)
    {
        Invoice mapInv = invoices.get(oneLineItemInvoice.getId());
        if (mapInv != null)
        {
             mapInv.getLineItems().add(oneLineItemInvoice.getLineItems().iterator().next());
        }
        else
        {
            invoices.put(oneLineItemInvoice.getId(), oneLineItemInvoice);
        }
    }
    consolidatedInvoices.addAll(invoices.values());
    return consolidatedInvoices;
}