DouQiKeJi.rar

package com.douqi.dao; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 数据库连接对象 * @author 李世明 * @time 2011-05-11 */ public class DaoImpl implements Dao{ private String sDBDriver = "org.gjt.mm.mysql.Driver"; private String sConnStr = "jdbc:mysql://localhost:3306/douqi?useUnicode=true&characterEncoding=utf-8";//设置数据库名称为：pubs private String username = "root"; //登录数据库用户名 private String password = "root"; //登录数据库密码 /** * 建立连接 * @return * @throws ClassNotFoundException * @throws SQLException */ public Connection getConnection() throws ClassNotFoundException, SQLException{ Class.forName(sDBDriver); //指定JDBC数据库驱动程序 return DriverManager.getConnection(sConnStr,username,password); } /** * 根据sql查询列表数据(查询一条)，不支持预编译的方式 * @param sql * @return * @throws ClassNotFoundException * @throws SQLException */ public Map<String, Object> executeQueryForMap(String sql)throws ClassNotFoundException, SQLException { System.err.println("查询一条："+sql); System.out.println(1); Connection connect =this.getConnection(); System.out.println(2); Statement stmt = connect.createStatement(); System.out.println(3); ResultSet rs = stmt.executeQuery(sql); System.out.println(4); List<Map<String, Object>> list = this.rsToList(rs); System.out.println(list.size()); if( !list.isEmpty() ){ return list.get(0); } this.releaseConnection(rs, stmt, connect);//关闭连接 return null; } /** * 根据sql查询列表数据(查询一条)，支持预编译的方式 * @param sql * @param types * @param values * @return * @throws SQLException * @throws ClassNotFoundException */ public Map<String, Object> executeQueryForMap(String sql, int[] types,Object[] values) throws ClassNotFoundException, SQLException { System.err.println("查询一条："+sql); this.print(values); Connection connect = this.getConnection(); PreparedStatement pst = connect.prepareStatement(sql); if( types != null ){ for(int i=0;i<types.length;i++){ switch( types[i] ){ case Types.VARCHAR: pst.setString(i+1, String.valueOf( values[i] ) ); break; case Types.INTEGER: pst.setInt(i+1, Integer.parseInt( String.valueOf( values[i] ) )); break; } } } ResultSet rs = pst.executeQuery(); List<Map<String, Object>> list = this.rsToList(rs); if( !list.isEmpty() ){ return list.get(0); } this.releaseConnection(rs, pst, connect); return null; } /** * 根据sql查询列表数据，不支持预编译的方式 * @param sql * @return * @throws ClassNotFoundException * @throws SQLException */ public List<Map<String, Object>> executeQueryForList(String sql) throws SQLException, ClassNotFoundException{ System.err.println("查询多条："+sql); Connection connect =this.getConnection(); Statement stmt = connect.createStatement(); ResultSet rs = stmt.executeQuery(sql); List<Map<String, Object>> list = this.rsToList(rs); this.releaseConnection(rs, stmt, connect);//关闭连接 return list; } /** * 执行 增、删、改、等的操作，不支持预编译的方式 * @param sql * @return * @throws ClassNotFoundException * @throws SQLException */ public int executeUpdate(String sql) throws ClassNotFoundException, SQLException { System.err.println("更新："+sql); Connection connect=this.getConnection(); Statement stmt=connect.createStatement(); int count=stmt.executeUpdate(sql); this.releaseConnection(stmt, connect);//关闭连接 return count; } /** * 根据sql查询列表数据，支持预编译的方式 * @param sql * @param types * @param values * @return * @throws SQLException * @throws ClassNotFoundException */ public List<Map<String, Object>> executeQueryForList(String sql , int[] types,Object[] values) throws ClassNotFoundException, SQLException{ System.err.println("查询多条："+sql); this.print(values); Connection connect = this.getConnection(); PreparedStatement pst = connect.prepareStatement(sql); if( types != null ){ for(int i=0;i<types.length;i++){ switch( types[i] ){ case Types.VARCHAR: pst.setString(i+1, String.valueOf( values[i] ) ); break; case Types.INTEGER: pst.setInt(i+1, Integer.parseInt( String.valueOf( values[i] ) )); break; } } } ResultSet rs = pst.executeQuery(); List<Map<String, Object>> list = this.rsToList(rs); this.releaseConnection(rs, pst, connect); return list; } /** * 预编译sql操作， 支持insert ， update ， delete 语句 * @param sql * @param types * @param values * @return * @throws SQLException * @throws ClassNotFoundException * @throws IOException */ public int executeUpdate(String sql , int[] types,Object[] values) throws ClassNotFoundException, SQLException, IOException{ System.err.println("更新："+sql); this.print(values); Connection connect = this.getConnection(); PreparedStatement pst = connect.prepareStatement(sql); if( types != null ){ for(int i=0;i<types.length;i++){ switch( types[i] ){ case Types.VARCHAR: pst.setString(i+1, String.valueOf( values[i] ) ); break; case Types.INTEGER: pst.setInt(i+1, Integer.parseInt( String.valueOf( values[i] ) )); break; case Types.DATE: pst.setDate(i+1, Date.valueOf( String.valueOf( values[i] ) )); break; case Types.BLOB: InputStream in = new FileInputStream( (File)values[i] ); pst.setBinaryStream(i+1, in , in.available() ); break; } } } int count = pst.executeUpdate(); if (count>0) { System.out.println("更新成功"); } this.releaseConnection(pst, connect); return count; } /** * 查询一个整数，例如记录总数（不支持预编译） * @param sql * @param types * @param values * @return * @throws SQLException * @throws ClassNotFoundException */ public int executeQueryForInt(String sql) throws ClassNotFoundException, SQLException{ System.err.println("查询总数："+sql); Connection connect =this.getConnection(); Statement stmt = connect.createStatement(); ResultSet rs = stmt.executeQuery(sql); if(rs.next()){ return rs.getInt(1); } this.releaseConnection(rs, stmt, connect); return 0; } /** * 查询一个整数，例如记录总数（支持预编译） * @param sql * @param types * @param values * @return * @throws SQLException * @throws ClassNotFoundException */ public int executeQueryForInt(String sql , int[] types,Object[] values) throws ClassNotFoundException, SQLException{ System.err.println("查询总数："+sql); this.print(values); Connection connect = this.getConnection(); PreparedStatement pst = connect.prepareStatement(sql); if( types != null ){ for(int i=0;i<types.length;i++){ switch( types[i] ){ case Types.VARCHAR: pst.setString(i+1, String.valueOf( values[i] ) ); break; case Types.INTEGER: pst.setInt(i+1, Integer.parseInt( String.valueOf