List of usage examples for org.apache.commons.dbutils QueryRunner QueryRunner
public QueryRunner(DataSource ds)
DataSource
to use. From source file:it.itis.pertini.falessi.tunes.jdbc.QueryRunnerProvider.java
/** * {@inheritDoc} */ public QueryRunner get() { return new QueryRunner(dataSource); }
From source file:cn.itcast.bbs.dao.ReplyDao.java
public int countReplyByTopic(int id) throws SQLException { QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); String sql = "select count(*) from reply where topic_id = ?;"; Long cnt = runner.query(sql, new ScalarHandler(), id); return cnt.intValue(); }
From source file:cn.itcast.bbs.dao.TopicDao.java
public int countTopicByType(int id) throws SQLException { QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); String sql = "select count(*) from topic where type_id = ?;"; Long cnt = runner.query(sql, new ScalarHandler(), id); return cnt.intValue(); }
From source file:jongo.demo.Demo.java
private static void generateDemoDatabase(final DatabaseConfiguration dbcfg) { final String database = dbcfg.getDatabase(); QueryRunner run = new QueryRunner(JDBCConnectionFactory.getDataSource(dbcfg)); l.info("Generating Demo resources in database {}", database); update(run, getCreateAuthTable());//from w w w . jav a2s.c o m update(run, getCreateUserTable()); update(run, getCreateMakersTable()); update(run, getCreateCarsTable()); update(run, getCreateCommentsTable()); update(run, getCreatePicturesTable()); update(run, getCreateSalesStatsTable()); update(run, getCreateSalesByMakerAndModelStatsTable()); update(run, getCreateEmptyTable()); l.info("Generating Demo Data in database {}", database); final String insertAuthQuery = "INSERT INTO jongo_auth (email, password) VALUES (?,?)"; update(run, insertAuthQuery, "a@a.com", JongoUtils.getHashedPassword("123456")); update(run, insertAuthQuery, "a@b.com", JongoUtils.getHashedPassword("This is a test")); final String insertUserQuery = "INSERT INTO users (name, age, birthday, credit) VALUES (?,?,?,?)"; update(run, insertUserQuery, "foo", 30, "1982-12-13", 32.5); update(run, insertUserQuery, "bar", 33, "1992-01-15", 0); for (CarMaker maker : CarMaker.values()) { update(run, "INSERT INTO maker (name, realname) VALUES (?,?)", maker.name(), maker.getRealName()); } final String insertCar = "INSERT INTO car (maker, model, year, fuel, transmission, currentMarketValue, newValue) VALUES (?,?,?,?,?,?,?)"; update(run, insertCar, "CITROEN", "C2", 2008, "Gasoline", "Manual", 9000, 13000); update(run, "INSERT INTO car (maker, model, year, transmission, currentMarketValue, newValue) VALUES (?,?,?,?,?,?)", "FIAT", "500", 2010, "Manual", 19000, 23.000); update(run, insertCar, "BMW", "X5", 2011, "Diesel", "Automatic", 59000, 77000); final String insertComment = "INSERT INTO comments (car_id, car_comment) VALUES (?,?)"; update(run, insertComment, 0, "The Citroen C2 is a small car with a great attitude"); update(run, insertComment, 0, "I Love my C2"); update(run, insertComment, 2, "BMW's X5 costs too much for what it's worth. Checkout http://www.youtube.com/watch?v=Bg1TB4dRobY"); final String insertPicture = "INSERT INTO pictures (car_id, picture) VALUES (?,?)"; update(run, insertPicture, 0, "http://www.babez.de/citroen/c2/picth01.jpg"); update(run, insertPicture, 0, "http://www.babez.de/citroen/c2/pic02.jpg"); update(run, insertPicture, 0, "http://www.babez.de/citroen/c2/picth03.jpg"); update(run, insertPicture, 1, "http://www.dwsauto.com/wp-content/uploads/2008/07/fiat-500-photo.jpg"); update(run, insertPicture, 1, "http://www.cochesadictos.com/coches/fiat-500/imagenes/index1.jpg"); update(run, insertPicture, 1, "http://www.cochesadictos.com/coches/fiat-500/imagenes/index4.jpg"); update(run, insertPicture, 2, "http://www.coches21.com/fotos/100/bmw_x5_457.jpg"); update(run, insertPicture, 2, "http://www.coches21.com/fotos/100/bmw_x5_460.jpg"); update(run, insertPicture, 2, "http://www.coches21.com/modelos/250/bmw_x5_65.jpg"); // generate some random data for the stats page DateTimeFormatter isofmt = ISODateTimeFormat.dateTime(); DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.SSSSSSSSS"); DateTime dt;// = isofmt.parseDateTime("2012-01-16T13:34:00.000Z"); for (int year = 2000; year < 2012; year++) { for (int month = 1; month <= 12; month++) { int val = 1910 + new Random().nextInt(100); dt = isofmt.parseDateTime(year + "-" + month + "-01T01:00:00.000Z"); update(run, "INSERT INTO sales_stats (year, month, sales, last_update) VALUES (?,?,?,?)", year, month, val, fmt.print(dt)); for (CarMaker maker : CarMaker.values()) { val = new Random().nextInt(100); update(run, "INSERT INTO maker_stats (year, month, sales, maker, last_update) VALUES (?,?,?,?,?)", year, month, val, maker.name(), fmt.print(dt)); } } } update(run, "SET TABLE maker READONLY TRUE"); //load the sp update(run, "CREATE FUNCTION simpleStoredProcedure () RETURNS TINYINT RETURN 1"); update(run, "CREATE PROCEDURE insert_comment (IN car_id INTEGER, IN car_comment VARCHAR(255)) MODIFIES SQL DATA INSERT INTO comments VALUES (DEFAULT, car_id, car_comment)"); update(run, "CREATE PROCEDURE get_year_sales (IN in_year INTEGER, OUT out_total INTEGER) READS SQL DATA SELECT COUNT(sales) INTO out_total FROM sales_stats WHERE year = in_year"); update(run, getCreateView()); }
From source file:com.mmone.hsqldb.Database.java
private void setup() throws SQLException { JDBCDataSource ds = new JDBCDataSource(); ds.setDatabase("jdbc:hsqldb:file:" + dbParh); conn = ds.getConnection("sa", ""); if (conn == null) { System.out.println("conn not created"); }/*from www.j a v a2 s.c om*/ qr = new QueryRunner(ds); }
From source file:com.geaviation.controller.LoginController.java
protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception { ModelAndView mv = new ModelAndView(); DataSource ds = (DataSource) this.getApplicationContext().getBean("myDataSource"); try {//from w ww. ja v a 2 s . com String username = request.getParameter("username"); String password = request.getParameter("password"); //String status = "FAILURE"; QueryRunner run = new QueryRunner(ds); ResultSetHandler<UsersBean> user = new BeanHandler<UsersBean>(UsersBean.class); Object[] params = new Object[2]; params[0] = username; params[1] = password; UsersBean ub = run.query("select * from user_account where UNAME =? and UPASSWORD =?", user, params); if (ub != null) { mv.addObject("username", username); mv.setViewName("home"); } else { mv.addObject("error", "true"); mv.setViewName("index"); } } catch (Exception e) { e.printStackTrace(); } return mv; }
From source file:com.example.data.StoreData.java
public void placeOrder(BindObject bindObj) throws SQLException { deleteOrder(bindObj.get("id")); QueryRunner run = new QueryRunner(H2DB.getDataSource()); run.update("insert into order(id, pet_id, quantity, ship_date, status) " + "values(?, ?, ?, ?, ?)", bindObj.get("id"), bindObj.get("petId"), bindObj.get("quantity"), bindObj.get("shipDate"), bindObj.get("status")); }
From source file:cn.itcast.bbs.dao.TypeDao.java
public Type findTypeById(int id) throws SQLException { QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); String sql = "select *from type where id=?;"; Type type = runner.query(sql, new BeanHandler(Type.class), id); return type;//from w w w . j a v a2 s . c om }
From source file:iudex.da.Helper.java
@Before public void clear() throws SQLException { QueryRunner runner = new QueryRunner(_dataSource); runner.update("DELETE from urls;"); }
From source file:com.example.data.UserData.java
public void addUser(BindObject bindObj) throws SQLException { QueryRunner run = new QueryRunner(H2DB.getDataSource()); run.update(//from w w w. j a va 2 s .c o m "insert into user(id, user_name, first_name, last_name, email, password, phone, status) " + "values(?, ?, ?, ?, ?, ?, ?, ?)", bindObj.get("id"), bindObj.get("username"), bindObj.get("firstName"), bindObj.get("lastName"), bindObj.get("email"), bindObj.get("password"), bindObj.get("phone"), bindObj.get("status")); }