通过properties配置文件连接数据库

时间:2021-07-05 11:55:42

/**
 * @作者:Jcuckoo
 * @日期:2008-11-8
 * @版本:V 1.0
 */

db.properties

  1. DBDriver=sun.jdbc.odbc.JdbcOdbcDriver
  2. Connection=jdbc:odbc:login
  3. User=Jcuckoo
  4. Password=

dbPool.java

  1. import java.io.*;
  2. import java.util.*;
  3. import java.sql.*;
  4. public class dbPool{
  5.     private static dbPool instance = null;
  6.     //取得连接
  7.     public static synchronized Connection getConnection() {
  8.         if (instance == null){
  9.             instance = new dbPool();
  10.         }
  11.         return instance._getConnection();
  12.     }
  13.     private dbPool(){
  14.         super();
  15.     }
  16.     private  Connection _getConnection(){
  17.         try{
  18.             String sDBDriver  = null;
  19.             String sConnection   = null;
  20.             String sUser = null;
  21.             String sPassword = null;
  22.             Properties p = new Properties();
  23.             InputStream is = getClass().getResourceAsStream("/db.properties");
  24.             p.load(is);
  25.             sDBDriver = p.getProperty("DBDriver",sDBDriver);
  26.             sConnection = p.getProperty("Connection",sConnection);
  27.             sUser = p.getProperty("User","");
  28.             sPassword = p.getProperty("Password","");
  29.             Properties pr = new Properties();
  30.             pr.put("user",sUser);
  31.             pr.put("password",sPassword);
  32.             pr.put("characterEncoding""GB2312");
  33.             pr.put("useUnicode""TRUE");
  34.             Class.forName(sDBDriver).newInstance();
  35.             return DriverManager.getConnection(sConnection,pr);
  36.         }
  37.         catch(Exception se){
  38.             System.out.println(se);
  39.             return null;
  40.         }
  41.     }
  42.     //释放资源
  43.     public static void dbClose(Connection conn,PreparedStatement ps,ResultSet rs)
  44.     throws SQLException
  45.     {
  46.           rs.close();
  47.           ps.close();
  48.           conn.close();
  49.       }
  50.     }
StudentDAO.java

  1. public class StudentDAO {
  2.     public StudentDAO() {
  3.     }
  4.     Connection conn;
  5.     Statement st;
  6.     ResultSet rs;
  7.     public boolean checkLogin(String userName,String userPwd){
  8.         String sql="select * from student where userName='"+userName+"' and userPwd='"+userPwd+"'";
  9.         
  10.         try {
  11.             conn=dbPool.getConnection();
  12.             st=conn.createStatement();
  13.             rs=st.executeQuery(sql);
  14.             if(rs.next()){
  15.                 conn.close();
  16.                 return true;
  17.             }else{
  18.                 conn.close();
  19.                 return false;
  20.             }
  21.         } catch (SQLException e) {
  22.             try {
  23.                 conn.close();
  24.             } catch (SQLException e1) {
  25.                 e1.printStackTrace();
  26.             }
  27.             e.printStackTrace();
  28.             return false;
  29.         }
  30.     }
  31.     public boolean checkLogin(StudentForm student){
  32.         String sql="select * from student where userName='"+student.getUserName()+"' and userPwd='"+student.getUserPwd()+"'";
  33.         
  34.         try {
  35.             conn=dbPool.getConnection();
  36.             st=conn.createStatement();
  37.             rs=st.executeQuery(sql);
  38.             if(rs.next()){
  39.                 conn.close();
  40.                 return true;
  41.             }else{
  42.                 conn.close();
  43.                 return false;
  44.             }
  45.         } catch (SQLException e) {
  46.             try {
  47.                 conn.close();
  48.             } catch (SQLException e1) {
  49.                 e1.printStackTrace();
  50.             }
  51.             e.printStackTrace();
  52.             return false;
  53.         }
  54.     }
  55.     public List getAllStudent(){
  56.         String sql="select * from student ";
  57.         List students=new ArrayList();
  58.         try {
  59.             conn=dbPool.getConnection();
  60.             st=conn.createStatement();
  61.             rs=st.executeQuery(sql);
  62.             while(rs.next()){
  63.                 Student student=new Student(rs.getString("xuehao"),
  64.                         rs.getString("userName"),
  65.                         rs.getString("userPwd"),
  66.                         rs.getInt("czxt"),
  67.                         rs.getInt("wjyl"),
  68.                         rs.getInt("sjjg")
  69.                 );
  70.                 students.add(student);
  71.             }
  72.             
  73.         } catch (SQLException e) {
  74.             try {
  75.                 conn.close();
  76.             } catch (SQLException e1) {
  77.                 e1.printStackTrace();
  78.             }
  79.             e.printStackTrace();
  80.             return null;
  81.         }
  82.         return students;
  83.         
  84.     }
  85.     
  86.     public Student getStudentByXuehao(String xuehao){
  87.         String sql="select * from student where xuehao='"+xuehao+"'";
  88.         Student student=null;
  89.         try {
  90.             conn=dbPool.getConnection();
  91.             st=conn.createStatement();
  92.             rs=st.executeQuery(sql);
  93.             if(rs.next()){
  94.                 student=new Student(rs.getString("xuehao"),
  95.                         rs.getString("userName"),
  96.                         rs.getString("userPwd"),
  97.                         rs.getInt("czxt"),
  98.                         rs.getInt("wjyl"),
  99.                         rs.getInt("sjjg")
  100.                 );
  101.             }
  102.             return student;
  103.             
  104.         } catch (SQLException e) {
  105.             try {
  106.                 conn.close();
  107.             } catch (SQLException e1) {
  108.                 e1.printStackTrace();
  109.             }
  110.             e.printStackTrace();
  111.             return null;
  112.         }
  113.     }
  114.     public boolean deleteStudent(String xuehao){
  115.         String sql="delete from student where xuehao='"+xuehao+"'";
  116.         conn=dbPool.getConnection();
  117.         try {
  118.             st=conn.createStatement();
  119.             st.execute(sql);
  120.             conn.close();
  121.             return true;
  122.         } catch (SQLException e) {
  123.             e.printStackTrace();
  124.             try {
  125.                 conn.close();
  126.             } catch (SQLException e1) {
  127.                 e1.printStackTrace();
  128.             }
  129.             return false;
  130.         }
  131.     }
  132.     public boolean updateStudent(Student student){
  133.         String sql="update student set userName='" +student.getUserName()+
  134.                 "',userPwd='" +student.getUserPwd()+"',czxt="+student.getCzxt()+
  135.                 ",wjyl="+student.getWjyl()+",sjjg="+student.getSjjg()+" where xuehao='"+student.getXuehao()+"'";
  136.         conn=dbPool.getConnection();
  137.         try {
  138.             st=conn.createStatement();
  139.             st.execute(sql);
  140.             conn.close();
  141.             return true;
  142.         } catch (SQLException e) {
  143.             e.printStackTrace();
  144.             try {
  145.                 conn.close();
  146.             } catch (SQLException e1) {
  147.                 e1.printStackTrace();
  148.             }
  149.             return false;
  150.         }
  151.     }
  152.     public boolean insertStudent(Student student){
  153.         String sql="insert into student values('"+student.getXuehao()+"','" +student.getUserName()+
  154.                 "','" +student.getUserPwd()+"',"+student.getCzxt()+
  155.                 ","+student.getWjyl()+","+student.getSjjg()+")";
  156.         //System.out.println(sql);
  157.         conn=dbPool.getConnection();
  158.         try {
  159.             st=conn.createStatement();
  160.             st.execute(sql);
  161.             conn.close();
  162.             return true;
  163.         } catch (SQLException e) {
  164.             e.printStackTrace();
  165.             try {
  166.                 conn.close();
  167.             } catch (SQLException e1) {
  168.                 e1.printStackTrace();
  169.             }
  170.             return false;
  171.         }
  172.     }
  173. }