Firstly you need to make a reference to jtds-1.2.5.jar in the Referenced Libraries

main class:


import java.sql.*;

public class main {

	public static void main(String[] args) {		
		ConnectMSSQLServer connServer = new ConnectMSSQLServer();
		 connServer.dbConnect("jdbc:jtds:sqlserver://localhost:1433/SQLEXPRESS2008;databaseName=mandatoryDB;", 
				 "sa", "password");
	}

}



ConnectMSSQLServer class:

import java.sql.*;

public class ConnectMSSQLServer {
	
	public void dbConnect(String db_connect_string, String db_userid, String db_password)
			 {
			 try {
			 Class.forName("net.sourceforge.jtds.jdbc.Driver");
			 Connection conn = DriverManager.getConnection(db_connect_string,
			 db_userid, db_password);
			 System.out.println("connected");
			 Statement statement = conn.createStatement();
			 String queryString = "select * from Readings where readingsid=17998";
			 ResultSet rs = statement.executeQuery(queryString);
			 while (rs.next()) {
			 System.out.println(rs.getString(3));
			 }
			 } catch (Exception e) {
			 e.printStackTrace();
			 }

	    }
	}

                        

                            Notice:

You need to make a reference to following jar:
sqljdbc4.jar
eclipselink.jar
javax.persistence_2.0.0.v201002051058.jar
ojdbc14.jar

you also need to set up a system DNS, i call it mandatoryDB

finally you need to make sure you have set the db SQL Server Express correctly:



STEP 1:        
 Configuration Manager (Start > All Programs > Microsoft SQL Server 2005 > Configurations Tools > SQL Server Configuration Manager):
STEP 2:
            - In 2005 Network Configuration > Protocols for SQLEXPRESS is TCP/IP normally disabled. It needs to be enabled! 
STEP 3:
            - Rightclick again on TCP/IP and choose properties > IP Adresses. Do to the bottom and delete what is written in TCP Dynamic Ports. Set TCP Port to 1433.
STEP 4:
            - Restart SQL Server Express by choosing: SQL Server 2005 Services > SQLServer (SQLEXPRESS) > Restart.


AND

you need to setup the DSN...
please google "How to create a System DSN for Microsoft SQL Server"
or use this guide:
http://www.truthsolutions.com/sql/odbc/creating_a_new_odbc_dsn.htm



Customer class:

package jpa;

import java.util.ArrayList;
import java.util.Collection;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class CustomerJ {

	@Id
	@Column(length = 25)
	private int id;
	@Column(length = 25)
	private String name;
	@Column(length = 25)
	private String phone;
	@Column(length = 25)
	private String address;
	@Column(length = 25)
	private boolean isDeleted;
	
	@OneToMany
	private Collection<MeterJ> meters;
	
	public CustomerJ() {
	}
	
	public CustomerJ(int id, String name, String phone, String address, boolean isDeleted) {
		this.id = id;
		this.name = name;
		this.phone = phone;
		this.address = address;
		this.isDeleted = isDeleted;
		meters = new ArrayList<MeterJ>();
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public boolean isDeleted() {
		return isDeleted;
	}

	public void setDeleted(boolean isDeleted) {
		this.isDeleted = isDeleted;
	}
	
	@Override
	public String toString() {
		return id + " " + name + " " + phone + " " + address + " " + isDeleted;// + " [" + meters + "]";
	}
	
	public void addMeter(MeterJ meter) {
		if (!meters.contains(meter)){
			meters.add(meter);
		}
	}

	public void removeMeter(MeterJ meter) {
		meters.remove(meter);
	}

	public Collection<MeterJ> getMeters() {
		return meters;
	}
}










Meter class:



package jpa;

import java.util.ArrayList;
import java.util.Collection;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class MeterJ {

	@Id
	@Column(length = 25)
	private int id;
	@Column(length = 25)
	private String type;
	@Column(length = 25)
	private String location;
	@Column(length = 25)
	private boolean isDeleted;
	
	@OneToMany
	private Collection<ReadingJ> readings;
	
	public MeterJ() {
	}

	public MeterJ(int id, String type, String location, boolean isDeleted) {
		this.id = id;
		this.type = type;
		this.location = location;
		this.isDeleted = isDeleted;
		readings = new ArrayList<ReadingJ>();
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getLocation() {
		return location;
	}

	public void setLocation(String location) {
		this.location = location;
	}

	public boolean isDeleted() {
		return isDeleted;
	}

	public void setDeleted(boolean isDeleted) {
		this.isDeleted = isDeleted;
	}
	
	@Override
	public String toString() {
		return id + " " + type + " " + location + " " + isDeleted;// + " \n[" + readings + "]\n";
	}
	
	public void addReading(ReadingJ meter) {
		if (!readings.contains(meter)){
			readings.add(meter);
		}
	}

	public void removeReading(ReadingJ reading) {
		readings.remove(reading);
	}

	public Collection<ReadingJ> getReadings() {
		return readings;
	}
}











Readings class:

package jpa;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
public class ReadingJ {

	@Id
	private int id;
	@Temporal(TemporalType.DATE)
	private Date readTime;
	@Column(length = 25)
	private String readBy;
	@Column(length = 25)
	private int kwh;
	@Column(length = 25)
	private boolean isDeleted;
	
	public ReadingJ() {
	}

	public ReadingJ(int id, Date readTime, String readBy, int kwh,
			boolean isDeleted) {
		super();
		this.id = id;
		this.readTime = readTime;
		this.readBy = readBy;
		this.kwh = kwh;
		this.isDeleted = isDeleted;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public Date getReadTime() {
		return readTime;
	}

	public void setReadTime(Date readTime) {
		this.readTime = readTime;
	}

	public String getReadBy() {
		return readBy;
	}

	public void setReadBy(String readBy) {
		this.readBy = readBy;
	}

	public int getKwh() {
		return kwh;
	}

	public void setKwh(int kwh) {
		this.kwh = kwh;
	}

	public boolean isDeleted() {
		return isDeleted;
	}

	public void setDeleted(boolean isDeleted) {
		this.isDeleted = isDeleted;
	}
	
	@Override
	public String toString() {
		return id + " " + readTime + " " + readBy + " " + kwh + " " + isDeleted;
	}
}









main class:


package jpa;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Collection;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

public class Test {

	@SuppressWarnings("unchecked")
	public static void main(String[] args) {
		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory("MandatoryAssignment");
		EntityManager em = emf.createEntityManager();

		ReadingJ r1 = new ReadingJ(0, Date.valueOf("2010-05-24"), "company",
				365, false);
		ReadingJ r2 = new ReadingJ(1, Date.valueOf("2011-05-27"), "company",
				800, false);

		MeterJ m3 = new MeterJ(2, "AB12CD34", "behind the blue door", false);
		m3.addReading(r1);
		m3.addReading(r2);

		ReadingJ r3 = new ReadingJ(2, Date.valueOf("2010-05-24"), "company",
				1000, false);
		ReadingJ r4 = new ReadingJ(3, Date.valueOf("2011-05-27"), "company",
				1365, false);

		MeterJ m4 = new MeterJ(3, "AB12CD66", "behind the blue door", false);
		m4.addReading(r3);
		m4.addReading(r4);

		CustomerJ c2 = new CustomerJ(1, "Java", "+4587654321",
				"Virtual reality", false);
		c2.addMeter(m3);
		c2.addMeter(m4);

		em.getTransaction().begin();

		em.persist(c2);
		em.persist(m3);
		em.persist(r1);
		em.persist(r2);
		em.persist(m4);
		em.persist(r3);
		em.persist(r4);

		em.getTransaction().commit();

		// get some data from the SQL database
		getReadingsSQL();

		// printing out the Customer with all the Meters and Readings
		System.out.println("##################");
		// print all the orders
		Collection<CustomerJ> customers = ((Query) em
				.createQuery("SELECT c FROM CustomerJ c")).getResultList();
		for (CustomerJ c : customers) {
			System.out.println("Found Customers: " + c);
			int sumKWH = 0;
			for (MeterJ m : c.getMeters()) {
				System.out.println("##########\nMeter\n" + m + "\n");
				int maxKWH = 0;
				for (ReadingJ r : m.getReadings()) {
					System.out.println("    Reading\n    " + r);
					maxKWH = ((r.getKwh() > maxKWH) ? r.getKwh() : 0);
				}
				sumKWH += maxKWH;
				System.out.println("\nMAXKWH: " + maxKWH + "\n##########\n");
			}
			System.out.println("Total KW Consumption: " + sumKWH);
		}

		System.out.println("##################");

		// close the EM and EMF when done
		em.close();
		emf.close();
	}

	private static void getReadingsSQL() {
		try {
			Connection myConnection;
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			myConnection = DriverManager.getConnection("jdbc:odbc:mandatoryDB",
					"sa", "password");
			Statement stmt = myConnection.createStatement();
			ResultSet res = stmt.executeQuery("select * from Readings");
			while (res.next()) {
				System.out.println(res.getString(3) );
//						+ "    " + res.getString(2)
//						+ "    " + res.getString(3) + "    " + res.getString(4)
//						+ "    " + res.getString(5));
			}
			if (res != null)
				res.close();
			if (stmt != null)
				stmt.close();
			if (myConnection != null)
				myConnection.close();
		} catch (Exception e) {
			System.err.println("error:  " + e.getMessage());
		}

	}
}













Test connection class:

package ma;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {

	/**
	 * (import the sqljdbc4.jar and make the correct DNS connection to the database)
	 */
	public static void main(String[] args) {
		try {
			Connection myConnection;
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			myConnection = DriverManager.getConnection("jdbc:odbc:mandatoryDB","sa","password");
			Statement stmt = myConnection.createStatement();
			ResultSet res=stmt.executeQuery("select * from Customer");
			while (res.next()) {
				System.out.println(res.getString(1) + "    " + res.getString(2) );
//						+ "    " + res.getString(3) + "    " + res.getString(4)
//						+ "    " + res.getString(5) + "    " + res.getString(6));
			}
			if (res != null) res.close();
			if (stmt != null) stmt.close();
			if (myConnection != null) myConnection.close();
		}
		catch (Exception e) {
			System.out.println("error:  "+e.getMessage());
		}
	}
}







Persistence.xml:


<?xml version="1.0" encoding="UTF-8"?>
<persistence 
    xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0">

    <persistence-unit name="MandatoryAssignment" transaction-type="RESOURCE_LOCAL">
        <class>jpa.CustomerJ</class>
        <class>jpa.MeterJ</class>
        <class>jpa.ReadingJ</class>
		<properties>
		
           <property name="javax.persistence.jdbc.driver" 
				  value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
				  
			<property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
			
			<property name="javax.persistence.jdbc.url" 
				  value="jdbc:sqlserver://SIMPALAPTOP-PC\SQLEXPRESS2008;databaseName=mandatoryDB;"/>
			<property name="javax.persistence.jdbc.user" value="sa"/>
			<property name="javax.persistence.jdbc.password" value="password"/>
			
			<!-- enable this property to see SQL and other logging -->
            <!--property name="eclipselink.logging.level" value="FINE"/ -->

            <!-- EclipseLink should create the database schema automatically -->
			<!--property name="eclipselink.ddl-generation" value="drop-and-create-tables"/-->
			<!--property name="eclipselink.ddl-generation.output-mode" value="database"/-->
			
        </properties>
    </persistence-unit>
</persistence>





HAPPY CODING...

                        

                            
public class houseobj { 

int houseno;
String ts; 

public houseobj(int h,String t) {
houseno=h;
ts=t;
}

}









// EXAMPLE 1





import java.sql.*;
import java.util.*;
import java.io.*;
public class Cottage1 {

	/**
	 * @param args
	 */ 
	public static void main(String[] args) {
		try {
			System.out.println("Welcome to this holiday cottage renting system");
			System.out.println("Which week do you want?");
			BufferedReader inLine = new BufferedReader(new InputStreamReader(System.in));
			String week=inLine.readLine();
			Connection myConnection;
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			myConnection = DriverManager.getConnection("jdbc:odbc:oeksdb","sa","torben07");
			Statement stmt = myConnection.createStatement();
			myConnection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);  // <--
			myConnection.setAutoCommit(false);  // <--
			String sql = "select c.houseno, priceaweek, area from cottage c,period p where c.houseno=p.houseno and rented = '0' and weekno = " + week;
			ResultSet res=stmt.executeQuery(sql);
			System.out.println("We can offer the following cottages in week " + week);
			System.out.println("Houseno:  Price for a week:    Area:");
			HashSet<Integer> housenombers = new HashSet<Integer>();
			while (res.next()) {
				int t= res.getInt(1);
				housenombers.add(t);
				System.out.println(t + "         " + res.getInt(2) + "                " + res.getString(3));
			}
			System.out.println("If you want to rent a house - put in the house nomber else put in 0 ");
			String house=inLine.readLine();
			int houseno=Integer.parseInt(house);
			if (!housenombers.contains(houseno)) {
				System.out.println("You will now exit the system");
				houseno = 0;
				myConnection.rollback();  // <--
			}
			if (houseno != 0) {
				System.out.println("Put in your name");
				String name=inLine.readLine();
				stmt.execute("update period set rented = '1',tenant = '" + name + "' where houseno = " + houseno + " and weekno = " + week );
				myConnection.commit();  // <--
				System.out.println("House no " + houseno + " is now rented for week " + week);
				}
				
		}
		
		catch (Exception e) {
			System.out.println("error:  "+e.getMessage());
		}
	}
	

}








// EXAMPLE 2






import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashSet;

public class Cottage2 {

	public static void main(String[] args) {
		try {
			System.out.println("Welcome to this holiday cottage renting system");
			System.out.println("Which week do you want?");
			BufferedReader inLine = new BufferedReader(new InputStreamReader(System.in));
			String week=inLine.readLine();
			Connection myConnection;
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			myConnection = DriverManager.getConnection("jdbc:odbc:oeksdb","sa","torben07");
			Statement stmt = myConnection.createStatement();
			myConnection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); 
			myConnection.setAutoCommit(true);  
			String sql = "select c.houseno, priceaweek, area from cottage c,period p where c.houseno=p.houseno and rented = '0' and weekno = " + week;
			ResultSet res=stmt.executeQuery(sql);
			System.out.println("We can offer the following cottages in week " + week);
			System.out.println("Houseno:  Price for a week:    Area:");
			HashSet<Integer> housenombers = new HashSet<Integer>();
			while (res.next()) {
				int t= res.getInt(1);
				housenombers.add(t);
				System.out.println(t + "         " + res.getInt(2) + "                " + res.getString(3));
			}
			System.out.println("If you want to rent a house - put in the house nomber else put in 0 ");
			String house=inLine.readLine();
			int houseno=Integer.parseInt(house);
			if (!housenombers.contains(houseno)) {
				System.out.println("You will exit the system");
				houseno = 0;
//				myConnection.rollback();  // <-- Transaction is not started, so don't do it
			}
			if (houseno != 0) {
				System.out.println("Put in your name");
				String name=inLine.readLine();
				myConnection.setAutoCommit(false); // <-- start the transaction here'
				// now check to see, if the house is still free
				sql = "select houseno from period p where houseno= " + house + " and rented = '0' and weekno = " + week;
				res=stmt.executeQuery(sql);
				if (res.next()) {
				  stmt.execute("update period set rented = '1',tenant = '" + name + "' where houseno = " + houseno + " and weekno = " + week );
				  myConnection.commit(); 
				  System.out.println("House no " + houseno + " is now rented for week " + week);
				}
				else {
					myConnection.rollback();
					System.out.println("Someone else have rented the house - we are sorry");
				}
				}
		}
		
		catch (Exception e) {
			System.out.println("error:  "+e.getMessage());
		}

	}

}





// EXAMPLE 3





import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;


public class Cottage3 {

	/**
	 * uses the rowversion type 
	 */
	public static void main(String[] args) {
		try {
			System.out.println("Welcome to this holiday cottage renting system");
			System.out.println("Which week do you want?");
			BufferedReader inLine = new BufferedReader(new InputStreamReader(System.in));
			String week=inLine.readLine();
			Connection myConnection;
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			myConnection = DriverManager.getConnection("jdbc:odbc:oeksdb","sa","torben07");
			Statement stmt = myConnection.createStatement();
			myConnection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); 
			myConnection.setAutoCommit(true);  
			String sql = "select c.houseno, priceaweek, area,ts from cottage c,periodts p where c.houseno=p.houseno and rented = '0' and weekno = " + week;
			ResultSet res=stmt.executeQuery(sql);
			System.out.println("We can offer the following cottages in week " + week);
			System.out.println("Houseno:  Price for a week:    Area:");
			HashSet<Integer> housenombers = new HashSet<Integer>();
			ArrayList<houseobj> timestamps = new ArrayList<houseobj>();
			while (res.next()) {
				int t= res.getInt(1);
				housenombers.add(t);
				System.out.println(t + "         " + res.getInt(2) + "                " + res.getString(3));
			    String temp = res.getString(4);
			    timestamps.add(new houseobj(t,temp));
			}
			System.out.println("If you want to rent a house - put in the house nomber else put in 0 ");
			String house=inLine.readLine();
			int houseno=Integer.parseInt(house);
			if (!housenombers.contains(houseno)) {
				System.out.println("You will exit the system");
				houseno = 0;
//				myConnection.rollback();  // <-- Transaction is not started, so don't do it
			}
			if (houseno != 0) {
				System.out.println("Put in your name");
				String name=inLine.readLine();
				boolean found =false;
				myConnection.setAutoCommit(false); // <-- start the transaction here'
				// now check to see, if the house is still free
				sql = "select ts from periodts p where houseno = " + houseno +" and weekno = " + week;
				res=stmt.executeQuery(sql);
				if (res.next()) {
				  String newts;
				  newts = res.getString(1);
				  int i = 0;
				  while (i < timestamps.size() & !found) {
					  if (timestamps.get(i).houseno == houseno)
						  if (timestamps.get(i).ts.equals(newts))
							  found = true;
					  i ++;  
				  }
				}  
				if (found) {
				  stmt.execute("update periodts set rented = '1',tenant = '" + name + "' where houseno = " + houseno + " and weekno = " + week );
				  myConnection.commit(); 
				  System.out.println("House no " + houseno + " is now rented for week " + week);
				}
				else {
					myConnection.rollback();
					System.out.println("Someone else have rented the house - we are sorry");
				}

		}
			
		}		
		
		catch (Exception e) {
			System.out.println("error:  "+e.getMessage());
		}

	}


}





// EXAMPLE 4






import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;


public class Cottage4 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			System.out.println("Welcome to this holiday cottage renting system");
			System.out.println("Which week do you want?");
			BufferedReader inLine = new BufferedReader(new InputStreamReader(System.in));
			String week=inLine.readLine();
			Connection myConnection;
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			myConnection = DriverManager.getConnection("jdbc:odbc:oeksdb","sa","torben07");
			Statement stmt = myConnection.createStatement();
		//	myConnection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
			stmt.execute("set transaction isolation level snapshot");
		//	start the transaction
			myConnection.setAutoCommit(false);  
			String sql = "select c.houseno, priceaweek, area from cottage c,period p where c.houseno=p.houseno and rented = '0' and weekno = " + week;
			ResultSet res=stmt.executeQuery(sql);
			System.out.println("We can offer the following cottages in week " + week);
			System.out.println("Houseno:  Price for a week:    Area:");
			HashSet<Integer> housenombers = new HashSet<Integer>();
			while (res.next()) {
				int t= res.getInt(1);
				housenombers.add(t);
				System.out.println(t + "         " + res.getInt(2) + "                " + res.getString(3));
			}
			System.out.println("If you want to rent a house - put in the house nomber else put in 0 ");
			String house=inLine.readLine();
			int houseno=Integer.parseInt(house);
			if (!housenombers.contains(houseno)) {
				System.out.println("You will exit the system");
				houseno = 0;
//				myConnection.rollback();  // <-- Transaction is not started, so don't do it
			}
			if (houseno != 0) {
				System.out.println("Put in your name");
				String name=inLine.readLine();
				stmt.execute("update period set rented = '1',tenant = '" + name + "' where houseno = " + houseno + " and weekno = " + week );
				myConnection.commit(); 
				System.out.println("House no " + houseno + " is now rented for week " + week);
			}
				
				
		}
		catch (SQLException e) {
			System.out.println("fejl:  "+e.getMessage());
		}
		
		catch (Exception e) {
			System.out.println("error:  "+e.getMessage());
		}

	}

}