JDBC之增删改查的封装笔记

准备工作

    // 保存静态的连接
    private static Connection connection =null;
    static {
        try {
            // 静态加载mysql驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 防止用户创建实例
     */
    private DBAccess(){}

    /**
     * 获取类的实例
     * @param dbURL 数据源
     * @param dbName 数据库名称
     * @param user 用户
     * @param password 密码
     * @return 类的单例
     */
    public static DBAccess getInstance(String dbURL, String dbName, String user, String password){
        getInstance(dbURL, dbName, user, password);
        return InnerClass.dbAccess;
    }
    static class InnerClass{
         static DBAccess dbAccess = new DBAccess();
    }
    // 获取或更新数据库的连接
    protected static Connection getConnection(String dbURL, String dbName, String user, String password){
            try {
                if (connection == null || connection.isClosed())
                    connection = DriverManager.getConnection(String.format("jdbc:mysql://%s:3306/%s?user=%s&password=%s", dbURL, dbName, user, password));
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return connection;
    }

对增删该查的封装

    /**
     * 增、改、删
     * @param sql
     * @param objects
     * @return 修改的记录条数
     */
    public int insertOrUpdateOrDelete(String sql, Object[] objects){
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            if (objects != null){
                for (int i = 0; i < objects.length; i ++){
                    preparedStatement.setObject(i + 1, objects[i]);
                }
            }
            count = preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 查询
     * @param sql
     * @return 结果集
     */
    public ResultSet find(String sql, Object[] objects){
        ResultSet set = null;
        PreparedStatement preparedStatement = null;
        List<Worker> list = null;
        try{
            preparedStatement =  connection.prepareStatement(sql);
            if (objects != null){
                for (int i = 0; i < objects.length; i ++){
                    preparedStatement.setObject(i + 1, objects[i]);
                }
            }
            set = preparedStatement.executeQuery(sql);
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return set;
    }

用法

 /**
     * 插入员工
     * @param worker
     * @return
     */
    public int addWorker(Worker worker){
        DBAccess dao = DBAccess.getInstance("localhost", "demo", "root", "root");
        Object[] objects = {worker.getName(), worker.getSex(), worker.getAge(), worker.getPosition(), worker.getTel()};
        return dao.insertOrUpdateOrDelete("insert into worker values(null, ? , ? ,?, ?, ?)", objects);
    }

    /**
     * 删除员工
     * @param workerId
     * @return
     */
    public int deleteWorker(int workerId){
       DBAccess dao = DBAccess.getInstance("localhost", "demo", "root", "root");
        return dao.insertOrUpdateOrDelete("delete from worker where id=?", new Object[]{workerId});
    }

    /**
     * 查询所有员工
     * @return
     */
    public ResultSet getAllWorker(){
        DBAccess dao = DBAccess.getInstance("localhost", "demo", "root", "root");
        return dao.find("select * from worker", null);
    }

    /**
     * 更新表数据
     * @param worker
     * @return
     */
    public int updateWorker(Worker worker){
        DBAccess dao = DBAccess.getInstance("localhost", "demo", "root", "root");
        Object[] objects={worker.getName(), worker.getSex(), worker.getAge(), worker.getPosition(), worker.getTel()};
        return dao.insertOrUpdateOrDelete("update worker set name=?, sex=?, age=?, position=?, tel=?", objects);
    }