以前写项目一直用框架，封装实体很方便，这次直接用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.运行结果
原标题：java连接数据库工具类
关键词：JAVA
我知道了 