View Javadoc

1   /*
2    *  $Id: CoursePeer.java,v 1.10 2004/07/25 14:12:21 roku Exp $ 
3    *
4    *  Copyright 2004 University of Hannover
5    *
6    *  Licensed under the Apache License, Version 2.0 (the "License");
7    *  you may not use this file except in compliance with the License.
8    *  You may obtain a copy of the License at
9    *
10   *      http://www.apache.org/licenses/LICENSE-2.0
11   *
12   *  Unless required by applicable law or agreed to in writing, software
13   *  distributed under the License is distributed on an "AS IS" BASIS,
14   *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15   *  See the License for the specific language governing permissions and
16   *  limitations under the License.
17   */
18  
19  package olr.om;
20  
21  import java.sql.Connection;
22  import java.sql.ResultSet;
23  import java.util.ArrayList;
24  import java.util.List;
25  
26  import olr.rdf.Definitions;
27  import olr.rdf.OLR3Definitions;
28  
29  import org.apache.log4j.Logger;
30  
31  public final class CoursePeer extends RdfStatementPeer
32  {
33      public static List getAllCourses() 
34      {        
35          Connection dbConn = null;
36          final List courses = new ArrayList();
37          
38          try {
39              dbConn = DBUtils.getConnection();
40              String sql = "SELECT ms.model_id AS modelID, n.ns_name||r.ro_name AS course, '' AS name, s.id "
41                            +"FROM model_statement ms, rdf_statement s, rdf_resource r, rdf_namespace n "
42                            +"WHERE ms.statement_id=s.id and s.subject=r.id and n.id=r.ns " 
43                            +"and s.predicate in (select r2.id from rdf_resource r2, rdf_namespace n2 "
44                                + "where n2.id=r2.ns and r2.ro_name='"+Definitions.RDF_TYPE_NAME+"' and n2.ns_name='"+Definitions.RDF_NS +"') "
45                            +"and s.obj_resource in (select r3.id from rdf_resource r3, rdf_namespace n3 "
46                                + "where n3.id=r3.ns and r3.ro_name='Course'"
47                                +" and n3.ns_name='"+OLR3Definitions.OLR3_NS +"') " +
48                            "ORDER BY modelID, s.id";            
49              ResultSet rs = dbConn.createStatement().executeQuery(sql);
50  
51              while(rs.next())
52                  try {
53                      courses.add(new Course(rs));
54                  }
55                  catch(Exception e) {
56                      Logger.getLogger(CoursePeer.class).error(e);
57                  }
58  
59              rs.close();
60          }
61          catch(Exception e) {
62              Logger.getLogger(CoursePeer.class).error(e);
63          }
64          DBUtils.releaseConnection(dbConn);
65          setCourseNames(courses);
66  
67          return courses;
68      }
69  
70      private static void setCourseNames(final List courses) {
71          final List names = new ArrayList(courses.size());
72          
73          Connection dbConn = null;
74          StringBuffer in = new StringBuffer(courses.size()*6);
75          in.append("(");
76          
77          for(int i=0; i<courses.size(); i++)
78              in.append("'").append(((Course)courses.get(i)).getRelativeUrl()).append("',");
79          in.setCharAt(in.length()-1, ')');        
80          
81          try {
82              dbConn = DBUtils.getConnection();
83              final StringBuffer sql = new StringBuffer("SELECT DISTINCT l.literal AS name, ms.model_id, s.id FROM rdf_literal l,model_statement ms , rdf_statement s, rdf_namespace n WHERE l.id=s.obj_literal AND ms.statement_id=s.id AND s.subject IN (SELECT r2.id FROM rdf_resource r2 WHERE ro_name IN ").append(in).append(") AND s.predicate IN (SELECT r3.id FROM rdf_resource r3, rdf_namespace n3 WHERE r3.ro_name='label' AND n3.ns_name='http://www.w3.org/2000/01/rdf-schema#') ORDER BY ms.model_id, s.id");
84              final ResultSet rs = dbConn.createStatement().executeQuery(sql.toString());
85              DBUtils.releaseConnection(dbConn);
86              
87              for(int i=0; rs.next(); i++)
88                  try {
89                      assert(i < courses.size());
90                      ((Course)courses.get(i)).setName(rs.getString("name"));
91                  }
92                  catch(Exception e) {
93                      Logger.getLogger(CoursePeer.class).error(e);
94                  }
95              rs.close();
96          }
97          catch(Exception e) {
98              Logger.getLogger(CoursePeer.class).error(e);
99          }        
100     }
101 
102     public static int getModelIDForCourse(String course) {
103         
104         Connection dbConn = null;
105         int modelID = 0;
106         
107         try {
108             dbConn = DBUtils.getConnection();
109             final StringBuffer sql = new StringBuffer("SELECT DISTINCT ms.model_id FROM model_statement ms, rdf_statement s, rdf_namespace n, rdf_resource r WHERE ms.statement_id=s.id AND s.subject=r.id AND r.ns=n.id AND n.ns_name||r.ro_name='").append(course).append("'");
110             ResultSet rs = dbConn.createStatement().executeQuery(sql.toString());
111             DBUtils.releaseConnection(dbConn);
112             
113             if(rs.next())
114                 try {
115                     modelID = rs.getInt("model_id");
116                 }
117                 catch(Exception e) {
118                     Logger.getLogger(CoursePeer.class).error(e);
119                 }
120             rs.close();
121         }
122         catch(Exception e) {
123             Logger.getLogger(CoursePeer.class).error(e);
124         }
125         return modelID;
126     }
127 }