java 自定义封装jdbc dao类

来源：互联网发布：怪物猎人角色数据编辑：程序博客网时间：2024/06/13 06:11

手动封装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);    }}

0 0