qmapper
Project home

 

   Connection con = null;
   ResultSet rs= null;
   PreparedStatment stmt=  null;
   try {
      con = new Connection(url,driver,username,password);
      String sql = "SELECT a.id,a.subject,a.message,a.creationDate,b.username" +
            "FROM blogs as a, user_profiles as b "+ 
		 "WHERE a.user_id=b.user_id AND b.username=?";
      stmt = con.prepareStatment(sql);
      stmt.setParam(1, pUsername);
      rs = stmt.execute();
      ArrayList blogs = new ArrayList();
      While (rs.next()) {
         Blog blog  = new Blog();
         blog.setId(rs.getInt(1));
         blog.setSubject(rs.getString(2));
         blog.setMessage(rs.getString(3));
         blog.setCreationDate(rs.getDate(4));
         User user = new User();
         user.setUsername(rs.getString(5));
         blog.setAuthor(user);
         blogs.add(blog);
      }
      return blogs;
   }
   catch (Exception e) {
      e.printStackTrace();
      return null;
   }
   finally {
      try { if (con != null) con.close(); } catch (Exception ignored) {}
      try { if (stmt != null) stmt.close();} catch (Exception ignored) {}
      try { if (rs != null) rs.close(); } catch (Exception ignored) {}
   }
 

   QueryManager manager = new QueryManager("D:/xmlconfig.xml");
   ArrayList params = new ArrayList();
   params.add("livejavabean");
   ArrayList blogs = (ArrayList)manager.executeQuery("get_user_blogs",params);
   for(int i=0; i<blogs.size(); i++) {
      Blog blog = (Blog) blogs.get(i);
      System.out.println(blog.getId());
      System.out.println(blog.getAuthor().getUsername());
      System.out.println(blog.getAuthor().getName().getFirstName());
      System.out.println(blog.getAuthor().getName().getLastName());
   }
 

<?xml version="1.0"
<encoding="UTF-8"?>
<bean-query>
   <connections>
      <connection name="ljb">
         <url>jdbc:mysql://localhost/livejavabean</url>
         <driver>org.gjt.mm.mysql.Driver</driver>
         <username></username>
         <password></password>
      </connection>
   </connections>
   <queries>
      <query name="get_user_blogs" connection="ljb" cache="true" preload="false">
         <sql><![CDATA[
            SELECT 
               a.id,
               a.subject,
               a.message,
               a.creationDate,
               b.username,
               b.firstName,
               b.lastName
               b.email,
            FROM 
               blogs as a,
               user_profiles as b 
            WHERE 
               a.user_id=b.user_id AND 
               b.username=?";
         ]]></sql> 
         <bean-mapping class="org.sifut.blog.Blog" return-as-list="false">
            <property>id</property> 
            <property>subject</property>
            <property>message</property>
            <property>creationDate</property>
            <property>author.username</property>
            <property>author.name.firstName</property>
            <property>author.name.lastName</property>
            <property>author.email</property>
         </bean-mapping>
      </query>
   </queries>
</bean-query>