java连接数据库工具类

　　以前写项目一直用框架，封装实体很方便，这次直接用servlet和jsp写，自己封装数据，好多重复的代码，又刚好复习了反射机制，所以自己写了个工具类。

1.team 表中字段

2.实体Team类

package com.beb.entity;

public class Team {

    private Integer id;
    private String teamId;
    private String teamName;
    private String teamDesc;
    
    
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getTeamId() {
        return teamId;
    }
    public void setTeamId(String teamId) {
        this.teamId = teamId;
    }
    public String getTeamName() {
        return teamName;
    }
    public void setTeamName(String teamName) {
        this.teamName = teamName;
    }
    public String getTeamDesc() {
        return teamDesc;
    }
    public void setTeamDesc(String teamDesc) {
        this.teamDesc = teamDesc;
    }
    @Override
    public String toString() {
        return "Team [id="+id+", teamId=" + teamId + ", teamName=" + teamName
                + ", teamDesc=" + teamDesc + "]";
    }
    public Team(String teamId, String teamName, String teamDesc) {
        this.teamId = teamId;
        this.teamName = teamName;
        this.teamDesc = teamDesc;
    }
    public Team() {
    
    }
    
    
}

3.连接数据库的属性文件 db.propertie，放在src路径下

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/rss
user=root
password=123456

4.工具类SqlHelper.java

package com.beb.util;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
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 java.util.Properties;


public class SqlHelper {
    /*连接数据库用的参数*/
    private static String driver;  
    private static String url;     
    private static String user;
    private static String password;
    
    /*数据库参数*/
    private static Connection conn=null; 
    private static PreparedStatement ps=null;
    private static ResultSet rs = null;
    
    static{
        Properties prop = new Properties();
        try {
            //得到属性文件的路径
            InputStream is=SqlHelper.class.getClassLoader().getResourceAsStream("db.properties");
            //InputStream is =new FileInputStream(new File("db.properties"));
            prop.load(is);
            driver=prop.getProperty("driver");
            url=prop.getProperty("url");
            user=prop.getProperty("user");
            password = prop.getProperty("password");
            Class.forName(driver);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 得到数据库连接
     * @return
     */
    public Connection getConnection(){
        try {
            conn= DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    /**
     * 关闭资源
     * @param conn
     * @param ps
     * @param rs
     */
    public void close(Connection conn,PreparedStatement ps,ResultSet rs){
        if(conn!=null){
            try {
                conn.close();
                conn=null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(ps!=null){
            try {
                ps.close();
                ps=null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
                rs=null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    /**
     * 普通查询，在封装的时候麻烦
     * @param sql
     * @param parameters
     * @return
     */
    public  List query(String sql,Object[] parameters){
        List list = new ArrayList();
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            //传递参数
            if(parameters!=null && parameters.length>0){
                for(int j = 0;j<parameters.length;j++){
                    ps.setObject(j+1, parameters[j]);
                }
            }
            rs = ps.executeQuery();
            //得到有多少列，getMetaData()得到数据源，
            int columnCount = rs.getMetaData().getColumnCount();
            while(rs.next()){
                //将获得的数据封装到Object数组中
                Object[] obj=new Object[columnCount];
                for(int i=0;i<columnCount;i++){
                    obj[i]=rs.getObject(i+1);
                }
                list.add(obj);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            close(conn, ps, rs);
        }
        return list;
    }
    
    /**
     * 通用查询，适合所有entity，但是表的字段名必须和实体类中的属性名相同
     * @param <T>
     * @param entity
     * @param sql
     * @param parameters
     * @return
     */
    public <T extends Object> List<T> query2(T entity,String sql,Object...parameters){
        List<T> list = new ArrayList<T>();
        try {
            Connection conn = getConnection();
            ps = conn.prepareStatement(sql);
            //传递参数
            if(parameters!=null && parameters.length>0){
                for(int j = 0;j<parameters.length;j++){
                    ps.setObject(j+1, parameters[j]);
                }
            }
            rs = ps.executeQuery();
            //得到数据源
            ResultSetMetaData rsmd = rs.getMetaData();
            //得到有多少列
            int columnCount = rsmd.getColumnCount();
            //得到列名
            String[] columnName = new String[columnCount];
            for(int i=0;i<columnCount;i++){
                columnName[i]= rsmd.getColumnName(i+1);
            }
            //得到entity的Class对象，
            Class clazz = entity.getClass();
            //得到所有属性
            Field[]  fields = clazz.getDeclaredFields();
            Object obj = null;
            while(rs.next()){
                obj=clazz.newInstance();
                for(int i=0;i<columnCount;i++){
                    for(int j=0;j<fields.length;j++){
                        //判断表列名是否与属性名相同
                        if(columnName[i].equals(fields[j].getName())){
                            fields[j].setAccessible(true);
                            fields[j].set(obj, rs.getObject(columnName[i]));
                            break;
                        }
                        
                    }
                }
                list.add((T) obj);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally{
            close(conn, ps, rs);
        }
        return list;
    }
    
    /**
     * 增删改
     * @param sql
     * @param parameters
     * @return
     */
    public int update(String sql,Object[] parameters){
       int res = 0;
　　　　try {
            conn = getConnection();
            conn.setAutoCommit(false);
            ps=conn.prepareStatement(sql);
            if(parameters!=null){
                for(int i=0;i<parameters.length;i++){
                    ps.setObject(i+1, parameters[i]);
                }
            }
            res = ps.executeUpdate();
            conn.commit();
　　　　　　　conn.setAutoCommit(true);
            return res;
        } catch (SQLException e) {
　　　　　　　conn.rollback();
　　　　　　　conn.setAutoCommit(true);
            e.printStackTrace();
            return 0;
        }finally{
            close(conn, ps, rs);
        }
        
    }
}

5.测试类 Test.java

package com.beb.test;

import java.util.List;

import com.beb.entity.Team;
import com.beb.util.SqlHelper;

public class Test {

    public static void main(String[] args) {
        SqlHelper sqlHelper = new SqlHelper();
        String sql = "select * from team";
        List<Team> teamList = sqlHelper.query2(new Team(), sql, null);
        for(Team team:teamList){
            System.out.println(team);
        }
    }
}

6.运行结果

爱程序网

Tags：[db:关键字]