public
class
SqliteSave {
public
static
void
InitDb() {
try
{
Class.forName(
"org.sqlite.JDBC"
);
Connection c = DriverManager.getConnection(
"jdbc:sqlite:sqlite/beacon.db"
);
CommonUtils.print_good(
"[DB] The sqlite/beacon.db is created successfully!"
);
Statement stmt = c.createStatement();
stmt.executeUpdate(
"CREATE TABLE Beacon (Id INTEGER PRIMARY KEY AUTOINCREMENT, BeaconId CHAR(50), Hash CHAR(50), StartTime CHAR(50), External CHAR(50), Internal CHAR(50), Computer CHAR(50), Process CHAR(50), User CHAR(50), Arch CHAR(50), Note CHAR(50), UpdateNoteTime CHAR(50))"
);
if
(stmt !=
null
) {
stmt.close();
}
if
(c !=
null
) {
c.close();
}
}
catch
(Exception e) {
System.err.println(e.getClass().getName() +
": "
+ e.getMessage());
System.exit(
0
);
}
CommonUtils.print_info(
"[DB] The database is initialized successfully!"
);
}
public
static
Connection OpenDb() {
Connection c =
null
;
try
{
Class.forName(
"org.sqlite.JDBC"
);
c = DriverManager.getConnection(
"jdbc:sqlite:sqlite/beacon.db"
);
}
catch
(Exception e) {
System.err.println(e.getClass().getName() +
": "
+ e.getMessage());
System.exit(
0
);
}
return
c;
}
public
static
HashMap<String, String> CheckBeaconHash(Connection c, String hash) {
HashMap<String, String> Beacon =
new
HashMap<>();
try
{
PreparedStatement ps = c.prepareStatement(
"SELECT * FROM Beacon WHERE Hash = (?) ORDER BY UpdateNoteTime DESC;"
);
ps.setString(
1
, hash);
ResultSet rs = ps.executeQuery();
while
(rs.next()) {
if
(rs.getString(
"Hash"
).equals(hash)) {
Beacon.put(
"StartTime"
, rs.getString(
"StartTime"
));
Beacon.put(
"Note"
, rs.getString(
"Note"
));
ps.close();
c.close();
return
Beacon;
}
}
rs.close();
ps.close();
c.close();
return
null
;
}
catch
(SQLException e) {
throw
new
RuntimeException(e);
}
}
public
static
HashMap<String, String> CheckBeacon(Connection c, String hash, String BeaconId) {
HashMap<String, String> Beacon =
new
HashMap<>();
try
{
PreparedStatement ps = c.prepareStatement(
"SELECT * FROM Beacon WHERE Hash = (?) AND BeaconId = (?) ORDER BY UpdateNoteTime DESC;"
);
ps.setString(
1
, hash);
ps.setString(
2
, BeaconId);
ResultSet rs = ps.executeQuery();
while
(rs.next()) {
if
(rs.getString(
"Hash"
).equals(hash)) {
Beacon.put(
"StartTime"
, rs.getString(
"StartTime"
));
Beacon.put(
"Note"
, rs.getString(
"Note"
));
ps.close();
c.close();
return
Beacon;
}
}
rs.close();
ps.close();
c.close();
return
null
;
}
catch
(SQLException e) {
throw
new
RuntimeException(e);
}
}
public
static
HashMap<String, String> CheckSShBeacon(Connection c, info BeaconInfo) {
HashMap<String, String> Beacon =
new
HashMap<>();
try
{
PreparedStatement ps = c.prepareStatement(
"SELECT * FROM Beacon WHERE Computer = (?) and User = (?) and Arch = (?) and Process = (?) and External = (?) ORDER BY id DESC;"
);
ps.setString(
1
, BeaconInfo.Computer);
ps.setString(
2
, BeaconInfo.User);
ps.setString(
3
, BeaconInfo.Arch);
ps.setString(
4
, BeaconInfo.Process);
ps.setString(
5
, BeaconInfo.External);
ResultSet rs = ps.executeQuery();
while
(rs.next()) {
if
(rs !=
null
) {
Beacon.put(
"Hash"
, rs.getString(
"Hash"
));
Beacon.put(
"StartTime"
, rs.getString(
"StartTime"
));
Beacon.put(
"Note"
, rs.getString(
"Note"
));
Beacon.put(
"Id"
, rs.getString(
"Id"
));
Beacon.put(
"External"
, rs.getString(
"External"
));
Beacon.put(
"Internal"
, rs.getString(
"Internal"
));
Beacon.put(DOMKeyboardEvent.KEY_PROCESS, rs.getString(DOMKeyboardEvent.KEY_PROCESS));
Beacon.put(
"Arch"
, rs.getString(
"Arch"
));
Beacon.put(
"User"
, rs.getString(
"User"
));
Beacon.put(
"Computer"
, rs.getString(
"Computer"
));
ps.close();
c.close();
return
Beacon;
}
}
rs.close();
ps.close();
c.close();
return
null
;
}
catch
(SQLException e) {
throw
new
RuntimeException(e);
}
}
public
static
void
AddBeacon(Connection c, info BeaconInfo) {
String times = utils.BeijingTime.formatToBeijingTime();
try
{
PreparedStatement ps = c.prepareStatement(
"INSERT INTO Beacon (Hash,StartTime,Note,BeaconId,External,Process,Arch,User,Computer,Internal,UpdateNoteTime) VALUES (?, ?, \"\",?,?,?,?,?,?,?,?);"
);
ps.setString(
1
, BeaconInfo.Hash);
ps.setString(
2
, times);
ps.setString(
3
, BeaconInfo.BeaconId);
ps.setString(
4
, BeaconInfo.External);
ps.setString(
5
, BeaconInfo.Process);
ps.setString(
6
, BeaconInfo.Arch);
ps.setString(
7
, BeaconInfo.User);
ps.setString(
8
, BeaconInfo.Computer);
ps.setString(
9
, BeaconInfo.Internal);
ps.setString(
10
, times);
ps.execute();
if
(ps !=
null
) {
ps.close();
}
if
(c !=
null
) {
c.close();
}
}
catch
(SQLException e) {
throw
new
RuntimeException(e);
}
}
public
static
void
AddBeacon2(Connection c, info BeaconInfo) {
try
{
PreparedStatement ps = c.prepareStatement(
"INSERT INTO Beacon (Hash,StartTime,Note,BeaconId,External,Process,Arch,User,Computer,Internal,UpdateNoteTime) VALUES (?, ?, ?,?,?,?,?,?,?,?,\"\");"
);
ps.setString(
1
, BeaconInfo.Hash);
ps.setString(
2
, BeaconInfo.StartTime);
ps.setString(
3
, BeaconInfo.Note);
ps.setString(
4
, BeaconInfo.BeaconId);
ps.setString(
5
, BeaconInfo.External);
ps.setString(
6
, BeaconInfo.Process);
ps.setString(
7
, BeaconInfo.Arch);
ps.setString(
8
, BeaconInfo.User);
ps.setString(
9
, BeaconInfo.Computer);
ps.setString(
10
, BeaconInfo.Internal);
ps.execute();
if
(ps !=
null
) {
ps.close();
}
if
(c !=
null
) {
c.close();
}
}
catch
(SQLException e) {
throw
new
RuntimeException(e);
}
}
public
static
void
UpBeaconNote(Connection c, String BeaconId, String Note) {
String times = utils.BeijingTime.formatToBeijingTime();;
try
{
PreparedStatement ps = c.prepareStatement(
"UPDATE Beacon set Note = ? , UpdateNoteTime = ? where BeaconId=?;"
);
ps.setString(
1
, Note);
ps.setString(
2
, times);
ps.setString(
3
, BeaconId);
ps.executeUpdate();
ps.close();
c.close();
}
catch
(SQLException e) {
throw
new
RuntimeException(e);
}
}
public
static
void
UpBeaconLastTime(Connection c, String BeaconId, String Note) {
String times = utils.BeijingTime.formatToBeijingTime();;
try
{
PreparedStatement ps = c.prepareStatement(
"UPDATE Beacon set LastTime = ? where BeaconId=?;"
);
ps.setString(
1
, times);
ps.setString(
2
, BeaconId);
ps.executeUpdate();
ps.close();
c.close();
}
catch
(SQLException e) {
throw
new
RuntimeException(e);
}
}
public
static
void
UpBeaconId(Connection c, String Hash, String BeaconId) {
try
{
PreparedStatement ps = c.prepareStatement(
"UPDATE Beacon set BeaconId = ? where Hash=?;"
);
ps.setString(
1
, BeaconId);
ps.setString(
2
, Hash);
ps.executeUpdate();
ps.close();
c.close();
}
catch
(SQLException e) {
throw
new
RuntimeException(e);
}
}
}