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','[e-mail]','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);
        }
   }
}