jsp_JdbcUtil封装

最新推荐文章于 2024-11-29 23:21:45 发布

原创最新推荐文章于 2024-11-29 23:21:45 发布 · 376 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#jsp

jsp 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了一个实用的 Java JDBC 工具类及其在数据库操作中的应用，包括连接管理、查询单条记录、查询记录列表及增删改操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package com.tz.servlet.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtil {

    private static String driver;
    private static String url;
    private static String user;
    private static String password;


    //初始化
    static {
        Properties properties = new Properties();
        //返回一个输入流
        InputStream inStream = JdbcUtil.class.getResourceAsStream("/db.properties");
        //加载流，获取4个属性值
        try {
            properties.load(inStream);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");

            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //获取Connection对象
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }


    //关闭连接
    public static void close(Connection conn, Statement state, ResultSet result) {
        if(result != null) {
            try {
                result.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Dao 接口

package com.tz.servlet.dao;

import java.util.List;

import com.tz.servlet.entity.UserInfo;

public interface UserInfoDao {

    /**
     * 查询返回一条数据 
     */
    public <T> T getUserInfo(Class<T> clazz, String sql, Object ...params);


    /**
     * 查询返回一个集合
     */
    public <T> List<T> getUserInfoList(Class<T> clazz, String sql, Object ...params);



    /**
     * 可以实现添加,修改,删除三个操作
     */
    public void update(String sql, Object ...params);


}

接口的实现

package com.tz.servlet.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;

import com.tz.servlet.dao.UserInfoDao;
import com.tz.servlet.utils.JdbcUtil;

public class UserInfoDaoImpl implements UserInfoDao{


    /**
     * 查询返回一条数据 
     */
    @Override
    public <T> T getUserInfo(Class<T> clazz, String sql, Object ...params) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet result = null;
        T entityObj = null;

        try {
            conn = JdbcUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            //填充数据
            for(int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1, params[i]);
            }
            result = preparedStatement.executeQuery();
            //利用反射创建一个相应的对象
            entityObj = clazz.newInstance();
            //遍历数据存放一个实体类里去
            while(result.next()) {

                ResultSetMetaData resultSetMetaData = result.getMetaData();//获取这个result对象的列的编号，类型和属性等
                //resultSetMetaData.getColumnCount(); 返回字段数;
                for(int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    String columnLabel = resultSetMetaData.getColumnLabel(i+1);   //返回字段名或者别名;
                    System.out.println(columnLabel);
                    Object columnValue = result.getObject(i+1);                   //获取字段值
                    BeanUtils.setProperty(entityObj, columnLabel, columnValue);//给实体对象属性值
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, preparedStatement, result);
        }
        return entityObj;
    }

    /**
     * 查询返回一个集合
     */
    @Override
    public <T> List<T> getUserInfoList(Class<T> clazz, String sql, Object ...params) {

        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet result = null;
        T entityObj = null;
        List<T> list = new ArrayList<T>();

        try {
            conn = JdbcUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            //填充数据
            for(int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1, params[i]);
            }
            result = preparedStatement.executeQuery();
            //遍历数据存放一个实体类里去
            while(result.next()) {

                //利用反射创建一个相应的对象
                entityObj = clazz.newInstance();

                ResultSetMetaData resultSetMetaData = result.getMetaData();//获取这个result对象的列的编号，类型和属性等
                //resultSetMetaData.getColumnCount(); 返回字段数;
                for(int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    String columnLabel = resultSetMetaData.getColumnLabel(i+1);   //返回字段名或者别名;
                    System.out.println(columnLabel);
                    Object columnValue = result.getObject(i+1);                   //获取字段值
                    BeanUtils.setProperty(entityObj, columnLabel, columnValue);//给实体对象属性值
                }
                list.add(entityObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, preparedStatement, result);
        }       
        return list;
    }





















    /**
     * 可以实现添加,修改,删除三个操作
     */
    @Override
    public void update(String sql, Object ...params) {
        Connection conn = JdbcUtil.getConnection();
        PreparedStatement preparedStatement = null;
        //String sql = "insert into user_info values(?,?,?,?,?,?)";
        try {
            preparedStatement = conn.prepareStatement(sql);
            for(int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1, params[i]);
            }
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, preparedStatement, null);
        }
    }





    /**
     * 添加用户信息
        @Override
        public void addUserInfo(UserInfo user) {
            Connection conn = JdbcUtil.getConnection();
            PreparedStatement preparedStatement = null;
            String sql = "insert into user_info values(?,?,?,?,?,?)";
            try {
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setInt(1, user.getUserId());
                preparedStatement.setString(2, user.getUserName());
                preparedStatement.setInt(3, user.getUserAge());
                preparedStatement.setString(4, user.getUserSex());
                preparedStatement.setString(5, user.getPhone());
                preparedStatement.setString(6, user.getEmail());

                preparedStatement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(conn, preparedStatement, null);
            }
        }
        //修改用户信息
        @Override
        public void modifyUserInfo(UserInfo user) {
            Connection conn = JdbcUtil.getConnection();
            PreparedStatement preparedStatement = null;
            String sql = "update user_info set user_name=?,user_age=?,user_sex=?,phone=?,email=? where user_id=?";
            try {
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1, user.getUserName());
                preparedStatement.setInt(2, user.getUserAge());
                preparedStatement.setString(3, user.getUserSex());
                preparedStatement.setString(4, user.getPhone());
                preparedStatement.setString(5, user.getEmail());
                preparedStatement.setInt(6, user.getUserId());

                preparedStatement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(conn, preparedStatement, null);
            }
        }
        //删除用户信息
        @Override
        public void delUserInfo(Integer userId) {
            Connection conn = JdbcUtil.getConnection();
            PreparedStatement preparedStatement = null;
            String sql = "delete from user_info where user_id = ?";
            try {
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setInt(1, userId);

                preparedStatement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(conn, preparedStatement, null);
            }
        }
     */


}