准备util工具包，数据库链接设置，数据库操作工具类

1 package com.util; 2 //数据库设置 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 9 /** 10 * 数据库驱动连接类 11 * @author ZBK 12 */ 13 public class DBHelper { 14 /** 15 * 数据库用户名 16 */ 17 public static final String USERNAME = "test"; 18 /** 19 * 数据库密码 20 */ 21 public static final String PASSWORD = "test"; 22 /** 23 * 数据库驱动类 24 */ 25 public static final String DRIVER = "oracle.jdbc.OracleDriver"; 26 /** 27 * 数据库地址URL 28 */ 29 public static final String URL = "jdbc:oracle:thin:@localhost:1521:xe"; 30 31 /** 32 * 获取数据库连接 33 * @return 34 */ 35 public static Connection getConnection() { 36 Connection conn = null; 37 try { 38 Class.forName(DRIVER); 39 conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); 40 } catch (ClassNotFoundException e) { 41 e.printStackTrace(); 42 } catch (SQLException e) { 43 e.printStackTrace(); 44 } 45 return conn; 46 } 47 48 /** 49 * 释放资源 50 * @param conn 数据库连接对象 51 * @param sm Statement对象 52 * @param rs ResultSet结果集对象 53 */ 54 public static void destroy(Connection conn, Statement sm, ResultSet rs) { 55 if (conn != null) { 56 try { 57 conn.close(); 58 } catch (SQLException e) { 59 e.printStackTrace(); 60 } 61 conn = null; 62 } 63 if (sm != null) { 64 try { 65 sm.close(); 66 } catch (SQLException e) { 67 e.printStackTrace(); 68 } 69 sm = null; 70 } 71 if (rs != null) { 72 try { 73 rs.close(); 74 } catch (SQLException e) { 75 e.printStackTrace(); 76 } 77 rs = null; 78 } 79 } 80 81 /** 82 * 验证前台传入的参数是否为空 83 * @param args 84 * @return 85 */ 86 public static boolean checkParam(String... args) { 87 for (String s : args) { 88 if (s == null || s.trim().length() < 1) { 89 return false; 90 } 91 } 92 return true; 93 } 94 } 1 package com.util; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.util.ArrayList; 8 import java.util.List; 9 10 import com.model.Area; 11 import com.model.City; 12 import com.model.Province; 13 14 public class DataBaseUtil { 15 private Connection conn; 16 private PreparedStatement ps; 17 private ResultSet rs; 18 19 /** 20 * 初始化数据库链接 21 */ 22 public void init(String sql) { 23 conn = DBHelper.getConnection(); 24 try { 25 ps = conn.prepareStatement(sql); 26 } catch (SQLException e) { 27 e.printStackTrace(); 28 } 29 } 30 31 /** 32 * 查询所有省信息 33 * 34 */ 35 public List<Province> SelectAllPro() { 36 String sql = "select * from C_PROVINCES t"; 37 init(sql); 38 List<Province> plist = null; 39 try { 40 rs = ps.executeQuery(); 41 if(rs != null) { 42 plist = new ArrayList<Province>(); 43 while(rs.next()) { 44 Province p = new Province(); 45 p.setId(rs.getInt("id")); 46 p.setProvince(rs.getString("province")); 47 p.setProvinceid(rs.getString("provinceid")); 48 plist.add(p); 49 } 50 } 51 } catch (SQLException e) { 52 e.printStackTrace(); 53 } 54 55 return plist; 56 } 57 58 public List<City> SelectCity(String provinceid) { 59 String sql = "select * from C_CITIES t where t.provinceid=?"; 60 init(sql); 61 List<City> clist = null; 62 try { 63 ps.setString(1, provinceid); 64 rs = ps.executeQuery(); 65 if(rs!=null) { 66 clist = new ArrayList<City>(); 67 while(rs.next()) { 68 City city = new City(); 69 city.setId(rs.getInt("id")); 70 city.setCity(rs.getString("city")); 71 city.setCityid(rs.getString("cityid")); 72 city.setProvinceid(rs.getString("provinceid")); 73 clist.add(city); 74 } 75 } 76 } catch (SQLException e) { 77 e.printStackTrace(); 78 } 79 80 return clist; 81 } 82 83 public List<Area> SelectArea(String cityid) { 84 String sql = "select * from C_AREAS t where t.cityid=?"; 85 init(sql); 86 List<Area> alist = null; 87 try { 88 ps.setString(1, cityid); 89 rs = ps.executeQuery(); 90 if(rs!=null) { 91 alist = new ArrayList<Area>(); 92 while(rs.next()) { 93 Area a = new Area(); 94 a.setId(rs.getInt("id")); 95 a.setArea(rs.getString("area")); 96 a.setAreaid(rs.getString("areaid")); 97 a.setCityid(rs.getString("cityid")); 98 alist.add(a); 99 } 100 } 101 } catch (SQLException e) { 102 e.printStackTrace(); 103 } 104 105 return alist; 106 } 107 }