JDBC连接数据库工具类并实现增删改查

JDBC连接数据库工具类并实现增删改查

1、JDBC连接数据库工具类

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class JDBCUtils {

    private static final String driverClass;
    private static final String url;
    private static final String userName;
    private static final String passWord;
    
    //静态代码块初始化变量值
    static{
        //获取配置文件内容
        Properties properties = null;
        try {
            ClassPathResource fileResource = new ClassPathResource("my.properties");
            //InputStream is = new FileInputStream("src/my.properties");
            InputStream is = fileResource.getInputStream();
            properties = new Properties();
            properties.load(is);
        } catch (Exception e) {         
            e.printStackTrace();
        }
         driverClass = properties.getProperty("driverClass");
         url = properties.getProperty("url");
         userName = properties.getProperty("userName");
         passWord = properties.getProperty("passWord");
    }

    //注册驱动
    public static void loadDriver() {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {          
            e.printStackTrace();
        }
    }
    
    //获取数据库连接
    public static Connection getConnection() {
        Connection conn = null;
        try {
            //加载数据库驱动
            loadDriver();
            conn = DriverManager.getConnection(url, userName, passWord);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    //释放资源
     public static void closeResource(ResultSet rs,PreparedStatement psmt,Connection conn) {
     
         if(rs != null) {
             try {
                rs.close();
            } catch (SQLException e) {               
                e.printStackTrace();
            }
             //垃圾回收尽快回收对象
             rs = null;
         }
         
         if(psmt != null) {
             try {
                psmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
             psmt = null;
         }
         
         if(conn != null) {
             try {
                conn.close();
            } catch (SQLException e) {            
                e.printStackTrace();
            }
             conn = null;
         }
     }
}

2、使用工具类实现增删改查

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.springframework.stereotype.Controller;

@Controller
public class Controller{

    //查询
    @RequestMapping("test")
    public void test() {
        //注册驱动 获得连接
        Connection conn = null;
        //conn连接数据库获得语句执行者psmt
        PreparedStatement psmt = null;
        //psmt执行后获得结果集rs
        ResultSet rs =null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from user";
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            while(rs.next()) {          
            //pass_word为数据库表字段对应的列名
                String passWord = rs.getString("pass_word");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(rs, psmt, conn);
        }
    }
    
    //添加
   @RequestMapping("test")
    public void test() {
    
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs =null;
        
        try {
            //获得连接
            conn = JDBCUtils.getConnection();
            //获得语句执行者
            String sql = "insert into user values (null,?)";
            psmt = conn.prepareStatement(sql);
            //设置参数
            psmt.setString(1, "密码");
            int i = psmt.executeUpdate();
            if(i > 0) {
                System.out.println("添加成功!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(rs, psmt, conn);
        }
    }

    //修改
   @RequestMapping("test")
    public void test() {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        
        try {
            //获得连接
            conn = JDBCUtils.getConnection();
            //psmt语句执行者
            String sql = "update user set passWord = ? where id = ?";
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, "密码");
            psmt.setInt(2, 6);
            int i = psmt.executeUpdate();
            if(i > 0) {
                System.out.println("修改成功!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(rs, psmt, conn);
        }
    }

    //删除
    @RequestMapping("test")
    public void test() {
    
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        
        try {
            //获得连接
            conn = JDBCUtils.getConnection();
            //psmt语句执行者
            String sql = "delete from user where id = ?";
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, 6);
            int i = psmt.executeUpdate();
            if(i > 0) {
                System.out.println("删除成功！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(rs, psmt, conn);
        }
    }
}