com.mysql.jdbc.Statement Java Examples

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 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
    }
 

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();
        }
    }
}
 

/**
 * Tests Util.isJdbcInterface()
 * 
 * @throws Exception
 */
public void testIsJdbcInterface() throws Exception {
    // Classes directly or indirectly implementing JDBC interfaces.
    assertTrue(Util.isJdbcInterface(PreparedStatement.class));
    assertTrue(Util.isJdbcInterface(StatementImpl.class));
    assertTrue(Util.isJdbcInterface(Statement.class));
    assertTrue(Util.isJdbcInterface(ResultSetImpl.class));
    Statement s = (Statement) Proxy.newProxyInstance(this.getClass().getClassLoader(), new Class<?>[] { Statement.class }, new InvocationHandler() {
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            return null;
        }
    });
    assertTrue(Util.isJdbcInterface(s.getClass()));

    // Classes not implementing JDBC interfaces.
    assertFalse(Util.isJdbcInterface(Util.class));
    assertFalse(Util.isJdbcInterface(UtilsTest.class));

}
 

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 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;
}
 

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();
        }
    }
}
 

/**
 * Tests Util.isJdbcInterface()
 * 
 * @throws Exception
 */
public void testIsJdbcInterface() throws Exception {
    // Classes directly or indirectly implementing JDBC interfaces.
    assertTrue(Util.isJdbcInterface(PreparedStatement.class));
    assertTrue(Util.isJdbcInterface(StatementImpl.class));
    assertTrue(Util.isJdbcInterface(Statement.class));
    assertTrue(Util.isJdbcInterface(ResultSetImpl.class));
    Statement s = (Statement) Proxy.newProxyInstance(this.getClass().getClassLoader(), new Class<?>[] { Statement.class }, new InvocationHandler() {
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            return null;
        }
    });
    assertTrue(Util.isJdbcInterface(s.getClass()));

    // Classes not implementing JDBC interfaces.
    assertFalse(Util.isJdbcInterface(Util.class));
    assertFalse(Util.isJdbcInterface(UtilsTest.class));

}
 

/**
 * @description:get a new uuid by type
 * @date:2014-5-6 下午6:05:08
 * @version:v1.0
 * @param type
 * @return
 */
public synchronized String add(String type){
	Connection conn = null;
	PreparedStatement ptmt = null;
	ResultSet rs = null;
	try {
		conn = DbPoolConnection.getInstance().getConnection();
		ptmt = conn.prepareStatement("insert into uuid (type) values(?)",Statement.RETURN_GENERATED_KEYS);
		ptmt.setString(1, type);
		ptmt.execute();
		rs = ptmt.getGeneratedKeys();
		if (rs.next()) {
			return String.valueOf(rs.getInt(1));
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		DbPoolConnection.getInstance().closeResultSet(rs);
		DbPoolConnection.getInstance().closeStatment(ptmt);
		DbPoolConnection.getInstance().closeConn(conn);
	}
	return "";
}
 

/**
 * 说明：用于执行插入、更新、删除的sql语句，当受影响的行数为0和执行失败时返回false
 * 
 * @param sql
 * @return boolean 成功返回true，失败返回false
 */
public boolean excSql(String sql) {
	if (con == null)
		getConnection(); // 连接到数据库

	try {
		Statement st = (Statement) con.createStatement(); // 创建用于执行静态sql语句的Statement对象
		int counts = st.executeUpdate(sql); // 执行操作的sql语句
		if (0 == counts) {
			log.info("执行成功,，共0条数据受到影响，没有完成操作！：SQL语句-->【" + sql + "】");
			return false;
		}
		log.info("执行成功,共" + counts + "条数据受到影响:" + "SQL语句-->【" + sql + "】");
		return true;
	} catch (SQLException e) {
		log.error("执行失败：SQL语句-->【" + sql + "】");
		log.error(e.getMessage());
		return false;
	}

}
 

/**
 * 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;
}
 

@Test
public void test() throws Exception {
	Class.forName("com.mysql.jdbc.Driver");
	Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1", "root", null);

	PreparedStatement stmt = conn.prepareStatement("insert into test.test (name) values (?)",
	      Statement.RETURN_GENERATED_KEYS);

	stmt.setString(1, "a");
	stmt.addBatch();
	stmt.setString(1, "b");
	stmt.addBatch();

	stmt.executeBatch();

	ResultSet rs = stmt.getGeneratedKeys();
	while (rs.next()) {
		System.out.println(rs.getLong(1));
	}
}
 

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 ResultSetInternalMethods postProcess(String sql, Statement interceptedStatement, ResultSetInternalMethods originalResultSet, Connection connection)
        throws SQLException {

    // requirement of anonymous class
    final ResultSetInternalMethods finalResultSet = originalResultSet;

    return (ResultSetInternalMethods) Proxy.newProxyInstance(originalResultSet.getClass().getClassLoader(), new Class[] { ResultSetInternalMethods.class },
            new InvocationHandler() {

                public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

                    if ("equals".equals(method.getName())) {
                        // Let args[0] "unwrap" to its InvocationHandler if it is a proxy.
                        return args[0].equals(this);
                    }

                    Object invocationResult = method.invoke(finalResultSet, args);

                    String methodName = method.getName();

                    if (invocationResult != null && invocationResult instanceof String || "getString".equals(methodName) || "getObject".equals(methodName)
                            || "getObjectStoredProc".equals(methodName)) {
                        Matcher matcher = ResultSetScannerInterceptor.this.regexP.matcher(invocationResult.toString());

                        if (matcher.matches()) {
                            throw new SQLException("value disallowed by filter");
                        }
                    }

                    return invocationResult;
                }
            });

}
 

@Override
public PreparedStatement getPreparedStatement(Connection connection, String sql)
        throws SQLException
{
    PreparedStatement statement = connection.prepareStatement(sql);
    if (statement.isWrapperFor(Statement.class)) {
        statement.unwrap(Statement.class).enableStreamingResults();
    }
    return statement;
}
 

@Override
public ResultSetInternalMethods postProcess(String sql, Statement interceptedStatement,
  ResultSetInternalMethods originalResultSet, Connection connection, int warningCount,
  boolean noIndexUsed, boolean noGoodIndexUsed, SQLException statementException) {
  Span span = ThreadLocalSpan.CURRENT_TRACER.remove();
  if (span == null || span.isNoop()) return null;

  if (statementException != null) {
    span.error(statementException);
    span.tag("error", Integer.toString(statementException.getErrorCode()));
  }
  span.finish();

  return null;
}
 

public Long insert() {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(conn -> conn.prepareStatement("INSERT INTO initial_code VALUE()",
            Statement.RETURN_GENERATED_KEYS), keyHolder);

    return keyHolder.getKey().intValue() - 1L;
}
 

public Long insert(Long initialCode, String url) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(
            conn -> {
                PreparedStatement statement = conn.prepareStatement(
                        String.format("INSERT INTO %s(url) VALUES(?)", getTableName(initialCode)),
                        Statement.RETURN_GENERATED_KEYS);
                statement.setString(1, url);
                return statement;
            }, keyHolder);

    return calculatePhysicalCode(initialCode, keyHolder.getKey().longValue());
}
 

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

    // requirement of anonymous class
    final ResultSetInternalMethods finalResultSet = originalResultSet;

    return (ResultSetInternalMethods) Proxy.newProxyInstance(originalResultSet.getClass().getClassLoader(), new Class[] { ResultSetInternalMethods.class },
            new InvocationHandler() {

                public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

                    if ("equals".equals(method.getName())) {
                        // Let args[0] "unwrap" to its InvocationHandler if it is a proxy.
                        return args[0].equals(this);
                    }

                    Object invocationResult = method.invoke(finalResultSet, args);

                    String methodName = method.getName();

                    if (invocationResult != null && invocationResult instanceof String || "getString".equals(methodName) || "getObject".equals(methodName)
                            || "getObjectStoredProc".equals(methodName)) {
                        Matcher matcher = ResultSetScannerInterceptor.this.regexP.matcher(invocationResult.toString());

                        if (matcher.matches()) {
                            throw new SQLException("value disallowed by filter");
                        }
                    }

                    return invocationResult;
                }
            });

}
 

public ResultSet query(String sql){
    try{
        this.dbStatement = (Statement) this.dbConnection.createStatement();
        return this.dbStatement.executeQuery(sql);
    }
    catch(SQLException e){
        System.out.println("Erro ao tentar realizar a Consulta: " + e.getMessage());
        System.out.println("SQL que foi realizada: " + sql);
    }
    return null;
}
 

public ResultSet query(String sql){
    try{
        this.dbStatement = (Statement) this.dbConnection.createStatement();
        return this.dbStatement.executeQuery(sql);
    }
    catch(SQLException e){
        System.out.println("Erro ao tentar realizar a Consulta: " + e.getMessage());
        System.out.println("SQL que foi realizada: " + sql);
    }
    return null;
}
 

public ResultSet query(String sql){
    try {
        this.dbStatement = (Statement) dbConnection.createStatement();
        return this.dbStatement.executeQuery(sql);
    } catch (SQLException ex) {
        System.out.println("Houve um erro ao tentar executar um sql (consulta) : " + ex.getMessage());
        System.out.println("[SQL] " + sql);
    }
    return null;
}
 

public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) throws SQLException {
    return null;
}
 

public ResultSetInternalMethods postProcess(String sql, Statement interceptedStatement, ResultSetInternalMethods originalResultSet, Connection connection,
        int warningCount, boolean noIndexUsed, boolean noGoodIndexUsed, SQLException statementException) throws SQLException {
    return originalResultSet;
}
 

public ResultSetInternalMethods postProcess(String sql, Statement interceptedStatement, ResultSetInternalMethods originalResultSet, Connection connection)
        throws SQLException {
    return null;
}
 

public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) throws SQLException {
    // we don't care about this event

    return null;
}
 

public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) throws SQLException {
    // we don't care about this event

    return null;
}
 

public ResultSetInternalMethods postProcess(String sql, Statement interceptedStatement, ResultSetInternalMethods originalResultSet, Connection connection)
        throws SQLException {
    return null;
}
 

public ResultSetInternalMethods postProcess(String sql, Statement interceptedStatement, ResultSetInternalMethods originalResultSet, Connection connection,
        int warningCount, boolean noIndexUsed, boolean noGoodIndexUsed, SQLException statementException) throws SQLException {
    return originalResultSet;
}
 

public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) throws SQLException {
    return null;
}