com.mysql.jdbc.Connection Java Examples

@Override
public int execute(MapleClient c, String[] splitted) {
    if (splitted.length < 2) {
        c.getPlayer().dropMessage(0, splitted[0] + " <SQL命令>");
        return 0;
    }
    try {
        Connection con = (Connection) DatabaseConnection.getConnection();
        PreparedStatement ps = (PreparedStatement) con.prepareStatement(StringUtil.joinStringFrom(splitted, 1));
        ps.executeUpdate();
        ps.close();
    } catch (SQLException e) {
        c.getPlayer().dropMessage(0, "执行SQL命令失败");
        return 0;
    }
    return 1;
}
 

public ArrayList<Department> query_all_department() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from department order by dno;";
	ArrayList<Department> results = new ArrayList<Department>();
	
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			Department temp = new Department();
			temp.setDno(rs.getString("Dno")); 
			temp.setDname(rs.getString("Dname"));
			results.add(temp);
		}
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 

public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) throws SQLException {

        if (connection.versionMeetsMinimum(5, 0, 2)) {
            populateMapWithSessionStatusValues(connection, this.preExecuteValues);
        }

        return null; // we don't actually modify a result set
    }
 

public int delete_user(String username) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from user where username = ?;";
	
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, username);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public ArrayList<User> query_all_user() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from user order by username;";
	ArrayList<User> results = new ArrayList<User>();
	
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		while(rs.next()){
			User temp = new User();
			temp.setUsername(rs.getString("username"));
			temp.setPassword(rs.getString("password"));
			temp.setLevel(rs.getString("level"));
			results.add(temp);
		}
		//关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 

public User register(String username,String password,String level) {
	Connection conn = DBUtils.getConnection();
	User user = null;
	
	try {
		//判断数据库中是否存在该用户
		if(!userIsExist(username)){//不存在该用户，可以注册
			user = new User();//实例化一个user对象
			//给用户对象赋值
			user.setUsername(username);
			user.setPassword(password);
			user.setLevel(level);
			//将用户对象写入数据库
			Statement stmt = (Statement) conn.createStatement();
			stmt.executeUpdate("insert into user values('"+username+"','"+password+"','"+level+"');");
			stmt.close();//释放资源
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return user;
}
 

public boolean userIsExist(String username) {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from user where username = ?";
	try{
		//获取PreparedStatement对象
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, username);//给用户对象属性赋值
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			//数据库中存在此用户
			return true;
		}
		//释放资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return false;
}
 

public int alter_course(String cno,String after_cno,String after_cname,String after_cteacher,double after_ccredit) {
	Connection conn = DBUtils.getConnection();
	String sql = "update course set cno = ?,cname = ?,cteacher = ?,ccredit = ? where cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_cno);
		ps.setString(2, after_cname);
		ps.setString(3, after_cteacher);
		ps.setDouble(4, after_ccredit);
		ps.setString(5, cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int delete_course(String Cno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from course where Cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int insert_course(String Cno, String Cname, String Cteacher, double Ccredit) {
	Connection conn = DBUtils.getConnection();
	String sql = "insert into course values(?,?,?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Cno);
		ps.setString(2, Cname);
		ps.setString(3, Cteacher);
		ps.setDouble(4, Ccredit);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int delete_department(String dno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from department where dno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, dno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public ArrayList<Class> query_all_class() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from class order by clno;";
	ArrayList<Class> results = new ArrayList<Class>();
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Class temp = new Class();
			temp.setClno(rs.getString("clno"));
			temp.setClname(rs.getString("clname"));
			temp.setDno(rs.getString("dno"));
			results.add(temp);
		}
		// 关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 

public int delete_class(String clno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from class where clno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, clno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

private void populateMapWithSessionStatusValues(Connection connection, Map<String, String> toPopulate) throws SQLException {
    java.sql.Statement stmt = null;
    java.sql.ResultSet rs = null;

    try {
        toPopulate.clear();

        stmt = connection.createStatement();
        rs = stmt.executeQuery("SHOW SESSION STATUS");
        Util.resultSetToMap(toPopulate, rs);
    } finally {
        if (rs != null) {
            rs.close();
        }

        if (stmt != null) {
            stmt.close();
        }
    }
}
 

/**
 * Uses {@link ThreadLocalSpan} as there's no attribute namespace shared between callbacks, but
 * all callbacks happen on the same thread.
 *
 * <p>Uses {@link ThreadLocalSpan#CURRENT_TRACER} and this interceptor initializes before
 * tracing.
 */
@Override
public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement,
  Connection connection) {
  // Gets the next span (and places it in scope) so code between here and postProcess can read it
  Span span = ThreadLocalSpan.CURRENT_TRACER.next();
  if (span == null || span.isNoop()) return null;

  // When running a prepared statement, sql will be null and we must fetch the sql from the statement itself
  if (interceptedStatement instanceof PreparedStatement) {
    sql = ((PreparedStatement) interceptedStatement).getPreparedSql();
  }
  int spaceIndex = sql.indexOf(' '); // Allow span names of single-word statements like COMMIT
  span.kind(CLIENT).name(spaceIndex == -1 ? sql : sql.substring(0, spaceIndex));
  span.tag("sql.query", sql);
  parseServerIpAndPort(connection, span);
  span.start();
  return null;
}
 

public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) throws SQLException {
    String key = getSessionKey();

    if (key != null && !key.equals(this.currentSessionKey)) {
        PreparedStatement pstmt = connection.clientPrepareStatement("SET @mysql_proxy_session=?");

        try {
            pstmt.setString(1, key);
            pstmt.execute();
        } finally {
            pstmt.close();
        }

        this.currentSessionKey = key;
    }

    return null;
}
 

public void init(Connection conn, Properties props) throws SQLException {
    String regexFromUser = props.getProperty("resultSetScannerRegex");

    if (regexFromUser == null || regexFromUser.length() == 0) {
        throw new SQLException("resultSetScannerRegex must be configured, and must be > 0 characters");
    }

    try {
        this.regexP = Pattern.compile(regexFromUser);
    } catch (Throwable t) {
        SQLException sqlEx = new SQLException("Can't use configured regex due to underlying exception.");
        sqlEx.initCause(t);

        throw sqlEx;
    }

}
 

public int queryrepeat(String url,String body,Connection conn) {
 String sql_exec = "SELECT COUNT(*) as count FROM httplog WHERE url=? AND body = ?";
 int flag = 0;
 try {
	PreparedStatement ps = conn.prepareStatement(sql_exec);
	ps.setString(1, url);
	ps.setString(2, body);
	ResultSet rs = ps.executeQuery();
	while (rs.next()) {
		flag = rs.getInt("count");
	}
	return flag;
	
} catch (SQLException e) {
	e.printStackTrace();
}
 
return 0;
}
 

public int insert(Map<String,String> sql,Connection conn,PrintWriter stdout) {
 String sql_exec = "INSERT INTO httplog(url,method,header,body) VALUE(?,?,?,?)";
 try {
	PreparedStatement ps = conn.prepareStatement(sql_exec);
	ps.setString(1, sql.get("url"));
	ps.setString(2, sql.get("method"));
	ps.setString(3, sql.get("headers"));
	ps.setString(4, sql.get("body"));
	int i = ps.executeUpdate();
	stdout.println("[+] insert ["+i+"] row ");
	return i;
} catch (SQLException e) {
	e.printStackTrace();
}
 
 return 0;
}
 

public int delete_student(String sno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from student where sno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, sno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int insert_student(String Sno,String Sname,String Ssex,int Sage,String Clno) {
	Connection conn = DBUtils.getConnection();
	String sql = "insert into student values(?,?,?,?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Sno);
		ps.setString(2, Sname);
		ps.setString(3, Ssex);
		ps.setInt(4, Sage);
		ps.setString(5, Clno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public ArrayList<Student> query_all_student() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from student order by sno;";
	ArrayList<Student> results = new ArrayList<Student>();
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Student temp = new Student();
			temp.setSno(rs.getString("sno"));
			temp.setSname(rs.getString("sname"));
			temp.setSsex(rs.getString("ssex"));
			temp.setSage(rs.getInt("sage"));
			temp.setClno(rs.getString("clno"));
			results.add(temp);
		}
		// 关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 

public int alter_sc(String Sno, String Cno,double after_grade) {
	Connection conn = DBUtils.getConnection();
	String sql = "update sc set grade = ? where sno = ? and cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setDouble(1, after_grade);
		ps.setString(2, Sno);
		ps.setString(3, Cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int delete_sc(String Sno,String Cno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from sc where sno = ? and cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Sno);
		ps.setString(2, Cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int alter_class(String clno, String after_clno, String after_clname, String after_dno) {
	Connection conn = DBUtils.getConnection();
	String sql = "update class set clno = ?,clname = ?,dno = ? where clno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_clno);
		ps.setString(2, after_clname);
		ps.setString(3, after_dno);
		ps.setString(4, clno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

private static synchronized XAConnection findConnectionForXid(Connection connectionToWrap, Xid xid) throws SQLException {
    // TODO: check for same GTRID, but different BQUALs...MySQL doesn't allow this yet

    // Note, we don't need to check for XIDs here, because MySQL itself will complain with a XAER_NOTA if need be.

    XAConnection conn = XIDS_TO_PHYSICAL_CONNECTIONS.get(xid);

    if (conn == null) {
        conn = new MysqlXAConnection(connectionToWrap, connectionToWrap.getLogXaCommands());
        XIDS_TO_PHYSICAL_CONNECTIONS.put(xid, conn);
    }

    return conn;
}
 

/**
 * Construct a new LogicalHandle and set instance variables
 * 
 * @param mysqlPooledConnection
 *            reference to object that instantiated this object
 * @param mysqlConnection
 *            physical connection to db
 * 
 * @throws SQLException
 *             if an error occurs.
 */
public ConnectionWrapper(MysqlPooledConnection mysqlPooledConnection, Connection mysqlConnection, boolean forXa) throws SQLException {
    super(mysqlPooledConnection);

    this.mc = mysqlConnection;
    this.closed = false;
    this.isForXa = forXa;

    if (this.isForXa) {
        setInGlobalTx(false);
    }
}
 

public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) throws SQLException {

        if (connection.versionMeetsMinimum(5, 0, 2)) {
            populateMapWithSessionStatusValues(connection, this.preExecuteValues);
        }

        return null; // we don't actually modify a result set
    }
 

public void init(Connection conn, Properties props) throws SQLException {
    this.connection = conn;

    String pkURL = this.connection.getServerRSAPublicKeyFile();
    if (pkURL != null) {
        this.publicKeyString = readRSAKey(this.connection, pkURL);
    }
}
 

/**
    * 关闭数据库连接
    * @param conn Connection对象
    */
   public static void closeConnection(Connection conn) {
	//判断conn是否为空
   	if(conn != null){
   		try {
			conn.close();//关闭数据库连接
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
   	}
}