你的位置:首页 > Java教程

[Java教程]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.Driverurl=jdbc:mysql://localhost:3306/rssuser=rootpassword=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){    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]);        }      }      int res = ps.executeUpdate();      conn.commit();      return res;    } catch (SQLException e) {      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.运行结果