206 lines
5.4 KiB
Java
206 lines
5.4 KiB
Java
|
|
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;
|
|
|
|
class DBController
|
|
{
|
|
|
|
private Connection connection;
|
|
|
|
private String DB_PATH_Linux;
|
|
|
|
public String dbname = "tlcIndex";
|
|
|
|
public void main()
|
|
{
|
|
try {
|
|
connection = DriverManager
|
|
.getConnection("jdbc:sqlite:" + DB_PATH_Linux + dbname + ".db");
|
|
} catch (SQLException e) {
|
|
// TODO Auto-generated catch block
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
|
|
public DBController(String projectPath)
|
|
{
|
|
this.DB_PATH_Linux = projectPath;
|
|
}
|
|
|
|
public void connectDatabase()
|
|
{ // connect to database
|
|
System.out.println("Verbinde ... DB name: " + dbname);
|
|
try {
|
|
if (connection != null)
|
|
return;
|
|
connection = DriverManager
|
|
.getConnection("jdbc:sqlite:" + DB_PATH_Linux + dbname + ".db");
|
|
if (!connection.isClosed())
|
|
System.out.println("DB Datei-Verbindung erstellt");
|
|
} catch (SQLException e) {
|
|
throw new RuntimeException(e);
|
|
}
|
|
Runtime.getRuntime().addShutdownHook(new Thread() {
|
|
public void run()
|
|
{
|
|
try {
|
|
if (!connection.isClosed() && connection != null) {
|
|
connection.close();
|
|
if (connection.isClosed())
|
|
System.out.println();
|
|
}
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
});
|
|
|
|
System.out.println("Connect geklappt");
|
|
|
|
}
|
|
|
|
public void createTableImages()
|
|
{ // create table position
|
|
System.out.println("Erstelle Tabelle images");
|
|
try {
|
|
Statement stmt = connection.createStatement();
|
|
stmt.executeUpdate("DROP TABLE IF EXISTS images;");
|
|
stmt.executeUpdate(
|
|
"CREATE TABLE images (imageCounter, path, day, month, year, hour, minute, size, brightness);");
|
|
} catch (SQLException e) {
|
|
System.err.println("Couldn't handle DB-Query");
|
|
e.printStackTrace();
|
|
System.exit(-1);
|
|
}
|
|
}
|
|
|
|
public void fillImages(int imageCounter, String path, int day, int month,
|
|
int year, int hour, int minute, int size, int brightness)
|
|
{ // create new data in table
|
|
// System.out.println("Erstelle neuen images eintrag");
|
|
try {
|
|
PreparedStatement ps = connection.prepareStatement(
|
|
"INSERT INTO images VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);");
|
|
ps.setInt(1, imageCounter); // primary
|
|
ps.setString(2, path);
|
|
ps.setInt(3, day);
|
|
ps.setInt(4, month);
|
|
ps.setInt(5, year);
|
|
ps.setInt(6, hour);
|
|
ps.setInt(7, minute);
|
|
ps.setInt(8, size);
|
|
ps.setInt(9, brightness);
|
|
ps.addBatch();
|
|
connection.setAutoCommit(false);
|
|
ps.executeBatch(); // SQL execution
|
|
connection.setAutoCommit(true);
|
|
} catch (SQLException e) {
|
|
System.err.println("Couldn't handle DB-Query");
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
|
|
public List<Image> query(String pQuery)
|
|
{
|
|
List<Image> list = new ArrayList<Image>();
|
|
|
|
try {
|
|
Statement stmt = connection.createStatement();
|
|
ResultSet rs = stmt.executeQuery(pQuery);
|
|
while (rs.next()) {
|
|
list.add(new Image(rs.getInt("imageCounter"), rs.getString("path"),
|
|
rs.getInt("day"), rs.getInt("month"), rs.getInt("year"),
|
|
rs.getInt("hour"), rs.getInt("minute"), rs.getInt("size"),
|
|
rs.getInt("brightness")));
|
|
} // end while
|
|
return list;
|
|
} catch (SQLException e) {
|
|
System.err.println("Couldn't handle DB-Query");
|
|
e.printStackTrace();
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public String getPathFromIndex(int pCounter)
|
|
{
|
|
try {
|
|
Statement stmt = connection.createStatement();
|
|
ResultSet rs = stmt
|
|
.executeQuery("SELECT path FROM images WHERE imageCounter = "
|
|
+ pCounter + " ;");
|
|
return rs.getString("path");
|
|
} catch (SQLException e) {
|
|
System.err.println("Couldn't handle DB-Query");
|
|
e.printStackTrace();
|
|
return "Error 404";
|
|
}
|
|
}
|
|
|
|
public int getHourFromIndex(int pCounter)
|
|
{
|
|
try {
|
|
Statement stmt = connection.createStatement();
|
|
ResultSet rs = stmt
|
|
.executeQuery("SELECT hour FROM images WHERE imageCounter = "
|
|
+ pCounter + " ;");
|
|
return rs.getInt("hour");
|
|
} catch (SQLException e) {
|
|
System.err.println("Couldn't handle DB-Query");
|
|
e.printStackTrace();
|
|
return 404;
|
|
}
|
|
}
|
|
|
|
public int getMinuteFromIndex(int pCounter)
|
|
{
|
|
try {
|
|
Statement stmt = connection.createStatement();
|
|
ResultSet rs = stmt
|
|
.executeQuery("SELECT minute FROM images WHERE imageCounter = "
|
|
+ pCounter + " ;");
|
|
return rs.getInt("minute");
|
|
} catch (SQLException e) {
|
|
System.err.println("Couldn't handle DB-Query");
|
|
e.printStackTrace();
|
|
return 404;
|
|
}
|
|
}
|
|
|
|
public int getDayFromIndex(int pCounter)
|
|
{
|
|
try {
|
|
Statement stmt = connection.createStatement();
|
|
ResultSet rs = stmt
|
|
.executeQuery("SELECT day FROM images WHERE imageCounter = "
|
|
+ pCounter + " ;");
|
|
return rs.getInt("day");
|
|
} catch (SQLException e) {
|
|
System.err.println("Couldn't handle DB-Query");
|
|
e.printStackTrace();
|
|
return 404;
|
|
}
|
|
}
|
|
|
|
public int getSizeFromIndex(int pCounter)
|
|
{
|
|
try {
|
|
Statement stmt = connection.createStatement();
|
|
ResultSet rs = stmt
|
|
.executeQuery("SELECT size FROM images WHERE imageCounter = "
|
|
+ pCounter + " ;");
|
|
return rs.getInt("size");
|
|
} catch (SQLException e) {
|
|
System.err.println("Couldn't handle DB-Query");
|
|
e.printStackTrace();
|
|
return 404;
|
|
}
|
|
}
|
|
|
|
}
|