private void initFromDatabase() throws SQLException, BlockStoreException {
Statement s = conn.get().createStatement();
ResultSet rs;
rs = s.executeQuery("SELECT value FROM settings WHERE name = '" + CHAIN_HEAD_SETTING + "'");
if (!rs.next()) {
throw new BlockStoreException("corrupt Postgres block store - no chain head pointer");
}
Sha256Hash hash = new Sha256Hash(rs.getBytes(1));
rs.close();
this.chainHeadBlock = get(hash);
this.chainHeadHash = hash;
if (this.chainHeadBlock == null) {
throw new BlockStoreException("corrupt Postgres block store - head block not found");
}
rs =
s.executeQuery(
"SELECT value FROM settings WHERE name = '" + VERIFIED_CHAIN_HEAD_SETTING + "'");
if (!rs.next()) {
throw new BlockStoreException(
"corrupt Postgres block store - no verified chain head pointer");
}
hash = new Sha256Hash(rs.getBytes(1));
rs.close();
s.close();
this.verifiedChainHeadBlock = get(hash);
this.verifiedChainHeadHash = hash;
if (this.verifiedChainHeadBlock == null) {
throw new BlockStoreException("corrupt Postgres block store - verified head block not found");
}
}
public void testDelete() {
try {
insertData(con);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(EIACODXA) FROM CA");
rs.next();
int origCount = rs.getInt(1);
rs.close();
deleteXBRecord(con);
rs = stmt.executeQuery("SELECT COUNT(EIACODXA) FROM CA");
rs.next();
int newCount = rs.getInt(1);
rs.close();
stmt.close();
assertEquals(9, newCount);
} catch (SQLException e) {
this.assertTrue("SQLException thrown", false);
}
}
/**
* Test that we recover from self locking in the auto-create nested transaction (cf solution for
* DERBY-48).
*/
public void testDerby48SelfLockingRecovery() throws SQLException {
Connection c1 = openUserConnection("newuser");
c1.setAutoCommit(false);
c1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement s1 = c1.createStatement();
// Set read locks in parent transaction
s1.executeQuery("select count(*) from sys.sysschemas");
// ..which conflicts with the auto-create in a subtransaction
// which will self-lock here, but should recover to try again
// in outer transaction:
s1.executeUpdate("create table t1(i int)");
JDBC.assertSingleValueResultSet(
s1.executeQuery("select schemaname from sys.sysschemas " + "where schemaname='NEWUSER'"),
"NEWUSER");
c1.rollback();
// Since the fallback does the auto-create of the schema in
// the outer transaction, a rollback will remove it:
JDBC.assertEmpty(s1.executeQuery("select * from sys.sysschemas where schemaname='NEWUSER'"));
c1.rollback();
}
public static void namejudge(Socket socket, Connection conn, BufferedReader is) {
System.out.println("用户查询存在模块");
try {
PrintWriter os = new PrintWriter(socket.getOutputStream());
String rec;
rec = is.readLine();
// 执行SQL语句
Statement stmt = conn.createStatement(); // 创建语句对象，用以执行sql语言
if (rec.charAt(0) == '1') {
ResultSet rs = stmt.executeQuery("select * from teacher_table where logname=" + rec);
if (rs.first()) {
os.println(rec);
os.flush();
} else {
os.println("no" + rec);
os.flush();
}
} else if (rec.charAt(0) == '2') {
ResultSet rs = stmt.executeQuery("select * from student_table where logname=" + rec);
if (rs.first()) {
os.println(rec);
os.flush();
} else {
os.println("no" + rec);
os.flush();
}
} else {
os.println("no" + rec);
os.flush();
}
} catch (Exception e) {
System.out.println("Error:" + e);
}
}
/**
* Test support for JDBC 1 style positioned updates with named cursors.
*
* <p>When running on SQL Server this test will exercise MSCursorResultSet. When running on Sybase
* this test will exercise CachedResultSet.
*/
public void testPositionedUpdate() throws Exception {
assertTrue(con.getMetaData().supportsPositionedDelete());
assertTrue(con.getMetaData().supportsPositionedUpdate());
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #TESTPOS (id INT primary key, data VARCHAR(255))");
for (int i = 1; i < 5; i++) {
stmt.execute("INSERT INTO #TESTPOS VALUES(" + i + ", 'This is line " + i + "')");
}
stmt.setCursorName("curname");
ResultSet rs = stmt.executeQuery("SELECT * FROM #TESTPOS FOR UPDATE");
Statement stmt2 = con.createStatement();
while (rs.next()) {
if (rs.getInt(1) == 1) {
stmt2.execute("UPDATE #TESTPOS SET data = 'Updated' WHERE CURRENT OF curname");
} else if (rs.getInt(1) == 3) {
stmt2.execute("DELETE FROM #TESTPOS WHERE CURRENT OF curname");
}
}
rs.close();
stmt.setFetchSize(100);
rs = stmt.executeQuery("SELECT * FROM #TESTPOS");
while (rs.next()) {
int id = rs.getInt(1);
assertTrue(id != 3); // Should have been deleted
if (id == 1) {
assertEquals("Updated", rs.getString(2));
}
}
stmt2.close();
stmt.close();
}
public void deleteOption(String modelName, String optionSetName, String option) {
try {
int autoid = 0;
String sql = "select id from automobile where name ='" + modelName + "';";
ResultSet rs;
rs = statement.executeQuery(sql);
while (rs.next()) {
autoid = rs.getInt("id"); // get auto_id
}
int opsid = 0;
sql =
"select id from optionset where name ='"
+ optionSetName
+ "' and auto_id= "
+ autoid
+ ";";
rs = statement.executeQuery(sql);
while (rs.next()) {
opsid = rs.getInt("id"); // get option_id
}
sql = "delete from options where name= '" + option + "' and option_id= " + opsid;
statement.executeUpdate(sql); // delete it using name and option_id
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* fine the co-authors of given author name
*
* @param author's name
* @return return a string of all co-authors
* @throws Exception
*/
public String findCoAuthor(String name) throws Exception {
Statement stmt = conn.createStatement();
String useDB = "USE dblp;";
stmt.executeUpdate(useDB);
String sql = "SELECT id FROM authorInfo WHERE name = \"" + name + "\";";
ResultSet rs = stmt.executeQuery(sql);
ArrayList<String> id = new ArrayList<String>();
Set<String> coAuthor = new HashSet<>();
String ret = "Co-Authors of ".concat(name).concat(":\n");
while (rs.next()) {
id.add(rs.getString("id"));
}
if (id.size() == 0) {
return "No such author";
}
for (String s : id) {
sql = "SELECT name FROM authorInfo WHERE id=\"" + s + "\";";
rs = stmt.executeQuery(sql);
while (rs.next()) {
coAuthor.add(rs.getString("name"));
}
}
coAuthor.remove(name);
for (String s : coAuthor) {
ret = ret.concat(s + "\n");
}
if (ret.equals("Co-Authors of ".concat(name).concat(":\n"))) ret = "No Co-Author found";
if (stmt != null) {
stmt.close();
}
return ret;
}
public static void createDefaultShippingRegion(Connection connection) throws SQLException {
String sql =
"insert "
+ "into shipping_region "
+ " (published, rec_create_by, rec_create_datetime, rec_update_by, rec_update_datetime, shipping_region_name, site_id, system_record) "
+ "values ('Y', 'admin', now(), 'admin', now(), 'default', ?, 'Y')";
PreparedStatement insertStatement = connection.prepareStatement(sql);
Statement select = connection.createStatement();
sql =
"select site_id "
+ "from site "
+ "where site_id != '_system' "
+ "and system_record = 'Y'";
ResultSet result = select.executeQuery(sql);
while (result.next()) {
String siteId = result.getString("site_id");
sql =
"select count(*) "
+ "from shipping_region "
+ "where site_id = '"
+ siteId
+ "' "
+ "and system_record = 'Y'";
Statement countStatement = connection.createStatement();
ResultSet shippingRegionResult = countStatement.executeQuery(sql);
shippingRegionResult.first();
int count = shippingRegionResult.getInt(1);
if (count == 0) {
insertStatement.setString(1, siteId);
insertStatement.executeUpdate();
}
}
}
private boolean validateConnectiontoDBMS() throws SQLException {
int NumberOfRows, Response;
VDBMSC = DriverManager.getConnection(ConnectionStringNoDB, Username, Password);
VDBMSS = VDBMSC.createStatement();
VDBMSRS = VDBMSS.executeQuery("SHOW DATABASES LIKE 'salesdata'");
if (!VDBMSRS.next())
for (String sql : SQL) {
VDBMSS.executeUpdate(sql);
}
else {
VDBMSS.executeUpdate("USE salesdata;");
VDBMSRS = VDBMSS.executeQuery("SHOW TABLES");
// VDBMSRS.last();
// NumberOfRows = VDBMSRS.getRow();
// if(NumberOfRows!=1)
// {
// if(JOptionPane.showConfirmDialog(null, "Unauthorized change has been found in
// database : termresultcalculator .\nProbably, one or more Tables have been dropped which may
// stop the application to work correctly.\nDo you want to create the database again? ",
// "Change in Database Table", JOptionPane.YES_NO_OPTION,
// JOptionPane.QUESTION_MESSAGE)==JOptionPane.YES_OPTION)
// {
// for(int i = 1; i<5; i++)
// VDBMSS.executeUpdate(SQL[i]);
// JOptionPane.showMessageDialog(null, "Changes have been made.","Success",
// JOptionPane.INFORMATION_MESSAGE);
// }
// }
}
closeVDBMS();
return true;
}
/**
* Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
* student's directory which relates to the course. Caution: vizualisation file will be deleted
* eventhough it also relates to another course if the student is also registered to that course.
* (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
* error occured during deletion, or other exception occured.
*
* @param username student's user name
* @param courseID course id (course number + instructor name)
* @throws InvalidDBRequestException
*/
public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
int count = 0;
// check if student registered to the course
rs =
stmt.executeQuery(
"select * from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (!rs.next())
throw new InvalidDBRequestException("Student is not registered to the course");
// remove student from the course
count =
stmt.executeUpdate(
"delete from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");
// delete the quiz visualization files
rs =
stmt.executeQuery(
"select distinct unique_id, s.test_name from scores s, courseTest t "
+ "where s.test_name = t.test_name "
+ "and course_id = '"
+ courseID
+ "' "
+ "and user_login = '******'");
while (rs.next()) {
deleteVisualization(rs.getString(1), username, rs.getString(2));
count =
stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addstudent: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
private void okActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_okActionPerformed
try {
if (txtuser.getText().equals("") || txtpassword.getText().equals("")) {
JOptionPane.showMessageDialog(this, "Please Enter Username & Password", "Message", WIDTH);
} else { // connect
Class.forName("com.mysql.jdbc.Driver");
Connection conn =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/student_information", "root", "123");
Statement stm = conn.createStatement();
String qry = "select password from login where username = '******' ;";
ResultSet rst = stm.executeQuery(qry);
Statement stm2 = conn.createStatement();
String qry2 = "select state from login where username = '******'; ";
ResultSet rst2 = stm2.executeQuery(qry2);
if (!rst.next()) { // validate username
JOptionPane.showMessageDialog(this, "Invalid Username", "Error", WIDTH);
} else if (rst.getString("password").equals(txtpassword.getText())) { // check password
MainWindow m = new MainWindow(); // open main
m.setVisible(true);
m.lbluser.setText(txtuser.getText());
if (rst2.next() && rst2.getInt("State") == 1) { // block lecturer
m.lblState.setText("Admin");
} else {
m.lblState.setText("Lecturer");
/* MainWindow mw1 = new MainWindow();
mw1.btnStudentm.setVisible(false);
mw1.btnCoursem.setVisible(false);
mw1.btnLecturerm.setVisible(false);
mw1.btnAdmin.setVisible(false);
mw1.lbllec.setVisible(false);*/
}
this.setVisible(false);
txtuser.setText("");
txtpassword.setText("");
} else {
JOptionPane.showMessageDialog(this, "Invalid Password", "Error", WIDTH);
}
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, "Error in Excecution " + e, "Error", WIDTH);
}
} // GEN-LAST:event_okActionPerformed
private void testUpdateLob() throws SQLException {
Connection conn = getConnection();
Statement stat = conn.createStatement();
stat.execute(
"CREATE TABLE object_index " + "(id integer primary key, object other, number integer)");
PreparedStatement prep =
conn.prepareStatement("INSERT INTO object_index (id,object) VALUES (1,?)");
prep.setObject(1, "hello", Types.JAVA_OBJECT);
prep.execute();
ResultSet rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
rs.next();
assertEquals("hello", rs.getObject(1).toString());
stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
rs.next();
assertEquals("hello", rs.getObject(1).toString());
rs.updateInt(2, 1);
rs.updateRow();
rs.close();
stat = conn.createStatement();
rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1");
rs.next();
assertEquals("hello", rs.getObject(1).toString());
conn.close();
}
/**
* This method gets the moments happening after the reference moments, in particular the moments
* that occur in the range ]referenceMoment, referenceMoment + range{
*
* @param referenceMoment the reference moment to be used to as boundary
* @param range how many minutes after we want to capture (remember the ranges are not inclusive
* of the boundaries)
* @return a vector of moments that happened after the referenceMoment
*/
public Vector<Moment> getMomentsAfter(Moment referenceMoment, int range) throws SQLException {
Vector<Moment> before = new Vector<Moment>();
String query =
"SELECT * FROM minute WHERE id > "
+ referenceMoment.getMinute()
+ "AND id < "
+ String.valueOf(Integer.parseInt(referenceMoment.getMinute()) + range);
Statement st = this.connection.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
Moment moment = new Moment();
moment.setMinute(rs.getString("id"));
moment.setDate(rs.getString("date"));
moment.setLocation(rs.getString("location"));
moment.setActivity(rs.getString("activity"));
moment.setImagepath(rs.getString("image_path"));
String queryimages = "SELECT `image-path` FROM image WHERE id='" + moment.getMinute() + "'";
Statement subst = this.connection.createStatement();
ResultSet subrs = subst.executeQuery(queryimages);
while (subrs.next()) {
moment.getImages().add(subrs.getString("image-path"));
}
}
return before;
}
public Moment queryMoment(String image_path) throws SQLException {
Moment moment = new Moment();
String queryminute = "SELECT id FROM image WHERE `image-path`='" + image_path + "'";
Statement st = this.connection.createStatement();
ResultSet rs = st.executeQuery(queryminute);
String minute = "";
while (rs.next()) {
minute = rs.getString("id");
}
String query = "SELECT * FROM minute WHERE id='" + minute + "'";
st = this.connection.createStatement();
rs = st.executeQuery(query);
int counter = 0;
while (rs.next()) {
moment.setMinute(rs.getString("id"));
moment.setDate(rs.getString("date"));
moment.setLocation(rs.getString("location"));
moment.setActivity(rs.getString("activity"));
moment.setImagepath(image_path);
String queryimages = "SELECT `image-path` FROM image WHERE id='" + moment.getMinute() + "'";
Statement subst = this.connection.createStatement();
ResultSet subrs = subst.executeQuery(queryimages);
while (subrs.next()) {
moment.getImages().add(subrs.getString("image-path"));
}
counter++;
}
if (counter > 1)
System.err.println(
"Attention! More than a moment associated to image "
+ image_path
+ ". Returning only the last moment");
return moment;
}
private void testTriggerBeforeSelect() throws SQLException {
Connection conn;
Statement stat;
conn = getConnection("trigger");
stat = conn.createStatement();
stat.execute("drop table if exists meta_tables");
stat.execute("create table meta_tables(name varchar)");
stat.execute(
"create trigger meta_tables_select "
+ "before select on meta_tables call \""
+ TestSelect.class.getName()
+ "\"");
ResultSet rs;
rs = stat.executeQuery("select * from meta_tables");
assertTrue(rs.next());
assertFalse(rs.next());
stat.execute("create table test(id int)");
rs = stat.executeQuery("select * from meta_tables");
assertTrue(rs.next());
assertTrue(rs.next());
assertFalse(rs.next());
conn.close();
if (!config.memory) {
conn = getConnection("trigger");
stat = conn.createStatement();
stat.execute("create table test2(id int)");
rs = stat.executeQuery("select * from meta_tables");
assertTrue(rs.next());
assertTrue(rs.next());
assertTrue(rs.next());
assertFalse(rs.next());
conn.close();
}
}
private void testTriggerAdapter() throws SQLException {
Connection conn;
Statement stat;
conn = getConnection("trigger");
stat = conn.createStatement();
stat.execute("drop table if exists test");
stat.execute("create table test(id int, c clob, b blob)");
stat.execute("create table message(name varchar)");
stat.execute(
"create trigger test_insert before insert, update, delete on test "
+ "for each row call \""
+ TestTriggerAdapter.class.getName()
+ "\"");
stat.execute("insert into test values(1, 'hello', 'abcd')");
ResultSet rs;
rs = stat.executeQuery("select * from test");
rs.next();
assertEquals(10, rs.getInt(1));
stat.execute("update test set id = 2");
rs = stat.executeQuery("select * from test");
rs.next();
assertEquals(20, rs.getInt(1));
stat.execute("delete from test");
rs = stat.executeQuery("select * from message");
assertTrue(rs.next());
assertEquals("+1;", rs.getString(1));
assertTrue(rs.next());
assertEquals("-10;+2;", rs.getString(1));
assertTrue(rs.next());
assertEquals("-20;", rs.getString(1));
assertFalse(rs.next());
stat.execute("drop table test, message");
conn.close();
}
public Formation get(String s) {
try {
Connection connection = DataBaseManager.getConnection();
Statement statement = connection.createStatement();
ResultSet rs =
statement.executeQuery("SELECT * FROM FORMATIONS \n" + "WHERE codeForm = '" + s + "'");
while (rs.next()) {
TeachingUnitManager tum = new TeachingUnitManager();
TeacherManager tm = new TeacherManager();
Formation form =
new Formation(
rs.getString("Title"), rs.getString("Grade"), tm.get(rs.getString("Director")));
ResultSet rstu =
statement.executeQuery(
"SELECT * FROM FORMATIONS_TUS \n" + "WHERE codeForm = '" + s + "'");
while (rstu.next()) {
TeachingUnit tu = tum.get(rstu.getString("codeTU"));
form.addTeachingUnits(tu);
}
DataBaseManager.releaseConnection(connection);
return form;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// Methode für den Insert einer Adresse
private int insertAdresse(final Adresse adr) throws SQLException {
try {
Statement stm = createStatement();
StringBuilder sb = new StringBuilder();
sb.append(
"INSERT INTO dbsys20.Adresse (AdressId, Landesid, plz, strasze, hausnummer) VALUES (");
sb.append("dbsys20.SQAdressId.nextval").append(", ");
sb.append(Integer.toString(adr.getLand().getLandesId())).append(", ");
sb.append("'").append(adr.getPLZ()).append("', ");
sb.append("'").append(adr.getStrasze()).append("', ");
sb.append("'").append(adr.getHausnummer()).append("') ");
String myInsertQuery = sb.toString();
System.out.println(myInsertQuery); // FIXME REMOVE
stm.executeQuery(myInsertQuery);
String select = "Select dbsys20.SQAdressID.currVal from dual";
ResultSet resultSet = stm.executeQuery(select);
if (!resultSet.next()) throw new SQLException("no result");
int adressID = resultSet.getInt(1);
// new Adresse(adressID, adr.getStrasze(), adr.getHausnummer(), adr.getOrt(), adr.getPLZ(),
// adr.getLand());
stm.close();
return adressID;
// con.commit(); must not commit. will be commited in createNewUser
} catch (SQLException e) {
System.err.println("Exception while inserting new adress");
throw e;
}
}
public LinkedList<Trigger> listaTrigger(String nombreT, Connection con) throws SQLException {
Statement s = con.createStatement();
LinkedList<Trigger> lT = new LinkedList();
LinkedList<String> nombTrigger = nombreTrigger(nombreT, s);
ResultSet rs;
String disparador = null;
for (int i = 0; i < nombTrigger.size(); i++) {
LinkedList<String> condTrigger = new LinkedList();
rs =
s.executeQuery(
"SELECT event_manipulation,action_timing FROM information_schema.triggers where trigger_name ='"
+ nombTrigger.get(i)
+ "' order by event_manipulation");
int u = 0;
while (rs.next()) {
u++;
}
rs =
s.executeQuery(
"SELECT event_manipulation,action_timing FROM information_schema.triggers where trigger_name ='"
+ nombTrigger.get(i)
+ "' order by event_manipulation");
while (rs.next()) {
disparador = rs.getObject("action_timing").toString();
condTrigger.add(rs.getObject("event_manipulation").toString());
// System.out.println("COND "+nombTrigger.get(i)+" -
// "+rs.getObject("event_manipulation").toString());
}
lT.add(new Trigger(nombTrigger.get(i), condTrigger, disparador));
}
s.close();
return lT;
}
public static void deleteThought(int index) {
try {
java.sql.Connection con = Database.getConnection();
java.sql.Statement s =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
int indext = index + 1;
ResultSet thoughts;
thoughts = s.executeQuery("select * from thoughts");
thoughts.absolute(indext);
thoughts.deleteRow();
int size = aantalID("thoughts");
ResultSet thoughts2;
thoughts2 = s.executeQuery("select * from thoughts");
if (thoughts2.next()) {
while (indext < size) {
thoughts2.absolute(indext);
thoughts2.updateInt("id", indext);
thoughts2.updateRow();
indext++;
thoughts2.next();
}
}
} catch (SQLException ex) {
Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static void createCustomerClass(Connection connection) throws SQLException {
String sql =
"insert "
+ "into customer_class "
+ " (cust_class_name, rec_create_by, rec_create_datetime, rec_update_by, rec_update_datetime, site_id, system_record) "
+ "values ('Regular', 'admin', now(), 'admin', now(), ?, 'Y')";
PreparedStatement insertStatement = connection.prepareStatement(sql);
Statement select = connection.createStatement();
sql =
"select site_id "
+ "from site "
+ "where site_id != '_system' "
+ "and system_record = 'Y'";
ResultSet result = select.executeQuery(sql);
while (result.next()) {
String siteId = result.getString("site_id");
sql = "select count(*) " + "from customer_class " + "where site_id = '" + siteId + "'";
Statement countStatement = connection.createStatement();
ResultSet customerClassResult = countStatement.executeQuery(sql);
customerClassResult.first();
int count = customerClassResult.getInt(1);
if (count == 0) {
insertStatement.setString(1, siteId);
insertStatement.executeUpdate();
}
}
}
public static void deleteProject(int id) {
try {
java.sql.Connection con = Database.getConnection();
java.sql.Statement s =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet projects;
projects = s.executeQuery("select * from projects");
projects.absolute(id);
projects.deleteRow();
int size = aantalID("projects");
projects = s.executeQuery("select * from projects");
if (projects.next()) {
while (id < size) {
projects.absolute(id);
projects.updateInt("id", id);
projects.updateRow();
id++;
projects.next();
}
}
} catch (SQLException ex) {
Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void updateOptionPrice(
String modelName, String optionSetName, String optionName, float newPrice) {
try {
int autoid = 0;
String sql = "select id from automobile where name ='" + modelName + "';";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
autoid = rs.getInt("id"); // get auto_id
}
int opsid = 0;
sql =
"select id from optionset where name ='"
+ optionSetName
+ "' and auto_id= "
+ autoid
+ ";";
rs = statement.executeQuery(sql);
while (rs.next()) {
opsid = rs.getInt("id"); // get option_id
}
sql =
"update options set price = "
+ newPrice
+ " where name= '"
+ optionName
+ "' and option_id= "
+ opsid
+ ";";
statement.executeUpdate(sql); // update it with name and option_id
} catch (SQLException e) {
e.printStackTrace();
}
}
public static List<Document> getDocumentsBySerie(String serie, Connection conn) {
Statement stmt;
List<Document> resultat = new ArrayList<Document>();
try {
stmt = conn.createStatement();
int serieId;
ResultSet rs = stmt.executeQuery("SELECT S_ID FROM SERIE WHERE NOM=\"" + serie + "\"");
rs.next();
serieId = rs.getInt(1);
rs =
stmt.executeQuery(
"SELECT * FROM IMAGE D WHERE EXISTS(SELECT * FROM IMSERIE I WHERE I.I_ID = D.I_ID AND S_ID="
+ serieId
+ ")");
while (rs.next()) {
resultat.add(
new Document(
rs.getInt("I_ID"),
rs.getDate("I_DATE"),
rs.getInt("SIZE"),
rs.getString("NOM"),
rs.getString("CHEMIN"),
rs.getInt("WIDTH"),
rs.getInt("HEIGHT"),
rs.getInt("NOTE"),
rs.getString("DESCRIPTION")));
}
} catch (SQLException e) {
e.printStackTrace();
}
return resultat;
}
@Test
public void testImport() throws Exception {
MemoryDatabase memDb = new MemoryDatabase("sample");
memDb.start();
final String jsonFileName = "/sample.json";
memDb.importJSON(getClass(), jsonFileName);
JSONParser jsonParser = new JSONParser();
JSONArray tables =
(JSONArray)
jsonParser.parse(new InputStreamReader(getClass().getResourceAsStream(jsonFileName)));
assertEquals(2, tables.size());
Connection conn = memDb.getConnection();
Statement stmt = conn.createStatement();
JSONObject employee = (JSONObject) tables.get(0);
ResultSet rs = stmt.executeQuery("SELECT * FROM \"employee\"");
verifyTableEquals(employee, rs);
rs.close();
JSONObject team = (JSONObject) tables.get(1);
rs = stmt.executeQuery("SELECT * FROM \"team\"");
verifyTableEquals(team, rs);
rs.close();
stmt.close();
conn.close();
memDb.stop();
}
public static Document executeSelectDocument(Connection conn, int id) {
Statement stmt;
Document d = new Document();
try {
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM IMAGE WHERE I_ID=" + id);
rs.next();
d =
new Document(
rs.getInt("I_ID"),
rs.getDate("I_DATE"),
rs.getInt("SIZE"),
rs.getString("NOM"),
rs.getString("CHEMIN"),
rs.getInt("WIDTH"),
rs.getInt("HEIGHT"),
rs.getInt("NOTE"),
rs.getString("DESCRIPTION"));
rs = stmt.executeQuery("SELECT NOM FROM IMTAG I, TAG T WHERE I.T_ID=T.T_ID AND I.I_ID=" + id);
while (rs.next()) {
d.addTag(rs.getString("NOM"));
}
rs =
stmt.executeQuery(
"SELECT NOM FROM IMSERIE I, SERIE S WHERE I.S_ID=S.S_ID AND I.I_ID=" + id);
while (rs.next()) {
d.addSerie(rs.getString("NOM"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return d;
}
private void testColumnLength() throws SQLException {
trace("testColumnDisplayLength");
ResultSet rs;
ResultSetMetaData meta;
stat.execute("CREATE TABLE one (ID INT, NAME VARCHAR(255))");
rs = stat.executeQuery("select * from one");
meta = rs.getMetaData();
assertEquals("ID", meta.getColumnLabel(1));
assertEquals(11, meta.getColumnDisplaySize(1));
assertEquals("NAME", meta.getColumnLabel(2));
assertEquals(255, meta.getColumnDisplaySize(2));
stat.execute("DROP TABLE one");
rs = stat.executeQuery("select 1, 'Hello' union select 2, 'Hello World!'");
meta = rs.getMetaData();
assertEquals(11, meta.getColumnDisplaySize(1));
assertEquals(12, meta.getColumnDisplaySize(2));
rs = stat.executeQuery("explain select * from dual");
meta = rs.getMetaData();
assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
assertEquals(Integer.MAX_VALUE, meta.getPrecision(1));
rs = stat.executeQuery("script");
meta = rs.getMetaData();
assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
assertEquals(Integer.MAX_VALUE, meta.getPrecision(1));
rs = stat.executeQuery("select group_concat(table_name) " + "from information_schema.tables");
rs.next();
meta = rs.getMetaData();
assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
assertEquals(Integer.MAX_VALUE, meta.getPrecision(1));
}
// Checken ob ein User an der Reihe ist in diesem Spiel.
private boolean isUserNextPlayer(int userID, int gameID) {
if (!isUserInGame(userID, gameID)) {
return false;
}
Connection con = DatabaseConnector.getConnection();
Statement stmt;
ResultSet rs;
try {
stmt = con.createStatement();
// Checken ob Spieler am Spiel teilnimmt
int nextStationType = -1, stationType = -2;
rs = stmt.executeQuery("SELECT next_station_type FROM games WHERE game_id=" + gameID);
if (rs.next()) {
nextStationType = rs.getInt("next_station_type");
con.commit();
}
rs =
stmt.executeQuery(
"SELECT station_type FROM stations NATURAL JOIN users WHERE game_id="
+ gameID
+ " AND user_id="
+ userID);
if (rs.next()) {
stationType = rs.getInt("station_type");
con.commit();
}
if (nextStationType == stationType) return true;
else return false;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
@Override
public void pushToDB(Connection con) {
Statement stmt;
try {
stmt = con.createStatement();
StringBuilder sqlString = new StringBuilder("INSERT INTO question_response VALUES(null,");
sqlString.append(statement);
sqlString.append("\",\" ");
for (String string : answers) {
sqlString.append(string);
sqlString.append(" &&& ");
}
sqlString.replace(sqlString.length() - 5, sqlString.length(), "");
sqlString.append("\" ");
System.out.print(sqlString.toString());
ResultSet resultSet = stmt.executeQuery(sqlString.toString());
stmt = con.createStatement();
sqlString = new StringBuilder("SELECT * FROM question_response WHERE statement=\"");
sqlString.append(statement);
sqlString.append("\" ");
System.out.print(sqlString.toString());
resultSet = stmt.executeQuery(sqlString.toString());
while (resultSet.next()) {
this.setqID(resultSet.getInt("question_id")); // will always be the last one
}
} catch (Exception e) {
}
}
public int miseAJourStatutCommande() {
ConnexionBD bd = new ConnexionBD();
Statement st = bd.createStatement();
int rs = 0;
try {
// on regarde combie de colis à la commande correspondant au colis controle
ResultSet i =
st.executeQuery(
"SELECT id_commande, count(id_colis) FROM "
+ Constantes.base_colis
+ " WHERE id_colis = "
+ id
+ " GROUP BY id_commande;");
if (i.next()) {
int numCommande = i.getInt(1);
int nbColis = i.getInt(2);
// i.close();
// on récupère les colis donc le statut à été controle, cad passer par la douane et approuvé
ResultSet g =
st.executeQuery(
" SELECT id_colis FROM "
+ Constantes.base_colis
+ " WHERE id_commande = "
+ numCommande
+ " AND statut IN ('controle', 'emballe') GROUP BY id_colis;");
if (g.next()) {
g.last();
int nombreColis = g.getRow();
g.beforeFirst();
// si le nombre de colis de la commande est égal au nombre de colis controle de la
// commande, on peut changer le statut de la commande a expedie
if (nbColis == nombreColis) {
rs =
st.executeUpdate(
"UPDATE "
+ Constantes.base_commande
+ " SET statut = 'expediee' WHERE id_commande = "
+ numCommande
+ ";");
}
} else {
System.out.println("NOOOON");
}
} else {
System.out.println("RIEEEEN");
}
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}