Jag håller på med ett projekt just nu och har en class som fungerar, dock tycker jag den är väldigt lång.
/*
* DatabaseClass.java
*
*/
/**
*
* @author Christoffer Börjessson
*/
package truefashion;
import java.sql.*;
import java.util.*;
import java.awt.Image;
public class DatabaseClass
{
private Connection con;
public Statement stmt;
private String[][] entries = new String[100][100];
private String [] columns;
int id;
int matchesToId;
private static final String dbUrl = "jdbc:..";
private static final String User = "iuser";
private static final String Password = "pw";
private static final String DriverName = "com.mysql.jdbc.Driver";
/**
* Constructor
* @param columnNames
*/
public DatabaseClass(String [] c)
{
columns = c;
loadDriver();
}
/**
* Constructor
*
*/
public DatabaseClass()
{
loadDriver();
}
/**
* loadDriver
* loads the JDBC driver in com folder
*/
public void loadDriver()
{
try
{ //1. Load the JDBC driver
Class.forName(DriverName);
}
catch (ClassNotFoundException cnf) // cannot find driver
{
System.out.println (cnf.getMessage());
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
}
/**
* connect
* sets up a connection to the database and creates a statement
*/
public void connect()
{
try
{
// 2. Get a database connection
con = DriverManager.getConnection(dbUrl, User, Password);
// 3. Create statement
stmt = con.createStatement();
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
}
}
/**
* closeConnection
* Closes the connection
*/
public void closeConnection()
{
try //close stmt and connection
{
stmt.close();
con.close();
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
}
}
public Statement getStatement()
{
return stmt;
}
// ResultSet rs = stmt.executeQuery(selectSQL);
// kollektion, aktiv eller ej.
private ArrayList getCollection(String collectionName)
{
connect();
ArrayList collection = new ArrayList();
try
{
String selectSQL = "SELECT*FROM Collection WHERE collectionName="+collectionName+";";
// 4. Execute query
ResultSet rs = stmt.executeQuery(selectSQL);
while (rs.next()) {
collection.add(rs.getString("matchesToID"));
}
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
return null;
}
closeConnection();
return collection;
}
// Retunerar en samling med alla färgkoder
public ArrayList getColorCodes()
{
connect();
ArrayList colorCodes = new ArrayList();
try
{
String selectSQL = "SELECT*FROM Colors";
// 4. Execute query
ResultSet rs = stmt.executeQuery(selectSQL);
while (rs.next()) {
colorCodes.add(rs.getString("colorCode"));
}
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
return null;
}
closeConnection();
return colorCodes;
}
// Storlek och saldo på den storleken hämtas via id som stämmer överrens.
private ArrayList getMatchingGarment(String id)
{
connect();
ArrayList matchingGarment = new ArrayList();
try
{
String selectSQL = "SELECT matchesToID FROM MatchingGarment WHERE id="+id+";";
// 4. Execute query
ResultSet rs = stmt.executeQuery(selectSQL);
while (rs.next()) {
matchingGarment.add(rs.getString("matchesToID"));
}
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
return null;
}
closeConnection();
return matchingGarment;
}
// Storlek och saldo på den storleken hämtas via id som stämmer överrens.
private ArrayList getStorage(String id)
{
connect();
ArrayList storage = new ArrayList();
try
{
String selectSQL = "SELECT size,quantity FROM Storage WHERE id="+id+";";
// 4. Execute query
ResultSet rs = stmt.executeQuery(selectSQL);
while (rs.next()) {
Storage store = new Storage(rs.getString("size"),rs.getInt("quantity"));
// skapa en ny class istället , gör classen också.
storage.add(store);
//storage.add(rs.getString("size"));
//storage.add(rs.getString("quantity"));
}
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
return null;
}
closeConnection();
return storage;
}
// Hämtar färgerna till kläder med samma ID.
private ArrayList getColorsInGarment(String id)
{
connect();
ArrayList colors = new ArrayList();
try
{
String selectSQL = "SELECT * FROM GarmentColor WHERE id="+id+";";
// 4. Execute query
ResultSet rs = stmt.executeQuery(selectSQL);
while (rs.next()) {
colors.add(rs.getString("colorCode"));
}
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
return null;
}
closeConnection();
return colors;
}
/**
* insertValue
* @param v, the values to insert
*/
public ArrayList getGarment()
{
connect();
ArrayList garment = new ArrayList();
try
{
String selectSQL = "SELECT*FROM Garment";
// 4. Execute query
ResultSet rs = stmt.executeQuery(selectSQL);
while (rs.next()) {
// null = Image image ( ej klar vid kodningen)
Garment g = new Garment(rs.getString("style"),getColorsInGarment(rs.getString("id")), getStorage(rs.getString("id")), rs.getString("type"), rs.getString("artnr"), rs.getFloat("price"), rs.getString("collectionName"), rs.getString("gender"), rs.getString("category"), null, rs.getInt("id"));
garment.add(g);
}
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
return null;
}
closeConnection();
return garment;
}
// Lägger till kläder i Garment.
public void setGarment(Garment garment)
{
connect();
try
{
String selectSQL = "INSERT INTO Garment(artNr,type,price,collectionName,gender,category,imageURL,style) VALUES ('"+garment.getArtNr()+"','"+garment.getType()+"',"+garment.getPrice()+",'"+garment.getCollectionName()+"','"+garment.getGender()+"','"+garment.getCategory()+"','"+garment.getImageUrl()+"','"+garment.getStyle()+"');";
stmt.executeUpdate(selectSQL);
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
}
closeConnection();
}
// Tar bort ett garment från databasen
private void delGarment(Garment garment)
{
connect();
try
{
String selectSQL = "DELETE Garment WHERE id="+garment.getID()+");";
stmt.executeUpdate(selectSQL);
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
}
closeConnection();
}
// Sparar vilka överdelar som matchar till underdelar. Överdelarnas ID ska kontrolleras så dom sparas som ID
// och underdelens ID ska sparas som matchesToId.
public void setMatchesToId(Garment garment, Garment garment2)
{
connect();
try
{
// Kontrollerar om plagget är en överdel eller underdel
if (garment.getType() == "upperBody") {
id = garment.getID();
matchesToId = garment2.getID();
} else {
matchesToId = garment.getID();
id = garment2.getID();
}
String selectSQL = "INSERT INTO matchingGarment(id,matchingToId) VALUES ("+ id +","+ matchesToId +");";
stmt.executeUpdate(selectSQL);
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
}
closeConnection();
}
// Tar bort ett en matchning mellan 2 plagg, garment sorteras först så överdelens ID kollar i databasens ID.
public void delMatchesToId (Garment garment, Garment garment2)
{
connect();
try
{
if (garment.getType() == "upperBody") {
id = garment.getID();
matchesToId = garment2.getID();
} else {
matchesToId = garment.getID();
id = garment2.getID();
}
String selectSQL = "DELETE matchingGarment WHERE id="+ id +" AND matchesToID="+ matchesToId +";";
stmt.executeUpdate(selectSQL);
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
}
closeConnection();
}
/**
* Retunerar en samling med färgkoder som stämmer överrens med skinID & hairID.
*
*/
public ArrayList getLookMatchColors(String skinID, String hairID)
{
connect();
ArrayList lookMatchColors = new ArrayList();
try
{
String selectSQL = "SELECT colorCode FROM LookMatch WHERE skinID="+skinID+" AND hairID="+hairID+";";
// 4. Execute query
ResultSet rs = stmt.executeQuery(selectSQL);
while (rs.next()) {
lookMatchColors.add(rs.getString("colorCode"));
}
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
return null;
}
closeConnection();
return lookMatchColors;
}
/**
* Retunerar en samling med LookMatch objekt där matchningskombinationer ligger i.
*
*/
public ArrayList getLookMatch()
{
connect();
ArrayList lookMatch = new ArrayList();
try
{
String selectSQL = "SELECT DISTINCT skinID, hairID FROM LookMatch";
// 4. Execute query
ResultSet rs = stmt.executeQuery(selectSQL);
while (rs.next()) {
LookMatch lm = new LookMatch (rs.getString("skinID"),getLookMatchColors(rs.getString("skinID"),rs.getString("hairID")), rs.getString("hairID"));
lookMatch.add(lm);
}
}
catch (SQLException sqe)
{
System.out.println (sqe.getMessage ());
System.out.println (sqe.getSQLState());
return null;
}
closeConnection();
return lookMatch;
}
}
