Code-Bin #1280 - JDBC dao

JDBC dao
Posted by: KarlK | May 24, 2011 @ 6:40am

Java Code

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Company;
import model.Zipcode;

public class Dao
{
	static Connection conn = createConnection();

public static Connection createConnection()
	{
		Connection conn = null;

try {
			String userName = "root";
			String password = "test123";
			String url = "jdbc:mysql://localhost/exampledb";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");

} catch (Exception e) {
			System.err.println("Cannot connect to database server: " + e);
		}
		return conn;

}

public static void storeCompany(Company company)
	{
		try {
			PreparedStatement c;
			c = conn.prepareStatement("INSERT INTO company (companynr,companyname,zip) VALUES(?,?,?)");
			c.setInt(1, company.getCompanynr());
			c.setString(2, company.getCompanyname());
			c.setString(3, company.getZip());
			c.executeUpdate();
			c.close();
		} catch (SQLException e) {
			System.out.println(e);
		}

}

public static void updateCompany(Company company, int oldNumber)
	{
		try {
			PreparedStatement c;
			c = conn.prepareStatement("UPDATE company SET companynr=?, companyname=?, zip=? WHERE companynr=?");
			c.setInt(1, company.getCompanynr());
			c.setString(2, company.getCompanyname());
			c.setString(3, company.getZip());
			c.setInt(4, oldNumber);
			c.executeUpdate();
			c.close();
		} catch (SQLException e) {
			if (e.getMessage().contains("Duplicate entry")) {
				System.out
						.println("Cannot have duplicate Primary Key (number)");
			}
			if (e.getMessage()
					.contains(
							"Cannot add or update a child row: a foreign key constraint fails")) {
				System.out.println("Invalid ZIP");
			}
			System.out.println(e);
		}
	}

public static void deleteCompany(Company company)
	{
		try {
			PreparedStatement c;
			c = conn.prepareStatement("DELETE FROM company WHERE companynr=?");
			c.setInt(1, company.getCompanynr());
			c.executeUpdate();
			c.close();
		} catch (SQLException e) {
			System.out.println(e);
		}
	}

public static List<Company> getAllCompanys()
	{
		ArrayList<Company> result = new ArrayList<Company>();

try {

Statement c = conn.createStatement();
			c.executeQuery("SELECT companynr, companyname, zip FROM company");
			ResultSet rs = c.getResultSet();
			while (rs.next()) {
				Company company = new Company();

company.setCompanynr(rs.getInt("companynr"));

company.setCompanyname(rs.getString("companyname"));
				if (rs.wasNull()) {
					company.setCompanyname("(name missing)");
				}

company.setZip(rs.getString("zip"));
				if (rs.wasNull()) {
					company.setZip("????");
				}
				result.add(company);

}
			rs.close();
			c.close();

} catch (SQLException e) {
			System.out.println(e);
		}
		return result;

}

public static ArrayList<Zipcode> getDistricts()
	{
		ArrayList<Zipcode> districts = new ArrayList<Zipcode>();

try {

Statement z = conn.createStatement();
			z.executeQuery("SELECT zip, postaldistrict FROM zipcode");
			ResultSet rs = z.getResultSet();
			while (rs.next()) {
				Zipcode zipcode = new Zipcode();

zipcode.setZip(rs.getString("zip"));
				if (rs.wasNull()) {
					zipcode.setZip("????");
				}

zipcode.setPostaldistrict(rs.getString("postaldistrict"));
				if (rs.wasNull()) {
					zipcode.setPostaldistrict("(district missing)");
				}

districts.add(zipcode);

}
			rs.close();
			z.close();

} catch (SQLException e) {
			System.out.println(e);
		}
		return districts;

}

public static ArrayList<Zipcode> getDistricts(String zip)
	{
		ArrayList<Zipcode> districts = new ArrayList<Zipcode>();

try {

Statement z = conn.createStatement();
			z.executeQuery("SELECT zip, postaldistrict FROM zipcode WHERE zip LIKE '"
					+ zip + "%'");
			System.out.println(zip);
			ResultSet rs = z.getResultSet();
			while (rs.next()) {
				Zipcode zipcode = new Zipcode();

zipcode.setZip(rs.getString("zip"));
				if (rs.wasNull()) {
					zipcode.setZip("????");
				}

zipcode.setPostaldistrict(rs.getString("postaldistrict"));
				if (rs.wasNull()) {
					zipcode.setPostaldistrict("(district missing)");
				}

districts.add(zipcode);

}
			rs.close();
			z.close();

} catch (SQLException e) {
			System.out.println(e);
		}
		return districts;

}
}

Syntax Highlighting

[Open in new window]

Author Comments

none

Rating

4.45 / 8
223 Votes