package cn.chenzhiheng.servlet;

import java.io.IOException;
import java.util.Enumeration;

import javax.servlet.Servlet;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

public class servletDemo implements Servlet{

	private ServletConfig sc ;
	/*
	 *    servlet 初始化方法
	 *    servlet生命周期方法之一
	 */
	@Override
	public void init(ServletConfig servletConfig) throws ServletException {
		System.out.println(servletConfig.getServletName());
		Enumeration<String> e = servletConfig.getInitParameterNames();
		while(e.hasMoreElements()){
			String key = e.nextElement();
			String value = servletConfig.getInitParameter(key);
			System.out.println(key+" : "+value );
		}
		this.sc =  servletConfig;
		System.out.println("init ...");
	}
	/*
	 *    servlet 处理请求方法
	 *    servlet生命周期方法之一
	 */
	@Override
	public void service(ServletRequest arg0, ServletResponse arg1) throws ServletException, IOException {
		System.out.println("service ...");
	}
	/*
	 *    servlet  销毁方法
	 *    servlet生命周期方法之一
	 */
	@Override
	public void destroy() {
		System.out.println("destroy ...");
	}
	/*
	 * 获取 servlet 配置信息方法。
	 */
	@Override
	public ServletConfig getServletConfig() {
		System.out.println("getServletConfig ...");
		return sc;
	}
	/*
	 *  获取servlet信息。
	 */
	@Override
	public String getServletInfo() {
		System.out.println("我是一个快乐的小Servlet！ ...");
		return null;
	}

}

package dbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;


/*
 * 数据库工具类
 */
public class DbUtils {
	//连接池对象
	private static DataSource dataSource; 
	//数据库连接对象  Connection
	private static Connection conn;
	//操作数据库对象Statement
	private static Statement sta;
	//操作数据库预编译对象Preparstatement
	private static PreparedStatement ps;

	/*
	 * 静态代码块   类加载时完成数据初始化
	 */
	static {
		ComboPooledDataSource cpds = new ComboPooledDataSource();
		dataSource =  cpds;
	}
	/*
	 * 获取Connection连接的方法
	 * @return conn
	 */
	public static  Connection getConnection() {
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/*
	 * 关闭Connection连接的方法
	 */
	public static void close(){
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
//	public static void main(String[] args) {
//		Connection conn = DbUtils.getConnection();
//		System.out.println(conn);
//	}
}

package com.shanxi.weixin;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.CallableStatement;
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.Properties;

public class JDBCDemo6 {

	public static void main(String[] args) {
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
	  		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.42.43:1521:orcl","gao", "gao");
	  		//操作数据库调用函数或存储过程需要另一个操作数据库的对象CallableStatement   
	  		CallableStatement pc = conn.prepareCall("{?= call fun_getInfo(?)}");
	  		// 注册返回值的类型
	  		pc.registerOutParameter(1,Types.VARCHAR);
	  		// 设置函数/存储过程的参数列表    in  in out  out   
	  		pc.setInt(2,10);
	  		//调用对应的函数/存储过程
	  		pc.execute();
	  		//接受返回值
	  		String name = pc.getString(1);
	  		System.out.println(name);
	  		if(conn!=null){
	  			conn.close();
	  		}                
	  		
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package com.shanxi.weixin;

import java.io.FileInputStream;
import java.io.IOException;
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.util.Properties;

public class JDBCDemo5 {

	public static void main(String[] args) {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
	  		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test4?useUnicode=true&characterEncoding=utf8","root", "root");
	  		String sql = "insert into people(name,age,sex) values(?,?,?)";
	  		//Statement sta = conn.createStatement();
	  		
	  		//打开一个预编译对象  同时绑定sql
	  		PreparedStatement ps = conn.prepareStatement(sql);
	  		ps.setString(1, "bob");
	  		ps.setString(3, "boy");
	  		ps.setInt(2, 22);
	  		//将本条sql处理 加入批处理
	  		ps.addBatch();
	  		
	  		ps.setString(1, "mary");
	  		ps.setString(3, "girl");
	  		ps.setInt(2, 18);
	  		//将重新设置参数后的预编译sql继续加入批处理
	  		ps.addBatch();
	  		//同时也可以绑定多条静态sql
	  		ps.addBatch("insert into people(name,age,sex) values('rose',19,'girl')");
	  		//执行批处理任务
	  		int[] counts = ps.executeBatch();
	  		ps.clearBatch();
	  		for (int i : counts) {
				System.out.println(i);
			}
	  		if(conn!=null){
	  			conn.close();
	  		}                                      
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package com.shanxi.weixin.dao;

import java.sql.Connection;
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;

import com.shanxi.weixin.dbUtils.DbUtils;
/**
 * superDao实现类   
 * 本类提供了  查询多条数据  查询单条数据 查询数量 对数据增删改的基本实现 
 */
public class SuperDaoImpl implements SuperDao {

	/**
	 * 不支持预编译的查询多条数据方法
	 * @param sql    需要执行的sql语句
	 * @return list  执行查询sql后返回的结果视图集合
	 */
	@Override
	public List<Map<String, Object>> queryForList(String sql) {
		List<Map<String,Object>> list =  new ArrayList<Map<String, Object>>();
		try {
			Connection conn = DbUtils.getConnection();
			Statement sta = conn.createStatement();
			ResultSet rs = sta.executeQuery(sql);
			list = rsToList(rs);
			DbUtils.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 支持预编译的查询多条数据方法
	 * @param sql 	需要执行的预编译sql语句
	 * @param types	预编译sql中参数的类型数组
	 * @param obj	预编译sql中参数的值数组
	 * @return		执行查询sql后返回的结果视图集合
	 */
	@Override
	public List<Map<String, Object>> queryForList(String sql, int[] types, Object[] obj) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			Connection conn = DbUtils.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			//循环遍历参数数组 并将数组中的值设置给预编译sql
			for (int i = 0; i < obj.length; i++) {
				switch (types[i]) {
				case Types.INTEGER:
					ps.setInt(i+1,Integer.parseInt(obj[i].toString()));
					break;
				case Types.VARCHAR:
					ps.setString(i+1,obj[i].toString());
					break;
				default:
					ps.setString(i+1,obj[i].toString());
				}
			}
			ResultSet rs = ps.executeQuery();
			list = rsToList(rs);
			DbUtils.close();
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	/**
	 * 不支持预编译的查询单条数据方法
	 * @param sql	需要执行的sql语句
	 * @return		执行查询sql后返回的单条数据
	 */
	@Override
	public Map<String, Object> queryForMap(String sql) {
		List<Map<String,Object>> list =  new ArrayList<Map<String, Object>>();
		try {
			
			Connection conn = DbUtils.getConnection();
			Statement sta = conn.createStatement();
			ResultSet rs = sta.executeQuery(sql);
			list = rsToList(rs);
			DbUtils.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(!list.isEmpty()){
			return list.get(0);
		}
		return null;
	}
	/**
	 * 支持预编译的查询单条数据方法
	 * @param sql	需要执行的预编译sql语句
	 * @param types 预编译sql中参数的类型数组
	 * @param obj	预编译sql中参数的值数组
	 * @return		执行查询sql后返回的单条数据
	 */
	@Override
	public Map<String, Object> queryForMap(String sql, int[] types, Object[] obj) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			Connection conn = DbUtils.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			//循环遍历参数数组 并将数组中的值设置给预编译sql
			for (int i = 0; i < obj.length; i++) {
				switch (types[i]) {
				case Types.INTEGER:
					ps.setInt(i+1,Integer.parseInt(obj[i].toString()));
					break;
				case Types.VARCHAR:
					ps.setString(i+1,obj[i].toString());
					break;
				default:
					ps.setString(i+1,obj[i].toString());
				}
			}
			ResultSet rs = ps.executeQuery();
			list = rsToList(rs);
			DbUtils.close();
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(!list.isEmpty()){
			return list.get(0);
		}
		return null;
	}

	/**
	 * 不支持预编译的增删改方法
	 * @param sql	需要执行的sql语句
	 * @return		对数据库操作后发生变化的数据条数
	 */
	@Override
	public int excuteUpdate(String sql) {
		try {
			Connection conn = DbUtils.getConnection();
			Statement sta = conn.createStatement();
			int count = sta.executeUpdate(sql);
			DbUtils.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 支持预编译的增删改方法
	 * @param sql	需要执行的预编译sql语句
	 * @param types 预编译sql中参数的类型数组
	 * @param obj	预编译sql中参数的值数组
	 * @return		对数据库操作后发生变化的数据条数
	 */
	@Override
	public int excuteUpdate(String sql, int[] types, Object[] obj) {
		try {
			Connection conn = DbUtils.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			//循环遍历参数数组 并将数组中的值设置给预编译sql
			for (int i = 0; i < obj.length; i++) {
				switch (types[i]) {
				case Types.INTEGER:
					ps.setInt(i+1,Integer.parseInt(obj[i].toString()));
					break;
				case Types.VARCHAR:
					ps.setString(i+1,obj[i].toString());
					break;
				default:
					ps.setString(i+1,obj[i].toString());
				}
			}
			int count = ps.executeUpdate();
			DbUtils.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 不支持预编译的统计数据条数方法
	 * @param sql	需要执行的sql语句
	 * @return		查询sql执行后返回的条目数量结果
	 */
	@Override
	public int queryForCount(String sql) {
		try {
			Connection conn = DbUtils.getConnection();
			Statement sta = conn.createStatement();
			ResultSet rs = sta.executeQuery(sql);
			int count = 0 ;
			while(rs.next()){
				count = rs.getInt(1);
			}
			DbUtils.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 支持预编译的统计数据条数方法
	 * @param sql	需要执行的预编译sql语句
	 * @param types 预编译sql中参数的类型数组
	 * @param obj	预编译sql中参数的值数组
	 * @return		查询sql执行后返回的条目数量结果
	 */
	@Override
	public int queryForCount(String sql, int[] types, Object[] obj) {
		try {
			Connection conn = DbUtils.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			//循环遍历参数数组 并将数组中的值设置给预编译sql
			for (int i = 0; i < obj.length; i++) {
				switch (types[i]) {
				case Types.INTEGER:
					ps.setInt(i+1,Integer.parseInt(obj[i].toString()));
					break;
				case Types.VARCHAR:
					ps.setString(i+1,obj[i].toString());
					break;
				default:
					ps.setString(i+1,obj[i].toString());
				}
			}
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while(rs.next()){
				count = rs.getInt(1);
			}
			DbUtils.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 提供将ResultSet结果集转换为list集合的实现
	 * @param  rs 	需要转换的结果集视图
	 * @return list	转换后封装数据的集合 
	 */
	private List<Map<String, Object>> rsToList(ResultSet rs) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			while(rs.next()){
				Map<String, Object>  map =  new HashMap<String,Object>();
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					switch (rs.getMetaData().getColumnType(i)) {
					case Types.INTEGER:
						map.put(rs.getMetaData().getColumnName(i),rs.getInt(i));
						break;
					case Types.VARCHAR:
						map.put(rs.getMetaData().getColumnName(i),rs.getString(i));
						break;
					default:
						map.put(rs.getMetaData().getColumnName(i),rs.getObject(i));
					}
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	public static void main(String[] args) {
		//查询多条数据
		SuperDao dao =  new SuperDaoImpl();
		String sql = "select * from people";
		List<Map<String, Object>> list = dao.queryForList(sql);
		System.out.println(list);
	}
}