20160406javaweb JDBC 例項工具類

一、建立靜態的資料庫配置檔案：

config.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/database01
user=root
password=

二、建立工具類 ：

JDBCUtils.java

package com.dzq.util;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
    
   private static  Properties prop=null;
   
   private JDBCUtils(){
    
   }
   
   /**
    * 靜態程式碼塊，載入資料庫配置檔案
    */
   static{
       try{
        prop=new Properties();
        prop.load(new FileReader(JDBCUtils.class.getClassLoader().getResource("config.properties").getPath()));
       }catch(Exception e){
           e.printStackTrace();
           throw new RuntimeException();
       }
   }
   
   /**
    * 獲取連線
    */
   public static Connection getConn() throws Exception{
      
       
       Class.forName(prop.getProperty("driver"));
       return DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("user"), prop.getProperty("password"));
   }
   
   /**
    * 關閉連線
    */
   public static void close(ResultSet rs,Statement stat,Connection conn){
       if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                rs=null;
            }
        }
        if(stat!=null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                stat=null;
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                conn=null;
            }
        }
   }
}

三、查詢、增刪改測試程式碼：

JDBCDemo2.java

 

package com.dzq.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

import com.dzq.util.JDBCUtils;

public class JDBCDemo2 {
    
    /**
     * 該類中方法均以JUnit測試的形式給出
     */
    
    
    @Test
    public void delete(){
        Connection conn=null;
        Statement stat=null;
        ResultSet rs=null;
        try{
        //2.獲取連線    
            conn=JDBCUtils.getConn();
        //3.獲取傳輸器物件
            stat=conn.createStatement();
            stat.executeUpdate("delete from user where name='zhaoliu'");
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCUtils.close(rs, stat, conn);
        }
    }
    
    @Test
    public void find(){
        Connection conn=null;
        Statement stat=null;
        ResultSet rs=null;
        try{
        //2.獲取連線    
            conn=JDBCUtils.getConn();
        //3.獲取傳輸器物件
            stat=conn.createStatement();
            rs=stat.executeQuery("select * from user where name='zhaoliu'");
            while(rs.next()){
                String name=rs.getString("name");
                String password=rs.getString("password");
                String email=rs.getString("email");
                System.out.println("name:"+name+"pwd:"+password+"email:"+email);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCUtils.close(rs, stat, conn);
        }
    }
    @Test
    public void update(){
        Connection conn=null;
        Statement stat=null;
        try {
        //2.獲取連線    
            conn=JDBCUtils.getConn();
        //3.獲取傳輸器物件
            stat=conn.createStatement();
            stat.executeUpdate("update user set password=999 where name='zhangsan'");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.close(null, stat, conn);
        }
    }
    @Test
    public void add(){
        Connection conn=null;
        Statement stat=null;
    //1.註冊資料庫驅動
        try{
        
    //2.獲取連線    
        conn=JDBCUtils.getConn();
    //3.獲取傳輸器物件
        stat=conn.createStatement();
    //4.執行sql語句
        int count=stat.executeUpdate("insert into user values (null,'zhangsan','123456','zhaoliu@qq.com','1999-09-09')");
    //5.處理結果
        if(count>0){
            System.out.println("新增資料成功,影響行數為"+count);
        }else{
            System.out.println("執行失敗");
        }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
    //6.關閉資源
            JDBCUtils.close(null, stat, conn);
        }
   }
}