insert_infodao.java

本方案是在与***信息技术（北京）有限公司相关人员协商并分析了北京****投资有限公司综合信息管理系统（以下简称“**管理系统”）相关资料之后提交的网络安全解决方案。 本方案描述***管理系统的网络

package com.test.dao;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import org.gjt.mm.mysql.Driver;
import com.test.model.BuildBean;
import com.test.model.LoginBean;
import com.test.model.RoleBean;
import com.test.model.UserBean;
import com.test.servers.Builder;
import com.test.servers.RoleGet;
import com.test.dao.pool.*;

public class Insert_infoDAO {
	private Connection conn;
	private ConnectionPool connectionPool;

	/**
	 * 构造一个数据库连接池
	 */
	public Insert_infoDAO() {
		try {
			this.connectionPool = ConnectionPool.getConnectionPool();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("无法创建数据库连接池");
		}
	}

	// 得到连接的方法
	private Connection getConnection() {
		try {
			return connectionPool.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}
	}

	// 关闭connection的方法
	private void closeConnection() {
		try {
			this.conn.close();
			this.conn = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 插入数据的方法
	 * 
	 * @param sqlText
	 *            sql insert 字符串
	 */
	public void insert(String sqlText) {
		try {
			conn = this.getConnection();
			Statement stat = conn.createStatement();
			stat.executeUpdate(sqlText);
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("数据插入失败，请重试");
		} finally {
			this.closeConnection();
		}
	}

	/**
	 * 更新数据的方法
	 * 
	 * @param sqlText
	 *            更新字符串sql
	 */
	public void update(RoleBean role, String sqlText) {
		try {
			conn = this.getConnection();
			Statement stat = conn.createStatement();
			stat.executeUpdate(sqlText);
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("数据更新失败，请重试");
		} finally {
			this.closeConnection();
		}
	}

	/**
	 * 防止注入攻击
	 * 
	 * @param user
	 *            要保存的用户
	 * @param sqlText
	 *            sql字符串
	 * @reutrn 返回该用户的pk_id，如果出错，则返回-1
	 */
	public int insertUser(UserBean user, String sqlText) {
		try {
			conn = this.getConnection();
			conn.setAutoCommit(false);
			PreparedStatement ps = conn.prepareStatement(sqlText);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getStr_a());
			ps.setString(3, user.getStr_b());
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			int pk_id = -1;
			if (rs.next()) {
				pk_id = rs.getInt(1);
				user.setPk_id(pk_id);
			}
			conn.commit();
			rs.close();
			ps.close();
			this.conn.setAutoCommit(true);
			return pk_id;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
				this.conn.setAutoCommit(true);
				throw new RuntimeException("插入用户失败，请重试");
			} catch (SQLException e1) {
				e1.printStackTrace();
				throw new RuntimeException("回滚出错");
			}
		} finally {
			this.closeConnection();
		}
	}

	/**
	 * 查询多个用户
	 * 
	 * @param sqlText
	 *            sql查询字符串
	 * @return 包含多个用户的集合
	 */
	public List<UserBean> query(String sqlText) {
		try {
			conn = this.getConnection();
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sqlText);
			List<UserBean> users = new java.util.ArrayList();
			while (rs.next()) {
				UserBean user = new UserBean();
				user.setPk_id(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setStr_a(rs.getString(3));
				user.setStr_b(rs.getString(4));
				user.setCexist(rs.getBoolean(5));
				user.setCreateNow(rs.getInt(6));
				users.add(user);
			}
			rs.close();
			stat.close();
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("查询用户失败，请重试");
		} finally {
			this.closeConnection();
		}
	}

	/**
	 * 查询多个用户
	 * 
	 * @param sqlText
	 *            sql查询字符串
	 * @return 包含多个用户的集合
	 */
	public List<BuildBean> queryBuild(String sqlText) {
		try {
			conn = this.getConnection();
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sqlText);
			List<BuildBean> builds = new java.util.ArrayList();
			while (rs.next()) {
				BuildBean build = new BuildBean();
				build.setStr_c(rs.getString(1));
				build.setSigntime(rs.getDate(2));
				build.setPk_id(rs.getInt(3));
				builds.add(build);
			}
			rs.close();
			stat.close();
			return builds;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("查询用户失败，请重试");
		} finally {
			this.closeConnection();
		}
	}

	/**
	 * 根据username,str_a,str_b 得到用户的所有信息
	 * 
	 * @param user
	 *            要查询的用户
	 * @param sqlText
	 *            查询字符串
	 * @return 包含所有用户信息，插询不到则返回null
	 */
	public void fillUserByPk_id(UserBean user, String sqlText) {
		try {
			conn = this.getConnection();
			PreparedStatement ps = conn.prepareStatement(sqlText);
			ps.setInt(1, user.getPk_id());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				user.setUsername(rs.getString(1));
				user.setStr_a(rs.getString(2));
				user.setStr_b(rs.getString(3));
				user.setCexist(rs.getBoolean(4));
				user.setCreateNow(rs.getInt(5));
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("填充用户信息失败");
		} finally {
			this.closeConnection();
		}
	}

	/**
	 * 查看用户的信息
	 * 
	 * @author liubin
	 * 
	 * 
	 */
	public Vector<UserBean> queryOneUser() {
		try {
			conn = this.getConnection();
			Statement stat = conn.createStatement();
			ResultSet rs = stat
					.executeQuery("select * from insert_info where state_now=1  and state='0'");
			Vector<UserBean> user1 = new Vector<UserBean>();
			while (rs.next()) {
				UserBean user = new UserBean();
				user.setPk_id(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setStr_a(rs.getString(3));
				user.setStr_b(rs.getString(4));
				user.setCexist(rs.getBoolean(5));
				user.setCreateNow(rs.getInt(6));
				user1.add(user);

			}
			rs.close();
			stat.close();
			return user1;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("");
		} finally {
			this.closeConnection();
		}
	}

	/**
	 * 添加用户的信息
	 * 
	 * @author lb
	 */
	public void insertStrToBuild(Vector<UserBean> v) {

		try {
			conn = this.getConnection();

			// BuildBean build = new BuildBean();
			Builder b = new Builder();
			// v = new Vector<UserBean>();

			Iterator<UserBean> i = v.iterator();
			while (i.hasNext()) {
				UserBean user = i.next();

				Statement ps = conn.createStatement();
				String sql = "insert into build( str_c,signtime,pk_id,state) values( '"
						+ b.build(user.getStr_a(), user.getStr_b())
						+ "' ,now()," + user.getPk_id() + ",1) ";
				// ps.setString(1, ));
				// ps.setInt(user.getPk_id(), 2);
				ps.executeUpdate(sql);
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
	}

	/**
	 * 当我将C串插入到数据库 同时修改insert_info中state_now 状态为"1" liubin
	 * 
	 */
	public void updateState(Vector<UserBean> users) {

		try {
			conn = this.getConnection();