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; }