java 自定义封装jdbc dao类

手动封装jdbc和dao层，体会其中的优点与不足。

注：本次采用的mysql数据库记得添加数据库的驱动包。

Dbhelper类：对jdbc进行封装，采用单例模式，不用每次都去连接数据库，释放连接，影响性能，对sql语句进行封装，利用列表数组动态添加数据，

不需要一个一个设置，操作时只需传sql 语句和列表数组，前提是？与数组中的参数要一一对应。返回结果封装：对于执行操作，结果只有成功与失败；

对于查询数据会封装进一个List<Map<String,String>>，同时释放结果集，提高系统的性能，方便用户操作。

不足的是，数据库的信息是硬编码，不利于扩展

package com.lzw.db1.db;



import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 该类是一个单例模式
 * jdbc封装类
 */
public class DbHelper {
    private static DbHelper dbHelper = null;
    private static Connection connection = null;
    private static final String DRIVEN = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/book";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    private DbHelper() {
        if (connection == null) {
           getConnection();
        }
    }

    /**
     * 获取数据库连接
     */
    private void getConnection(){

        try {

            Class.forName(DRIVEN);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            System.out.println("数据库连接成功！");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 获取实例，
     * @return
     */
    public static DbHelper getInstance() {
        if (dbHelper == null) {
            dbHelper = new DbHelper();
        }
        return dbHelper;
    }

    /**
     *
     * @param sql sql语句
     * @param list 注入数据
     * @return
     */
    public boolean excute(String sql, List<String> list) {
        boolean is = false;
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (list != null)
            for (int i = 0; i < list.size(); i++) {
                try {
                    preparedStatement.setString(i + 1, list.get(i));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        try {
            int row = preparedStatement.executeUpdate();
            if (row > 0) {
                is = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return is;

    }

    /**
     *
     * @param sql  sql语句
     * @param list 注入数据
     * @return
     */

    public List<Map<String, String>> query(String sql, List<String> list) {
        List<Map<String, String>> resultlist = null;
        ResultSet resultSet = null;
        ResultSetMetaData resultSetMetaData = null;
        PreparedStatement preparedStatement = null;


        try {
            preparedStatement = connection.prepareStatement(sql);
            if (list != null)
                for (int i = 0; i < list.size(); i++) {

                    preparedStatement.setString(i + 1, list.get(i));

                }
            resultSet = preparedStatement.executeQuery();
            resultSetMetaData = resultSet.getMetaData();
            resultlist = new ArrayList<Map<String, String>>();
            while (resultSet.next()) {
                Map<String, String> map = new HashMap<String, String>();
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    //列示从1开始的
                    map.put(resultSetMetaData.getColumnName(i+1), resultSet.getString(i+1));
                }
                resultlist.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                if (resultSet != null)
                    resultSet.close();
                if(preparedStatement!=null)
                    preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }


        }
        return resultlist;

    }
}

UserDao：user表基本操作的封装

package com.lzw.db1.dao;

import com.lzw.db1.bean.User;
import com.lzw.db1.db.DbHelper;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2015/12/6.
 */
public class UserDao {
    DbHelper dbHelper=null;
    public UserDao(){
        dbHelper=DbHelper.getInstance();
    }

    public User find(String id){
        String sql = "SELECT * FROM user where id=?";
        List<String> list = new ArrayList<String>();
        list.add(id);
        List<Map<String,String>> listresult=dbHelper.query(sql, list);
        if(listresult==null||listresult.size()==0){
            return  null;
        }else {
            User user=new User();
            user.setId(listresult.get(0).get("id"));
            user.setName(listresult.get(0).get("name"));
            user.setPassword(listresult.get(0).get("password"));
            return user;
        }

    }
    public boolean add(User user){
        String sql = "insert into user values(NULL ,?,?,UNIX_TIMESTAMP())";
        List<String> list = new ArrayList<String>();
        list.add(user.getName());
        list.add(user.getPassword());
        return  dbHelper.excute(sql,list);
    }

    public boolean update(User user){
        if(user==null||user.getId()==null){
            return false;
        }
        String sql = "update user set name=?,password=? where id=?";
        List<String> list = new ArrayList<String>();
        list.add(user.getName());
        list.add(user.getPassword());
        list.add(user.getId());
        return  dbHelper.excute(sql,list);
    }

    public boolean delete(String id){

        String sql = "delete from user where id=?";
        List<String> list = new ArrayList<String>();
        list.add(id);
       return dbHelper.excute(sql,list);

    }



}