jdbc--------JdbcUtilDao 类

2018-12-14

目标：做成一个比较通用的 sql 操作

import com.ljs.util.JDBCUtil;

类名：JdbcUtilDao 

1： 更新操作， 针对任何表，增加，删除，更新操作都可以满足。

知识点： 可变数组， preparedstatement 对象。（？） 占位符。

public int update(String sql, Object ...objects){   // 
        
        // 1 获取conn 连接
        Connection connection =null;
        PreparedStatement pt = null;
        try {
            connection = JDBCUtil.getConn();
            
            pt = connection.prepareStatement(sql);
            
            for( int i =0; i < objects.length; ++i ){
                pt.setObject(i+1, objects[i]);
            }
            int len = pt.executeUpdate();
            return len;
        } catch ( Exception e) {
            
            e.printStackTrace();
            return -1;
        } finally {
            // 3 关闭 con
            JDBCUtil.close(null, pt, connection);
        }
    
    }

2： 通用的查询单条记录， 只返回一条的记录。  这条记录用 javabean 对象封装好。

知识点：泛型，ResultSetMetaData 对象保存结果集的属性结构。 和根据ResultSet 的值

反射，  生成对象，和获取对象的属性并且赋值。

ResultSet --> bean.

public<T> T queryOne(Class<T> clazz, String sql, Object ...Object){
        
        try {
            Connection connection = JDBCUtil.getConn();
            
            PreparedStatement pst = connection.prepareStatement(sql);
            for( int i=0; i<Object.length; ++i ){
                pst.setObject(i+1, Object[i]);
            }
            
            ResultSet resultSet = pst.executeQuery();
            
            // ResultSetMetaData  保存这个结果集的属性结构。
            ResultSetMetaData metaData = resultSet.getMetaData();
            int len = metaData.getColumnCount();
            
            while( resultSet.next() ){
                
                T bean = clazz.newInstance();
                for( int i = 1; i<=len; ++i ){
                    String column_name = metaData.getColumnLabel(i);
                    Object value = resultSet.getObject(column_name);
                    
                    /*
                     * 根据结果集的属性结构 构造 属性结构。
                     * 反射 。
                     */
                    Field field = clazz.getDeclaredField(column_name);
                    field.setAccessible(true);
                    field.set(bean, value);
                    
                }
                return bean;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return null;
        
    }

3 查询多条记录，返回list容器列表中。 

知识点： 和上一样。 ArrayList

public <T> List<T> queryMutil(Class<T> clazz, String sql, Object ...Object){
        
        
        try {
            Connection connection = JDBCUtil.getConn();
            PreparedStatement pst = connection.prepareStatement(sql);
            List<T> list = new ArrayList<T>();
            for( int i=0; i<Object.length; ++i ){
                pst.setObject(i+1, Object[i]);
            }
            
            ResultSet set = pst.executeQuery();
            
            ResultSetMetaData metadata = set.getMetaData();
            int len = metadata.getColumnCount();
            
            while(set.next()){
                T bean = clazz.newInstance();
                for (int i = 1; i <= len; i++) {
                    String name = metadata.getColumnLabel(i);
                    Object object2 = set.getObject(name);
                    
                    Field field = clazz.getDeclaredField(name);
                    field.setAccessible(true);
                    field.set(bean, object2);
                }
                list.add(bean);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

上面的测试代码

package com.ljs.daoTest;

import java.util.List;

import org.junit.Test;

import com.ljs.bean.UserBean;
import com.ljs.dao.JdbcUtilDao;


public class JdbcDaoTest {
    
    @Test
    public void update(){
        
        JdbcUtilDao dao = new JdbcUtilDao();
        //String sql = "insert into user(name,password) values(?,?)";
        String sql = "update user set name = ? where id = 11";
        int len = dao.update(sql, "lijinsheng");
        System.out.println(len>0?"yes":"no");
    }
    
    @Test
    public void queryOne(){
        JdbcUtilDao dao = new JdbcUtilDao();
        String sql = "select * from user where name= ?";
        UserBean tBean = dao.queryOne(UserBean.class, sql,"lijinsheng");
        System.out.println(tBean);
        
    }
    
    @Test
    public void queryMutil(){
        JdbcUtilDao dao = new JdbcUtilDao();
        List<UserBean> list;
        String sql = "select * from user where name= ?";
        list =  dao.queryMutil(UserBean.class, sql, "lijinsheng");
        for( int i=0; i<list.size(); ++i ){
            System.out.println(list.get(i));
        }
    }

}

public class DaoImp implements Dao{


    @Override
    public UserBean query(int id) throws SQLException {

        String sql = "select * from USER where id = ?";
       Connection connection = JDBCUtil.getConn();
       UserBean bean = new UserBean();
       PreparedStatement preparedStatement = (PreparedStatement)
               connection.prepareStatement(sql);

       preparedStatement.setInt(1,id);

       ResultSet rs = preparedStatement.executeQuery();

       while(rs.next()){

           bean.setId(rs.getInt(1));
           bean.setName(rs.getString(2));
           bean.setPassword(rs.getString(3));
           bean.setPhone(rs.getString(4));

       }

       JDBCUtil.close(connection,preparedStatement, rs);
       return bean;

    }

    @Override
    public void modify(UserBean userBean) throws SQLException {
        String sql = "update user set name=?, password=?, phone=? where id=?";
        Connection connection = JDBCUtil.getConn();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,userBean.getName());
        preparedStatement.setString(2, userBean.getPassword());
        preparedStatement.setString(3,userBean.getPhone());
        preparedStatement.setInt(4,userBean.getId());
        preparedStatement.executeUpdate();

    }


    @Override
    public void add(UserBean user) throws SQLException {
        String sql = "insert into user(id,name,password,phone) values(?,?,?,?)";
        Connection connection = JDBCUtil.getConn();

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,user.getId());
        preparedStatement.setString(2,user.getName());
        preparedStatement.setString(3,user.getPhone());
        preparedStatement.setString(4,user.getPassword());
        preparedStatement.executeUpdate();
    }

    @Override
    public void delete(int id) throws SQLException {

        String sql = "delete from user where id=?";
        Connection connection = JDBCUtil.getConn();

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        preparedStatement.executeUpdate();

    }
}