JDBC重构实现Dao工具类

1.JDBC重构实现Dao工具类

package com.rj.bd.jdbcs.jdbc01;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
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;

/**
 * 数据库连接对象
 */
public class DaoImpl2 implements Dao{
	private String sDBDriver = "org.gjt.mm.mysql.Driver";
	private String sConnStr = "jdbc:mysql://localhost:3306/examOnline?useUnicode=true&characterEncoding=utf-8";//设置数据库名称为：pubs
    private String user = "root";  //登录数据库用户名
    private String password = "root";   //登录数据库密码    
    /**
     * 建立连接
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection getConnection() throws ClassNotFoundException, SQLException{ 	
		 Class.forName(sDBDriver); //指定JDBC数据库驱动程序
		 return DriverManager.getConnection(sConnStr,user,password);
    }
	/**
     * 根据sql查询列表数据(查询一条)，不支持预编译的方式
     * @param sql
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
	public Map<String, Object> executeQueryForMap(String sql)throws ClassNotFoundException, SQLException {
	/**	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);
		if( !list.isEmpty() ){
	this.releaseConnection(rs, stmt, connect);//关闭连接
			return list.get(0);
		}
		this.releaseConnection(rs, stmt, connect);//关闭连接（代码写到这里能执行吗）
		return null;**/
Connection conn=null;
	Statement stmt=null;
	ResultSet rs =null;	
	System.out.println("查询一条--sql--"+sql);
	try 
	{
		conn = this.getConnection();
		stmt = conn.createStatement();
		rs= stmt.executeQuery(sql);
		List<Map<String, Object>> list = this.rsToList(rs);
		if (!list.isEmpty()) 
		{
			return list.get(0);
		}
	} 
	finally 
	{
	  this.releaseConection(conn, stmt, rs);	
	}
	return null;

	}
    /**
     * 根据sql查询列表数据，不支持预编译的方式
     * @param sql
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public List<Map<String, Object>> executeQueryForList(String sql) throws ClassNotFoundException, SQLException{
    	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;
	}
	/**
	 * 查询一个整数，例如记录总数（不支持预编译）
	 * @param sql
	 * @param types
	 * @param ObjectValues
	 * @return
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public int executeQueryForCount(String sql) throws ClassNotFoundException, SQLException{
			System.out.println("查询一共有多少条的sql:"+sql);
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	try 
	{
		conn = this.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		if (rs.next()) 
		{
			return rs.getInt(1);
		}
	} 
	finally
	{
		this.releaseConection(conn, stmt, rs);	
	}    
	return 0;
	}	
	/**
	 * 将ResultSet中的结果包装成list中装Map的结构
	 * @param		 rs
	 * @return
	 * @throws SQLException
	 */
	private List<Map<String, Object>> rsToList( ResultSet rs ) throws SQLException{
		List<Map<String, Object>> row = new ArrayList<Map<String, Object>>();
		 while (rs.next()) {
			 Map<String, Object> col = new HashMap<String, Object>();
			 for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
				 //System.out.println(  rs.getMetaData().getColumnType(i)  );
				 switch( rs.getMetaData().getColumnType(i) ){
			 	 case Types.VARCHAR:
			 		col.put(rs.getMetaData().getColumnName(i), rs.getString(i));
			 		break;
			 	 case Types.INTEGER:
				 	col.put(rs.getMetaData().getColumnName(i), rs.getInt(i));
				 	break;	
			 	 case Types.BLOB:
			 		InputStream in = rs.getBinaryStream(i);
				 	col.put(rs.getMetaData().getColumnName(i), in );
				 	break;	
				 default:
					 col.put(rs.getMetaData().getColumnName(i), rs.getString(i));
				 	break;	
				 }				 
			 }
			row.add(col);
		}
		 return row;
	}
	@SuppressWarnings("unused")
	private void releaseConnection(Connection connect) throws SQLException{
	    try {
	        if (connect != null && !connect.isClosed()){
	        	connect.close();
	        }
	    } catch (SQLException se){
	        System.out.println("Close the connection encounter error!\n" + se.getMessage());
	        throw new SQLException("关闭连接异常！");
	    }
	}	
	private void releaseConnection(Statement stmt, Connection connect) throws SQLException{
	    try {
	        if (stmt != null){
	        	stmt.close();
	        }
	        if (connect != null && !connect.isClosed()){
	        	connect.close();
	        }
	    } catch (SQLException se){
	        System.out.println("Close the connection encounter error!\n" + se.getMessage());
	        throw new SQLException("关闭连接异常！");
	    }
	}	
	private void releaseConnection(ResultSet rs, Statement stmt, Connection connect) throws SQLException{
	    try {
	        if (rs != null){
	            rs.close();
	        }
	        if (stmt != null){
	        	stmt.close();
	        }
	        if (connect != null && !connect.isClosed()){
	        	connect.close();
	        }
	    } catch (SQLException se){
	        System.out.println("Close the connection encounter error!\n" + se.getMessage());
	        throw new SQLException("关闭连接异常！");
	    }
	}	
}

2.SQL语句操作之进阶

2.1模糊查询

select * from student;--全部查询所有的信息
1.模糊查询

select * from student where sname ='张三';--查询有where条件的信息

select*　from student where sname like '张%';--右模糊查询、

select *　from student where sname like '%三';--左模糊查询

select * from student where sname like '%萧%';--左右都模糊查询

2.2范围查询

 范围查询
 2.2.1关系运算符>  >=   <  <=  =   <>
select * from student where sage>23;
select * from student where sage <>23;--不等于 ，即要么大于，要么小于
select * from student where sage=23;

  2.2.2逻辑运算符：  and  or  not（经常与in一起连用）

select * from student where ssex='男'   and  sage='22'
select * from student where ssex='女'   or    sage>30
SELECT  *  FROM student  WHERE sage NOT IN(20,30,21)     /*not是非运算符*，其意思为非/、

  2.2.3 between  ...   and.....
SELECT  *
FROM student
WHERE sage BETWEEN  20  AND  30

 2.2.4算术运算符： + - * / %
   select * from student where sage+10=31

2.3连接(关联)

 2.3.1全连接
    select t.tname,c.* from teacher t ,course c where t.tno=c.tno;
    
    select  st.*, t.tname,c.*,s.score from student st,sc s,course c ,teacher t where st.sno=s.sno and c.cno=s.cno and t.tno=c.tno;
        
   2.3.2左连接:left join 返回包括左表中的所有记录和右表中连接字段相等的记录
   select * from course c left join teacher t on c.tno=t.tno; 
    
   2.3.3右连接:right join 返回包括右表的所有记录和左表中连接字段相等的记录
   select * from course c right join teacher t on c.tno=t.tno; 

2.4临时表

 临时表
              select * 
              from sc   ,( select* from student where sage>=23) linShi  
              where sc.sno=linshi.sno

2.5嵌套+子查询

嵌套+子查询
                 select  *
                 from sc
                 where sc.sno in (       
                                  select  sno from student where sage>20
                                   )

2.6分组

分组
select sno 
from sc 
where sc.score>55--成绩55小于其最小的成绩59，所以可以
group by sno--按照学号将自己的东西归为一组 
having count(sno)>=3--对分好组的信息再次做进一步的条件限制

2.7排序

在对结果进行排序的时候要用"order by 列名 DESC（降序）或者ASC(  升序)
SELECT  * 
FROM  sc
ORDER BY score DESC