import java.sql.*;
import java.io.ObjectOutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.swing.ImageIcon;
public class LibraryData {
private static Connection connection;
private static Statement stmt;
-----------------------------Das ist die Connection zur datenbank--------------------------------------------------
static {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Assumes Library.mdb is in the same folder as LibraryData.class
String sourceURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=Library.mdb;";
connection = DriverManager.getConnection(sourceURL, "admin", "");
stmt = connection.createStatement();
} catch (Exception e) {
// shouldn't happen if DB is set up correctly
System.out.println(e);
}
}
------------------------------------------------------------------------------------------------------------------------------
public static String listAll() {
String output = "";
try {
// Need single quote marks ' around the key field in SQL. This is easy to get wrong!
// For instance if key was "04" the SELECT statement would be:
// SELECT * FROM LibraryTable WHERE key = '04'
ResultSet res = stmt.executeQuery("SELECT * FROM LibraryTable");
while (res.next()) { // there is a result
// the name field is the second one in the ResultSet
// Note that with ResultSet we count the fields starting from 1
output += res.getString(1) + " " + " - " + res.getString(4) + " (Played Time) " + res.getString(5) + " - " + res.getString(2) + " - " + res.getString(3) + "\n";
}
} catch (Exception e) {
System.out.println(e);
return null;
}
return output;
}
public static String getName(String key) {
try {
// Need single quote marks ' around the key field in SQL. This is easy to get wrong!
// For instance if key was "04" the SELECT statement would be:
// SELECT * FROM LibraryTable WHERE key = '04'
ResultSet res = stmt.executeQuery("SELECT * FROM LibraryTable WHERE key = '" + key + "'");
if (res.next()) { // there is a result
// the name field is the second one in the ResultSet
// Note that with ResultSet we count the fields starting from 1
return res.getString(2);
} else {
return null;
}
} catch (Exception e) {
System.out.println(e);
return null;
}
}
-------------------------------------------------------------------- Das is der Part mit dem Blob---------------------------------------------------------------
public static String getPicture(String key) {
String sourceURL;
Connection con;
con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=Library.mdb;");
PreparedStatement ps;
// ich verstehe nicht ganz was ich in den Klammern hineinschreiben muss (name,Photo)+(value(?,?)
ps = con.prepareStatement("insert into Pictrue(name,photo) " + "values(?,?)");
ps.setString(6, "dj");
Blob blob = con.createBlob();
ImageIcon ii = new ImageIcon("dj.gif");
ObjectOutputStream oos;
oos = new ObjectOutputStream(blob.setBinaryStream(1));
oos.writeObject(ii);
oos.close();
ps.setBlob(6, blob);
ps.execute();
blob.free();
ps.close();
}
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
public static String getArtist(String key) {
// Similar to getName - use res.getString(3). If no result, return null
try {
// Need single quote marks ' around the key field in SQL. This is easy to get wrong!
// For instance if key was "04" the SELECT statement would be:
// SELECT * FROM LibraryTable WHERE key = '04'
ResultSet res = stmt.executeQuery("SELECT * FROM LibraryTable WHERE key = '" + key + "'");
if (res.next()) { // there is a result
// the name field is the second one in the ResultSet
// Note that with ResultSet we count the fields starting from 1
return res.getString(3);
} else {
return null;
}
} catch (Exception e) {
System.out.println(e);
return null;
}
}
public static int getRating(String key) {
// Similar to getName - use res.getInt(4). If no result, return -1
//return -1;
try {
// Need single quote marks ' around the key field in SQL. This is easy to get wrong!
// For instance if key was "04" the SELECT statement would be:
// SELECT * FROM LibraryTable WHERE key = '04'
ResultSet res = stmt.executeQuery("SELECT * FROM LibraryTable WHERE key = '" + key +"'" );
if (res.next()) { // there is a result
// the name field is the second one in the ResultSet
// Note that with ResultSet we count the fields starting from 1
return res.getInt(4);
} else {
return -1;
}
} catch (Exception e) {
System.out.println(e);
return -1;
}
}
public static int getPlayCount(String key) {
// Similar to getName - use res.getInt(5). If no result, return -1
//return -1;
try {
// Need single quote marks ' around the key field in SQL. This is easy to get wrong!
// For instance if key was "04" the SELECT statement would be:
// SELECT * FROM LibraryTable WHERE key = '04'
ResultSet res = stmt.executeQuery("SELECT * FROM LibraryTable WHERE key = '" + key + "'");
if (res.next()) { // there is a result
// the name field is the second one in the ResultSet
// Note that with ResultSet we count the fields starting from 1
return res.getInt(5);
} else {
return +1;
}
} catch (Exception e) {
System.out.println(e);
return +1;
}
}
public static void setRating(String key, int rating) {
// SQL UPDATE statement required. For instance if rating is 5 and key is "04" then updateStr is
// UPDATE Libary SET rating = 5 WHERE key = '04'
String updateStr = ("UPDATE LibraryTable SET rating = " + rating + " WHERE key = '" + key + "'" );
//System.out.println(updateStr);
try {
stmt.executeUpdate(updateStr);
} catch (Exception e) {
System.out.println(e);
}
}
public static void incrementPlayCount(String key) {
int playCount = getPlayCount(key) + 1;
// SQL UPDATE statement required. For instance if rating is 5 and key is "04" then updateStr is
// UPDATE Libary SET rating = 5 WHERE key = '04'
String updateStr = "UPDATE LibraryTable SET playCount = " + playCount + " WHERE key = '" + key + "'";
//System.out.println(updateStr);
try {
stmt.executeUpdate(updateStr);
} catch (Exception e) {
System.out.println(e);
}
// Similar to setRating - but must getPlayCount first and increment by 1
}
// close the database
public static void close() {
try {
connection.close();
} catch (Exception e) {
// this shouldn't happen
System.out.println(e);
}
}
private String stars(int Rating) {
String stars = "";
// line 77-79 for loop assigning a control variable to a starting value
// the test is carried out prior to any execution of the loop
// it increments the control variable by 1
for (int i = 0; i < Rating; ++i) {
stars += "*";
}
// line 81 returns the value
return stars;
}
}
