合作人:肖成龙
设计思想:
数据库建立了两张表,一张将所有的地铁线路信息储存进去,
还有一张用来存储六条地铁之间的连通节点。
通过连通性,判断是否需要换乘线路。
预估时间:一周
源程序代码:
数据库代码:
Dbutil.java
package com.hdq.util;
import java.sql.Connection;
import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * 数据库连接工具 * @author Hu * */ public class DBUtil { public static String db_url = "jdbc:mysql://localhost:3306/studentlist"; public static String db_user = "root"; public static String db_pass = "password"; public static Connection getConn () { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver");//加载驱动 conn = DriverManager.getConnection(db_url, db_user, db_pass); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 关闭连接 * @param state * @param conn */ public static void close (Statement state, Connection conn) { if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (ResultSet rs, Statement state, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) throws SQLException { // Connection conn = getConn(); // PreparedStatement pstmt = null; // ResultSet rs = null; // String sql ="select * from course"; // pstmt = conn.prepareStatement(sql); // rs = pstmt.executeQuery(); // if(rs.next()){ // System.out.println("空"); // }else{ // System.out.println("不空"); // } } }
ClassService.java
package com.hdq.service;
import java.util.List;
import com.hdq.dao.ClassDao; /** * CourseService * 服务层 * @author HDQ * */ public class ClassService { ClassDao cDao = new ClassDao(); /** * 添加 * @param course * @return */ public boolean add(String table,String strList[],String strList1[]) { boolean f = cDao.add(table,strList,strList1); return f; } /** * 删除 */ public boolean del(String table,String qian,String hou) { return cDao.delete(table,qian,hou); } /** * 修改 * @return */ public boolean update(String table,String []strlist,String []strlist1,String qian,String hou) { return cDao.update(table,strlist,strlist1,qian,hou); } /** * 查找 * @return * @throws IllegalAccessException * @throws InstantiationException */ public <T> List<T> search(String table, String []strList, String []strList1,Class<T> clazz) throws InstantiationException, IllegalAccessException { return cDao.search(table,strList,strList1,clazz); } /** * 由时间查找 * @return * @throws IllegalAccessException * @throws InstantiationException */ public <T> List<T> searchByTime(String table, String []strList, String []strList1,String biaoshi,String qian,String hou,Class<T> clazz) throws InstantiationException, IllegalAccessException { return cDao.searchByTime(table, strList, strList1, biaoshi, qian, hou, clazz); } /** * 全部数据 * @return * @throws IllegalAccessException * @throws InstantiationException * @throws ClassNotFoundException */ public <T> List<T> list(String table,String []strList,Class<T> clazz) throws ClassNotFoundException, InstantiationException, IllegalAccessException { return cDao.list(table,strList,clazz); } /** * 创建数据库表单 * @return * @throws IllegalAccessException * @throws InstantiationException */ public boolean createTable(String table,String []info,String []type,int []size) { return cDao.createTable(table, info, type, size); } }
Linenum.java
package com.hdq.entity;
public class Linenum {
int linenum; public int getLinenum() { return linenum; } public void setLinenum(int linenum) { this.linenum = linenum; } }
Lineinfo.java
package com.hdq.entity;
public class LineInfo {
int linenum; String name; public int getLinenum() { return linenum; } public void setLinenum(int linenum) { this.linenum = linenum; } public String getName() { return name; } public void setName(String name) { this.name = name; } public LineInfo() { linenum=-1; } }
ClassDao.java
package com.hdq.dao;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.hdq.util.DBUtil; /** * 通用类Dao * Dao层操作数据 * @author HDQ * */ public class ClassDao { /** * 添加 * @return */ public <T> boolean add(String table,String []strList,String []strList1) { if(strList.length==0) return false; String sql = "insert into "+table+"("; for(int i=0;i<strList.length;i++) { if(i!=strList.length-1) sql+=strList[i]+","; else sql+=strList[i]+")"; } sql+=" values('"; for(int i=0;i<strList1.length;i++) { if(i!=strList1.length-1) sql+=strList1[i]+"','"; else sql+=strList1[i]+"')"; } //创建数据库链接 Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); a=state.executeUpdate(sql); } catch (Exception e) { e.printStackTrace(); } finally { //关闭连接 DBUtil.close(state, conn); } if (a > 0) { f = true; } return f; } /** * 删除 * * @return */ public boolean delete (String table,String zhixing,String biaoshi) { boolean f = false; String sql = "delete from "+table+" where "+zhixing+"='" + biaoshi + "'"; Connection conn = DBUtil.getConn(); Statement state = null; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(state, conn); } if (a > 0) { f = true; } return f; } /** * 修改 * @param pass */ public boolean update(String table,String []strlist,String []strlist1,String qian,String hou) { String sql = "update "+table+" set "; for(int i=0;i<strlist.length;i++) { if(i!=strlist.length-1) sql+=strlist[i]+"='" + strlist1[i] + "',"; else sql+=strlist[i]+"='" + strlist1[i] + "' where "+qian+"='" + hou + "'"; } Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(state, conn); } if (a > 0) { f = true; } return f; } /** * 验证通用类名称是否唯一 * true --- 不唯一 * @return */ public boolean name(String table,String zhi,String weiyi) { boolean flag = false; String sql = "select "+zhi+" from "+table+" where "+zhi+" = '" + weiyi + "'"; Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return flag; } /** * 查找 * @return * @throws IllegalAccessException * @throws InstantiationException */ @SuppressWarnings("deprecation") public <T> List<T> search(String table,String []strList,String []strList1,Class<T> clazz) throws InstantiationException, IllegalAccessException { String sql = "select * from "+table; int i=0,k=0; for(String it:strList1) { if(it!=null&&!it.equals("")) { if(k==0) sql +=" where "+ strList[i]+" like '%" + it + "%'"; else sql +=" and "+ strList[i]+" like '%" + it + "%'"; ++k; } ++i; } List<T> list = new ArrayList<>(); Connection conn = DBUtil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); T bean = null; while (rs.next()) { bean=clazz.newInstance(); for(String it:strList) { Field fs=getDeclaredField(bean, it); if(fs==null){ throw new IllegalArgumentException("Could not find field["+